字串的公用首碼對Mysql B+樹查詢影響回溯分析

標籤：article   回溯   欄位   arch   pad   結果   block   pop   讀取   

    年前項目組接公眾號。

上線之後，跟相關的用cid列的查詢會話的SQL變慢了幾十倍！思考這個問題思考了非常久。從出現以來一直是我心頭的一個結。cid這一列是建了索引的，普通的cid列更新都沒問題，為何僅僅有的有問題？同樣的首碼又是怎樣影響索引的？
   分析過程    1.explain下cid的查詢。的cid會以mid-qqwanggou001為首碼插入資料

explain select *from analysis_sessionswhere cid = "mid-qqwanggou001-b99359d9054171901c0"

分析結果例如以下：

watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" width="867" height="100" />

從explain分析能夠看出。這個查詢使用了索引，可是innodb覺得有165萬行資料須要給mysqlserver篩選（也就是用where條件過濾）。

假設這些龐大的資料在記憶體，遍曆一遍花不了多少時間。可是極有可能，這些資料是在磁碟上的。這麼多的資料從磁碟讀取然後載入記憶體。大量磁碟IO必定是十分的耗時的。

相比記憶體的電子運動。磁碟機械臂的物理運動要慢好幾個數量級。

2.分析普通cid的查詢

取資料進行explain。cid = "sid-a2f9047ddf528d837e5f60843c83aae9"。這個資料是不帶公用首碼的。

   

explain select *from analysis_sessionswhere cid = "sid-a2f9047ddf528d837e5f60843c83aae9"

分析結果例如以下：

同樣的列，同樣的索引。這次儲存引擎向mysqlserver僅僅返回了一行資料。也就是說innodb僅僅須要讀取一個二級索引的葉子節點。

相對於上面那個sql的IO，壓力顯然小非常多。

初步分析結論：帶有長首碼的cid查詢。innodb儲存引擎會向mysql上端server返回百萬層級的資料。

這僅僅是現象，我還是想問，同樣的表，同樣的列，同樣的索引結構（B+樹索引）。同樣的查詢，僅僅不同的資料。結果為何有差麼大的區別？

近一步分析

糾結這個問題非常久了，直到前天晚上散步時候。無意的會想到了 explain結果的key_len這一列。這一列我從來不看，覺得沒用。可是27與cid這一列50個varchar的定義格格不入。27明顯小於50，首先能夠肯定，這個索引用的是首碼索引，說白了，截取了字串的前面一部分作為索引資料。analysis_session表用的gbk編碼。也就是說，索引須要2個位元組表示一個varchar。解釋一下key_len

    27 = 2 * 12 + 2 + 1

27位的索引，僅僅索引了前面12個字元。中間的2儲存長度。後面的一個位元組儲存Null資訊，由於這一列是同意Null的。

終於結論：問題到這已經非常明了了，cid的首碼是17個字元的，大於首碼索引的12個字元，也就是說。 全部儲存cid資料（百萬層級）B+樹葉子節點將僅僅有一個B+樹非分葉節點的指標指向這裡。於是。當你查cid相關的資料時，全部cid將被返回給mysqlserver進行where過濾了，效率上講，這是非常恐怖的。

索引確實還是被用上了。不然會造成全表掃描。可是這個資料設計的有問題。B+樹的尋找效率是O(LogN)的，可是遇上這個資料，立馬變成O(N),相當於一個局部全表掃描。

    那麼合理的猜測。僅僅要有新增的cid，cid的查詢僅僅會變的更慢。

引申，更佳的代碼 practice：

    varchar，blob, text等邊長資料建索引的時候。資料庫會自己主動建首碼索引，於是B+樹不會索引整個欄位的部分。非常多同學喜歡用首碼作為字串的標誌，這次要注意了，有前車之鑒了。首碼存入mysql之後會減少檢索效率，首碼越長。B+樹查詢的效率越低。

    這裡給出代碼的建議：

    1.將首碼作為尾碼，startWith改為endWith

    2.不要嘗試尾碼模糊搜尋，like "%.com",這樣的做法更糟糕，全然用不了索引，於是全表掃描。

字串的公用首碼對Mysql B+樹查詢影響回溯分析