04-Mysql执行原理之索引合并详解.pdf

索引合并

一条sql查询涉及到多个索引的操作。

Intersection合并(交集)

一条sql查询，涉及到两个二级索引，并将两个二级索引查询到的结果取交集。

  1. SELECT * FROM order_exp WHERE order_no = 'a' AND expire_time = 'b';

为啥不直接使用idx_order_no或者idx_insert_time只根据某个搜索条件去读取一个二级 索引，然后回表后再过滤另外一个搜索条件呢？

成本计算

回表查询是随机IO,而进行二级索引查询是顺序IO，此效率相差极大。如果读取一个二级索引得到的值太多，超过再读取一个二级索引的时间，即表示此时两个二级索引的结果取交集的成本要小于先读一个二级索引，再根据主键id进行回表查询，再过滤第二个条件

使用场景

情况一：等值匹配

情况二、主键列可以是范围匹配

上边说的情况一和情况二只是发生Intersection索引合并的必要条件，不是充分条 件。也就是说即使情况一、情况二成立，也不一定发生Intersection索引合并，这得看优 化器的心情。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致 回表开销太大，而通过Intersection索引合并后需要回表的记录数大大减少时才会使用 Intersection索引合并。

Union合并 （并集）

使用场景

等值匹配

分析同Intersection合并

主键列可以是范围匹配

分析同Intersection合并

使用Intersection索引合并的搜索条件

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

先按照搜索条件order_no = ‘a’ AND expire_time = ‘b’从索引idx_order_no和 idx_expire_time中使用Intersection索引合并的方式得到一个主键集合。
再按照搜索条件 insert_time = ‘a’ AND order_status = ‘b’ AND expire_time = ‘c’从联合索引u_idx_day_status中得到另一个主键集合。 采用Union索引合并的方式把上述两个主键集合取并集，然后进行回表操作，将结果返回 给用户。
当然，查询条件符合了这些情况也不一定就会采用Union索引合并，也得看优化器的心 情。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过Union 索引合并后进行访问的代价比全表扫描更小时才会使用Union索引合并。

Sort-Union合并

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

先根据order_no< ‘a’条件从idx_order_no二级索引中获取记录，并按照记录的主键值进 行排序 再根据expire_time> ‘z’条件从idx_expire_time二级索引中获取记录，并按照记录的主 键值进行排序 因为上述的两个二级索引主键值都是排好序的，剩下的操作和Union索引合并方式就一样 了。
上述这种先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方 式称之为Sort-Union索引合并，很显然，这种Sort-Union索引合并比单纯的Union索引合 并多了一步对二级索引记录的主键值排序的过程。

联合索引替代Intersection索引合并