IT评测·应用市场-qidao123.com技术社区

标题: MySQL中为什么要利用索引合并(Index Merge)？ [打印本页]

作者: 大号在练葵花宝典 时间: 2024-7-12 12:16
标题: MySQL中为什么要利用索引合并(Index Merge)？
本文分享自华为云社区《【华为云MySQL技术专栏】MySQL中为什么要利用索引合并(Index Merge)？》，作者：GaussDB 数据库。
在生产环境中，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)

复制代码

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. 场景构造

key_par1 = const1 AND key_par2 = const2 ... AND key_partN = constN

复制代码

未利用索引合并的SQL2代码示例：

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)

复制代码

未利用索引合并时，SQL2语句需要花费约23ms来扫描全表100001行数据，随后再举行条件判断。而利用索引合并时，通过合并两个索引筛选出的主键id集合，筛选出2056个符合条件的主键id, 随后回表获取最终的数据。这个环节中，索引合并大大减少了需要访问的记录数量。
此外，从SQL1和SQL3的查询结果也可以看出，数据分布也会影响索引合并的效果。相同的SQL模板类型，根据匹配数值的不同，查询时间存在差异。如需要合并的主键id集合越小，需要回表的主键id越少，查询时间越短。

key_par1 = const1 OR key_par2 = const2 ... OR key_partN = constN

复制代码

总结

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

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

欢迎光临 IT评测·应用市场-qidao123.com技术社区 (https://dis.qidao123.com/)