MySQL，本章包含面试八股文中有关MySQL的常见问题。关于MySQL考察最多的就是MySQL的优化问题，例如定位慢查询，索引，SQL优化等方面的问题。

慢查询

如何定位慢查询

慢查询是SQL优化最常见的一个问题，慢查询的原因有很多，例如聚合查询，多表查询，查询数据量过大，分页深度过大等，定位慢查询通常有两种方法：使用开源的调试或运维工具监控，例如Arthas， Prometheus等工具，提供了命令行或GUI来监控应用运行情况，并展示异常接口等，可以帮助我们定位慢查询；另一种定位慢查询的方式就是通过MySQL自带的慢查询日志来定位，这也是我们重点要讲解的内容。

通过slow_query_log参数可以开启慢查询日志记录，通过long_query_time参数来控制执行时间的阈值，超过这个阈值的查询就会被记录到慢查询日志中。

但是一般在生产环境下不开启慢查询日志，因为这会损耗MySQL的部分性能。

如何分析慢查询

慢查询的原因中聚合查询可以通过新增临时表解决，多表查询可以通过优化SQL语句的结构来解决，表数据量过大可以通过建立索引来解决。

这三种原因都可以通过分析SQL执行计划来解决，使用MySQL提供的explain语句可以得到SQL语句的执行计划。

例如执行explain select * from user where id = 1; 语句可能会得到如下输出

字段解析：

• select_type：查询类型，常见的值包括SIMPLE、PRIMARY、UNION等。

• table：当先操作的是哪张表，如果是多表联查的话会有多张表。

• type：这条SQl连接的类型，分析性能的核心指标。性能从好到差为NULL、system、const、eq_ref、ref、range、index、all。

  • NULL：不需要访问表，在优化阶段就能计算出结果。

  • system：查询系统中的表，一般不会出现在我们写的SQL语句中。

  • const：根据主键查询，一般而言性能最好

  • eq_ref：主键查询或者根据唯一索引查询。

  • ref：使用了索引查询，但不是唯一索引查询。

  • range：SQL执行走的是索引，但是是范围查询。

  • index：走的是索引查询，但是遍历的是整个索引树，效率不高。

  • all：全表扫描，效率最低。

• possible_keys：理论上可能用到的索引。

• key：实际使用到的索引。

• key_len：索引长度，一般以字节为单位。

• Extra：额外执行信息，是优化重点。

  • Using where / Using Index：查找使用了索引，需要的数据列在索引中都能找到，不需要回表查询数据。

  • Using index condition：查找使用了索引，但需要回表查询数据。

使用explain分析SQL语句的技巧：

1. 可以通过key可key_len字段检查是否命中了索引（索引本身是否存在失效的情况）

2. 通过type字段查看SQL语句是否有进一步优化空间，是否存在全索引扫描或则全表扫描。

3. 通过extra判断是否出现了回表查询的情况，如果出现了，可以尝试添加索引或者修改返回字段来解决。

索引

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+树），这些数据结构以某种方式引用数据，这样就可以在这些数据结构之上实现高级查找算法，这种数据结构就是索引。

MySQL的InnoDB引擎默认采用B+树的数据结构作为索引。

B+树在B树的基础上进行了优化，B树可以认为是二叉搜索树的升级版，二叉搜索树每个节点只能有两个子节点，B树的每个节点可能有多个子节点，而B+树和B树的主要区别在于B+树的非叶子节点是不存储数据的，只存储导航数据（指针），即应该向哪个方向搜索，因此相比于B树，B+树在查找效率上比B树更加稳定，因为每次都要搜索到叶子节点才可以得到数据，同时B+树的叶子节点还使用了双向链表连接，形成了一个有序链表，便于区间查询和扫库。同时相比于二叉搜索树。B+树的阶数更大，高度更短，查询效率更高。下面两张图分别是B树和B+树的实例。

B树示例图，这是一棵最大度数为5的B-tree，即每个节点最多存储4个key，因为每两个key之间有一个指针指向子节点。

B+树示例图，B+树的所有叶子节点形成了一个双向有序链表，便于区间查询和扫库。

覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列全部在索引中能够找到，此时便不需要回表查询，可以直接返回数据。

拓展：MySQL超大分页如何处理？

对于两个分页查询：select * from user limit 0, 10; 和 select * from user limit 9000000, 10;我们会看到这两条查询花费的时间是截然不同的，第一个查询可能很快就会执行完毕，第二个查询可能花费十多秒才能执行完成。

