mysql二级索引需要回表_MySQL-索引-索引合并学习

MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但是，在这些特殊情况下也可能在一个查询中使用到多个二级索引，设计MySQL的把这种使用到多个索引来完成一次查询的执行方法称之为：index merge

所有的索引合并往往还是需要看数据回表的数量与 mysql 的优化器！优化器只有在单独根据搜索条件从某个二级索引中

获取的记录数比较少，通过Union索引合并后进行访问的代价比全表扫描更小时才会使用Union索引合并

Intersection合并

Intersection翻译过来的意思是交集。这里是说某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集

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

假设这个查询使用Intersection合并的方式执行的话:

从idx_key1二级索引对应的B+树中取出key1 = 'a’的相关记录。

从idx_key3二级索引对应的B+树中取出key3 = 'b’的相关记录。

二级索引的记录都是由索引列 + 主键构成的，所以我们可以计算出这两个结果集中id值的交集。按照上一步生成的id值列表进行回表操作，也就是从聚簇索引中把指定id值的完整用户记录取出来，返回给用户。

虽然读取多个二级索引比读取一个二级索引消耗性能，但是读取二级索引的操作是顺序I/O，而回表操作是随机I/O，

所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。

什么情况可能会使用到Intersection索引合并：

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

说明 不能有范围查找，并且联合索引情况下所有列都需要出现。

主键列可以是范围匹配 SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';

对于InnoDB的二级索引来说，记录先是按照索引列进行排序，如果该二级索引是一个联合索引，那么会按照联合索引中的各个列依次排序。

而二级索引的用户记录是由索引列 + 主键构成的，二级索引列的值相同的记录可能会有好多条，

这些索引列的值相同的记录又是按照主键的值进行排序的。

所以重点来了，之所以在二级索引列都是等值匹配的情况下才可能使用Intersection索引合并，

是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的

Union合并

OR我们在写查询语句时经常想把既符合某个搜索条件的记录取出来，也把符合另外的某个搜索条件的记录取出来;

Intersection是交集的意思，这适用于使用不同索引的搜索条件之间使用AND连接起来的情况；Union是并集的意思，适用于使用不同索引的搜索条件之间使用OR连接起来的情况。与Intersection索引合并基本相同，并且可以与Intersection一起用，MySQL在某些特定的情况下才可能会使用到Union索引合并

Sort-Union合并

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

到Union索引合并：SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'

这是因为根据key1 < 'a’从idx_key1索引中获取的二级索引记录的主键值不是排好序的，根据key3 > 'z’从idx_key3索引中获取的二级索引记录的主键值也不是排好序的。所以：

先根据key1 < 'a’条件从idx_key1二级索引总获取记录，并按照记录的主键值进行排序

再根据key3 > 'z’条件从idx_key3二级索引总获取记录，并按照记录的主键值进行排序

因为上述的两个二级索引主键值都是排好序的，剩下的操作和Union索引合并方式就一样了，把id合并在一起。

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

联合索引替代Intersection索引合并

这个查询之所以可能使用Intersection索引合并的方式执行，还不是因为idx_key1和idx_key3是两个单独的B+树索引，索引要是把这两个列搞一个联合索引，那直接使用这个联合索引就把事情搞定了，何必用啥索引合并呢。

不过还是要考虑一下单idx_key3索引的查询问题！要么单独建一个，要么在查询上配合上联合查询。