聚簇索引（Clustered Index）和非聚簇索引（Non-clustered Index）是數(shù)據(jù)庫(kù)中的兩種索引類(lèi)型，它們?cè)诮M織和存儲(chǔ)數(shù)據(jù)時(shí)有不同的方式。

聚簇索引

聚簇索引簡(jiǎn)單理解就是將數(shù)據(jù)與索引放在一起，找到索引即找到了數(shù)據(jù)。換句話說(shuō)，對(duì)于聚簇索引，其非葉子節(jié)點(diǎn)上存儲(chǔ)的是索引字段的值，而葉子節(jié)點(diǎn)上存儲(chǔ)的是對(duì)應(yīng)記錄的整行數(shù)據(jù)。

圖片

在 InnoDB 中，聚簇索引（Clustered Index）是指按照每張表的主鍵構(gòu)建的一種索引方式。它將表數(shù)據(jù)按照主鍵的順序存儲(chǔ)在磁盤(pán)上，確保了行的物理存儲(chǔ)順序與主鍵的邏輯順序相同。這種索引方式使得查找聚簇索引的速度非?？臁?/p>

非聚簇索引是指將索引與數(shù)據(jù)分開(kāi)存儲(chǔ)的一種方式。在非聚簇索引中，葉子節(jié)點(diǎn)包含索引字段的值以及指向數(shù)據(jù)頁(yè)數(shù)據(jù)行的邏輯指針。

圖片

在 InnoDB 中，非聚簇索引（Non-clustered Index）是根據(jù)非主鍵字段創(chuàng)建的索引，通常稱(chēng)為二級(jí)索引。它不影響表中數(shù)據(jù)的物理存儲(chǔ)順序，而是單獨(dú)創(chuàng)建一張索引表，用于存儲(chǔ)索引列和對(duì)應(yīng)行的指針。

在 InnoDB 中，主鍵索引就是聚簇索引，而非主鍵索引則是非聚簇索引。因此，在 InnoDB 中：

• 對(duì)于聚簇索引，其非葉子節(jié)點(diǎn)上存儲(chǔ)的是索引值，而葉子節(jié)點(diǎn)上存儲(chǔ)的是整行記錄。
• 對(duì)于非聚簇索引，其非葉子節(jié)點(diǎn)上存儲(chǔ)的是索引值，而葉子節(jié)點(diǎn)上存儲(chǔ)的是主鍵的值以及索引值。

因此，通過(guò)非聚簇索引進(jìn)行查詢時(shí)，需要進(jìn)行一次回表操作，即先通過(guò)索引查找到主鍵 ID，然后再通過(guò) ID 查詢所需字段。

沒(méi)有創(chuàng)建主鍵怎么辦？

在 InnoDB 中，如果表結(jié)構(gòu)中沒(méi)有定義主鍵，數(shù)據(jù)庫(kù)會(huì)自動(dòng)為每行記錄添加一個(gè)隱藏的主鍵，通常稱(chēng)為 db_row_id 字段。這個(gè)隱藏主鍵會(huì)確保每行記錄都有一個(gè)唯一的標(biāo)識(shí)符。

如果表中沒(méi)有合適的唯一索引可用作聚簇索引，數(shù)據(jù)庫(kù)會(huì)使用這個(gè)隱藏主鍵來(lái)構(gòu)建聚簇索引。這樣可以確保每行記錄都有一個(gè)物理上的唯一標(biāo)識(shí)符，并且能夠保持索引的唯一性和快速查詢的特性。

擴(kuò)展知識(shí)

我們剛剛又提到回表的概念，什么是回表呢？

什么是回表，怎么減少回表的次數(shù)？

在 InnoDB 中，索引 B+樹(shù)的葉子節(jié)點(diǎn)存儲(chǔ)了整行數(shù)據(jù)的是主鍵索引，也被稱(chēng)為聚簇索引。而索引 B+樹(shù)的葉子節(jié)點(diǎn)存儲(chǔ)了主鍵的值的是非主鍵索引，也被稱(chēng)為非聚簇索引。

在數(shù)據(jù)存儲(chǔ)方面，主鍵（聚簇）索引的 B+樹(shù)的葉子節(jié)點(diǎn)直接包含了我們要查詢的整行數(shù)據(jù)。而非主鍵（非聚簇）索引的葉子節(jié)點(diǎn)則包含了主鍵的值。

因此，當(dāng)我們通過(guò)非聚簇索引進(jìn)行查詢時(shí)，首先會(huì)通過(guò)非聚簇索引查找到主鍵的值，然后需要再通過(guò)主鍵的值進(jìn)行一次查詢才能獲取到我們要查詢的數(shù)據(jù)。這個(gè)過(guò)程稱(chēng)為回表。

