导读

在之前的博客文章《MySQL索引底层结构及索引优化实战》,一般情况下一条查询语句只会使用一个索引,但是也存在特殊情况。

特殊情况下 一条查询语句 会使用 多个二级索引,这种情况统称为 索引合并(index merge)。

通过trace ,可以明确知道 MySQL是否使用了 索引合并。

trace 索引合并情况

索引合并类型

Intersection合并(交集合并)

注意事项

  • 以下合并条件(等值匹配)都满足的情况下,不代表优化器一定会进行 索引合并
    • 优化器只有单独通过某个二级索引获取的结果集太多,导致回表开销大时,才会考虑 索引合并

只使用一个索引的查询成本

根据搜索条件读取一个 索引,在通过索引的主键值进行回表(聚簇索引),回表后再过滤其他的搜索条件。

使用多个二级索引的查询成本

根据搜索条件读取多个二级索引,将多个二级索引的主键值 进行交集合并,然后再回表。

为什么读取多个索引效率更高

  1. 虽然 读取多个索引比读取单个索引消耗IO,但是读取索引的IO是顺序IO,而回表的IO是随机IO,随机IO效率慢还消耗资源。
  2. 读取多个索引交集合并后 结果集更少,意味着后续的搜索条件 回表次数更少,相对应损耗时间就更少。

交集合并满足条件

  • 以下两个条件任意一个条件 都可满足 交集合并要求

条件1 - 等值匹配

二级索引必须 等值匹配,如果是联合索引的话,索引中每个列都必须 等值匹配,不能只匹配联合索引的个别字段。

如果搜索条件中 的二级索引有范围匹配(主键ID是可以范围匹配),则无法 进行交集合并。

单值索引 等值匹配

正确案例:

SELECT * FROM order_exp WHERE order_no='a' AND insert_time='a'

错误案例:

  • 错误原因:因为order_no 进行了范围匹配,搜索条件中的索引字段存在范围查询,就无法进行 索引合并

SELECT*FROM order_exp WHERE order_no> 'a' AND insert_time='a' AND order_status='b' AND expire_time='c';

联合索引 必须全部列都等值匹配

联合索引:UNIQUE KEY u_idx_day_status (insert_time,order_status,expire_time) USING BTREE

错误案例:

  • 错误原因:联合索引必须 全部列都要等值匹配,该SQL缺少了 insert_time

SELECT*FROM order_exp WHERE order_status='b' AND expire_time='c';

正确案例:

SELECT*FROM order_exp WHERE insert_time='a' AND order_status='b' AND expire_time='c';

条件2 - 主键ID范围匹配

  • 主键ID 进行范围匹配时,是可以满足 交集合并条件的
    • SELECT * FROM order_exp WHERE id > 100 AND insert_time = 'a';

Union合并(并集合并)

  • 适用于使用 不同索引的搜索条件之间使用OR

并集合并满足条件

  • 以下三个条件任意一个条件 都可满足 并集合并条件

条件1 - 等值匹配

SELECT * FROM order_exp WHERE order_no = 'a' OR expire_time = 'b'

条件2 - 主键ID范围匹配

SELECT * FROM order_exp WHERE id > 100 OR insert_time = 'a';

条件3 - 通过 交集合并后的搜索条件

SELECT * FROM order_exp WHERE insert_time = 'a' AND order_status = 'b' AND expire_time = 'c' OR (order_no = 'a' AND expire_time = 'b');

合并流程:

  1. 先获取 OR右边条件 (order_no = ‘a’ AND expire_time = ‘b’),通过 order_no 和 expire_time 获取 交集合并的主键集合
  2. OR左边条件 insert_time = ‘a’ AND order_status = ‘b’ AND expire_time = ‘c’ ,这3个字段刚好满足 联合索引所有字段等值匹配,通过这3个字段取得 交集合并的主键集合
  3. 把2个交集合并的主键集合 在进行 并集合并,然后在进行回表操作

Sort-Union合并(排序并集合并)

  • Union 并集合并的条件较为苛刻,必须保证每个二级索引都要等值匹配,因此 MySQL就开发出了 新的 Sort-Union 排序并集合并。

无法使用Union的SQL

SELECT * FROM order_exp WHERE order_no< 'a' OR expire_time> 'z'

  • 虽然order_no 和expire_time 都是二级单值索引,但这里并不是等值匹配,所以无法满足 Union并集

Sort-Union排序并集合并思路

  1. 先通过 各个单值索引(如:order_no、expire_time)单独查询出 主键ID结果,不回表
  2. 把所有主键ID结果 进行排序,将排序后的结果集 进行合并,合并后再回表查询