從InnoDB 索引執行簡述聚集索引和非聚集索引、覆蓋索引、回表、索引下推

小碼code發表於2021-12-28

關於InnoDB 儲存引擎的有聚集索引和非聚集索引，覆蓋索引，回表，索引下推等概念，這些知識點比較多，也比較零碎，但是概念都是基於索引建立的，本文從索引查詢資料講述上述概念。

聚集索引和非聚集索引

在 MySQL 資料庫中 InnoDB 儲存引擎，B+ 樹可分為聚集索引和非聚集索引。聚集索引也叫聚簇索引，非聚集索引也叫輔助索引或者二級索引。
建表的時候都會建立一個聚集索引，每張表都有唯一的聚集索引：

如果主鍵被定義了，那麼這個主鍵就是作為聚集索引
如果沒有主鍵被定義，那麼該表的第一個唯一非空索引作為聚集索引
如果沒有主鍵也沒有唯一索引，InnoDB 內部會生成一個隱藏的主鍵作為聚集索引，這個隱藏的主鍵是一個 6 個位元組的列，該類的值會隨著資料的插入自增。

在建立表新增的索引都是非聚集索引，非聚集索引就是一個為了找到聚集索引的二級索引，通過二級索引索引找到主鍵，再查詢資料。
建立一個表 T,表中有個一個主鍵id。表中有欄位 k,並建立在 k 欄位上建立索引。

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k)
)engine=InnoDB;

在表中插入資料分別為(100,1)、（200,2）、（300,3）、（500,5）、（600,6），分別用R1~R5表示。當建立表和插入資料後會生成兩棵樹：

其中左邊的是聚集索引，右邊的是非聚集索引。非聚集索引葉子節點儲存的是主鍵的值，聚集索引儲存的是整行的資料。
執行 select * from T where k between 3 and 5,有以下的執行流程：

1、在 k 索引樹上找到 k = 3,取得ID = 300
2、再到 ID 索引樹查到 ID=300對應的R3
3、在 k 索引樹取下一個值 k=5，取得ID=500
4、再回到 ID 索引樹查到 ID=500對應的 R4
5、在 k 索引樹取下一個值 k=6，不滿足條件，查詢結束

在這個過程中，從二級索引回答主鍵索引樹查詢的過程，稱為回表。上面流程回表了兩次，分別是步驟2和步驟4。

覆蓋索引

如果執行的語句是 select ID from T where k between 3 and 5,這個時候只需要查 ID 的值，而 ID 的值已經在 k 索引樹上了，這個時候就不需要回表，也就是說在這個查詢中，索引 k “覆蓋了”查詢，這個稱為覆蓋索引。

由於覆蓋索引可以減少樹的搜尋次數，提高查詢效能，所以使用覆蓋索引是一個常用的索引優化手段。

使用覆蓋索引最常見的方法是建立聯合索引，將需要查詢的欄位都放在聯合索引上。

最左字首原則

最左字首原則，指的是在一個複合索引中（a,b,c），b+ 樹會按照從左往右的順序建立搜尋樹，b+ 樹會優先比較 a，如果 a 相同在依次比較 b 和 c，最後得到檢索資料，但是像查詢（b，c）這樣的資料沒有 a 欄位，b+樹就不知道從哪個結點查起了。因為搜尋樹的第一個比較因子就是 a。

索引下推（icp）

索引下推是 mysql 5.6 新特性

建立一個表 use,其中主要有幾個欄位：id、name、age、address。建立聯合索引（name,age）。

mysql> create table use(
id int primary key, 
name varchar(16), 
age int ,
index(name,age)
)engine=InnoDB;

在表中分別插入資料，（ID3，張六，30），（ID4，張三，10），（ID5，張三，10），（106，張三,20）
要執行以下查詢：

select * from user where name like '張%' and age=10

上面說到 InnoDB 索引滿足最左匹配原則，當不符合最左字首，會怎麼樣呢？在這個搜尋樹中，只能用“張”，找到一個滿足條件 103,然後再判斷其他條件是否滿足。

這條語句在 Mysql 5.6 之前和 Mysql 5.6 以及 Mysql 5.6 以後版本執行是不一致的。

Mysql 5.6 之前

在 5.6 之前是沒有索引下推的，只能從 ID3 開始一個個回表，虛線表示回表。到主鍵索引上找資料行，再對比欄位值，如下圖：
5.6 引入了索引下推，可以在索引遍歷過程中，對索引包含的欄位先做判斷，直接過濾到不滿足條件的記錄，減少回表次數。如下圖：

有了索引下推後，InnoDB 在（name，age）索引內就判斷了 age 是否等於 10，不等於 10 的直接跳過，所以上面只需要回表 2 次。