歡迎訪問

什么是索引？

提高查詢效率的一種數據結構，索引是數據的目錄

索引的分類

• 按「數據結構」分類：B+tree索引、Hash索引、Full-text索引。
• 按「物理存儲」分類：聚簇索引、二級索引。
• 按「字段特性」分類：主鍵索引、唯一索引、普通索引、前綴索引。
• 按「字段個數」分類：單列索引、聯合索引（復合索引）。

按數據結構分類

為什么 MySQL InnoDB 選擇 B+tree 作為索引的數據結構？

1、B+Tree vs B Tree

• B+Tree 只在葉子節點存儲數據，而 B 樹 的非葉子節點也要存儲數據，所以 B+Tree 的單個節點的數據量更小，在相同的磁盤 I/O 次數下，就能查詢更多的節點。

• B+ 樹有大量的冗余節點（所有非葉子節點都是冗余索引），這些冗余索引讓 B+ 樹在插入、刪除的效率都更高，比如刪除根節點的時候，不會像 B 樹那樣會發生復雜的樹的變化；

• 另外，B+Tree 葉子節點采用的是雙鏈表連接，適合 MySQL 中常見的基于范圍的順序查找，而 B 樹無法做到這一點。

2、B+Tree vs 二叉樹

• B+樹的節點相對較大，能夠存儲更多的索引項，從而減少了樹的高度，降低了訪問磁盤的次數(矮胖)
• B+樹的節點相對較大，一個節點可以存儲多個關鍵字，這意味著在進行磁盤IO時，可以一次性讀取更多的索引項到內存中，利用了局部性原理，減少了IO的開銷（局部性）
• B+樹的節點相對較大，樹的高度低，節點分裂和合并等平衡操作相對較少，在插入和刪除操作時，B+樹通常能夠更加高效地維持樹的平衡性，而不需要頻繁地進行平衡調整操作
• B+樹的葉子節點形成了一個有序鏈表，便于范圍查詢

3、B+Tree vs Hash

Hash 在做等值查詢的時候效率賊快，搜索復雜度為 O(1)。

但是 Hash 表不適合做范圍查詢，它更適合做等值的查詢，這也是 B+Tree 索引要比 Hash 表索引有著更廣泛的適用場景的原因。

按物理存儲分類

分為聚簇索引和二級索引

在查詢時使用了二級索引，如果查詢的數據能在二級索引里查詢的到，那么就不需要回表，這個過程就是覆蓋索引。如果查詢的數據不在二級索引里，就會先檢索二級索引，找到對應的葉子節點，獲取到主鍵值后，然后再檢索主鍵索引，就能查詢到數據了，這個過程就是回表

按字段特性分類

分為主鍵索引、唯一索引、普通索引、前綴索引

主鍵索引

通常在創建表的時候一起創建，一張表最多只有一個主鍵索引，索引列的值不允許有空值

唯一索引

一張表可以有多個唯一索引，索引列的值必須唯一，但是允許有空值

普通索引

既不要求字段為主鍵，也不要求字段為 UNIQUE

前綴索引

前綴索引是指對字符類型字段的前幾個字符建立的索引，使用前綴索引的目的是為了減少索引占用的存儲空間，提升查詢效率

普通索引和唯一索引，應該怎么選擇？

查詢過程：

• 對于普通索引來說，查找到滿足條件的第一個記錄 (5,500) 后，需要查找下一個記錄，直到碰到第一個不滿足 k=5 條件的記錄。
• 對于唯一索引來說，由于索引定義了唯一性，查找到第一個滿足條件的記錄后，就會停止繼續檢索。

更新過程：

第一種情況是，這個記錄要更新的目標頁在內存中。這時，InnoDB 的處理流程如下：

• 對于唯一索引來說，找到 3 和 5 之間的位置，判斷到沒有沖突，插入這個值，語句執行結束；
• 對于普通索引來說，找到 3 和 5 之間的位置，插入這個值，語句執行結束。

