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

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

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

Intersection合并

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

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

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

情況一：等值匹配

二級索引列是等值匹配的情況，對于聯(lián)合索引來說，在聯(lián)合索引中的每個列都必須等值匹配，不能出現(xià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';

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

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

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

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

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

所以重點來了，之所以在二級索引列都是等值匹配的情況下才可能使用Intersection索引合并，是因為只有在這種情況下根據二級索引查詢出的結果集是按照主鍵值排序的。

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

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

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

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

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

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

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

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

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

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

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

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

我們在寫查詢語句時經常想把既符合某個搜索條件的記錄取出來，也把符合另外的某個搜索條件的記錄取出來，我們說這些不同的搜索條件之間是OR關系。有時候OR關系的不同搜索條件會使用到不同的索引，比方說這樣：

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

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

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

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

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

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í)行這個查詢：

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

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

采用Union索引合并的方式把上述兩個主鍵集合取并集，然后進行回表操作，將結果返回給用戶。

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

Sort-Union合并

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

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

這是因為根據order_no< 'a'從idx_order_no索引中獲取的二級索引記錄的主鍵值不是排好序的，根據expire_time> 'z'從idx_expire_time索引中獲取的二級索引記錄的主鍵值也不是排好序的，但是order_no< 'a'和expire_time> 'z''這兩個條件又特別讓我們動心，所以我們可以這樣：

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

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

因為上述的兩個二級索引主鍵值都是排好序的，剩下的操作和Union索引合并方式就一樣了。

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

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

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

這個查詢之所以可能使用Intersection索引合并的方式執(zhí)行，還不是因為idx_order_no和idx_expire_time是兩個單獨的B+樹索引，要是把這兩個列搞一個聯(lián)合索引，那直接使用這個聯(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都干掉，再添加一個聯(lián)合索引idx_order_no_expire_time，使用這個聯(lián)合索引進行查詢簡直是又快又好，既不用多讀一棵B+樹，也不用合并結果。

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

最新評論