MySQL索引合并

MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但存在有特殊情况，也可能在一个查询中使用到多个二级索引，MySQL中这种使用到多个索引来完成一次查询的执行方法称之为：索引合并/index merge
在上一篇文章《MySQL内核查询成本计算》中提到，会先分析单独使用这些索引的成本，最后还要分析是否可能使用到索引合并。
而使用索引合并的前提条件是要满足既定的一些规范，其中就包括下面三种：

一、Intersection合并

Intersection是交集的意思。某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集，比如下面的例子：

SELECT * FROM employees WHERE name = 'lizhi' AND hire_time = '2022-03-30 23:00:00';

如果这个查询使用Intersection合并的方式执行的话，大概步骤如下：

1、从idx_name二级索引对应的B+树中取出name = 'lizhi'的所有记录

2、再从idx_hire_time二级索引对应的B+树中取出hire_time='2022-03-30 23:00:00'的所有记录。

3、因为二级索引的叶子节点都是由索引列和主键ID构成的，可以计算出两个二级索引结果集中主键ID的交集

4、根据交集中的主键ID进行回表操作

注：至于为什么不直接使用其中一个索引，根据查询条件过滤出主键ID，然后回表再判断其他的查询条件是否满足，主要因为回表操作是一个随机I/O操作，性能比较差。如果可以通过二级索引的的顺序IO进行条件过滤，然后只对交集中的主键进行回表操作，可以大大减少随机I/O读取的成本消耗。

但是只有在下面两种情况下才可能会使用到Intersection索引合并。

1.1 等值匹配

二级索引列是等值匹配的情况才有可能使用到索引合并，而对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。

比如假设表employees有idx_name_age联合索引以及idx_hire_time单列索引，那么下面两种情况就没法使用索引合并：

select * from employees where name = 'lizhi' and age > 25 and hire_time='2022-03-30 
23:00:00;

select * from employees where name = 'lizhi' and hire_time='2022-03-30 23:00:00;

第一个SQL是因为使用了范围查询，而第二个SQL由于只用了联合索引idx_name_age的部分列，这两种情况就没法使用索引合并。

1.2 主键范围匹配

二级索引列都是等值匹配的情况下才可能使用Intersection索引合并，是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。

Intersection索引合并会把从多个二级索引中查询出的主键值求交集，如果从各个二级索引中查询的到的结果集本身就是已经按照主键排好序的，那么求交集的过程就很容易。

但是如果从各个二级索引中查询出的结果集并不是按照主键排序的话，那就要先把结果集中的主键值排序完再来做上边的那个过程，就比较耗时了。

按照有序的主键值去回表取记录有个专有名词，叫：Rowid Ordered Retrieval，简称ROR。

索引合并也可以有聚簇索引参加，在搜索条件中有主键的范围匹配的情况下也可以使用Intersection索引合并索引合并。

比如下面的例子：

SELECT * FROM employees WHERE id > 100 AND name= 'lizhi';

通过二级索引筛选出来的主键ID，可以通过id>100再过滤掉一部分。

注意：

上边说两种情况只是发生Intersection索引合并的必要条件，不是充分条件。

也就是说即使情况一、情况二成立，也不一定发生Intersection索引合并，优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，而通过Intersection索引合并后需要回表的记录数大大减少时才会使用Intersection索引合并。

二、Union合并

在写查询语句时经常想把既符合某个搜索条件的记录取出来，也把符合另外的某个搜索条件的记录取出来，我们说这些不同的搜索条件之间是OR关系。有时候OR关系的不同搜索条件会使用到不同的索引，比如下面的例子：

SELECT * FROM employees WHERE name = 'lizhi' OR hire_time = '2022-03-30 23:00:00';

Union是并集的意思，适用于使用不同索引的搜索条件之间使用OR连接起来的情况。

与Intersection索引合并类似，MySQL在某些特定的情况下才可能会使用到Union索引合并。

其中等值匹配和主键范围匹配与Intersection合并中的是一样，只是Unon合并多了一种情况。

2.1 使用Intersection索引合并的搜索条件

搜索条件的某些部分使用Intersection索引合并的方式得到的主键集合和其他方式得到的主键集合取并集，比方说这个查询：

SELECT * FROM employees WHERE age = 22 AND position = 'dev' OR (name = 'lizhi' AND hire_time = '2022-03-30 23:00:00');

先按照搜索条件name = 'lizhi' AND hire_time = '2022-03-30 23:00:00'从索引idx_name和idx_hire_time中使用Intersection索引合并的方式得到一个主键集合。

再按照搜索条件 age = 22 AND position = 'dev'从联合索引idx_age_position中得到另一个主键集合。

采用Union索引合并的方式把上述两个主键集合取并集，然后进行回表操作，将结果返回。

三、Sort-Union合并

Union索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹配的条件下才可能被用到，比方说下边这个查询就无法使用到Union索引合并：

SELECT * FROM employees WHERE name > 'lizhi' OR hire_time < '2022-03-30 23:00:00';

这是因为根据这两个条件分别从idx_name和idx_hire_time二级索引中取出来的主键并不一定是有序的。

但MySQL可以对这两个索引满足条件的主键集合进行排序，然后在进行Union。

上述这种先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并，很显然，这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/112092.html