sql in语句优化_SQL优化实操案例：union all 语句该如何优化？

概要

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

问题如何定位

---

• 拆分SQL分成两部分优化

SQL1执行计划

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

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

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

SQL1改写

通过缩小结果集来优化

PROC_INST_ID_ = (SELECT DISTINCT entityidFROM VBTXMASMWHERE 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 btxmasLEFT JOIN (SELECT TXNO, MAX(ACCEPTOPINION) AS ACCEPTOPINIONFROM VLOAMASE where TXNO='817648') mase ON mase.TXNO = btxmas.TXNOLEFT JOIN VSECBURM BURM ON BURM.USERID = btxmas.LASTMODUSERLEFT JOIN (SELECT MIN(ROLEID) AS ROLEID, USERIDFROM VSECBTRMGROUP BY USERID) BTRM ON BTRM.USERID = BURM.USERIDLEFT JOIN VSECROLM ROLM ON ROLM.ROLEID = BTRM.ROLEIDWHERE 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 btxmasLEFT JOIN (SELECT TXNO, MAX(ACCEPTOPINION) AS ACCEPTOPINIONFROM VLOAMASE where TXNO='817648') mase ON mase.TXNO = btxmas.TXNOLEFT JOIN VSECBURM BURM ON BURM.USERID = btxmas.LASTMODUSERLEFT JOIN (SELECT MIN(ROLEID) AS ROLEID, USERIDFROM VSECBTRMGROUP BY USERID) BTRM ON BTRM.USERID = BURM.USERIDLEFT JOIN VSECROLM ROLM ON ROLM.ROLEID = BTRM.ROLEIDWHERE btxmas.txno = '817648'UNION ALLSELECT 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 TLEFT 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 entityidFROM VBTXMASMWHERE 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, USERIDFROM VSECBTRM) BTRM ON BTRM.USERID = BURM.USERIDLEFT JOIN VSECROLM ROLM ON ROLM.ROLEID = BTRM.ROLEIDLEFT JOIN ST_ACT_HI_IDENTITYLINK inde ON ROLM.ROLEDESC = inde.GROUP_ID_WHERE m.MESSAGE_ IS NOT NULLAND m.MESSAGE_ = '同意'AND inde.TASK_ID_ = T.ID_AND T.PROC_INST_ID_ = (SELECT DISTINCT entityidFROM VBTXMASMWHERE txno = '817648')ORDER BY TASK_ID_ ASC--耗时：7ms

改写后结果与原SQL相同

总结

---

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

---

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