最近在深入學(xué)習(xí)MySQL，在學(xué)習(xí)最左匹配原則的時(shí)候，遇到了一個(gè)有意思的事情。請(qǐng)聽(tīng)我細(xì)細(xì)道來(lái)。

我的MySQL版本為8.0.32

可以通過(guò) show variables like 'version'; 查看使用的版本。

準(zhǔn)備工作

先建表，SQL語(yǔ)句如下：

create table joint_index_test(
        id int primary key,
        a int,
        b int,
        c int
);
alter table joint_index_test add index index_a_b_c(a,b,c);

表結(jié)構(gòu)非常簡(jiǎn)單，4個(gè)字段，兩個(gè)索引，主鍵索引id和聯(lián)合索引abc。暫時(shí)不向表中添加數(shù)據(jù)。

開(kāi)始測(cè)試

接下來(lái)我們進(jìn)行查詢操作和使用explain查看select語(yǔ)句的執(zhí)行

1. 最左匹配原則

explain select * from joint_index_test where a = 3;

這條SQL語(yǔ)句是否走了索引大家基本上都能夠分析出來(lái)，基礎(chǔ)比較好的小伙伴甚至可以直接分析出來(lái)掃描類型是什么。

執(zhí)行結(jié)果如下圖：

由于where后面的條件是a,遵循聯(lián)合索引的最左匹配原則，會(huì)使用索引index_a_b_c,進(jìn)行查詢。由于我們查詢的列是*,在joint_index_test可以擴(kuò)展為id,a,b,c,這些列在聯(lián)合索引a,b,c中都可以查詢到。所以MySQL在執(zhí)行的時(shí)候，會(huì)選擇使用覆蓋索引，不再進(jìn)行回表查詢?！緀xtra列為Using index】

繼續(xù)進(jìn)行測(cè)試第二條SQL語(yǔ)句

2. 覆蓋索引

explain select * from joint_index_test where b = 3;

根據(jù)最左匹配原則，我們可以判斷出來(lái)，第二條SQL語(yǔ)句應(yīng)該不會(huì)使用到index_a_b_c聯(lián)合索引，因?yàn)槁?lián)合索引是按照字段的順序從左到右進(jìn)行構(gòu)建的，也就是從字段a進(jìn)行從小到大的排序，只有字段a相等的時(shí)候才會(huì)使用b,c進(jìn)行排序。也就是說(shuō)，b、c在全局是無(wú)序的，在局部卻是有序的。當(dāng)我們的條件中缺失聯(lián)合索引最左邊的字段時(shí)，MySQL在進(jìn)行查詢的時(shí)候，一般情況下，是不能夠使用到聯(lián)合索引了。

但是也有例外，像上面的這一條SQL語(yǔ)句，執(zhí)行的時(shí)候會(huì)利用聯(lián)合索引進(jìn)行全索引掃描，因?yàn)槲覀円樵兊淖侄卧诼?lián)合索引中都可以查詢到，然后將所有查詢到的結(jié)果使用where條件進(jìn)行篩選。

為什么會(huì)優(yōu)先走聯(lián)合索引？

因?yàn)槎?jí)索引樹(shù)的記錄東西很少，就只有「索引列+主鍵值」，而聚簇索引記錄的東西會(huì)更多，比如聚簇索引中的葉子節(jié)點(diǎn)則記錄了主鍵值、事務(wù) id、用于事務(wù)和 MVCC 的回滾指針以及所有的剩余列。MySQL的查詢是基于成本的，會(huì)優(yōu)先原則成本低的查詢方案。

如果我們向joint_index_test表中添加一個(gè)name字段，這時(shí)候，我們要查詢的所有字段就沒(méi)有辦法在聯(lián)合索引中全部找到了，MySQL會(huì)放棄聯(lián)合索引，改走全表掃描。

全索引掃描

添加一個(gè)name字段后，type從index->ALL

3. 索引跳躍掃描

我們將name字段刪除，表中還只保留 id、a、b、c 四個(gè)字段，并向表中生成數(shù)據(jù)。

