这一篇文章分享 MySQL 的面试知识，涵盖点比较多。下面我们来从总体到局部来看完 my SQL 相关的面试知识。

问：mysql 整体架构是怎么样的？

1）网络连接层

Connectors 组件，是 mysql 向外提供的交互组件，如 java ,.net,php 等语言可以通过该组件来操作 SQL 语句 ，实现与 SQL 的交互。

2）服务层

服务层是 mysql Server 的核心。主要包含系统管理和控制工具、连接池、SQL 接口、解析器、查询优化器和缓存 Cache&Buffer 六个部分。

连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个 线程 负责管理一个连接。

系统管理和控制工具（Management Services & Utilities）：例如备份恢复、安全管理、集群 管理等

SQL 接口（SQL Interface）：用于接受客户端发送的各种 SQL 命令，并且返回用户需要查询的结果。

解析器（Parser）：负责将请求的 SQL 解析生成一个”解析树”。然后根据一些 my sql 规则进一步检查解析树是否合法。

查询优化器（Optimizer）：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互。

缓存（Cache&Buffer）：缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

3）存储引擎层（Pluggable Storage Engines）

存储引擎负责 MySQL 中数据的存储与提取，与底层系统文件进行交互。MySQL 存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异 。现在有很多种存储引擎，各有各的特点，最常见的是 myisam 和 innodb 。

4）系统文件层（ File System ）

该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid 文件， socket 文件等。

问：mysql 集群架构

关于 mysql 的架构，最底层的只有主从模式，关于主从模式是简单灵活，能满足多种需求，比较主流的用法，但是写操作高可用需要自己考虑。我们常见还有双主模式，该模式从主从模式演变为双主模式，有双主双写、双主单写两种方式，一般建议使用双主单写。关于主从架构的模式参考下面的图。

问：MySQL 的查询和插入的执行流程

下面我们图解 mysql 一条查询语句是怎么运行的：

mysql 客户端对 mysql server 的监听端口发起请求

在连接池组件创建连接，分配线程，并验证用户名，密码，库表>权限。

查询 query_cache，如果有数据直接返回，没有则继续执行。

通过 sql 接口组件接收 sql 语句，sql 会通过查询分析器分解成数据结构，并且这个结构传递给后续步骤

查询优化器组件组成查询路径树，并选举一条最优的查询路径。

调用存储引擎接口，打开表，执行查询，检查存储引擎缓存中是否有对应的缓存记录，如果没有就继续往下执行。

到磁盘物理文件中寻找数据。

当查询到所需要的数据之后，先写入存储引擎缓存中，并往 query_cache 写进去。

返回数据给客户端。

关闭表。

关闭线程。

关闭连接。

mysql 插入的过程如下

mysql 客户端对 mysql server 的监听端口发起请求

在连接池组件创建连接，分配线程，并验证用户名，密码，库表>权限。

检查没有问题之后，便进入引擎层开始正式的提交。我们知道 InnoDB 会将数据页缓存至内存中的 buffer pool，所以 insert 语句到了这里并不需要立刻将数据写入磁盘文件中，只需要修改 buffer pool 当中对应的数据页就可以了。

在开启 redo log 刷盘策略的时候，当 innodb_flush_log_at_trx_commit=1 时，每次事务提交都会触发一次 redo log 刷盘。（redo log 是顺序写入，相比直接修改数据文件，redo 的磁盘写入效率更加高效）

如果开启了 binlog 日志，我们还需将事务逻辑数据写入 binlog 文件，且为了保证复制安全，建议使用 sync_binlog=1 ，也就是每次事务提交时，都要将 binlog 日志的变更刷入磁盘。

返回数据给客户端。

关闭表。

关闭线程。

关闭连接。

问：mysql 有哪些字段类型

关于 mysql 的数据类型主要以上几种，一般我们创建表结构都是使用 innodb 引擎，后面我们会深入 innodb 引擎的知识。特别我们需要注意的是，为了获取更好的兼容性，建议使用 utf 8mb4 字符集，主要是用来兼容四字节的 unicode 。

utf8mb4 与 utf8 的区别

