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

80 阅读 0 评论 53 点赞

我是靠谱客的博主俏皮鸵鸟，最近开发中收集的这篇文章主要介绍记一次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类型，会有更高概率走索引查询。