如何检查sql语句的条件字段是否使用了索引以及索引失效的几种情况
今天执行通过时间范围查询订单数量的sql时,想看看该时间字段是否走了索引,发现一个很有意思的问题.
首先说一下查询是否使用了索引的方法
通过explain来查看,即将explain放在查询的sql前面
explain SELECT * from ord_order_consume where create_time > '2020-04-01 00:00:00' and create_time < '2020-07-23 23:59:59'
查询结果
主要说明一下红框里边字段的含义
table: sql所查询的表名
type: 结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题
possible_keys: sql可能用到的索引
key: sql实际用到的索引,如果是Null,说明没有用到索引
rows: MySQL认为执行查询时必须检查的行数
这里有一个有意思的问题,那就是其实我的表里边create_time字段是加了索引的,但可以看到type=ALL,说明没有使用到索引,这是为什么呢?
开始我以为是< >符号的问题,因为我看一篇博客上说,sql语句使用< >符号,会导致全部搜索,于是我改用between...and...语法
explain SELECT * from ord_order_consume where create_time between '2020-04-01 00:00:00' and '2020-07-23 23:59:59'
查询结果
发现type仍然是ALL,没有使用索引,说明这里跟< >符号没有关系
但是,当我将日期范围调小之后
explain SELECT * from ord_order_consume where create_time > '2020-06-01 00:00:00' and create_time < '2020-07-23 23:59:59'
查询结果
发现type变为了range,并且key也有索引的名称,说明使用了索引
由此得出结论
当时间类型的字段存在索引时,索引是否使用与查询到的数量大小有关
查询到的数量大,不使用索引;
查询到的数量小,使用索引;
至于这个数量是什么范围,好像是有一个比例.不过我大致测了一下,如果查询结果在两万左右,会使用索引,再大就不再使用索引了(这里仅做参考)
那么,导致sql语句跳过索引,直接全表搜索的情况还有哪些呢?这里简单做个总结
1.where条件查询语句带or,只要带or,即使其中的查询条件包含有索引的字段,也会导致索引失效,除非查询条件的字段都带索引(注意:主键自带索引,属于唯一索引的特定类型)
explain select * from ord_order_consume where user_id = 1349813 or buss_type = 2
结果
2.对于复合索引,如果查询条件不是第一个索引字段,那么不会使用索引
explain select * from ord_order_consume where create_time = '2020-06-01 00:00:00'
结果
explain select * from ord_order_consume where order_state = 20
结果
3.like模糊查询,%在左边(查了不少资料,说%在右边不会导致索引失效,但我实践了一下,发现也失效了,具体原因有待进一步研究)
explain select * from ord_order_consume where stake_no like '%1'
结果
4.where查询条件字段如果是varchar类型,必须用引号引起来,否则索引失效
explain select * from ord_order_consume where stake_no = 1140290000001466
select * from ord_order_consume where stake_no = '1140290000001466'
5.索引字段上使用 != 或者 <> 不等于操作符时,索引失效
EXPLAIN SELECT * from user where age != 18
EXPLAIN SELECT * from user where age <> 18
EXPLAIN SELECT * from user where age > 17 or age < 19
6.in 和 not in 会导致索引失效
EXPLAIN SELECT * from user where age in (18,19)
EXPLAIN SELECT * from user where age not in (18,19)
7.where查询字段进行表达式操作,索引失效
EXPLAIN SELECT * from user where age/2 = 10
EXPLAIN SELECT * from user where age = 10*2
8.where查询字段进行函数式操作,索引失效
EXPLAIN SELECT * from user where substring(name,1,1) = '张'
9.当全表扫描速度比索引速度快时,Mysql会使用全表扫描,此时索引失效
