mysql中合理的使用索引是提高查询效率的有效途径.但是有些时候我们建立了索引,查询效率依然没有没有改善. 这时候我们就需要检查我们的查询sql是否有问题,导致了索引失效.

索引失效的情况

建立了索引,但是查询语句并没有使用我们建立的索引.

1.对条件字段进行函数操作

对索引字段做函数操作时，优化器会放弃使用索引.

查询某一天的订单信息使用如下sql.即便create_date有索引,因为对create_date了函数操作所以此查询sql是不会利用索引的. 而是会进行全表扫描.

1
2

select * from order_info 
where date(create_date) = '2020-04-06'

此sql如果想利用索引查询可以改写成范围查询:

1
2

select * from order_info
where create_date > '2020-04-05 00:00:00' and create_date <= '2020-04-06 00:00:00'

2.条件字段有隐式的类型装换

用户信息表user_info,用户的编号user_no为varchar类型.查询编号为10000的用户信息.

1
2

select * from user_info 
where user_no=10000

因为user_no字段和查询值10000的数据类型不相同.因此上边sql实际等价于:

1
2

select * from user_info 
where cast(user_no as signed int)=10000

可以看出实际执行的sql中对条件字段有函数操作,所以隐式的类型转换是不能走索引的.

要想解决使用索引将sql改写成:

1
2

select * from user_info 
where user_no='10000'

建议在写SQL时，先看字段类型，然后根据字段类型写SQL.

3.左模糊查询

用户信息表user_info,用户名称user_name为varchar类型. 我们经常遇到产品要求左模糊查询的情况.对于mysql来说左模糊查询是不能利用索引的,进行全表扫描查询效率低下.

1
2

select * from user_info 
where user_name like '%建国'

为了利用索引可以将sql改写成:

1
2

select * from user_info 
where user_name like '川%'

如果产品需求必须实现左模糊查询,可以考虑使用elasticsearch实现此类功能

4.范围查询

对查询字段建立了索引,使用范围查询却没有利用上索引走了全表扫描. 原因是优化器会根据检索比例、表大小、I/O块大小等进行评估是否使用索引.并不是所有的范围查找都不会使用索引. 比如单次查询的数据量过大,优化器将不走索引. 查看范围查询是否使用索引可以通过explain查询执行计划.要想使用索引,可以对范围查询进行分页,减少单次查询的数据条目.

在执行以范围为条件的操作的时候,应该执行explain分析,确定走索引,再进行操作.否则不但可能导致操作缓慢, 在做更新或者删除操作的时候,甚至会导致表的所有记录锁住.

5.对条件字段进行计算

对索引字段做运算将使用不了索引

查询订单表order_info中交易金额等于100的订单

1
2

select * from order_info 
where amount-1='99'

合理的写法为

1
2

select * from order_info 
where amount=99+1

实际开发中,对条件字段的查询值做计算的操作应当放置在程序的代码中.尽量不要放在sql中