【轉】mysql索引最左匹配原則的理解

標籤：為我   好的   har   actual   mysql索引   int   char   not   答案   

沈傑

連結：https://www.zhihu.com/question/36996520/answer/93256153

來源：知乎

 

CREATE TABLE `student` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) DEFAULT NULL,  `cid` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `name_cid_INX` (`name`,`cid`),  KEY `name_INX` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

執行1:

EXPLAIN SELECT * FROM student WHERE    name=‘小紅‘;

執行2:

EXPLAIN SELECT * FROM student WHERE   cid=1;

EXPLAIN SELECT * FROM student WHERE   cid=1 AND name=‘小紅‘;

為什麼還能匹配索引？ 

你的疑問是：sql查詢用到索引的條件是必須要遵守最左首碼原則，為什麼上面兩個查詢還能用到索引？
---------------------------------------------------------------------------------------------------------------------------

講上面問題之前，我先補充一些知識，因為我覺得你對索引理解是狹隘的：
上述你的兩個查詢的explain結果中顯示用到索引的情況類型是不一樣的。,可觀察explain結果中的type欄位。你的查詢中分別是：
1. type: index
2. type: ref

解釋：
index：這種類型表示是mysql會對整個該索引進行掃描。

　　　  要想用到這種類型的索引，對這個索引並無特別要求，只要是索引，或者某個複合索引的一部分，mysql都可能會採用index類型的方式掃描。

          但是呢，缺點是效率不高，mysql會從索引中的第一個資料一個個的尋找到最後一個資料，直到找到符合判斷條件的某個索引。

所以：對於你的第一條語句：

EXPLAIN SELECT * FROM student WHERE   cid=1;

判斷條件是cid=1,而cid是(name,cid)複合索引的一部分，沒有問題，可以進行index類型的索引掃描方式。explain顯示結果使用到了索引，是index類型的方式。

---------------------------------------------------------------------------------------------------------------------------

ref：這種類型表示mysql會根據特定的演算法快速尋找到某個合格索引，而不是會對索引中每一個資料都進行一 一的掃描判斷，也就是所謂你平常理解的使用索引查詢會更快的取出資料。

　　 而要想實現這種尋找，索引卻是有要求的，要實現這種能快速尋找的演算法，索引就要滿足特定的資料結構。

       簡單說，也就是索引欄位的資料必須是有序的，才能實現這種類型的尋找，才能利用到索引。

有些瞭解的人可能會問，索引不都是一個有序排列的資料結構麼。不過答案說的還不夠完善，那隻是針對單個索引，而複合索引的情況有些同學可能就不太瞭解了。

下面就說下複合索引：
以該表的(name,cid)複合索引為例,它內部結構簡單說就是下面這樣排列的： 

mysql建立複合索引的規則是首先會對複合索引的最左邊的，也就是第一個name欄位的資料進行排序，在第一個欄位的排序基礎上，然後再對後面第二個的cid欄位進行排序。

其實就相當於實現了類似 order by name cid這樣一種定序。

所以：第一個name欄位是絕對有序的，而第二欄位就是無序的了。

        所以通常情況下，直接使用第二個cid欄位進行條件判斷是用不到索引的，當然，可能會出現上面的使用index類型的索引。

        這就是所謂的mysql為什麼要強調最左首碼原則的原因。

那麼什麼時候才能用到呢?
　　  當然是cid欄位的索引資料也是有序的情況下才能使用咯，什麼時候才是有序的呢？　　  觀察可知，當然是在name欄位是等值匹配的情況下，cid才是有序的。　　  發現沒有，觀察兩個name名字為 c 的cid欄位是不是有序的呢。從上往下分別是4 5。
　　  這也就是mysql索引規則中要求複合索引要想使用第二個索引，必須先使用第一個索引的原因。（而且第一個索引必須是等值匹配）。
---------------------------------------------------------------------------------------------------------------------------
所以對於你的這條sql查詢：

EXPLAIN SELECT * FROM student WHERE   cid=1 AND name=‘小紅‘;

沒有錯，而且複合索引中的兩個索引欄位都能很好的利用到了！因為語句中最左面的name欄位進行了等值匹配，所以cid是有序的，也可以利用到索引了。

你可能會問：

　　   我建的索引是(name,cid)。

　　   而我查詢的語句是cid=1 AND name=‘小紅‘; 我是先查詢cid，再查詢name的，不是先從最左面查的呀？

 　　  好吧，我再解釋一下這個問題：首先可以肯定的是把條件判斷反過來變成這樣 name=‘小紅‘ and cid=1; 最後所查詢的結果是一樣的。
         那麼問題產生了？既然結果是一樣的，到底以何種順序的查詢方式最好呢？

所以　　 　　　而此時那就是我們的mysql查詢最佳化工具該登場了，mysql查詢最佳化工具會判斷糾正這條sql語句該以什麼樣的順序執行效率最高，最後才產生真正的執行計畫。         所以，當然是我們能盡量的利用到索引時的查詢順序效率最高咯，所以mysql查詢最佳化工具會最終以這種順序進行查詢執行。 

沈傑
連結：https://www.zhihu.com/question/36996520/answer/93256153
來源：知乎

【轉】mysql索引最左匹配原則的理解