索引合并(index merge)

一般来说，Mysql只会为单一索引生成扫描区间，但还是有特殊情况会为多个索引生成扫描区间。
这种为多个索引生成扫描区间，也就是使用多个索引来完成一次查询的方法称为索引合并(index merge)。

为了故事顺利发展，先把之前的例子再贴一次

mysql> create table single_table(
    -> id int not null auto_increment,
    -> key1 varchar(10),
    -> key2 int,
    -> key3 varchar(100),
    -> key_part1 varchar(100),
    -> key_part2 varchar(100),
    -> key_part3 varchar(100),
    -> common_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)
    -> ) engine=InnoDB charset=utf8;
Query OK, 0 rows affected, 1 warning (0.05 sec)

为id列建立的聚簇索引
为key1列建立的idx_key1二级索引
为key2列建立的uk_key2二级索引，且是唯一
为key3列建立的idx_key3二级索引
为key_part1、key_part2、key_part3列建立的idx_key_part二级索引，这也是个联合索引

intersection索引合并

select * from single_table where key1 = 'a' and key3 = 'b';

当然你可以透过key1的idx_key1二级索引或key3的idx_key3二级索引先去查询获得纪录并回表操作後去比对key3 = 'b'或key1 = 'a'的纪录，这样子的全表扫描的方式也是可以。
但如果我们同时使用idx_key1二级索引及idx_key3二级索引，也就是在idx_key1树扫描key1值在['a','a']区间中的纪录，且同时在idx_key3树扫描key3值在['b','b']区间中的纪录，然後再根据各自的结果找到id值相同的纪录再去回表操作，这样大大的降低回表操作的负担，提升了效能。

另外如果使用intersection索引合并则要求key1值在['a','a']区间中的纪录及key3值在['b','b']区间中的纪录必须是按照主键值排序！
因为
第一个从两个有序集合中取交集远比两个无序集合容易多。
再来第二个如果id值是有序排列的，则这些id值在回表操作的时候就不再是单纯的随机I/O，进而提高效率。

举个实际例子来看一下
假设idx_key1的扫描区间['a','a']中二级索引纪录的id值是排好序的，顺序是1、3、5
idx_key3的扫描区间['b','b']中二级索引纪录的id值也是排好序的，顺序是2、3、4
那麽使用intersection索引合并来查询的过程如下：
步骤1
先从idx_key1的扫描区间['a','a']中取出第一笔二级索引纪录，该纪录的主键值为1
然後从idx_key3的扫描区间['b','b']中取出第一笔二级索引纪录，该纪录的主键值为2
因为1<2，纪录不同，所以直接把小的那笔纪录(1)舍弃

步骤2
再从idx_key1的扫描区间['a','a']中取出第一笔二级索引纪录，该纪录的主键值为3
跟本来步骤1中的主键值2做比较
因为2<3，纪录不同，所以直接把小的那笔纪录(2)舍弃

步骤3
再idx_key3的扫描区间['b','b']中取出第一笔二级索引纪录，该纪录的主键值为3
跟本来步骤2中的主键值3做比较
因为3=3，交集成功，去回表得到完整的使用者纪录将其发送给用户端

步骤4
再从idx_key1的扫描区间['a','a']中取出第一笔二级索引纪录，该纪录的主键值为5
然後从idx_key3的扫描区间['b','b']中取出第一笔二级索引纪录，该纪录的主键值为4
因为4<5，纪录不同，所以直接把小的那笔纪录(4)舍弃

步骤5
从idx_key3的扫描区间['b','b']中要再取一笔二级索引纪录，发现没有了，结束查询

如果在使用某个二级索引查询时，取得的纪录不是照主键值排序，则不能使用intersection索引合并。
如下：

select * from single_table where key1 > 'a' and key3 = 'b';

因为从idx_key1的扫描区间['a',+无限大]获得的纪录并不是按照主键值排序的，所以不能使用intersection索引合并。

select * from single_table where key1 = 'a' and key_part1 = 'a';

对於idx_key_part二级索引来说，先照key_part1排序，相同再照key_part2排序。
所以key_part1 = 'a'不是照主键值排序，就不能使用intersection索引合并。

union索引合并

select * from single_table where key1 = 'a' or key3 = 'b';

使用key1的idx_key1二级索引或key3的idx_key3二级索引去查询，因为其区间为[-无限大,+无限大]，所有的纪录都要再去回表操作，比不用索引的全表扫描还慢。
所以这时我们可以跟intersection索引合并一样
同时使用idx_key1二级索引及idx_key3二级索引去各自得到结果，只是这边变成是去掉重复的值，然後再回表得到完整使用者纪录，这就是union索引合并。
也跟intersection一样各个索引的结果必须是有序的。

sort-union索引合并
由於union索引合并的条件太过严苛(各个索引中扫描到的纪录都必须是有序的)，所以有了sort-union索引合并。
看以下的例子：

select * from single_table where key1 < 'a' or key3 > 'z';

我们可以先把根据key1 < 'a'条件得到的纪录做排序
再将根据key3 > 'z'得到的纪录做排序
接下来就跟union合并操作一样辣
这个就叫做sort-union索引合并(比union索引合并多了一个sort的过程)