本文分享自華為雲社群《【華為雲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)
應用場景約束
1. 總體約束
以上約束適用於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() 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';
2. 執行結果及分析
每條語句查詢5次，去掉最大值和最小值，取剩餘三次結果平均值。4條語句查詢結果如下：
|
測試語句 |
第一次查詢/ms |
第二次查詢/ms |
第三次查詢/ms |
第四次查詢/ms |
第五次查詢/ms |
平均值/ms |
|---|---|---|---|---|---|---|
|
SQL1 |
5.481 |
5.422 |
5.117 |
4.892 |
5.426 |
5.322 |
|
SQL2 |
31.129 |
32.645 |
30.943 |
31.142 |
32.625 |
31.632 |
|
SQL3 |
7.872 |
7.200 |
7.824 |
7.955 |
7.949 |
7.882 |
|
SQL4 |
31.139 |
33.318 |
31.476 |
31.645 |
31.27 |
31.464 |
對比使用索引合併的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)，以及索引合併的實現原理、場景約束與透過案例驗證的優缺點。在實際使用中，當查詢條件列較多且無法使用聯合索引時，就可以考慮使用索引合併，利用多個索引加速查詢。但要注意，索引合併並非在任何場景下均具有較好的效果，需要結合具體的資料分佈進行演算法的選擇。
點選關注，第一時間瞭解華為雲新鮮技術~