今天执行通过时间范围查询订单数量的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'