记一次Mysql不走日期字段索引的原因小结

目录

背景

在一个表中,datatime字段设置是varchar类型,存入的数据是日期格式的数据,并且为该字段设置了索引。但是在日志记录中,有一条关于该表的慢查询。查询语句为:
select * from digitaltwin_meteorological where datatime > '2021-10-15';
explain分析sql语句,发现sql语句执行了全表扫描。为何sql中用了datatime索引列,为啥还走全表扫描呢?

探索

一:起初,认为是datatime字段类型为varchar,所以mysql在索引排序时,按照字符串顺序进行排序了,而不是日期大小顺序进行排序的,所以在范围查询时,并不能按照日期顺序进行索引的范围分区。于是把datatime改为datatime类型,在分析语句,发现还是全表扫描。

二:改变查询条件的值,

 select count(*) from digitaltwin_meteorological where datatime > '2021-10-15';

执行结果为3910。

explain select * from digitaltwin_meteorological where datatime > '2021-10-15';

sql语句分析结果为全表扫描:

我们把查询条件改为16号,看有多少条数据:

 select count(*) from digitaltwin_meteorological where datatime > '2021-10-16';

查询结果为2525,下面我们分析16号的查询语句:

explain select * from digitaltwin_meteorological where datatime > '2021-10-16';

执行结果为range查询,利用到了索引:

由此可见,当查询出来的记录条数多时,mysql会走全表扫描,认为全表扫描的效率更快。当查询出来的记录少时,mysql会使用索引查询。
全表的数据量为19714条数据,也就是说当2525/19714=13%的时候,mysql走索引查询。当3910/19714=20%的时候,mysql走全表扫描。

三:我们把datatime该为了datetime数据类型,那么查询条件是否还需要加引号呢,我们把datatime查询条件的引号去掉,看结果:

explain select * from digitaltwin_meteorological where datatime > 2021-10-16;

可见,去掉引号后,又成了全表扫描。所以说,不管字段类型是varchar还是datetime,查询条件的值都需要加引号。而不加引号,mysql会把这个值做一些运算操作,其实不加引号后2021-10-16就不再是16号的日期了,我们看如下sql:

 select count(*) from digitaltwin_meteorological where datatime > 2021-10-16;

计算结果为19714,全表的数据,所以说,datetime查询条件也需要加引号。

四:如上的分析,都是datatime在datetime类型情况下的讨论。而最初的字段类型是varchar,那么改成varchar类型,如上的结论还存在吗,我们修改类型,再执行sql:

explain select * from digitaltwin_meteorological where datatime > '2021-10-16';

可以看到,改成varchar类型后,16号查询成了全表扫描,而不是range扫描。
把条件改成17号,看执行结果:

explain select * from digitaltwin_meteorological where datatime > '2021-10-17';

17号的查询走了索引查询。我们看17号的数据量是1749。
所以,在字段类型为varchar时,1749/19714=9%的情况下,会走索引,而2525/19714=13%的情况下,会全表扫描。
也就是说当是datetime类型时,查询结果占13%的情况下,会走索引查询,而当是varchar类型时,查询结果占全表数据的13%时,会走全表扫描。这也是为什么日期类型我们要设置为datetime而不是varchar的原因之一。

总结

通过上述分析,可以总结如下结论:
1.范围查询中,当查询的数据量达到一定范围后,mysql认为全表扫描效率更高,会走全表扫描,而非索引。
2.datetime字段类型的值在查询时也要加引号,否则mysql不会按日期进行处理。
3.日期格式的数据,设置为varchar类型,范围查询走索引还是全表扫描的临界值比datetime类型的查询走索引查询还是全表扫描的临界值低,所以日期类型数据设置为datetime类型,会有更高概率走索引查询。

到此这篇关于记一次mysql不走日期字段索引的原因的文章就介绍到这了,更多相关mysql 日期字段索引内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com!

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