mysql 左查詢(xún)，mysql聯(lián)合索引最左匹配原因？

最左前綴匹配原則

在mysql建立聯(lián)合索引時(shí)會(huì)遵循最左前綴匹配的原則，即最左優(yōu)先，在檢索數(shù)據(jù)時(shí)從聯(lián)合索引的最左邊開(kāi)始匹配，

示例：

對(duì)列Gid、列Cid和列Sid建一個(gè)聯(lián)合索引

聯(lián)合索引 uni_Gid_Cid_SId 實(shí)際建立了(Gid)、(Gid,Cid)、(Gid,Cid,SId)三個(gè)索引。

插入模擬數(shù)據(jù)

查詢(xún)實(shí)例：

上面這個(gè)查詢(xún)語(yǔ)句執(zhí)行時(shí)會(huì)依照最左前綴匹配原則，檢索時(shí)會(huì)使用索引(Gid,Cid)進(jìn)行數(shù)據(jù)匹配。

注意

索引的字段可以是任意順序的，如：

這兩個(gè)查詢(xún)語(yǔ)句都會(huì)用到索引(Gid,Cid)，mysql創(chuàng)建聯(lián)合索引的規(guī)則是首先會(huì)對(duì)聯(lián)合合索引的最左邊的，也就是第一個(gè)字段Gid的數(shù)據(jù)進(jìn)行排序，在第一個(gè)字段的排序基礎(chǔ)上，然后再對(duì)后面第二個(gè)字段Cid進(jìn)行排序。其實(shí)就相當(dāng)于實(shí)現(xiàn)了類(lèi)似 order by Gid Cid這樣一種排序規(guī)則。

有人會(huì)疑惑第二個(gè)查詢(xún)語(yǔ)句不符合最左前綴匹配：首先可以肯定是兩個(gè)查詢(xún)語(yǔ)句都保函索引(Gid,Cid)中的Gid、Cid兩個(gè)字段，只是順序不一樣，查詢(xún)條件一樣，最后所查詢(xún)的結(jié)果肯定是一樣的。既然結(jié)果是一樣的，到底以何種順序的查詢(xún)方式最好呢？此時(shí)我們可以借助mysql查詢(xún)優(yōu)化器explain，explain會(huì)糾正sql語(yǔ)句該以什么樣的順序執(zhí)行效率最高，最后才生成真正的執(zhí)行計(jì)劃。

那么問(wèn)題產(chǎn)生了？既然結(jié)果是一樣的，到底以何種順序的查詢(xún)方式最好呢？

所以，而此時(shí)那就是我們的mysql查詢(xún)優(yōu)化器該登場(chǎng)了，sql語(yǔ)句中字段的順序不需要和聯(lián)合索引中定義的字段順序一致，查詢(xún)優(yōu)化器會(huì)自己調(diào)整順序，mysql查詢(xún)優(yōu)化器會(huì)判斷糾正這條sql語(yǔ)句該以什么樣的順序執(zhí)行效率最高，最后才生成真正的執(zhí)行計(jì)劃。所以，當(dāng)然是我們能盡量的利用到索引時(shí)的查詢(xún)順序效率最高咯，所以mysql查詢(xún)優(yōu)化器會(huì)最終以這種順序進(jìn)行查詢(xún)執(zhí)行。

為什么要使用聯(lián)合索引

減少開(kāi)銷(xiāo)。建一個(gè)聯(lián)合索引(Gid,Cid,SId)，實(shí)際相當(dāng)于建了(Gid)、(Gid,Cid)、(Gid,Cid,SId)三個(gè)索引。每多一個(gè)索引，都會(huì)增加寫(xiě)操作的開(kāi)銷(xiāo)和磁盤(pán)空間的開(kāi)銷(xiāo)。對(duì)于大量數(shù)據(jù)的表，使用聯(lián)合索引會(huì)大大的減少開(kāi)銷(xiāo)！

