Mysql執(zhí)行原理之索引合并詳解

我們前邊說過MySQL在一般情況下執(zhí)行一個(gè)查詢時(shí)最多只會(huì)用到單個(gè)二級(jí)索引，但存在有特殊情況，在這些特殊情況下也可能在一個(gè)查詢中使用到多個(gè)二級(jí)索引，MySQL中這種使用到多個(gè)索引來完成一次查詢的執(zhí)行方法稱之為：索引合并/index merge，在前面的成本計(jì)算中我們說到過這個(gè)概念：“我們需要分別分析單獨(dú)使用這些索引執(zhí)行查詢的成本，最后還要分析是否可能使用到索引合并”。其實(shí)optimizer trace輸出的文本中就有這個(gè)片段：

具體的索引合并算法有下邊三種。

Intersection合并

Intersection翻譯過來的意思是交集。這里是說某個(gè)查詢可以使用多個(gè)二級(jí)索引，將從多個(gè)二級(jí)索引中查詢到的結(jié)果取交集，比方說下邊這個(gè)查詢：

SELECT * FROM order_exp WHERE order_no = 'a' AND expire_time = 'b';

假設(shè)這個(gè)查詢使用Intersection合并的方式執(zhí)行的話，那這個(gè)過程就是這樣的：從idx_order_no二級(jí)索引對(duì)應(yīng)的B+樹中取出order_no= 'a’的相關(guān)記錄。從idx_insert_time二級(jí)索引對(duì)應(yīng)的B+樹中取出insert_time= 'b’的相關(guān)記錄。二級(jí)索引的記錄都是由索引列 + 主鍵構(gòu)成的，所以我們可以計(jì)算出這兩個(gè)結(jié)果集中id值的交集。
按照上一步生成的id值列表進(jìn)行回表操作，也就是從聚簇索引中把指定id值的完整用戶記錄取出來，返回給用戶。為啥不直接使用idx_order_no或者idx_insert_time只根據(jù)某個(gè)搜索條件去讀取一個(gè)二級(jí)索引，然后回表后再過濾另外一個(gè)搜索條件呢？這里要分析一下兩種查詢執(zhí)行方式之間需要的成本代價(jià)。
只讀取一個(gè)二級(jí)索引的成本：
按照某個(gè)搜索條件讀取一個(gè)二級(jí)索引，根據(jù)從該二級(jí)索引得到的主鍵值進(jìn)行回表操作，然后再過濾其他的搜索條件讀取多個(gè)二級(jí)索引之后取交集成本：
按照不同的搜索條件分別讀取不同的二級(jí)索引，將從多個(gè)二級(jí)索引得到的主鍵值取交集，然后進(jìn)行回表操作。雖然讀取多個(gè)二級(jí)索引比讀取一個(gè)二級(jí)索引消耗性能，但是大部分情況下讀取二級(jí)索引的操作是順序I/O，而回表操作是隨機(jī)I/O，所以如果只讀取一個(gè)二級(jí)索引時(shí)需要回表的記錄數(shù)特別多，而讀取多個(gè)二級(jí)索引之后取交集的記錄數(shù)非常少，當(dāng)節(jié)省的因?yàn)榛乇矶斐傻男阅軗p耗比訪問多個(gè)二級(jí)索引帶來的性能損耗更高時(shí)，讀取多個(gè)二級(jí)索引后取交集比只讀取一個(gè)二級(jí)索引的成本更低。
MySQL在某些特定的情況下才可能會(huì)使用到Intersection索引合并，哪些情況呢？

情況一：等值匹配

二級(jí)索引列是等值匹配的情況，對(duì)于聯(lián)合索引來說，在聯(lián)合索引中的每個(gè)列都必須等值匹配，不能出現(xiàn)只匹配部分列的情況。而下邊這兩個(gè)查詢就不能進(jìn)行Intersection索引合并：

SELECT * FROM order_exp WHERE order_no> 'a' AND insert_time = 'a' AND
order_status = 'b' AND expire_time = 'c';
SELECT * FROM order_exp WHERE order_no = 'a' AND insert_time = 'a';

第一個(gè)查詢是因?yàn)閷?duì)order_no進(jìn)行了范圍匹配，第二個(gè)查詢是因?yàn)槁?lián)合索引u_idx_day_status中的order_status和expire_time列并沒有出現(xiàn)在搜索條件中，所以這兩個(gè)查詢不能進(jìn)行Intersection索引合并。

情況二：主鍵列可以是范圍匹配

比方說下邊這個(gè)查詢可能用到主鍵和u_idx_day_status（insert_time, order_status,expire_time）進(jìn)行Intersection索引合并的操作：

SELECT * FROM order_exp WHERE id > 100 AND insert_time = 'a';

