其實(shí)我們之前所講的回表，就是兩個(gè)索引樹(shù)同時(shí)使用，先在二級(jí)索引樹(shù)中搜索到對(duì)應(yīng)的主鍵值，然后在再去主鍵索引樹(shù)中查詢完整的記錄。

但是我今天的問(wèn)題是，兩個(gè)不同的二級(jí)索引樹(shù)，會(huì)同時(shí)生效嗎?理論上來(lái)說(shuō)，應(yīng)該是可以同時(shí)生效的，不然這個(gè) MySQL 也太笨了。不過(guò)根據(jù)松哥日常開(kāi)發(fā)經(jīng)驗(yàn)，這種事情最好能夠避免，如果發(fā)生了同時(shí)搜索兩棵索引樹(shù)的事情，大概是你的索引設(shè)計(jì)有問(wèn)題，此時(shí)就要去檢查一下索引的設(shè)計(jì)是否合理。

加粗的是實(shí)踐經(jīng)驗(yàn)，但是對(duì)于兩個(gè)索引同時(shí)生效的知識(shí)點(diǎn)，我們還是要懂，一起來(lái)看下。

1. 索引合并

例如我有如下一張表結(jié)構(gòu)：

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `password` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `username` (`username`),
  KEY `address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

這個(gè)表里邊有 username 和 address 兩個(gè)索引，注意是兩個(gè)索引，每個(gè)索引中有一個(gè)字段，這不是聯(lián)合索引。

現(xiàn)在我的查詢 SQL 如下：

select * from user where username='1' or address='1';

搜索條件有兩個(gè)，username 和 address，這是兩個(gè)索引，分屬于兩棵不同的索引樹(shù)。那么它在搜索的時(shí)候會(huì)兩棵索引樹(shù)都去搜索嗎?還是只搜索一顆索引樹(shù)，再用另一個(gè)搜索條件過(guò)濾第一棵樹(shù)搜索出來(lái)的結(jié)果?

我們來(lái)看下數(shù)據(jù)庫(kù)執(zhí)行計(jì)劃：

大致上瞥一眼這個(gè)執(zhí)行計(jì)劃，大家也能猜出來(lái)，這里其實(shí)兩個(gè)索引都用到了，在這個(gè)執(zhí)行計(jì)劃中有幾個(gè)新面孔：

• type 為 index_merge。
• Extra 為 Using union(username,address); Using where。

這個(gè) type 中的 index_merge 就是索引合并。

2. 舊版玩法

當(dāng)然這個(gè) index_merge 并不是一開(kāi)始就有的，這是從 MySQL5.0 開(kāi)始引入的東西。雖然大家現(xiàn)在基本山不會(huì)再用到 MySQL5.0 之前的版本了，但是我這里還是說(shuō)一下，加深大家對(duì) MySQL 的理解。在 MySQL5.0 之前，對(duì)于我們上面給出的查詢 SQL，是不會(huì)走索引的，會(huì)全表掃描。在那個(gè)年代，如果你想實(shí)現(xiàn)上面這個(gè)查詢，但是又想走索引，你的 SQL 得這樣寫(xiě)：

select * from user where username='1' union all select * from user where address='1' and username!='1'

不過(guò)這種寫(xiě)法很明顯有點(diǎn)笨拙。

所以，從 MySQL5.0 開(kāi)始，在查詢中可以自動(dòng)使用多個(gè)索引進(jìn)行掃描，并將結(jié)果進(jìn)行合并，也就是我們前面所說(shuō)的索引合并(index_merge)。

3. 三種情況

索引合并這種算法有三個(gè)變種，我們分別來(lái)看。

3.1 union這是求兩個(gè)索引的并集。

我們來(lái)看如下 SQL：

select * from user where username like '1%' or address like '1%';

這個(gè) SQL 在執(zhí)行的過(guò)程中就會(huì)涉及到兩個(gè)索引，需要去兩棵索引樹(shù)中進(jìn)行搜索，再對(duì)搜索結(jié)果求并集，我們來(lái)看一下該 SQL 的執(zhí)行計(jì)劃：

可以看到，這個(gè)執(zhí)行計(jì)劃中已經(jīng)發(fā)生了索引合并(看 type 、key、Extra)。

那么是不是只要是兩個(gè)索引查詢就總會(huì)發(fā)送索引合并呢?我們?cè)賮?lái)看一個(gè)栗子：

select * from user where username>'a' or address='1';

大家看一下，只是搜索條件變了一下而已，這里就沒(méi)用索引合并了，而變成了全表掃描，這是為什么呢?這就引出來(lái)索引合并的一個(gè)條件，即：每個(gè)索引對(duì)應(yīng)的搜索條件，搜到的主鍵必須是有序的，如果搜到的主鍵是無(wú)序的，抱歉，索引合并用不了。在二級(jí)索引中，數(shù)據(jù)按照二級(jí)索引的順序進(jìn)行排序，結(jié)構(gòu)類(lèi)似下面這樣：

當(dāng) username 相同的時(shí)候，主鍵是有序的，當(dāng) username 不同的時(shí)候，就不能保證主鍵有序了，如果獲取到的主鍵無(wú)序，就無(wú)法實(shí)現(xiàn)索引合并了。

這又引出來(lái)一個(gè)問(wèn)題，為什么獲取到的主鍵有序才能發(fā)生索引合并呢?因?yàn)橹挥挟?dāng)主鍵是有序的，將來(lái)去重(union、sort-union)亦或者求交集(intersect)，效率都要高一些。

從 MySQL5.0 開(kāi)始，索引合并默認(rèn)是開(kāi)啟的，當(dāng)然你也可以選擇關(guān)閉，關(guān)閉 union 索引合并方式如下：

SET optimizer_switch = 'index_merge_union=off';

關(guān)閉之后再來(lái)看執(zhí)行計(jì)劃：

大家看到，依然發(fā)生了索引合并，但是這次不是 union，而是 sort_union 了，那我們接下來(lái)就來(lái)看下什么是 sort_union。

3.2 sort_union

sort_union 基本上和 union 一樣，只是多了一個(gè)排序的能力。

因?yàn)榍懊嫖覀冋f(shuō)，如果獲取到無(wú)序的主鍵，就不會(huì)發(fā)生索引合并，可能最終會(huì)直接上全表掃描。因此 MySQL 里邊又搞了一個(gè) sort_union，就是先在 username 索引樹(shù)和 address 索引樹(shù)中同時(shí)進(jìn)行搜索，分別拿到主鍵值之后先進(jìn)行排序，排序完了再進(jìn)行去重，然后回表拿完整的數(shù)據(jù)。

和 union 相比主要是多了加粗的那一步。

那我們繼續(xù)，關(guān)閉 sort_union，如下：

SET optimizer_switch = 'index_merge_sort_union=off';

關(guān)閉之后，再去看執(zhí)行計(jì)劃，如下：

此時(shí)就沒(méi)有索引合并了，直接全表掃描。

3.3 intersect

這個(gè)是求兩個(gè)索引的交集。

例如如下 SQL：

select * from user where username like '1%' and address like '1%';

這個(gè) SQL 在執(zhí)行的過(guò)程中就有可能出現(xiàn)求交集的情況。當(dāng)然這并非絕對(duì)的，具體還要看優(yōu)化器優(yōu)化后的情況。

松哥嘗試了很久，沒(méi)法復(fù)現(xiàn)一個(gè)例子出來(lái)，主要是我的模擬數(shù)據(jù)不太對(duì)味。如果小伙伴們有現(xiàn)成的 Using intersect 例子歡迎留言分享(執(zhí)行計(jì)劃 Extra 中會(huì)出現(xiàn) Using intersect 的)。

但是我把這個(gè)原理這里和大家分享下，我們來(lái)看如下一張圖：

假設(shè)有二級(jí)索引 S 和二級(jí)索引 T，現(xiàn)在交叉獲取主鍵(這里有一點(diǎn)需要注意，如果我們是單獨(dú)在 S 和 T 上搜索，且 S 上搜索條件是 username like '1%'，T 上的搜索條件是 address like '1%'，那么在搜索的過(guò)程中，各自拿到的主鍵 id 是有序的，這也是 intersect 的前提)：

• 首先去二級(jí)索引 S 上去搜索，找到第一條滿足條件的記錄，由于二級(jí)索引的葉子結(jié)點(diǎn)保存的是主鍵值，此時(shí)拿到主鍵值之后，先不要急著回表。
• 接下來(lái)去二級(jí)索引 T 上去搜索，找到第一條滿足條件的記錄，并且拿到對(duì)應(yīng)的主鍵值。
• 比較第一步和第二步搜索拿到的主鍵值：3.1 如果主鍵值不相等，則舍棄值小的主鍵，留下大的主鍵，下一次在 S 上搜索的時(shí)候，就拿著這個(gè)大的主鍵和 S 上搜索出來(lái)的主鍵進(jìn)行比較。3.2 如果主鍵值相等，則說(shuō)明這個(gè)主鍵是滿足搜索條件的，那就拿著這個(gè)主鍵回表。
• 重復(fù)前三步，直到各自索引中沒(méi)有滿足條件的記錄為止。

這就是所謂的交叉獲取主鍵。

好啦，這就是索引合并的三種情況。

4. 小結(jié)

很多小伙伴可能會(huì)說(shuō)，既然有索引合并，是不是我索引就可以隨便建立了?nonono!索引合并是一種不得已而為之的辦法，如果發(fā)生了索引合并，大概率是你設(shè)計(jì)的索引不太合理導(dǎo)致的，所以我們應(yīng)該去琢磨該如何優(yōu)化索引。

參考資料：

• ??https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html??
• 《MySQL 是怎么運(yùn)行的》
• 《高性能 MySQL》
• ??https://www.modb.pro/db/29619??