索引失效底層原理分析，這么多年終于有人講清楚了
前言
吊打面試官又來(lái)啦，今天我們講講MySQL索引為什么會(huì)失效，很多文章和培訓(xùn)機(jī)構(gòu)的教程，都只會(huì)告訴你，在什么情況下索引會(huì)失效。
比如：沒(méi)遵循最佳左前綴法則、范圍查詢(xún)的右邊會(huì)失效、like查詢(xún)用不到索引等等
但是沒(méi)有一個(gè)人告訴你，索引失效的原理是什么，老哥今天就告訴大家，讓你們知其然，還要知其所以然。
單值索引B+樹(shù)圖
單值索引在B+樹(shù)的結(jié)構(gòu)里，一個(gè)節(jié)點(diǎn)只存一個(gè)鍵值對(duì)
聯(lián)合索引
開(kāi)局一張圖，由數(shù)據(jù)庫(kù)的a字段和b字段組成一個(gè)聯(lián)合索引。
從本質(zhì)上來(lái)說(shuō)，聯(lián)合索引也是一個(gè)B+樹(shù)，和單值索引不同的是，聯(lián)合索引的鍵值對(duì)不是1，而是大于1個(gè)。
a, b 排序分析
a順序：1，1，2，2，3，3
b順序：1，2，1，4，1，2
大家可以發(fā)現(xiàn)a字段是有序排列，b字段是無(wú)序排列（因?yàn)锽+樹(shù)只能選一個(gè)字段來(lái)構(gòu)建有序的樹(shù)）
一不小心又會(huì)發(fā)現(xiàn)，在a相等的情況下，b字段是有序的。
大家想想平時(shí)編程中我們要對(duì)兩個(gè)字段排序，是不是先按照第一個(gè)字段排序，如果第一個(gè)字段出現(xiàn)相等的情況，就用第二個(gè)字段排序。這個(gè)排序方式同樣被用到了B+樹(shù)里。
分析最佳左前綴原理
先舉一個(gè)遵循最佳左前綴法則的例子
- select * from testTable where a=1 and b=2
分析如下：
首先a字段在B+樹(shù)上是有序的，所以我們可以通過(guò)二分查找法來(lái)定位到a=1的位置。
其次在a確定的情況下，b是相對(duì)有序的，因?yàn)橛行?，所以同樣可以通過(guò)二分查找法找到b=2的位置。
再來(lái)看看不遵循最佳左前綴的例子
- select * from testTable where b=2
分析如下：
我們來(lái)回想一下b有順序的前提：在a確定的情況下。
現(xiàn)在你的a都飛了，那b肯定是不能確定順序的，在一個(gè)無(wú)序的B+樹(shù)上是無(wú)法用二分查找來(lái)定位到b字段的。
所以這個(gè)時(shí)候，是用不上索引的。大家懂了嗎？
范圍查詢(xún)右邊失效原理
舉例
- select * from testTable where a>1 and b=2
分析如下：
首先a字段在B+樹(shù)上是有序的，所以可以用二分查找法定位到1，然后將所有大于1的數(shù)據(jù)取出來(lái)，a可以用到索引。
b有序的前提是a是確定的值，那么現(xiàn)在a的值是取大于1的，可能有10個(gè)大于1的a，也可能有一百個(gè)a。
大于1的a那部分的B+樹(shù)里，b字段是無(wú)序的（開(kāi)局一張圖），所以b不能在無(wú)序的B+樹(shù)里用二分查找來(lái)查詢(xún)，b用不到索引。
like索引失效原理
- where name like "a%"
- where name like "%a%"
- where name like "%a"
我們先來(lái)了解一下%的用途
- %放在右邊，代表查詢(xún)以"a"開(kāi)頭的數(shù)據(jù)，如：abc
- 兩個(gè)%%，代表查詢(xún)數(shù)據(jù)中包含"a"的數(shù)據(jù)，如：cab、cba、abc
- %放在左邊，代表查詢(xún)以"a"為結(jié)尾的數(shù)據(jù)，如cba
為什么%放在右邊有時(shí)候能用到索引
- %放右邊叫做：前綴
- %%叫做：中綴
- %放在左邊叫做：后綴
沒(méi)錯(cuò)，這里依然是最佳左前綴法則這個(gè)概念
大家可以看到，上面的B+樹(shù)是由字符串組成的。
字符串的排序方式：先按照第一個(gè)字母排序，如果第一個(gè)字母相同，就按照第二個(gè)字母排序。。。以此類(lèi)推
開(kāi)始分析
一、%號(hào)放右邊（前綴）
由于B+樹(shù)的索引順序，是按照首字母的大小進(jìn)行排序，前綴匹配又是匹配首字母。所以可以在B+樹(shù)上進(jìn)行有序的查找，查找首字母符合要求的數(shù)據(jù)。所以有些時(shí)候可以用到索引。
二、%號(hào)放左邊
是匹配字符串尾部的數(shù)據(jù)，我們上面說(shuō)了排序規(guī)則，尾部的字母是沒(méi)有順序的，所以不能按照索引順序查詢(xún)，就用不到索引。
三、兩個(gè)%%號(hào)
這個(gè)是查詢(xún)?nèi)我馕恢玫淖帜笣M(mǎn)足條件即可，只有首字母是進(jìn)行索引排序的，其他位置的字母都是相對(duì)無(wú)序的，所以查找任意位置的字母是用不上索引的。
總結(jié)
這里把一些經(jīng)典的索引失效案例給大家分析了，希望能引發(fā)大家的思考，能夠通過(guò)這些案例，明白其他情況索引失效的原理。
之后我們?cè)谥v講，如何通過(guò)索引查詢(xún)到數(shù)據(jù)整個(gè)流程，InnoDB和MyISAM兩個(gè)引擎底層索引的實(shí)現(xiàn)區(qū)別。
授人以魚(yú)不如授人以漁，這一瞬間，老哥感覺(jué)自己特別的shuai。
