HarmonyOS 鸿蒙Next：MySQL中为什么要使用索引合并(Index Merge)？

HarmonyOS 鸿蒙Next：MySQL中为什么要使用索引合并(Index Merge)？ 摘要：本文介绍了索引合并（Index Merge）包含的三种类型，即交集(intersection)、并集(union)和排序并集(sort-union)，以及索引合并的实现原理、场景约束与通过案例验证的优缺点。

本文分享自华为云社区《【华为云MySQL技术专栏】MySQL中为什么要使用索引合并(Index Merge)？》，作者：武文斌。

在生产环境中，MySQL语句的where查询通常会包含多个条件判断，以AND或OR操作进行连接。然而，对一个表进行查询最多只能利用该表上的一个索引，其他条件需要在回表查询时进行判断（不考虑覆盖索引的情况）。当回表的记录数很多时，需要进行大量的随机IO，这可能导致查询性能下降。因此，MySQL 5.x 版本推出索引合并(Index Merge)来解决该问题。

本文将基于MySQL8.0.22版本对MySQL的索引合并功能、实现原理及场景约束进行详细介绍，同时也会结合原理对其优缺点进行浅析，并通过例子进行验证。

什么是Index Merge？

索引合并是通过对一个表同时使用多个索引进行条件扫描，并将满足条件的多个主键集合取交集或并集后再进行回表，可以提升查询效率。

索引合并主要包含交集(intersection)，并集(union)和排序并集(sort-union)三种类型：

• intersection：将基于多个索引扫描的结果集取交集后返回给用户
• union：将基于多个索引扫描的结果集取并集后返回给用户
• sort-union：与union类似，不同的是sort-union会对结果集进行排序，随后再返回给用户

MySQL提供了四个开关（index_merge、index_merge_intersection、index_merge_union以及index_merge_sort_union）对上述三种索引合并类型提供支持，可以通过修改optimizer_switch系统参数中的四个开关标识来控制索引合并特性的使用。

假设创建表T，并插入如下数据：

CREATE TABLE T(
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int NOT NULL,
  `b` char(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`) USING BTREE,
  KEY `idx_b` (`b`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=1;

INSERT INTO T (a, b) VALUES (1, 'A'), (2, 'B'),(3, 'C'),(4, 'B'),(1, 'C');

默认情况下，四个开关均为开启状态。如果需要单独使用某个合并类型，需设置index_merge=off，并将相应待启用的合并类型标识（例如，index_merge_sort_union）设置为on。

开关开启后，可通过EXPLAIN执行计划查看当前查询语句是否使用了索引合并。

Index Merge原理

1. Index Merge Intersection

Index Merge Intersection会在使用到的多个索引上同时进行扫描，并取这些扫描结果的交集作为最终结果集。

以“SELECT * FROM T WHERE a=1 AND b=‘C’;”语句为例：

• 未使用索引合并时，MySQL利用索引idx_a获取到满足条件a=1的所有主键id，根据主键id进行回表查询到相关记录，随后再使用条件b='C’对这些记录进行判断，获取最终查询结果。

• 使用索引合并时，MySQL分别利用索引idx_a和idx_b获取满足条件a=1和b='C’的主键id集合setA和setB。随后取setA和setB中主键id的交集setC，并使用setC中主键id进行回表，获取最终查询结果。

2. Index Merge Union

Index Merge Union会在使用到的多个索引上同时进行扫描，并取这些扫描结果的并集作为最终结果集。

以“SELECT * FROM T WHERE a=1 OR b=‘B’;”语句为例：

• 未使用索引合并时，MySQL通过全表扫描获取所有记录信息，随后再使用条件a=1和b='B’对这些记录进行判断，获取最终查询结果。

• 使用索引合并算法时，MySQL分别利用索引idx_a和idx_b获取满足条件a=1和b='B’的主键id集合setA和setB。随后，取setA和setB中主键id的并集setC，并使用setC中主键id进行回表，获取最终查询结果。

3. Index Merge Sort-Union

Sort-Union索引合并与Union索引合并原理相似，只是比单纯的Union索引合并多了一步对二级索引记录的主键id排序的过程。由OR连接的多个范围查询条件组成的WHERE子句不满足Union算法时，优化器会考虑使用Sort-Union算法。

应用场景约束

1. 总体约束

• Index Merge不能应用于全文索引（Fulltext Index）。
• Index Merge只能合并同一个表的索引扫描结果，不能跨表合并。

以上约束适用于Intersection，Union和Sort-Union三种合并类型。此外，Intersection和Union存在特殊的场景约束。

2. Index Merge Intersection

使用Intersection要求AND连接的每个条件必须是如下形式之一：

• 当索引包含多个列时，每个列都必须被如下等值条件覆盖，不允许出现范围查询。若使用索引为联合索引时，每个列都必须等值匹配，不能出现只匹配部分列的情况。

• 若过滤条件中存在主键列，主键列可以进行范围匹配。

3. Index Merge Union

使用Union要求OR连接的每个条件，必须是如下形式之一：

• 当索引包含多个列时，则每个列都必须被如下等值条件覆盖，不允许出现范围查询。若使用索引为联合索引时，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。

• 若过滤条件中存在主键列，主键列可以进行范围匹配。

Index Merge的优缺点

• Index Merge Intersection在使用到的多个索引上同时进行扫描，并取这些扫描结果的并集作为最终结果集。

• Index Merge Union在使用到的多个索引上同时进行扫描，并取这些扫描结果的并集作为最终结果集。

• Index Merge Sort-Union比单纯的Union索引合并多了一步对索引记录的主键id排序的过程。

我们以Index Merge Union为例，对上述分析进行验证。

场景构造

# 创建表
CREATE TABLE T(
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int NOT NULL,
  `b` char(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`) USING BTREE,
  KEY `idx_b` (`b`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=1;

# 插入数据
DELIMITER $$
CREATE PROCEDURE insertT()
BEGIN
DECLARE i INT DEFAULT 0;
START TRANSACTION;
WHILE i<=100000 do
if (i%100 = 0) then
INSERT INTO T (a, b) VALUES (10,CHAR(rand()*(90-65)+65));
else
INSERT INTO T (a, b) VALUES (i,CHAR(rand()*(90-65)+65));
end if;
SET i=i+1;
END WHILE;
COMMIT;
END$$
DELIMITER ;
call insertT();

# 执行测试语句
SQL1: SELECT * FROM T WHERE a=101 OR b='A';
SQL2: SELECT /*+ NO_INDEX_MERGE(T idx_a,idx_b) */ * FROM T WHERE a=101 OR b='A';
SQL3: SELECT * FROM T WHERE a=10 OR b='A';
SQL4: SELECT /*+ NO_INDEX_MERGE(T idx_a,idx_b) */ * FROM T WHERE a=10 OR b='A';

执行结果及分析

每条语句查询5次，去掉最大值和最小值，取剩余三次结果平均值。4条语句查询结果如下：

对比使用索引合并的SQL1和未使用索引合并的SQL2的查询结果可知，使用索引合并的SQL1具有更高的查询效率。

总结

本文介绍了索引合并（Index Merge）包含的三种类型，即交集(intersection)、并集(union)和排序并集(sort-union)，以及索引合并的实现原理、场景约束与通过案例验证的优缺点。在实际使用中，当查询条件列较多且无法使用联合索引时，就可以考虑使用索引合并，利用多个索引加速查询。但要注意，索引合并并非在任何场景下均具有较好的效果，需要结合具体的数据分布进行算法的选择。

[点击关注，第一时间了解华为云新鲜技术~]