對(duì)于InnoDB的二級(jí)索引來說，記錄先是按照索引列進(jìn)行排序，如果該二級(jí)索引是一個(gè)聯(lián)合索引，那么會(huì)按照聯(lián)合索引中的各個(gè)列依次排序。而二級(jí)索引的用戶記錄是由索引列 + 主鍵構(gòu)成的，二級(jí)索引列的值相同的記錄可能會(huì)有好多條，這些索引列的值相同的記錄又是按照主鍵的值進(jìn)行排序的。

所以重點(diǎn)來了，之所以在二級(jí)索引列都是等值匹配的情況下才可能使用Intersection索引合并，是因?yàn)橹挥性谶@種情況下根據(jù)二級(jí)索引查詢出的結(jié)果集是按照主鍵值排序的。

Intersection索引合并會(huì)把從多個(gè)二級(jí)索引中查詢出的主鍵值求交集，如果從各個(gè)二級(jí)索引中查詢的到的結(jié)果集本身就是已經(jīng)按照主鍵排好序的，那么求交集的過程就很容易。

假設(shè)某個(gè)查詢使用Intersection索引合并的方式從idx_order_no和idx_expire_time這兩個(gè)二級(jí)索引中獲取到的主鍵值分別是：

從idx_order_no中獲取到已經(jīng)排好序的主鍵值：1、3、5

從idx_expire_time中獲取到已經(jīng)排好序的主鍵值：2、3、4

那么求交集的過程就是這樣：逐個(gè)取出這兩個(gè)結(jié)果集中最小的主鍵值，如果兩個(gè)值相等，則加入最后的交集結(jié)果中，否則丟棄當(dāng)前較小的主鍵值，再取該丟棄的主鍵值所在結(jié)果集的后一個(gè)主鍵值來比較，直到某個(gè)結(jié)果集中的主鍵值用完了，時(shí)間復(fù)雜度是O(n)。（這個(gè)求交集思路可以學(xué)習(xí)下）

但是如果從各個(gè)二級(jí)索引中查詢出的結(jié)果集并不是按照主鍵排序的話，那就要先把結(jié)果集中的主鍵值排序完再來做上邊的那個(gè)過程，就比較耗時(shí)了。

按照有序的主鍵值去回表取記錄有個(gè)專有名詞，叫：Rowid Ordered Retrieval，簡稱ROR。

另外，不僅是多個(gè)二級(jí)索引之間可以采用Intersection索引合并，索引合并也可以有聚簇索引參加，也就是我們上邊寫的情況二：在搜索條件中有主鍵的范圍匹配的情況下也可以使用Intersection索引合并索引合并。為啥主鍵這就可以范圍匹配了？還是得回到應(yīng)用場景里：

SELECT * FROM order_exp WHERE id > 100 AND order_no = 'a';

假設(shè)這個(gè)查詢可以采用Intersection索引合并，我們理所當(dāng)然的以為這個(gè)查詢會(huì)分別按照id > 100這個(gè)條件從聚簇索引中獲取一些記錄，在通過order_no= 'a'這個(gè)條件從idx_order_no二級(jí)索引中獲取一些記錄，然后再求交集，其實(shí)這樣就把問題復(fù)雜化了，沒必要從聚簇索引中獲取一次記錄。別忘了二級(jí)索引的記錄中都帶有主鍵值的，所以可以在從idx_order_no中獲取到的主鍵值上直接運(yùn)用條件id > 100過濾就行了，這樣多簡單。所以涉及主鍵的搜索條件只不過是為了從別的二級(jí)索引得到的結(jié)果集中過濾記錄罷了，是不是等值匹配不重要。

當(dāng)然，上邊說的情況一和情況二只是發(fā)生Intersection索引合并的必要條件，不是充分條件。也就是說即使情況一、情況二成立，也不一定發(fā)生Intersection索引合并，這得看優(yōu)化器的心情。優(yōu)化器只有在單獨(dú)根據(jù)搜索條件從某個(gè)二級(jí)索引中獲取的記錄數(shù)太多，導(dǎo)致回表開銷太大，而通過Intersection索引合并后需要回表的記錄數(shù)大大減少時(shí)才會(huì)使用Intersection索引合并。

Union合并（并集 ：  合并后可能 在 一頁 一起找出來了 同樣找的時(shí)候也可能去重，說白了減少io次數(shù)）

我們?cè)趯懖樵冋Z句時(shí)經(jīng)常想把既符合某個(gè)搜索條件的記錄取出來，也把符合另外的某個(gè)搜索條件的記錄取出來，我們說這些不同的搜索條件之間是OR關(guān)系。有時(shí)候OR關(guān)系的不同搜索條件會(huì)使用到不同的索引，比方說這樣：

