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

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

问题背景

多表关联查询慢,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 

在这里插入图片描述

分析与尝试

  1. 给create_time加单索引
create index index_create_time on event_track(create_time);
-- drop index index_create_time on event_track;
  1. 执行SQL-V1,耗时67s,很明显between…and…可能没走索引。
    在这里插入图片描述
  2. 尝试修改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 

在这里插入图片描述

  1. 尝试在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 

在这里插入图片描述

  1. 尝试在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 ,字段是日期类型,可尝试如下方法优化查询:

  1. 字段加索引,查询时强制使用。如FORCE INDEX ( index_create_time );
  2. 减少字段的格式转换。如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),字段顺序也有学问,此文不做赘述。