MS SQL Server多表关联查询耗时过长，求优化方案

原查询的核心问题 #

1. 数据膨胀导致计算量暴增：直接用LEFT JOIN关联三个奖牌表时会产生笛卡尔积。比如某国家有10块金牌、5块银牌、3块铜牌，连接后会生成10*5*3=150条重复记录，GROUP BY阶段需要处理海量冗余数据，这是查询极慢的根本原因。
2. 统计结果错误：原查询中Count(Gold.Medal)统计的是连接后的行数，而非实际奖牌数。比如上述例子中，金牌会被重复统计150次，最终得到的结果远大于实际奖牌数量。

正确的实现方法 #

方法1：预聚合子查询（推荐） #

先对每个奖牌表按NOC统计数量，再与Countries表关联，彻底避免数据膨胀：

SELECT
    C.NOC,
    C.Region,
    ISNULL(G.TotalGM, 0) AS TotalGM,
    ISNULL(S.TotalSM, 0) AS TotalSM,
    ISNULL(B.TotalBM, 0) AS TotalBM
FROM Countries C
LEFT JOIN (
    SELECT NOC, COUNT(Medal) AS TotalGM
    FROM Gold
    GROUP BY NOC
) G ON C.NOC = G.NOC
LEFT JOIN (
    SELECT NOC, COUNT(Medal) AS TotalSM
    FROM Silver
    GROUP BY NOC
) S ON C.NOC = S.NOC
LEFT JOIN (
    SELECT NOC, COUNT(Medal) AS TotalBM
    FROM Bronze
    GROUP BY NOC
) B ON C.NOC = B.NOC
-- 按需添加排序逻辑，例如按总奖牌数排序
-- ORDER BY (TotalGM + TotalSM + TotalBM) DESC

方法2：UNION ALL合并后统计 #

将三个奖牌表的记录合并并标记奖牌类型，再按国家聚合统计：

SELECT
    C.NOC,
    C.Region,
    SUM(CASE WHEN MedalType = 'Gold' THEN 1 ELSE 0 END) AS TotalGM,
    SUM(CASE WHEN MedalType = 'Silver' THEN 1 ELSE 0 END) AS TotalSM,
    SUM(CASE WHEN MedalType = 'Bronze' THEN 1 ELSE 0 END) AS TotalBM
FROM Countries C
LEFT JOIN (
    SELECT NOC, 'Gold' AS MedalType FROM Gold
    UNION ALL
    SELECT NOC, 'Silver' AS MedalType FROM Silver
    UNION ALL
    SELECT NOC, 'Bronze' AS MedalType FROM Bronze
) M ON C.NOC = M.NOC
GROUP BY C.NOC, C.Region

优化建议 #

• 添加索引：给Gold、Silver、Bronze表的NOC字段创建非聚集索引，若Medal字段可能为空，建议创建覆盖索引包含Medal，避免查询时回表：

  CREATE NONCLUSTERED INDEX IX_Gold_NOC ON Gold(NOC) INCLUDE(Medal);
  CREATE NONCLUSTERED INDEX IX_Silver_NOC ON Silver(NOC) INCLUDE(Medal);
  CREATE NONCLUSTERED INDEX IX_Bronze_NOC ON Bronze(NOC) INCLUDE(Medal);
  

• 优化Countries表：确保Countries表的NOC字段是主键或唯一索引，提升关联效率：

  ALTER TABLE Countries ADD CONSTRAINT PK_Countries_NOC PRIMARY KEY (NOC);
  

• 规范TOP使用：原查询用TOP 10但未指定排序，返回结果随机。若需要取特定排序的前10个国家，务必添加ORDER BY子句，且在预聚合后使用TOP，能进一步提升效率。

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