引言

在數(shù)據(jù)庫(kù)領(lǐng)域，聚簇索引和非聚簇索引是至關(guān)重要的概念，它們?cè)跀?shù)據(jù)組織與存儲(chǔ)方式上截然不同，深刻影響著查詢性能。

聚簇索引：數(shù)據(jù)與索引的緊密結(jié)合

聚簇索引的核心特點(diǎn)是數(shù)據(jù)與索引存儲(chǔ)在一處。其非葉子節(jié)點(diǎn)存放索引字段值，葉子節(jié)點(diǎn)則承載對(duì)應(yīng)記錄的整行數(shù)據(jù)。在InnoDB存儲(chǔ)引擎里，聚簇索引依表的主鍵構(gòu)建，表數(shù)據(jù)會(huì)依照主鍵順序存儲(chǔ)于磁盤，保證了行的物理存儲(chǔ)順序與主鍵邏輯順序一致，這使得聚簇索引在查找時(shí)速度優(yōu)勢(shì)顯著。

圖片

例如，若有一張員工表，以員工ID為主鍵構(gòu)建聚簇索引，那么在查詢時(shí)，一旦定位到索引節(jié)點(diǎn)，即可直接獲取該行員工的全部信息，無(wú)需額外操作。

非聚簇索引：索引與數(shù)據(jù)的分離存儲(chǔ)

與聚簇索引相反，非聚簇索引將索引和數(shù)據(jù)分開存放。它的葉子節(jié)點(diǎn)包含索引字段值以及指向數(shù)據(jù)頁(yè)數(shù)據(jù)行的邏輯指針。在InnoDB中，非聚簇索引通常基于非主鍵字段創(chuàng)建，也被稱作二級(jí)索引。

圖片

如在上述員工表中，若針對(duì)員工年齡創(chuàng)建非聚簇索引，其葉子節(jié)點(diǎn)會(huì)存儲(chǔ)年齡值及對(duì)應(yīng)的員工ID指針。這意味著通過(guò)非聚簇索引查詢時(shí)，先找到索引值對(duì)應(yīng)的主鍵ID，再憑借主鍵ID去查找所需數(shù)據(jù)，此過(guò)程即為回表操作。

回表操作：非聚簇索引查詢的額外步驟

在InnoDB中，由于非聚簇索引葉子節(jié)點(diǎn)僅存儲(chǔ)主鍵值，當(dāng)執(zhí)行查詢時(shí)，如依據(jù)員工年齡查找員工其他信息，首先要通過(guò)非聚簇索引獲取主鍵ID，接著利用該主鍵ID再次查詢數(shù)據(jù)表以獲取完整數(shù)據(jù)，這增加了查詢的I/O開銷。相比之下，使用聚簇索引（主鍵索引）查詢則無(wú)需回表，效率更高。為減少回表次數(shù)，可借助覆蓋索引和索引下推技術(shù)優(yōu)化。

覆蓋索引：減少數(shù)據(jù)讀取的高效策略

覆蓋索引是指查詢語(yǔ)句所需數(shù)據(jù)能直接從索引獲取，無(wú)需訪問(wèn)數(shù)據(jù)表。例如，表中有聯(lián)合索引idx_key1_key2 (key1,key2)，當(dāng)執(zhí)行SELECT key2 FROM covering_index_sample WHERE key1 = 'keytest';時(shí)，可直接利用索引返回結(jié)果，避免回表。

但需注意，若不符合最左前綴匹配原則，如SELECT key1 FROM covering_index_sample WHERE key2 = 'keytest';，雖看似覆蓋索引，但會(huì)掃描索引樹，無(wú)法有效利用索引。并且，若查詢信息不在聯(lián)合索引內(nèi)，如SELECT key2, key3 FROM covering_index_sample WHERE key1 = 'keytest';也無(wú)法使用覆蓋索引。

索引下推：優(yōu)化查詢性能的有力手段

索引下推是MySQL 5.6引入的默認(rèn)開啟優(yōu)化技術(shù)（可關(guān)閉）。以people表的(zipcode，lastname，firstname)索引為例，對(duì)于查詢SELECT * FROM people WHERE zipcode='123' AND lastname LIKE '%yian%' AND address LIKE '%Main Street%';，若未啟用索引下推，MySQL先依zipcode='123' 從存儲(chǔ)引擎獲取數(shù)據(jù)返回服務(wù)端，再在服務(wù)端判斷其他條件；啟用后，會(huì)先篩選符合zipcode='123'且滿足lastname LIKE '%yian%'的索引，符合條件才定位數(shù)據(jù)，有效減少回表次數(shù)。

不僅限于LIKE條件，聯(lián)合索引中非前導(dǎo)列因類型不匹配等原因索引失效需掃表回表時(shí)，如select d from t2 where a = "ni" and b = 1; （b 字段索引失效），也可利用索引下推優(yōu)化。

在執(zhí)行計(jì)劃中，使用索引下推時(shí)extra字段會(huì)顯示"Using index condition" 。