详解如何用两条索引将生产数据库上某条sql从400多s都查不出结果优化到0.9s

概述

问题出现在今天下午，zabbix监控数据库CPU使用率过高触发告警，查看了一下数据库服务器，发现cpu飙升到900%，采集MySQL数据库 awr报告后做性能剖析发现是由于一条sql导致。这里简单记录下问题解决过程。

问题sql定位

1、采集awr报告

输出部分结果如下：

2、查看问题sql的执行计划和执行耗时

可以看到涉及的几个表都走全扫，跑了439s也没出结果，最后中断了。

SELECT
	count( 0 ) 
FROM
	(
	SELECT
		tos.unit_code,
		tos.unit_name,
		tos.dept_code,
		tos.dept_name,
		count( 0 ) AS inspectionCount,
		sum( CASE WHEN tos.abnormal_type = 'D' THEN 0 ELSE 1 END ) AS abnormalCount,
		sum( CASE WHEN ( tos.STATUS = 3 AND tos.abnormal_type != 'D' ) THEN 1 ELSE 0 END ) AS finishCount,
		ifnull((
			SELECT
				count( 1 ) AS onceCompleteTims 
			FROM
				t_ocs_secure_inspection_info osi 
			WHERE
				osi.STATUS = '3' 
				AND osi.abnormal_type != 'D' 
				AND NOT EXISTS (
				SELECT
					1 
				FROM
					t_ocs_secure_inspection_log tore 
				WHERE
					tore.pid = osi.id 
				AND tore.operat_type IN ( 'approved_reject', 'inspect_reject' )) 
				AND DATE_FORMAT( osi.inspection_date, '%Y-%m-%d' ) >= DATE_FORMAT( '2021-03-01', '%Y-%m-%d' ) 
				AND DATE_FORMAT( osi.inspection_date, '%Y-%m-%d' ) <= DATE_FORMAT( '2021-03-24', '%Y-%m-%d' ) 
				AND osi.unit_code = tos.unit_code 
				AND osi.dept_code = tos.dept_code 
			GROUP BY
				osi.unit_code,
				osi.dept_code 
				),
			0 
		) AS onceCompleteTims 
	FROM
		t_ocs_secure_inspection_info tos 
	WHERE
		DATE_FORMAT( tos.inspection_date, '%Y-%m-%d' ) >= DATE_FORMAT( '2021-03-01', '%Y-%m-%d' ) 
		AND DATE_FORMAT( tos.inspection_date, '%Y-%m-%d' ) <= DATE_FORMAT( '2021-03-24', '%Y-%m-%d' ) 
	GROUP BY
		tos.unit_code,
		tos.unit_name,
		tos.dept_code,
	tos.dept_name 
	) table_count

优化sql

1、优化1：在tore表的pid列上建索引

这里按id从大到小去看，先从子查询中的tore表入手，可以发现pid是没建对应索引的

create index idx_pid on t_ocs_secure_inspection_log(pid);

再次查看优化后的sql执行计划及查询耗时，可以发现优化后的sql，tore表走索引，且耗时10.5s即可出结果，但10s还是有点长。

2、优化2：在osi表上建立unit_code和dept_code的联合索引

继续优化子查询部分中的osi表，可以发现unit_code和dept_code是去和tos表关联，但没对应索引

create index idx_code on t_ocs_secure_inspection_info(unit_code,dept_code);

再次查看优化后的sql执行计划及查询耗时，可以发现子查询部分已经走指定索引，最终查询耗时0.9s，符合需求

总结

每周sql优化是DBA的必修课！大家一定要保证这个习惯。