這樣看來，普通索引和唯一索引對更新語句性能影響的差別，只是一個判斷，只會耗費微小的 CPU 時間。

但，這不是我們關注的重點。

第二種情況是，這個記錄要更新的目標頁不在內存中。這時，InnoDB 的處理流程如下：

• 對于唯一索引來說，需要將數據頁讀入內存，判斷到沒有沖突，插入這個值，語句執行結束；
• 對于普通索引來說，則是將更新記錄在 change buffer，語句執行就結束了。

將數據從磁盤讀入內存涉及隨機 IO 的訪問，是數據庫里面成本最高的操作之一。change buffer 因為減少了隨機磁盤訪問，所以對更新性能的提升是會很明顯的。（因此，對于寫多讀少的業務來說，頁面在寫完以后馬上被訪問到的概率比較小，此時 change buffer 的使用效果最好）

按字段個數分類

分為單列索引、聯合索引（復合索引）

使用聯合索引時，存在最左匹配原則，也就是按照最左優先的方式進行索引的匹配。在使用聯合索引進行查詢的時候，如果不遵循「最左匹配原則」，聯合索引會失效

聯合索引范圍查詢

聯合索引的最左匹配原則，在遇到范圍查詢（如 >、<）的時候，就會停止匹配，也就是范圍查詢的字段可以用到聯合索引，但是在范圍查詢字段的后面的字段無法用到聯合索引。注意，對于 >=、<=、BETWEEN、like 前綴匹配的范圍查詢，并不會停止匹配，因為等于的時候可以用到索引

select * from t_table where a >= 1 and b = 2  //a=1等于時，b有序

索引下推

對于聯合索引（a, b），在執行 select * from table where a > 1 and b = 2 語句的時候，只有 a 字段能用到索引

對于b=2的判斷：

• 在 MySQL 5.6 之前，回表之后判斷
• 而 MySQL 5.6 引入的索引下推優化（index condition pushdown)，在搜索引擎判斷，符合再回表

索引區分度

建立聯合索引時，要把區分度大的字段排在前面，這樣區分度大的字段越有可能被更多的 SQL 使用到。

區分度＝不同的列/總列數

性別的區分度就很小，不適合建立索引或不適合排在聯合索引列的靠前的位置，而 UUID 這類字段就比較適合做索引或排在聯合索引列的靠前的位置

聯合索引進行排序

這里出一個題目，針對針對下面這條 SQL，你怎么通過索引來提高查詢效率呢？

select * from order where status = 1 order by create_time asc

給 status 和 create_time 列建立一個聯合索引

什么時候需要 / 不需要創建索引？

索引最大的好處是提高查詢速度，但是索引也是有缺點**（空間、維護）**的，比如：

• 需要占用物理空間，數量越大，占用空間越大；
• 創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增大；
• 會降低表的增刪改的效率，因為每次增刪改索引，B+ 樹為了維護索引有序性，都需要進行動態維護。

什么時候適用索引？

唯一、常查、排序

• 字段有唯一性限制的，比如商品編碼；
• 經常用于 WHERE 查詢條件的字段，這樣能夠提高整個表的查詢速度，如果查詢條件不是一個字段，可以建立聯合索引。
• 經常用于 GROUP BY 和 ORDER BY 的字段，這樣在查詢的時候就不需要再去做一次排序了，因為我們都已經知道了建立索引之后在 B+Tree 中的記錄都是排序好的

什么時候不需要創建索引？

不查不排、重復數據、數據量小、常更新

• WHERE 條件，GROUP BY，ORDER BY 里用不到的字段，索引的價值是快速定位，如果起不到定位的字段通常是不需要創建索引的，因為索引是會占用物理空間的。
• 字段中存在大量重復數據；
• 表數據太少的時候，不需要創建索引；
• 經常更新的字段不用創建索引，比如不要對電商項目的用戶余額建立索引，因為索引字段頻繁修改，由于要維護 B+Tree的有序性，那么就需要頻繁的重建索引，這個過程是會影響數據庫性能的。

