聚簇索引 (主鍵索引)（Clustered Index (Primary Index)）

聚簇索引與其說是索引，不如說是InnoDB用來存儲(chǔ)記錄的數(shù)據(jù)容器更為恰當(dāng)。

InnoDB中的聚簇索引采用B-Tree組織起來，每個(gè)節(jié)點(diǎn)都是一個(gè)Page（InnoDB存儲(chǔ)記錄的最小單位）；非葉節(jié)點(diǎn)存 Key 的值和指向孩子節(jié)點(diǎn)的指針，葉子節(jié)點(diǎn)則存儲(chǔ)記錄和指向相鄰葉節(jié)點(diǎn)的指針（所有葉節(jié)點(diǎn)構(gòu)成一個(gè)雙向鏈表），下面是一個(gè)簡單的示意圖：

InnoDB根據(jù)Key值順序存儲(chǔ)記錄，相鄰的Node彼此通過指針連接，這樣有兩個(gè)好處：

1. 這樣相關(guān)的記錄會(huì)存儲(chǔ)的比較近，讀取相關(guān)記錄的時(shí)候只需要Load少數(shù)Page就好。例如上圖若要讀取key 5和key 6的記錄，只要加載page 5就好。
2. 執(zhí)行范圍查詢時(shí)不用整棵B+樹都掃描一遍，只要找到最小的key值所在的葉節(jié)點(diǎn)Page，一直往后讀即可。例如上圖查詢key值在5?10的記錄，只要找到key 5所在的page 5，然后一直往后讀值到key 10所在的page 7為止。

當(dāng)然排好序的記錄也有一些缺點(diǎn)，如果插入key值無序的記錄時(shí)容易造成性能問題。例如上圖如果插入一條key為13的記錄時(shí)，MySQL會(huì)直接寫入到page 8；但若是插入一條key為9的記錄時(shí)會(huì)導(dǎo)致page 7發(fā)生頁拆分（Page Split），這種情況下MySQL會(huì)在Page之間移動(dòng)記錄，繼而影響性能。

MySQL并不會(huì)把Page的全部空間都用完，相反，它會(huì)保留一部分空間為日后添加或更新使用，如上圖的page 6。根據(jù)MySQL 5.7 Reference Manual：

If index records are inserted in a sequential order (ascending or descending), the resulting index pages are about 15/16 full. If records are inserted in a random order, the pages are from 1/2 to 15/16 full.

如果index的記錄是順序插入，索引頁（Index Page）的使用率會(huì)在15/16左右，但若是亂序插入的話，索引頁的使用率則會(huì)在1/2到15/16之間。

由上述原因可以知道，聚簇索引是如何影響范圍查詢、插入記錄的性能以及Page空間的使用率的，所以一個(gè)恰當(dāng)?shù)木鄞厮饕I值（Clustered Key）很重要。MySQL選擇聚簇索引鍵值的規(guī)則如下：

1. 有Primary Key，選Primary Key；
2. 沒有Primary Key，選第一個(gè)NOT NULL的UNIQUE Key；
3. 若都沒有，則InnoDB生成一個(gè)auto increment的隱藏字段做聚簇索引鍵值。

二級索引（Secondary Index）

二級索引同樣使用B-Tree數(shù)據(jù)結(jié)構(gòu)，不同的是葉節(jié)點(diǎn)只存儲(chǔ)二級索引的鍵值和聚簇索引鍵值（通常是Primary Key），聚簇索引鍵值是用于回表查詢該條記錄。

注意到上圖中二級索引鍵值的順序和聚簇索引鍵值順序通常不同，所以二級索引做范圍查詢讀取記錄的性能通常不如聚簇索引高效（回表操作會(huì)有大量的隨機(jī)IO）。因?yàn)槎壦饕龝?huì)存儲(chǔ)聚簇索引的鍵值，因此儲(chǔ)聚簇索引鍵值的大小也會(huì)影響二級索引的大小，所以在選擇聚簇索引鍵值時(shí)需要注意這點(diǎn)。

另外當(dāng)SELECT的字段被二級索引覆蓋的話，MySQL就不需要再回表查詢了，這樣執(zhí)行速度更快。例如：

CREATE TABLE `test_table` (
    `primary_key` int(11) NOT NULL,
    `secondary_key` int(11) DEFAULT NULL,
    `other_key` int(11) DEFAULT NULL,
    PRIMARY KEY (`primary_key`),
    KEY `SECONDARY` (`secondary_key`)
)

用explain命令分析SELECT primary_key 和 secondary_key 的 SQL，extra 字段顯示 using index，即 MySQL 執(zhí)行這條語句時(shí)直接從索引取值。

反之，則會(huì)看到extra字段顯示 using index condition，即需要進(jìn)行回表取需要的字段值。

小結(jié)

索引是個(gè)復(fù)雜的主題，但通過了解索引底層的運(yùn)行原理可以幫助我們更精準(zhǔn)的使用索引，基本原則如下：

1. InnoDB讀取記錄是以Page為單位，加載一個(gè)Page只為讀取一條記錄是很浪費(fèi)且低效的行為。挑選好Primary Key，可以利用訪問局部性(Locality of Reference)提高性能（讓相關(guān)的記錄集中在幾個(gè)Page里面，這樣InnoDB加載一個(gè)Page就可以讀取到多條記錄）。
2. 使用二級索引讀取記錄需要進(jìn)行回表操作，正如同上面第一點(diǎn)提到的，加載一個(gè)Page讀取一條記錄是低效的。因此二級索引覆蓋所有需要的字段對性能會(huì)有顯著提升。

譯自 A Brief Introduction to Cluster Index and Secondary Index in InnoDB