1. 前言

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

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

SELECT id,a,b FROM T WHERE a>100 AND b>200;

因?yàn)?code>idx_a索引只存儲(chǔ)了列a和id的值，無(wú)法判斷b>200條件是否成立，所以只能拿著id去回表查詢。 同樣idx_b索引只存儲(chǔ)了列b和id的值，無(wú)法判斷a>100條件是否成立，也只能拿著id去回表查詢。 可以看到，最大的開(kāi)銷其實(shí)是回表操作，通過(guò)二級(jí)索引匹配到的數(shù)據(jù)越少，回表的開(kāi)銷也就越低。所以理論上來(lái)說(shuō)，a>100和b>200分別符合這兩個(gè)條件的記錄數(shù)越少，MySQL就會(huì)使用哪個(gè)索引。MySQL是如何判斷符合這些條件的記錄數(shù)量的呢？不也得老老實(shí)實(shí)的掃描全表嗎？MySQL采用預(yù)估的方式，通過(guò)表的統(tǒng)計(jì)數(shù)據(jù)或訪問(wèn)表中少量的數(shù)據(jù)來(lái)進(jìn)行預(yù)估，并分別計(jì)算使用這兩個(gè)索引進(jìn)行查詢各自的成本是多少，最終選擇執(zhí)行成本更低的索引方案。關(guān)于MySQL如何預(yù)估執(zhí)行成本，不在本篇文章的討論范圍內(nèi)，先跳過(guò)。 ?

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

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

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

2. Index Merge

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

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

我們使用一個(gè)具體的例子，來(lái)分別演示下三種索引合并。假設(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;

大家可以寫個(gè)存儲(chǔ)過(guò)程，向表中批量插入記錄，我這里貼一下代碼，寫的很簡(jiǎn)陋。

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)用幾次存儲(chǔ)，最終的數(shù)據(jù)就是：a的值在一萬(wàn)以內(nèi)重復(fù)，b的值在A~Z之間重復(fù)，主鍵保持遞增。下面我們基于這張表的數(shù)據(jù)來(lái)演示。

2.1 Intersection

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

針對(duì)這個(gè)查詢，目前我們知道它可以有以下三種查詢方式：

• 全表掃描，判斷兩個(gè)條件是否匹配。
• 利用idx_a索引將獲取到id回表查詢?cè)倥袛鄺l件b是否達(dá)成。
• 利用idx_b索引將獲取到id回表查詢?cè)倥袛鄺l件a是否達(dá)成。

有了Intersection索引合并，MySQL其實(shí)還可以有第四種查詢方式，查詢過(guò)程是這樣的：

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

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

綜上所述，這種通過(guò)從多個(gè)索引中掃描到的記錄的主鍵值取交集后再回表查詢的方式，就是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索引合并是有條件的。如果使用到的索引都是二級(jí)索引的話，則要求通過(guò)二級(jí)索引取出的記錄是按照主鍵排好序的。為什么會(huì)有這個(gè)要求呢？主要是有以下兩個(gè)好處：

• 對(duì)兩個(gè)有序集合取交集更簡(jiǎn)單。
• 主鍵有序的情況下，回表將不再是單純的隨機(jī)IO，回表的效率更高。

很顯然，我們這個(gè)查詢是能利用Intersection索引合并的。idx_a索引中是先根據(jù)a排序再根據(jù)id排序的，a=1的情況下，取出的記錄是按照id排好序的。idx_b索引中是先根據(jù)b排序再根據(jù)id排序的，b='A'的情況下，取出的記錄也是按照id排好序的。所以是符合要求的。 ?

最后，我們看一下MySQL從兩個(gè)集合中取交集的過(guò)程。假設(shè)idx_a過(guò)濾出的id是[1,3,5]，idx_b過(guò)濾出的id集合是[2,3,4]，取交集的過(guò)程其實(shí)是這樣的：

• 從idx_a取出第一條記錄，id值是1。再?gòu)?code>idx_b取出第一條記錄，id值是2，因?yàn)?code>1<2所以id為1的那條記錄直接丟棄。
• 從idx_a取出第二條記錄，id值是3，因?yàn)?code>2<3，所以id為2的那條記錄直接丟棄。
• 從idx_b取出第二條記錄，id值是3，因?yàn)?code>3=3，所以拿3去回表查詢，結(jié)果返回給客戶端，同時(shí)id為3的兩條記錄也直接丟棄。
• 從idx_a取出第三條記錄，id值是5。從idx_b取出第三條記錄，id值是4。因?yàn)?code>4<5所以id為4的記錄被丟棄，又因?yàn)殡p方都沒(méi)有記錄了，id為5的記錄也被丟棄，交集過(guò)程結(jié)束。

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

2.2 Union

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

針對(duì)這個(gè)查詢，我們是無(wú)法單獨(dú)使用idx_a或idx_b索引來(lái)完成的，因?yàn)樗鼈兊臈l件關(guān)系是OR，目前我們已知的查詢方式就一種：

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

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

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

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

綜上所述，這種通過(guò)從多個(gè)索引中掃描到的記錄的主鍵值取并集后再回表查詢的方式，就是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索引合并也是有條件的。如果使用到的索引都是二級(jí)索引的話，則要求通過(guò)二級(jí)索引取出的記錄是按照主鍵排好序的。為什么會(huì)有這個(gè)要求呢？主要是有以下兩個(gè)好處：

• 對(duì)兩個(gè)有序集合取并集更簡(jiǎn)單。
• 主鍵有序的情況下，回表將不再是單純的隨機(jī)IO，回表的效率更高。

至于為啥這個(gè)查詢可以使用Union索引，其實(shí)上面已經(jīng)說(shuō)過(guò)了，這里不再贅述。

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

2.3 Sort Union

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

針對(duì)這個(gè)查詢，是不能使用Union索引合并的，因?yàn)樗粷M足條件：從idx_b二級(jí)索引取出的記錄并非是按照主鍵排序的。所以目前我們已知的查詢方式就一種：

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

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

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

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

綜上所述，這種通過(guò)從多個(gè)索引中掃描到的記錄的主鍵值排好序后，再按照Union索引合并的方式執(zhí)行查詢的方式，就是Sort Union索引合并。相較于Union，其實(shí)就是多了一個(gè)對(duì)主鍵手動(dòng)排序的過(guò)程。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，為啥沒(méi)有Sort Intersection呢？不就是先手動(dòng)排序再取交集嗎？ ?

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

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

3. 總結(jié)

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

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

最新評(píng)論