SQL Server中LEFT OUTER JOIN慢查询优化方案咨询

1. 修正WHERE子句逻辑，避免LEFT JOIN失效 #

原SQL的WHERE JSA.ACTIVITY_ID = 10977 OR RISK.OWNING_ACTIVITY_ID = 10977会过滤掉LEFT JOIN中JSA表无匹配的行，变相破坏了LEFT JOIN的逻辑。如果要保留所有符合条件的RISK行，同时匹配JSA中符合条件的记录，应将JSA的过滤条件移至JOIN子句：

SELECT RISK.RA_HAZ_ID,
       RISK.RA_CTRL_ID,
       RISK.RA_HAZ_HAZARD_NAME,
       RISK.RA_CTRL_CONTROL_NAME,
       RISK.RA_CTRL_CONTROL_ID,
       RISK.RA_IS_ESCALATED,
       RISK.RA_IS_CONSEQUENCE,
       RISK.RA_CTRL_OUTCOME,
       RISK.RA_HAZ_IS_ALARP,
       RISK.RA_HAZ_CONSEQUENCE,
       RISK.RA_STEP_ORDER,
       RISK.RA_STEP_NAME,
       RISK.RA_HAZ_TYPE_NAME,
       RISK.RA_CTRL_IS_REQUIRED,
       RISK.RA_CTRL_IS_PRE_SELECTED,
       RISK.RA_CTRL_IS_SELECTED,
       RISK.RA_HAZ_SORT_ORDER,
       RISK.RA_HAZ_COMMENT,
       RISK.RA_CONTROL_COMMENT,
       RISK.INIT_RISK,
       RISK.RES_RISK,
       RISK.RA_MATRIX_ID,
       RISK.RISK_LEVEL,
       RISK.INIT_RISK_LEVEL,
       MAX(RISK.RA_CTRL_ASSIGNED_FULL_NAME) AS RA_CTRL_ASSIGNED_FULL_NAME
FROM PRINT_RISK_ASSESSMENT_DETAILS RISK
LEFT OUTER JOIN PRINT_JSA_REFERENCE JSA 
  ON JSA.OWNING_ACTIVITY_ID = RISK.OWNING_ACTIVITY_ID
  AND JSA.ACTIVITY_ID = 10977 -- 将JSA的过滤条件移至此处
WHERE RISK.OWNING_ACTIVITY_ID = 10977
GROUP BY RISK.RA_HAZ_ID,
         RISK.RA_CTRL_ID,
         RISK.RA_HAZ_HAZARD_NAME,
         RISK.RA_CTRL_CONTROL_NAME,
         RISK.RA_CTRL_CONTROL_ID,
         RISK.RA_IS_ESCALATED,
         RISK.RA_IS_CONSEQUENCE,
         RISK.RA_CTRL_OUTCOME,
         RISK.RA_HAZ_IS_ALARP,
         RISK.RA_HAZ_CONSEQUENCE,
         RISK.RA_STEP_ORDER,
         RISK.RA_STEP_NAME,
         RISK.RA_HAZ_TYPE_NAME,
         RISK.RA_CTRL_IS_REQUIRED,
         RISK.RA_CTRL_IS_PRE_SELECTED,
         RISK.RA_CTRL_IS_SELECTED,
         RISK.RA_HAZ_SORT_ORDER,
         RISK.RA_HAZ_COMMENT,
         RISK.RA_CONTROL_COMMENT,
         RISK.INIT_RISK,
         RISK.RES_RISK,
         RISK.RA_MATRIX_ID,
         RISK.RISK_LEVEL,
         RISK.INIT_RISK_LEVEL
ORDER BY RISK.RA_STEP_ORDER,
         RISK.RA_HAZ_SORT_ORDER,
         RISK.RA_HAZ_HAZARD_NAME,
         RISK.RA_HAZ_TYPE_NAME DESC,
         RISK.RA_CTRL_IS_REQUIRED DESC,
         RISK.RA_CTRL_IS_PRE_SELECTED DESC,
         RISK.RA_CTRL_CONTROL_NAME

2. 创建针对性索引，加速过滤、JOIN与排序 #

• 给PRINT_RISK_ASSESSMENT_DETAILS创建覆盖复合索引，覆盖WHERE过滤、GROUP BY、ORDER BY及SELECT的所有字段，避免回表查询：

-- MySQL 示例
CREATE INDEX idx_risk_core ON PRINT_RISK_ASSESSMENT_DETAILS
(OWNING_ACTIVITY_ID, RA_STEP_ORDER, RA_HAZ_SORT_ORDER, RA_HAZ_HAZARD_NAME)
INCLUDE (RA_HAZ_ID, RA_CTRL_ID, RA_HAZ_HAZARD_NAME, RA_CTRL_CONTROL_NAME, RA_CTRL_CONTROL_ID, RA_IS_ESCALATED, RA_IS_CONSEQUENCE, RA_CTRL_OUTCOME, RA_HAZ_IS_ALARP, RA_HAZ_CONSEQUENCE, RA_STEP_NAME, RA_HAZ_TYPE_NAME, RA_CTRL_IS_REQUIRED, RA_CTRL_IS_PRE_SELECTED, RA_CTRL_IS_SELECTED, RA_HAZ_COMMENT, RA_CONTROL_COMMENT, INIT_RISK, RES_RISK, RA_MATRIX_ID, RISK_LEVEL, INIT_RISK_LEVEL, RA_CTRL_ASSIGNED_FULL_NAME);

