我去,為什么最左前綴原則失效了?

問題

最近,在 mysql 測試最左前綴原則,發現了匪夷所思的事情。根據最左前綴原則,本來應該索引失效,走全表掃描的,但是,卻發現可以正常走索引。

表結構如下( 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_bin

INSERT INTO user(`id`, `name`, `age`, `address`) VALUES (1, 'zs', 12, 'beijing');

表中總共有四個字段。 id 為主鍵,還有一個由 name,age,address 組成的聯合索引。 存儲引擎為 InnoDB,并插入一條測試數據。

根據最左前綴原則,以下 sql ,肯定會使索引失效的。(若不懂最左前綴原則,稍后會講~)

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

然而結果,卻是讓人大失所望。如下,通過查看執行計劃,發現它走索引了。

這就讓我非常疑惑了,難不成最左前綴原則是錯的?又或者,是 Mysql 隨著版本升級,已經智能到不需要 care 最左前綴原則了嗎?

目錄

帶著這個疑問,我們一探究竟。在這之前需要了解一些前置知識。本篇文章目錄如下:

  • 什么是聚集索引和非聚集索引?
  • 什么是回表查詢?
  • 什么是索引覆蓋?
  • 最左前綴原則
  • 問題解惑

正文

由于,現在基本上都是用的 InnoDB引擎,所以下面都以 InnoDB為例,MyISAM 順帶提一下。

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

我們知道 Mysql 底層是用 B+ 樹來存儲索引的,且數據都存在葉子節點。對于 InnoDB 來說,它的主鍵索引和行記錄是存儲在一起的,因此叫做聚集索引(clustered index)。

PS:MyISAM 的行記錄是單獨存儲的,不和索引在一起,因此 MyISAM也就沒有聚集索引。

除了聚集索引,其它索引都叫做非聚集索引(secondary index)。包括普通索引,唯一索引等。

另外需要注意,在 InnoDB 中有且只有一個聚集索引。它有三種情況:

  1. 若表存在主鍵,則主鍵索引就是聚集索引。
  2. 若不存在主鍵,則會把第一個非空的唯一索引作為聚集索引。
  3. 否則,就會隱式的定義一個 rowid 作為聚集索引。

為了方便理解,下邊以 InnoDB 的主鍵索引和普通索引為例,看下它們的存儲結構。

創建一張表,結構如下,并添加幾條記錄(張三,李四,王五,孫七):

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_bin

insert 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 中,主鍵索引的葉子節點存儲的是主鍵和行記錄,而普通索引的葉子節點存儲的是主鍵(對于 MyISAM來說主鍵索引的葉子節點存儲的是主鍵和對應行記錄的指針,普通索引的葉子節點存儲的是當前索引列和對應行記錄的指針)。

因此,id 為聚集索引,name 為非聚集索引。它們對應的 B+ 樹結構如下圖所示,

clustered index, secondary index

什么是回表查詢?

從上邊的索引存儲結構,我們可以看到,在主鍵索引樹上,通過主鍵就可以一次性查出來我們所需要的數據,速度非常的快。

因為主鍵和行記錄就存儲在一起,定位到了主鍵,也就定位到了所要找的記錄,當前行的所有字段都在這(這也是為什么我們說,在創建表的時候,最好是創建一個主鍵,查詢時也盡量用主鍵來查詢)。

對于普通索引,如例子中的 name,則需要根據 name 的索引樹(非聚集索引)找到葉子節點對應的主鍵,然后再通過主鍵去主鍵索引樹查詢一遍,才可以得到要找的記錄。這就叫 回表查詢

以如下 sql 為例。

select * from student where name='zs';

它需要查詢兩遍索引樹。

  • 通過非聚集索引定位到主鍵 id=1。
  • 通過聚集索引定位到主鍵id為1,對應的行記錄。

它的查詢過程圖如下,

回表查詢

什么是索引覆蓋?

對于上邊的回表查詢來說,無疑會降低查詢效率。那么,有的童鞋就會問了,有沒有什么辦法,讓它不回表呢?

答案當然是有了,就是索引覆蓋

何為索引覆蓋,就是在用這個索引查詢時,使它的索引樹,查詢到的葉子節點上的數據可以覆蓋到你查詢的所有字段,這樣就可以避免回表。

還是以上邊的表為例,現在 zs 對應的索引樹上邊,只有它本身和主鍵的數據,并不能覆蓋到 age 字段。那么,我們就可以創建聯合索引,如 KEY(name,age)。并且,查詢的時候,顯式的寫出聯合索引對應的字段(name和age)。

創建聯合索引如下,

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

查詢語句修改如下,

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

這樣,當查詢索引樹的時候,就不用回表,可以一次性查出所有的字段。對應的索引樹結構如下:

聯合索引

PS:圖中,聯合索引中的字段(name,age)都應該出現在索引樹上的,這里為了畫圖方便,且因數據量太小,沒有畫出來。只表現出了:葉子節點存儲了所有的聯合索引字段。

最左前綴原則

最左前綴原則,顧名思義,就是最左邊的優先。指的是聯合索引中,優先走最左邊列的索引。如上表中,name和age的聯合索引,相當于創建了 name 單列索引和 (name,age)聯合索引。在查詢時,where 條件中若有 name 字段,則會走這個聯合索引。

對于多個字段的聯合索引,也同理。如 index(a,b,c) 聯合索引,則相當于創建了 a 單列索引,(a,b)聯合索引,和(a,b,c)聯合索引。

為了驗證最左前綴原則,我們需要對原來的表結構進行改造。再添加兩個字段(address,sex),然后創建三列的聯合索引(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);

查看表數據如下,

表數據

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

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';

然后查看它們的執行計劃如下,

可以看到,最終都走了索引。現在,修改 sql 如下,如何?

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

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

PS:拓展思考,若 sql 改為如下,會導致全表掃描嗎?(自己動手嘗試哦)

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

問題解惑

到現在為止,我們發現最左前綴原則一切正常。然后回到最開始拋出的問題,為什么這個原則就不生效了呢?(創建的聯合索引,還有 sql 語句都是一樣的啊!)

別著急,還記得前面我們說的索引覆蓋嗎? 這次,我們利用索引覆蓋原理,只查詢特定的字段(只有主鍵和聯合索引字段)。

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

再查看執行計劃,

問題來了,此時違反了最左前綴原則,但是符合覆蓋索引,為什么就走索引了呢?

我們對比一下,若用最左列,和不用最左列,它們的執行計劃有何不同。

會發現,若不符合最左前綴原則,則 type為 index,若符合,則 type 為 ref。

index 代表的是會對整個索引樹進行掃描,如例子中的,最右列 address,就會導致掃描整個索引樹。

ref 代表 mysql 會根據特定的算法查找索引,這樣的效率比 index 全掃描要高一些。但是,它對索引結構有一定的要求,索引字段必須是有序的。而聯合索引就符合這樣的要求!

聯合索引內部就是有序的,我們可以把它理解為類似于 order by name,age,address 這樣的排序規則。會先根據 name 排序,若name 相同,再根據 age 排序,依次類推。

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

退而求其次,若不符合最左前綴原則,但是符合覆蓋索引,就可以掃描整個索引樹,從而找到覆蓋索引對應的列(避免了回表)。

若不符合最左前綴原則,且也不符合覆蓋索引(形同 select *),則需要掃描整個索引樹。完成之后,還需要再回表,查詢對應的行記錄。

此時,查詢優化器,就會認為,這樣的兩次查詢索引樹,還不如全表掃描來的快(因為聯合索引此時不符合最左前綴原則,要比普通單列索引查詢慢的多)。因此,此時就會走全表掃描。

有童鞋就要問了,你在這廢話了一大堆,還是沒有解答最初的疑惑啊 !!!

不然,其實上邊的分析就已經解答了。我們仔細觀察最開始的 user 表,和此時的 student 表有什么不同。

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

所以,在 user 中,我們最初的 sql 語句就等同于 ,

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

這個結構就是我們上邊討論的情況:不符合最左前綴原則,但是符合索引覆蓋。這種情況,是會走索引的。

結論

那么,結論也就出來了。并不是最左前綴原則失效了,也不是 Mysql 變的更智能了,而是此時創建的表結構,以及查詢的 sql 語句恰好符合了索引覆蓋而已。真的是虛驚一場 !!

若本文對你有用,歡迎關注我,給我點贊吧 ~

posted @ 2020-05-20 09:20  煙雨星空  閱讀(509)  評論(3編輯  收藏
最新chease0ldman老人|无码亚洲人妻下载|大香蕉在线看好吊妞视频这里有精品www|亚洲色情综合网