mysql 在 5.5.3 版本之后增加了 utf8mb4 编码，mb4 就是 most bytes 4 的意思，专门用来兼容四字节的 unicode。其实，utf8mb4 是 utf8 的超集，理论上原来使用 utf8 ，然后将 字符集 修改为 utf8mb4，也不会对已有的 utf8 编码读取产生任何问题。mysql 支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就插入异常。

char 和 varchar 区别

char 表示定长，长度固定，varchar 表示变长，即长度可变。char 如果插入的长度小于定义长度时，则用空格填充；varchar 小于定义长度时，还是按实际长度存储，插入多长就存多长。

char 的存取速度还是要比 varchar 要快得多，方便程序的存储与查找；但是 char 也为此付出的是空间的代价，因为其长度固定，所以会占据多余的空间，可谓是以空间换取时间效率。varchar 则刚好相反，以时间换空间。

对 char 来说，最多能存放的字符个数 255，和编码无关。而 varchar 呢，最多能存放 65532 个字符。varchar 的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532 字节。

timestamp 和 datatime 区别

timestamp 占 4 个字节。datetime 占用 8 个字节

timestamp 记录是 1970-01-01 00:00:01 到现在的数数,时间范围：‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTC，受时区影响。datetime 不受时区影响，时间范围:‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’

timestamp 存储占用的空间和 INT 类型相同，客户端插入的时间从当前时区转化为 UTC，查询时，将其又转化为客户端当前时区进行返回。datetime，不做任何改变，基本上是原样输入和输出。

数据库三范式

第一范式 ( 1NF )是指数据库表的每一列都是不可分割的基本数据线；也就是说：每列的值具有原子性，不可再分割。

第二范式(2NF)是在第一范式(1NF)的基础上建立起来得，满足第二范式(2NF)必须先满足第一范式(1NF)。如果表是单主键，那么主键以外的列必须完全依赖于主键；如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分。

第三范式( 3NF )是在第二范式的基础上建立起来的，即满足第三范式必须要先满足第二范式。第三范式(3NF)要求：表中的非主键列必须和主键直接相关而不能间接相关；也就是说：非主键列之间不能相关依赖。在规范和性能之间需要平衡，如何表拆得过细，会导致查表关联过多，导致性能问题。因此需要在规范性和性能之间作取舍。有时候会采用反三范式。

问：mysql 存储引擎有哪些？innodb 有什么特点？

innodb，myisam，memory， merge ，archive，ndb

innodb 引擎，从 MySQL5.5 版本之后，MySQL 的默认内置存储引擎已经是 innodb 了

支持事务，默认的事务隔离级别是可重复度，通过 MVCC （并发版本控制）来实现。

行级锁，可以支持更高的并发。

支持外键，虽然有这个功能，但实际业务中不怎么用。

在 innodb 中有缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加速查询速度。

在 innodb 中数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和 索引 放在一块，都位于 B+数的叶子节点上。

innodb 引擎如果没有设置主键索引，innodb 则会选择内置的 6 字节的 row-id 作为隐含的聚集索引。

innodb 支持分区，表空间。

innodb 引擎对硬件要求比较高。

innodb 灾难恢复性比较好。

问：mysql 索引有哪些，特征是怎么样的？

什么是索引？

索引是帮助存储引擎高效获取数据的一种数据结构。

索引按照物理存储的类型分 聚簇索引 ，辅助索引

索引按照字段特性分类：主键索引，唯一索引，普通索引，前缀索引，组合索引， 全文索引 （mysql5.6.4 之后支持）

优点：<br/>1）检索：可以提高数据检索的效率，降低数据库的 IO 成本，2）排序：通过索引列对数据进行排序，降低了 CPU 的消耗

缺点：<br/>1）占磁盘空间，2）降低更新表的效率

下面我们看下不同的索引的含义和解释

聚簇索引：主键索引(聚集索引)的 叶子 结点会存储数据行，也就是说数据和索引在一起

辅助索引：同样用 B+Tree,data 域存储相应记录主键的值而不是地址，首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

覆盖索引 ：覆盖索引一般针对的是辅助索引，整个査询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值。

