前言

接下來(lái)我們通過(guò)幾種情況來(lái)描述最左匹配原則的使用。首先如下所示，為userName、phone以及userDate創(chuàng)建聯(lián)合索引。

全列匹配

explain select * from user where userName ='admin' and phone ='13413413400' 
and userDate ='2000-04-29-12:53'

很明顯，當(dāng)按照索引中所有列進(jìn)行精確匹配（這里精確匹配指“=”或“IN”匹配）時(shí)，索引可以被用到。這里有一點(diǎn)需要注意，理論上索引對(duì)順序是敏感的，但是由于MySQL的查詢(xún)優(yōu)化器會(huì)自動(dòng)調(diào)整where子句的條件順序以使用適合的索引，例如我們將where中的條件順序顛倒,其效果是一樣的。

explain select * from user where userName ='admin'  
and userDate ='2000-04-29-12:53' and phone ='13413413400'

最左前綴匹配

比如我們where條件中只有userName：

explain select * from user where userName ='admin' 

當(dāng)查詢(xún)條件精確匹配索引的左邊連續(xù)一個(gè)或幾個(gè)列時(shí)，如userName，索引可以被用

到，但是只能用到一部分，即條件所組成的最左前綴。

上面的查詢(xún)從分析結(jié)果看用到了 const 索引，key_len為152，說(shuō)明只用到了索引的第一列前綴。

精確匹配

查詢(xún)條件用到了索引中列的精確匹配，但是中間某個(gè)條件未提供

比如下面我們沒(méi)有phone：

explain select * from user where userName ='admin' 
and userDate ='2000-04-29-12:53'

注意，上圖Extra中值是Using index condition，說(shuō)明MySQL正在使用覆蓋索引，它只掃描索引的數(shù)據(jù)而不是按索引次序的每一行。它比按索引次序全表掃描的開(kāi)銷(xiāo)要少很多。

此時(shí)索引使用情況和情況二相同，因?yàn)閜hone未提供，所以查詢(xún)只用到了索引的第一列，而后面的userDate雖然也在索引中，但是由于phone不存在而無(wú)法和左前綴連接，因此需要對(duì)結(jié)果進(jìn)行掃描過(guò)濾userDate。

如果想讓userDate也使用索引而不是where過(guò)濾，可以增加一個(gè)輔助索引<userName, userDate>，此時(shí)上面的查詢(xún)會(huì)使用這個(gè)索引。

查詢(xún)條件沒(méi)有指定索引第一列

由于不是最左前綴，索引這樣的查詢(xún)顯然用不到索引。

explain select * from user where userDate ='2000-04-29-12:53'

Using where：使用了用where子句來(lái)過(guò)濾結(jié)果集。這意味著MySQL服務(wù)器將在存儲(chǔ)引擎檢索行后再進(jìn)行過(guò)濾。

匹配某列的前綴字符串

explain select * from user where userName ='admin' and phone like '134%' 
and  userDate ='2000-04-29-12:53'

此時(shí)可以用到索引，如果通配符%不出現(xiàn)在開(kāi)頭，則可以用到索引，但根據(jù)具體情況不同可能只會(huì)用其中一個(gè)前綴。

范圍查詢(xún)

explain select * from user where userName ='admin' and phone >'134' 
and  userDate ='2000-04-29-12:53'

范圍列可以用到索引（必須是最左前綴），但是范圍列后面的列無(wú)法用到索引。同時(shí)，索引最多用于一個(gè)范圍列，因此如果查詢(xún)條件中有兩個(gè)范圍列則無(wú)法全用到索引。

這里特別要說(shuō)明MySQL一個(gè)有意思的地方，那就是僅用explain可能無(wú)法區(qū)分范圍索引和多值匹配，因?yàn)樵趖ype中這兩者都顯示為range。同時(shí)，用了“between”并不意味著就是范圍查詢(xún)，例如下面的查詢(xún)：

explain select * from user where userName ='admin' and phone 
between '13413413400' and '13513513500' and  userDate ='2000-04-29-12:53'

看起來(lái)是用了兩個(gè)范圍查詢(xún)，但作用于phone上的“BETWEEN”實(shí)際上相當(dāng)于“IN”，也就是說(shuō)phone實(shí)際是多值精確匹配?？梢钥吹竭@個(gè)查詢(xún)用到了索引全部三個(gè)列。因此在MySQL中要謹(jǐn)慎地區(qū)分多值匹配和范圍匹配，否則會(huì)對(duì)MySQL的行為產(chǎn)生困惑。

查詢(xún)條件中含有函數(shù)或表達(dá)式

如果查詢(xún)條件中含有函數(shù)或表達(dá)式，則MySQL不會(huì)為這列使用索引。

explain select * from user where userName ='admin' 
and left(phone ,6) and  userDate ='2000-04-29-12:53'

可以看到這個(gè)其實(shí)就是只用到了userName這一列的索引。

關(guān)于explain中各個(gè)關(guān)鍵字說(shuō)明可以參考：認(rèn)真學(xué)習(xí)傳送門(mén)

到此這篇關(guān)于MySQL最左匹配原則深入分析的文章就介紹到這了,更多相關(guān)MySQL最左匹配內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論