覆蓋索引。對(duì)聯(lián)合索引(Gid,Cid,SId)，如果有如下的sql: select Gid,Cid,SId from student where Gid=1 and Cid=2。那么MySQL可以直接通過(guò)遍歷索引取得數(shù)據(jù)，而無(wú)需回表，這減少了很多的隨機(jī)io操作。減少io操作，特別的隨機(jī)io其實(shí)是dba主要的優(yōu)化策略。所以，在真正的實(shí)際應(yīng)用中，覆蓋索引是主要的提升性能的優(yōu)化手段之一。

效率高。索引列越多，通過(guò)索引篩選出的數(shù)據(jù)越少。有1000W條數(shù)據(jù)的表，有如下sql:select from table where Gid=1 and Cid=2 and SId=3,假設(shè)假設(shè)每個(gè)條件可以篩選出10%的數(shù)據(jù)，如果只有單值索引，那么通過(guò)該索引能篩選出1000W10%=100w條數(shù)據(jù)，然后再回表從100w條數(shù)據(jù)中找到符合Gid=2 and Cid= 3的數(shù)據(jù)，然后再排序，再分頁(yè)；如果是聯(lián)合索引，通過(guò)索引篩選出1000w10% 10% *10%=1w，效率提升可想而知！

缺點(diǎn)。聯(lián)合索引越多，索引列越多，則創(chuàng)建的索引越多，索引都是存儲(chǔ)在磁盤(pán)里的，通過(guò)索引算法(Btree代表索引算法使用二叉樹(shù)的形式來(lái)做索引的)來(lái)查找數(shù)據(jù)，的確可以極大的提高查詢(xún)效率，但是與此同時(shí)增刪改的同時(shí)，需要更新索引，同樣是需要花時(shí)間的，并且索引所占的磁盤(pán)空間也不小。

建議。單表盡可能不要超過(guò)一個(gè)聯(lián)合索引，單個(gè)聯(lián)合索引不超過(guò)3個(gè)字段。

引申

對(duì)于聯(lián)合索引(Gid,Cid,SId)，查詢(xún)語(yǔ)句SELECT * FROM student WHERE Cid = 465176354 ;是否能夠觸發(fā)索引？大多數(shù)人都會(huì)說(shuō)NO，實(shí)際上卻是YES。

原因：

觀察上述兩個(gè)explain結(jié)果中的type字段。查詢(xún)中分別是：

type: indextype: refindex：這種類(lèi)型表示mysql會(huì)對(duì)整個(gè)該索引進(jìn)行掃描。要想用到這種類(lèi)型的索引，對(duì)這個(gè)索引并無(wú)特別要求，只要是索引，或者某個(gè)聯(lián)合索引的一部分，mysql都可能會(huì)采用index類(lèi)型的方式掃描。但是呢，缺點(diǎn)是效率不高，mysql會(huì)從索引中的第一個(gè)數(shù)據(jù)一個(gè)個(gè)的查找到最后一個(gè)數(shù)據(jù)，直到找到符合判斷條件的某個(gè)索引。所以，上述語(yǔ)句會(huì)觸發(fā)索引。

ref：這種類(lèi)型表示mysql會(huì)根據(jù)特定的算法快速查找到某個(gè)符合條件的索引，而不是會(huì)對(duì)索引中每一個(gè)數(shù)據(jù)都進(jìn)行一一的掃描判斷，也就是所謂你平常理解的使用索引查詢(xún)會(huì)更快的取出數(shù)據(jù)。而要想實(shí)現(xiàn)這種查找，索引卻是有要求的，要實(shí)現(xiàn)這種能快速查找的算法，索引就要滿(mǎn)足特定的數(shù)據(jù)結(jié)構(gòu)。簡(jiǎn)單說(shuō)，也就是索引字段的數(shù)據(jù)必須是有序的，才能實(shí)現(xiàn)這種類(lèi)型的查找，才能利用到索引。