MySQL索引命中与失效

时间：2023-01-16 09:51:56

目录

讨论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"

结果：

MySQL索引命中与失效

 

第二种情况：去掉大哥，看看索引是否命中；

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

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

MySQL索引命中与失效

 

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

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

删除组合索引

drop index idx_username_password_user_rank on `user`

创建唯一索引

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

查看索引

MySQL索引命中与失效

 

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

MySQL索引命中与失效

 

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

MySQL索引命中与失效

 

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

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

MySQL索引命中与失效

 

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

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

MySQL索引命中与失效

 

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

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

MySQL索引命中与失效
 

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

and情况：

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

MySQL索引命中与失效
 

or情况：

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

MySQL索引命中与失效

我们给user_rank加上索引

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

MySQL索引命中与失效
再次查询：

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

MySQL索引命中与失效

 

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

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

or 

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

MySQL索引命中与失效

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

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

MySQL索引命中与失效

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

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"

MySQL索引命中与失效

 

如果是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

MySQL索引命中与失效

总结隐式转换：

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

 
 

总结

（1）MySQL中索引失效的情况

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

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

标签：

相关文章