索引選擇

首先，看MySQL 執行過程：

MySQL 數據庫由 Server 層和 Engine 層組成：

• Server 層有 SQL 分析器、SQL優化器、SQL 執行器，用于負責 SQL 語句的具體執行過程；
• Engine 層負責存儲具體的數據，如最常使用的 InnoDB 存儲引擎，還有用于在內存中存儲臨時結果集的 TempTable 引擎。

SQL 優化器會分析所有可能的執行計劃，選擇成本最低的執行，這種優化器稱之為：CBO（Cost-based Optimizer，基于成本的優化器）。

而在 MySQL中，一條 SQL 的計算成本計算如下所示：

Cost  = Server Cost + Engine Cost= CPU Cost + IO Cost

其中，CPU Cost 表示計算的開銷，比如索引鍵值的比較、記錄值的比較、結果集的排序……這些操作都在 Server 層完成；

IO Cost 表示引擎層 IO 的開銷，MySQL 8.0 可以通過區分一張表的數據是否在內存中，分別計算讀取內存 IO 開銷以及讀取磁盤 IO 的開銷。

總結來說：

• MySQL 優化器是 CBO 的，MySQL 會選擇成本最低的執行計劃，你可以通過 EXPLAIN 命令查看每個 SQL 的成本；
• 一般只對高選擇度的字段和字段組合創建索引，低選擇度的字段如性別，不創建索引；
• 低選擇性，但是數據存在傾斜，通過索引找出少部分數據，可以考慮創建索引。若數據存在傾斜，可以創建直方圖，讓優化器知道索引中數據的分布，進一步校準執行計劃。

有什么優化索引的方法？

幾種常見優化索引的方法：

• 前綴索引優化；
• 覆蓋索引優化；
• 主鍵索引最好是自增的；
• 防止索引失效；

前綴索引優化

使用某個字段中字符串的前幾個字符建立索引，減小索引字段大小，可以增加一個索引頁中存儲的索引值，有效提高索引的查詢速度。

不過，前綴索引有一定的局限性，例如：

• order by 就無法使用前綴索引；
• 無法把前綴索引用作覆蓋索引；

覆蓋索引優化

查詢的數據能在二級索引里查詢的到，不需要通過主鍵索引查詢獲得，可以避免回表的操作

假設我們只需要查詢商品的名稱、價格，有什么方式可以避免回表呢？

我們可以建立一個聯合索引，即「商品ID、名稱、價格」作為一個聯合索引。如果索引中存在這些數據，查詢將不會再次檢索主鍵索引，從而避免回表。

所以，使用覆蓋索引的好處就是，不需要查詢出包含整行記錄的所有信息，也就減少了大量的 I/O 操作。

主鍵索引最好是自增的

我們在建表的時候，都會默認將主鍵索引設置為自增的，具體為什么要這樣做呢？又什么好處？

InnoDB 創建主鍵索引默認為聚簇索引，數據被存放在了 B+Tree 的葉子節點上。也就是說，同一個葉子節點內的各個數據是按主鍵順序存放的，因此，每當有一條新的數據插入時，數據庫會根據主鍵將其插入到對應的葉子節點中。

如果我們使用自增主鍵，那么每次插入的新數據就會按順序添加到當前索引節點的位置，不需要移動已有的數據，當頁面寫滿，就會自動開辟一個新頁面。因為每次插入一條新記錄，都是追加操作，不需要重新移動數據，因此這種插入數據的方法效率非常高。

如果我們使用非自增主鍵，由于每次插入主鍵的索引值都是隨機的，因此每次插入新的數據時，就可能會插入到現有數據頁中間的某個位置，這將不得不移動其它數據來滿足新數據的插入，甚至需要從一個頁面復制數據到另外一個頁面，我們通常將這種情況稱為頁分裂。頁分裂還有可能會造成大量的內存碎片，導致索引結構不緊湊，從而影響查詢效率。

