具体分析，什么情况下会导致MYSQL索引失效？

1、简介

导致MYSQL索引失效的原因有多种场景，但是为什么他们会导致索引失效呢，或者说我们怎么判断索引是否失效了。为了验证MYSQL中哪些情况下会导致索引失效，我们可以借助MYSQL 的执行计划（explain）来分析索引失效的具体场景。

• explain的详情介绍

个人认为会导致索引失效的情况有四种

1. 错误模糊查询导致索引失效
2. 非最左匹配导致索引失效
3. 列运算导致索引失效
4. 使用函数导致索引失效

2、测试数据准备

-- user表如果存在，则删除
DROP TABLE IF EXISTS `user`;
-- 创建user表
CREATE TABLE `user`(
	id int PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID，主键',
	`no` VARCHAR(32) NOT NULL COMMENT '编号',
	`username` VARCHAR(200) NOT NULL COMMENT '姓名',
	gender CHAR(1) NOT NULL COMMENT '性别: 1 男 0 女',
	age int NOT NULL COMMENT '年龄',
	phone VARCHAR(11) NOT NULL COMMENT '手机号码',
	addr varchar(250) COMMENT '家庭住址',
	KEY index_addr (addr),
	KEY index_no_username_age (no,username,age)
);
-- 插入测试数据
INSERT INTO `user`
VALUES
	(1,'u001','小陈','1','23','19985857777','湖南'),
	(2,'u001','小yu','0','22','19985857797','湖北'),
	(3,'u001','小泡','1','15','17985857777','湖南')

为了方便大家理解，我创建了三个索引，如下图：

3、索引失效情况分析

3.1、错误模糊查询导致索引失效

总所周知，模糊查询 LIKE 有三种常见用法：

1. 模糊匹配后面接任意字符：LIKE ‘小%’
2. 模糊匹配前面接任意字符：LIKE ‘%陈’
3. 模糊匹配前后面接任意字符：LIKE ‘%小%’

而这三种用法，只有第一种可以使用到索引，其余两种用法都会导致索引失效。具体执行结果如下：

注意： explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

3.2、非最左匹配导致索引失效

最左匹配原则是指，以最左边为起点字段查询可以使用联合索引，否则将不能使用联合索引。

测试数据中联合索引的字段顺序为（no–>username–>age），这里假设他们的顺序为A–>B–>C,那么以下联合索引的使用情况：

所以说，非最左匹配也会导致索引失效

3.3、列运算导致索引失效

如果索引列进行了运算，也会导致索引失效，情况如下：

3.4、使用函数导致索引失效

在查询列中使用MYSQL提供的函数就会导致索引失效，比如使用IFNULL函数之后的索引执行情况如下：

4、个人看法

在网上看其他博主说导致索引失效的情况还有索引列存在类型转换会导致索引、使用is not null 会导致索引失效，其实我是不太认同的，我自己测试情况如下：

以上只是我个人的看法，如果有不正确的地方麻烦大家多多指出，我们共同进步。