1. 前言

先問大家一個問題，在不考慮多表聯(lián)查這種復(fù)雜的查詢場景下，一個簡單的單表查詢，MySQL可以同時利用幾個索引？ ?

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

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去回表查詢。 可以看到，最大的開銷其實是回表操作，通過二級索引匹配到的數(shù)據(jù)越少，回表的開銷也就越低。所以理論上來說，a>100和b>200分別符合這兩個條件的記錄數(shù)越少，MySQL就會使用哪個索引。MySQL是如何判斷符合這些條件的記錄數(shù)量的呢？不也得老老實實的掃描全表嗎？MySQL采用預(yù)估的方式，通過表的統(tǒng)計數(shù)據(jù)或訪問表中少量的數(shù)據(jù)來進行預(yù)估，并分別計算使用這兩個索引進行查詢各自的成本是多少，最終選擇執(zhí)行成本更低的索引方案。關(guān)于MySQL如何預(yù)估執(zhí)行成本，不在本篇文章的討論范圍內(nèi)，先跳過。 ?

我們假設(shè)最終MySQL使用idx_a索引，那么這個查詢過程其實是這樣的：

• InnoDB從idx_aB+樹中獲取到第一條a>100的記錄，拿記錄里的id值回表查詢。
• 回表查詢獲取到完整的用戶記錄，判斷b>200是否成立，成立則返回給客戶端，否則丟棄該記錄。
• InnoDB繼續(xù)從idx_aB+樹中獲取到下一條a>100的記錄，重復(fù)前面的過程。

建立了這么多索引，每次查詢只使用一個，太可惜了不是嘛。能不能同時利用多個索引來完成查詢呢？可以的，但是條件有些嚴(yán)苛，這就是我們今天要介紹的索引合并Index Merge。

2. Index Merge

MySQL將這種使用多個索引來完成一次查詢的執(zhí)行方法稱為 索引合并「index merge」。如何才能知道我們寫的SQL語句使用了索引合并呢？通過EXPLAIN分析一下就知道了，如果使用了索引合并，對應(yīng)的type列顯示的值應(yīng)該是index_merge，key列顯示用的到所有索引名稱，Extra列會顯示具體使用了哪種類型的索引合并。 如下所示，同時使用了idx_a和idx_b兩個索引完成查詢，且索引合并類型為Intersection。

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

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

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均是普通索引，值是允許重復(fù)的，大家可以多調(diào)用幾次存儲，最終的數(shù)據(jù)就是：a的值在一萬以內(nèi)重復(fù)，b的值在A~Z之間重復(fù)，主鍵保持遞增。下面我們基于這張表的數(shù)據(jù)來演示。

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回表查詢，將結(jié)果返回給客戶端。

這個過程描述的其實是有問題的，但是大概意思是對的，主要是幫助大家理解。對id取交集的過程，并不是這樣的，本質(zhì)上MySQL并不會存儲這些id集合，因為數(shù)據(jù)量一大是很占用內(nèi)存的，這個我們待會說。 ?

綜上所述，這種通過從多個索引中掃描到的記錄的主鍵值取交集后再回表查詢的方式，就是Intersection索引合并。EXPLAIN分析結(jié)果如下：

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索引中是先根據(jù)a排序再根據(jù)id排序的，a=1的情況下，取出的記錄是按照id排好序的。idx_b索引中是先根據(jù)b排序再根據(jù)id排序的，b='A'的情況下，取出的記錄也是按照id排好序的。所以是符合要求的。 ?

最后，我們看一下MySQL從兩個集合中取交集的過程。假設(shè)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去回表查詢，結(jié)果返回給客戶端，同時id為3的兩條記錄也直接丟棄。
• 從idx_a取出第三條記錄，id值是5。從idx_b取出第三條記錄，id值是4。因為4<5所以id為4的記錄被丟棄，又因為雙方都沒有記錄了，id為5的記錄也被丟棄，交集過程結(jié)束。

通過上述過程，現(xiàn)在你應(yīng)該很清楚為啥MySQL要求二級索引返回的記錄必須根據(jù)主鍵排好序了吧，如此一來，整個求交集的過程將變得非常簡單，MySQL也無需使用額外的內(nèi)存空間來保存這些id集合。

2.2 Union

SELECT * FROM T WHERE a=1 OR b='A';

針對這個查詢，我們是無法單獨使用idx_a或idx_b索引來完成的，因為它們的條件關(guān)系是OR，目前我們已知的查詢方式就一種：

