索引是數(shù)據(jù)庫(kù)中必考的知識(shí)點(diǎn)丹拯，平時(shí)工作中的優(yōu)化也是從索引入手站超，所以有必要對(duì)索引清楚的認(rèn)識(shí)。下面的文章是基于上Java3y 的文章修改而來(lái)的乖酬，里面的知識(shí)點(diǎn)確實(shí)太多死相，某些地方需要深入研究，目前對(duì)索引的知識(shí)點(diǎn)進(jìn)行全面熟悉咬像，后續(xù)根據(jù)實(shí)際業(yè)務(wù)來(lái)寫(xiě)對(duì)應(yīng)的知識(shí)點(diǎn)算撮。
- 聲明：如果沒(méi)有說(shuō)明具體的數(shù)據(jù)庫(kù)和存儲(chǔ)引擎，默認(rèn)指的是MySQL中的InnoDB存儲(chǔ)引擎
1县昂、索引的相關(guān)問(wèn)題
在之前肮柜，我對(duì)索引有以下的認(rèn)知：
- 索引可以加快數(shù)據(jù)庫(kù)的檢索速度；
- 表經(jīng)常進(jìn)行INSERT/UPDATE/DELETE操作就不要建立索引了倒彰，換言之：索引會(huì)降低插入审洞、刪除、修改等維護(hù)任務(wù)的速度待讳；
- 索引需要占物理和數(shù)據(jù)空間预明；
- 了解過(guò)索引的最左匹配原則；
- 知道索引的分類(lèi)：聚集索引和非聚集索引耙箍；
- Mysql支持Hash索引和B+樹(shù)索引兩種撰糠。
看起來(lái)好像啥都知道，但面試讓你說(shuō)的時(shí)候可能就GG了：
- 使用索引為什么可以加快數(shù)據(jù)庫(kù)的檢索速度氨缋ァ阅酪？
- 為什么說(shuō)索引會(huì)降低插入、刪除、修改等維護(hù)任務(wù)的速度术辐？
- 索引的最左匹配原則指的是什么砚尽？
- Hash索引和B+樹(shù)索引有什么區(qū)別？主流的使用哪一個(gè)比較多辉词？InnoDB存儲(chǔ)都支持嗎必孤？
- 聚集索引和非聚集索引有什么區(qū)別？
- ……..
2瑞躺、MySQL的基本存儲(chǔ)結(jié)構(gòu)
首先Mysql的基本存儲(chǔ)結(jié)構(gòu)是頁(yè)(記錄都存在頁(yè)里邊)：
- 各個(gè)數(shù)據(jù)頁(yè)可以組成一個(gè)雙向鏈表敷搪；
- 而每個(gè)數(shù)據(jù)頁(yè)中的記錄又可以組成一個(gè)單向鏈表；
- 每個(gè)數(shù)據(jù)頁(yè)都會(huì)為存儲(chǔ)在它里邊兒的記錄生成一個(gè)頁(yè)目錄幢哨，在通過(guò)主鍵查找某條記錄的時(shí)候可以在頁(yè)目錄中使用二分法快速定位到對(duì)應(yīng)的槽赡勘，然后再遍歷該槽對(duì)應(yīng)分組中的記錄即可快速找到指定的記錄
- 以其他列(非主鍵)作為搜索條件：只能從最小記錄開(kāi)始依次遍歷單鏈表中的每條記錄。
- 所以說(shuō)捞镰，如果我們
select * from user where username = 'Java3y'這樣沒(méi)有進(jìn)行任何優(yōu)化的sql語(yǔ)句闸与，默認(rèn)會(huì)這樣做：
- 定位到記錄所在的頁(yè)
- 需要遍歷雙向鏈表，找到所在的頁(yè)
- 從所在的頁(yè)內(nèi)中查找相應(yīng)的記錄
- 由于不是根據(jù)主鍵查詢(xún)岸售，只能遍歷所在頁(yè)的單鏈表了
很明顯践樱，在數(shù)據(jù)量很大的情況下這樣查找會(huì)很慢！
- 由于不是根據(jù)主鍵查詢(xún)岸售，只能遍歷所在頁(yè)的單鏈表了
3凸丸、索引提高檢索速度
索引做了些什么可以讓我們查詢(xún)加快速度呢映胁？
其實(shí)就是將無(wú)序的數(shù)據(jù)變成有序(相對(duì))：
要找到id為8的記錄簡(jiǎn)要步驟：
很明顯的是：沒(méi)有用索引我們是需要遍歷雙向鏈表來(lái)定位對(duì)應(yīng)的頁(yè)，現(xiàn)在通過(guò)“目錄”就可以很快地定位到對(duì)應(yīng)的頁(yè)上了甲雅！
其實(shí)底層結(jié)構(gòu)就是B+樹(shù)解孙，B+樹(shù)作為樹(shù)的一種實(shí)現(xiàn)，能夠讓我們很快地查找出對(duì)應(yīng)的記錄抛人。
4弛姜、索引降低增刪改的速度
B+樹(shù)是平衡樹(shù)的一種。
什么是平衡樹(shù)：它是一棵空樹(shù)或它的左右兩個(gè)子樹(shù)的高度差的絕對(duì)值不超過(guò)1妖枚，并且左右兩個(gè)子樹(shù)都是一棵平衡二叉樹(shù)廷臼。
如果一棵普通的樹(shù)在極端的情況下，是能退化成鏈表的(樹(shù)的優(yōu)點(diǎn)就不復(fù)存在了)
B+樹(shù)是平衡樹(shù)的一種绝页，是不會(huì)退化成鏈表的荠商，樹(shù)的高度都是相對(duì)比較低的(基本符合矮矮胖胖(均衡)的結(jié)構(gòu))【這樣一來(lái)我們檢索的時(shí)間復(fù)雜度就是O(logn)】！從上一節(jié)的圖我們也可以看見(jiàn)续誉，建立索引實(shí)際上就是建立一顆B+樹(shù)莱没。
- B+樹(shù)是一顆平衡樹(shù)，如果我們對(duì)這顆樹(shù)增刪改的話(huà)酷鸦，那肯定會(huì)破壞它的原有結(jié)構(gòu)饰躲。
- 要維持平衡樹(shù)牙咏，就必須做額外的工作。正因?yàn)檫@些額外的工作開(kāi)銷(xiāo)嘹裂，導(dǎo)致索引會(huì)降低增刪改的速度
B+樹(shù)刪除和修改具體可參考：
5妄壶、哈希索引
除了B+樹(shù)之外，還有一種常見(jiàn)的是哈希索引寄狼。
哈希索引就是采用一定的哈希算法丁寄，把鍵值換算成新的哈希值，檢索時(shí)不需要類(lèi)似B+樹(shù)那樣從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)逐級(jí)查找泊愧，只需一次哈希算法即可立刻定位到相應(yīng)的位置伊磺，速度非常快拼卵。
- 本質(zhì)上就是把鍵值換算成新的哈希值，根據(jù)這個(gè)哈希值來(lái)定位蛮艰。
看起來(lái)哈希索引很牛逼啊腋腮，但其實(shí)哈希索引有好幾個(gè)局限(根據(jù)他本質(zhì)的原理可得)：
- 哈希索引也沒(méi)辦法利用索引完成排序
- 不支持最左匹配原則
- 在有大量重復(fù)鍵值情況下，哈希索引的效率也是極低的---->哈希碰撞問(wèn)題壤蚜。
- 不支持范圍查詢(xún)
參考資料：
6即寡、InnoDB支持哈希索引嗎？
主流的還是使用B+樹(shù)索引比較多袜刷，對(duì)于哈希索引聪富，InnoDB是自適應(yīng)哈希索引的（hash索引的創(chuàng)建由InnoDB存儲(chǔ)引擎引擎自動(dòng)優(yōu)化創(chuàng)建，我們干預(yù)不了）著蟹！
參考資料：
7墩蔓、索引的分類(lèi)
簡(jiǎn)單概括：
- 聚集索引就是以主鍵創(chuàng)建的索引
- 非聚集索引就是以非主鍵創(chuàng)建的索引
區(qū)別：
- 聚集索引在葉子節(jié)點(diǎn)存儲(chǔ)的是表中的數(shù)據(jù)
- 非聚集索引在葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵和索引列
- 使用非聚集索引查詢(xún)出數(shù)據(jù)時(shí)，拿到葉子上的主鍵再去查到想要查找的數(shù)據(jù)萧豆。(拿到主鍵再查找這個(gè)過(guò)程叫做回表)
非聚集索引也叫做二級(jí)索引奸披，不用糾結(jié)那么多名詞，將其等價(jià)就行了~
非聚集索引在建立的時(shí)候也未必是單列的涮雷，可以多個(gè)列來(lái)創(chuàng)建索引阵面。
- 此時(shí)就涉及到了哪個(gè)列會(huì)走索引，哪個(gè)列不走索引的問(wèn)題了(最左匹配原則-->后面有說(shuō))
- 創(chuàng)建多個(gè)單列(非聚集)索引的時(shí)候洪鸭，會(huì)生成多個(gè)索引樹(shù)(所以過(guò)多創(chuàng)建索引會(huì)占用磁盤(pán)空間)
- 我們前面知道了样刷，如果不是聚集索引，葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵+列值
- 最終還是要“回表”览爵，也就是要通過(guò)主鍵再查找一次置鼻。這樣就會(huì)比較慢
- 覆蓋索引就是把要查詢(xún)出的列和索引是對(duì)應(yīng)的，不做回表操作蜓竹！
比如說(shuō)：
- 現(xiàn)在我創(chuàng)建了索引
(username,age)沃疮，在查詢(xún)數(shù)據(jù)的時(shí)候：select username , age from user where username = 'Java3y' and age = 20盒让。 - 很明顯地知道，我們上邊的查詢(xún)是走索引的司蔬，并且邑茄，要查詢(xún)出的列在葉子節(jié)點(diǎn)都存在！（要查詢(xún)的列
username俊啼、age都在葉子節(jié)點(diǎn)上）所以肺缕，就不用回表了~ - 所以，能使用覆蓋索引就盡量使用吧~
8授帕、索引最左匹配原則
最左匹配原則：
- 索引可以簡(jiǎn)單如一個(gè)列(a)同木，也可以復(fù)雜如多個(gè)列(a, b, c, d)，即聯(lián)合索引跛十。
- 如果是聯(lián)合索引彤路，那么key也由多個(gè)列組成，同時(shí)芥映，索引只能用于查找key是否存在（相等）洲尊，遇到范圍查詢(xún)(>、<奈偏、between坞嘀、like左匹配)等就不能進(jìn)一步匹配了，后續(xù)退化為線(xiàn)性查找惊来。
- 因此丽涩，列的排列順序決定了可命中索引的列數(shù)。
例子：
- 如有索引(a, b, c, d)裁蚁，查詢(xún)條件a = 1 and b = 2 and c > 3 and d = 4矢渊，則會(huì)在每個(gè)節(jié)點(diǎn)依次命中a、b枉证、c昆淡，無(wú)法命中d。(很簡(jiǎn)單：索引命中只能是相等的情況刽严，不能是范圍匹配)=昂灵、in自動(dòng)優(yōu)化順序。
不需要考慮=舞萄、in等的順序眨补，mysql會(huì)自動(dòng)優(yōu)化這些條件的順序，以匹配盡可能多的索引列倒脓。
例子：
- 如有索引(a, b, c, d)撑螺，查詢(xún)條件c > 3 and b = 2 and a = 1 and d < 4與a = 1 and c > 3 and b = 2 and d < 4等順序都是可以的，MySQL會(huì)自動(dòng)優(yōu)化a = 1 and b = 2 and c > 3 and d < 4崎弃，依次命中a甘晤、b含潘、c。
9线婚、索引的總結(jié)
索引在數(shù)據(jù)庫(kù)中是一個(gè)非常重要的知識(shí)點(diǎn)遏弱！上面談的其實(shí)就是索引最基本的東西，要?jiǎng)?chuàng)建出好的索引要顧及到很多的方面：
- 1塞弊、最左前綴匹配原則漱逸。這是非常重要、非常重要游沿、非常重要（重要的事情說(shuō)三遍）的原則饰抒，MySQL會(huì)一直向右匹配直到遇到范圍查詢(xún)（>,<,BETWEEN,LIKE）就停止匹配。
- 2诀黍、盡量選擇區(qū)分度高的列作為索引袋坑，區(qū)分度的公式
COUNT(DISTINCT col) / COUNT(*)。表示字段不重復(fù)的比率眯勾，比率越大我們掃描的記錄數(shù)就越少枣宫。 - 3、索引列不能參與計(jì)算咒精，盡量保持列“干凈”镶柱。比如旷档，
FROM_UNIXTIME(create_time) = '2016-06-06'就不能使用索引模叙，原因很簡(jiǎn)單，B+樹(shù)中存儲(chǔ)的都是數(shù)據(jù)表中的字段值鞋屈，但是進(jìn)行檢索時(shí)范咨，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然這樣的代價(jià)太大厂庇。所以語(yǔ)句要寫(xiě)成 ：create_time = UNIX_TIMESTAMP('2016-06-06')渠啊。 - 4、盡可能的擴(kuò)展索引权旷，不要新建立索引贯城。比如表中已經(jīng)有了a的索引钞澳，現(xiàn)在要加（a,b）的索引，那么只需要修改原來(lái)的索引即可。
- 5术吝、單個(gè)多列組合索引和多個(gè)單列索引的檢索查詢(xún)效果不同，因?yàn)樵趫?zhí)行SQL時(shí)挑豌，MySQL只能使用一個(gè)索引奈搜，會(huì)從多個(gè)單列索引中選擇一個(gè)限制最為嚴(yán)格的索引。
10鄙麦、參考資料
https://zhuanlan.zhihu.com/p/23624390--簡(jiǎn)單理解索引
https://blog.csdn.net/mysteryhaohao/article/details/51719871--
https://monkeysayhi.github.io/2018/03/06/%E6%B5%85%E8%B0%88MySQL%E7%9A%84B%E6%A0%91%E7%B4%A2%E5%BC%95%E4%B8%8E%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96/---淺談MySQL的B樹(shù)索引與索引優(yōu)化
https://mp.weixin.qq.com/s?__biz=MzI4Njg5MDA5NA==&mid=2247484721&idx=1&sn=410dea1863ba823bec802769e1e6fe8a&chksm=ebd74430dca0cd265a9a91dcb2059e368f43a25f3de578c9dbb105e1fba0947e1fd0b9c2f4ef&token=1676899695&lang=zh_CN#rd
