亲宝软件园·资讯

展开

mysql中datetime字段建立索引并比较大小详解

太上码农 人气:0

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字段的索引才能走,暂时没发现为啥会出现此问题,有知道可以分享下哦

总结

加载全部内容

相关教程
猜你喜欢
用户评论