【mysql】关于查询优化及索引的尝试(between and +日期类型字段场景)

最新推荐文章于 2025-04-07 22:22:45 发布

summer108

最新推荐文章于 2025-04-07 22:22:45 发布

阅读量8.5k

点赞数 2

分类专栏： mysql 文章标签：性能优化 sql优化 SQL索引

本文链接：https://blog.csdn.net/sinat_35630008/article/details/124013064

版权

mysql 专栏收录该内容

6 篇文章

订阅专栏

针对多表关联查询的性能问题，通过为create_time字段创建索引并调整查询条件，显著提升了查询效率。使用FORCEINDEX强制索引和避免日期格式转换，将查询时间从66秒降低到0.69秒，证明了优化策略的有效性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

问题背景

多表关联查询慢，3张表关联查询耗时66s。

便于后面区分，先将原始SQL命名为SQL-V1：

-- SQL-V1
select  a.id,a.nick_name,m.phonenumber,m.dept_name,case when m.status = '0' then '正常' when  m.status = '1' then '停用' END status
,channel,page_name,event_name,create_time
,param_key,param_value 
from event_track a 
LEFT JOIN (select s.user_name,s.phonenumber,t.dept_name,s.status  
from sys_user s join sys_dept t on s.dept_id = t.dept_id where s.del_flag=0) m 
on m.user_name=a.user_name
LEFT JOIN event_track_param b on a.id = b.event_track_id 
where DATE_FORMAT(create_time, '%Y%m%d') between '20220407'  and '20220407' 
order by  create_time limit 10 offset 0

在这里插入图片描述

分析与尝试

给create_time加单索引

create index index_create_time on event_track(create_time);
-- drop index index_create_time on event_track;

执行SQL-V1，耗时67s，很明显between…and…可能没走索引。
尝试修改create_time字段查询写法，SQL-V2耗时67s，依赖没起色。

-- SQL-V2
select  a.id,a.nick_name,m.phonenumber,m.dept_name,case when m.status = '0' then '正常' when  m.status = '1' then '停用' END status
,channel,page_name,event_name,create_time
,param_key,param_value 
from event_track a 
LEFT JOIN (select s.user_name,s.phonenumber,t.dept_name,s.status  
from sys_user s join sys_dept t on s.dept_id = t.dept_id where s.del_flag=0) m 
on m.user_name=a.user_name
LEFT JOIN event_track_param b on a.id = b.event_track_id 
-- where DATE_FORMAT(create_time, '%Y%m%d') between '20220407'  and '20220407' 
where create_time between STR_TO_DATE(concat('20220407','000000'), '%Y%m%d %H%i%s')  and STR_TO_DATE(concat('20220407','235959'), '%Y%m%d %H%i%s')
order by  create_time limit 10 offset 0

在这里插入图片描述

尝试在SQL-V2基础上，增加强制查索引写法 “FORCE INDEX ( index_create_time )”，SQL-V3耗时0.69s。

-- SQL-V3 
select  a.id,a.nick_name,m.phonenumber,m.dept_name,case when m.status = '0' then '正常' when  m.status = '1' then '停用' END status
,channel,page_name,event_name,create_time
,param_key,param_value 
from event_track a 
FORCE INDEX ( index_create_time )
LEFT JOIN (select s.user_name,s.phonenumber,t.dept_name,s.status  
from sys_user s join sys_dept t on s.dept_id = t.dept_id where s.del_flag=0) m 
on m.user_name=a.user_name
LEFT JOIN event_track_param b on a.id = b.event_track_id 
-- where DATE_FORMAT(create_time, '%Y%m%d') between '20220407'  and '20220407' 
where create_time between STR_TO_DATE(concat('20220407','000000'), '%Y%m%d %H%i%s')  and STR_TO_DATE(concat('20220407','235959'), '%Y%m%d %H%i%s')
order by  create_time limit 10 offset 0

在这里插入图片描述

尝试在SQL-V1基础上增加强制查索引写法 “FORCE INDEX ( index_create_time )”，SQL-V4耗时0.98s，相比来看SQL-V3表现更好。

-- SQL-V4
select  a.id,a.nick_name,m.phonenumber,m.dept_name,case when m.status = '0' then '正常' when  m.status = '1' then '停用' END status
,channel,page_name,event_name,create_time
,param_key,param_value 
from event_track a 
FORCE INDEX ( index_create_time )
LEFT JOIN (select s.user_name,s.phonenumber,t.dept_name,s.status  
from sys_user s join sys_dept t on s.dept_id = t.dept_id where s.del_flag=0) m 
on m.user_name=a.user_name
LEFT JOIN event_track_param b on a.id = b.event_track_id 
where DATE_FORMAT(create_time, '%Y%m%d') between '20220407'  and '20220407' 
order by  create_time limit 10 offset 0

在这里插入图片描述

小结

如果查询条件没有 = 字段，使用between and ，字段是日期类型，可尝试如下方法优化查询：

字段加索引，查询时强制使用。如FORCE INDEX ( index_create_time )；
减少字段的格式转换。如DATE_FORMAT(create_time, ‘%Y%m%d’) between ‘20220407’ and ‘20220407’ 改为 create_time between STR_TO_DATE(concat(‘20220407’,‘000000’), ‘%Y%m%d %H%i%s’) and STR_TO_DATE(concat(‘20220407’,‘235959’), ‘%Y%m%d %H%i%s’)。

此外，如果有多个条件可以尝试用联合索引，如create index index_create_time on event_track(id,nick_name,create_time)，字段顺序也有学问，此文不做赘述。