sql语句性能进阶必须了解的知识点——索引失效分析

在前面的文章中讲解了sql语句的优化策略

https://blog.51cto.com/liwen629/8146651

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不会走索引

我的每一篇文章都希望帮助读者解决实际工作中遇到的问题！如果文章帮到了您，劳烦点赞、收藏、转发！您的鼓励是我不断更