记一次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';

EXPLAIN select * from digitaltwin_meteorological where dataTime > '2021-10-15';

在这里插入图片描述

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

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

EXPLAIN select * from digitaltwin_meteorological where dataTime > '2021-10-16';

在这里插入图片描述

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

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

EXPLAIN select * from digitaltwin_meteorological where dataTime > 2021-10-16;

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

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

EXPLAIN select * from digitaltwin_meteorological where dataTime > '2021-10-16';

EXPLAIN select * from digitaltwin_meteorological where dataTime > '2021-10-17';

总结

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

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

背景

探索

总结

更多相关文章

随机推荐