含重复子查询的SQL运行缓慢 如何通过单次关联表优化查询效率

你可以先对TABLE2按Id做一次预聚合，计算出每个Id对应的最大c、d、e值，再通过LEFT JOIN和TABLE1关联，即可完全替代3次重复的子查询，逻辑和原查询完全一致：

SELECT
    tbl1.ID,
    tbl1.A,
    tbl1.B,
    CASE WHEN tbl1.c IS NULL THEN tbl2_agg.max_c ELSE tbl1.c END AS C,
    CASE WHEN tbl1.d IS NULL THEN tbl2_agg.max_d ELSE tbl1.d END AS D,
    CASE WHEN tbl1.e IS NULL THEN tbl2_agg.max_e ELSE tbl1.e END AS e,
    tbl1.f,
    tbl1.g,
    tbl1.h,
    tbl1.i
FROM TABLE1 tbl1
LEFT JOIN (
    SELECT 
        Id,
        MAX(c) AS max_c,
        MAX(d) AS max_d,
        MAX(e) AS max_e
    FROM TABLE2
    GROUP BY Id
) tbl2_agg ON tbl1.Id = tbl2_agg.Id

优化说明 #

• 原查询的MAX(DISTINCT 列)可以直接简化为MAX(列)：最大值本身是唯一值，DISTINCT不会改变计算结果，还会额外产生排序去重的开销，去掉后性能会进一步提升。
• 建议给TABLE2添加覆盖索引，避免聚合时回表查询数据，不同数据库的索引写法略有差异：
  • SQL Server：CREATE INDEX idx_table2_id_cde ON TABLE2(Id) INCLUDE (c, d, e)
  • MySQL/PostgreSQL：CREATE INDEX idx_table2_id_cde ON TABLE2(Id, c, d, e)
• 优化后仅对200万行的TABLE2做一次扫描聚合，相比原逻辑的3次全表扫描+匹配，性能可以提升至少3倍，配合索引使用耗时可以降到秒级。

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