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直接跳过不符合的构造范围的行。还是那一句话，联合索引不是万能的，之中优化是基于以下条件的：

1. 只适用于单表查询;
2. 查询语句中不能使用GROUP BY或DISTINCT;
3. 只能对联合索引中构建的B+数包含的列进行查询;
4. 缺少的前缀必须是常数，数字类型的字段
5. 查询条件必须适用连词进行连接，比如使用AND或者OR

以上还有一些条件，笔者暂时还没有看懂，值得一提的是，在满足上面的所有条件的情况下，索引跳跃扫描并不是一定发生的，因为对缺失的前缀进行组合是需要成本的。mysql的查询永远会选择成本最低的方案，而索引跳跃扫描仅仅是其中的一种方案。我们可以将索引跳跃扫描看作是覆盖索引条件查询缺失前缀的一种优化方案。

官方链接：MySQL :: MySQL 8.0 Reference Manual :: 10.2.1.2 Range Optimization

欢迎纠正与交流

原文地址：https://blog.csdn.net/wangye135/article/details/140389620

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！