• 全表掃描，判斷兩者條件滿足其一就返回給客戶端。?

這種方式很明顯太笨了，有了Union索引合并，MySQL其實可以有第二種查詢方式，過程是這樣的：

• 利用idx_a索引將獲取到的id集合記作id_setA。
• 利用idx_b索引將獲取到的id集合記作id_setB。
• 將id_setA和id_setB取并集，記作id_set。
• 對id_set回表查詢，將結(jié)果返回給客戶端。

這個過程和Intersection其實很像，只是交集換成了并集而已，所以很好理解。同樣的，取并集的過程也并非如此，這里只是方便大家理解。 ?

綜上所述，這種通過從多個索引中掃描到的記錄的主鍵值取并集后再回表查詢的方式，就是Union索引合并。EXPLAIN分析結(jié)果如下：

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索引，其實上面已經(jīng)說過了，這里不再贅述。

Union索引合并取并集的過程，和Intersection也很像。MySQL依然不需要使用額外的內(nèi)存存儲這些id集合，大家可以按照上述流程自己走一遍，這里不再贅述。

2.3 Sort Union

SELECT * FROM T WHERE a=1 OR b>='Z';

針對這個查詢，是不能使用Union索引合并的，因為它不滿足條件：從idx_b二級索引取出的記錄并非是按照主鍵排序的。所以目前我們已知的查詢方式就一種：

• 全表掃描，判斷兩者條件滿足其一就返回給客戶端。

Intersection和Union使用的條件很嚴(yán)苛，必須要求二級索引取出的記錄是按照主鍵排好序的，針對這個查詢無法使用。但是這兩個條件a=1和b>='Z'很大概率能過濾掉大部分記錄，是可以提升查詢效率的，怎么辦呢？

MySQL很想利用這兩個索引，于是想了個辦法。既然二級索引自然取出來的主鍵不是排好序的，那我就先放到內(nèi)存里自己排好序再使用Union的方式去查詢。整個過程是這樣的：

• 先從idx_b索引中取出所有符合條件記錄，提取id集合先去重再排序，記作id_setB。
• 此時id_setB已經(jīng)是有序的了，從idx_a中依次取出記錄的id值，走正常取并集的過程即可。
• 對最終的id并集回表，將結(jié)果返回給客戶端。

綜上所述，這種通過從多個索引中掃描到的記錄的主鍵值排好序后，再按照Union索引合并的方式執(zhí)行查詢的方式，就是Sort Union索引合并。相較于Union，其實就是多了一個對主鍵手動排序的過程。EXPLAIN分析結(jié)果如下：

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呢？不就是先手動排序再取交集嗎？ ?

沒有查找到相關(guān)資料解釋為啥不支持，我可以說下我的理解。大家可以想一下，交集的本質(zhì)是什么？一般情況下是將兩個很大的集合，變成一個較小的集合。而并集的本質(zhì)又是什么呢？一般情況下是將兩個較小的集合，變成一個較大的集合。 ?

大家明白了嗎？對兩個較小的集合在內(nèi)存中排序，開銷可以接受。但是對兩個較大的集合在內(nèi)存中完成排序，這個操作本身的開銷可能比回表的開銷都大了，那MySQL還不如只利用「單索引+回表」的方式查詢呢。

3. 總結(jié)

不要天真的給WHERE條件涉及到的列都加上索引，通常情況下這只會讓結(jié)果更糟。因為一般情況下，對于單表查詢MySQL一次只能利用一個索引。但是，如果條件允許，MySQL也可以利用「Index Merge」的方式利用多個索引完成一次查詢。MySQL支持三種索引合并的方式，分別是Intersection、Union、Sort Union，其實就是利用二級索引中的主鍵值取交集、并集后再回表查詢。其中Intersection和Union使用條件比較嚴(yán)苛，要求從二級索引取出的記錄必須是根據(jù)主鍵排好序的。有時候條件不滿足，但是MySQL又很想使用Index Merge，就會嘗試自己在內(nèi)存中手動排序，這就是Sort Union，它只比Union多了個手動排序的過程。至于為啥沒有Sort Intersection，作者說了一點自己的思考，不一定對，大家也可以思考一下。

到此這篇關(guān)于MySQL優(yōu)化之Index Merge的使用的文章就介紹到這了,更多相關(guān)MySQL Index Merge內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論