MySQL的3种索引合并优化⭐️or到底能不能用索引?

前言

前文我们讨论过MySQL优化回表的多种方式：索引条件下推ICP、多范围读取MRR、覆盖索引等

这篇文章我们来聊聊MySQL提供的另一种优化回表的手段：index merge 索引合并

在阅读本文前，你需要了解MySQL的server层与存储引擎层如何交互、二级索引和聚簇索引的区别、回表等知识

如果同学不太了解这些知识可以回看前文：

MySQL的优化利器⭐️索引条件下推，千万数据下性能提升273%🚀

MySQL的优化利器⭐️Multi Range Read与Covering Index是如何优化回表的？

MySQL导致索引失效的八股文中有这样一条：使用or会导致索引失效

那么是不是所有场景都会失效呢？带着这个问题，我们往下看

案例使用上篇文章的座位表，并分别建立seat_code、student_id 两个二级索引

CREATE TABLE `seat` (
  `seat_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '座位ID',
  `seat_code` char(10) DEFAULT NULL COMMENT '座位码',
  `student_id` bigint(20) DEFAULT NULL COMMENT '座位关联的学生ID',
  PRIMARY KEY (`seat_id`),
  KEY `idx_student_id` (`student_id`),
  KEY `idx_seat_code` (`seat_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

index merge

正常情况下优化器只能选择一个它认为最成本最低的索引来生成执行计划

但在某些情况下可以使用多个索引进行索引合来优化

索引合并的优化分成三种方式：

1. index merge intersection 交集索引合并
2. index merge union 并集索引合并
3. index merge sort union 排序并集索引合并

三种方式各自有什么不同呢？请按顺序往下看：

index merge intersection

index merge intersection 是用于交集的索引合并，交集往往和查询条件中的and相关

什么是交集？

比如有两个集合分别是（1，2，3）、（2，3，4），那么交集就是它们都存在的值（2，3）

举例这样一条SQL：

select * from seat 
where seat_code = 'caicaiseat' and student_id = 1

当不使用索引合并优化时，优化器可能选择seat_code索引或者student_id索引

当使用seat_code索引时，先在索引中找到满足seat_code = caicaiseat的记录，再回表查询聚簇索引获取完整记录

image.png

关闭交集索引合并的优化

SET optimizer_switch='index_merge_intersection=off';

查看执行计划

image.png

在这种场景下，可能存在很多满足seat_code = caicaiseat ，但是不满足 student_id = 1的记录

如果这些记录也需要回表，再回表后还是会被过滤，浪费资源来对这些记录进行回表

回表查询不仅仅是多查询一次，在这次查询中还可能是随机IO，查询量大的情况下，回表的代价是很高的

使用交集索引合并后

1. 先使用seat_code索引找到满足 seat_code = caicaiseat 的条件
2. 然后使用student_id索引找到满足 student_id = 1 的条件
3. 接着根据主键seat_id对它们进行交集过滤，剩下的记录再进行回表，以此来减少回表的次数

（图中未回表是因为正好满足覆盖索引）

image.png

需要注意的是使用交集索引合并需要主键值需要有序，如果主键值乱序进行交集过滤，在回表时会产生随机IO，得不偿失

在二级索引中只有索引列相等时才对主键值进行排序，因此大部分使用交集索引合并的场景是等值比较=

开启交集索引合并，查看执行计划

type类型为索引合并，使用到这两个索引，附加信息显示用到交集索引合并，并且还用上覆盖索引不需要回表

由于seat座位表只存在主键seat_id、座位码seat_code、学生ID student_id，需要查询的列都在二级索引上，因此不用回表

image.png

有的同学可能注意到：为啥不把seat_code与student_id组成（seat_code，student_id）联合索引呢？

实际上（seat_code，student_id）联合索引也是可以用上索引的，但如果要单独查询student_id就会导致索引失效了

index merge union

index merge union是用于并集的索引合并，并集往往与查询条件or相关

什么是并集？

比如有两个集合分别是（1，2，3）、（2，3，4），那么并集就是它们都存在值的总和（1，2，3，4）

举例这样一条SQL

select * from seat 
where seat_code = 'caicaiseat' or student_id = 1;

当不使用index merge union的情况下，会直接全表扫描（聚簇索引），依次判断记录是否满足条件

index_merge_union=off 关闭并集索引合并

index_merge_sort_union 关闭排序的并集索引合并（是下一个要说明的索引合并，其在并集索引合并的基础上增加排序）

image.png

当使用index merge union的情况下

1. 先使用seat_code索引找到满足条件seat_code = 'caicaiseat'的记录
2. 再使用student_id索引找到满足条件student_id = 1的记录
3. 然后将它们的主键值seat_id取并集后再回表查询，以此来减少开销

image.png

开启union优化，查看执行计划：已经使用index merge union

image.png

所以以后不要再傻乎乎的背八股文说or用不上索引啦~

使用index merge union的前提与index merge intersection类似也需要主键值有序

index merge sort union

由于or在某些场景下会让优化器认为回表成本大，不如全表扫描，从而导致索引失效

而index merge union的使用前提（主键有序）太苛刻，很多场景下还是无法使用索引

index merge sort union 排序的并集索引合并：对主键值无序的场景排序后再进行并集

比如这条SQL中

select * from seat where seat_code like 'a%' and student_id = 1

查询条件seat_code不再是等值比较，这样满足seat_code like 'a%'记录的主键值也不一定是有序的

而seat_code索引中满足student_id = 1的记录主键值是有序的

为了将seat_code索引满足条件的记录与seat_code索引满足条件的记录作并集

先对seat_code索引满足条件的记录进行排序，有序后再取并集

image.png

开启sort union后，查看执行计划：使用index merge sort union

image.png

情况与index merge union类似，使用前提可以是主键乱序，在主键乱序后对其进行排序再取交集

总结

index merge索引合并优化默认开启，分为intersection交集、union并集、sort union排序并集三种方式

index merge intersection使用的前提：and和可以使用多个索引且结果中主键有序，分别在对应的索引中找到满足条件的记录，对记录进行交集过滤后再进行回表，减少不必要的回表开销

index merge union 使用的前提：or和可以使用多个索引且结果中主键有序，分别在对应索引中找到满足条件的记录，对记录进行并集过滤后再进行回表，避免全表扫描

index merge intersection/union使用的前提都是需要主键有序，因为主键乱序需要先排序再进行交集/并集，否则会有随机IO

由于index merge union中or容易导致优化器认为回表成本大进而全表扫描，而满足主键有序的场景太苛刻，因此使用index merge sort union 在主键乱序的情况下排序再取并集

最后（不要白嫖，一键三连求求拉~）

本篇文章被收入专栏 由点到线，由线到面，构建MySQL知识体系，感兴趣的同学可以持续关注喔

本篇文章笔记以及案例被收入 gitee-StudyJava、 github-StudyJava 感兴趣的同学可以stat下持续关注喔~

有什么问题可以在评论区交流，如果觉得菜菜写的不错，可以点赞、关注、收藏支持一下~

关注菜菜，分享更多干货，公众号：菜菜的后端私房菜

我正在参与2024腾讯技术创作特训营最新征文，快来和我瓜分大奖！