mysql 索引 最左前綴原則

來自專欄 MySQL Redis 資料庫

本文重在講清啥是mysql中索引的最左前綴原則，並不在索引，索引這東西太大了，有點消化不良，不敢講。

首先，我是參考 SQL 常用優化手段總結 - 索引的應用 這位大哥的sql語句作為基礎的，但是覺得他的講解方式讓我感到有些迷茫，覺得有點控制變數法用得不對勁。所以去多方學習，在此記下筆記。

首先是，這個最左前綴原則是發生在複合索引上的，只有複合索引才會有所謂的左和右之分。

這下面是我參考前面的鏈接修改的創建表的方式以及插入裡面的內容，我們關注在它的索引上，一個主鍵索引id，一個複合索引，左邊是name，右邊是address。

CREATE TABLE `test_table3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `birthday` datetime DEFAULT NULL, `address` varchar(45) DEFAULT NULL, `phone` varchar(45) DEFAULT NULL, `note` varchar(45) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `LeftMostPreFix` (`name`,`address`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=145 DEFAULT CHARSET=utf8 insert into test_table3 values(1,"張","1997-07-08 06:00:00","東京","13978419970","its me2",22);insert into test_table3 values(2,"張","1997-07-08 06:00:00","河南","13978419970","its me2",26);insert into test_table3 values(3,"劉","1997-07-08 06:00:00","東京","13978419970","its me2",29);

然後我們來看我的測試：

測試一：

explain SELECT * FROM test_table3 where address = 東京

返回為：

這裡的type=all，表示全表掃描，mysql遍歷全表來找到匹配的行，然後key之類的都為null，表示表示實際並沒有使用到索引。

測試二：

explain SELECT * FROM test_table3 where name = 張;

返回為：

這裡的type為ref，表示使用非唯一索引掃描或唯一索引掃描的前綴掃描（但這裡我們並沒有建立唯一索引，所以是非唯一索引掃描，唯一索引是很珍貴的資源，和主鍵索引有得拼的）。然後這裡的possible_key和key都是前面定義的那個複合索引，也就是說這次的查詢用到的是上面的複合索引。

然後，我們來分析為啥會這樣？

沒錯，原因就是在於mysql這裡的最左前綴的原則，在建立複合索引時你放在最左邊的欄位就能享受索引的支持，即使沒有單獨為你建立索引，而不是最左邊的就不能單獨享受這個索引的支持了。如果覺得不相信，那麼你可以再單獨建立一個表，在建立複合索引時把那兩個欄位的位置互換，然後再運行那兩個查詢的測試。

然後就是沒有索引就代表沒有捷徑，效率就會低下。

然後左前綴原則還體現在模糊查詢上，也就是上面鏈接里說的。這裡再給出差不多的sql：

CREATE TABLE `test_table5` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `birthday` datetime DEFAULT NULL, `address` varchar(45) DEFAULT NULL, `phone` varchar(45) DEFAULT NULL, `note` varchar(45) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `ADDRESS` (`address`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=145 DEFAULT CHARSET=utf8

插入語句和前面的一樣，然後是測試語句，和前面的鏈接一樣：

explain SELECT * FROM test_table5 where address like %東 這個什麼索引都沒用

explain SELECT * FROM test_table5 where address like 東% 這個使用了address索引。

所以，這就是左前綴索引的兩個方面的體現了。

歡迎交流討論。