索引失效的几种情况

1.联合索引不满足最左匹配原则

   建立一个如下联合索引

KEY `union_id_usename_age` (`id`,`username`,`age`)

 建立这个联合索引相当于建立了 (id),(id,username),(id,username,age)这一组索引，按照最左匹配原则，所以只要sql语句中没使用以上组合中的列，就会导致索引失效。

正常使用索引：

select * from user where id = '1' ;

select * from user where id = '1' and username = 'zs';

select * from user where id= '1' and username = 'zs' and age = '19' 

索引失效的情况：//未使用id列，不满足最左匹配原则

select * from user where username = 'zs' and age = '19' 

select * from user where username = 'zs'

select * from user where age = '19' ;

2.在联合索引下，尽量使用明确的查询列来趋向于走覆盖索引(禁止使用select * )；

在上面的联合索引中，如果查询条件是age或username，当使用了select * ，肯定是不会走索引的。

但如果希望根据username查询出id_no、username、age这三个结果（均为索引字段），明确查询结果字段，是可以走覆盖索引的：

1. explain select id_no, username, age from t_user where username = 'Tom2';

2. explain select id_no, username, age from t_user where age = 12;

3. 索引列参与运算

如下语句，id列参与运算，则索引会失效

select * from user where id + 1 = 2 ; 

可以在内存中进行计算好预期的值，或在参数的等号右侧进行运算，如：

-- 内存计算，得知要查询的id为1
select * from user where id = 1 ;
-- 参数侧计算
select * from user where id = 2 - 1 ;

 4.索引列参使用了函数

select * from user where SUBSTR(id,1,3) = '100';

 5.错误的Like使用

select * from user where name like '%s%';

这种情况不做索引的原因很容易理解，索引本身就相当于目录，从左到右逐个排序。而条件的左侧使用了占位符，导致无法按照正常的目录进行匹配，导致索引失效就很正常了。

6.类型隐式转换

如下语句，id列是varchar类型，而查询语句没有加上引号，所以会隐式转换未int类型进行查找，导致全表扫描

select * from user where id = 1002; 

但是有一个特例，如果字段类型为int类型，而查询条件添加了单引号或双引号，则Mysql会将参数转化为int类型，虽然使用了单引号或双引号，依旧会走索引： 

select * from user where id = '2';

7.使用OR操作

不当使用or也会导致索引失效，

1.如下语句，id有索引而gender无索引，这样也会导致全表扫描（or两边列都要加上索引才会正常走索引）

 select * from user where id= '1' or gender = '男'

2.如果or两边同时使用“>”和“<”，则索引也会失效

select * from user where id  > 1 or id  < 80;

8.两列数据做比较，即便两列都创建了索引，索引也会失效

 select * from user where id > age;

9.查询条件使用不等进行比较时，需要慎重，普通索引会查询结果集占比较大时索引会失效。

当查询条件为字符串时，使用”<>“或”!=“作为条件查询，有可能不走索引，但也不全是，当查询结果集占比比较小时，会走索引，占比比较大时不会走索引。此处与结果集与总体的占比有关。

select * from user where create_time != '2024-09-09 16:56:00'; // 结果集占比较大不走索引

select * from user where id != 2;//走索引

10.查询条件使用is null时正常走索引，使用is not null时，不走索引。

11.not in 和 not exist

1.查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效。

2.查询条件使用not exists时，索引失效。

12. order by导致索引失效

当查询条件涉及到order by、limit等条件时，是否走索引情况比较复杂，而且与Mysql版本有关，通常普通索引，如果未使用limit，则不会走索引。order by多个索引字段时，可能不会走索引。其他情况，建议在使用时进行expain验证。

13. 参数不同导致索引失效

当查询条件为大于等于、in等范围查询时，根据查询结果占全表数据比例的不同，优化器有可能会放弃索引，进行全表扫描。