这是因为第一个查询只需要查询到前十条记录然后直接返回就可以了，但是第二个查询需要查询到前9000010条记录，并对这些数据进行排序，然后丢弃前9000000条记录，然后返回剩下十条数据，因此对于这种超大分页，最好的解决办法就是建立覆盖索引。当我们建立覆盖索引之后，优化器可以直接游标定位。执行流程就变为了：顺着索引有序遍历，遍历到10第一条满足条件的数据，向后遍历十条，然后直接返回即可，不会把所有满足条件的数据全部加载出来。

索引使用的原则

1. 针对于数据量较大、查询频繁的表创建索引（前提）。

2. 针对常作为查询条件、排序规则、分组的字段建立索引。

3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。例如假设一个列大部分的数据都相同，那么为这个列创建索引显然是不划算的选择。

4. 如果是字符串类型的字段，并且字段的长度较长，可以针对字段的特点建立前缀索引。

5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6. 要控制索引数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率，因为在增删改数据的时候都要修改涉及到的所有索引结构。

7. 如果索引列不能存储NULL值，在创建索引的时候用NOT NULL 约束它。当优化器知道当前列是否包含NULL值时，它可以更好地确定哪个索引对当前查询更加有效。

索引失效的情况

索引失效指的是我们已经为字段创建了索引，但是由于SQL语句编写不当，导致在查询时没有使用到索引，从而导致走了全索引树扫描或全表扫描导致查询效率降低。

1. 违反最左前缀法则

最左前缀法则：查询如果使用了索引，必须从索引的最左列开始匹配，中间不可以跳过，否则右侧的索引会失效。

假设我们已经为tb_seller表的name、status、address字段创建了一个联合索引，分别执行如图的三条explain语句

我们重点关注输出中的key和key_len字段，第一条输出中可以看到我们使用了创建的索引，长度为303，因为我们只根据name查询，所以只使用到了索引中的name列；第二条查询也使用到了索引，len是309，说明使用到了name和status；第三个输出同理，说明我们使用到了全部的三个索引列。

以上是索引正常生效的情况，也符合最左前缀法则，因为我们的查询条件是按照name、status、address的顺序使用的，没有出现跳过status只使用name和address的情况

但是如果执行下面两条语句

可以看到这两条SQL语句虽然查询的是索引列，但是explain的输出中key部分为NULL，这说明此时索引已经失效，原因就在于违反了最左前缀法则，第一条语句中只根据status和address查询，跳过了name，索引会失效；第二条也是，只使用status查询，跳过了name列；

如果符合最左前缀法则，但是跳过了某一列，那么只有跳过的列左边的索引列会生效

此时可以看到我们根据name和address查询，跳过了status，索引生效了，但是key_len只有303，所以只有name列的索引生效了。

2. 范围查询右边的列索引会失效。

图中第一条SQL执行计划中可以看到索引的长度是612，说明三个索引列全部被使用到了；但是下面的SQL语句的key_len长度是309，说明只使用到了name和status列，没有使用到address列，因为status是范围查询，其右侧的列不能使用索引。

3. 在索引列上进行运算也会使索引失效。

在这条SQL语句中，name列上进行了substring操作，因此在执行计划中就没有使用索引了，

4. 字符串不加单引号也会导致索引失效

   

这个是比较容易理解的，因为status本身是字符串类型，如果查询条件是status = 1会触发自动类型转换，导致索引失效。

5. 以%开头的模糊查询会导致索引失效，如果仅仅是字符串末尾模糊匹配，索引不会失效，头部模糊匹配会导致索引失效

   

简单来说就是只要开头没有%模糊匹配，索引就不会失效。

SQL优化

SQL优化包含很多部分，例如表的设计优化、索引优化、SQL语句优化、主从复制、读写分离、分库分表等优化方法。其中索引优化可以参考前面的索引设计原则和索引失效的情况，这里先介绍表结构优化和SQL语句优化和读写分离，分库分表由于内容较多，会在后面单开一节。

1. 表的设计优化可以参考阿里的Java开发手册，里面详细罗列了很多开发规范，包括数据库开发规范，例如设计表时要根据实际数值长度从int或bigint、tinyint中选择合适的类型。

