前言
之前在網(wǎng)上看到過很多關于mysql聯(lián)合索引最左前綴匹配的文章揪罕，自以為就了解了其原理，最近面試時和面試官交流忍些，發(fā)現(xiàn)遺漏了些東西坎怪，這里自己整理一下這方面的內(nèi)容。
最左前綴匹配原則
在mysql建立聯(lián)合索引時會遵循最左前綴匹配的原則嘁酿，即最左優(yōu)先男应，在檢索數(shù)據(jù)時從聯(lián)合索引的最左邊開始匹配，示例：
對列col1游桩、列col2和列col3建一個聯(lián)合索引
KEY test_col1_col2_col3 on test(col1,col2,col3);
聯(lián)合索引 test_col1_col2_col3實際建立了(col1)耐朴、(col1,col2)、(col,col2,col3)三個索引筛峭。
SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4”
上面這個查詢語句執(zhí)行時會依照最左前綴匹配原則影晓，檢索時會使用索引(col1,col2)進行數(shù)據(jù)匹配。
注意
索引的字段可以是任意順序的挂签，如：
SELECT * FROM test WHERE col1=“1” AND clo2=“2”
SELECT * FROM test WHERE col2=“2” AND clo1=“1”
這兩個查詢語句都會用到索引(col1,col2)饵婆，mysql創(chuàng)建聯(lián)合索引的規(guī)則是首先會對聯(lián)合合索引的最左邊的，也就是第一個字段col1的數(shù)據(jù)進行排序啦辐，在第一個字段的排序基礎上，然后再對后面第二個字段col2進行排序续挟。其實就相當于實現(xiàn)了類似 order by col1 col2這樣一種排序規(guī)則侥衬。
有人會疑惑第二個查詢語句不符合最左前綴匹配：首先可以肯定是兩個查詢語句都保函索引(col1,col2)中的col1跑芳、col2兩個字段直颅，只是順序不一樣，查詢條件一樣盆佣，最后所查詢的結(jié)果肯定是一樣的械荷。既然結(jié)果是一樣的，到底以何種順序的查詢方式最好呢痹兜？此時我們可以借助mysql查詢優(yōu)化器explain颤诀，explain會糾正sql語句該以什么樣的順序執(zhí)行效率最高，最后才生成真正的執(zhí)行計劃崖叫。
為什么要使用聯(lián)合索引
減少開銷归露。建一個聯(lián)合索引(col1,col2,col3)斤儿，實際相當于建了(col1),(col1,col2),(col1,col2,col3)三個索引。每多一個索引往果，都會增加寫操作的開銷和磁盤空間的開銷陕贮。對于大量數(shù)據(jù)的表，使用聯(lián)合索引會大大的減少開銷肮之！
覆蓋索引。對聯(lián)合索引(col1,col2,col3)眶明，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2筐高。那么MySQL可以直接通過遍歷索引取得數(shù)據(jù)丑瞧，而無需回表蜀肘，這減少了很多的隨機io操作。減少io操作西乖，特別的隨機io其實是dba主要的優(yōu)化策略涵卵。所以，在真正的實際應用中典鸡，覆蓋索引是主要的提升性能的優(yōu)化手段之一坏晦。
效率高。索引列越多昆婿，通過索引篩選出的數(shù)據(jù)越少仓蛆。有1000W條數(shù)據(jù)的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假設假設每個條件可以篩選出10%的數(shù)據(jù)看疙，如果只有單值索引，那么通過該索引能篩選出1000W10%=100w條數(shù)據(jù)施禾，然后再回表從100w條數(shù)據(jù)中找到符合col2=2 and col3= 3的數(shù)據(jù)搁胆，然后再排序，再分頁攀例；如果是聯(lián)合索引顾腊，通過索引篩選出1000w10% 10% *10%=1w，效率提升可想而知承耿！
引申
對于聯(lián)合索引(col1,col2,col3)，查詢語句SELECT * FROM test WHERE col2=2;是否能夠觸發(fā)索引加袋？
大多數(shù)人都會說NO职烧，實際上卻是YES。
原因：
EXPLAIN SELECT * FROM test WHERE col2=2;
EXPLAIN SELECT * FROM test WHERE col1=1;
觀察上述兩個explain結(jié)果中的type字段蚀之。查詢中分別是：
- type: index
- type: ref
index：這種類型表示mysql會對整個該索引進行掃描足删。要想用到這種類型的索引，對這個索引并無特別要求失受，只要是索引，或者某個聯(lián)合索引的一部分痪署，mysql都可能會采用index類型的方式掃描兄旬。但是呢，缺點是效率不高悯森，mysql會從索引中的第一個數(shù)據(jù)一個個的查找到最后一個數(shù)據(jù)罐孝，直到找到符合判斷條件的某個索引肥缔。所以，上述語句會觸發(fā)索引改艇。
ref：這種類型表示mysql會根據(jù)特定的算法快速查找到某個符合條件的索引坟岔，而不是會對索引中每一個數(shù)據(jù)都進行一一的掃描判斷，也就是所謂你平常理解的使用索引查詢會更快的取出數(shù)據(jù)承疲。而要想實現(xiàn)這種查找，索引卻是有要求的兄世，要實現(xiàn)這種能快速查找的算法啊研，索引就要滿足特定的數(shù)據(jù)結(jié)構(gòu)。簡單說党远，也就是索引字段的數(shù)據(jù)必須是有序的沟娱，才能實現(xiàn)這種類型的查找，才能利用到索引花沉。
總結(jié)
以上所述是小編給大家介紹的Mysql聯(lián)合索引最左匹配原則碱屁，希望對大家有所幫助，如果大家有任何疑問請給我留言娩脾，小編會及時回復大家的。
