我们都知道，MySQL 在执行查询是都会根据现有的查询方案（全表、索引）进行成本分析，最后选出一个成本最低的作为执行方案。在一般情况下执行一个查询时最多只会用到单个二级索引，但存在有特殊情况，在这些特殊情况下也可能在一个查询中使用到多个二级索引，MySQL 中这种使用到多个索引来完成一次查询的执行方法称之为 “索引合并”。

我们可以通过 optimizer trace 输出的文本信息中查看：

  1. "analyzing_roworder_intersect": {
  2.     "usable": false,
  3.      "cause": "too_few_roworder_scans"
  4. }

为了方便演示，我们先创建一张表：

  1. CREATE TABLE `order_exp`  (
  2.   `id` bigint(255) NOT NULL AUTO_INCREMENT COMMENT '订单主键',
  3.   `order_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单号',
  4.   `order_note` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单描述',
  5.   `insert_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '订单插入时间',
  6.   `expire_duration` bigint(255) NOT NULL COMMENT '订单的过期时长，单位秒',
  7.   `expire_time` datetime(0) NOT NULL COMMENT '订单过期时间',
  8.   `order_status` smallint(6) NOT NULL COMMENT '订单的状态,0:未支付;:已支付;-1:已过期，关闭',
  9.   PRIMARY KEY (`id`) USING BTREE,
  10.   UNIQUE INDEX `u_idx_day_status`(`insert_time`, `expire_time`, `order_status`) USING BTREE,
  11.   INDEX `idx_order_no`(`order_no`) USING BTREE,
  12.   INDEX `idx_expire_time`(`expire_time`) USING BTREE
  13. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

Intersection 合并

Intersection 翻译过来的意思是交集。我们知道二级索引的叶子节点存放的是聚簇索引中的主键值，这里是说某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集，比方说下边这个查询：

SELECT * FROM order_exp WHERE order_no = ‘a’ AND expire_time = ‘b’

image.png

假设这个查询使用 Intersection 合并的方式执行的话，那这个过程就是这样的：

  1. 从idx_order_no二级索引对应的B+树中取出 order_no= ‘a’ 的相关记录。
  2. 从idx_insert_time二级索引对应的B+树中取出 insert_time= ‘b’ 的相关记录。
  3. 二级索引的记录都是由索引列 + 主键构成的，所以我们可以计算出这两个结果集中 id值的交集。按照上一步生成的 id 值列表进行回表操作，也就是从聚簇索引中把指定 id值的完整用户记录取出来，返回给用户。

细心的朋友可能会有这样一个疑问，对比直接从一个二级索引中取出数据直接回表取出记录，这不是多了一次 IO 操作吗？虽然读取多个二级索引比读取一个二级索引消耗性能，但是大部分情况下读取二级索引的操作是顺序I/O，而回表操作是随机I/O，所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。MySQL 在某些特定的情况下才可能会使用到 Intersection 索引合并，哪些情况呢？

  1. 等值匹配

二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。比如下边这个查询就不能进行 Intersection 索引合并

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

该查询是因为对 order_no 进行了范围匹配，所以这不能进行 Intersection 索引合并。Intersection 索引合并会把从多个二级索引中查询出的主键值求交集，如果从各个二级索引中查询的到的结果集本身就是已经按照主键排好序的，那么求交集的过程就很容易。假设某个查询使用 Intersection 索引合并的方式从 idx_order_no 和 idx_expire_time 这两个二级索引中获取到的主键值分别是：

从 idx_order_no 中获取到已经排好序的主键值：1、3、5 从 idx_expire_time 中获取到已经排好序的主键值：2、3、4

求交集的过程就是这样：逐个取出这两个结果集中最小的主键值，如果两个值相等，则加入最后的交集结果中；否则丢弃当前较小的主键值，再取该丢弃的主键值所在结果集的后面一个主键值来比较，直到某个结果集中的主键值用完了，时间复杂度是O(n)。但是如果从各个二级索引中查询出的结果集并不是按照主键排序的话，那就要先把结果集中的主键值排序完再来做上边的那个过程，就比较耗时了。按照有序的主键值去回表取记录有个专有名词：Rowid Ordered Retrieval，简称ROR。

  1. 主键列可以是范围匹配

对于 InnoDB 的二级索引来说，记录先是按照索引列进行排序，如果该二级索引是一个联合索引，那么会按照联合索引中的各个列依次排序。而二级索引的用户记录是由索引列 + 主键构成的，二级索引列的值相同的记录可能会有好多条，这些索引列的值相同的记录又是按照主键的值进行排序的。 所以重点来了，之所以在二级索引列都是等值匹配的情况下才可能使用 Intersection 索引合并，是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。

不仅是多个二级索引之间可以采用 Intersection 索引合并，索引合并也可以有聚簇索引参加，在搜索条件中有主键是范围匹配的情况下也可以使用 Intersection 索引合并索引合并

SELECT * FROM order_exp WHERE id > 100 AND insert_time = ‘a’


假设这个查询可以采用 Intersection 索引合并，我们理所当然的以为这个查询会分别按照
id > 100 这个条件从聚簇索引中获取一些记录，再通过 order_no= ‘a’ 这个条件从二级索引中获取一些记录，然后再求交集。其实这样就把问题复杂化了，没必要从聚簇索引中获取一次记录，别忘了二级索引的记录中都带有主键值的。所以可以在从 idx_order_no 中获取到的主键值上直接运用条件 id > 100 过滤就行了，这样多简单。所以涉及主键的搜索条件只不过是为了从别的二级索引得到的结果集中过滤记录，是不是等值匹配不重要。

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

Union 合并

我们在写查询语句时经常想把既符合某个搜索条件的记录取出来，也把符合另外的某个搜索条件的记录取出来，我们说这些不同的搜索条件之间是 OR 关系。有时候 OR 关系的不同搜索条件会使用到不同的索引，比方说这样：

SELECT * FROM order_exp WHERE order_no = ‘a’ OR expire_time = ‘b

image.png

Union是并集的意思，适用于使用不同索引的搜索条件之间使用OR连接起来的情况。与Intersection 索引合并类似，MySQL在某些特定的情况下才可能会使用到 Union 索引合并

  1. 等值匹配
  2. 主键列可以是范围匹配
  3. 使用Intersection索引合并的搜索条件

这里我们主要分析一下第三种情况（前两种情况分析同上），搜索条件的某些部分使用Intersection 索引合并的方式得到的主键集合和其他方式得到的主键集合取并集，比方说这个查询：

SELECT * FROM order_exp WHERE insert_time = ‘a’ AND order_status = ‘b’ AND 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合并

Union 索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹配的条件下才可能被用到，比方说下边这个查询就无法使用到 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 索引中获取的二级索引记录的主键值也不是排好序的，但是 order_no< ‘a’ 和 expire_time> ‘z’ 这两个条件又特别让我们动心，所以我们可以这样：

  • 先根据 order_no< ‘a’ 条件从 idx_order_no 二级索引中获取记录，并按照记录的主键值进行排序
  • 再根据 expire_time> ‘z’ 条件从 idx_expire_time 二级索引中获取记录，并按照记录的键值进行排序

因为上述的两个二级索引主键值都是排好序的，剩下的操作和 Union 索引合并方式就一样了。这种先按照二级索引记录的主键值进行排序，之后按照 Union 索引合并方式执行的方式称之为 Sort-Union 索引合并。很显然，这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。

联合索引替代Intersection索引合并

上面不管是交集合并还是并集合并，都是 MySQL 基于我们已有的语句进行一个优化，尽可能地减少回表次数。使用索引合并根本的原因是这些索引是单独的 B+ 树索引，需要单独的 B+ 树进行搜索。既然如此，那我们为什么不把这两个索引合并到一起呢？使用联合索引进行查询简直是又快又好，既不用多读一棵B+树，也不用合并结果。