復雜sql分析以及索引合并

復雜sql分析

簡單的sql語句我們很簡單的就可以分析出來它的執行計劃，但是復雜的sql呢。例如

SELECT	*	FROM	single_table	WHERE(key1	>	'xyz'	AND	key2	=	748	)	OR(key1	<	'abc'	AND	key1	>	'lmn')	OR(key1	LIKE	'%suf'	AND	key1	>	'zzz'	AND	(key2	<	8000	OR	common_field	=	'abc'))	;

這條語句很難分析出來，遇到這樣的語句怎么辦呢，怎么分析它的范圍匹配區間呢？

首先我們在為某個索引確定范圍區間的時候只需要把用不到相關索引的搜索條件替換成true。

之所以把用不到索引的搜索條件替換為true，是因為我們呢不打算使用這些條件進行在該索引上進行過濾，所以不管索引的記錄滿不滿足這些條件，我們都把他們選取出來，帶到之后徽標的時候再使用他們過濾。

例如select * from table where key1 >100 and key2 > 10;

如果假設為用key1上的索引就可以簡化為:select * from table where key1 > 100 and true;

如果假設為用key2上的索引就可以簡化為:select * from table where true and key2 > 10;

根據小學的知識：a? and? b? ?只有a和b同時為true的時候結果才為true，只有所以key1>100 and true 可以簡化為key1 > 100，因為最后的結果由key1 > 100決定。

所以如果假設為用key1上的索引就可以簡化為:select * from table where key1 > 100；

根據上面的方法就可以簡化上面的復雜sql為：

我們假設使用key1上的索引。

①key1 < 'abc' AND key1 > 'lmn'，永遠都不會滿足這個條件，所以為false

②key1 like ‘%suf'? 因為%在前面所以這個條件是用不到索引的

SELECT	*	FROM	single_table	WHERE(key1	>	'xyz'	AND	true	)	OR(false)	OR(true	AND	key1	>	'zzz'	AND	(true	OR	true))	;

false or 條件? ? ? ?根據小學的知識，最后的結果取決與條件為true還是false，所以簡化為：條件?

SELECT	*	FROM	single_table	WHEREkey1	>	'xyz'	OR  key1	>	'zzz';

這就變成了簡單的sql。可以很好的進行分析

繼續簡化因為條件為or，所以最后的結果取并集，所以就是最小的值key1 > 'xyz'

我們假設使用key2上的索引。

SELECT	*	FROM	single_table	WHEREkey2 = 748  or true	;

true? or? ?任何條件的結果：true

所以使用key2?需要掃描key2二級索引的全部記錄，然后回表，這效率很低甚至不如全表掃描，所以優化器最后選擇key1上的索引。

索引合并

mysql在一般的情況下執行一個查詢最多會用到單個二級索引，但是也有些特上述情況可能用到多個二級索引，mysql中把這種使用到多個索引來完成一次查詢的執行方法稱之為：index_merge

explain的type = index_merge

索引合并有三種算法

Intersection合并

intersection就是交集的意思，就是某個查詢可以使用多個二級索引，將多個二級索引中查到的結果取交集，例如

select * from table where key1 = 'a' and key2 = 'b';

假設使用intersection算法就是：

①從key1二級索引的B+樹中取出key1=’a'的記錄

②從key2?二級索引的B+樹中取出key2=’a'的記錄

③二級索引都是由二級索引列+id值組成，所以我們可以計算出兩個結果集中id值的交集

④根據交集的id值進行回表操作，把結果返回給用戶。

這?有同學會思考：為啥不直接使?idx_key1或者idx_key3只根據某個搜索條件去讀取?個?級索引，然后回表后再過濾另外?個搜索條件呢？這?要分析?下兩種查詢執??式之間需要的成本代價。

只讀取?個?級索引的成本：

按照某個搜索條件讀取?個?級索引

根據從該?級索引得到的主鍵值進?回表操作，然后再過濾其他的搜索條件

讀取多個?級索引之后取交集成本：

按照不同的搜索條件分別讀取不同的?級索引

將從多個?級索引得到的主鍵值取交集，然后進?回表操作

雖然讀取多個?級索引?讀取?個?級索引消耗性能，但是讀取?級索引的操作是順序I/O，?回表操作是隨機I/O，所以如果只讀取?個?級索引時需要回表的記錄數特別多，?讀取多個?級索引之后取交集的記錄數?常少，當節省的因為回表?造成的性能損耗?訪問多個?級索引帶來的性能損耗更?時，讀取多個?級索引后取交集?只讀取?個?級索引的成本更低。

MySQL在某些特定的情況下才可能會使?到Intersection索引合并：

情況?：?級索引列是等值匹配的情況，對于聯合索引來說，在聯合索引中的每個列都必須等值匹配，不能出現只匹配部分列的情況。

??說下邊這個查詢可能?到idx_key1和idx_key_part這兩個?級索引進?Intersection索引合并的操作：

SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

?下邊這兩個查詢就不能進?Intersection索引合并：

SELECT * FROM single_table WHERE key1 > 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a';

第?個查詢是因為對key1進?了范圍匹配，第?個查詢是因為聯合索引idx_key_part中的key_part2列并沒有出現在搜索條件中，所以這兩個查詢不能進? Intersection索引合并。

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

