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

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

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

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

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

聚簇索引

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

以下面這個表為例：

以主鍵id來為其生成一個簡單的B+樹索引為：

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

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

非聚簇索引

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

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

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

回表查詢

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

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

select * from user where username = 'zhaoliu'

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

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

2.這時就會拿著id去聚簇索引中比對查找

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

回表查詢增加了 I/O 操作，尤其是表數(shù)據(jù)量大時會顯著降低性能，所以我們在設(shè)計索引時，盡量要避免出現(xiàn)回表查詢

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

覆蓋索引

覆蓋索引（Covering Index） 是一種索引優(yōu)化技術(shù)，指的是查詢所需的所有字段都可以直接從索引中獲取，無需再回表查詢。這種方式可以顯著提高查詢性能，因為避免了回表操作

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

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

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

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

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

CREATE INDEX idx_name_age ON users(name, age)

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

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

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

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

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

總結(jié)

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

最新評論