而如果記錄是順序插入的，例如插入數據11，則只需開辟新的數據頁，也就不會發生頁分裂：

索引最好設置為 NOT NULL

為了更好的利用索引，索引列要設置為 NOT NULL 約束。有兩個原因：（優化器選擇復雜，NULL有NULL值表占用空間）

• 第一原因：索引列存在 NULL 就會導致優化器在做索引選擇的時候更加復雜，更加難以優化，因為可為 NULL 的列會使索引、索引統計和值比較都更復雜，比如進行索引統計時，count 會省略值為NULL 的行。
• 第二個原因：NULL 值是一個沒意義的值，但是它會占用物理空間，所以會帶來的存儲空間的問題，因為 InnoDB 存儲記錄的時候，如果表中存在允許為 NULL 的字段，那么行格式中至少會用 1 字節空間存儲 NULL 值列表，如下圖的紫色部分

防止索引失效

發生索引失效的情況：

like和聯合沒有左匹配，函數，or

• 當我們使用左或者左右模糊匹配的時候，也就是 like %xx 或者 like %xx%這兩種方式都會造成索引失效；
• 當我們在查詢條件中對索引列做了計算、函數、類型轉換操作，這些情況下都會造成索引失效；
• 聯合索引要能正確使用需要遵循最左匹配原則，也就是按照最左優先的方式進行索引的匹配，否則就會導致索引失效。
• 在 WHERE 子句中，如果在 OR 前的條件列是索引列，而在 OR 后的條件列不是索引列，那么索引會失效。

問題自測

1.為什么索引能提?查詢速度?

索引是提高查詢效率的一種數據結構，索引是數據的目錄，索引的底層為B+樹實現，B+Tree 只在葉子節點存儲數據，單個節點的數據量小，且B+樹矮胖，磁盤IO少

通過使用索引，數據庫系統可以直接跳到存儲數據的位置，而不必逐行查找

2.聚集索引和?聚集索引的區別？?聚集索引?定回表查詢嗎?

1. 聚集索引（Clustered Index）：
   • 聚集索引中，表的行數據按照索引的順序進行存儲，而且每張表只能有一個聚集索引
   • 因為數據行按照索引的順序存儲，所以聚集索引可以加速范圍查詢和排序操作，但是插入和更新操作可能會導致數據的重新排序，因此對于頻繁進行這些操作的表來說，可能會影響性能。
2. 非聚集索引（Non-Clustered Index）：
   • 非聚集索引中，索引的葉子節點并不包含實際的數據行，而是包含指向數據行的指針（或者稱為引用）。
   • 表可以有多個非聚集索引，這些索引可以加速檢索

在查詢時使用了二級索引，如果查詢的數據能在二級索引里查詢的到，那么就不需要回表，這個過程就是覆蓋索引。如果查詢的數據不在二級索引里，就會先檢索二級索引，找到對應的葉子節點，獲取到主鍵值后，然后再檢索主鍵索引，就能查詢到數據了，這個過程就是回表

3.索引這么多優點，為什么不對表中的每?個列創建?個索引呢？(使?索引?定能提?查 詢性能嗎?)

索引最大的好處是提高查詢速度，但是索引也是有缺點**（空間、維護）**的，比如：

• 需要占用物理空間，數量越大，占用空間越大；
• 創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增大；
• 會降低表的增刪改的效率，因為每次增刪改索引，B+ 樹為了維護索引有序性，都需要進行動態維護。

什么時候適用索引？

唯一、常查、排序

• 字段有唯一性限制的，比如商品編碼；
• 經常用于 WHERE 查詢條件的字段，這樣能夠提高整個表的查詢速度，如果查詢條件不是一個字段，可以建立聯合索引。
• 經常用于 GROUP BY 和 ORDER BY 的字段，這樣在查詢的時候就不需要再去做一次排序了，因為我們都已經知道了建立索引之后在 B+Tree 中的記錄都是排序好的