2. SQL语句优化

   • select语句务必指明字段，避免使用select * ……，因为指明字段有可能覆盖索引，提高查询效率，使用select * 很可能会造成回表查询，降低查询性能，同时只select自己需要的字段还可以降低数据IO压力，以及传输的数据量等，总之就是只查询需要的字段

   • SQL语句编写要避免会引起索引失效的情况。

   • 尽量使用union all代替union，因为union会多一次过滤，降低查询效率。

   • 避免在where字句中对查询进行操作，因为这会导致索引失效。

   • join优化：能用内连接优先使用内连接，如果必须使用外连接优先以小表为驱动，因为驱动表的数据量越小，连接的次数越少，外连接的主要时间花费在表连接上而非遍历。而内连接会对表进行优化，优先把小表放在外边，把大表放在里面，而外连接不会自动调整顺序。

3. 主从复制、读写分离

如果数据库的读操作比较多，为了避免写操作对性能的影响，可以采用读写分离的架构，读写分离解决的是数据库的写入对查询效率的影响。和redis类似，在主从集群中，主节点负责写入，从节点负责读数据，主节点写入数据之后会同步到从节点。

事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的数据库操作作为一个整体一起提交给系统或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务的特性

事务有四大特性，简称为ACID

• 原子性：事务是不可分割的最小操作单元，其包含的操作要么全部成功，要么全部失败。

• 一致性：事务完成时，必须使所有的数据都保持一致状态（例如在转账时，一个人少了1000元，就必须有另一个人多1000元）

• 隔离性：一个事务的运行不可以被其他事务干扰。

• 持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题与隔离级别

并发事务所带来的问题主要有三个：脏读、不可重复读、幻读。

• 脏读：一个事务读到另外一个事务还没有提交的数据。

• 不可重复读：一个事务先后读取同一条数据，但两次读到的数据不同。

• 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这条数据已经存在，好像出现了一个“幻影”。

解决这些并发事务问题的办法就是隔离事务，MySQL提供了四种不同的事务隔离级别来供开发者使用

表中√表示当前事务隔离级别下存在这个并发事务问题，×表示当前这个事务隔离级别不会存在这个并发事务问题。

虽然更加严格额事务隔离级别解决了更多的并发事务问题，但是代价就是性能会降低。其中，read uncommitted三种并发事务问题全部存在，在实际开发中一般不会选用，serializable虽然不会存在并发事务问题，但是性能很差，所以一般也不会选用这种事务隔离级别。所以我们一般使用的都是MySQL的默认事务隔离级别：repeatable read，可重复读。这个事务隔离级别是安全性和性能的折中选择，兼顾了数据安全性和数据库性能。

undo log 和 redo log的区别

前提知识补充：

• 缓冲池（buffer pool）：主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池中没有数据，则从磁盘加载并缓存），以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

• 数据页（page）：数据页是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认是16KB.页中存储是行数据。

redo log：重做日志，记录的是事务提交时数据页的物理修改，用来实现事务的持久性。该文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘中发生错误时，进行数据恢复使用。

undo log：回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和MVCC（多版本并发控制）。undo log和redo log记录物理日志不一样，undo log记录的是逻辑日志。

例如当执行一条delete语句时，undo log中会记录一条对应的insert记录，反之亦然。当update一条记录的时候，undo log会记录一条相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应内容并进行回滚。

因此undo log可以实现事务的原子性和一致性

因此二者的区别可以简述为：

• redo log记录的是数据页的物理变化，服务宕机可以用来恢复数据。

• undo log记录的是逻辑日志，当事务回滚时，可以通过逆操作来恢复到数据更新前的版本。

• redo log保证数据的持久性，undo log保证数据的一致性和原子性。

MVCC（多版本并发控制）

undo log 和 redo log保证了事务的原子性、一致性、持久性，那么隔离性如何保证呢？

在MySQL中，事务的隔离性主要依靠锁和MVCC保证。

• 锁：排他锁，如果一个事务获取了一行的排他锁，那么其他事务将无法获取该行的任何锁，包括排他锁和共享锁。

• MVCC：多版本并发控制。

MVCC：全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突。

在讲解MVCC的原理之前，先明白几个概念：当前读和快照读。

当前读和快照读

当前读

在MySQL中，当前读是一种读取数据的操作方式，它可以直接读取最新的数据版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。MySQL提供了实现当前读的机制：

• 默认隔离级别（可重复读，RR）下，MySQL使用一致性读来实现当前读。

• 在事务开始时，MySQL会创建一个一致性视图，该视图反应了事务开始时刻数据库的快照。

