中文精品视频一区二区在线观看,免费看日b视频,99久久99热精品免费观看国产

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

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

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

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;

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

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

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

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

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

MYSQL查詢中，有沒有可能多個索引一起用呢？

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

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

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

2. 舊版玩法

當(dāng)然這個 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 開始，在查詢中可以自動使用多個索引進(jìn)行掃描，并將結(jié)果進(jìn)行合并，也就是我們前面所說的索引合并(index_merge)。

3. 三種情況

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

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

我們來看如下 SQL：

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

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

MYSQL查詢中，有沒有可能多個索引一起用呢？

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

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

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

MYSQL查詢中，有沒有可能多個索引一起用呢？

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

username	主鍵
a	20
b	30
c	9
c	10
c	18
d	1
d	5

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

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

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

SET optimizer_switch = 'index_merge_union=off';

關(guān)閉之后再來看執(zhí)行計劃：

MYSQL查詢中，有沒有可能多個索引一起用呢？

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

3.2 sort_union

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

因為前面我們說，如果獲取到無序的主鍵，就不會發(fā)生索引合并，可能最終會直接上全表掃描。因此 MySQL 里邊又搞了一個 sort_union，就是先在 username 索引樹和 address 索引樹中同時進(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í)行計劃，如下：

MYSQL查詢中，有沒有可能多個索引一起用呢？

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

3.3 intersect

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

例如如下 SQL：

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

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

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

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

MYSQL查詢中，有沒有可能多個索引一起用呢？

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

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

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

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