SQL Server中两表高效LEFT JOIN的优化方案问询

针对你用临时表#tmpRecords和#tmpRecordsStock做LEFT JOIN、含NULL匹配关联条件+嵌套聚合计算StockQuantity的场景，以下是更落地的优化方向：

• 给临时表建针对性索引
  临时表默认无统计信息和索引，仅给真实表建索引不够。针对关联条件里的字段（包括需要匹配NULL的字段）建非聚集索引，同时把聚合用到的StockQuantity包含到索引中，避免回表：

  CREATE NONCLUSTERED INDEX IX_tmpRecords_关联字段 ON #tmpRecords (关联字段1, 关联字段2);
  CREATE NONCLUSTERED INDEX IX_tmpRecordsStock_关联_Stock ON #tmpRecordsStock (关联字段1, 关联字段2) INCLUDE (StockQuantity);
  

  注意：SQL Server索引默认包含NULL值，无需额外处理NULL匹配的索引逻辑。

• 简化NULL匹配的关联逻辑
  如果关联条件是a.col = b.col OR (a.col IS NULL AND b.col IS NULL)这类写法，可改用COALESCE(a.col, '无值占位符') = COALESCE(b.col, '无值占位符')（占位符要选不会和实际数据冲突的值），或者ISNULL(a.col, 0) = ISNULL(b.col, 0)（数值型字段），让查询优化器更容易识别并利用索引，避免OR导致的全表扫描。

• 预聚合替代嵌套聚合
  把嵌套在关联后的聚合逻辑提前到临时表预处理：

  -- 先预计算每个关联键对应的最小StockQuantity
  SELECT 
    关联字段1, 关联字段2,
    MIN(StockQuantity) AS MinStockQuantity
  INTO #tmpPreAggStock
  FROM #tmpRecordsStock
  GROUP BY 关联字段1, 关联字段2;
  
  -- 再关联预聚合表，减少关联后的计算量
  SELECT 
    r.*, s.MinStockQuantity AS StockQuantity
  FROM #tmpRecords r
  LEFT JOIN #tmpPreAggStock s
    ON r.关联字段1 = s.关联字段1 
    AND (r.关联字段2 = s.关联字段2 OR (r.关联字段2 IS NULL AND s.关联字段2 IS NULL));
  

  当#tmpRecordsStock数据量较大时，预聚合能大幅降低后续关联的计算开销。

• 用OUTER APPLY替代LEFT JOIN+聚合
  如果LEFT JOIN后大量行是右表无匹配的NULL，改用OUTER APPLY可以避免全表关联后再聚合，而是针对左表每一行单独执行聚合子查询：

  SELECT 
    r.*, s.StockQuantity
  FROM #tmpRecords r
  OUTER APPLY (
    SELECT MIN(StockQuantity) AS StockQuantity
    FROM #tmpRecordsStock s
    WHERE r.关联字段1 = s.关联字段1 
      AND (r.关联字段2 = s.关联字段2 OR (r.关联字段2 IS NULL AND s.关联字段2 IS NULL))
  ) s;
  

  若右表过滤后单条匹配的数据量小，这种方式的执行效率会优于LEFT JOIN。

• 更新临时表统计信息
  临时表数据加载完成后，手动更新统计信息，帮助查询优化器生成更优的执行计划：

  UPDATE STATISTICS #tmpRecords;
  UPDATE STATISTICS #tmpRecordsStock;
  

• 优化TOP 1替代MIN的索引
  你用SELECT TOP 1 StockQuantity FROM #tmpRecordsStock ORDER BY StockQuantity ASC替代MIN时，要给#tmpRecordsStock建关联字段+StockQuantity的复合索引，让子查询直接通过索引过滤并取最小值，无需排序：

  CREATE NONCLUSTERED INDEX IX_tmpRecordsStock_关联_StockQty ON #tmpRecordsStock (关联字段1, 关联字段2, StockQuantity);
  

  这样子查询可以直接走索引seek，性能比单纯的MIN聚合更稳定。

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