聚簇索引、非聚簇索引、覆蓋索引

本篇將帶你搞懂什么是聚簇索引、非聚簇索引、覆蓋索引，并且通過畫圖的方式了解索引查找的過程，明白什么是回表查詢

索引（Index）是數(shù)據(jù)庫(kù)中一種用于快速查找和訪問表中數(shù)據(jù)的結(jié)構(gòu)，它類似于書的目錄，通過索引可以快速定位到目標(biāo)數(shù)據(jù)，而無(wú)需遍歷整個(gè)表，索引的存在可以顯著提高查詢速度，尤其是在處理大量數(shù)據(jù)時(shí)

MySQL中默認(rèn)使用索引的數(shù)據(jù)結(jié)構(gòu)是B+樹，詳細(xì)可以參考：MySQL索引為什么是B+數(shù)

那什么又是聚簇索引、非聚簇索引、覆蓋索引？

聚簇索引

聚簇索引也叫聚集索引，是一種將數(shù)據(jù)行的物理存儲(chǔ)順序與索引的邏輯順序相同的索引，換句話說，數(shù)據(jù)是直接存儲(chǔ)在索引的葉子節(jié)點(diǎn)中，通過主鍵查找到某一行數(shù)據(jù)，這一行數(shù)據(jù)的全部?jī)?nèi)容都存放在這個(gè)葉子節(jié)點(diǎn)中，聚簇索引必須有，而且只能有一個(gè)。

以下面這個(gè)表為例：

以主鍵id來為其生成一個(gè)簡(jiǎn)單的B+樹索引為：

這個(gè)利用主鍵字段來生成的索引就是一個(gè)聚簇索引，當(dāng)我們通過id查找某一個(gè)用戶時(shí)，通過查找算法，找到了他所在的葉子節(jié)點(diǎn)，那么在這個(gè)葉子節(jié)點(diǎn)中，就存放了該用戶的整行數(shù)據(jù)，包括姓名、賬號(hào)、年齡：

如果定義了主鍵，MySQL會(huì)自動(dòng)將主鍵列作為聚簇索引；如果沒有主鍵，MySQL會(huì)選擇一個(gè)唯一非空索引；如果沒有唯一索引，MySQL會(huì)生成一個(gè)隱藏的rowid作為主鍵來生成聚簇索引

非聚簇索引

相比于聚簇索引，非聚簇索引也叫二級(jí)索引，它的葉子節(jié)點(diǎn)存儲(chǔ)的是索引列的值以及指向?qū)嶋H數(shù)據(jù)行的指針（或主鍵值），而不是完整的數(shù)據(jù)行，也就是說，非聚簇索引的葉子節(jié)點(diǎn)中，存放的不再是整行數(shù)據(jù)，而是該行數(shù)據(jù)的主鍵

當(dāng)我們?yōu)樯媳斫⑺饕龝r(shí)，使用的不是id，而是username：

默認(rèn)會(huì)使用字母順序來進(jìn)行排序，但是其葉子節(jié)點(diǎn)中存儲(chǔ)的不再是整行數(shù)據(jù)，而是該行數(shù)據(jù)的索引值

回表查詢

回表查詢是數(shù)據(jù)庫(kù)查詢中的一個(gè)術(shù)語(yǔ)，指的是在使用非聚簇索引（或普通索引）時(shí)，數(shù)據(jù)庫(kù)查詢引擎需要通過索引查找到對(duì)應(yīng)的行號(hào)（或主鍵值），然后再回到表中查找完整的行數(shù)據(jù)的過程

以上表為例，我們想要查詢username為zhaoliu的用戶全部信息：

select * from user where username = 'zhaoliu'

1.此時(shí)回先到username的非聚簇索引中去查找

通過username的比對(duì)查找，找到了zhaoliu所在的葉子節(jié)點(diǎn)，但是葉子節(jié)點(diǎn)中并沒有我們需要的全部用戶信息，只有該用戶的主鍵值id

2.這時(shí)就會(huì)拿著id去聚簇索引中比對(duì)查找

找到該用戶的葉子節(jié)點(diǎn)，再把葉子節(jié)點(diǎn)中的整行數(shù)據(jù)返回

回表查詢?cè)黾恿?I/O 操作，尤其是表數(shù)據(jù)量大時(shí)會(huì)顯著降低性能，所以我們?cè)谠O(shè)計(jì)索引時(shí)，盡量要避免出現(xiàn)回表查詢

當(dāng)查詢的字段較少且頻率較高時(shí)，建議使用覆蓋索引優(yōu)化查詢

覆蓋索引

覆蓋索引（Covering Index） 是一種索引優(yōu)化技術(shù)，指的是查詢所需的所有字段都可以直接從索引中獲取，無(wú)需再回表查詢。這種方式可以顯著提高查詢性能，因?yàn)楸苊饬嘶乇聿僮?/p>

也就是說，當(dāng)我們建立索引時(shí)，不再使用單一字段來建立索引，而是包含多個(gè)字段，這樣在利用索引進(jìn)行查詢信息時(shí)，葉子節(jié)點(diǎn)中可以包含全部的所需數(shù)據(jù)，不必再到其他索引中查詢，因此可以避免回表查詢

比如之前以username建立的索引，如果我們不查詢用戶的全部數(shù)據(jù)，而是只需要返回username和id：

select id,name from user where name = 'zhangsan'

以username建立的索引中已經(jīng)包含了想要的全部數(shù)據(jù)：username和id，因此不需要再去查詢其他的索引，這就是覆蓋索引

在建立索引時(shí)，可以設(shè)置多字段建立索引：

CREATE INDEX idx_name_age ON users(name, age)

這樣索引的葉子節(jié)點(diǎn)中就存放了更多的數(shù)據(jù)

當(dāng)我們查詢所需要的信息name 和 age時(shí)，索引 idx_name_age 包含 name 和 age，因此查詢所需的字段完全覆蓋，無(wú)需回表

SELECT name, age FROM users WHERE name = 'John'

但是覆蓋索引也有一些弊端：

• 1.增加存儲(chǔ)開銷
• 索引存儲(chǔ)需要額外空間，字段越多，索引越大。
• 2.寫性能受影響
• 插入、更新、刪除操作需要同時(shí)更新覆蓋索引，導(dǎo)致寫入開銷增加。
• 3.適用場(chǎng)景有限
• 覆蓋索引適用于查詢固定字段的場(chǎng)景，但無(wú)法應(yīng)對(duì)動(dòng)態(tài)字段需求。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論