SQL查询NOT EXISTS写法触发超时性能瓶颈的优化咨询

原查询核心瓶颈分析 #

• 内层子查询滥用SELECT *，读取大量非必要字段，提升IO开销
• 无覆盖索引支撑过滤、关联逻辑，查询过程中产生大量回表操作
• 部分字段未指定表别名，可能导致优化器解析异常，生成低效执行计划

已验证的有效优化点 #

你采用的LEFT JOIN + IS NULL替换NOT EXISTS、指定查询字段、用GROUP BY对齐索引列的调整方向正确，以上改动可以大幅降低查询开销。

进一步优化建议 #

• 补全语法歧义：原优化后代码中TABLE5未指定别名M、关联条件PRSN_NUM未标注所属表，需补全避免逻辑错误
• 按需移除GROUP BY：如果TABLE1、TABLE2、TABLE3按关联条件关联后不会产生重复的ID_NBR记录，可直接删除GROUP BY语句，避免不必要的排序分组开销
• 新增覆盖索引，避免全表扫描和回表：
  • TABLE1：(SUPP_NBR, ID_NBR, STAT_NUM, BGHT_ID)，覆盖WHERE过滤字段和关联字段
  • TABLE2：(ID_NBR, CUST_NUM)，覆盖关联和取值逻辑
  • TABLE3：(CUST_NUM, STAT_CD)
  • TABLE4：(ID_NBR)，优化LEFT JOIN匹配效率
  • TABLE5：(LOC_ID, PRSN_NUM, TXT_LOC)，覆盖外层过滤、关联逻辑
• 外层查询替换SELECT *为业务需要的指定字段，进一步降低数据传输开销

最终优化后SQL参考 #

SELECT APPL.ID_NBR, APPL.SUPP_NBR, APPL.STAT_CD, M.TXT_LOC /* 替换为实际需要的字段 */
FROM
(
    SELECT B.ID_NBR, B.SUPP_NBR, B.STAT_NUM, B.BGHT_ID, A.CUST_NUM, L.STAT_CD 
    FROM TABLE1 B
    INNER JOIN TABLE2 A ON B.ID_NBR = A.ID_NBR
    INNER JOIN TABLE3 L ON A.CUST_NUM = L.CUST_NUM
    LEFT JOIN TABLE4 C ON C.ID_NBR = B.ID_NBR
    WHERE B.SUPP_NBR = '17' 
      AND B.STAT_NUM <> 4 
      AND B.BGHT_ID IS NOT NULL 
      AND C.ID_NBR IS NULL
    /* 确认关联无重复可删除GROUP BY */
    GROUP BY B.ID_NBR, B.SUPP_NBR, B.STAT_NUM, B.BGHT_ID, A.CUST_NUM, L.CUST_NUM, L.STAT_CD
) APPL
INNER JOIN TABLE5 M ON M.PRSN_NUM = APPL.PRSN_NUM /* 补全字段所属表 */
WHERE M.LOC_ID = 'US' 
  AND (APPL.STAT_CD <> 'S' OR M.TXT_LOC != 'UNKNOWN');

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