其實我們之前所講的回表，就是兩個索引樹同時使用，先在二級索引樹中搜索到對應的主鍵值，然后在再去主鍵索引樹中查詢完整的記錄。

但是我今天的問題是，兩個不同的二級索引樹，會同時生效嗎?理論上來說，應該是可以同時生效的，不然這個 MySQL 也太笨了。不過根據松哥日常開發經驗，這種事情最好能夠避免，如果發生了同時搜索兩棵索引樹的事情，大概是你的索引設計有問題，此時就要去檢查一下索引的設計是否合理。

加粗的是實踐經驗，但是對于兩個索引同時生效的知識點，我們還是要懂，一起來看下。

1. 索引合并

例如我有如下一張表結構：

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;

這個表里邊有 username 和 address 兩個索引，注意是兩個索引，每個索引中有一個字段，這不是聯合索引。

現在我的查詢 SQL 如下：

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

搜索條件有兩個，username 和 address，這是兩個索引，分屬于兩棵不同的索引樹。那么它在搜索的時候會兩棵索引樹都去搜索嗎?還是只搜索一顆索引樹，再用另一個搜索條件過濾第一棵樹搜索出來的結果?

我們來看下數據庫執行計劃：

大致上瞥一眼這個執行計劃，大家也能猜出來，這里其實兩個索引都用到了，在這個執行計劃中有幾個新面孔：

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

這個 type 中的 index_merge 就是索引合并。

2. 舊版玩法

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

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

不過這種寫法很明顯有點笨拙。

所以，從 MySQL5.0 開始，在查詢中可以自動使用多個索引進行掃描，并將結果進行合并，也就是我們前面所說的索引合并(index_merge)。

3. 三種情況

索引合并這種算法有三個變種，我們分別來看。

3.1 union這是求兩個索引的并集。

我們來看如下 SQL：

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

這個 SQL 在執行的過程中就會涉及到兩個索引，需要去兩棵索引樹中進行搜索，再對搜索結果求并集，我們來看一下該 SQL 的執行計劃：

可以看到，這個執行計劃中已經發生了索引合并(看 type 、key、Extra)。

那么是不是只要是兩個索引查詢就總會發送索引合并呢?我們再來看一個栗子：

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

大家看一下，只是搜索條件變了一下而已，這里就沒用索引合并了，而變成了全表掃描，這是為什么呢?這就引出來索引合并的一個條件，即：每個索引對應的搜索條件，搜到的主鍵必須是有序的，如果搜到的主鍵是無序的，抱歉，索引合并用不了。在二級索引中，數據按照二級索引的順序進行排序，結構類似下面這樣：

當 username 相同的時候，主鍵是有序的，當 username 不同的時候，就不能保證主鍵有序了，如果獲取到的主鍵無序，就無法實現索引合并了。

這又引出來一個問題，為什么獲取到的主鍵有序才能發生索引合并呢?因為只有當主鍵是有序的，將來去重(union、sort-union)亦或者求交集(intersect)，效率都要高一些。

從 MySQL5.0 開始，索引合并默認是開啟的，當然你也可以選擇關閉，關閉 union 索引合并方式如下：

SET optimizer_switch = 'index_merge_union=off';

關閉之后再來看執行計劃：

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

3.2 sort_union

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

因為前面我們說，如果獲取到無序的主鍵，就不會發生索引合并，可能最終會直接上全表掃描。因此 MySQL 里邊又搞了一個 sort_union，就是先在 username 索引樹和 address 索引樹中同時進行搜索，分別拿到主鍵值之后先進行排序，排序完了再進行去重，然后回表拿完整的數據。

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

那我們繼續，關閉 sort_union，如下：

SET optimizer_switch = 'index_merge_sort_union=off';

關閉之后，再去看執行計劃，如下：

此時就沒有索引合并了，直接全表掃描。

3.3 intersect

這個是求兩個索引的交集。

例如如下 SQL：

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

這個 SQL 在執行的過程中就有可能出現求交集的情況。當然這并非絕對的，具體還要看優化器優化后的情況。

松哥嘗試了很久，沒法復現一個例子出來，主要是我的模擬數據不太對味。如果小伙伴們有現成的 Using intersect 例子歡迎留言分享(執行計劃 Extra 中會出現 Using intersect 的)。

但是我把這個原理這里和大家分享下，我們來看如下一張圖：

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

• 首先去二級索引 S 上去搜索，找到第一條滿足條件的記錄，由于二級索引的葉子結點保存的是主鍵值，此時拿到主鍵值之后，先不要急著回表。
• 接下來去二級索引 T 上去搜索，找到第一條滿足條件的記錄，并且拿到對應的主鍵值。
• 比較第一步和第二步搜索拿到的主鍵值：3.1 如果主鍵值不相等，則舍棄值小的主鍵，留下大的主鍵，下一次在 S 上搜索的時候，就拿著這個大的主鍵和 S 上搜索出來的主鍵進行比較。3.2 如果主鍵值相等，則說明這個主鍵是滿足搜索條件的，那就拿著這個主鍵回表。
• 重復前三步，直到各自索引中沒有滿足條件的記錄為止。

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

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

4. 小結

很多小伙伴可能會說，既然有索引合并，是不是我索引就可以隨便建立了?nonono!索引合并是一種不得已而為之的辦法，如果發生了索引合并，大概率是你設計的索引不太合理導致的，所以我們應該去琢磨該如何優化索引。

參考資料：

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