九、通勤路上搞定 MySQL 面试

前言

到这里可以说是暂时和 Java 代码告一段落，接下来我们做好被面试官疯狂虐 MySQL 面试题的准备。 与之前一样，我会通过面试官常问的 MySQL 面试题为入口点，来一层层剖析 MySQL 的相关知识。

下面我们通过 4 道常问的面试题进行相关知识点的学习：

  1. 为什么 MySQL 索引用 B+Tree 作为数据结构？用红黑树不行吗？
  2. 联合索引多个字段之间顺序如何选择？
  3. Innodb 是如何在可重复读隔离级别下解决幻读？
  4. 使用explain时应注意那些字段？

1. 为什么 MySQL 索引用 B+Tree 作为数据结构？用红黑树不行吗？

有关 B+Tree\B-Tree\红黑树 的相关数据结构我会默认为读者已经了解，如果尚未了解的可以去搜索相关资料，这方面的资料是比较丰富并且详细，本文不会阐述相应知识。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。这样的话，索引查找过程中就要产生磁盘 I/O 消耗，相对于内存存取，I/O 存取的消耗要高几个数量级，因此索引的结构组织要尽量减少查找过程中磁盘 I/O 的存取次数，而决定磁盘 I/O 次数的，便是存储结构的深度，即树的深度。

首先，红黑树是平衡二叉树的优化，其本质还是二叉树，因此当节点过多的时候，红黑树的深度会很大，因此红黑树只适用于内存存储，而不适用于磁盘存储。

而 B+Tree 针对 B-Tree 进行优化，继承了 B-Tree 多路平衡（一个节点可以有多个子节点）的同时，将数据都存储到叶子节点，并通过指针相连，非叶子节点只存储索引相关的数据，这样的好处是可以减少磁盘 I/O 的消耗，同时可以很方便的实现范围查询，因此 B+Tree 在文件存储方面有着相当大的优势。

---

虽然 MySQL 中 MyISAM 和 InnoDB 两个存储引擎都使用了 B+Tree 的数据结构作为索引，但是在实现上完全不同，而我们一般通过“非聚集索引(nonclustered index)”和“聚集索引(clustered index)”来进行区分。

这里通过一个简单的例子解释下聚集索引和非聚集索引的区别。

聚集索引可以理解为电话簿，电话簿上面一般没有目录，只能自己去根据姓氏之类的标识翻查，但是只要找到符合目标的名字，后续的号码什么的都会一并找到；
而非聚集索引可以理解为字典，我们需要在目录上面寻找对应的字所在的页码，再根据这个页码寻找详细的信息。

MyISAM 的索引方式属于“非聚集索引”，该索引方式的特点便是叶子节点存放数据的地址，即索引本身不存储数据，在根据索引查询数据的时候，会通过索引寻找到数据的地址，在根据这个地址寻找最终的值。

InnoDB 的索引方式有“聚集索引”，也有“非聚集索引”。
我们先说前者，InnoDB 会为数据表默认添加一个主键索引，并且数据也会存储到这个主键索引当中，即 InnoDB 的索引文件等同于表数据文件，索引的叶子节点存储了对应主键的所有行数据。
如果 InnoDB 的表没有主键或者识别度较高的字段，InnoDB 会生成一个隐含字段作为主键。

而对于后者，其实也就是我们开发人员平时自己定义的索引，我们称其辅助索引，其叶子节点不包含行记录的全部数据，而是包含行数据的聚集索引（一般是主键），告诉InnoDB存储引擎去哪里查找具体的行数据，即聚集索引在这里承担了一个指针的职责。辅助索引与聚集索引的关系就是结构相似、独立存在，但辅助索引查找非索引数据需要依赖于聚集索引来查找。

2. 联合索引多个字段之间顺序如何选择？

这里主要是讲述在 InnoDB 引擎下创建联合索引的场景问题，联合索引即我们上面讲的辅助索引。
在将多个字段组合为一个索引的时候，我们有时候可能会忽略这些字段的前后顺序，这些前后顺序如果没有与业务进行挂钩处理，那么可能会造成这个索引的部分字段使用失效，这里我举个例子解释一下：

比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的联合索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，其中a,b,d的顺序可以任意调整。

这里面其实就是涉及到了最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，即这些符号后面的字段不会进行索引的匹配。除此之外，想要使用联合索引，必须得按照创建顺序进行使用，即创建了索引(a,b,c)，如果条件里面没有使用到 a 字段，那么后面就算使用到字段 b 和 c，那也不会走索引。

