1.2、索引 B+Tree 結構的特性：

①、B+Tree 只有葉子節點會存儲真實的數據，非葉子節點只會存儲索引字段值；

②、B+Tree的葉子節點之間使用 雙向鏈表 鏈接，所以更加適合范圍查詢和排序；

2、索引的類型：

在平時創建的索引中，可以將索引大體分為兩類：

①、聚簇索引(主鍵索引) ②、非聚簇索引(二級索引)

二級索引根據索引中的字段個數可以分為：

①、單字段索引 ②、聯合索引 / 復合索引(多個字段組成的索引)

3、不同類型索引在磁盤中的B+Tree的存儲結構：

3.1、聚簇索引：(主鍵索引)

聚簇索引：當表中創建了主鍵，默認就會生成主鍵索引；

聚簇索引的B+Tree索引結構中，非葉子節點中存儲的是 ID主鍵值 ，存儲在葉子節點中的真實數據是具體的 行記錄 ； 結構圖如下：

3.2、單字段索引：(二級索引)

單字段索引：手動創建的索引，由 一個字段 組成的索引；

單字段索引的B+Tree索引結構中，非葉子節點中存儲的是 索引字段值 ，存儲在葉子節點中的數據部分是 主鍵值 。結構圖如下：

3.3、聯合索引：(二級索引)

聯合索引：手動創建的索引，由 多個字段 組成的索引；

聯合索引的B+Tree索引結構中，非葉子節點中存儲的是 多個索引字段值 ，存儲在葉子節點中的數據部分是 主鍵值 。結構圖如下：

4、回表：

什么是回表？

回表是發生在 二級索引 中的；在使用二級索引查詢數據時，如果 select 投影列 中擁有 索引字段和主鍵 之外的字段時，此時就需要 回表；使用在二級索引樹中查詢到的 主鍵值 去主鍵索引樹中查詢具體的行記錄，然后在具體行記錄中取得最終的 select 投影列 數據。

4.1、舉例說明：

數據表：t_user

字 段：id(主鍵)、name、age、address、sex

索 引：index( name, age ) 測試的這個索引是 聯合索引，單字段索引也是一樣的原理

Sql語句：select name, age, address, sex from t_user where name=’黎明’ and age=18

上面的sql語句執行時，就會發生回表；具體執行步驟如下：

①、執行時，首先會使用到 聯合索引 index( name, age )，在此索引樹中查詢時，最終在葉子節點中查詢到數據，發現此時最終只能得到 name、age、id 三個字段的數據，發現 address、age 這兩個字段的數據沒有得到，所以只能再根據查詢到主鍵值去主鍵索引樹中查詢；

②、拿著 id主鍵值 去主鍵索引樹中查詢具體的行記錄，然后在行記錄中取出select投影列需要的字段的數據，最后返回。

4.2、擴展：

根據上面舉的例子可以知道什么時候會發生回表，但是發生回表就代表著 查詢效率 會比較低下的，因為需要走兩邊索引樹(二級索引樹 + 主鍵索引樹)；所以一般情況下需要避免回表的發生；怎么避免發生呢？這又涉及到了 覆蓋索引 這個知識點。下面就來介紹下 覆蓋索引的知識 。

5、覆蓋索引：

覆蓋索引：大白話就是 將select 的投影列字段全部放入到 索引中；

5.1、舉例說明：

數據表：t_user

字 段：id(主鍵)、name、age、address、sex

索 引：index( name, age )

Sql語句：select name, age, address, sex from t_user where name=’黎明’ and age=18

這個sql執行時，會發生回表；

發生回表的原因通過上面的解釋應該也清楚了些，所以如果 將索引 index( name, age ) 改為 * index( name, age, address, sex ) * 后，在執行上面的sql 就 不會發生回表了，并且提高了查詢效率， 這就是 覆蓋索引。

但是，索引的創建及維護也是需要耗費代價的，并且這種代價也是隨著索引中索引字段的個數增加而增加的，所以覆蓋索引需要根據實際情況使用。

