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 走的就是这种范围索引，这种范围类型的索引结构开销是有点大的，大到一定程度就不走索引了，比如在性别字段上建索引！

补充：Mysql 时间Datetime 索引不生效问题

今天发现之前在使用日期索引时，通过explain发现一直不走日期索引，在网上查询了下，发现使用过程中要注意以下情况：

1、在查询数据条数约占总条数五分之一以下时能够使用到索引，但超过五分之一时，则使用全表扫描了。

2、查询条件有日期索引和其他条件的话，只有所有条件都有索引的情况下，才会走日期索引，

例如：

我建立了复合索引car_date_index: date_time,car_plate_no.

当我是用查询条件： WHERE car_plate_no=‘冀E8888’ and date_time<=‘2019-05-01 00:00:00’，如果想走car_date_index的索引，表中必须有car_plate_no字段的索引才能走，暂时没发现为啥会出现此问题，有知道可以分享下哦

总结