sql语句性能进阶必须了解的知识点——索引失效分析
在前面的文章中讲解了sql语句的优化策略
sql语句性能进阶必须了解的知识点——sql语句的优化方案-CSDN博客
sql语句的优化重点还有一处，那就是—— 索引！好多sql语句慢的本质原因就是设置的索引失效或者根本没有建立索引！今天我们就来总结一下那些无效的索引设置方式进而避免大家踩坑！看到这里有的同学会问：what？设置的索引还会失效？没错！接下来就让我们细细道来，文章非常有用，建议大家收藏。
不满足最左匹配原则
建立联合索引：idx_code_age_name。
该索引字段的顺序是：
-
code
-
age
-
name
以下会走索引
select * from user where code='101';
select * from user where code='101' and age=21
select * from user where code='101' and age=21 and name='Kevin';
select * from userwhere code = '101' and name='Kevin';
以下不会走索引
select * from user where age=21;
select * from user where name='Kevin';
select * from user where age=21 and name='Kevin';
使用了select *
如果select *语句中的查询列，都是索引列，那么这些列被称为覆盖索引。这种情况下，查询的相关字段都能走索引，索引查询效率相对来说更高一些；而使用select *查询所有列的数据，大概率会查询非索引列的数据，非索引列不会走索引，查询效率非常低。
索引列上有计算
例如 select * from user where id+1=2; 索引会失效
索引类用了函数
例如 select * from user where SUBSTR(height,1,2)=17;索引会失效
字段类型不同
字段类型不同索引会失效，例如设置code 为varchar，类型使用语句select * from user where code="101" 会走索引，如果我们不小心写成select * from user where code=101，则索引失效。因为code字段的类型是varchar，而传参的类型是int，两种类型不同导致索引失效。注意有一个例外就是int类型的参数，不管在查询时加没加引号，都能走索引。
like左边包括%
目前like查询主要有三种情况：
-
like '%a'（索引无效）
-
like 'a%'（索引有效）
-
like '%a%'（索引无效）
列对比
如果把两个单独建了索引的列，用来做列对比时索引会失效。例如： select * from user where id=height
使用or关键字
要么不用or要么将or的条件列都加索引（新版本mysql8以上，mysql5.6 or 索引无效）会采用索引合并的方式优化查询。
Not in 和not exists
主键字段中使用not in关键字查询数据范围，仍然可以走索引。而普通索引字段使用了not in关键字查询数据范围，索引会失效；
not exists时，索引也会失效。
order by 的坑
前提：已经建了联合索引：idx_code_age_name。
1.配合limit一起遵循最左匹配原则
explain select * from user order by code limit 100;
explain select * from user order by code,age limit 100;
explain select * from user order by code,age,name limit 100;
注意 order by不满足最左匹配原则，不会走索引。例如select * from user order by name limit 100
2.order by还能配合where一起遵循最左匹配原则。
例如：select * from user where code='101' order by age;
3.order by后面如果包含了联合索引的多个排序字段，只要它们的排序规律是相同的（要么同时升序，要么同时降序），也可以走索引。
注意：如果使用不同的排序规则不会走索引，例如select * from user order by code asc,age desc limit 100
具体sql如下：
explain select * from user order by code desc,age desc limit 100;
4.如果某个联合索引字段，在where和order by中都有也可以走索引
explain select * from user where code='101' order by code, name;
5.如果order by语句中没有加where或limit关键字，该sql语句将不会走索引。
6.对多个索引（注意：不是联合索引）进行order by不会走索引
我的每一篇文章都希望帮助读者解决实际工作中遇到的问题！如果文章帮到了您，劳烦点赞、收藏、转发！您的鼓励是我不断更新文章最大的动力！