MySQL 索引失效详解

一、MySQL索引失效原因汇总

1. 隐式的类型转换，索引失效
2. 查询条件包含or，可能导致索引失效
3. like通配符可能导致索引失效
4. 查询条件不满足联合索引的最左匹配原则
5. 在索引列上使用mysql的内置函数
6. 对索引进行列运算(如，+、-、*、/)
7. 索引字段上使用 (! = 或者< >)，索引可能失效
8. 索引字段上使用is null， is not null，索引可能失效
9. 左右连接，关联的字段编码格式不一样
10. 优化器选错了索引

二、MySQL索引失效原因分析

（1）隐式的类型转换，索引失效

如：普通索引userId是string，查询时用了where userId = 123;结果不走索引；如果给数字加上’',也就是说，传的是一个字符串呢，当然是走索引。
解释：为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，
MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。隐式的类型转换，索引会失效。

（2）查询条件包含or，可能导致索引失效

如：其中userId加了索引，但是age没有加索引的。我们使用了or，以下SQL是不走索引的，如下：select * from user where userId = ‘123’ or age = ‘18’;
解释：对于or+没有索引的age这种情况，假设它走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并。
如果它一开始就走全表扫描，直接一遍扫描就完事。Mysql优化器出于效率与成本考虑，遇到or条件，让索引失效，看起来也合情合理嘛。
注意：如果or条件的列都加了索引，索引可能会走也可能不走，大家可以自己试一试哈。
但是平时大家使用的时候，还是要注意一下这个or，学会用explain分析。遇到不走索引的时候，考虑拆开两条SQL。

（3）like通配符可能导致索引失效

如：like查询以%开头，索引失效，如：select * from user where userId like ‘%123’;
like把%放后面，发现索引还是正常走的，如下：select * from user where userId like ‘123%’;
解释：并不是用了like通配符，索引一定会失效，而是like查询是以%开头，才会导致索引失效。
思考：既然like查询以%开头，会导致索引失效。我们如何优化呢？
1.使用覆盖索引；2.把%放后面

（4）查询条件不满足联合索引的最左匹配原则

MySQl建立联合索引时，会遵循最左前缀匹配的原则，即最左优先。在联合索引中，查询条件满足最左匹配原则时，索引才正常生效。
如果你建立一个（a,b,c）的联合索引，相当于建立了(a)、(a,b)、(a,b,c)三个索引。
前提：联合索引(userId,name)
如：select * from user where name =‘张三’;因为查询条件列name不是联合索引idx_userid_name中的第一个列，索引不生效；
select * from user where userId =‘123’;查询条件满足最左匹配原则时，索引才正常生效。

（5）在索引列上使用mysql的内置函数

如：给login_time加了索引，但是因为使用了mysql的内置函数Date_ADD()，索引直接失效，
select * from user where DATE_ADD(login_time,INTERVAL 1 DAY)=‘2022-05-22 00:00:00’;
思考：一般这种情况怎么优化呢？可以把内置函数的逻辑转移到右边，如下：
select * from user where login_time = DATE_ADD(‘2022-05-22 00:00:00’,INTERVAL 1 DAY);

（6）对索引进行列运算(如，+、-、*、/)

如：给age加了索引，但是因为它进行运算，索引直接失效,如：select * from user where age -1 = 10;
所以不可以对索引列进行运算，可以在代码处理好，再传参进去。

（7）索引字段上使用 (! = 或者< >)，索引可能失效

如：给age加了索引，但是使用了！= 或者< >，not in这些时，索引如同虚设。如下：select * from user where age != 18;
解释：其实这个也是跟mySQL优化器有关，如果优化器觉得即使走了索引，还是需要扫描很多很多行的话，它觉得不划算，不如直接不走索引。
平时我们用！= 或者< >，not in的时候，留点心眼哈。

（8）索引字段上使用is null， is not null，索引可能失效

如：给单个name字段加上索引，并查询name为非空的语句，其实会走索引的，如下:select * from user where name is not null;
单个card字段加上索引，并查询card为非空的语句，其实会走索引的，如下:select * from user where card is not null;
注意：但是它两用or连接起来，索引就失效了，如下：select * from user where name is not null or card is not null;
解释：很多时候，也是因为数据量问题，导致了MySQL优化器放弃走索引。同时，平时我们用explain分析SQL的时候，如果type=range,要注意一下哈，
因为这个可能因为数据量问题，导致索引无效。

（9）左右连接，关联的字段编码格式不一样

前提：user表的name字段编码是utf8mb4，而user_job表的name字段编码为utf8。
如：执行左外连接查询,user_job表还是走全表扫描，如下：select * from user a join user_job b on a.name = b.name;
如果把它们的name字段改为编码一致，相同的SQL，还是会走索引。

（10）优化器选错了索引

解释：MySQL 中一张表是可以支持多个索引的。你写SQL语句的时候，没有主动指定使用哪个索引的话，用哪个索引是由MySQL来确定的。
思考：我们日常开发中，不断地删除历史数据和新增数据的场景，有可能会导致MySQL选错索引。那么有哪些解决方案呢？
1.使用force index 强行选择某个索引
2.修改你的SQl，引导它使用我们期望的索引
3.优化你的业务逻辑
4.优化你的索引，新建一个更合适的索引，或者删除误用的索引。

关注林哥，持续更新哦！！！★,°:.☆(￣▽￣)/$:.°★ 。

来源地址：https://blog.csdn.net/qq_45912025/article/details/130404195