6、最左前綴原則：

說到索引的最左前綴原則，就必須說下 索引長度 這個概念了；

索引長度指的是 索引字段列的 前綴長度索引；

例如：name字段的長度設置為100，但是在以name設置索引時設置的索引長度為20；意思就是創建的索引中存儲的索引key鍵值就是name字段值的前20長度的內容。

6.1、索引的最左前綴原則的兩種情況：

①、索引字段的最左前綴原則；

②、聯合索引的最左前綴原則；

6.2、索引字段的最左前綴原則：

根據 索引長度 應該就可以明白了 索引字段的最左前綴原則了；但還是舉例說明下：

經常說的導致索引失效的情況之一： 模糊查詢時 將 % 放在了索引字段的前面；導致失效的最終原因就是 不滿足索引的最左前綴原則；

數據表：t_user

字 段：id(主鍵)、name、age、address、sex

索 引：index( name )

Sql語句：select name, age from t_user where name like ’ % 黎明 ’

上面這個 sql 語句執行時，并不會走 index(name) 索引查詢，因為什么呢？不滿足索引的最左前綴原則。

6.2、聯合索引的最左前綴原則：

根據聯合索引的B+Tree 存儲結構可以知道其最左前綴原則是什么。

不太清楚也別著急，下面將會舉例說明：

數據表：t_user

字 段：id(主鍵)、name、age、address、sex

索 引：index( name, age )

Sql語句：select id from t_user where name=’黎明’ and age=26

測試數據 如圖：

根據上面的測試數據及聯合索引可以得到 B+Tree存儲結構圖：

上面sql執行時索引樹的搜索步驟：

①、首先是加載索引樹的根節點(磁盤塊1)，然后匹配根節點中的第一個索引字段的值，查詢name值是“黎明”的數據，得到指向其子節點地址的指針p1；

②、然后根據指針p1 找到子節點所在的磁盤塊，然后將磁盤塊加載到內存中，然后接著查詢name值是“黎明”的數據，得到指向其子節點(葉子節點)地址的指針p3；

③、然后根據p3指針找到name值是“黎明”的 所在葉子節點的磁盤塊，然后將磁盤塊中數據加載到內存中，然后再比較 age 為26的數據，最終找到name值是“黎明”并且age為26 的 id主鍵值，由于select 投影列 只有 id 主鍵值，所以不需要回表，直接返回結果集了。

注意：在聯合索引中，當索引中前一個索引字段值相同時，后面緊挨著的索引字段的值是有序的，所以索引天然適合進行排序，無需自己再進行排序了，提升了查詢效率。

根據 上面的索引樹搜索過程，知道一開始是以索引中的第一個索引字段進行搜索的，最后在葉子節點中依次進行的索引字段值的匹配，這就是聯合索引的最左前綴原則；

所以如果sql語句中的where 條件中沒有使用到聯合索引的第一個索引字段，則整個索引就失效了。

索引創建的準則：

1、考慮創建索引的表的讀寫情況：

對于 寫操作比較多 的表，創建索引時，應該盡量保證 聯合索引 盡可能的 窄(窄： 索引字段個數盡可能少)， 如果索引字段比較多的話，寫數據時的索引維護比較麻煩。

所以說，能創建單字段索引就不創建聯合索引。

2、避免冗余索引：

合理創建索引，避免冗余索引；

聯合索引index(a, b, c)相當于 index(a)、index(a，b)、index(a，b，c)這三個索引，所以如果有 index(a, b, c)索引了，那么 index(a)、index(a，b)這兩個索引就不用存在了，屬于重復索引了。

3、對多表關聯查詢使用的 連接鍵 字段創建索引：

針對 多表關聯 查詢時，需要將 連接鍵 字段創建索引 ； 例如：user 、role 兩張表；

(一)、 select * from user a, role b where a.name = b.name 【普通連接查詢】

需要在 role 表中創建 name 連接鍵的索引，user 不必創建 name 字段索引；

(二)、select * from user a left join role b on a.name = b.name 【左外連接查詢】