什么時候不需要創建索引？

不查不排、重復數據、數據量小、常更新

• WHERE 條件，GROUP BY，ORDER BY 里用不到的字段，索引的價值是快速定位，如果起不到定位的字段通常是不需要創建索引的，因為索引是會占用物理空間的。
• 字段中存在大量重復數據；
• 表數據太少的時候，不需要創建索引；
• 經常更新的字段不用創建索引，比如不要對電商項目的用戶余額建立索引，因為索引字段頻繁修改，由于要維護 B+Tree的有序性，那么就需要頻繁的重建索引，這個過程是會影響數據庫性能的。

4.索引底層的數據結構了解么？Hash 索引和 B+樹索引優劣分析

Hash 在做等值查詢的時候效率賊快，搜索復雜度為 O(1)。

但是 Hash 表不適合做范圍查詢，它更適合做等值的查詢，這也是 B+Tree 索引要比 Hash 表索引有著更廣泛的適用場景的原因。

5.B+樹做索引?紅?樹好在哪?？

紅黑樹通常用于實現內存中的數據結構，例如C++的STL中的map和set。它是一種高度平衡的二叉搜索樹，對于內存中的數據結構來說，維護起來比較簡單。

B+樹則更適合用于實現磁盤存儲上的索引結構：

• 2、B+Tree vs 二叉樹
  • B+樹的節點相對較大，能夠存儲更多的索引項，從而減少了樹的高度，降低了訪問磁盤的次數(矮胖)
  • B+樹的節點相對較大，一個節點可以存儲多個關鍵字，這意味著在進行磁盤IO時，可以一次性讀取更多的索引項到內存中，利用了局部性原理，減少了IO的開銷（局部性）
  • B+樹的節點相對較大，樹的高度低，節點分裂和合并等平衡操作相對較少，在插入和刪除操作時，B+樹通常能夠更加高效地維持樹的平衡性，而不需要頻繁地進行平衡調整操作
  • B+樹的葉子節點形成了一個有序鏈表，便于范圍查詢

6.最左前綴匹配原則了解么？

聯合索引要按照最左優先的方式進行索引的匹配

比如，如果創建了一個 (a, b, c) 聯合索引，如果查詢條件是以下這幾種，就可以匹配上聯合索引：

• where a=1；
• where a=1 and b=2 and c=3；
• where a=1 and b=2；

需要注意的是，因為有查詢優化器，所以 a 字段在 where 子句的順序并不重要。

但是，如果查詢條件是以下這幾種，因為不符合最左匹配原則，所以就無法匹配上聯合索引，聯合索引就會失效:

• where b=2；
• where c=3；
• where b=2 and c=3；

為什么聯合索引不遵循最左匹配原則就會失效？

原因是，在聯合索引的情況下，數據是按照索引第一列排序，第一列數據相同時才會按照第二列排序

7.什么是覆蓋索引

查詢的數據能在二級索引里查詢的到，不需要通過主鍵索引查詢獲得，可以避免回表的操作

假設我們只需要查詢商品的名稱、價格，有什么方式可以避免回表呢？

我們可以建立一個聯合索引，即「商品ID、名稱、價格」作為一個聯合索引。如果索引中存在這些數據，查詢將不會再次檢索主鍵索引，從而避免回表。

8.如何查看某條 SQL 語句是否?到了索引？[待完善]

在這個查詢前加上 EXPLAIN 來查看執行計劃：

EXPLAIN SELECT * FROM my_table WHERE column_name = 'value';

這將會返回一個執行計劃，其中包含了MySQL執行這個查詢時使用的索引信息。如果在 Extra 列中看到 Using index 或者 Using where; Using index，那么表示查詢使用了索引。如果沒有使用索引，可能會看到 Using where 或者 Using filesort 等