1. 前言
先問大家一個問題，在不考慮多表聯查這種復雜的查詢場景下，一個簡單的單表查詢，MySQL可以同時利用幾個索引？ ?
當初我學習MySQL的時候，天真的以為只要把WHERE條件涉及到的列全部加上索引，就可以提升查詢速度，這個想法其實大錯特錯。因為一般情況下，單表查詢MySQL只能利用一個索引，比如下面這個查詢，假設id是主鍵，a和b分別創建了索引，別天真的以為idx_a和idx_b都能發揮作用，其實不是的。
|
1
|
SELECT id,a,b FROM T WHERE a>100 AND b>200; |
因為idx_a索引只存儲了列a和id的值，無法判斷b>200條件是否成立，所以只能拿著id去回表查詢。 同樣idx_b索引只存儲了列b和id的值，無法判斷a>100條件是否成立，也只能拿著id去回表查詢。 可以看到，最大的開銷其實是回表操作，通過二級索引匹配到的數據越少，回表的開銷也就越低。所以理論上來說，a>100和b>200分別符合這兩個條件的記錄數越少，MySQL就會使用哪個索引。MySQL是如何判斷符合這些條件的記錄數量的呢？不也得老老實實的掃描全表嗎？MySQL采用預估的方式，通過表的統計數據或訪問表中少量的數據來進行預估，并分別計算使用這兩個索引進行查詢各自的成本是多少，最終選擇執行成本更低的索引方案。關于MySQL如何預估執行成本，不在本篇文章的討論范圍內，先跳過。 ?
我們假設最終MySQL使用idx_a索引，那么這個查詢過程其實是這樣的：
-
InnoDB從
idx_aB+樹中獲取到第一條a>100的記錄，拿記錄里的id值回表查詢。 -
回表查詢獲取到完整的用戶記錄，判斷
b>200是否成立，成立則返回給客戶端，否則丟棄該記錄。 -
InnoDB繼續從
idx_aB+樹中獲取到下一條a>100的記錄，重復前面的過程。
建立了這么多索引，每次查詢只使用一個，太可惜了不是嘛。能不能同時利用多個索引來完成查詢呢？可以的，但是條件有些嚴苛，這就是我們今天要介紹的索引合并Index Merge。
2. Index Merge
MySQL將這種使用多個索引來完成一次查詢的執行方法稱為 索引合并「index merge」。如何才能知道我們寫的SQL語句使用了索引合并呢？通過EXPLAIN分析一下就知道了，如果使用了索引合并，對應的type列顯示的值應該是index_merge，key列顯示用的到所有索引名稱，Extra列會顯示具體使用了哪種類型的索引合并。 如下所示，同時使用了idx_a和idx_b兩個索引完成查詢，且索引合并類型為Intersection。
| table | type | key | Extra |
|---|---|---|---|
| T | index_merge | idx_a,idx_b | Using intersect(idx_a,idx_b); Using where; Using index |
什么？索引合并還分類型？是的，MySQL目前共支持三種類型的索引合并，分別是：
| 索引合并類型 | 說明 |
|---|---|
| Intersection | 對多個二級索引里符合條件的主鍵值取交集合并 |
| Union | 對多個二級索引里符合條件的主鍵值去重后取并集合并 |
| Sort Union | 對多個二級索引里符合條件的主鍵值去重并排序后，再取并集合并 |
我們使用一個具體的例子，來分別演示下三種索引合并。假設有表T如下，id是主鍵，列a和列b分別創建索引。
|
1
2
3
4
5
6
7
|
CREATE TABLE T( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `a` INT NOT NULL, `b` CHAR(1) DEFAULT NULL, KEY `idx_a` (a) USING BTREE, KEY `idx_b` (b) USING BTREE)ENGINE=InnoDB AUTO_INCREMENT=1; |
大家可以寫個存儲過程，向表中批量插入記錄，我這里貼一下代碼，寫的很簡陋。
|
1
2
3
4
5
6
7
8
9
10
11
|
CREATE PROCEDURE insertT()BEGIN DECLARE i INT DEFAULT 0; START TRANSACTION; WHILE i<=10000 do INSERT INTO T (a, b) VALUES (i,CHAR(rand()*(90-65)+65)); SET i=i+1; END WHILE; COMMIT;END;call insertT(); |
列a和列b均是普通索引，值是允許重復的，大家可以多調用幾次存儲，最終的數據就是：a的值在一萬以內重復，b的值在A~Z之間重復，主鍵保持遞增。下面我們基于這張表的數據來演示。
2.1 Intersection
|
1
|
SELECT * FROM T WHERE a=1 AND b='A'; |
針對這個查詢，目前我們知道它可以有以下三種查詢方式：
- 全表掃描，判斷兩個條件是否匹配。
-
利用
idx_a索引將獲取到id回表查詢再判斷條件b是否達成。 -
利用
idx_b索引將獲取到id回表查詢再判斷條件a是否達成。
有了Intersection索引合并，MySQL其實還可以有第四種查詢方式，查詢過程是這樣的：
-
利用
idx_a索引將獲取到的id集合記作id_setA。 -
利用
idx_b索引將獲取到的id集合記作id_setB。 -
將
id_setA和id_setB取交集，記作id_set。 -
對
id_set回表查詢，將結果返回給客戶端。
這個過程描述的其實是有問題的，但是大概意思是對的，主要是幫助大家理解。對id取交集的過程，并不是這樣的，本質上MySQL并不會存儲這些id集合，因為數據量一大是很占用內存的，這個我們待會說。 ?
綜上所述，這種通過從多個索引中掃描到的記錄的主鍵值取交集后再回表查詢的方式，就是Intersection索引合并。EXPLAIN分析結果如下：
|
1
2
3
4
5
6
|
mysql> EXPLAIN SELECT * FROM T WHERE a=1 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,4 | NULL | 1 | 100.00 | Using intersect(idx_a,idx_b); Using where; Using index |+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+--------------------------------------------------------+ |
需要注意的是，使用Intersection索引合并是有條件的。如果使用到的索引都是二級索引的話，則要求通過二級索引取出的記錄是按照主鍵排好序的。為什么會有這個要求呢？主要是有以下兩個好處：
- 對兩個有序集合取交集更簡單。
- 主鍵有序的情況下，回表將不再是單純的隨機IO，回表的效率更高。
很顯然，我們這個查詢是能利用Intersection索引合并的。idx_a索引中是先根據a排序再根據id排序的，a=1的情況下，取出的記錄是按照id排好序的。idx_b索引中是先根據b排序再根據id排序的，b='A'的情況下，取出的記錄也是按照id排好序的。所以是符合要求的。 ?
最后，我們看一下MySQL從兩個集合中取交集的過程。假設idx_a過濾出的id是[1,3,5]，idx_b過濾出的id集合是[2,3,4]，取交集的過程其實是這樣的：
-
從
idx_a取出第一條記錄，id值是1。再從idx_b取出第一條記錄，id值是2，因為1<2所以id為1的那條記錄直接丟棄。 -
從
idx_a取出第二條記錄，id值是3，因為2<3，所以id為2的那條記錄直接丟棄。 -
從
idx_b取出第二條記錄，id值是3，因為3=3，所以拿3去回表查詢，結果返回給客戶端，同時id為3的兩條記錄也直接丟棄。 -
從
idx_a取出第三條記錄，id值是5。從idx_b取出第三條記錄，id值是4。因為4<5所以id為4的記錄被丟棄，又因為雙方都沒有記錄了，id為5的記錄也被丟棄，交集過程結束。
通過上述過程，現在你應該很清楚為啥MySQL要求二級索引返回的記錄必須根據主鍵排好序了吧，如此一來，整個求交集的過程將變得非常簡單，MySQL也無需使用額外的內存空間來保存這些id集合。
2.2 Union
|
1
|
SELECT * FROM T WHERE a=1 OR b='A'; |
針對這個查詢，我們是無法單獨使用idx_a或idx_b索引來完成的，因為它們的條件關系是OR，目前我們已知的查詢方式就一種：
- 全表掃描，判斷兩者條件滿足其一就返回給客戶端。?
這種方式很明顯太笨了，有了Union索引合并，MySQL其實可以有第二種查詢方式，過程是這樣的：
-
利用
idx_a索引將獲取到的id集合記作id_setA。 -
利用
idx_b索引將獲取到的id集合記作id_setB。 -
將
id_setA和id_setB取并集，記作id_set。 -
對
id_set回表查詢，將結果返回給客戶端。
這個過程和Intersection其實很像，只是交集換成了并集而已，所以很好理解。同樣的，取并集的過程也并非如此，這里只是方便大家理解。 ?
綜上所述，這種通過從多個索引中掃描到的記錄的主鍵值取并集后再回表查詢的方式，就是Union索引合并。EXPLAIN分析結果如下：
|
1
2
3
4
5
6
|
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,4 | NULL | 1016 | 100.00 | Using union(idx_a,idx_b); Using where |+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+ |
同樣，使用Union索引合并也是有條件的。如果使用到的索引都是二級索引的話，則要求通過二級索引取出的記錄是按照主鍵排好序的。為什么會有這個要求呢？主要是有以下兩個好處：
- 對兩個有序集合取并集更簡單。
- 主鍵有序的情況下，回表將不再是單純的隨機IO，回表的效率更高。
至于為啥這個查詢可以使用Union索引，其實上面已經說過了，這里不再贅述。
Union索引合并取并集的過程，和Intersection也很像。MySQL依然不需要使用額外的內存存儲這些id集合，大家可以按照上述流程自己走一遍，這里不再贅述。
2.3 Sort Union
|
1
|
SELECT * FROM T WHERE a=1 OR b>='Z'; |
針對這個查詢，是不能使用Union索引合并的，因為它不滿足條件：從idx_b二級索引取出的記錄并非是按照主鍵排序的。所以目前我們已知的查詢方式就一種：
- 全表掃描，判斷兩者條件滿足其一就返回給客戶端。
Intersection和Union使用的條件很嚴苛，必須要求二級索引取出的記錄是按照主鍵排好序的，針對這個查詢無法使用。但是這兩個條件a=1和b>='Z'很大概率能過濾掉大部分記錄，是可以提升查詢效率的，怎么辦呢？
MySQL很想利用這兩個索引，于是想了個辦法。既然二級索引自然取出來的主鍵不是排好序的，那我就先放到內存里自己排好序再使用Union的方式去查詢。整個過程是這樣的：
-
先從
idx_b索引中取出所有符合條件記錄，提取id集合先去重再排序，記作id_setB。 -
此時
id_setB已經是有序的了，從idx_a中依次取出記錄的id值，走正常取并集的過程即可。 - 對最終的id并集回表，將結果返回給客戶端。
綜上所述，這種通過從多個索引中掃描到的記錄的主鍵值排好序后，再按照Union索引合并的方式執行查詢的方式，就是Sort Union索引合并。相較于Union，其實就是多了一個對主鍵手動排序的過程。EXPLAIN分析結果如下：
|
1
2
3
4
5
6
|
mysql> EXPLAIN SELECT * FROM T WHERE a=1 OR b>='Z';+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+--------------------------------------------+| 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,4 | NULL | 975 | 100.00 | Using sort_union(idx_a,idx_b); Using where |+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+--------------------------------------------+ |
2.4 Sort Intersection
很遺憾，目前MySQL并不支持所謂的“Sort Intersection”索引合并的方式。大家肯定很好奇，既然有Sort Union，為啥沒有Sort Intersection呢？不就是先手動排序再取交集嗎？ ?
沒有查找到相關資料解釋為啥不支持，我可以說下我的理解。大家可以想一下，交集的本質是什么？一般情況下是將兩個很大的集合，變成一個較小的集合。而并集的本質又是什么呢？一般情況下是將兩個較小的集合，變成一個較大的集合。 ?
大家明白了嗎？對兩個較小的集合在內存中排序，開銷可以接受。但是對兩個較大的集合在內存中完成排序，這個操作本身的開銷可能比回表的開銷都大了，那MySQL還不如只利用「單索引+回表」的方式查詢呢。
3. 總結
不要天真的給WHERE條件涉及到的列都加上索引，通常情況下這只會讓結果更糟。因為一般情況下，對于單表查詢MySQL一次只能利用一個索引。但是，如果條件允許，MySQL也可以利用「Index Merge」的方式利用多個索引完成一次查詢。MySQL支持三種索引合并的方式，分別是Intersection、Union、Sort Union，其實就是利用二級索引中的主鍵值取交集、并集后再回表查詢。其中Intersection和Union使用條件比較嚴苛，要求從二級索引取出的記錄必須是根據主鍵排好序的。有時候條件不滿足，但是MySQL又很想使用Index Merge，就會嘗試自己在內存中手動排序，這就是Sort Union，它只比Union多了個手動排序的過程。至于為啥沒有Sort Intersection，作者說了一點自己的思考，不一定對，大家也可以思考一下。
到此這篇關于MySQL優化之Index Merge的使用的文章就介紹到這了,更多相關MySQL Index Merge內容請搜索服務器之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持服務器之家！
原文鏈接：https://juejin.cn/post/7071865447108313095
