Mysql執行原理之索引合併步驟詳解

Mysql執行原理之索引合併詳解

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

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

Intersection合併

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

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

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

情況一：等值匹配

二級索引列是等值匹配的情況，對於聯合索引來說，在聯合索引中的每個列都必須等值匹配，不能出現只匹配部分列的情況。而下邊這兩個查詢就不能進行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進行了範圍匹配，第二個查詢是因為聯合索引u_idx_day_status中的order_status和expire_time列並沒有出現在搜尋條件中，所以這兩個查詢不能進行Intersection索引合併。

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

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

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

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

所以重點來了，之所以在二級索引列都是等值匹配的情況下才可能使用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過濾就行了，這樣多簡單。所以涉及主鍵的搜尋條件只不過是為了從別的二級索引得到的結果集中過濾記錄罷了，是不是等值匹配不重要。

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

Union合併（並集 ：  合併後可能 在 一頁 一起找出來了 同樣找的時候也可能去重，說白了減少io次數）

我們在寫查詢語句時經常想把既符合某個搜尋條件的記錄取出來，也把符合另外的某個搜尋條件的記錄取出來，我們說這些不同的搜尋條件之間是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');

優化器可能採用這樣的方式來執行這個查詢：

先按照搜尋條件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'從聯合索引u_idx_day_status中得到另一個主鍵集合。

採用Union索引合併的方式把上述兩個主鍵集合取並集，然後進行回表操作，將結果返回給使用者。

當然，查詢條件符合了這些情況也不一定就會採用Union索引合併，也得看優化器的心情。優化器只有在單獨根據搜尋條件從某個二級索引中獲取的記錄數比較少，通過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索引合併方式執行的方式稱之為Sort-Union索引合併，很顯然，這種Sort-Union索引合併比單純的Union索引合併多了一步對二級索引記錄的主鍵值排序的過程。

聯合索引替代Intersection索引合併

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

這個查詢之所以可能使用Intersection索引合併的方式執行，還不是因為idx_order_no和idx_expire_time是兩個單獨的B+樹索引，要是把這兩個列搞一個聯合索引，那直接使用這個聯合索引就把事情搞定了，何必用啥索引合併呢，就像這樣：

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都幹掉，再新增一個聯合索引idx_order_no_expire_time，使用這個聯合索引進行查詢簡直是又快又好，既不用多讀一棵B+樹，也不用合併結果。

到此這篇關於Mysql執行原理之索引合併詳解的文章就介紹到這了,更多相關Mysql索引合併內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！