問(wèn)題

最近，在 mysql 測(cè)試最左前綴原則，發(fā)現(xiàn)了匪夷所思的事情。根據(jù)最左前綴原則，本來(lái)應(yīng)該索引失效，走全表掃描的，但是，卻發(fā)現(xiàn)可以正常走索引。

表結(jié)構(gòu)如下（ Mysql 版本 5.7.22）：

CREATE?TABLE?`user`?(`id`?int(11)?NOT?NULL?AUTO_INCREMENT,`name`?varchar(32)?COLLATE?utf8mb4_bin?DEFAULT?NULL,`age`?int(11)?DEFAULT?NULL,`address`?varchar(128)?COLLATE?utf8mb4_bin?DEFAULT?NULL,PRIMARY?KEY?(`id`),KEY?`idx_user`?(`name`,`age`,`address`) )?ENGINE=InnoDB?AUTO_INCREMENT=4?DEFAULT?CHARSET=utf8mb4?COLLATE=utf8mb4_binINSERT?INTO?user(`id`,?`name`,?`age`,?`address`)?VALUES?(1,?'zs',?12,?'beijing');

表中總共有四個(gè)字段。id 為主鍵，還有一個(gè)由 name，age，address 組成的聯(lián)合索引。存儲(chǔ)引擎為 InnoDB，并插入一條測(cè)試數(shù)據(jù)。

根據(jù)最左前綴原則，以下 sql ，肯定會(huì)使索引失效的。（若不懂最左前綴原則，稍后會(huì)講~）

EXPLAIN?select?*?from?user?where?address='beijing';

然而結(jié)果，卻是讓人大失所望。如下，通過(guò)查看執(zhí)行計(jì)劃，發(fā)現(xiàn)它走索引了。

這就讓我非常疑惑了，難不成最左前綴原則是錯(cuò)的？又或者，是 Mysql 隨著版本升級(jí)，已經(jīng)智能到不需要 care 最左前綴原則了嗎？

目錄

帶著這個(gè)疑問(wèn)，我們一探究竟。在這之前需要了解一些前置知識(shí)。本篇文章目錄如下：

• 什么是聚集索引和非聚集索引？

• 什么是回表查詢？

• 什么是索引覆蓋？

• 最左前綴原則

• 問(wèn)題解惑

正文

由于，現(xiàn)在基本上都是用的 InnoDB引擎，所以下面都以 InnoDB為例，MyISAM 順帶提一下。

什么是聚集索引和非聚集索引？

我們知道 Mysql 底層是用 B+ 樹(shù)來(lái)存儲(chǔ)索引的，且數(shù)據(jù)都存在葉子節(jié)點(diǎn)。對(duì)于 InnoDB 來(lái)說(shuō)，它的主鍵索引和行記錄是存儲(chǔ)在一起的，因此叫做聚集索引（clustered index)。

PS：MyISAM 的行記錄是單獨(dú)存儲(chǔ)的，不和索引在一起，因此 MyISAM也就沒(méi)有聚集索引。

除了聚集索引，其它索引都叫做非聚集索引（secondary index）。包括普通索引，唯一索引等。

另外需要注意，在 InnoDB 中有且只有一個(gè)聚集索引。它有三種情況：

若表存在主鍵，則主鍵索引就是聚集索引。

若不存在主鍵，則會(huì)把第一個(gè)非空的唯一索引作為聚集索引。

否則，就會(huì)隱式的定義一個(gè) rowid 作為聚集索引。

為了方便理解，下邊以 InnoDB 的主鍵索引和普通索引為例，看下它們的存儲(chǔ)結(jié)構(gòu)。

創(chuàng)建一張表，結(jié)構(gòu)如下，并添加幾條記錄（張三，李四，王五，孫七）：

CREATE TABLE `student` (`id` int(11) NOT NULL,`name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,`age` int(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_stu` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bininsert into student(id,name,age) values(1,'zs',12); insert into student(id,name,age) values(5,'ls',14); insert into student(id,name,age) values(9,'ww',12); insert into student(id,name,age) values(11,'sq',13);

在 InnoDB 中，主鍵索引的葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵和行記錄，而普通索引的葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵（對(duì)于 MyISAM來(lái)說(shuō)主鍵索引的葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵和對(duì)應(yīng)行記錄的指針，普通索引的葉子節(jié)點(diǎn)存儲(chǔ)的是當(dāng)前索引列和對(duì)應(yīng)行記錄的指針）。

因此，id 為聚集索引，name 為非聚集索引。它們對(duì)應(yīng)的 B+ 樹(shù)結(jié)構(gòu)如下圖所示，

clustered index, secondary index

?

什么是回表查詢？

從上邊的索引存儲(chǔ)結(jié)構(gòu)，我們可以看到，在主鍵索引樹(shù)上，通過(guò)主鍵就可以一次性查出來(lái)我們所需要的數(shù)據(jù)，速度非常的快。

因?yàn)橹麈I和行記錄就存儲(chǔ)在一起，定位到了主鍵，也就定位到了所要找的記錄，當(dāng)前行的所有字段都在這（這也是為什么我們說(shuō)，在創(chuàng)建表的時(shí)候，最好是創(chuàng)建一個(gè)主鍵，查詢時(shí)也盡量用主鍵來(lái)查詢）。