• 在事务执行期间，无论其他事务对数据进行了何种修改，事务始终使用一致性视图来读取数据。

• 这样可以保证在同一个事务内多次查询返回的结果是一致的，从而实现了当前读，并且避免了不可重复读问题。

update、delete、insert、select……for update等都是当前读。当前读实际上是一种加锁的操作，是悲观锁的实现。

快照读

快照读是在读取数据时读取一个一致性视图中的数据，MySQL中使用MVCC机制来支持快照读。

具体而言，每个事务在开始时会创建一个一致性视图，该视图反应了事务开始时候的数据库的快照。这个一致性视图会记录当前事务开始时已经提交的数据版本。

当执行查询操作时，MySQL会根据事务的一致性视图来决定可见的数据版本，只有那些在事务开始之前已经提交了的数据版本才是可见的，未提交的数据或在事务开始后修改的数据则对当前事务不可见。

快照读可能读到的并不一定是数据的最新版本，有可能是之前的历史版本。

注意：快照读的前提是事务的隔离级别不是串行化，在Serializable隔离级别下，事务之前完全串行执行，快照读会退化为当前读。

MVCC原理解析

1. 隐式字段

MySQL中的行数据，除了我们能看到的字段之外，每张表还包含了一些隐藏字段，它们在MySQL内部使用。默认情况下不会展示给用户。

• DB_ROW_ID：隐含的自增id（隐藏主键），用于唯一标识表中的每一行数据，如果数据库没有主键，InnoDB会自动以该行作为主键并建立一个聚簇索引，如果表在创建时已经指明了主键，那么该行将不会被创建。

• DB_TRX_ID：最近修改事务id，记录插入这条记录或最后一次修改该记录的事务id。

• DB_ROLL_PTR：回滚指针，执行这条记录的上一个版本，配合undo log，指向该行数据的上一个版本。

2. 版本链

在MVCC中，对于每次更新操作，旧值会被保存到一条undo日志中，即使它是该记录的就版本。随着更新次数的增加，所有的版本都会通过DB_ROLL_PTR字段连接形成一个链表，称之为版本练，版本链的头节点代表当前行的最新版本，每个版本还包含生成该版本的事务id。

下图是一个MVCC版本链的示例，左侧是事务时序图，右侧是版本链。

解析：假定事务2、3、4、5的id分别为1、2、3、4。事务2开启事务，修改age为30，此时产生一条历史版本0x00001，该版本记录了修改之前的值（age = 30），操作事务id为1，没有前面的历史版本；随后事务3修改name，产生了第二个历史版本0x00002，其name为A30（旧值），同时DB_TRX_ID为事务3的id（2），其DB_ROLL_PTR为0x00001；同理，事务4也修改了age字段，产生了0x00003的历史版本，其结构如图。

3. Read View

Read View（视图）是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃事务（未提交）的id。是用来判断版本链中的哪个版本对当前事务是可见的。

简单来说Read View就是事务进行快照读操作时候生成的读视图，在该事务执行快照读操作的时候，会生成数据库系统的一个快照，记录并维护当前系统活跃的事务id。

需要说明的是：Read View只针对RC和RR隔离级别。

• Read Uncommitted（RU）： 在 RU 隔离级别下，事务可以读取其他事务尚未提交的数据，即脏读。这意味着不需要通过 Read View 来限制访问范围，事务可以自由地读取其他事务的未提交数据。由于没有对可见性进行严格控制，因此不需要创建或使用 Read View。

• Serializable（串行化）： 在 Serializable 隔离级别下，事务具有最高的隔离性，确保每次读取都能看到一致的快照。为了实现这种隔离级别，MySQL使用锁机制来保证事务之间的串行执行。由于事务按顺序执行，并且不允许并发操作，所以不需要使用 Read View 进行可见性判断。

Read View中包含了四个字段：

• m_ids：当前活跃的事务id集合。

• min_trx_id：最小活跃事务id。

• max_trx_id：预分配事务id，也就是下一个事务要给其分配的id，值为当前事务id的最大值 + 1。

• creator_trx_id：Read View创建者的事务id。

Read View根据这四个字段来控制当前快照读能够读取到哪些数据，具体控制规则如下：

