MySQL優化之Index Merge的使用

1. 前言

先問大傢一個問題，在不考慮多表聯查這種復雜的查詢場景下，一個簡單的單表查詢，MySQL可以同時利用幾個索引？ ​

當初我學習MySQL的時候，天真的以為隻要把WHERE條件涉及到的列全部加上索引，就可以提升查詢速度，這個想法其實大錯特錯。因為一般情況下，單表查詢MySQL隻能利用一個索引，比如下面這個查詢，假設id是主鍵，a和b分別創建瞭索引，別天真的以為idx_a和idx_b都能發揮作用，其實不是的。

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。

什麼？索引合並還分類型？是的，MySQL目前共支持三種類型的索引合並，分別是：

我們使用一個具體的例子，來分別演示下三種索引合並。假設有表T如下，id是主鍵，列a和列b分別創建索引。

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;

大傢可以寫個存儲過程，向表中批量插入記錄，我這裡貼一下代碼，寫的很簡陋。

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

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分析結果如下：

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

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分析結果如下：

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

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分析結果如下：

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內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：

• mysql 索引合並的使用
• SQL中EXPLAIN命令的使用方法
• MySQL EXPLAIN語句的使用示例
• MySQL數據表使用的SQL語句整理
• 五分鐘讓你快速弄懂MySQL索引下推