索引
索引常見(jiàn)的類型有哈希索引，有序數(shù)組索引化焕，二叉樹(shù)索引铃剔，跳表等等番宁。本文主要探討 MySQL 的默認(rèn)存儲(chǔ)引擎 InnoDB 的索引結(jié)構(gòu)。
InnoDB的索引結(jié)構(gòu)
在InnoDB中是通過(guò)一種多路搜索樹(shù)——B+樹(shù)實(shí)現(xiàn)索引結(jié)構(gòu)的踱蠢。在B+樹(shù)中是只有葉子結(jié)點(diǎn)會(huì)存儲(chǔ)數(shù)據(jù)棋电，而且所有葉子結(jié)點(diǎn)會(huì)形成一個(gè)鏈表赶盔。而在InnoDB中維護(hù)的是一個(gè)雙向鏈表。
你可能會(huì)有一個(gè)疑問(wèn)撕攒，為什么使用 B+樹(shù) 而不使用二叉樹(shù)或者B樹(shù)抖坪？
首先闷叉，我們知道訪問(wèn)磁盤(pán)需要訪問(wèn)到指定塊中握侧，而訪問(wèn)指定塊是需要 盤(pán)片旋轉(zhuǎn) 和 磁臂移動(dòng) 的，這是一個(gè)比較耗時(shí)的過(guò)程埋合，如果增加樹(shù)高那么就意味著你需要進(jìn)行更多次的磁盤(pán)訪問(wèn)饥悴，所以會(huì)采用n叉樹(shù)。而使用B+樹(shù)是因?yàn)槿绻褂肂樹(shù)在進(jìn)行一個(gè)范圍查找的時(shí)候每次都會(huì)進(jìn)行重新檢索瓣铣，而在B+樹(shù)中可以充分利用葉子結(jié)點(diǎn)的鏈表贷揽。
在建表的時(shí)候你可能會(huì)添加多個(gè)索引禽绪，而 InnDB 會(huì)為每個(gè)索引建立一個(gè) B+樹(shù) 進(jìn)行存儲(chǔ)索引。
比如這個(gè)時(shí)候我們建立了一個(gè)簡(jiǎn)單的測(cè)試表
這個(gè)時(shí)候 InnDB 就會(huì)為我們建立兩個(gè) B+索引樹(shù)循捺，一個(gè)是 主鍵 的 聚簇索引从橘，另一個(gè)是 普通索引 的 輔助索引础钠。
可以看到在輔助索引上面的葉子節(jié)點(diǎn)的值只是存了主鍵的值旗吁，而在主鍵的聚簇索引上的葉子節(jié)點(diǎn)才是存上了整條記錄的值很钓。
回表
所以這里就會(huì)引申出一個(gè)概念叫回表，比如這個(gè)時(shí)候我們進(jìn)行一個(gè)查詢操作
我們知道因?yàn)闂l件 MySQL 是會(huì)走 a 的索引的回还，但是 a 索引上并沒(méi)有存儲(chǔ) name 的值叹洲，此時(shí)我們就需要拿到相應(yīng) a 上的主鍵值，然后通過(guò)這個(gè)主鍵值去走 聚簇索引 最終拿到其中的name值工禾，這個(gè)過(guò)程就叫回表运提。
我們來(lái)總結(jié)一下回表是什么？MySQL在輔助索引上找到對(duì)應(yīng)的主鍵值并通過(guò)主鍵值在聚簇索引上查找所要的數(shù)據(jù)就叫回表闻葵。
索引維護(hù)
我們知道索引是需要占用空間的民泵，索引雖能提升我們的查詢速度但是也是不能濫用。
比如我們?cè)谟脩舯砝镉蒙矸葑C號(hào)做主鍵槽畔，那么每個(gè)二級(jí)索引的葉子節(jié)點(diǎn)占用約20個(gè)字節(jié)栈妆，而如果用整型做主鍵，則只要4個(gè)字節(jié)，如果是長(zhǎng)整型（bigint）則是8個(gè)字節(jié)嬉橙。也就是說(shuō)如果我用整型后面維護(hù)了4個(gè)g的索引列表，那么用身份證將會(huì)是20個(gè)g寥假。
所以我們可以通過(guò)縮減索引的大小來(lái)減少索引所占空間市框。
當(dāng)然B+樹(shù)為了維護(hù)索引的有序性會(huì)在刪除，插入的時(shí)候進(jìn)行一些必要的維護(hù)(在InnoDB中刪除會(huì)將節(jié)點(diǎn)標(biāo)記為“可復(fù)用”以減少對(duì)結(jié)構(gòu)的變動(dòng))糕韧。
比如在增加一個(gè)節(jié)點(diǎn)的時(shí)候可能會(huì)遇到數(shù)據(jù)頁(yè)滿了的情況枫振，這個(gè)時(shí)候就需要做頁(yè)的分裂，這是一個(gè)比較耗時(shí)的工作萤彩，而且頁(yè)的分裂還會(huì)導(dǎo)致數(shù)據(jù)頁(yè)的利用率變低粪滤，比如原來(lái)存放三個(gè)數(shù)據(jù)的數(shù)據(jù)頁(yè)再次添加一個(gè)數(shù)據(jù)的時(shí)候需要做頁(yè)分裂，這個(gè)時(shí)候就會(huì)將現(xiàn)有的四個(gè)數(shù)據(jù)分配到兩個(gè)數(shù)據(jù)頁(yè)中雀扶，這樣就減少了數(shù)據(jù)頁(yè)利用率杖小。
覆蓋索引
上面提到了 回表，而有時(shí)候我們查輔助索引的時(shí)候就已經(jīng)滿足了我們需要查的數(shù)據(jù)怕吴，這個(gè)時(shí)候 InnoDB 就會(huì)進(jìn)行一個(gè)叫 覆蓋索引 的操作來(lái)提升效率窍侧，減少回表。
比如這個(gè)時(shí)候我們進(jìn)行一個(gè) select 操作
這個(gè)時(shí)候很明顯我們走了 a 的索引直接能獲取到 id 的值转绷，這個(gè)時(shí)候就不需要進(jìn)行回表伟件，我們這個(gè)時(shí)候就使用了 覆蓋索引。
簡(jiǎn)單來(lái)說(shuō) 覆蓋索引 就是當(dāng)我們走輔助索引的時(shí)候能獲取到我們所需要的數(shù)據(jù)的時(shí)候不需要再次進(jìn)行回表操作的操作议经。
聯(lián)合索引
這個(gè)時(shí)候我們新建一個(gè)學(xué)生表
我們使用 class(班級(jí)號(hào)) 和 name 做一個(gè) 聯(lián)合索引斧账，你可能會(huì)問(wèn)這個(gè)聯(lián)合索引有什么用呢？我們可以結(jié)合著上面的 覆蓋索引 去理解煞肾，比如這個(gè)時(shí)候我們有一個(gè)需求咧织，我們需要通過(guò)班級(jí)號(hào)去找對(duì)應(yīng)的學(xué)生姓名 。
這個(gè)時(shí)候我們就可以直接在 輔助索引 上查找到學(xué)生姓名而不需要再次回表籍救。
總的來(lái)說(shuō)习绢，設(shè)計(jì)好索引，充分利用覆蓋索引能很大提升檢索速度蝙昙。
最左前綴原則
這個(gè)是以 聯(lián)合索引 作為基礎(chǔ)的闪萄，是一種聯(lián)合索引的匹配規(guī)則。
這個(gè)時(shí)候奇颠，我們將上面的需求稍微變動(dòng)一下败去，這時(shí)我們有個(gè)學(xué)生遲到，但是他在門(mén)衛(wèi)記錄信息的時(shí)候只寫(xiě)了自己的名字張三而沒(méi)有寫(xiě)班級(jí)烈拒，所以我們需要通過(guò)學(xué)生姓名去查找相應(yīng)的班級(jí)號(hào)圆裕。
這個(gè)時(shí)候我們就不會(huì)走我們的聯(lián)合索引了广鳍，而是進(jìn)行了全表掃描。
為什么吓妆？因?yàn)?最左匹配原則赊时。我們可以畫(huà)一張簡(jiǎn)單的圖來(lái)理解一下。
我們可以看到整個(gè)索引設(shè)計(jì)就是這么設(shè)計(jì)的耿战，所以我們需要查找的時(shí)候也需要遵循著這個(gè)規(guī)則蛋叼，如果我們直接使用name，那么InnoDB是不知道我們需要干什么的剂陡。
當(dāng)然最左匹配原則還有這些規(guī)則：
全值匹配的時(shí)候優(yōu)化器會(huì)改變順序狈涮，也就是說(shuō)你全值匹配時(shí)的順序和原先的聯(lián)合索引順序不一致沒(méi)有關(guān)系，優(yōu)化器會(huì)幫你調(diào)好鸭栖。
索引匹配從最左邊的地方開(kāi)始歌馍，如果沒(méi)有則會(huì)進(jìn)行全表掃描，比如你設(shè)計(jì)了一個(gè)(a,b,c)的聯(lián)合索引晕鹊，然后你可以使用(a),(a,b),(a,b,c) 而你使用 (b),(b,c),(c)就用不到索引了松却。
遇到范圍匹配會(huì)取消索引。比如這個(gè)時(shí)候你進(jìn)行一個(gè)這樣的 select 操作
這個(gè)時(shí)候 InnoDB 就會(huì)放棄索引而進(jìn)行全表掃描溅话，因?yàn)檫@個(gè)時(shí)候 InnoDB 會(huì)不知道怎么進(jìn)行遍歷索引晓锻，所以進(jìn)行全表掃描。
索引下推
我給你挖了個(gè)坑飞几。剛剛的操作在 MySQL5.6 版本以前是需要進(jìn)行回表的砚哆，但是5.6之后的版本做了一個(gè)叫 索引下推 的優(yōu)化。
如何優(yōu)化的呢屑墨？因?yàn)閯倓偟淖钭笃ヅ湓瓌t我們放棄了索引躁锁，后面我們緊接著會(huì)通過(guò)回表進(jìn)行判斷 name，這個(gè)時(shí)候我們所要做的操作應(yīng)該是這樣的
但是有了索引下推之后就變成這樣了卵史，此時(shí) "李四" 和 "小明" 這兩個(gè)不會(huì)再進(jìn)行回表战转。
因?yàn)檫@里匹配了后面的name = 張三，也就是說(shuō)以躯，如果最左匹配原則因?yàn)榉秶樵兘K止了槐秧，InnoDB還是會(huì)索引下推來(lái)優(yōu)化性能。
一些最佳實(shí)踐
哪些情況需要?jiǎng)?chuàng)建索引忧设？
頻繁作為查詢條件的字段應(yīng)創(chuàng)建索引色鸳。
多表關(guān)聯(lián)查詢的時(shí)候，關(guān)聯(lián)字段應(yīng)該創(chuàng)建索引见转。
查詢中的排序字段，應(yīng)該創(chuàng)建索引蒜哀。
統(tǒng)計(jì)或者分組字段需要?jiǎng)?chuàng)建索引斩箫。
哪些情況不需要?jiǎng)?chuàng)建索引吏砂？
盡量選擇區(qū)分度高的列作為索引。
不要對(duì)索引進(jìn)行一些函數(shù)操作乘客，還應(yīng)注意隱式的類型轉(zhuǎn)換和字符編碼轉(zhuǎn)換狐血。
盡可能的擴(kuò)展索引，不要新建立索引易核。比如表中已經(jīng)有了a的索引匈织，現(xiàn)在要加（a,b）的索引，那么只需要修改原來(lái)的索引即可牡直。
多考慮覆蓋索引缀匕，索引下推，最左匹配碰逸。
作者：FrancisQ
https://juejin.im/post/5db19103e51d452a300b14c9
