不會like優(yōu)化欠下的一頓海底撈

hello大家好 我是大家的學(xué)習(xí)成長小伙伴Captain

昨天呢，下班之后在和表妹一起吃飯的時候，表妹突然腦子抽抽的問了我一句：表哥，今天我們leader要求我優(yōu)化我寫的like語句，我差點就反手一個大嘴巴子了，這玩意怎么優(yōu)化，真是的

此時的我一臉無奈的看著表妹，默默地?fù)u了搖頭

表妹看我狀態(tài)之后覺得事情并不是那么簡單，于是質(zhì)問我怎么回事，我不太想告訴我，于是最終以一頓海底撈的報酬成交了

給表妹分享了之后，順便啊我也把這個知識點整理了出來，給大家分享一波也

? ? ? ??? ? ?

聯(lián)合索引

也就是復(fù)合索引，我們平時可能使用的最多的是單獨索引，也就是一列作為索引，因為很多種情況下一列的重復(fù)度就足夠高了

那么什么時候使用聯(lián)合索引呢，也就是使用場景

當(dāng)需要用到多個字段進(jìn)行索引的時候，但是每個索引的區(qū)分度不是非常高，這時候使用聯(lián)合索引的效率就會變得很高了

如果單個字段索引就很高了，重復(fù)率就很低，那就沒必要使用聯(lián)合索引了，只需要把那個區(qū)分度高的字段拎出來作為索引就行了

你想啊，比如要找一個學(xué)校的學(xué)生，學(xué)號或者是姓名的區(qū)分度應(yīng)該都很高，重復(fù)率都比較低，所以只需要根據(jù)這些作為索引就行了，使用姓名或者學(xué)號就可以直接找到相應(yīng)的學(xué)生了

但是如果我們要統(tǒng)計全省的學(xué)生，如果多個學(xué)校之間的姓名重復(fù)度比較高，那只使用姓名就是一個糟糕的事情了，你想啊，如果按照姓名='張三'來搜索，搜索出幾十個甚至幾百個張三來，那剩下的還得根據(jù)別的字段才能區(qū)分出真正想要的那個張三

此時，聯(lián)合索引便派上用場了

我們可以讓學(xué)校編號+姓名作為聯(lián)合索引來使用，這樣通過這兩個就可以使區(qū)分度變得很高咯，一個學(xué)校的叫張三的可能就只有一兩個

只通過學(xué)校找可能需要找出很多學(xué)生，然后再通過張三來遍歷?；蛘咧苯油ㄟ^張三來找到幾百個張三，然后再根據(jù)別的特性找到我們要找的張三

有人可能會問，可以對這兩個字段分別建立索引啊，當(dāng)然也是可以的，但是這樣的效率遠(yuǎn)不如聯(lián)合索引，而且也會比較浪費空間，兩個索引就需要簡歷兩個B+樹，所以是比較浪費空間的

優(yōu)先級

在聯(lián)合索引中，有優(yōu)先級這一說，也就是建立聯(lián)合索引的順序是很關(guān)鍵的，并不是說隨便的順序

一句話，區(qū)分度高的需要放到聯(lián)合索引的前面

其實就是前面的區(qū)分度越高，后面的符合數(shù)據(jù)的也就越少了，這樣后面就只需要判斷更少的數(shù)據(jù)，就可以得到結(jié)果了

結(jié)構(gòu)

聯(lián)合索引的結(jié)構(gòu)同樣也是B+樹，等同于是B+樹排序的時候會按照聯(lián)合索引的優(yōu)先級來排序，聯(lián)合索引學(xué)校+姓名，也就是先按照學(xué)校進(jìn)行排序，學(xué)校相同的才會對姓名進(jìn)行第二次的排序

聯(lián)合索引只需要構(gòu)建一顆B+樹，使用多個單獨索引就需要構(gòu)建多個B+樹，B+樹的葉子節(jié)點存儲的都是主鍵

最左匹配

這個也是屬于聯(lián)合索引的一大特點，也就是使用聯(lián)合索引必須符合最左匹配原則，這個玩意是個什么呢，給大家解釋下

上面的聯(lián)合索引學(xué)校+姓名，我們一般使用

select * from student where school = '育才' and name = '張三'

使用上面這種是可以使用聯(lián)合索引的，因為符合最左匹配原則了，也就是我們使用SQL查詢的時候必須按照這個順序使用才可以

如果我們把SQL語句換成

select?*?from?student?where name = '張三'

這種情況下，是不會使用索引的

因為我們上面使用的是先按照學(xué)校作為第一比較順序構(gòu)建的B+樹，我們直接使用name是無法根據(jù)聯(lián)合索引構(gòu)建的B+樹進(jìn)行查詢的

所以這就導(dǎo)致了無法使用索引

舉個形象的例子，我們把索引理解成字典中的拼音目錄，如果我們想要查找其中的"中"字，我們可以根據(jù)首字母是z來進(jìn)行查詢

但是如果我們不知道首字母z，只知道尾部是ong，那這就沒法走索引了啊，這種拼音目錄就沒法起作用了，因為帶有ong的分布在整本字典的各個角落

第一頁可能就有，最后一頁可能也有

索引下推ICP

mysql5.6開始支持ICP，index?condition pushdown，不支持ICP之前，當(dāng)進(jìn)行索引查詢的時候，首先根據(jù)索引來查詢大量的數(shù)據(jù)，然后根據(jù)剩余的where條件來進(jìn)行過濾，這樣掃描了大量的沒必要的數(shù)據(jù)，造成了資源的浪費?

默認(rèn)參數(shù)

index_condition_pushdown：索引條件下推默認(rèn)開啟，設(shè)置為off關(guān)閉ICP特性。

在學(xué)校+姓名作為復(fù)合索引之后idx_school_name(school,name)，我們看SQL語句

select?*?from?student?where?school?=?'育才'?and?name?like?'%張三%'

在ICP關(guān)閉的情況下，上面的SQL語句會首先按照走復(fù)合索引中的school，查出復(fù)合條件的數(shù)據(jù)，然后在server端再過濾掉所有不符合name like '%張三%'的數(shù)據(jù)，剩下復(fù)合條件的數(shù)據(jù)

這樣的話，其實造成了在server端大量的無效的數(shù)據(jù)掃描

開啟索引下推ICP之后，上面的SQL走了第一部分的SQL索引之后，不會直接將數(shù)據(jù)全部讀取到server端，而是直接根據(jù)聯(lián)合索引來執(zhí)行第二部分的判斷，最后只會讀取這個聯(lián)合索引，然后第二個條件直接判斷即可，不需要將復(fù)合school條件的數(shù)據(jù)全部讀取出來之后在比較

只需要在server端篩選出想要的記錄即可，極大的減少了不必要的IO的開銷