掃二維碼與項目經(jīng)理溝通
我們在微信上24小時期待你的聲音
解答本文疑問/技術(shù)咨詢/運營咨詢/技術(shù)建議/互聯(lián)網(wǎng)交流
其實我們之前所講的回表，就是兩個索引樹同時使用，先在二級索引樹中搜索到對應的主鍵值，然后在再去主鍵索引樹中查詢完整的記錄。
創(chuàng)新互聯(lián)從2013年開始，公司以網(wǎng)站建設、成都網(wǎng)站制作、系統(tǒng)開發(fā)、網(wǎng)絡推廣、文化傳媒、企業(yè)宣傳、平面廣告設計等為主要業(yè)務，適用行業(yè)近百種。服務企業(yè)客戶成百上千家，涉及國內(nèi)多個省份客戶。擁有多年網(wǎng)站建設開發(fā)經(jīng)驗。為企業(yè)提供專業(yè)的網(wǎng)站建設、創(chuàng)意設計、宣傳推廣等服務。 通過專業(yè)的設計、獨特的風格，為不同客戶提供各種風格的特色服務。
但是我今天的問題是，兩個不同的二級索引樹，會同時生效嗎?理論上來說，應該是可以同時生效的，不然這個 MySQL 也太笨了。不過根據(jù)松哥日常開發(fā)經(jīng)驗，這種事情最好能夠避免，如果發(fā)生了同時搜索兩棵索引樹的事情，大概是你的索引設計有問題，此時就要去檢查一下索引的設計是否合理。
加粗的是實踐經(jīng)驗，但是對于兩個索引同時生效的知識點，我們還是要懂，一起來看下。
例如我有如下一張表結(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;
這個表里邊有 username 和 address 兩個索引，注意是兩個索引，每個索引中有一個字段，這不是聯(lián)合索引。
現(xiàn)在我的查詢 SQL 如下：
select * from user where username='1' or address='1';
搜索條件有兩個，username 和 address，這是兩個索引，分屬于兩棵不同的索引樹。那么它在搜索的時候會兩棵索引樹都去搜索嗎?還是只搜索一顆索引樹，再用另一個搜索條件過濾第一棵樹搜索出來的結(jié)果?
我們來看下數(shù)據(jù)庫執(zhí)行計劃：
大致上瞥一眼這個執(zhí)行計劃，大家也能猜出來，這里其實兩個索引都用到了，在這個執(zhí)行計劃中有幾個新面孔：
這個 type 中的 index_merge 就是索引合并。
當然這個 index_merge 并不是一開始就有的，這是從 MySQL5.0 開始引入的東西。雖然大家現(xiàn)在基本山不會再用到 MySQL5.0 之前的版本了，但是我這里還是說一下，加深大家對 MySQL 的理解。在 MySQL5.0 之前，對于我們上面給出的查詢 SQL，是不會走索引的，會全表掃描。在那個年代，如果你想實現(xiàn)上面這個查詢，但是又想走索引，你的 SQL 得這樣寫：
select * from user where username='1' union all select * from user where address='1' and username!='1'
不過這種寫法很明顯有點笨拙。
所以，從 MySQL5.0 開始，在查詢中可以自動使用多個索引進行掃描，并將結(jié)果進行合并，也就是我們前面所說的索引合并(index_merge)。
索引合并這種算法有三個變種，我們分別來看。
我們來看如下 SQL：
select * from user where username like '1%' or address like '1%';
這個 SQL 在執(zhí)行的過程中就會涉及到兩個索引，需要去兩棵索引樹中進行搜索，再對搜索結(jié)果求并集，我們來看一下該 SQL 的執(zhí)行計劃：
可以看到，這個執(zhí)行計劃中已經(jīng)發(fā)生了索引合并(看 type 、key、Extra)。
那么是不是只要是兩個索引查詢就總會發(fā)送索引合并呢?我們再來看一個栗子：
select * from user where username>'a' or address='1';
大家看一下，只是搜索條件變了一下而已，這里就沒用索引合并了，而變成了全表掃描，這是為什么呢?這就引出來索引合并的一個條件，即：每個索引對應的搜索條件，搜到的主鍵必須是有序的，如果搜到的主鍵是無序的，抱歉，索引合并用不了。在二級索引中，數(shù)據(jù)按照二級索引的順序進行排序，結(jié)構(gòu)類似下面這樣：
|
username |
主鍵 |
|
a |
20 |
|
b |
30 |
|
c |
9 |
|
c |
10 |
|
c |
18 |
|
d |
1 |
|
d |
5 |
當 username 相同的時候，主鍵是有序的，當 username 不同的時候，就不能保證主鍵有序了，如果獲取到的主鍵無序，就無法實現(xiàn)索引合并了。
這又引出來一個問題，為什么獲取到的主鍵有序才能發(fā)生索引合并呢?因為只有當主鍵是有序的，將來去重(union、sort-union)亦或者求交集(intersect)，效率都要高一些。
從 MySQL5.0 開始，索引合并默認是開啟的，當然你也可以選擇關閉，關閉 union 索引合并方式如下：
SET optimizer_switch = 'index_merge_union=off';
關閉之后再來看執(zhí)行計劃：
大家看到，依然發(fā)生了索引合并，但是這次不是 union，而是 sort_union 了，那我們接下來就來看下什么是 sort_union。
sort_union 基本上和 union 一樣，只是多了一個排序的能力。
因為前面我們說，如果獲取到無序的主鍵，就不會發(fā)生索引合并，可能最終會直接上全表掃描。因此 MySQL 里邊又搞了一個 sort_union，就是先在 username 索引樹和 address 索引樹中同時進行搜索，分別拿到主鍵值之后先進行排序，排序完了再進行去重，然后回表拿完整的數(shù)據(jù)。
和 union 相比主要是多了加粗的那一步。
那我們繼續(xù)，關閉 sort_union，如下：
SET optimizer_switch = 'index_merge_sort_union=off';
關閉之后，再去看執(zhí)行計劃，如下：
此時就沒有索引合并了，直接全表掃描。
這個是求兩個索引的交集。
例如如下 SQL：
select * from user where username like '1%' and address like '1%';
這個 SQL 在執(zhí)行的過程中就有可能出現(xiàn)求交集的情況。當然這并非絕對的，具體還要看優(yōu)化器優(yōu)化后的情況。
松哥嘗試了很久，沒法復現(xiàn)一個例子出來，主要是我的模擬數(shù)據(jù)不太對味。如果小伙伴們有現(xiàn)成的 Using intersect 例子歡迎留言分享(執(zhí)行計劃 Extra 中會出現(xiàn) Using intersect 的)。
但是我把這個原理這里和大家分享下，我們來看如下一張圖：
假設有二級索引 S 和二級索引 T，現(xiàn)在交叉獲取主鍵(這里有一點需要注意，如果我們是單獨在 S 和 T 上搜索，且 S 上搜索條件是 username like '1%'，T 上的搜索條件是 address like '1%'，那么在搜索的過程中，各自拿到的主鍵 id 是有序的，這也是 intersect 的前提)：
這就是所謂的交叉獲取主鍵。
好啦，這就是索引合并的三種情況。
很多小伙伴可能會說，既然有索引合并，是不是我索引就可以隨便建立了?nonono!索引合并是一種不得已而為之的辦法，如果發(fā)生了索引合并，大概率是你設計的索引不太合理導致的，所以我們應該去琢磨該如何優(yōu)化索引。
我們在微信上24小時期待你的聲音
解答本文疑問/技術(shù)咨詢/運營咨詢/技術(shù)建議/互聯(lián)網(wǎng)交流
微信二維碼
微信二維碼
移動版官網(wǎng)