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，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

 
 

总结

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

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

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

 
 

拓展

为什么我们要尽量避免使用

select * 

这得从我们的主键索引和非主键索引说，比如我们使用name来作为唯一索引，而唯一索引又是非聚簇索引，非聚簇索引中叶子节点数据区挂载的是主键id，那么我们使用select * from table where name = liu。这个时候，我们就会先从name索引树中查找，找到之后再到主键索引树中查找主键值对应的记录（这个过程称为回表），再进行返回。而如果我们使用select id ,name from table where name = liu。在name索引树的数据区中找到name = liu的数据时，一同把 id、name 返回，不用遍历主键索引树，效率高。

理清楚思路之后再次总结：其实这个的话，其实是命中了覆盖索引，而在了解覆盖索引之前，我们还是先需要知道聚簇索引和非聚簇索引的区别。覆盖索引，我们在通过聚簇索引/稀疏索引在B+Tree的叶子节点数据区中找到我们想找到的内容并返回（如聚簇索引/稀疏索引username,主键索引id）,而不用产生回表操作，拿到主键值id之后再去遍历一遍主键索引树，这就是覆盖索引。覆盖索引查询一颗树，总比查询两颗树快，这也是为什么覆盖索引能够加快我们查询效率的原因，而这也是为什么我们在写SQL语句的时候需要写什么就写什么，而不要使用select *,我们举给例子：
我们select id , username from talbe where username = “xxx”，这个时候通过非聚簇索引在B+Tree叶子节点数据区获取到辅助我们找到行记录的主键值id之后就返回了，速度快。

 
具体B+Tree原理见我博客：MySQL索引和B+Tree底层原理