MySQL最左匹配原则

说明

 

说到最左匹配原则，我们还得先从组合索引说起。

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(5) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
  `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户密码',
  `create_essay` int(5) NOT NULL COMMENT '原创文章',
  `user_visited` int(10) NOT NULL COMMENT '被访问量',
  `user_rank` int(5) NOT NULL COMMENT '用户排名',
  `perms` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `nickname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户昵称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 116856 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

// 创建组合索引
ALTER TABLE `user` ADD INDEX idx_username_password_user_rank (`username`,`password`,`user_rank`)

我们在创建索引的时候，如果我们只选一列，那就叫单列索引，而如果我们选择多列，那么就是组合索引。
在上表中我们创建了一个组合索引：idx_username_password_user_rank(username,password,user_rank)

一般我们组合索引字段数量不建议超过5个，而我们需要理解组合索引的最左匹配原则，我们就可以避免重复创建索引。比如我们建立了（x,y,z）索引,我们就不需要建立（x）索引，（x，y）索引，因为我们建立（x,y,z）索引就相当于建立了（x）索引,（x,y）组合索引，（x,y,z）组合索引。
 
 

最左匹配原则

 

什么是最左匹配原则？

然后我们在建立索引的时候，还需要遵循一个规范，就是最左匹配原则，也就是带头大哥在不在的问题。在写查询条件的时候，我们一定要遵循最左匹配原则，只要大哥不在，索引就会失效了。

比如：我们创建了一个组合索引叫 idx_username_password_user_rank (username,password,user_rank)
如果我们在查询过程中，我们这样写 select * from table where password= “xxx” and user_rank = 1
我们这个查询语句是不会命中索引的，因为带头大哥不在，所以索引失效。

也就是我们不能使用空中楼阁，我们把我们的 username当成1楼，password当成2楼，user_rank当成3楼，1楼都不在，我们怎么上2、3楼，这就是最左匹配原则，第一个根本就没有匹配到，后面的就根本不用看了。
 
 

为什么会有最左匹配原则？

要想知道为什么会在组合索引中有最左匹配原则，我们得先理解索引的本质。
我们知道索引的本质是一颗B+Tree，所以组合索引的本质也是一颗B+Tree，不同的是组合索引的键值的数量不是1，而是>=2。又因为构建一颗B+Tree只能根据一个值来确定索引关系，所以MySQL根据组合索引的最左字段来构建B+Tree。

我们来举一个例子。
我们创建一个（a,b）的组合索引，这个组合索引会创建两颗索引树（a）,（a,b），而（a，b）的索引树就是下面这个样子的。

我们可以看到a的值是天然有序的1、 1、 2、 2、 3、 3，而b的值是没有顺序的1、 2、 1、 4、 1、 2。但是我们在a值一定的情况下，b的值又是顺序排列的，但是这种顺序排列是相对于a来说的。

所以最左匹配原则是因为 MySQL创建组合索引树的规则是首先对组合索引最左边第一个字段进行排序，然后在第一个字段排序的基础上，再对第二个字段进行排序。所以b=2这种查询条件用不到这两颗联合索引树。

 
 

实例说明

针对联合索引，是否遵循最左匹配原则；

建立一个组合索引

idx_username_password_user_rank(`username`,`password`,`user_rank`)

// 命中索引跟顺序无关
explain SELECT * from `user` where username =  "liuxiangcheng" and password = "515239" and user_rank = 1
explain SELECT * from `user` where user_rank = 1 and username =  "liuxiangcheng" and password = "515239"  
explain SELECT * from `user` where user_rank = 1 and password = "515239" and username =  "liuxiangcheng"

结果：

 

去掉大哥，看看索引是否命中；

// 去掉大哥
explain SELECT * from `user` where   password = "515239" and user_rank = 1

去掉大哥之后，索引失效，全表扫描。

 
 

MySQL索引命中与失效

查看MySQL索引命中与失效具体见我另一篇博客：MySQL索引命中与失效