1. 首页
  2. >
  3. 数据库技术
  4. >
  5. MySQL

分享一则生产数据库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


分享一则生产数据库sql优化案例—从98s优化到1.4s


分析执行计划

从执行计划可以发现只需要让t表和ter表命中索引即可


分享一则生产数据库sql优化案例—从98s优化到1.4s


优化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


分享一则生产数据库sql优化案例—从98s优化到1.4s


优化2:修改字段类型使得ter表走索引

这里只是left join ter表,ter表的head_id还是有索引的,但一直没命中,一直以为是数据上问题,排查很久发现是表的字段类型问题..

1、检查表结构

可以发现之所以不走索引是因为这两个关联字段不同类型导致。


分享一则生产数据库sql优化案例—从98s优化到1.4s


分享一则生产数据库sql优化案例—从98s优化到1.4s

2、修改表结构

修改表结构如下:

分享一则生产数据库sql优化案例—从98s优化到1.4s

3、再次执行sql并查看执行计划

再次执行sql只需1.4s。

分享一则生产数据库sql优化案例—从98s优化到1.4s

查看执行计划:可以发现命中索引。

分享一则生产数据库sql优化案例—从98s优化到1.4s