如何查询sql语句的条件字段是否使用了索引以及跳过索引的几种情况 [数据库教程]

database

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

如何查询sql语句的条件字段是否使用了索引以及跳过索引的几种情况