前言
在使用數(shù)據(jù)庫的時候，最需要了解的知識點(diǎn)肯定就是索引，所以，針對索引希望能夠整理出一些知識點(diǎn)，做個簡單總結(jié)。
索引結(jié)構(gòu)
B+樹
B+樹在之前的文章中已經(jīng)做了簡單介紹多種樹結(jié)構(gòu)分析
在InnoDB 索引結(jié)構(gòu)最終選擇了 B+ 樹，主要是出于以下幾點(diǎn)考慮：
- B+ 樹的磁盤讀寫代價更低
B+樹的非葉子結(jié)點(diǎn)不存儲數(shù)據(jù)，只存儲索引，所以，非葉子結(jié)點(diǎn)的存儲結(jié)構(gòu)更小。由于數(shù)據(jù)都存儲在葉子結(jié)點(diǎn)，也減少了查詢磁盤的次數(shù)。 - B+ 樹查詢效率更加穩(wěn)定
所有數(shù)據(jù)都存儲在葉子結(jié)點(diǎn)上，無論怎么查詢所有關(guān)鍵字查詢的路徑長度是固定的。 - B+ 樹對范圍查詢的支持更好
B+ 樹所有數(shù)據(jù)都在葉子節(jié)點(diǎn)，非葉子節(jié)點(diǎn)都是索引，那么做范圍查詢的時候只需要掃描一遍葉子節(jié)點(diǎn)即可；而 B 樹因為非葉子節(jié)點(diǎn)也保存數(shù)據(jù)，范圍查詢的時候要找到具體數(shù)據(jù)還需要進(jìn)行一次中序遍歷
MyISAM 和 InnoDB 索引組織的區(qū)別
- MyISAM 也是使用 B+ 樹作為索引存儲結(jié)構(gòu)，他的葉子節(jié)點(diǎn) data 域存放的是數(shù)據(jù)的物理地址，即索引結(jié)構(gòu)和真正的數(shù)據(jù)結(jié)構(gòu)其實是分開存儲的。
- InnoDB 中采用主鍵索引的方式，所有的數(shù)據(jù)都保存在主鍵索索引中。這里就又可以聯(lián)系到主鍵索引其實就是聚簇索引。
索引的分類
- 主鍵索引
上面講了，主鍵索引為聚簇索引，是基于主鍵來創(chuàng)建的 B+ 樹索引結(jié)構(gòu)，如果沒有指定主鍵，也找不到任何一列不重復(fù)的列可以作為主鍵的情況下，InnoDB 會新增一個隱藏列 RowId 作為主鍵繼而創(chuàng)建聚簇索引。 - 二級索引
二級索引就是指除了主鍵索引外的索引。主鍵索引和所有的二級索引都是各自維護(hù)各自的 B+ 樹結(jié)構(gòu)，但是二級所以是非聚簇索引，葉子結(jié)點(diǎn)存儲的不是數(shù)據(jù)，而是主鍵索引對應(yīng)的主鍵值。所以，innodb中，使用二級索引需要查詢兩次B+樹。
這里就牽出了索引失效的問題：
- 聯(lián)合索引失效
聯(lián)合索引對應(yīng)的 B+ Tree 索引結(jié)構(gòu)每個節(jié)點(diǎn)其實是按照三個字段的前后順序排列的，即 a 字段檢索在最前面，然后是b，然后是c。如果你的查詢不是按照這個順序來檢索，是不會被這個索引識別的。我的理解在樹結(jié)構(gòu)上只保留a，葉子結(jié)點(diǎn)上以聯(lián)合索引存儲著具體的三個字段作為數(shù)據(jù)加上主鍵索引對應(yīng)的主鍵值。這樣其實就很好理解最左匹配原則了。當(dāng)只有a為第一個條件，中間沒有其他條件時，索引才生效。 - 在索引列上做操作（函數(shù)，自定義計算）
索引是對已有的數(shù)據(jù)進(jìn)行歸納排序，你計算之后的數(shù)據(jù)是新的內(nèi)容，索引并沒有包含這些數(shù)據(jù)，無從查起。如果是運(yùn)算，可以將運(yùn)算方式變形，以適應(yīng)索引規(guī)則。 - 查詢條件包含 or，可能導(dǎo)致索引失效
or條件左右必須都加了索引在大多數(shù)情況下才可能會走，有些特殊場景不會走。這也是優(yōu)化器優(yōu)化后，發(fā)現(xiàn)沒有走索引部分會引起全表掃描等，所以就直接不走索引了。 - like 通配符可能導(dǎo)致索引失效
只有以 “%” 開頭的 like 查詢才會失效。主要是因為左連接查詢或者右連接查詢查詢關(guān)聯(lián)的字段編碼格式不一樣，具體的試驗可以后續(xù)嘗試一下。
這里還能得出為啥創(chuàng)建過多的索引是崩潰性的：
- 磁盤空間占用。從上面可知，索引其實就是創(chuàng)建了一個B+樹，如果是一張大表，每個索引會維護(hù)一張B+樹，越多會越占用空間。
- 更新類操作。從之前的關(guān)于B+樹的文章中，可以看到，一定程度上，更新類操作，是需要時間成本的，過多，過于復(fù)雜的索引，容易在更新時引起更多的消耗。
sql優(yōu)化
explain.png
具體案例待補(bǔ)充
