通常情况下,mysql只会用到一个二级索引.但在某些特殊情况下,查询可能用到多个二级索引,称为索引合并.

Intersection 合并

交集合并.指的是某个查询将从多个二级索引的查询结果集中取交集
eg. SELECT * FROM order_exp WHERE order_no = ‘a’ AND expire_time = ‘b’;
假设采用交集合并的话,
1.则是从order_no索引树 取 =a 的结果集 和expre_time的索引树取expre_time =b的结果集,再取交集 结果为主键值
2.将1中的主键值进行回表操作返回给用户.

为什么会采用交集合并而不是单个索引?
1.假如只采用单个索引 eg. 采用order_no=’a’ 取到一堆 数据,在回表,再比较expire_time的值.
虽然读多个二级索引会增加成本,更消耗性能,但回表是随机IO,如果回表次数太多,显然成本太高.mysql此时经过成本计算选择多走一次二级索引

什么情况下可能会使用到交集合并

一、等值匹配：二级索引是等值匹配的情况，对联合索引来说每个列都必须等值匹配
二、主键列可以是范围匹配，eg. SELECT * FROM order_exp WHERE id > 100 AND insert_time = ‘a’;

原因:
二级索引的构成是 索引列+主键列,在等值匹配下二级索引下的主键列都是有序的,取交集的过程就很容易,同理可以解释主键列可以是范围匹配

一般情况下推荐使用联合索引消除交集合并,联合索引更高效

Union 合并

指的是某个查询将从多个二级索引的查询结果集中取并集
eg. SELECT * FROM order_exp WHERE order_no = ‘a’ OR expire_time = ‘b’

什么情况下可能会使用到并集合并

一、等值匹配：二级索引是等值匹配的情况，对联合索引来说每个列都必须等值匹配
二、主键列可以是范围匹配
分析同交集合并

Sort-Union 合并

eg. SELECT * FROM order_exp WHERE order_no< ‘a’ OR expire_time> ‘z’

上述语句显然无法使用union合并.
因为从oder_no和expire_time找到的id列都不是有序的,所以此时mysql选择根据order_no< ‘a’获取到的主键值排好序,
再根据expire_time> ‘z’获取到的主键值排好序,再合并

采用以上策略均由mysql通过成本计算决定是否使用上述的三种合并方式.