$\text{版本链数据访问规则} \begin{cases} \begin{aligned} \text{①}.&\ \text{trx\_id} = \text{creator\_trx\_id}, \quad &&\text{可访问} \Rightarrow \text{当前事务修改的数据} \\ \text{②}.&\ \text{trx\_id} < \text{min\_trx\_id}, \quad &&\text{可访问} \Rightarrow \text{事务已提交} \\ \text{③}.&\ \text{trx\_id} > \text{max\_trx\_id}, \quad &&\text{不可访问} \Rightarrow \text{事务在视图创建后开启} \\ \text{④}.&\ \text{min\_trx\_id} \le \text{trx\_id} \le \text{max\_trx\_id}, \quad &&\text{不在m\_ids则可访问} \Rightarrow \text{数据已提交} \end{aligned} \end{cases}$

对于Read View的生成时机，RC和RR是有区别的：RC（读已提交）在每次快照读都生成一个Read View，而RR（可重复读）只会在第一次快照读时生成Read View，后续快照读会复用这个Read View。

总结：InnoDB事务的隔离性主要依赖于MVCC多版本并发控制和排他锁机制共同保障，MVCC面向快照读实现无锁并发查询；锁机制保障当前读的一致性。MVCC通过trx_id和roll_ptr这两个隐藏字段来记录行数据的历史版本和信息，通过undo log实现事务回滚和串联不同版本形成版本链，Read View定义了可见性判断规则，快照读时从最新版本沿着版本链向前查找，返回第一个符合可见规则的记录，解决了一个查询事务应该选择哪个历史版本的问题，三者配合共同实现了MVCC。RC隔离级别在每次快照读时都创建Read View，RR隔离级别仅在事务第一次快照读时创建Read view，后续复用。

主从同步

现代应用的数据库大多采用主从集群，MySQL的主从集群原理和redis类似，主节点负责写入数据，从节点负责读数据，当主节点写入数据之后需要同步到从节点，本节简单讲解一下主从复制的原理和过程。

MySQL主从复制的核心是二进制日志。

二进制日志（binlog）记录了所有DDL（数据定义语言）和DML（数据操纵语言），但不包括数据查询语句（select、show）

主从复制过程：

1. master主库在提交事务时，会将数据变动记录到自身的binlog中。

2. slave从库中有一个IOthread线程专门监听和读取master的binlog，写入到自身的中继日志relay log中。

3. slave的SQLthread线程通过重做relay log中的事件来实现主从同步。

主从复制图示

分库分表

主从同步只解决了读写压力的问题，但是并没有解决海量数据存储的问题，因为主节点和从节点存储的数据是相同的，当业务数据量逐渐增大，优化已经解决不了性能问题了，因为数据基数太大了，产生的IO瓶颈和CPU瓶颈已经无法通过软件层面的优化解决了，此时就需要分库分表来分担单库存储的压力了。

分库分表就是按照一定的规则将一个数据库或一张表中的数据拆分成多个库和表，分担压力。分库分表的策略分为四种：

• 垂直分库：以表为单位，根据业务将不同表拆分到不同库中。例如用户有关的表放在一个库中，商品有关的表放在另一个库中。在微服务架构中，每个服务可以使用一个独立的库。提高磁盘IO和网络连接数。

• 垂直分表：以字段为单位，根据字段属性不同将字段拆分到不同的表中。常用的拆分规则是把不常用的字段单独放在一张表中，把大字段单独放到一张表中，例如商品详细信息，这种拆分可以使冷热数据分离，减少IO过度争抢，多表互不影响。

• 水平分库：将一个库的数据拆分到多个库中，每个库的结构完全相同，所有库的数据加起来等于原来i的库数据。解决海量数据存储和高并发的问题

• 水平分表：和水平分库类似，将一个表中的数据拆分到多个表中。多个表可以在同一个库内。解决单表存储和性能问题。

水平分库分表带来了一个问题就是如何判断哪条数据应该存放到哪个节点，可以根据一些路由规则来判断数据的节点归属，例如根据数据的hash值取模，或者按主键范围路由等。

新的问题

分库分表必然会带来一些新的问题

• 分布式事务：如果一个事务涉及到多个库或多个节点，如何保证这些操作的原子性。

• 跨节点关联查询：如何在不同节点之间进行关联查询。

• 跨节点分页、排序函数：和跨节点关联查询类似。

• 主键避重：每个节点都有自己的主键自增计数器，如何保证这些节点的主键不会重复。

好在MySQL提供了很多中间件为我们解决这些问题，例如mycat、sharding-sphere等分库分表中间件，在开发过程中使用这些中间件可以帮助我们降低开发难度。