讨论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执行优化器来查看
执行优化器，顾名思义，优化语句的，准确来说是优化查询语句。其实就是在我们写的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）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底层原理