type
status
date
slug
summary
tags
category
icon
password
在前两篇文章中，我们讨论了关于索引的基础和失效问题。
我们也随之对索引有了较为深刻的了解，但索引是否是万能的？它又有何优缺点和机制呢？

索引的优点

  1. 使用索引可以大幅度减少所需要的扫描的数据。
  1. 当 SQL 语句中使用 GROUP BY 和 ORDER BY 时，可以依靠索引来减少分组或排序的时间。
  1. 连表查询时，在主外键字段上建立索引，可以大幅加快连表速度。
  1. 可以将随机 IO 变成顺序 IO 。
由此可见，索引作为一个高效的优化手段，它所带来的好处可以说是非常明显，然而 “凡事有利必有弊”，MySQL 的索引机制也有它的弊端。

索引的缺点

  1. 对于较小的表，使用索引不如直接使用全表扫描。
  1. 写入数据时，索引的维护需要额外的时间开销，“写” 相关的操作都会有性能损耗。
  1. 区分度不大的索引，不恰当的联合索引都会导致资源浪费。
因此，为了更好的使用索引，理解它的一些重要机制可以说是必要的。

索引的重要机制

索引覆盖

在MySQL索引（一） — 理解索引中讲到二级索引时提到过索引覆盖：如果当前索引包含了所有所需要查询的列，那么此时就无需回表查找，这个情况被称为 “索引覆盖”。
例如下面这个例子：
在 user 表中，有联合索引 idx_name_age，有下面 SQL 语句
可以看出，如果使用 idx_name_age，就能够直接拿到所有需要的字段，而无需再回表去查找，这就是索引覆盖的能力，能够大大减少回表次数，优化性能。
如果查询时使用 *，基本上大部分索引都不会涵盖表中的所有列，所以基本都会触发回表操作，因此不推荐在业务中使用 * 来作为查找列，会导致索引覆盖失效。

索引下推

索引下推是 MySQL5.6 版本以后推出的一种优化机制，用于优化查找时的额外回表问题。
对于如下 user 表：
id
name
age
address
1
张三
18
浙江
2
李四
25
北京
3
王五
37
上海
4
张四
22
山东
5
赵六
23
浙江
它有联合索引 idx_name_address，执行如下 SQL：
在 5.6 版本之前，MySQL 的查询流程如下：
  1. 利用联合索引 idx_name_address，找到 “张三”、“张四” 两个索引节点。
  1. 返回索引节点存储的 id（1、4）给 Server 层，之后逐一进行回表查找。
  1. 在 Serevr 层中根据 address = '浙江' 这个条件逐条判断，最终确定 id = 1 的数据是真正的数据。
很显然，这是不够聪明的做法，毕竟联合索引中已经涵盖了 address 的数据，那么在引擎查找的时候，就应该可以筛选出 address != ‘浙江’ 的数据，从而减少回表次数。
所以在 5.6 版本之后，加入了 “索引下推” 机制，MySQL 的查询流程如下：
  1. 利用联合索引 idx_name_address，找到 “张三”、“张四” 两个索引节点。
  1. 根据 address = '浙江' 这个条件在索引节点中逐条判断，筛选出符合的节点 id = 1.
  1. 返回索引节点存储的 id（1）给 Server 层，之后进行回表查找出所需要的数据。
由此可见，索引下推能够优化查找的次数，增加查询效率。

MMR(Multi-Range Read) 机制

Multi-Range Read 简称为 MRR 机制，这也是和索引下推一同在 MySQL5.6 版本中引入的性能优化措施，那什么叫做MRR优化呢？
在大多数业务情况中，我们都会通过调整索引来尽量避免回表产生的磁盘 IO ，以免降低数据查找的性能。但大多数时候，回表是不可避免的，并且更严重的是，它有可能会产生大量的离散 IO，大大增加查找时间。
例如这句 SQL
这条语句很清晰，是在 user 表中查找年龄在 18 岁到 45 岁的用户信息，但假设目前在 age 字段上有一个普通索引 idx_age，那么这条 SQL 语句的执行流程就会变成这样：
  1. 先在 age 字段的索引上找到 18 岁的节点，然后拿着 id 回表得到 18 岁的用户信息。
  1. 再次回到 idx_age 索引，继续找到所有 19 岁的节点，继续回表得到 19 岁的用户信息。
  1. 再次回到 idx_age 索引，继续找到所有 20 岁的节点......
  1. 周而复始，不断重复这个过程，直到将 18 ~ 45 岁的所有用户信息全部拿到为止。
那此时假设此时年龄 18~28 岁的表数据，位于磁盘空间的page_01 页上，而成绩为 28~38 岁的数据，位于磁盘空间的 page_02 页上，成绩为 38~45 岁的数据，又位于磁盘空间的 page_01 页上。此时回表查询时就会导致在 page_01、page_02 两页空间上来回切换，但 18~28 和 38~45 岁的数据完全可以合并，然后读一次 page_01 就可以了，既能减少 IO 次数，同时还避免了离散 IO 。
而 MRR 机制就主要是解决这个问题的，针对于辅助索引的回表查询，减少离散 IO，并且将随机 IO 转换为顺序 IO，从而提高查询效率。
而 MRR 是如何做到这一点的呢？
在 MRR 机制中，对于二级索引中查询出的主键 id，会将其放到缓冲区的 read_rnd_buffer 中，等到所有的索引检查工作完成后，或者缓冲区中的数据打到 read_rnd_buffer_size 的大小时，MySQL 会对缓冲区中的数据进行排序，从而得到一个有序的 id 集合 —— rest_sort，最终在根据顺序 IO 去主键索引中回表查询数据。

Index Skip Scan 索引跳跃式扫描

在讲联合索引时，提到过最左前缀匹配原则，也就是 SQL 的查询条件中必须要包含联合索引的第一个字段，这样才能命中联合索引查询。
实际上这条规则也并不是 100% 遵循的。因为在 MySQL8.x 版本中加入了一个新的优化机制，也就是索引跳跃式扫描，这种机制使得即使查询条件中，没有使用联合索引的第一个字段，也依旧可以使用联合索引，看起来就像跳过了联合索引中的第一个字段一样，这也是跳跃扫描的名称由来。
比如此时通过 (A、B、C) 三个列建立了一个联合索引，此时有如下一条 SQL：
按理来说，这条 SQL 既不符合最左前缀原则，也不具备使用索引覆盖的条件，因此绝对是不会走联合索引查询的，但思考一个问题，这条 SQL 中都已经使用了联合索引中的两个字段，结果还不能使用索引，这似乎有点亏啊对不？因此 MySQL8.x 推出了跳跃扫描机制，但跳跃扫描并不是真正的“跳过了”第一个字段，而是优化器为你重构了 SQL ，比如上述这条 SQL 则会重构成如下情况：
其实也就是 MySQL 优化器会自动对联合索引中的第一个字段的值去重，然后基于去重后的值全部拼接起来查一遍，一句话来概述就是：虽然你没用第一个字段，但我给你加上去，今天这个联合索引你就得用，不用也得给我用。
参考资料：
  • 《高性能 MySQL 第三版》
Tuwilt
Tuwilt
言辞不必有力