索引合并Intersection、union (3)--单表访问方法（三十八)

上篇文章我们说了，使用索引的注意事项，前面我们总结了查询数据库的方式有const，ref，ref_or_null，range，index，all，而使用时候需要注意，当where语句后面全是索引查询，当where语句后面跟着非索引的时候，当用and连接，比如where key1 and 非索引 = ‘abc’，这时候会先二级索引查询索引b+树进行回表。若用where key1 or 非索引 = ‘abc’，这时候会直接全表查询

索引合并

mysql查询的情况，一般下会用到单个二级索引，但某些时刻也会在一个查询里使用到多个二级索引，设计innoDB的人吧这个称为 index merge。

Intersection合并

顾名思义，这是交集的意思，就是将多少二级索引查询的数据取交集，

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

假如用intersection合并的方执行的话，这里有两个b+树，从key1和key3的索引树叶子节点查询到他们的交集数据id，再用交集id回表查询需要的数据。

那为什么不查询一个索引的id，然后继续在聚簇索引树过滤剩下的记录呢？这里就要分析两个方式查询的成本代价。

虽然读取多个二级索引比读取一个二级索引消耗性能，但是读取二级索引的时候是顺序I/O，而回表操作是随机I/O,而且读取一个二级索引，回表的记录特别多，这样也会降低性能，而取交集之后，回表的记录更少，获取数据的成本更低。

Mysql在哪些情况下才会使用intersection索引合并呢？

二级索引必须等值匹配，联合索引必须所有的列等值匹配

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

而下面两个就不可以用intersection索引：

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';

个是因为范围查询了，第二个是因为联合索引只有一个等值匹配。

主键列可以范围匹配

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

比如说这个sql就可以用到intersection索引。

那上面两个条件为什么成立，回忆我们之前学的，二级索引节点都是排序好的，按主键排序，所以如果二级索引不是等值的情况下，如何进行主键排序，所以只有二级索引是等值的情况下，不是范围查询的，才可以使用intersection合并索引查询，因为只有这种情况下，查询出来的数据是按主键排序的。所以这时候吧排序好的id，回表查询本身的数据就效率很高。

假设用intersection方法从key1和key2两个二级索引获取到的主键分别是：

Key1获取到排序好的主键：1，3，5

Key2获取到排序好的主键：2，3，6

那mysql获取他们交集的过程就是，逐个比较两个结果集小的值，小的那个就丢弃，相等则放入交集结果，继续比较后一位数，小的就丢弃，直到遇到相等放入结果集中。

这种效率非常高，过程非常快，时间复杂度是0(n)，但如果二级索引查询出来的主键不是排序好的，所以就需要先排序，这样就比较耗时间。

情况二又是怎么回事呢？

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

当上面这个sql需要查询的时候，我们会查询key1的二级索引树，然后回表查询过滤的id>100,其实这样是没必要的，为了提高效率，可以用intersection合并索引，别忘了二级索引的叶子节点是存着主键id的，直接排序获取就好。

当然以上两个情况即使成立，也未必就会intersection合并索引查询，还是要看回表查询的代价，如果sql优化器觉得回表的代价太大，回表查询数据量太多，自然就会用intersection合并索引查询。

Union合并

Union在两个表取出并集可以用上，但这里不是在两个表使用，意思是sql语句取并集：

情况一，二级索引是等值匹配，联合索引必须每个列的值都等值匹配，不能只匹配部分列。

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

比方说上面的sql就可以用union方法查询。

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';

这两个sql就不可以用union查询，原因是二级索引不能范围查询和联合索引必须所有列都在。

情况二：主键可以范围查询

情况三：使用intersection索引合并搜索条件

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

优化器可能会这样处理，

先按key_part1和key_part2和key_part3使用intersection索引合并的方式得到一个主键集合。

在按key1和key3使用intersection索引合并的方式得到一个主键集合。

后按union索引并集的方法吧这两个集合合在一起。

Sort-union合并

union索引使用的条件太苛刻，

SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'

这时候如果由上面这种情况：

先通过key1查询索引树的集合，因为不是排序好的，需要把他进行排序。

再通过key3查询索引树大于z的集合，因为不是排序好的，需要把他进行排序。

之后再用union合并索引，把两个id集合合并在一起，这种方法就是sort-union。但是是没有sort-intersection的，因为sort-union适合从某个索引获取的记录数少，即使排序，成本也不会太高。而前者使用场景是单独根据搜索条件从二级索引中获取的记录太多，排序成本太高。

索引合并注意事项

联合索引不可以代替intersection索引

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

上面的sql会直接用intersection合并索引查询，那如果吧他们建立一个联合索引直接用联合索引idx_key1_key3查询不也能搞定吧，但如果建立联合索引而放弃普通的单个二级索引的话，当需要把key3列单独查询，就没有索引了。

总结：当二级索引两个列不是联合索引但是在一起查询的时候，有机会触发intersection合并查询和union查询。