聯合索引的最左字首匹配原則

https://www.jianshu.com/p/b7911e0394b0

CREATE TABLE `user2` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL DEFAULT '',
  `password` varchar(20) NOT NULL DEFAULT '',
  `usertype` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`userid`),
  KEY `a_b_c_index` (`username`,`password`,`usertype`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

上表中有一個聯合索引，下面開始驗證最左匹配原則。
當存在username時會使用索引查詢：

explain select * from user2 where username = '1' and password = '1';

Paste_Image.png

 

當沒有username時，不會使用索引查詢：

explain select * from user2 where password = '1';

Paste_Image.png

當有username，但順序亂序時也可以使用索引：

explain select * from user2 where password = '1' and username = '1';

Paste_Image.png

 

在最左匹配原則中，有如下說明：

1. 最左字首匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。
2. =和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識別的形式