概要

运行1857ms的语句如何下降到7ms，听我慢慢道来~~

问题如何定位

• 拆分SQL分成两部分优化

SQL1执行计划

SQL1执行时间耗时1110 ms，通过执行计划预测子查询结果集可能是导致语句低效的原因，进一步确认

注：子查询结果集太大，导致连接时扫描的行数太多，耗时889ms，确认是由于子查询导致

需要缩小结果集来优化此SQL

SQL1改写

通过缩小结果集来优化

PROC_INST_ID_ = (SELECT DISTINCT entityid
		FROM VBTXMASM
		WHERE txno = '817648'

缩小结果集后原执行耗时1110 ms下降到5ms

SQL2

SQL2同理SQL1

SQL2改写

SELECT 0 AS PROC_INST_ID_, BURM.USERNAME, 0 AS TASK_ID_, '' AS ASSIGNEE_, btxmas.LASTMODDATE AS START_TIME_
	, btxmas.LASTMODDATE AS END_TIME_, '' AS MESSAGE_, ROLM.ROLEDESC AS MEMO_
FROM VBTXMASM btxmas
	LEFT JOIN (
		SELECT TXNO, MAX(ACCEPTOPINION) AS ACCEPTOPINION
		FROM VLOAMASE where TXNO='817648'
	) mase ON mase.TXNO = btxmas.TXNO
	LEFT JOIN VSECBURM BURM ON BURM.USERID = btxmas.LASTMODUSER
	LEFT JOIN (SELECT MIN(ROLEID) AS ROLEID, USERID
		FROM VSECBTRM
		GROUP BY USERID
		) BTRM ON BTRM.USERID = BURM.USERID
	LEFT JOIN VSECROLM ROLM ON ROLM.ROLEID = BTRM.ROLEID
WHERE btxmas.txno = '817648'

--耗时：5ms

改写后的SQL整合

SELECT 0 AS PROC_INST_ID_, BURM.USERNAME, 0 AS TASK_ID_, '' AS ASSIGNEE_, btxmas.LASTMODDATE AS START_TIME_
	, btxmas.LASTMODDATE AS END_TIME_, '' AS MESSAGE_, ROLM.ROLEDESC AS MEMO_
FROM VBTXMASM btxmas
	LEFT JOIN (
		SELECT TXNO, MAX(ACCEPTOPINION) AS ACCEPTOPINION
		FROM VLOAMASE where TXNO='817648'
	) mase ON mase.TXNO = btxmas.TXNO
	LEFT JOIN VSECBURM BURM ON BURM.USERID = btxmas.LASTMODUSER
	LEFT JOIN (SELECT MIN(ROLEID) AS ROLEID, USERID
		FROM VSECBTRM
		GROUP BY USERID
		) BTRM ON BTRM.USERID = BURM.USERID
	LEFT JOIN VSECROLM ROLM ON ROLM.ROLEID = BTRM.ROLEID
WHERE btxmas.txno = '817648'
UNION ALL
SELECT T.PROC_INST_ID_, BURM.USERNAME, T.ID_ AS TASK_ID_, T.ASSIGNEE_, T.START_TIME_
	, T.END_TIME_, NVL(N.MESSAGE_, '') AS MESSAGE_, ROLM.ROLEDESC AS MEMO_
FROM ST_ACT_HI_TASKINST T
	LEFT JOIN (SELECT PROC_INST_ID_,MIN(ID_) AS MIN_COMMENT_ID_, MAX(ID_) AS MAX_COMMENT_ID_
		FROM ST_ACT_HI_COMMENT where PROC_INST_ID_ = (SELECT DISTINCT entityid
		FROM VBTXMASM
		WHERE txno = '817648'
		)) A ON A.PROC_INST_ID_ = T.PROC_INST_ID_
	LEFT JOIN ST_ACT_HI_COMMENT M ON M.PROC_INST_ID_ = T.PROC_INST_ID_
		AND M.TASK_ID_ = T.ID_
		AND M.ID_ = A.MIN_COMMENT_ID_
	LEFT JOIN ST_ACT_HI_COMMENT N ON N.PROC_INST_ID_ = T.PROC_INST_ID_
		AND N.TASK_ID_ = T.ID_
		AND N.ID_ = A.MAX_COMMENT_ID_
	LEFT JOIN VSECBURM BURM ON BURM.USERID = T.ASSIGNEE_
	LEFT JOIN (SELECT ROLEID, USERID
		FROM VSECBTRM
		) BTRM ON BTRM.USERID = BURM.USERID
	LEFT JOIN VSECROLM ROLM ON ROLM.ROLEID = BTRM.ROLEID
	LEFT JOIN ST_ACT_HI_IDENTITYLINK inde ON ROLM.ROLEDESC = inde.GROUP_ID_
WHERE m.MESSAGE_ IS NOT NULL
	AND m.MESSAGE_ = '同意'
	AND inde.TASK_ID_ = T.ID_
	AND T.PROC_INST_ID_ = (
		SELECT DISTINCT entityid
		FROM VBTXMASM
		WHERE txno = '817648'
		)
ORDER BY TASK_ID_ ASC

--耗时：7ms

改写后结果与原SQL相同

总结

• 该条SQL语句由union all 两条语句组成，分成上下两部分进行优化
• 上半部分通过执行机会获取的查询效率扫描行数最多，导致效率慢
• 通过缩小子查询的结果集进行改写

本文分享内容是我自己的一些见解，欢迎大家一起来评论留言探讨，我会不定期更新内容，希望大家关注我~