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索引xy索引因为我们建立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楼这就是最左匹配原则第一个根本就没有匹配到后面的就根本不用看了。

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

建立一个组合索引

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

// 命中索引跟顺序无关explain SELECT * from `user` where username =  "liuxiangcheng" and password = "515239" and user_rank = 1explain 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索引命中与失效