【MySQL 】单表访问之索引合并
MySQL在⼀般情况下执⾏⼀个查询时最多只会⽤到单个⼆级索引，但存在有特殊情况，在这些特殊情况下也可能在⼀个查询中使⽤到多个⼆级索引，MySQL中这种使⽤到多个索引来完成⼀次查询的执⾏⽅法称之为：索引合并/index merge，具体的索引合并算法有下边三种。Intersection 合并
Intersection翻译过来的意思是交集。这⾥是说某个查询可以使⽤多个⼆级索引，将从多个⼆级索引中查询到的结果取交集，⽐⽅说下边这个查询：
假设这个查询使⽤Intersection合并的⽅式执⾏的话，那这个过程就是这样的：
1. 从idx_first_name⼆级索引对应的B+树中取出first_name='Georgi'的相关记录。从idx_last_name⼆级索引对应的B+树中取出last_name='Facello'的相关记录。⼆级索引的记录都是由索引列+主键构成的，所以我们可以计算出这两个结果集中id值的交集。
2. 按照上⼀步⽣成的id值列表进⾏回表操作，也就是从聚簇索引中把指定id值的完整⽤户记录取出来，返回给⽤户。
为啥不直接使⽤idx_first_name或者idx_last_name只根据某个搜索条件去读取⼀个⼆级索引，然后回表后再过滤另外⼀个搜索条件呢？这⾥要分析⼀下两种查询执⾏⽅式之间需要的成本代价。
只读取⼀个⼆级索引的成本：按照某个搜索条件读取⼀个⼆级索引，根据从该⼆级索引得到的主键值进⾏回表操作，然后再过滤其他的搜索条件
读取多个⼆级索引之后取交集成本：按照不同的搜索条件分别读取不同的⼆级索引，将从多个⼆级索引得到的主
键值取交集，然后进⾏回表操作。
虽然读取多个⼆级索引⽐读取⼀个⼆级索引消耗性能，但是⼤部分情况下读取⼆级索引的操作是顺序I/O，⽽回表操作是随机I/O，所以如果只读取⼀个⼆级索引时需要回表的记录数特别多，⽽读取多个⼆级索引之后取交集的记录数⾮常少，当节省的因为回表⽽造成的性能损耗⽐访问多个⼆级索引带来的性能损耗更⾼时，读取多个⼆级索引后取交集⽐只读取⼀个⼆级索引的成本更低。
MySQL在某些特定的情况下才可能会使⽤到Intersection索引合并，哪些情况呢？
情况⼀：等值匹配
mysql交集查询普通的⼆级索引列是等值匹配的情况。
对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。⽽下边这两个查询就不能进⾏Intersection索引合并：
第⼀个查询是因为对hire_date进⾏了范围匹配，第⼆个查询是因为联合索引udx_union_test中的first_name列并没有出现在搜索条件中，所以这两个查询不能进⾏Intersection索引合并。6 rows mysql > select  * from  t_emp where  first_name ='Georgi' and  last_name ='Facello';
1mysql > select  * from  t_emp where  hire_date >'1960-04-17' and  birth_date ='1991-11-27' and  first_name ='Georgi';mysql > select  * from  t_emp where  hire_date ='1960-04-17' and  birth_date ='1991-11-27';
1
2
情况⼆：主键列可以是范围匹配
⽐⽅说下边这个查询可能⽤到主键和idx_first_name进⾏Intersection索引合并的操作：
对于InnoDB的⼆级索引来说，记录先是按照索引列进⾏排序，如果该⼆级索引是⼀个联合索引，那么会按照联合索引中的各个列依次排序。⽽⼆级索引的记录是由索引列+主键构成的，⼆级索引列的值相同的记录可能会有好多条，这些索引列的值相同的记录⼜是按照主键的值进⾏排序的。
所以重点来了，之所以在⼆级索引列都是等值匹配的情况下才可能使⽤Intersection索引合并，是因为只有在这种情况下根据⼆级索引查询出的结果集是按照主键值排序的。
Intersection索引合并会把从多个⼆级索引中查询出的主键值求交集，如果从各个⼆级索引中查询的到的结果集本⾝就是已经按照主键排好序的，那么求交集的过程就很容易。假设某个查询使⽤Intersection索引合并的⽅式从idx_first_name或者idx_last_name这两个⼆级索引中获取到的主键值分别是：从idx_first_name中获取到已经排好序的主键值：1、3、5
从idx_last_name中获取到已经排好序的主键值：2、3、4
那么求交集的过程就是这样：逐个取出这两个结果集中最⼩的主键值，如果两个值相等，则加⼊最后的交集结果中，否则丢弃当前较⼩的主键值，再取该丢弃的主键值所在结果集的后⼀个主键值来⽐较，直到某个结果集中的主键值⽤完了，时间复杂度是O(n)。
但是如果从各个⼆级索引中查询出的结果集并不是按照主键排序的话，那就要先把结果集中的主键值排序完再来做上边的那个过程，就⽐较耗时了。按照有序的主键值去回表取记录有个专有名词，叫：Rowid Ordered Retrieval，简称ROR。mysql > select  * from  t_emp where  emp_no > 493549 and  first_name ='Georgi';
1

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。