SELECT * FROM order_exp WHERE order_no = 'a' OR expire_time = 'b'

Intersection是交集的意思，這適用于使用不同索引的搜索條件之間使用AND連接起來的情況；Union是并集的意思，適用于使用不同索引的搜索條件之間使用OR連接起來的情況。與Intersection索引合并類似，MySQL在某些特定的情況下才可能會(huì)使用到Union索引合并：

情況一：等值匹配
分析同Intersection合并

情況二：主鍵列可以是范圍匹配
分析同Intersection合并

情況三：使用Intersection索引合并的搜索條件
就是搜索條件的某些部分使用Intersection索引合并的方式得到的主鍵集合和其他方式得到的主鍵集合取交集，比方說這個(gè)查詢：

SELECT * FROM order_exp WHERE insert_time = 'a' AND order_status = 'b' AND expire_time = 'c' OR (order_no = 'a' AND expire_time = 'b');

優(yōu)化器可能采用這樣的方式來執(zhí)行這個(gè)查詢：

先按照搜索條件order_no = 'a' AND expire_time = 'b'從索引idx_order_no和idx_expire_time中使用Intersection索引合并的方式得到一個(gè)主鍵集合。

再按照搜索條件 insert_time = 'a' AND order_status = 'b' AND expire_time = 'c'從聯(lián)合索引u_idx_day_status中得到另一個(gè)主鍵集合。

采用Union索引合并的方式把上述兩個(gè)主鍵集合取并集，然后進(jìn)行回表操作，將結(jié)果返回給用戶。

當(dāng)然，查詢條件符合了這些情況也不一定就會(huì)采用Union索引合并，也得看優(yōu)化器的心情。優(yōu)化器只有在單獨(dú)根據(jù)搜索條件從某個(gè)二級(jí)索引中獲取的記錄數(shù)比較少，通過Union索引合并后進(jìn)行訪問的代價(jià)比全表掃描更小時(shí)才會(huì)使用Union索引合并。

Sort-Union合并

Union索引合并的使用條件太苛刻，必須保證各個(gè)二級(jí)索引列在進(jìn)行等值匹配的條件下才可能被用到，比方說下邊這個(gè)查詢就無法使用到Union索引合并：

SELECT * FROM order_exp WHERE order_no< 'a' OR expire_time> 'z'

這是因?yàn)楦鶕?jù)order_no< 'a'從idx_order_no索引中獲取的二級(jí)索引記錄的主鍵值不是排好序的，根據(jù)expire_time> 'z'從idx_expire_time索引中獲取的二級(jí)索引記錄的主鍵值也不是排好序的，但是order_no< 'a'和expire_time> 'z''這兩個(gè)條件又特別讓我們動(dòng)心，所以我們可以這樣：

先根據(jù)order_no< 'a'條件從idx_order_no二級(jí)索引中獲取記錄，并按照記錄的主鍵值進(jìn)行排序

再根據(jù)expire_time> 'z'條件從idx_expire_time二級(jí)索引中獲取記錄，并按照記錄的主鍵值進(jìn)行排序

因?yàn)樯鲜龅膬蓚€(gè)二級(jí)索引主鍵值都是排好序的，剩下的操作和Union索引合并方式就一樣了。

上述這種先按照二級(jí)索引記錄的主鍵值進(jìn)行排序，之后按照Union索引合并方式執(zhí)行的方式稱之為Sort-Union索引合并，很顯然，這種Sort-Union索引合并比單純的Union索引合并多了一步對(duì)二級(jí)索引記錄的主鍵值排序的過程。

聯(lián)合索引替代Intersection索引合并

SELECT * FROM order_exp WHERE order_no= 'a' And expire_time= 'z';

這個(gè)查詢之所以可能使用Intersection索引合并的方式執(zhí)行，還不是因?yàn)閕dx_order_no和idx_expire_time是兩個(gè)單獨(dú)的B+樹索引，要是把這兩個(gè)列搞一個(gè)聯(lián)合索引，那直接使用這個(gè)聯(lián)合索引就把事情搞定了，何必用啥索引合并呢，就像這樣：

ALTER TABLE order_exp drop index idx_order_no, idx_expire_time,

add index idx_order_no_expire_time(order_no, expire_time);

這樣我們把idx_order_no, idx_expire_time都干掉，再添加一個(gè)聯(lián)合索引idx_order_no_expire_time，使用這個(gè)聯(lián)合索引進(jìn)行查詢簡直是又快又好，既不用多讀一棵B+樹，也不用合并結(jié)果。

到此這篇關(guān)于Mysql執(zhí)行原理之索引合并詳解的文章就介紹到這了,更多相關(guān)Mysql索引合并內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論