對(duì)于普通索引，如例子中的 name，則需要根據(jù) name 的索引樹(shù)（非聚集索引）找到葉子節(jié)點(diǎn)對(duì)應(yīng)的主鍵，然后再通過(guò)主鍵去主鍵索引樹(shù)查詢一遍，才可以得到要找的記錄。這就叫?回表查詢。

以如下 sql 為例。

select?*?from?student?where?name='zs';

它需要查詢兩遍索引樹(shù)。

• 通過(guò)非聚集索引定位到主鍵 id=1。

• 通過(guò)聚集索引定位到主鍵id為1，對(duì)應(yīng)的行記錄。

它的查詢過(guò)程圖如下，

回表查詢

?

什么是索引覆蓋？

對(duì)于上邊的回表查詢來(lái)說(shuō)，無(wú)疑會(huì)降低查詢效率。那么，有的童鞋就會(huì)問(wèn)了，有沒(méi)有什么辦法，讓它不回表呢？

答案當(dāng)然是有了，就是索引覆蓋。

何為索引覆蓋，就是在用這個(gè)索引查詢時(shí)，使它的索引樹(shù)，查詢到的葉子節(jié)點(diǎn)上的數(shù)據(jù)可以覆蓋到你查詢的所有字段，這樣就可以避免回表。

還是以上邊的表為例，現(xiàn)在 zs 對(duì)應(yīng)的索引樹(shù)上邊，只有它本身和主鍵的數(shù)據(jù)，并不能覆蓋到 age 字段。那么，我們就可以創(chuàng)建聯(lián)合索引，如 KEY(name,age)。并且，查詢的時(shí)候，顯式的寫(xiě)出聯(lián)合索引對(duì)應(yīng)的字段（name和age）。

創(chuàng)建聯(lián)合索引如下，

KEY?`idx_stu`?(`name`,`age`)

查詢語(yǔ)句修改如下，

--?覆蓋聯(lián)合索引中的字段 select?id,name,age?from?student?where?name='zs'?and?age=12;?

這樣，當(dāng)查詢索引樹(shù)的時(shí)候，就不用回表，可以一次性查出所有的字段。對(duì)應(yīng)的索引樹(shù)結(jié)構(gòu)如下：

聯(lián)合索引

PS：圖中，聯(lián)合索引中的字段（name，age）都應(yīng)該出現(xiàn)在索引樹(shù)上的，這里為了畫(huà)圖方便，且因數(shù)據(jù)量太小，沒(méi)有畫(huà)出來(lái)。只表現(xiàn)出了：葉子節(jié)點(diǎn)存儲(chǔ)了所有的聯(lián)合索引字段。

最左前綴原則

最左前綴原則，顧名思義，就是最左邊的優(yōu)先。指的是聯(lián)合索引中，優(yōu)先走最左邊列的索引。如上表中，name和age的聯(lián)合索引，相當(dāng)于創(chuàng)建了 name 單列索引和 (name,age)聯(lián)合索引。在查詢時(shí)，where 條件中若有 name 字段，則會(huì)走這個(gè)聯(lián)合索引。

對(duì)于多個(gè)字段的聯(lián)合索引，也同理。如 index(a,b,c) 聯(lián)合索引，則相當(dāng)于創(chuàng)建了 a 單列索引，(a,b)聯(lián)合索引，和(a,b,c)聯(lián)合索引。

為了驗(yàn)證最左前綴原則，我們需要對(duì)原來(lái)的表結(jié)構(gòu)進(jìn)行改造。再添加兩個(gè)字段(address,sex)，然后創(chuàng)建三列的聯(lián)合索引(name,age,address)。

drop?table?student; CREATE?TABLE?`student`?(`id`?int(11)?NOT?NULL,`name`?varchar(255)?COLLATE?utf8mb4_bin?DEFAULT?NULL,`age`?int(11)?DEFAULT?NULL,`address`?varchar(255)?COLLATE?utf8mb4_bin?DEFAULT?NULL,`sex`?int(1)?DEFAULT?NULL,PRIMARY?KEY?(`id`),KEY?`idx_stu`?(`name`,`age`,`address`) )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb4?COLLATE=utf8mb4_bin;insert?into?student(id,name,age,address,sex)?values(1,'zs',12,'beijing',1); insert?into?student(id,name,age,address,sex)?values(5,'ls',14,'tianjin',0); insert?into?student(id,name,age,address,sex)?values(9,'ww',12,'shanghai',1); insert?into?student(id,name,age,address,sex)?values(11,'sq',13,'hebei',1);

查看表數(shù)據(jù)如下，

