关于mysql 索引失效 :
1、索引列不独立 –> 指被索引的这一列不能是表达式的一部分, 不能是函数的参数。如下 :
select id,name,age,salary from table_name where salary + 1000 = 6000;
其中索引列 salary 被用来做加法运算。
select id,name,age,salary from table_name where substring(name,1,3)= 'luc';
其中索引列被放在函数中用来做运算。
2、使用了左模糊 -> 如果实在避免不了左模糊查询的话,考虑一下搜索引擎 比如 ES
select id,name,age,salary from table_name where name like '%lucs%';
3、or查询部分字段没有使用索引 -> 这种情况下可以考虑所有字段都建立索引
select id,name,age,salary from table_name where name ='lucs' or age >25
4、字符串条件没有使用 ‘’ 引号 -> 如下 phone是字符串则会发生类型转换
select id,name,age,salary from table_name where phone=13088772233
5、不符合最左前缀原则的查询 ->
例如有这样一个组合索引 index(a,b,c)
select * from table_name where b='1'and c='2'
select * from table_name where c='2'
// 上面这两条 SQL 都是无法走索引执行的
6、索引字段没有添加 not null 约束 -> mysql 官方建议是把字段设置为 not null
select * from table_name where a is null;
// 这条sql就无法走索引执行了,is null 条件 不能使用索引,只能全表扫描了
7、隐式转换 -> 关联表的两个字段类型不一致会发生隐式转换,解决方式就是统一设置字段类型
select * from table_name t1 left join table_name2 t2 on t1.id=t2.tid;
上面这条语句里,如果 t1 表的id 类型和 t2 表的tid 类型不一致的时候,就无法按索引执行了。
8、优化器选择优先使用 order by 中的字段作为索引。
MySQL · 捉虫动态 · order by limit 造成优化器选择索引错误_weixin_33985507的博客-CSDN博客
https://blog.csdn.net/shaochenshuo/article/details/113520677
处理方案1:主动关闭该优化器开关 prefer_ordering_index
处理方案2:重写 sql
处理方案3:通过 hint 固化执行计划。比如通过 force index 指定使用的索引