上面提到的需要最左前缀匹配的理由其实很简单，假如有索引(a,b,c)，三个字段都为数字，那么给出以下数据 “x(1,5,9)，y(2,3,6)，z(3,4,1)”，按照索引的建立顺序，先根据字段 a 进行排序，在根据字段 b 进行排序，最后才根据字段 c 排序，可以看出，数据 x 的 a 字段最小，但是它的 b/c 字段是最大的，即数据 x, y, z 中的 b 和 c 字段单独拿出来是没有顺序可言，因此我们如果没有使用最左匹配原则，mysql 也不可能通过非前缀索引字段进行排序比较，因为它们是无序的，最终导致的结果便是只能遍历整个索引数据。

因此，我们在创建联合索引的时候，需要考虑以下两点：

1. 考虑业务上会不会对相应的字段进行范围查询，有的话，则尽量将带范围查询的字段设置在最后
2. 把使用频率最多的字段往前放（如果不是用于范围查询）。

3. Innodb 是如何在可重复读隔离级别下解决幻读的？

首先，我们抛开 Innodb 引擎，先看看事务隔离级别和事务并发一致性问题的关系：

通过上面几点，可以得出的结论是：

1. 脏读是因为 select 没有隔离事务，可以通过读已提交解决，即事务提交后才能会被其他事务查询到。
2. 不可重复读是因为 update 没有隔离事务，可以通过可重复读解决，只有事务A完成了多次相同的查询，事务B才能进行与事务A查询条件符合的数据的 update 的操作。
3. 幻读是因为 insert/delete 没有隔离事务，可以通过串行化解决，即所有事务排队进行，无法并行，但是并发数大的情况下效率会极其低下。

了解完基本的信息，我们回看 MySQL 的 Innodb 引擎是如何在 **可重复读（RR）**的事务隔离级别下处理幻读。
Innodb 引擎处理幻读分为两个场景，分别是

1. 快照读(snapshot read)，例如简单的 select 操作(不包括 select … lock in share mode, select … for update)
2. 当前读(current read)，例如 update,delete,insert 和 select … lock in share mode, select … for update语句操作

在快照读的场景下，Innodb 引擎是通过数据多版本并发控制（MVCC） 来解决幻读，因此快照读读取的是记录的可见版本 (有可能是历史版本)。
首先 Innodb 引擎每创建一个事务，系统的版本号都会递增，而这个版本号都会存储到每行数据后面的两个隐藏字段，分别是创建版本号和删除版本号，这两个版本号在以下操作时会进行赋值和修改：

1. insert : 为新增的行的创建版本号设置为当前系统版本号

id	name	createVersion	deleteVersion
1	new_value	1

2. update: 新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号

id	name	createVersion	deleteVersion
1	new_value	1	2
1	update_value	2

3. delete: 将当前事务的版本号保存至行的删除版本号

id	name	createVersion	deleteVersion
1	new_value	2	3

在事务里面进行 select 操作的时候，只会查询小于进行这个事务时候的系统版本的数据，并且保存这份数据的版本号，在后续 select 中也只会根据版本号搜索这些数据，从而防止在一个事务内出现同样的 select 操作但是会有不同的结果，这样便解决了快照读场景下的幻读，同时这个 MVCC 的操作是自动操作，无需用户进行管理。而 MVCC 如何根据事务版本选择相应版本的数据，便涉及到版本链和 readView，可以通过以下链接进行简单了解：版本链和 readView

---

接下来我们来看看当前读的场景，Innodb 在当前读的操作下会进行默认的上锁操作，它是一种范围性的锁，范围大于等于行锁但小于表锁，也可以说是行锁+间隙锁的组成，主要是对数据与数据之间的间隙上锁，即对不存在的数据上锁，这种锁的存在可以防止其他事务在这个范围内进行增删操作，从而解决了幻读的问题，但是由于是上锁的缘故，在并发下性能不如上面所说的快照读场景。
举个例子，目前有一张表，分别有id为(1,3,5,7,9)的数据，如果我要执行一条范围查询语句：
select * from table where id > 5 for update;
那么 Innodb 便会在这个表id为 (5,7] 间隙中上锁，其他事务无法在这个区间内进行增删操作。有关这些锁的上锁规则，可以参考该文章的案例：间隙锁上锁案例

最后，我们总结一下 innodb 解决幻读的方法：

1. 在快照读中会通过 MVCC 和 undo log 来解决幻读
2. 在当前读中会通过间隙锁和行锁来解决幻读

4. 使用explain时应注意那些字段？

谈到 sql 查询的优化，一般都会想到使用 explain 命令，通过该命令查看 sql 的执行情况，本章节主要谈谈如何通过 explain 命令下的 type 和 extra 属性来判断 sql 执行的好与坏。
我们先来看看 type 和 extra 属性代表什么：

1. type :表示 Mysql 的查询方式，性能从优到差依次是：
   system > const > eq_ref > ref > range > index > all

