MySQL在索引执行过程中的原则与优化机制

本文主要介绍MySQL在执行查询语句使用索引时的原子以及优化措施，如MRR、索引跳跃扫描等等

最左匹配原则

对于联合索引，MySQL会一直向右匹配直到遇到范围查询（>,<）就停止匹配，也就是范围查询字段后面的字段无法使用到联合索引，但是对于>=、<=、between、like前缀匹配的范围查询不会停止匹配，会使用到范围查询后面的字段

原因

索引树是按照索引的顺序从左到右建立的，先根据第一个字段进行排序，在第一个字段相同的情况下根据第二个字段进行排序，以此类推

除了第一个字段，其他字段都是全局无序，局部有序的

所以在符合范围查询的字段后面的字段是无序的，无法使用到索引

示例

建立一个表，表中对v1、v2建立联合索引（ix_v1_v2）

CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `v1` int DEFAULT NULL,
  `v2` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_v1_v2` (`v1`,`v2`)
) ;

EXPLAIN SELECT * FROM t1 WHERE v1 = 10 and v2 = 10;

等值查询能够使用到联合索引中的v1和v2

EXPLAIN SELECT * FROM t1 WHERE v1 > 10 and v2 = 10;

在符合v1 > 10的联合索引中，v2的值是无序的，因此联合索引中只能使用到v1，无法使用到v2

EXPLAIN SELECT * FROM t1 WHERE v1 >= 10 and v2 = 10;

在v1=10的联合索引中，v2是有序的，因此在联合索引中可以使用到v2字段

EXPLAIN SELECT * FROM t1 WHERE v1 BETWEEN 10 AND 20 and v2 = 10;

MySQL的between是闭区间也就是相当于v1 >= 10 and v1 <= 20，因此在v1=10和v1=20的时候，v2是有序的，因此在联合索引中可以使用到v2字段

EXPLAIN SELECT * FROM t1 WHERE v1 like ‘1%’ and v2 = 10;

在v1=1的时候，v2也是有序的，理由同上

索引跳跃扫描ISS

MySQL8.0之后新增的，当联合索引的某一个字段的唯一值比较少时，即使查询条件中没有使用到该字段，查询的时候也可以使用到联合索引中该字段后面的字段

适用场景

联合索引中前缀列的基数远小于后缀列的基数
查询条件中只涉及到联合索引的后缀列

失效场景

查询条件中包含非索引字段
SQL语句中带有group by或distinct
只支持单表查询，多表关联会失效

示例

CREATE TABLE `t2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `v1` int DEFAULT NULL,
  `v2` int DEFAULT NULL,
  `v3` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_v1_v2` (`v1`,`v2`)
);

CREATE DEFINER=`root`@`%` PROCEDURE `t2_insert_1k`()
BEGIN
	DECLARE i INT;
	SET i = 0;
	WHILE i < 500 DO
		INSERT INTO t2(v1,v2,v3) VALUES(1,i,i);
		SET i = i + 1;
	END WHILE;
	WHILE i < 1000 DO
		INSERT INTO t2(v1,v2,v3) VALUES(2,i,i);
		SET i = i + 1;
	END WHILE;
END

EXPLAIN SELECT v2 FROM t2 WHERE v2 = 10;

适用到了索引跳跃扫描功能

EXPLAIN SELECT * FROM t2 WHERE v2 = 10;

当查询字段中包含非索引的字段，就无法适用到索引跳跃扫描，这里为全表查询

EXPLAIN SELECT DISTINCT v1 FROM t2 WHERE v2 = 10;

即使适用到了联合索引，但是是扫描整棵联合索引树，没有使用到索引跳跃扫描

索引下推ICP

MySQL在存储引擎层进行索引遍历过程中对索引中包含的字段先做判断，直接过滤掉不满足条件的记录再返回给server层，减少回表的次数

示例

对于联合索引（a, b），在执行 select * from table where a > 1 and b = 2 语句的时候，只有 a 字段能用到索引，在联合索引的索引树中找到a>1的所有记录的主键值，之后对于b是否等于2的判断，是在联合索引中判断还是在主键索引中判断

没有使用ICP：需要对获取到的每个主键值到聚簇索引中得到所有的数据行并交给服务层判断
使用ICP：在获取每个主键值的同时会在联合索引树中判断是否符合要求，如果符合要求才取出对应的主键值到聚簇索引中获取数据返回给服务层

索引覆盖

SQL中查询的所有字段在索引B+树的叶子节点上都可以得到，不需要进行回表查询

MRR（Multi-Range Read）

通过将随机磁盘读转换为顺序磁盘读，提高查询性能

在业务中我们应该尽量通过索引覆盖减少回表操作降低IO次数，但是很多时候二级索引树没办法满足我们的需求，还是需要进行回表才能查询到数据。这样子会增加磁盘IO的次数，并且是离散IO

示例

SELECT * FROM zz_student_score WHERE score BETWEEN 0 AND 59;

在没有MRR之前的执行流程：

先在成绩字段的索引上找到0分的节点，然后拿着ID去回表得到成绩零分的学生信息。
再次回到成绩索引，继续找到所有1分的节点，继续回表得到1分的学生信息。
再次回到成绩索引，继续找到所有2分的节点…
周而复始，不断重复这个过程，直到将0~59分的所有学生信息全部拿到为止

此时假设此时成绩0~5分的表数据，位于磁盘空间的page_01页上，而成绩为5~10分的数据，位于磁盘空间的page_02页上，成绩为10~15分的数据，又位于磁盘空间的page_01页上。此时回表查询时就会导致在page_01、page_02两页空间上来回切换，但0~5、10~15分的数据完全可以合并，然后读一次page_01就可以了，既能减少IO次数，同时还避免了离散IO

MRR就是用来解决这个问题：

MRR机制中，对于辅助索引中查询出的ID，会将其放到缓冲区的read_rnd_buffer中，然后等全部的索引检索工作完成后，或者缓冲区中的数据达到read_rnd_buffer_size大小时，此时MySQL会对缓冲区中的数据排序，从而得到一个有序的ID集合：rest_sort，最终再根据顺序IO去聚簇/主键索引中回表查询数据

索引合并index merge

对多个索引分别进行扫描，然后将各种的结果进行合并

MySQL5.0之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。但是从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描

合并算法

intersection

将多个索引扫描得到的结果进行交集运算。一般出现在查询条件中使用AND

条件：

如果是二级索引，则必须是等值查询。如果二级索引是复合索引，则复合索引的每一列都必须覆盖到，不能只是其中的某几列
主键索引可以是范围查询

union

将多个索引扫描得到的结果进行并集运算。一般出现在查询条件中使用OR

条件：

如果是二级索引，则必须是等值查询。如果二级索引是复合索引，则复合索引的每一列都必须覆盖到，不能只是其中的某几列
主键索引可以是范围查询

sort_union

在union不适用的情况下会使用

二级索引也可以按照范围匹配
复合索引也不用覆盖所有列

原因

为了 intersect 和 union 操作方便，在各个单独的索引扫描的时候，都是要获取到有序的主键值的合集，各个索引都获取到有序的主键，然后求交集或者并集就会比较方便

因此union和intersect中：

二级索引必须等值匹配，等值匹配意味着最终拿到的 B+Tree 的叶子上的主键值就是唯一的；二级索引如果可以按照范围查找，那么最终从二级索引的 B+Tree 的叶子结点上拿到的主键值就不是有序的了
复合索引如果没有覆盖到所有列，意味着最终拿到的主键值也是无序

sort_union允许二级索引按照范围匹配与不需要覆盖所有列是因为会对先拿到的主键值进行排序，之后才会去求交集或并集。相比于前两种合并方式性能会比较低

Index Merge已知的缺陷

如果在where语句中存在多层嵌套的AND/OR，MySQL可能不会选择最优的方案，可以尝试拆分where子句的条件进行转换

(x and y) or z ==> (x or z) and (y or z)

(x or y) and z ==> (x and z) or (y and z)

index merge不能用于全文索引