MySQL最左匹配原則深入分析

Posted on by

前言

接下來我們通過幾種情況來描述最左匹配原則的使用。首先如下所示，為userName、phone以及userDate創建聯合索引。

全列匹配

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

很明顯，當按照索引中所有列進行精確匹配（這裡精確匹配指“=”或“IN”匹配）時，索引可以被用到。這裡有一點需要註意，理論上索引對順序是敏感的，但是由於MySQL的查詢優化器會自動調整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'

當查詢條件精確匹配索引的左邊連續一個或幾個列時，如userName，索引可以被用

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

上面的查詢從分析結果看用到瞭 const 索引，key_len為152，說明隻用到瞭索引的第一列前綴。

精確匹配

查詢條件用到瞭索引中列的精確匹配，但是中間某個條件未提供

比如下面我們沒有phone：

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

註意，上圖Extra中值是Using index condition，說明MySQL正在使用覆蓋索引，它隻掃描索引的數據而不是按索引次序的每一行。它比按索引次序全表掃描的開銷要少很多。

此時索引使用情況和情況二相同，因為phone未提供，所以查詢隻用到瞭索引的第一列，而後面的userDate雖然也在索引中，但是由於phone不存在而無法和左前綴連接，因此需要對結果進行掃描過濾userDate。

如果想讓userDate也使用索引而不是where過濾，可以增加一個輔助索引<userName, userDate>，此時上面的查詢會使用這個索引。

查詢條件沒有指定索引第一列

由於不是最左前綴，索引這樣的查詢顯然用不到索引。

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

Using where：使用瞭用where子句來過濾結果集。這意味著MySQL服務器將在存儲引擎檢索行後再進行過濾。

匹配某列的前綴字符串

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

此時可以用到索引，如果通配符%不出現在開頭，則可以用到索引，但根據具體情況不同可能隻會用其中一個前綴。

范圍查詢

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

范圍列可以用到索引（必須是最左前綴），但是范圍列後面的列無法用到索引。同時，索引最多用於一個范圍列，因此如果查詢條件中有兩個范圍列則無法全用到索引。

這裡特別要說明MySQL一個有意思的地方，那就是僅用explain可能無法區分范圍索引和多值匹配，因為在type中這兩者都顯示為range。同時，用瞭“between”並不意味著就是范圍查詢，例如下面的查詢：

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

看起來是用瞭兩個范圍查詢，但作用於phone上的“BETWEEN”實際上相當於“IN”，也就是說phone實際是多值精確匹配。可以看到這個查詢用到瞭索引全部三個列。因此在MySQL中要謹慎地區分多值匹配和范圍匹配，否則會對MySQL的行為產生困惑。

查詢條件中含有函數或表達式

如果查詢條件中含有函數或表達式，則MySQL不會為這列使用索引。

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

可以看到這個其實就是隻用到瞭userName這一列的索引。

關於explain中各個關鍵字說明可以參考：認真學習傳送門

到此這篇關於MySQL最左匹配原則深入分析的文章就介紹到這瞭,更多相關MySQL最左匹配內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：