問題
最近，在 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 中有且只有一個聚集索引，它有三種情況：
- 若表存在主鍵，則主鍵索引就是聚集索引，
- 若不存在主鍵，則會把第一個非空的唯一索引作為聚集索引，
- 否則，就會隱式的定義一個 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+ 樹結構如下圖所示，
什么是回表查詢？
從上邊的索引存盤結構，我們可以看到，在主鍵索引樹上，通過主鍵就可以一次性查出來我們所需要的資料，速度非常的快，
因為主鍵和行記錄就存盤在一起，定位到了主鍵，也就定位到了所要找的記錄，當前行的所有欄位都在這（這也是為什么我們說，在創建表的時候，最好是創建一個主鍵，查詢時也盡量用主鍵來查詢），
對于普通索引，如例子中的 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 陳述句恰好符合了索引覆寫而已，真的是虛驚一場 ！！
若本文對你有用，歡迎關注我，給我點贊吧 ~
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/187921.html
標籤：Java
上一篇：萌新學習SpringMVC