MySQL使用索引合并(Index?Merge)提高查詢效率
在生產環(huán)境中，MySQL語句的where查詢通常會包含多個條件判斷，以AND或OR操作進行連接。然而，對一個表進行查詢最多只能利用該表上的一個索引，其他條件需要在回表查詢時進行判斷（不考慮覆蓋索引的情況）。當回表的記錄數(shù)很多時，需要進行大量的隨機IO，這可能導致查詢性能下降。因此，MySQL 5.x 版本推出索引合并(Index Merge)來解決該問題。
本文將基于MySQL 8.0.22版本對MySQL的索引合并功能、實現(xiàn)原理及場景約束進行詳細介紹，同時也會結合原理對其優(yōu)缺點進行淺析，并通過例子進行驗證。
什么是索引合并(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ǒng)參數(shù)中的四個開關標識來控制索引合并特性的使用。
假設創(chuàng)建表T，并插入如下數(shù)據：
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');
默認情況下，四個開關均為開啟狀態(tài)。如果需要單獨使用某個合并類型，需設置index_merge=off，并將相應待啟用的合并類型標識（例如，index_merge_sort_union）設置為on。
開關開啟后，可通過EXPLAIN執(zhí)行計劃查看當前查詢語句是否使用了索引合并。
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，可以忽略索引合并優(yōu)化：
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)
執(zhí)行流程如下：
圖1 SELECT * FROM T WHERE a=1 AND b='C';執(zhí)行流程
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)
執(zhí)行流程如下：
圖2 SELECT * FROM T WHERE a=1 OR b='B';執(zhí)行流程
3. Index Merge Sort-Union
Sort-Union索引合并與Union索引合并原理相似，只是比單純的Union索引合并多了一步對二級索引記錄的主鍵id排序的過程。由OR連接的多個范圍查詢條件組成的WHERE子句不滿足Union算法時，優(yōu)化器會考慮使用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) 當索引包含多個列時，每個列都必須被如下等值條件覆蓋，不允許出現(xiàn)范圍查詢。若使用索引為聯(lián)合索引時，每個列都必須等值匹配，不能出現(xiàn)只匹配部分列的情況。
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) 當索引包含多個列時，則每個列都必須被如下等值條件覆蓋，不允許出現(xiàn)范圍查詢。若使用索引為聯(lián)合索引時，在聯(lián)合索引中的每個列都必須等值匹配，不能出現(xiàn)只匹配部分列的情況。
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的優(yōu)缺點
• Index Merge Intersection在使用到的多個索引上同時進行掃描，并取這些掃描結果的并集作為最終結果集。
當優(yōu)化器根據搜索條件從某個索引中獲取的記錄數(shù)極多時，適合使用Intersection對取交集后的主鍵id以順序I/O進行回表，其開銷遠小于使用隨機IO進行回表。反之，當根據搜索條件掃描出的記錄極少時，因為需要多一步合并操作，Intersection反而不占優(yōu)勢。在8.0.22版本，對于AND連接的點查場景，通過建立聯(lián)合索引可以更好的減少回表。
• Index Merge Union在使用到的多個索引上同時進行掃描，并取這些掃描結果的并集作為最終結果集。
當優(yōu)化器根據搜索條件從某個索引中獲取的記錄數(shù)比較少，通過Union索引合并后進行訪問的代價比全表掃描更小時，使用Union的效果才會更優(yōu)。
• Index Merge Sort-Union比單純的Union索引合并多了一步對索引記錄的主鍵id排序的過程。
當優(yōu)化器根據搜索條件從某個索引中獲取的記錄數(shù)比較少的時，對這些索引記錄的主鍵id進行排序的成本不高，此時可以加速查詢。反之，當需要排序的記錄過多時，該算法的查詢效率不一定更優(yōu)。
我們以Index Merge Union為例，對上述分析進行驗證。
1. 場景構造
# 創(chuàng)建表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; # 插入數(shù)據 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(); # 執(zhí)行測試語句 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. 執(zhí)行結果及分析
每條語句查詢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行數(shù)據，隨后再進行條件判斷。而使用索引合并時，通過合并兩個索引篩選出的主鍵id集合，篩選出2056個符合條件的主鍵id, 隨后回表獲取最終的數(shù)據。這個環(huán)節(jié)中，索引合并大大減少了需要訪問的記錄數(shù)量。
此外，從SQL1和SQL3的查詢結果也可以看出，數(shù)據分布也會影響索引合并的效果。相同的SQL模板類型，根據匹配數(shù)值的不同，查詢時間存在差異。如需要合并的主鍵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)，以及索引合并的實現(xiàn)原理、場景約束與通過案例驗證的優(yōu)缺點。在實際使用中，當查詢條件列較多且無法使用聯(lián)合索引時，就可以考慮使用索引合并，利用多個索引加速查詢。但要注意，索引合并并非在任何場景下均具有較好的效果，需要結合具體的數(shù)據分布進行算法的選擇。
到此這篇關于MySQL使用索引合并(Index Merge)提高查詢效率的文章就介紹到這了,更多相關MySQL索引合并優(yōu)化及底層原理內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了mysql按照時間分組查詢的語句,非常實用，sql語句簡單易懂，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-09-09
這篇文章主要介紹了深入了解Mysql邏輯架構的原因，幫助大家更好的理解和使用MySQL數(shù)據庫，感興趣的朋友可以了解下2020-12-12
這篇文章主要介紹了MySQL 5.0.16亂碼問題的解決方法，需要的朋友可以參考下2015-10-10
MySQL是一個關系型數(shù)據庫管理系統(tǒng)，由瑞典MySQL AB 公司開發(fā)，目前屬于 Oracle 旗下產品。這篇文章主要介紹了Mysql基礎語法的使用,需要的朋友可以參考下2018-07-07
一文深入理解MySQL中的UTF-8與UTF-8MB4字符集
在全球化的今天,數(shù)據的存儲與處理需要支持多種語言與字符集,對于 Web 應用程序和數(shù)據庫系統(tǒng)來說,字符集的選擇尤為重要,特別是在處理包含多種語言字符（如中文、阿拉伯文、表情符號等）的系統(tǒng)中,本文將深入探討 MySQL 中的兩個常見字符集：UTF-8 和 UTF-8MB42024-11-11
應國家等級保護三級安全要求，mysql 的 ssl 需要安全證書加密，本文主要介紹了mysql配置SSL證書登錄，感興趣的可以了解一下2021-09-09
這篇文章主要介紹了mysql中數(shù)據庫覆蓋導入的幾種方式總結，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-03-03
mysql建庫時提示Specified key was too long max key length is 1000
本文將詳細提供mysql建庫時提示Specified key was too long max key length is 1000 bytes的問題的解決方法，有需求的朋友可以參考2012-11-11
在mysql 中，索引可以分為兩種類型 hash索引和 btree索引。這篇文章主要介紹了mysql索引的相關知識，非常不錯，具有參考借鑒價值，感興趣的朋友一起看看吧2016-09-09
這篇文章主要介紹了mysql5.7單實例自啟動服務配置的過程，附含配置源碼，有需要的朋友可以借鑒參考下，希望可以有所幫助，感謝閱讀2021-09-09