mysql索引最左匹配原則的理解?
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
隨便建了一個student表做測試。
create INDEX name_cid_INX ON student(name,cid)
;
create INDEX name_INX ON student(name);
建了兩個索引，故意這樣建的。
執行1:
EXPLAIN SELECT * FROM student WHERE name="小紅";
依據mysql索引最左匹配原則，兩個索引都匹配上了，這個沒有問題。。
執行2:
EXPLAIN SELECT * FROM student WHERE cid=1;
EXPLAIN SELECT * FROM student WHERE cid=1 AND name="小紅";
為什麼還能匹配索引。
我來說一下吧：
這是你的表結構，有三個欄位，分別是id,name,cidCREATE 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`),
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
索引方面：id是主鍵，(name,cid)是一個多列索引。
-----------------------------------------------------------------------------下面是你有疑問的兩個查詢：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查詢優化器會最終以這種順序進行查詢執行。第三個查詢 WHERE cid=1 AND name="小紅" ，正好包含 name和cid,等同於 WHERE name="小紅" AND cid=1， 所以應用name_cid_IDX沒問題。
第二個查詢只有CID,雖然沒法直接從name_cid_IDX中定位到數據，但在name重複比較多的情況下，從每個name的子集合中搜索cid會比全表掃描要好一些，所以還是用到了name_cid_IDX.大概相當於 O(m*log(n))和O(m*n)的區別，這可能是mysql的邏輯，mysql顯然不知道人的名字重複的概率是很小的，這個執行計劃可能還不如全表掃描。不過這不是mysql的問題，如果name重複比較少，name+cid的複合索引就沒什麼意義了，還不是單name索引節省空間。非DBA,僅猜測。問題一，應該是索引覆蓋。證明，extra，欄位內有 using index，表示查詢使用了索引覆蓋。該表如果你改成 myisam，則不會使用索引，因為innodb主鍵採用的聚簇結構，而myisam不是。問題二：and條件，name="小紅", 這個必須能用到索引呀。 mysql優化器，不會這麼笨蛋的
謝邀。這方面的內容以前沒有注意過啊，我自己上Google搜索了一下，看到了有關MySQL索引的相關技術原理的解釋，做個搬運工：http://tech.meituan.com/mysql-index.html簡單來說，就是MySQL採用b+樹原理查詢，每次查詢複合欄位從左到右的屬性。在執行2的第一行代碼時，查詢值只有cid，而你的name_cid_index的首欄位是name，所以MySQL找不到該索引。
在執行2的第二行時，情況發生變化，你的查詢值變成了（cid，name），mysql會一直向右匹配直到遇到範圍查詢(&>、&<、between、like)就停止匹配，因而會查詢到name欄位。
希望能對你有所幫助。這裡需要提醒的是：mysql的執行計劃和查詢的實際執行過程並不完全吻合。如何證明這一點呢？
真正的執行過程可以通過mysql的trace工具來分析。1，針對執行2EXPLAIN SELECT * FROM student WHERE cid=1;
這裡只顯示trace結果的一部分內容：
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`student`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"rows": 1,
"cost": 1.2,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 1.2,
"rows_for_plan": 1,
"chosen": true
}
] /* considered_execution_plans */
},
可以看到並沒有使用索引，而是進行了全表掃描。
2，下面再來看一下執行1的trace： {
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`student`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "name_cid_INX",
"rows": 1,
"cost": 1.2,
"chosen": true
},
{
"access_type": "ref",
"index": "name_INX",
"rows": 1,
"cost": 1.2,
"chosen": false
},
{
"access_type": "scan",
"cause": "covering_index_better_than_full_scan",
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 1.2,
"rows_for_plan": 1,
"chosen": true
}
] /* considered_execution_plans */
},
可以看出最終使用的索引是name_cid_INX。
3，where中and條件的先後順序對如何選擇索引是無關的。因為優化器會去分析判斷選用哪個索引。你的這兩條查詢，碰巧用到了MySQL的覆蓋索引。
這個需求，一般會說把name_INX 這個索引去掉，因為最左原則重複了。
關於最左原則 有回答已經解釋的很好了。
在企業的生產環境優化上，一般會對需求進行溝通，比如cid查詢的qps，緩存情況來看下是否需要來新建個索引來避免對name_cid_INX的掃描，或者說對於業務需求方的查詢定性，去掉name查詢，或者去掉cid查詢sql 解釋器的機制其實挺複雜的，建議從explain深入，碰到沒見過的迅速查詢資料。當然其實mysql在單表查詢的時候有個index merge的sql執行計劃：https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html可以在單個欄位上建好索引，然後解釋器會自動優化。不過本質上還是分兩步走的seek，畢竟還是btree的存儲，而且一般生產對索引數量會有比較嚴格的要求，不會建那麼多的單列索引，所以還是不建議這麼搞了。 ps:在生產環節上我們還碰到了分區表在5.6.16之前版本 index merge產生了返回數據丟失，後來在查詢上加了個use index() 的補丁，很被動……題外話：如果以後碰到大量的olap環境，建議是還是在業務流程和架構上做好優化，拋棄無用的查詢邏輯，olap環境還是交給es，spark這種的專門做的環境。pss:最近在測試tidb，面對olap的seek貌似有不錯的改進，後續會持續跟進這個。
=和in 條件被優化
推薦閱讀：
※is NULL和= NULL，is not NULL和!= NULL有什麼區別？
※專精 Oracle 還是 MySQL？
※哪些互聯網公司的資料庫是選擇Oracle的，為什麼？
※學習 Oracle 和 MySQL 哪個更有前途？
※想非常精通資料庫的話需要哪方面數學？