如何优化含OR条件的LEFT JOIN子句的SQL查询？

原SQL的性能瓶颈确实出在LEFT JOIN TND_WF_TASK_DETAIL WFTD的OR条件上——OR会让数据库无法有效利用索引，被迫做全表扫描，导致查询超时。我们可以把OR拆分成多个独立LEFT JOIN，再合并结果，避免索引失效。

原逻辑拆解 #

先明确原JOIN的三个匹配分支（修正括号优先级，AND比OR优先级更高）：

1. 分支1：WFT.WORK_TRAY_ID = 'N07' 且 WFTD.WF_TASK_ID = WFT.PARENT_TASK 且 WFTD.CONSIDER_RESULT_CODE LIKE '%Consult%' 且 WFTD.POL_COV_FLAG = 'P' 且 WFT.STATUS IN (0,1,2)
2. 分支2：WFT.WORK_TRAY_ID IN ('N02','N03','N06') 且 WFTD.NEXT_TASK_ID = WFT.WF_TASK_ID 且 WFT.STATUS IN (0,1,2) 且 WFTD.POL_COV_FLAG = 'P'
3. 分支3：WFT.STATUS = 3 且 WFTD.WF_TASK_ID = WFT.WF_TASK_ID 且 WFTD.POL_COV_FLAG = 'P'

改写后的SQL #

把三个分支拆成独立的LEFT JOIN，用COALESCE取第一个匹配到的RELA值，逻辑和原SQL完全一致，但每个JOIN都能利用索引：

SELECT  
    COALESCE(wftd1.RELA, wftd2.RELA, wftd3.RELA) AS RELA
    , WFTCS.PARENT_TASK AS CONSULT_TASK_ID
    , WFTCS.CREATE_DATE AS CONSULT_DATE
    , WFTD_THIS_TASK.APPROVED_RESULT_DATE AS THISTASK_APPROVED_RESULT_DATE
FROM TNH_WF WF
INNER JOIN TND_WF_TASK WFT 
    ON WFT.WF_ID = WF.WF_ID 
    AND WFT.OWNER_ATONEMENT IS NOT NULL 
    AND WFT.WORK_TRAY_ID IN ('N02', 'N03', 'N06', 'N07')
INNER JOIN TND_WF_TASK WFTCS 
    ON WFTCS.WF_ID = WFT.WF_ID 
    AND WFTCS.WORK_TRAY_ID = 'N07' 

LEFT JOIN TND_WF_TASK_DETAIL WFTD_THIS_TASK 
    ON WFTD_THIS_TASK.WF_TASK_ID = WFT.WF_TASK_ID 
    AND WFTD_THIS_TASK.POL_COV_FLAG = 'P'

-- 分支1：匹配N07的父任务关联记录
LEFT JOIN TND_WF_TASK_DETAIL wftd1
    ON wftd1.WF_TASK_ID = WFT.PARENT_TASK
    AND WFT.WORK_TRAY_ID = 'N07'
    AND wftd1.CONSIDER_RESULT_CODE LIKE '%Consult%'
    AND wftd1.POL_COV_FLAG = 'P'
    AND WFT.STATUS IN (0,1,2)

-- 分支2：匹配N02/N03/N06的下任务关联记录
LEFT JOIN TND_WF_TASK_DETAIL wftd2
    ON wftd2.NEXT_TASK_ID = WFT.WF_TASK_ID
    AND WFT.WORK_TRAY_ID IN ('N02', 'N03', 'N06')
    AND wftd2.POL_COV_FLAG = 'P'
    AND WFT.STATUS IN (0,1,2)

-- 分支3：匹配状态为3的当前任务关联记录
LEFT JOIN TND_WF_TASK_DETAIL wftd3
    ON wftd3.WF_TASK_ID = WFT.WF_TASK_ID
    AND WFT.STATUS = 3
    AND wftd3.POL_COV_FLAG = 'P'

额外性能优化建议 #

为了让拆分后的JOIN更快，给TND_WF_TASK_DETAIL创建以下索引：

• 针对分支1：CREATE INDEX idx_wftd_wf_task_pol_consult ON TND_WF_TASK_DETAIL(WF_TASK_ID, POL_COV_FLAG, CONSIDER_RESULT_CODE);
• 针对分支2：CREATE INDEX idx_wftd_next_task_pol ON TND_WF_TASK_DETAIL(NEXT_TASK_ID, POL_COV_FLAG);
• 针对分支3：可以复用分支1的索引，或者单独创建CREATE INDEX idx_wftd_wf_task_pol ON TND_WF_TASK_DETAIL(WF_TASK_ID, POL_COV_FLAG);

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