主键索引：主键索引是一种特殊的唯一索引，一个表只能有一个主键且不允许有空值；索引列只能出现一次且必须唯一，InnoDB 要求表必须有主键，如果没有显示设置主键索引，那么会自动为数据表创建一个隐含的字段 row-id 作为主键，这个字段为 6 字节的长整型。

唯一索引：建立在 unique 字段上的索引就是唯一索引，不允许具有索引值相同的行，索引列的值可以允许为 null

普通索引：要求字段不为主键也不要求字段为 unique 的索引叫普通索引。

前缀索引：前缀索引是指对字符类型字段的前几个字符或对 二进制 类型字段的前几个 bytes 建立的索引。例子：name(varchar(16))

组合索引：在表中的对个字段组合上创建的索引。并且遵循最左前缀原则匹配。

全文索引：只能在 char,varchar,text 类型字段上使用全文索引。全文索引,通过建立 倒排索引 ,可以极大的提升检索效率,解决判断字段是否包含的问题。

问：讲讲 mysql 事务？

事务：事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务的特性：

原子性（atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用。

一致性（ consistency ）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的。

隔离性（ioslation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。

持久性（durability）：一个事务被提交之后，对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

事务的隔离级别

读取未提交 READ-UNCOMMITTED

允许读取尚未提交的数据变更，最低的隔离级别，可能导致 脏读 、 幻读 或 不可重复读 。

读取已提交 READ- COMMIT TED

允许读取并发事务已经提交的数据，可以避免脏读，可能导致幻读或不可重复读。

可重复读 REPEATABLE -READ

同一个事务下多次读取结果都是一致的，除非数据是被自己的事务所修改，可以避免脏读、不可重复读，但可能导致幻读。

串行化 SERIALIZABLE

脏读(读取未提交数据)：

脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读

不可重复读(前后多次读取，数据内容不一致)：

一个事务内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。

幻读(前后多次读取，数据总量不一致)：

一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表中还有没有修改的数据行，就好象发生了幻觉一样。

mysql 创建事务方式

START TARNSACTION |BEGIN：显式地开启一个事务。 COMMIT：提交事务，使得对数据库做的所有修改成为永久性。 ROLLBACK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。

mysql 默认的事务隔离级别是：可重复读

问：讲讲 mysql 锁相关知识

mysql 的锁按照范围可以分为全局锁、表锁、行锁，其中行锁是由数据库引擎实现的，并不是所有的引擎都提供行锁。我这里只讲 innodb 引擎的锁。

全局锁

mysql 提供全局锁来对整个数据库实例加锁。

// 锁表 FLUSH TABLES WITH READ LOCK // 解锁 unlock tables

上面语句一般都是用来备份的，当执行这条语句后，数据库所有打开的表都会被关闭，并且使用全局读锁锁定数据库的所有表，同时，其他线程的更新语句（增删改）， 数据定义 语句（建表，修改表结构）和更新类的事务提交都会被阻塞。

mysql 8.0 以后，对于备份，mysql 可以直接使用备份锁

// 加锁 LOCK INSTANCE FOR BACKUP // 解锁 UNLOCK INSTANCE

备份锁范围更广，会阻止文件的创建，重命名，删除，包括 REPAIR TABLE TRUNCATE TABLE, OPTIMIZE TABLE 操作以及账户的管理都会被阻塞。当然这些操作对于内存临时表来说是可以执行的，为什么内存表不受这些限制呢？因为内存表不需要备份，所以也就没必要满足这些条件。

表锁

表级别锁分为两类，一类是元数据锁（ Metadata Lock，MDL），一种是表锁。

表锁分为读锁和写锁 ，读锁不互斥，但是获取读锁不能写入数据,其他没有获取到读锁的 session 也是可以读取表的，所以读锁的目的就是限制表被写。

写锁被获取后可以对表进行读写 ，写锁是互斥的，一旦某个 session 获取到表的写锁，另外的 session 无法访问这个表，直到写锁被释放。

表的解锁可以使用 unlock tables 解锁，也可以客户端口自动解锁。

元数据锁(MDL) 不需要显式使用，在访问一个表的时候会被自动加上。这个特性需要 MySQL5.5 版本以上才会支持，当对一个表做增删改查的时候，该表会被加 MDL 读锁；当对表做结构变更的时候，加 MDL 写锁。