因此，在 InnoDB 中，使用主鍵進(jìn)行查詢效率更高，因?yàn)檫@個(gè)過(guò)程不需要回表。此外，通過(guò)依賴(lài)覆蓋索引、索引下推等技術(shù)，我們可以通過(guò)優(yōu)化索引結(jié)構(gòu)和 SQL 語(yǔ)句來(lái)減少回表的次數(shù)。

什么是索引覆蓋、索引下推？

覆蓋索引

覆蓋索引是指查詢語(yǔ)句的執(zhí)行只需從索引中獲取所需數(shù)據(jù)，而無(wú)需從數(shù)據(jù)表中讀取。也可以稱(chēng)之為實(shí)現(xiàn)了索引覆蓋。

當(dāng)一條查詢語(yǔ)句符合覆蓋索引條件時(shí)，MySQL 只需通過(guò)索引就能返回查詢所需數(shù)據(jù)，而不需要進(jìn)行索引查找后再返回表操作，從而減少 I/O，提高效率。

例如，在表 covering_index_sample 中有一個(gè)普通索引 idx_key1_key2(key1,key2)。

當(dāng)我們執(zhí)行以下 SQL 語(yǔ)句時(shí)：

SELECT key2 FROM covering_index_sample WHERE key1 = 'keytest';

此時(shí)可以通過(guò)覆蓋索引查詢，無(wú)需進(jìn)行回表操作。

但是對(duì)于以下 SQL 語(yǔ)句，雖然是索引覆蓋，但由于不符合最左前綴匹配，無(wú)法利用索引（會(huì)掃描索引樹(shù)）：

SELECT key1 FROM covering_index_sample WHERE key2 = 'keytest';

另外，如果查詢語(yǔ)句中需要的信息不包含在聯(lián)合索引中，那么就無(wú)法使用索引覆蓋。例如：

SELECT key2, key3 FROM covering_index_sample WHERE key1 = 'keytest';

索引下推

索引下推是 MySQL 5.6 引入的一種優(yōu)化技術(shù)，默認(rèn)開(kāi)啟，可通過(guò)設(shè)置 SET optimizer_switch = 'index_condition_pushdown=off'; 來(lái)關(guān)閉。

它的工作原理如下：假設(shè) people 表中（zipcode，lastname，firstname）構(gòu)成一個(gè)索引?？紤]以下查詢：

SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';

如果沒(méi)有使用索引下推技術(shù)，MySQL 會(huì)通過(guò) zipcode='95054'從存儲(chǔ)引擎中查詢對(duì)應(yīng)的數(shù)據(jù)，然后將結(jié)果返回到 MySQL 服務(wù)端，接著 MySQL 服務(wù)端再基于lastname LIKE '%etrunia%' 和 address LIKE '%Main Street%'來(lái)判斷數(shù)據(jù)是否符合條件。

而如果使用了索引下推技術(shù)，MySQL 首先會(huì)返回符合 zipcode='95054'的索引，然后根據(jù)lastname LIKE '%etrunia%'來(lái)判斷索引是否符合條件。如果符合條件，則根據(jù)該索引定位對(duì)應(yīng)的數(shù)據(jù)；如果不符合，則直接拒絕。有了索引下推優(yōu)化，可以在有 like 條件查詢的情況下，減少回表次數(shù)。

當(dāng)一條 SQL 使用到索引下推時(shí)，執(zhí)行計(jì)劃中的 extra 字段的內(nèi)容會(huì)顯示為 "Using index condition"。

索引下推不止 like

上面的例子中，提到了 like，包括 MySQL 官網(wǎng)中也只提到了 like，但是其實(shí)不止有 Like。因?yàn)槲艺J(rèn)為索引下推其實(shí)是解決索引失效帶來(lái)的效率低的問(wèn)題的一種手段。

所以當(dāng)聯(lián)合索引中，某個(gè)非前導(dǎo)列因?yàn)樗饕ФM(jìn)行掃表并回表時(shí)，就可以進(jìn)行索引下推優(yōu)化了。

如，有 a,b 聯(lián)合索引，類(lèi)型都是 varchar，以下 SQL 也可以用到索引下推：

select d from t2 where a = "ni" and b = 1;

因?yàn)?b 字段因?yàn)轭?lèi)型不匹配導(dǎo)致索引失效了，但是通過(guò)下推優(yōu)化其實(shí)是可以減少回表的次數(shù)的。