mysql-index_merge

什么是索引合并?

表结构准备：

#单表
create tabel single_table(
	id int not null auto_increment,
    key1 varchar(100),
    key2 int,
    key3 varchar(100),
    key_part1 varchar(100),
    key_part2 varchar(100),
    key_part3 varchar(100),
    commend_field varchar(100),
    primary key (id),
    key idx_key1 (key1),
    unique key uk_key2 (key2),
    key idx_key3 (key3),
    key idx_key_part(key_part1, key_part2, key_part3)
);

Index Merge可以称为索引合并，即查询优化器使用多个索引来满足单个查询的需求，主要有以下几种方式：

Intersection合并

二级索引取交集，即Mysql在一个查询中使用多个二级索引，并对其筛选的结果取交集输出。比如下面这个查询：

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

这个SQL就是一个“可能使用”index merge的语句，其搜索过程为：

（1）从idx_key1二级索引对应的B+树中取出key1 = 'a’的相关记录。
（2）从idx_key3二级索引对应的B+树中取出key3 = 'b’的相关记录。
（3）二级索引的记录都是由索引列 + 主键构成的，所以我们可以计算出这两个结果集中id值的交集。
（4）按照上一步生成的id值列表进行回表操作，也就是从聚簇索引中把指定id值的完整用户记录取出来，返回给用户。
为什么说“可能使用”呢，因为从查询成本的角度来看，还有另一种查询方式：使用idx_key1或者idx_key3只根据某个 搜索条件去读取一个二级索引，然后回表后再过滤另外一个搜索条件。
分析一下两种查询执行方式之间需要的成本代价：
只读取一个二级索引的成本：
（1）按照某个搜索条件读取一个二级索引
（2）根据从该二级索引得到的主键值进行回表操作，然后再过滤其他的搜索条件
读取多个二级索引之后取交集成本：
（1）按照不同的搜索条件分别读取不同的二级索引
（2）将从多个二级索引得到的主键值取交集，然后进行回表操作
从理论上来说，读取多个二级索引比读取一个二级索引消耗性能，但是读取二级索引是一个顺序IO操作，而回表是一个随机IO操作，因此，如果读取两个二级索引所得到的交集数量比较少，也就意味之回表操作较少，此时就会使用到index merge。
MySQL在某些情况下可能会使用到索引合并：
（1）二级索引列是等值匹配的情况。对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。

SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

（2）二级索引的范围查询（非等值查询）以及联合索引的部分匹配无法使用索引合并，如下语句不可以使用索引合并：

 #二级索引的非等值查询
 SELECT * FROM single_table WHERE key1 > 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
 #联合索引的不完全匹配
 SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a';

（3）主键列的范围查询可以使用索引合并，比如如下的语句：

SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';

看起来很乱，其实这些查询都有一个共同点，我们从索引结构来看：
对于InnoDB的二级索引来说，二级索引记录先是按照索引列进行排序，（如果该二级索引是一个联合索引，那么会按照联合索引中的各个列依次排序）。而二级索引的记录是由索引列 + 主键构成的，二级索引列的值相同的记录可能会有好多条，这些索引列的值相同的记录又是按照主键的值进行排序的。
所以重点来了，之所以在二级索引列都是等值匹配的情况下才可能使用索引合并，是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。因为如果从各个二级索引中查询的到的结果集本身就是已经按照主键排好序的，那么求交集的过程就变得简单（想一想两个有序的数组取交集的方法，O(n)复杂度即可完成），但是如果取交集前的ID数组不是有序的，就需要先对其进行排序，然后在进行交集操作。
但是为什么主键索引的范围查询就可以使用index merge呢？从上面的情况（3）可以看出来，二级索引等值查询后的可以获得排序好的主键序列，那怎么此时就可以直接与另一个主键匹配条件进行判断过滤了，过滤后的主键再去进行回表即可。
还需要注意的是，上述介绍的情况属于索引合并的充分条件，而不是必要条件，具体要不要使用索引合并，还要看优化器具体的优化操作。

Uion合并

查询中我们除了要使用AND来进行数据筛选，有时还需要使用OR来进行并集数据匹配，在多个二级索引进行数据匹配的时候，就可能会出现uion合并。与Intersection索引合并类似，MySQL在某些特定的情况下才可能会使用到Union索引合并：
（1）二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。

 SELECT * FROM single_table WHERE key1 = 'a' OR ( key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');

（2）二级索引的范围查询（非等值查询）以及联合索引的部分匹配无法使用索引合并，如下语句不可以使用索引合并：

  SELECT * FROM single_table WHERE key1 > 'a' OR (key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');
  SELECT * FROM single_table WHERE key1 = 'a' OR key_part1 = 'a';

（3）主键列可以是范围匹配
（4）子搜索条件使用了Intersection合并，就是搜索条件的某些部分使用Intersection索引合并的方式得到的主键集合和其他方式得到的主键集合取交集，比如：

SELECT * FROM single_table 
WHERE 
key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' 
OR (key1 = 'a' AND key3 = 'b');

优化器可能采用这样的方式来执行这个查询：
1）先按照搜索条件key1 = ‘a’ AND key3 = 'b’从索引idx_key1和idx_key3中使用Intersection索引合并的方式得到一个主键集合。
2）再按照搜索条件key_part1 = ‘a’ AND key_part2 = ‘b’ AND key_part3 = 'c’从联合索引idx_key_part中得到另一个主键集合。
3）采用Union索引合并的方式把上述两个主键集合取并集，然后进行回表操作，将结果返回给用户。