MDL 锁有一些规则

读锁之间不互斥，所以可以 多线程 多同一张表进行增删改查。

读写锁、写锁之间是互斥的，为了保证表结构变更的安全性，所以如果要多线程对同一个表加字段等表结构操作，就会变成串行化，需要进行锁等待。

MDL 的写锁优先级比 MDL 读锁的优先级高，但是可以设置 max_write_lock_count 系统变量 来改变这种情况，当写锁请求超过这个变量设置的数后，MDL 读锁的优先级会比 MDL 写锁的优先级高。（默认情况下，这个数字会很大，所以不用担心写锁的优先级下降）

MDL 的锁释放必须要等到事务结束才会释放

行锁

共享锁

共享锁能允许事务获取到锁后进行读操作，共享锁是不互斥的，一个事务获取到共享锁后，另外一个事务也可以获取共享锁，获取共享锁后不能进行写操作

排它锁

排他锁允许事务获取到锁后进行更新一行或者删除某一行操作，排他锁顾名思义是互斥的，一个事务获取到排他锁后，其他事务不能获取到排他锁，直到这个锁被释放。

意向锁

innodb 支持多种粒度的锁，允许行锁和表锁共存，这里说的意向锁其实是一种表级别的锁，但是我把它放在行锁里面是因为它不会单独存在，它的出现肯定会伴随着行锁（共享锁或者排他锁），它主要的目的就是表示将要锁定表中的行或者正在锁定表中的行。意向锁的获取必须在行锁获取之前，也就是说获取共享锁之前必须先要获取共享意向锁，对于排他锁也是一样的道理。

死锁 < DeadLock >

是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用，它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的 innodb。

问：讲讲 mysql mvcc 的机制

多版本并发控制 技术的英文全称是 Multiversion Concurrency Control，简称 MVCC。

多版本并发控制（MVCC） 是通过保存数据在某个时间点的快照来实现并发控制的。也就是说，不管事务执行多长时间，事务内部看到的数据是不受其它事务影响的，根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

多版本并发控制 的思想是保存数据的历史版本，通过对数据行的多个版本管理来实现数据库的并发控制。这样我们就可以通过比较版本号决定数据是否显示出来，读取数据的时候不需要加锁也可以保证事务的隔离效果。

MVCC 解决了哪些问题？

读写之间阻塞的问题。通过 MVCC 可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理能力。

降低了死锁的概率。因为 InnoDB 的 MVCC 采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行。

解决一致性读的问题。一致性读也被称为快照读，当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果。快照读（SnapShot Read） 是一种一致性不加锁的读，是 InnoDB 并发如此之高的核心原因之一。

innodb 的 MVCC 是如何工作的？

事务版本号：每开启一个事务，我们都会从数据库中获得一个事务 ID（也就是事务版本号），这个事务 ID 是自增长的，通过 ID 大小，我们就可以判断事务的时间顺序。

行记录的隐藏列：innodb 的叶子段存储了数据页，数据页中保存了行记录，而在行记录中有一些重要的隐藏字段：

DB_TRX_ID(6 字节)： 它是最近一次更新或者插入或者删除该行数据的事务 ID(若是删除，则该行有一个删除位更新为已删除。但并不是真正的进行物理删除，当 InnoDB 丢弃为删除而编写的更新撤消日志记录时，它才会物理删除相应的行及其索引记录。此删除操作称为清除，速度非常快)

DB_ROLL_PTR(7 字节)： 回滚指针，指向当前记录行的 undo log 信息(指向该数据的前一个版本数据)

DB_ROW_ID(6 字节)： 随着新行插入而单调递增的行 ID。InnoDB 使用聚集索引，数据存储是以聚集索引字段的大小顺序进行存储的，当表没有主键或唯一非空索引时，innodb 就会使用这个行 ID 自动产生聚簇索引。如果表有主键或唯一非空索引，聚簇索引就不会包含这个行 ID 了。这个 DB_ROW_ID 跟 MVCC 关系不大。

undo log 将行记录快照保存在里面，我们可以在回滚段中找到它们。

在可重复读的隔离级别下：

查询：符合下面两个条件的记录作为返回结果：1）innodb 只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。2）行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

