mysql的datetime字段建立索引并比较大小

1、问题背景

最近测试库查询一个表的数据，需要用到唯一的一个日期类型字段作为 where 的子查询（查询当天的数据），就正常写了个这样的 SQL，具体的表名我就不写了：

# create_time 是 datetime 类型
select * from ${tablename} where date(create_time)='20220919' limit 20;

其中字段的值样本如下：

我知道我写的这条 SQL 即使在 create_time 这个列有索引的情况下也不会走索引，但是执行了以后就 wc 了：

NM！20条记录你查询需要 8 s！
这种问题要不优化都不敢说自己是农民工！

2、优化过程

1） 操作索引

首先我看了下这个表的索引：

show index from ${tablename};

一看，尼玛，有一个列对应了 2 个索引字段，也就是 create_time 没有索引，我就干掉其中一个重复的并在 create_time 上建了一个（表名我就不写了）：

#删除索引
drop index index_create_time on ${tablename};
#添加索引
create index index_create_time on {tablename}(create_time);

2）是否走索引判断

有索引了关键是得让它走索引啊，先验证一下，我用执行计划继续执行了上面问题背景中的那个sql：

explain select * from ${tablename} where date(create_time)='20220919' limit 20;

结果如下，一点都不意外，照样全表扫描：

3）datetime使用索引查询

既然不能操作索引列，我又想查询某一天的数据，就只能用范围了，范围的就那个几个，between…and 、> 、<
于是，我写了下面查询 SQL：

select * from ${tablename} where create_time between '2022-09-19 00:00:00' and '2022-09-19 23:59:59';

果然不辜负我的小操作，查询结果如下：

查询 1850 条数据平均在 0.5 s，这不用说，绝壁走了索引，一个查询 20 条花费 8s，一个小 2 千记录花费半秒，没有可比性。

下面验证这个查询，同样在 sql 前加上 explain 后执行：

没毛病，索引类型 range，between…and 走的就是这种范围索引，这种范围类型的索引结构开销是有点大的，大到一定程度就不走索引了，比如在性别字段上建索引！