??說下邊這個查詢可能?到主鍵和idx_key1進?Intersection索引合并的操作：

SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';

為啥呢？憑啥呀？突然冒出這么兩個規定讓?家?臉懵逼，下邊我們慢慢品?品這?頭的?機。這話還得從InnoDB的索引結構說起，你要是記不清麻煩再回頭看看。對于InnoDB的?級索引來說，記錄先是按照索引列進?排序，如果該?級索引是?個聯合索引，那么會按照聯合索引中的各個列依次排序。??級索引的? 戶記錄是由索引列 + 主鍵構成的，?級索引列的值相同的記錄可能會有好多條，這些索引列的值相同的記錄?是按照主鍵的值進?排序的。

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

so？還是沒看懂根據?級索引查詢出的結果集是按照主鍵值排序的對使?Intersection索引合并有啥好處？?伙?，別忘了Intersection索引合并會把從多個? 級索引中查詢出的主鍵值求交集，如果從各個?級索引中查詢的到的結果集本身就是已經按照主鍵排好序的，那么求交集的過程就很easy啦。假設某個查詢使? Intersection索引合并的?式從idx_key1和idx_key2這兩個?級索引中獲取到的主鍵值分別是：

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

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

那么求交集的過程就是這樣：逐個取出這兩個結果集中最?的主鍵值，如果兩個值相等，則加?最后的交集結果中，否則丟棄當前較?的主鍵值，再取該丟棄的主鍵值所在結果集的后?個主鍵值來?較，直到某個結果集中的主鍵值?完了，如果還是覺得不太明?那繼續往下看：

先取出這兩個結果集中較?的主鍵值做?較，因為1 < 2，所以把idx_key1的結果集的主鍵值1丟棄，取出后邊的3來?較。

因為3 > 2，所以把idx_key2的結果集的主鍵值2丟棄，取出后邊的3來?較。

因為3 = 3，所以把3加?到最后的交集結果中，繼續兩個結果集后邊的主鍵值來?較。

后邊的主鍵值也不相等，所以最后的交集結果中只包含主鍵值3。

別看我們寫的啰嗦，這個過程其實可快了，時間復雜度是O(n)，但是如果從各個?級索引中查詢出的結果集并不是按照主鍵排序的話，那就要先把結果集中的主鍵值排序完再來做上邊的那個過程，就?較耗時了。

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

SELECT * FROM single_table WHERE key1 = 'a' AND id > 100;

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

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

Union合并

union是并集的意思，適用于不通索引的搜索條件之間使用or連接起來的情況。

情況?：?級索引列是等值匹配的情況，對于聯合索引來說，在聯合索引中的每個列都必須等值匹配，不能出現只出現匹配部分列的情況。

比如說下邊這個查詢可能?到idx_key1和idx_key_part這兩個二級索引進行Union索引合并的操作：

SELECT * FROM single_table WHERE key1 = 'a' OR ( key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');

而下邊這兩個查詢就不能進行Union索引合并：

SELECT * FROM single_table WHERE key1 > 'a' OR (key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');

SELECT * FROM single_table WHERE key1 = 'a' OR key_part1 = 'a';

第?個查詢是因為對key1進行了范圍匹配，第?個查詢是因為聯合索引idx_key_part中的key_part2列并沒有出現在搜索條件中，所以這兩個查詢不能進行?Union索引合并。

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

情況三：使用Intersection索引合并的搜索條件

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

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');

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

先按照搜索條件key1 = 'a' AND key3 = 'b'從索引idx_key1和idx_key3中使?Intersection索引合并的方式得到?個主鍵集合。

再按照搜索條件key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'從聯合索引idx_key_part中得到另?個主鍵集合。

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

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

Sort-Union合并

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

SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'

這是因為根據key1 < 'a'從idx_key1索引中獲取的?級索引記錄的主鍵值不是排好序的，根據key3 > 'z'從idx_key3索引中獲取的?級索引記錄的主鍵值也不是排好序的，但是key1 < 'a'和key3 > 'z'這兩個條件?特別讓我們動?，所以我們可以這樣：

先根據key1 < 'a'條件從idx_key1?級索引總獲取記錄，并按照記錄的主鍵值進?排序

再根據key3 > 'z'條件從idx_key3?級索引總獲取記錄，并按照記錄的主鍵值進?排序

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

我們把上述這種先按照?級索引記錄的主鍵值進?排序，之后按照Union索引合并?式執?的?式稱之為Sort-Union索引合并，很顯然，這種Sort-Union索引合并?單純的Union索引合并多了?步對?級索引記錄的主鍵值排序的過程。

為啥有Sort-Union索引合并，就沒有Sort-Intersection索引合并么？是的，的確沒有Sort-Intersection索引合并這么?說， SortUnion的適?場景是單獨根據搜索條件從某個?級索引中獲取的記錄數?較少，這樣即使對這些?級索引記錄按照主鍵值進?排序的成本也不會太? ?Intersection索引合并的適?場景是單獨根據搜索條件從某個?級索引中獲取的記錄數太多，導致回表開銷太?，合并后可以明顯降低回表開銷，但是如果加?Sort-Intersection后，就需要為?量的?級索引記錄按照主鍵值進?排序，這個成本可能?回表查詢都?了，所以也就沒有引?Sort-Intersection。