表數(shù)據(jù)

分別用三種方式，使之符合最左前綴原則。

explain?select?*?from?student?where?name='zs'; explain?select?*?from?student?where?name='zs'?and?age=12; explain?select?*?from?student?where?name='zs'?and?age=12?and?address='beijing';

然后查看它們的執(zhí)行計(jì)劃如下，

可以看到，最終都走了索引。現(xiàn)在，修改 sql 如下，如何？

explain?select?*?from?student?where?address='beijing';

如我們所料，這不符合最左前綴原則，因此索引失效，走了全表掃描。

PS：拓展思考，若 sql 改為如下，會(huì)導(dǎo)致全表掃描嗎？（自己動(dòng)手嘗試哦)

explain?select?*?from?student?where?name='zs'?and?address='beijing';

問(wèn)題解惑

到現(xiàn)在為止，我們發(fā)現(xiàn)最左前綴原則一切正常。然后回到最開(kāi)始拋出的問(wèn)題，為什么這個(gè)原則就不生效了呢？（創(chuàng)建的聯(lián)合索引，還有 sql 語(yǔ)句都是一樣的啊！）

別著急，還記得前面我們說(shuō)的索引覆蓋嗎？這次，我們利用索引覆蓋原理，只查詢特定的字段（只有主鍵和聯(lián)合索引字段）。

explain?select?id,name,age,address?from?student?where?address='beijing';

再查看執(zhí)行計(jì)劃，

問(wèn)題來(lái)了，此時(shí)違反了最左前綴原則，但是符合覆蓋索引，為什么就走索引了呢？

我們對(duì)比一下，若用最左列，和不用最左列，它們的執(zhí)行計(jì)劃有何不同。

會(huì)發(fā)現(xiàn)，若不符合最左前綴原則，則 type為 index，若符合，則 type 為 ref。

index 代表的是會(huì)對(duì)整個(gè)索引樹(shù)進(jìn)行掃描，如例子中的，最右列 address，就會(huì)導(dǎo)致掃描整個(gè)索引樹(shù)。

ref 代表 mysql 會(huì)根據(jù)特定的算法查找索引，這樣的效率比 index 全掃描要高一些。但是，它對(duì)索引結(jié)構(gòu)有一定的要求，索引字段必須是有序的。而聯(lián)合索引就符合這樣的要求！

聯(lián)合索引內(nèi)部就是有序的，我們可以把它理解為類似于 order by name,age,address 這樣的排序規(guī)則。會(huì)先根據(jù) name 排序，若name 相同，再根據(jù) age 排序，依次類推。

所以，這也解釋了，為什么我們要遵守最左前綴原則。當(dāng)最左列有序時(shí)，才可以保證右邊的索引列有序。

退而求其次，若不符合最左前綴原則，但是符合覆蓋索引，就可以掃描整個(gè)索引樹(shù)，從而找到覆蓋索引對(duì)應(yīng)的列（避免了回表）。

若不符合最左前綴原則，且也不符合覆蓋索引（形同 select *），則需要掃描整個(gè)索引樹(shù)。完成之后，還需要再回表，查詢對(duì)應(yīng)的行記錄。

此時(shí)，查詢優(yōu)化器，就會(huì)認(rèn)為，這樣的兩次查詢索引樹(shù)，還不如全表掃描來(lái)的快(因?yàn)槁?lián)合索引此時(shí)不符合最左前綴原則，要比普通單列索引查詢慢的多)。因此，此時(shí)就會(huì)走全表掃描。

有童鞋就要問(wèn)了，你在這廢話了一大堆，還是沒(méi)有解答最初的疑惑啊 ！！！

不然，其實(shí)上邊的分析就已經(jīng)解答了。我們仔細(xì)觀察最開(kāi)始的 user 表，和此時(shí)的 student 表有什么不同。

user 表中，和 student 表相比，少了 sex 字段。但是，它們所建立的聯(lián)合索引卻是一樣的 KEY(name,age,address)。

所以，在 user 中，我們最初的 sql 語(yǔ)句就等同于 ,

--?最初的sql EXPLAIN?select?*?from?user?where?address='beijing'; --?等同于 EXPLAIN?select?id,name,age,address?from?user?where?address='beijing';

這個(gè)結(jié)構(gòu)就是我們上邊討論的情況：不符合最左前綴原則，但是符合索引覆蓋。這種情況，是會(huì)走索引的。

結(jié)論

那么，結(jié)論也就出來(lái)了。并不是最左前綴原則失效了，也不是 Mysql 變的更智能了，而是此時(shí)創(chuàng)建的表結(jié)構(gòu)，以及查詢的 sql 語(yǔ)句恰好符合了索引覆蓋而已。真的是虛驚一場(chǎng) ！！

若本文對(duì)你有用，歡迎在贊在看哦?~

總結(jié)

以上是生活随笔為你收集整理的我去，为什么最左前缀原则失效了？的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。