需要在 role 表中創建 name 連接鍵的索引，user 不必創建 name 字段索引；

4、明確字段的區分度：

提前估算出表中每個字段存儲的值的區分度，盡量在 區分度高的字段創建索引 ，區分度低的字段創建索引用處不大，并且還會多出索引維護的消耗。

區分度：字段值的不同的比例；例如性別字段只會有兩種值，男或女，屬于區分度低的字段。

5、保證索引的唯一性：

創建索引時，盡可能保證索引的唯一性，唯一性指的什么呢？

在查看SQL執行計劃時，執行計劃中 possible_keys 中展示的就是優化器采樣估算后可能會使用到的索引，只有這里展示一個索引，并且與實際使用的索引一致時，這才是唯一性；如果此處展示了多個索引名稱的話，說明沒有保證索引的唯一性。

為什么要保證唯一性呢？

因為如果在執行一個SQL時，優化器是通過采樣分析判斷發現有兩個索引都可以時，那么就會面臨抉擇，到底最終使用哪個索引呢；如果只有一個索引符合的話，優化器直接選擇即可。所以如果可以使用的索引存在多個的話，那么優化器還需要通過 額外的運算 得到最終使用的索引的，那么這就會降低查詢效率的。

6、創建聯合索引時，索引字段怎么排序：

在聯合索引中，怎么對索引字段進行排序？ 大白話就是 在聯合索引中，哪些索引字段 放在最前面？

①、where 條件中常使用的字段 放到前面。

②、區分度高的字段放在前面，因為這樣可以減少掃描 B+tree 中的葉塊。

《高性能MySQL》一書中提到的一個經驗法則：將選擇性最高( 區分度高 )的列放到索引最前列。

7、SQL中，有等值查詢和范圍查詢時，聯合索引怎么創建？

當然是將 等值查詢的字段 放在聯合索引的前面， 范圍查詢 的放到聯合索引的后面；

為什么這么設計呢？

因為依據索引的查詢規則，在聯合索引中，首先都是根據第一個索引字段查詢符合要求的數據，然后再從符合要求的數據中篩選第二個索引字段符合的數據；如果第一個索引字段值不符合，則直接結束查詢了，不會再去進行范圍查詢了；但是如果將范圍查詢字段放在聯合索引的第一位的話，那么很大可能會查詢到符合范圍的數據，然后再從數據中篩選等值查詢的字段值，如果等值查詢的值不存在，那么前面范圍查詢所做的工作就白白浪費了；并且降低了查詢效率。

8、單表索引數量建議：

建議：單張表中索引的數量不超過5個；單個索引中的索引字段個數不超過5個；

導致索引失效的SQL：

明明已經創建好了索引，但是SQL執行時通過執行計劃發現沒有走索引查詢，為什么？

1、查看SQL的執行計劃：

創建好索引后，一般會查看下SQL的執行計劃，看看SQL執行時是否走了索引查詢；

Mysql 數據庫查看執行計劃 參考： 使用explain分析SQL執行計劃

Oracle 數據庫查看執行計劃 參考： Oracle通過執行計劃查看查詢語句是否使用索引

2、導致SQL執行時沒走索引的原因：

2.1、優化器采樣估算后不走索引：

可能數據庫的優化器在進行采樣估算時，發現走全表掃描更加合適；例如：表中數據量很小時，全表掃描和索引查詢可能一樣快，所以此時可能會直接進行全部掃描查詢了。

2.2、SQL語句問題導致索引失效：(簡單寫幾個常用到的)

• 不滿足索引的最左前綴原則；例如：like模糊查詢時，%放在前面；聯合索引；
• 使用了反向查詢，例如：not in 、!= 、<> 等，導致索引失效；
• 在索引字段上進行了 函數運算 或者 算數運算；
• 隱式轉換導致索引失效：隱式類型轉換、隱式字符集轉換；其實底層都是使用函數進行的隱式轉換，所以導致的索引失效；
• 對索引字段做 判 null 操作，會導致索引失效；