InnoDB學習（八）之 聚簇索引

御狐神發表於2022-01-05
索引

InnoDB中，表資料檔案本身就是以主鍵為索引的B+樹，樹的葉子節點存放一條條表資料，此索引樹被稱為表的聚簇索引。聚簇索引也稱為聚集索引，聚類索引，簇集索引，聚簇索引確定表中資料的物理順序。

InnoDB聚簇索引

InnoDB表主鍵

InnoDB中每張表都會有一個主鍵，表中的每一行資料都是按照主鍵的順序在聚簇索引中儲存的，InnoDB中有兩種方式確定一行資料的主鍵：

  • 顯式宣告：使用者可以在建表的時候通過primary key關鍵字來宣告主鍵列；
  • 唯一索引：如果使用者沒有宣告主鍵列，那麼InnoDB會使用第一個非空唯一列作為主鍵；
  • 自動生成：如果滿足以上兩種條件的列都不存在，那麼InnoDB會將RowId作為主鍵；

聚簇索引示例

首先我們在資料庫中建立一張使用者表，包含使用者ID、姓名、性別、年齡四個欄位：

create table user_info
(
    id   int primary key,
    age  int not null,
    name varchar(16),
    sex  bool
)engine=InnoDB;

向資料庫中插入如下資料：
使用者ID 1 2 3 4 5 6 7 8 9
姓名 陳爾 張散 李思 王舞 趙流 孫期 周跋 吳酒 鄭史
性別 男 男 女 女 男 男 男 女 男
年齡 5 10 20 28 35 56 25 80 90

上述表中插入指定資料後，得到的聚簇索引結構如下所示：

B+樹索引

可以看到，聚簇索引的葉子節點包含了所有資料，所以在需要查詢某一行資料的所有列時，通過聚簇索引查詢的效率最高。

非聚簇索引

InnoDB中，除了聚簇索引以外，其餘的索引都可以稱為非聚簇索引，非聚簇索引的葉子節點存放主鍵索引，而不是所有資料。通過非聚簇索引查詢資料，其流程是先通過非聚簇索引查詢到資料的主鍵，再通過主鍵查詢對應的資料。

對於上文中的使用者表，我們稍微修改一下建表語句，對使用者的年齡新增索引：

create table user_info
(
    id   int primary key,
    age  int not null,
    name varchar(16),
    sex  bool,
    key(age)
)engine=InnoDB;

向表中插入和上文中相同的資料，InnoDB會為這張表生成兩個索引樹：使用者ID對應的聚簇索引樹和使用者年齡對應的非聚簇索引樹，其結構如下圖所示。

B+樹索引

從圖中可以發現，聚簇索引和非聚簇索引最大的區別就是葉子節點存放的內容，聚簇索引的葉子節點存放了資料庫一行中的所有資料，而非聚簇索引的葉子節點存放了資料的主鍵。

大多數情況下，通過非聚簇索引查詢到主鍵值後，還需要通過主鍵值去聚簇索引查詢整行資料，從而獲取到滿足條件行的所有資料。所以非聚簇索引的查詢速度總是會比聚簇索引的查詢速度慢一些，日常開發中能使用聚簇索引應該儘量使用聚簇索引。

回表查詢

所謂的回表查詢，就是指通過非聚簇索引查詢資料時，可能需要回到聚簇索引多查詢一次資料的情況，對於上文中的資料，執行以下SQL語句，其查詢過程如下圖中的綠色路徑所示。

從圖中可以看到，在使用者年齡的索引樹的葉子節點中，包含了使用者的主鍵ID，由於我們需要獲取使用者的所有資訊，所以還需要按照使用者的ID去聚簇索引查詢使用者的所有資訊。

select * from user_info where age = 5;

B+樹索引

覆蓋索引

如果某次查詢的過程中，查詢需要的資料在非聚簇索引中就可以得到，那麼就沒有必要回到聚簇索引查詢行所有的資料，這種情況稱為覆蓋索引。我們可以把上面回表查詢的SQL修改為：

select id from user_info where age = 5;

由於年齡索引樹的葉子節點就包含了ID資訊，所以InnoDB不需要回聚簇索引再次查詢使用者ID，而是直接將年齡索引樹中的ID返回。

qrcode_for_gh_83670e17bbd7_344-2021-09-04-10-55-16

本文最先發布至微信公眾號，版權所有，禁止轉載！

相關文章