type	出现条件
const	只需要匹配一次即可获取对应的数据，一般出现在只通过主键索引或者只通过唯一索引进行搜索数据才会出现。
eq_ref	可以理解为多表查询情况下的 const，在多表查询的时候，通过被驱动表的唯一索引或者主键索引进行等值查询便会出现 eq_ref
ref	使用了唯一索引的部分前缀字段或者普通索引的搜索时候会出现，即使用了索引但是得需要匹配多次才能得出最终查询结果，因此结果不一定唯一
range	基于唯一索引或主键索引进行范围查询，mysql 只比较一次即可获取范围起始点和结束点，然后在进行范围扫描查询，这里的范围条件包括 in(), between,>,<,>=等
index	扫描某个索引整体数据，一般出现在使用了唯一索引的非前缀字段，例如有索引index(a,b,c)，sql 语句中的查询条件只出现了 b 和 c 字段，这个时候mysql 也只能遍历该索引的整体数据进行一个个匹配并获取数据，和 range 比较就是 index 无法通过查询条件直接定位范围起始点和结束点
all	遍历整个表数据，没有使用索引或者索引失效便会出现这个情况

可以看出，除了 all 以外的 type 类型都是通过搜索索引进行数据的查找，但是即便如此，也有巨大的性能差距，如果我们看到一张数据量较大的表出现了 all 或 index 的情况，便要考虑是否需要根据搜索条件建立相应的索引或者搜索条件是否没有遵循最左匹配原则。如果是一个复杂的多表连接查询，则判断是否为小表驱动大表，排序的字段是否是使用的驱动表的字段。

2. extra :

extra	出现条件
Using index	1. 查询列被索引覆盖 2. where筛选列是索引并属于索引前缀字段且是等值匹配查询 满足上面两个条件意味着通过索引超找就能直接找到符合条件的数据，并且无须回表
NULL	1.查询列存在未被索引覆盖 2.where筛选列属于索引前缀字段并采用等值查询 满足上面两个条件意味着通过索引查找并且通过“回表”来找到未被索引覆盖的字段
Using where; Using index	1. 查询的列被索引覆盖 2. where 条件是索引并属于索引前缀字段且采用了范围查询 3. where 条件是索引但不属于索引前缀字段 满足上面 1,2 或 1,3 条件意味着需要遍历整个索引并根据 where 条件进行过滤
Using where	当 where 后面没有使用索引或索引失效（mysql觉得没必要走索引）的时候，会走全表扫描，这时候便会出现 using where
Using filesort	1. order by 或 group by 后面的字段不是索引字段或者不属于索引前缀字段 2. order by 或 group by 后面的字段不是驱动表的字段 3. order by 后面的字段是索引并且属于索引前缀字段，但是部分按 asc 排序部分按 desc 排序 只要满足上面任意一个条件，那么便会出现 Using filesort ，导致这个的原因只有一个，那就是无法使用索引来直接排序，要通过内存或者磁盘进行外部排序，因此性能很差。
Using temporary; Using filesort	1. 连表查询 2. group by 或 order by 后面的字段不完全属于驱动表的索引 满足上面两个条件便会出现使用临时表且外部排序的情况，出现的原理很简单，因为 mysql 需要将结果集存放到临时表才能进行非驱动表索引字段的排序，因此也非常消耗性能

可以看出，这里面如果出现了 Using filesort 或 Using temporary;Using filesort 会导致性能的消耗。
首先是 Using filesort ，解决办法有：
1、使用mysql索引，将待排序的内容放到索引中，直接利用索引的排序。
2、如果对应字段区分度低，则去掉 sql 的排序，在应用程序中进行排序。
针对 Using temporary;Using filesort ，解决办法有：

1. 连表查询中没有与驱动表直接关联的表可以通过子查询将非直接关联的表的数据筛选出来，或将非直接关联根据业务转换为直接关联
2. 根据业务情况冗余非驱动表的排序字段
3. 去掉 sql 的排序，在应用程序中进行排序

---

总结

在该篇章中，我主要介绍了 MySQL 的一些常见面试题，并以总结的形式开展讲解，希望给读者提供相关的学习入口，如果只是单纯的刷面试题，这些问题对应的内容应该足以应付。

接下来，我们回到上面的四个问题

  1. 为什么 MySQL 索引用 B+Tree 作为数据结构？用红黑树不行吗？
  2. 联合索引多个字段之间顺序如何选择？
  3. Innodb 是如何在可重复读隔离级别下解决幻读？
  4. 使用explain时应注意那些字段？

如果你们可以很流畅的回答这些问题，那么恭喜你，该章节的内容已经全部掌握，如果不行，希望可以回到对应问题讲解的地方，或者对某个不了解的点进行额外的知识搜索，尽量用自己组织的语言回答这些问题。