MySQL覆盖索引和索引跳跃扫描

最近在深入学习MySQL，在学习最左匹配原则的时候，遇到了一个有意思的事情。请听我细细道来。

我的MySQL版本为8.0.32

可以通过 show variables like 'version'; 查看使用的版本。

准备工作：

先建表，SQL语句如下：

create table joint_index_test(
        id int primary key,
        a int,
        b int,
        c int
);
alter table joint_index_test add index index_a_b_c(a,b,c);

表结构非常简单，4个字段，两个索引，主键索引id和联合索引abc。暂时不向表中添加数据。

开始测试

接下来我们进行查询操作和使用explain查看select语句的执行

1. 最左匹配原则

1.explain select * from joint_index_test where a = 3;

这条SQL语句是否走了索引大家基本上都能够分析出来，基础比较好的小伙伴甚至可以直接分析出来扫描类型是什么。执行结果如下图：

由于where后面的条件是a,遵循联合索引的最左匹配原则，会使用索引index_a_b_c,进行查询。由于我们查询的列是*,在joint_index_test可以扩展为id,a,b,c,这些列在联合索引a,b,c中都可以查询到。所以MySQL在执行的时候，会选择使用覆盖索引，不再进行回表查询。【extra列为Using index】

继续进行测试第二条SQL语句

2. 覆盖索引

2.explain select * from joint_index_test where b = 3;

根据最左匹配原则，我们可以判断出来，第二条SQL语句应该不会使用到index_a_b_c联合索引，因为联合索引是按照字段的顺序从左到右进行构建的，也就是从字段a进行从小到大的排序，只有字段a相等的时候才会使用b,c进行排序。也就是说，b、c在全局是无序的，在局部却是有序的。当我们的条件中缺失联合索引最左边的字段时，MySQL在进行查询的时候，一般情况下，是不能够使用到联合索引了。

但是也有例外，像上面的这一条SQL语句，执行的时候会利用联合索引进行全索引扫描，因为我们要查询的字段在联合索引中都可以查询到，然后将所有查询到的结果使用where条件进行筛选。

为什么会优先走联合索引？

因为二级索引树的记录东西很少，就只有「索引列+主键值」，而聚簇索引记录的东西会更多，比如聚簇索引中的叶子节点则记录了主键值、事务 id、用于事务和 MVCC 的回滚指针以及所有的剩余列。MySQL的查询是基于成本的，会优先原则成本低的查询方案。

如果我们向joint_index_test表中添加一个name字段，这时候，我们要查询的所有字段就没有办法在联合索引中全部找到了，MySQL会放弃联合索引，改走全表扫描。

3. 索引跳跃扫描

我们将name字段删除，表中还只保留 id、a、b、c 四个字段，并向表中生成数据。

我们向表中生成一千条数据，id自增，a对1到6进行枚举，b、c是int类型的随机数。

我们再次执行 explain select * from joint_index_test where b = 3;这条SQL语句，发现type列和Extra列中的内容发生了变更。type从index -> range ; Extra列从Using Index -> Using index for skip scan.

之所以发生了这样的变化，是MySQL8.0.13后对最左原则失效的情况进行了优化。如果我们的联合索引构建的B+Tree中能够找到所有查询的列且where查询条件没有遵循最左匹配原则，MySQL会通过索引跳跃扫描进行优化处理。提前说明，索引跳跃扫描并不是万能的，我们在进行SQL查询的时候还是需要尽可能地遵循最左匹配原则。

接下来，我会根据MySQL官方文档对索引跳跃扫描进行解说，感兴趣的小伙伴也可以直接点击文末链接，自行阅读。

在MySQL8.0.13版本之前,执行这一条SQL语句,会出现 Using where,Using Index 使用索引扫描所有的数据,之后再利用条件进行过滤,其执行type为index对全索引进行扫描，性能仅次于ALL;

从MySQL 8.0.13版本开始，mysql支持多范围扫描;查询的条件的每个不同前缀值执行子范围扫描。例如会对 select * from joint_index_test where b = 3 这条SQL语句通过 distinct a 拆分成六条SQL语句，分别为：

explain select * from joint_index_test where a = 1 and b = 3;
explain select * from joint_index_test where a = 2 and b = 3；
explain select * from joint_index_test where a = 3 and b = 3;
explain select * from joint_index_test where a = 4 and b = 3;
explain select * from joint_index_test where a = 5 and b = 3;
explain select * from joint_index_test where a = 6 and b = 3;

让拆分后的语句能够遵循联合索引的最左匹配原则进行范围查询，之后对所有查询到的值进行合并，并作为整体返回。值得一提的是，索引跳跃扫描，并非跳过索引,而是在缺失的前缀索引的不同值之间进行跳跃；使用这种策略减少了访问的行数，因为MySQL直接跳过不符合的构造范围的行。还是那一句话，联合索引不是万能的，之中优化是基于以下条件的：