分享一则生产数据库sql优化案例—从98s优化到1.4s
概述
今天主要分享一则生产环境的sql优化案例,仅供参考。
问题sql
该sql定位还是比较容易的,直接查慢查询语句就直接定位出来了。
在测试环境执行需耗时98s,查出数据7108条。
SELECT * FROM ( SELECT t.group_code, ter.terminate_begin_date AS terminateBeginDate, ... FROM t_att_leave_info t LEFT JOIN t_att_terminate_leave ter ON t.id = ter.head_id WHERE 1 = 1 AND DATE_FORMAT( leave_start_time, '%Y-%m-%d' ) >= DATE_FORMAT( '2020-04-30 00:00:00', '%Y-%m-%d' ) AND DATE_FORMAT( t.leave_end_time, '%Y-%m-%d' ) <= DATE_FORMAT( '2020-08-31 00:00:00', '%Y-%m-%d' ) AND t.business_status = 'Completed' AND t.business_status != 'Draft' ) auth WHERE ( 1 = 1 ) LIMIT 0,7137
分析执行计划
从执行计划可以发现只需要让t表和ter表命中索引即可
优化1:改写sql命中t表在时间上建的联合索引
修改后的sql如下:
实际运行时间还是接近90s,不太理想。
--添加联合索引 create index idx_leave_time on t_att_leave_info(leave_start_time,leave_end_time); --改写sql SELECT * FROM ( SELECT t.id,.... FROM t_att_leave_info t LEFT JOIN t_att_terminate_leave ter ON t.id = ter.head_id WHERE 1 = 1 AND t.leave_start_time >= DATE_FORMAT( '2020-04-30 00:00:00', '%Y-%m-%d' ) AND t.leave_end_time <= DATE_FORMAT( '2020-08-31 00:00:00', '%Y-%m-%d' ) AND t.business_status = 'Completed' AND t.business_status != 'Draft' ) auth WHERE ( 1 = 1 ) LIMIT 0,7137
优化2:修改字段类型使得ter表走索引
这里只是left join ter表,ter表的head_id还是有索引的,但一直没命中,一直以为是数据上问题,排查很久发现是表的字段类型问题..
1、检查表结构
可以发现之所以不走索引是因为这两个关联字段不同类型导致。
2、修改表结构
修改表结构如下:
3、再次执行sql并查看执行计划
再次执行sql只需1.4s。
查看执行计划:可以发现命中索引。