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

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

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

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

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

聚簇索引

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

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

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

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

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

非聚簇索引

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

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

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

回表查詢(xún)

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

以上表為例，我們想要查詢(xún)username為zhaoliu的用戶(hù)全部信息：

select * from user where username = 'zhaoliu'

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

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

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

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

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

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

覆蓋索引

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

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

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

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

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

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

CREATE INDEX idx_name_age ON users(name, age)

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

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

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

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

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

總結(jié)

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

最新評(píng)論