最近在深入學(xué)習(xí)MySQL，在學(xué)習(xí)最左匹配原則的時候，遇到了一個有意思的事情。請聽我細(xì)細(xì)道來。

我的MySQL版本為8.0.32

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

準(zhǔn)備工作

先建表，SQL語句如下：

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)非常簡單，4個字段，兩個索引，主鍵索引id和聯(lián)合索引abc。暫時不向表中添加數(shù)據(jù)。

開始測試

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

1. 最左匹配原則

explain select * from joint_index_test where a = 3;

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

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

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

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

2. 覆蓋索引

explain select * from joint_index_test where b = 3;

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

但是也有例外，像上面的這一條SQL語句，執(zhí)行的時候會利用聯(lián)合索引進(jìn)行全索引掃描，因為我們要查詢的字段在聯(lián)合索引中都可以查詢到，然后將所有查詢到的結(jié)果使用where條件進(jìn)行篩選。

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

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

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

全索引掃描

添加一個name字段后，type從index->ALL

3. 索引跳躍掃描

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

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

我們再次執(zhí)行

explain select * from joint_index_test where b = 3;

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

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

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

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

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

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

例如會對 select * from joint_index_test where b = 3 這條SQL語句通過 distinct a 拆分成六條SQL語句，分別為：

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;

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

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

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

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

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

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

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

總結(jié)

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

最新評論