记一次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 日期字段索引内容请搜索PHP之友以前的文章或继续浏览下面的相关文章希望大家以后多多支持PHP之友！
您可能感兴趣的文章:- 浅谈Mysql哪些字段适合建立索引
- MySQL中因字段字符集不同导致索引不能命中的解决方法
- mysql为字段添加和删除唯一性索引(unique) 的方法
- Mysql判断表字段或索引是否存在