插入：innodb 为新插入的每一行保存当前系统版本号作为行版本号。

删除：innodb 为删除的每一行保存当前系统版本号作为行删除标识。删除在内部被视为更新，行中的一个特殊位会被设置为已删除。

更新：innodb 为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

问：讲讲的 mysql 主从复制

什么是主从复制？

mysql 主从复制是指数据可以从一个 mysql 数据库服务器主节点复制到一个或者多个从节点。mysql 默认使用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

主从复制实现原理

master 服务器将数据的改变记录二进制 binlog 日志，当 master 上的数据发生改变时，则将其改变写入二进制日志中。

slave 服务器会在一定时间间隔内对 master 二进制日志进行探测其是否发生改变，如果发生改变，则开始一个 I/OThread 请求 master 二进制事件。

同时主节点为每个 I/O 线程启动一个 dump 线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动 SQL 线程从中继日志中读取二进制日志，在本地解析执行，使得其数据和主节点的保持一致，最后 I/OThread 和 SQLThread 将进入睡眠状态，等待下一次被唤醒。

基于 gtid 的复制模式

什么是 gitd？

gtid（Global Transaction ID）对于一个已提交事务的编号，并且是一个全局唯一的编号。GTID 实际上 是由 UUID+TID 组成的。其中 UUID 是一个 mysql 实例的唯一标识。TID 代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。

gtid 的复制原理

当一个事务在主库端执行并提交时，产生 gtid，一同记录到 binlog 日志中。

binlog 传输到 slave,并存储到 slave 的 relaylog 后，读取这个 gtid 的这个值设置 gtid_next 变量，即告诉 Slave，下一个要执行的 gtid 值。

sql 线程从 relay log 中获取 gtid，然后对比 slave 端的 binlog 是否有该 gtid。

如果有记录，说明该 gtid 的事务已经执行，slave 会忽略。

如果没有记录，slave 就会执行该 gtid 事务，并记录该 gtid 到自身的 binlog，在读取执行事务前会先检查其他 session 持有该 gtid，确保不被重复执行。

在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

问：mysql 如何保证写入数据不丢失？

总结：保证 redo log 和 bin log 可以持久化到磁盘，并且确保 mysql 在异常重启后进行数据恢复。

bin log 的写入机制：

事务执行过程中，先把日志写到 binlog cache（内存）

事务提交的时候（mysql 客户端执行 commit 指令），再把 bin log cache 中写到 bin log 文件中，并清空 bin log cache

每个线程都有自己的一个 bin log cache，但是共同使用同一份 bin log

write 把 binlog cache 写入到文件系统的 page cache，不会真正将数据持久化到磁盘。

fsync 才是将数据持久化到磁盘（此时会占用磁盘的 IOPS）

redo log 的写入机制：

事务在执行过程中，生成的 redo log 首先会写到 redo log buffer

redo log 会在一些特定条件下写入日志文件

write 到磁盘（存储在 Page Cache 中），此时没有实际调用 fsync 写入磁盘

持久化到磁盘，调用了 fsync

问：mysql 的 MRR 是什么？

MRR，全称「Multi-Range Read Optimization」。

官方说法：MRR 通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。

其实 MRR 是基于索引的查询做的一个优化，对于 innodb，则会按照聚簇索引键值排好序，在内存上索引和磁盘上的索引存储也是有序的，通过顺序的读取聚簇索引。索引本身就是为了减少磁盘 IO，加快查询，而 MRR，则是把索引减少磁盘 IO 的作用，MRR 在本质上是一种用空间换时间的算法。

问：讲讲 mysql 如何分库分表？

分库分表主要解决 IO 瓶颈，CPU 瓶颈。

分库分表：水平分库分表，垂直分库分表等

具体分库分表的方式经验是：日志类的拆分策略是按照日期，另外拆分策略就是 hash 法。

小伙伴们有兴趣想了解内容和更多相关学习资料的请点赞收藏+评论转发+关注我，后面会有很多干货。我有一些面试题、架构、设计类资料可以说是程序员面试必备！所有资料都整理到网盘了，需要的话欢迎下载！私信我回复【111】即可免费获取

作者：【利志分享】

出处：