聚簇索引（Clustered Index）和非聚簇索引（Non-clustered Index）是數(shù)據(jù)庫中的兩種索引類型，它們在組織和存儲數(shù)據(jù)時有不同的方式。

聚簇索引

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

圖片

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

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

圖片

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

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

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

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

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

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

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

擴展知識

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

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

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

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

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

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

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

覆蓋索引

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

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

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

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

SELECT key2 FROM covering_index_sample WHERE key1 = 'keytest';

此時可以通過覆蓋索引查詢，無需進行回表操作。

但是對于以下 SQL 語句，雖然是索引覆蓋，但由于不符合最左前綴匹配，無法利用索引（會掃描索引樹）：

SELECT key1 FROM covering_index_sample WHERE key2 = 'keytest';

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

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

索引下推

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

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

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

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

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

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

索引下推不止 like

上面的例子中，提到了 like，包括 MySQL 官網(wǎng)中也只提到了 like，但是其實不止有 Like。因為我認為索引下推其實是解決索引失效帶來的效率低的問題的一種手段。

所以當(dāng)聯(lián)合索引中，某個非前導(dǎo)列因為索引失效而要進行掃表并回表時，就可以進行索引下推優(yōu)化了。

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

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

因為 b 字段因為類型不匹配導(dǎo)致索引失效了，但是通過下推優(yōu)化其實是可以減少回表的次數(shù)的。