MySQL索引命中与失效

讨论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`)

这里有一个组合索引的最左匹配原则具体看我另一篇博客MySQL最左匹配原则

 
 

查看MySQL中索引是否命中可以使用explainh执行优化器来查看

MySQL执行优化器

执行优化器顾名思义优化语句的准确来说是优化查询语句。其实就是在我们写的select语句前加一个Explain关键字。
 
 

索引的命中与失效情况

第一种情况针对联合索引是否遵循最左匹配原则

建立一个组合索引

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

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

 

第三种情况在索引列上做了函数操作会导致索引失效而导致全表扫描

我们先把那个联合索引删除掉然后在username这一列上建立一个唯一索引

删除组合索引

drop index idx_username_password_user_rank on `user`

创建唯一索引

alter table `user` ADD UNIQUE key (`username`)

查看索引

 

explain SELECT * from `user` where  username= 'user110819'

 

explain SELECT * from `user` where  concat(username,'')= 'user110819'

 

第四种情况模糊查询前缀是以%开头的索引失效

explain SELECT * from `user` where   username like '%user11081'

 

第五种情况模糊查询中后缀是以%可以命中索引

explain SELECT * from `user` where   username like 'user11081%'

 

第六种情况使用is not null 会导致索引失效

explain SELECT * from `user` where   username is not null

 

第六种情况使用and时其中有一个条件查询带有索引而另一个不带索引不会导致索引失效。而使用or时如果条件查询中其中一个不带索引导致索引失效必须全部带有索引。

and情况

explain SELECT * from `user` where   username = "liuxiangcheng" and password = "515239"

 

or情况

explain SELECT * from `user` where   username = "liuxiangcheng" or user_rank = 1

我们给user_rank加上索引

alter table `user` ADD  index (`user_rank`)

再次查询

explain SELECT * from `user` where   username = "liuxiangcheng" or user_rank = 1

 

第七种情况使用不等于(!= 或者<>)的时候会导致索引失效

explain SELECT * from `user` where   user_rank != 1

or 

explain SELECT * from `user` where   user_rank <> 1

 
第八种情况使用范围查询之后索引失效

explain SELECT * from `user` where   user_rank >(<,>=,<=) 1

 
第八种情况隐式转换可能会导致我们的索引失效

varchar类型如果用int类型来查询索引失效
数据库user表中我们的password是varchar类型
如果我们在条件查询中使用整形来替代那么这个时候索引就会失效where varchar = int 索引失效

// password is varchar type

explain SELECT * from `user` where password = 515239

explain SELECT * from `user` where password = '515239'

explain SELECT * from `user` where password = "515239"

 

如果是int类型我们使用varchar来替代索引命中

// user_rank is int type

explain SELECT * from `user` where user_rank = "1"

explain SELECT * from `user` where user_rank = '1'

explain SELECT * from `user` where user_rank = 1

总结隐式转换

1. 当操作符左右两边的数据类型不一致时会发生隐式转换。
2. 当 where 查询操作符左边为数值类型时发生了隐式转换但是索引会命中对查询效率影响不大但还是不推荐这么做。
3. 当 where 查询操作符左边为字符类型时发生了隐式转换这样会导致索引失效造成全表扫描。
4. 字符串转换为数值类型时非数字开头的字符串会转化为0以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

 
 

总结

1MySQL中索引失效的情况

1、组合索引中不遵循最左匹配原则带头大哥不在导致索引失效全表扫描。
2、在索引列上做了函数操作导致索引失效全表扫描。
3、模糊查询前缀是以%开头的导致索引失效全表扫描。
4、使用is not null 会导致索引失效。
5、使用or时如果条件查询中其中一个不带索引导致索引失效全表扫描。
6、使用不等于(!= 或者<>)的时候会导致索引失效。
7、使用范围查询>、<、>=、<=之后索引失效。
8、隐式转换可能会导致我们的索引失效。

2查看MySQL中索引是否命中可以使用explainh执行优化器来查看。