我們向表中生成一千條數(shù)據(jù)，id自增，a對(duì)1到6進(jìn)行枚舉，b、c是int類型的隨機(jī)數(shù)。

我們?cè)俅螆?zhí)行

explain select * from joint_index_test where b = 3;

這條SQL語(yǔ)句，發(fā)現(xiàn)type列和Extra列中的內(nèi)容發(fā)生了變更。

type從index -> range ; Extra列從Using Index -> Using index for skip scan.

之所以發(fā)生了這樣的變化，是MySQL8.0.13后對(duì)最左原則失效的情況進(jìn)行了優(yōu)化。如果我們的聯(lián)合索引構(gòu)建的B+Tree中能夠找到所有查詢的列且where查詢條件沒(méi)有遵循最左匹配原則，MySQL會(huì)通過(guò)索引跳躍掃描進(jìn)行優(yōu)化處理。提前說(shuō)明，索引跳躍掃描并不是萬(wàn)能的，我們?cè)谶M(jìn)行SQL查詢的時(shí)候還是需要盡可能地遵循最左匹配原則。

接下來(lái)，我會(huì)根據(jù)MySQL官方文檔對(duì)索引跳躍掃描進(jìn)行解說(shuō)，感興趣的小伙伴也可以直接點(diǎn)擊文末鏈接，自行閱讀。

在MySQL8.0.13版本之前,執(zhí)行這一條SQL語(yǔ)句,會(huì)出現(xiàn) Using where,Using Index 使用索引掃描所有的數(shù)據(jù),之后再利用條件進(jìn)行過(guò)濾,其執(zhí)行type為index對(duì)全索引進(jìn)行掃描，性能僅次于ALL;

從MySQL 8.0.13版本開(kāi)始，mysql支持多范圍掃描;查詢的條件的每個(gè)不同前綴值執(zhí)行子范圍掃描。

例如會(huì)對(duì) select * from joint_index_test where b = 3 這條SQL語(yǔ)句通過(guò) distinct a 拆分成六條SQL語(yǔ)句，分別為：

explain select * from joint_index_test where a = 1 and b = 3;
explain select * from joint_index_test where a = 2 and b = 3；
explain select * from joint_index_test where a = 3 and b = 3;
explain select * from joint_index_test where a = 4 and b = 3;
explain select * from joint_index_test where a = 5 and b = 3;
explain select * from joint_index_test where a = 6 and b = 3;

讓拆分后的語(yǔ)句能夠遵循聯(lián)合索引的最左匹配原則進(jìn)行范圍查詢，之后對(duì)所有查詢到的值進(jìn)行合并，并作為整體返回。

值得一提的是，索引跳躍掃描，并非跳過(guò)索引,而是在缺失的前綴索引的不同值之間進(jìn)行跳躍；使用這種策略減少了訪問(wèn)的行數(shù)，因?yàn)镸ySQL直接跳過(guò)不符合的構(gòu)造范圍的行。

還是那一句話，聯(lián)合索引不是萬(wàn)能的，之中優(yōu)化是基于以下條件的：

• 只適用于單表查詢;
• 查詢語(yǔ)句中不能使用GROUP BY或DISTINCT;
• 只能對(duì)聯(lián)合索引中構(gòu)建的B+數(shù)包含的列進(jìn)行查詢;
• 缺少的前綴必須是常數(shù)，數(shù)字類型的字段
• 查詢條件必須適用連詞進(jìn)行連接，比如使用AND或者OR

以上還有一些條件，筆者暫時(shí)還沒(méi)有看懂，值得一提的是，在滿足上面的所有條件的情況下，索引跳躍掃描并不是一定發(fā)生的，因?yàn)閷?duì)缺失的前綴進(jìn)行組合是需要成本的。

mysql的查詢永遠(yuǎn)會(huì)選擇成本最低的方案，而索引跳躍掃描僅僅是其中的一種方案。我們可以將索引跳躍掃描看作是覆蓋索引條件查詢?nèi)笔熬Y的一種優(yōu)化方案。

官方鏈接：MySQL :: MySQL 8.0 Reference Manual :: 10.2.1.2 Range Optimization

總結(jié)

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