快捷導(dǎo)航

MySQL中復(fù)合索引和覆蓋索引的區(qū)別詳解

更新時(shí)間：2023年11月22日 10:14:01 作者：杜小舟

這篇文章主要介紹了MySQL中復(fù)合索引和覆蓋索引的區(qū)別詳解,復(fù)合索引是一種索引,它包含多個(gè)字段,復(fù)合索引能夠使一個(gè)SQL查詢(xún)多個(gè)條件時(shí)也能走索引,提高查詢(xún)性能,需要的朋友可以參考下

前言準(zhǔn)備

我們先準(zhǔn)備一張表和幾個(gè)字段，方便介紹覆蓋索引和復(fù)合索引。

創(chuàng)建一個(gè)user表，表中有id、name、school、age字段。

字段名	字段類(lèi)型
id	int
name	varchar
school	varchar
age	int

復(fù)合索引

先來(lái)說(shuō)復(fù)合索引，復(fù)合索引是一種索引，它包含多個(gè)字段，復(fù)合索引能夠使一個(gè)SQL查詢(xún)多個(gè)條件時(shí)也能走索引，提高查詢(xún)性能。

比如，創(chuàng)建一個(gè)name、school和age的復(fù)合索引：

CREATE INDEX idx_name_school_age ON user(name, school, age);

查詢(xún)SQL為：

select * from user where name = '張三' and school = '北京大學(xué)' and age > 18

注意，在這里要注意查詢(xún)條件的順序要按照復(fù)合索引的字段順序，要是不按照復(fù)合索引的順序，通常情況下是不會(huì)走索引的，因?yàn)閺?fù)合索引是按照最左匹配原則，最左匹配原則的意思是查詢(xún)條件的順序要按照復(fù)合索引字段順序。

為什么要說(shuō)通常情況下不走索引呢，是因?yàn)镸ySQL的查詢(xún)優(yōu)化器會(huì)根據(jù)條件查詢(xún)和數(shù)據(jù)分布情況選擇最優(yōu)的執(zhí)行計(jì)劃，假設(shè)，我們把school和name的查詢(xún)條件到換一下，變成：

select * from user where school = '北京大學(xué)' and name = '張三' and age > 18

如果，我們條件中的school的值非常稀疏，name和age的值非常密集，那么MySQL在查詢(xún)時(shí)會(huì)認(rèn)為使用索引能夠加速查詢(xún)，也會(huì)使用索引。

這里，有些同學(xué)可能會(huì)對(duì)稀疏和密集這兩個(gè)詞有些困惑，不明白這兩個(gè)詞的意思，在這里要單獨(dú)拿出來(lái)說(shuō)一下，下面往user表中增加五條數(shù)據(jù)，做為案例數(shù)據(jù)：

id	name	school	age
1	張三	北京大學(xué)	18
2	李四	北京大學(xué)	18
3	王五	清華大學(xué)	18
4	趙六	北京大學(xué)	18
5	金七	清華大學(xué)	18

稀疏的意思是每個(gè)不同的值出現(xiàn)的次數(shù)很多，比如說(shuō)user表中有五條記錄，name字段分別有張三、李四、王五、趙六、金七，那么我們就可以說(shuō)name這個(gè)字段非常稀疏；

那么相反，密集就好理解了，比如，user表school字段的值分別只有北京大學(xué)和清華大學(xué)，那么就可以說(shuō)school字段的值非常密集。

總之，MySQL的查詢(xún)優(yōu)化器會(huì)根據(jù)條件查詢(xún)和數(shù)據(jù)分布情況選擇最優(yōu)的執(zhí)行計(jì)劃，并不是說(shuō)我們不按照復(fù)合索引的字段順序做查詢(xún)條件就不會(huì)走復(fù)合索引。

覆蓋索引

覆蓋索引是一種索引優(yōu)化手段，假設(shè)，我們想查詢(xún)user表中name等于張三，獲取張三的school和age字段數(shù)據(jù)，那么我們的SQL應(yīng)該是：

slelect school, age from user where name = '張三'

那么，為了優(yōu)化這個(gè)查詢(xún)SQL，我們就需要?jiǎng)?chuàng)建一個(gè)復(fù)合索引，復(fù)合索引中有name、school和age字段：

CREATE INDEX idx_name_school_age ON user(name, school, age);

當(dāng)我們查詢(xún)SQL時(shí)，MySQL就可以直接從索引中獲取所需要的數(shù)據(jù)，不需要再回表查詢(xún)數(shù)據(jù)了，這樣就能大大的提高查詢(xún)速度。

在這里介紹一下回表：假設(shè)，我們創(chuàng)建一個(gè)復(fù)合索引，復(fù)合索引中有name和age字段：

CREATE INDEX idx_name_school_age ON user(name, age);

我們這里寫(xiě)一個(gè)查詢(xún)SQL，SQL中查詢(xún)name等于張三的school和age字段值：

slelect school, age from user where name = '張三'

那么在查詢(xún)時(shí)，SQL語(yǔ)句會(huì)直接查詢(xún)索引，從索引中查詢(xún)到name叫做張三的數(shù)據(jù)位置，再根據(jù)位置去表中查詢(xún)完整的數(shù)據(jù)，這里，根據(jù)位置去表中查詢(xún)完整的數(shù)據(jù)叫做回表。