在生产环境中，MySQL 语句的 where 查询通常会包含多个条件判断，以 AND 或 OR 操作进行连接。然而，对一个表进行查询最多只能利用该表上的一个索引，其他条件需要在回表查询时进行判断（不考虑覆盖索引的情况）。当回表的记录数很多时，需要进行大量的随机 IO，这可能导致查询性能下降。因此，MySQL 5.x 版本推出索引合并(Index Merge)来解决该问题。
本文将基于 MySQL 8.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 执行计划查看当前查询语句是否使用了索引合并。
mysql> explain SELECT * FROM T WHERE a=1 OR b='B';+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+| 1 | SIMPLE | T | NULL | index_merge | idx_a,idx_b | idx_a,idx_b | 4,5 | NULL | 4 | 100.00 | Using union(idx_a,idx_b); Using where |+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+1 row in set, 1 warning (0.01 sec)
复制代码
上面代码显示 type 类型为 index_merge，表示使用了索引合并。key 列显示使用到的所有索引名称，该语句中同时使用了 idx_a 和 idx_b 两个索引完成查询。Extra 列显示具体使用了哪种类型的索引合并，该语句显示 Using union(...)，表示索引合并类型为 union。
此外，可以使用 index_merge/no_index_merge 给查询语句添加 hint，强制 SQL 语句使用/不使用索引合并。
• 如果查询默认未使用索引合并，可以通过添加 index_merge 强制指定：
mysql> EXPLAIN SELECT * FROM T WHERE a=2 AND b='A';+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+| 1 | SIMPLE | T | NULL | ref | idx_a,idx_b | idx_a | 4 | const | 1 | 20.00 | Using where |+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT /*+ INDEX_MERGE(T idx_a,idx_b) */ * FROM T WHERE a=2 AND b='A';+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+--------------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+--------------------------------------------------------+| 1 | SIMPLE | T | NULL | index_merge | idx_a,idx_b | idx_a,idx_b | 4,5 | NULL | 1 | 100.00 | Using intersect(idx_a,idx_b); Using where; Using index |+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+--------------------------------------------------------+1 row in set, 1 warning (0.00 sec)
复制代码
• 使用 no_index_merge 给查询语句添加 hint，可以忽略索引合并优化：
mysql> EXPLAIN SELECT * FROM T WHERE a=1 OR b='A';+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+| 1 | SIMPLE | T | NULL | index_merge | idx_a,idx_b | idx_a,idx_b | 4,5 | NULL | 3 | 100.00 | Using union(idx_a,idx_b); Using where |+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+1 row in set, 1 warning (0.00 sec)mysql> EXPLAIN SELECT /*+ NO_INDEX_MERGE(T idx_a,idx_b) */ * FROM T WHERE a=1 OR b='A';+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | T | NULL | ALL | idx_a,idx_b | NULL | NULL | NULL | 5 | 36.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
复制代码
索引合并(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> explain SELECT /*+ NO_INDEX_MERGE(T idx_a,idx_b) */ * FROM T WHERE a=1 AND b='C';+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+| 1 | SIMPLE | T | NULL | ref | idx_a,idx_b | idx_a | 4 | const | 2 | 40.00 | Using where |+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
复制代码
• 使用索引合并时，MySQL 分别利用索引 idx_a 和 idx_b 获取满足条件 a=1 和 b='C'的主键 id 集合 setA 和 setB。随后取 setA 和 setB 中主键 id 的交集 setC，并使用 setC 中主键 id 进行回表，获取最终查询结果。
mysql> explain SELECT * FROM T WHERE a=1 AND b='C';+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+--------------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+--------------------------------------------------------+| 1 | SIMPLE | T | NULL | index_merge | idx_a,idx_b | idx_a,idx_b | 4,5 | NULL | 1 | 100.00 | Using intersect(idx_a,idx_b); Using where; Using index |+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+--------------------------------------------------------+1 row in set, 1 warning (0.00 sec)
复制代码
执行流程如下：
图 1 SELECT * FROM T WHERE a=1 AND b='C';执行流程
2. Index Merge Union
Index Merge Union 会在使用到的多个索引上同时进行扫描，并取这些扫描结果的并集作为最终结果集。
以“SELECT * FROM T WHERE a=1 OR b='B'; ”语句为例：
• 未使用索引合并时，MySQL 通过全表扫描获取所有记录信息，随后再使用条件 a=1 和 b='B'对这些记录进行判断，获取最终查询结果。
mysql> EXPLAIN SELECT /*+ NO_INDEX_MERGE(T idx_a,idx_b) */ * FROM T WHERE a=1 OR b='B';+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | T | NULL | ALL | idx_a,idx_b | NULL | NULL | NULL | 5 | 50.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
复制代码
• 使用索引合并算法时，MySQL 分别利用索引 idx_a 和 idx_b 获取满足条件 a=1 和 b='B'的主键 id 集合 setA 和 setB。随后，取 setA 和 setB 中主键 id 的并集 setC，并使用 setC 中主键 id 进行回表，获取最终查询结果。
mysql> EXPLAIN SELECT * FROM T WHERE a=1 OR b='B';+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+| 1 | SIMPLE | T | NULL | index_merge | idx_a,idx_b | idx_a,idx_b | 4,5 | NULL | 4 | 100.00 | Using union(idx_a,idx_b); Using where |+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+1 row in set, 1 warning (0.01 sec)
复制代码
执行流程如下：
图 2 SELECT * FROM T WHERE a=1 OR b='B';执行流程
3. Index Merge Sort-Union
Sort-Union 索引合并与 Union 索引合并原理相似，只是比单纯的 Union 索引合并多了一步对二级索引记录的主键 id 排序的过程。由 OR 连接的多个范围查询条件组成的 WHERE 子句不满足 Union 算法时，优化器会考虑使用 Sort-Union 算法。例如：
mysql> EXPLAIN SELECT * FROM T WHERE a<3 OR b<'B';+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+--------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+--------------------------------------------+| 1 | SIMPLE | T | NULL | index_merge | idx_a,idx_b | idx_a,idx_b | 4,5 | NULL | 4 | 100.00 | Using sort_union(idx_a,idx_b); Using where |+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+--------------------------------------------+1 row in set, 1 warning (0.00 sec)
复制代码
应用场景约束
1. 总体约束
• Index Merge 不能应用于全文索引（Fulltext Index）。
• Index Merge 只能合并同一个表的索引扫描结果，不能跨表合并。
以上约束适用于 Intersection，Union 和 Sort-Union 三种合并类型。此外，Intersection 和 Union 存在特殊的场景约束。
2. Index Merge Intersection
使用 Intersection 要求 AND 连接的每个条件必须是如下形式之一：
(1) 当索引包含多个列时，每个列都必须被如下等值条件覆盖，不允许出现范围查询。若使用索引为联合索引时，每个列都必须等值匹配，不能出现只匹配部分列的情况。
key_par1 = const1 AND key_par2 = const2 ... AND key_partN = constN
复制代码
(2) 若过滤条件中存在主键列，主键列可以进行范围匹配。
mysql> EXPLAIN SELECT * FROM T WHERE id<3 AND b='A';+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+---------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+---------------------------------------------+| 1 | SIMPLE | T | NULL | index_merge | PRIMARY,idx_b | idx_b,PRIMARY | 9,4 | NULL | 1 | 100.00 | Using intersect(idx_b,PRIMARY); Using where |+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+---------------------------------------------+1 row in set, 1 warning (0.00 sec)
复制代码
上述的要求，本质上是为了确保索引取出的记录是按照主键 id 有序排列的，因为 Index Merge Intersection 对两个有序集合取交集更简单。同时，主键有序的情况下，回表将不再是单纯的随机 IO，回表的效率也会更高。
3. Index Merge Union
使用 Union 要求 OR 连接的每个条件，必须是如下形式之一：
(1) 当索引包含多个列时，则每个列都必须被如下等值条件覆盖，不允许出现范围查询。若使用索引为联合索引时，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。
key_par1 = const1 OR key_par2 = const2 ... OR key_partN = constN
复制代码
（2) 若过滤条件中存在主键列，主键列可以进行范围匹配。
mysql> EXPLAIN SELECT * FROM T WHERE id>3 OR b='A';+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+| 1 | SIMPLE | T | NULL | index_merge | PRIMARY,idx_b | PRIMARY,idx_b | 4,5 | NULL | 3 | 100.00 | Using union(PRIMARY,idx_b); Using where |+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+1 row in set, 1 warning (0.00 sec)
复制代码
Index Merge 的优缺点
• Index Merge Intersection 在使用到的多个索引上同时进行扫描，并取这些扫描结果的并集作为最终结果集。
当优化器根据搜索条件从某个索引中获取的记录数极多时，适合使用 Intersection 对取交集后的主键 id 以顺序 I/O 进行回表，其开销远小于使用随机 IO 进行回表。反之，当根据搜索条件扫描出的记录极少时，因为需要多一步合并操作，Intersection 反而不占优势。在 8.0.22 版本，对于 AND 连接的点查场景，通过建立联合索引可以更好的减少回表。
• Index Merge Union 在使用到的多个索引上同时进行扫描，并取这些扫描结果的并集作为最终结果集。
当优化器根据搜索条件从某个索引中获取的记录数比较少，通过 Union 索引合并后进行访问的代价比全表扫描更小时，使用 Union 的效果才会更优。
• Index Merge Sort-Union 比单纯的 Union 索引合并多了一步对索引记录的主键 id 排序的过程。
当优化器根据搜索条件从某个索引中获取的记录数比较少的时，对这些索引记录的主键 id 进行排序的成本不高，此时可以加速查询。反之，当需要排序的记录过多时，该算法的查询效率不一定更优。
我们以 Index Merge Union 为例，对上述分析进行验证。
1. 场景构造
# 创建表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()BEGINDECLARE i INT DEFAULT 0;START TRANSACTION;WHILE i<=100000 doif (i%100 = 0) thenINSERT INTO T (a, b) VALUES (10,CHAR(rand()*(90-65)+65));elseINSERT 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';
复制代码
2. 执行结果及分析
每条语句查询 5 次，去掉最大值和最小值，取剩余三次结果平均值。4 条语句查询结果如下：
对比使用索引合并的 SQL1 和未使用索引合并的 SQL2 的查询结果可知，使用索引合并的 SQL1 具有更高的查询效率，这点从语句的 explain analyze 分析中也可以看出：
使用索引合并的 SQL1 代码示例：
EXPLAIN ANALYZE SELECT * FROM T WHERE a=101 OR b='A';-> Filter: ((t.a = 101) or (t.b = 'A')) (cost=717.14 rows=2056) (actual time=0.064..5.481 rows=2056 loops=1)-> Index range scan on T using union(idx_a,idx_b) (cost=717.14 rows=2056) (actual time=0.062..5.120 rows=2056 loops=1)
复制代码
未使用索引合并的 SQL2 代码示例：
EXPLAIN ANALYZE SELECT /*+ NO_INDEX_MERGE(T idx_a,idx_b) */ * FROM T WHERE a=101 OR b='A';-> Filter: ((t.a = 101) or (t.b = 'A')) (cost=10098.75 rows=10043) (actual time=0.038..31.129 rows=2056 loops=1)-> Table scan on T (cost=10098.75 rows=100425) (actual time=0.031..22.967 rows=100001 loops=1)
复制代码
未使用索引合并时，SQL2 语句需要花费约 23ms 来扫描全表 100001 行数据，随后再进行条件判断。而使用索引合并时，通过合并两个索引筛选出的主键 id 集合，筛选出 2056 个符合条件的主键 id, 随后回表获取最终的数据。这个环节中，索引合并大大减少了需要访问的记录数量。
此外，从 SQL1 和 SQL3 的查询结果也可以看出，数据分布也会影响索引合并的效果。相同的 SQL 模板类型，根据匹配数值的不同，查询时间存在差异。如需要合并的主键 id 集合越小，需要回表的主键 id 越少，查询时间越短。
EXPLAIN ANALYZE SELECT * FROM T WHERE a=101 OR b='A';-> Filter: ((t.a = 101) or (t.b = 'A')) (cost=717.14 rows=2056) (actual time=0.064..5.481 rows=2056 loops=1) -> Index range scan on T using union(idx_a,idx_b) (cost=717.14 rows=2056) (actual time=0.062..5.120 rows=2056 loops=1)
EXPLAIN ANALYZE SELECT * FROM T WHERE a=10 OR b='A';-> Filter: ((t.a = 10) or (t.b = 'A')) (cost=983.00 rows=3057) (actual time=0.070..7.872 rows=3035 loops=1) -> Index range scan on T using union(idx_a,idx_b) (cost=983.00 rows=3057) (actual time=0.068..7.496 rows=3035 loops=1)
复制代码
总结
本文介绍了索引合并（Index Merge）包含的三种类型，即交集(intersection)、并集(union)和排序并集(sort-union)，以及索引合并的实现原理、场景约束与通过案例验证的优缺点。在实际使用中，当查询条件列较多且无法使用联合索引时，就可以考虑使用索引合并，利用多个索引加速查询。但要注意，索引合并并非在任何场景下均具有较好的效果，需要结合具体的数据分布进行算法的选择。
文章转载自：华为云开发者联盟
原文链接：https://www.cnblogs.com/huaweiyun/p/18297565
体验地址：http://www.jnpfsoft.com/?from=infoq
评论