-- Oracle 示例
CREATE INDEX idx_risk_core ON PRINT_RISK_ASSESSMENT_DETAILS
(OWNING_ACTIVITY_ID, RA_STEP_ORDER, RA_HAZ_SORT_ORDER, RA_HAZ_HAZARD_NAME)
INCLUDE (RA_HAZ_ID, RA_CTRL_ID, RA_HAZ_HAZARD_NAME, RA_CTRL_CONTROL_NAME, RA_CTRL_CONTROL_ID, RA_IS_ESCALATED, RA_IS_CONSEQUENCE, RA_CTRL_OUTCOME, RA_HAZ_IS_ALARP, RA_HAZ_CONSEQUENCE, RA_STEP_NAME, RA_HAZ_TYPE_NAME, RA_CTRL_IS_REQUIRED, RA_CTRL_IS_PRE_SELECTED, RA_CTRL_IS_SELECTED, RA_HAZ_COMMENT, RA_CONTROL_COMMENT, INIT_RISK, RES_RISK, RA_MATRIX_ID, RISK_LEVEL, INIT_RISK_LEVEL, RA_CTRL_ASSIGNED_FULL_NAME);

• 给PRINT_JSA_REFERENCE创建JOIN字段的复合索引：

CREATE INDEX idx_jsa_join ON PRINT_JSA_REFERENCE (OWNING_ACTIVITY_ID, ACTIVITY_ID);

3. 简化聚合逻辑，替换GROUP BY为窗口函数 #

原GROUP BY包含大量字段，仅需聚合RA_CTRL_ASSIGNED_FULL_NAME，可改用窗口函数替代GROUP BY，避免大量分组排序开销：

SELECT RISK.RA_HAZ_ID,
       RISK.RA_CTRL_ID,
       RISK.RA_HAZ_HAZARD_NAME,
       RISK.RA_CTRL_CONTROL_NAME,
       RISK.RA_CTRL_CONTROL_ID,
       RISK.RA_IS_ESCALATED,
       RISK.RA_IS_CONSEQUENCE,
       RISK.RA_CTRL_OUTCOME,
       RISK.RA_HAZ_IS_ALARP,
       RISK.RA_HAZ_CONSEQUENCE,
       RISK.RA_STEP_ORDER,
       RISK.RA_STEP_NAME,
       RISK.RA_HAZ_TYPE_NAME,
       RISK.RA_CTRL_IS_REQUIRED,
       RISK.RA_CTRL_IS_PRE_SELECTED,
       RISK.RA_CTRL_IS_SELECTED,
       RISK.RA_HAZ_SORT_ORDER,
       RISK.RA_HAZ_COMMENT,
       RISK.RA_CONTROL_COMMENT,
       RISK.INIT_RISK,
       RISK.RES_RISK,
       RISK.RA_MATRIX_ID,
       RISK.RISK_LEVEL,
       RISK.INIT_RISK_LEVEL,
       MAX(RISK.RA_CTRL_ASSIGNED_FULL_NAME) OVER (PARTITION BY RISK.RA_HAZ_ID) AS RA_CTRL_ASSIGNED_FULL_NAME
FROM PRINT_RISK_ASSESSMENT_DETAILS RISK
LEFT OUTER JOIN PRINT_JSA_REFERENCE JSA 
  ON JSA.OWNING_ACTIVITY_ID = RISK.OWNING_ACTIVITY_ID
  AND JSA.ACTIVITY_ID = 10977
WHERE RISK.OWNING_ACTIVITY_ID = 10977
ORDER BY RISK.RA_STEP_ORDER,
         RISK.RA_HAZ_SORT_ORDER,
         RISK.RA_HAZ_HAZARD_NAME,
         RISK.RA_HAZ_TYPE_NAME DESC,
         RISK.RA_CTRL_IS_REQUIRED DESC,
         RISK.RA_CTRL_IS_PRE_SELECTED DESC,
         RISK.RA_CTRL_CONTROL_NAME

如果RA_HAZ_ID是表的主键/唯一键，窗口函数的PARTITION BY仅需按RA_HAZ_ID分组即可，效率更高。

4. 更新表统计信息，帮助优化器生成最优计划 #

确保数据库统计信息最新，让优化器能准确判断数据分布，选择最优执行计划：

• MySQL：

ANALYZE TABLE PRINT_RISK_ASSESSMENT_DETAILS;
ANALYZE TABLE PRINT_JSA_REFERENCE;

• Oracle：

EXEC DBMS_STATS.GATHER_TABLE_STATS('你的 schema 名', 'PRINT_RISK_ASSESSMENT_DETAILS');
EXEC DBMS_STATS.GATHER_TABLE_STATS('你的 schema 名', 'PRINT_JSA_REFERENCE');

内容的提问来源于stack exchange，提问作者Thien Nguyen