如何提升SQL Server百万行数据场景下MERGE操作的执行速度

• 给临时表#temp添加匹配列的聚集索引
  高开销Sort操作的核心原因是临时表为无索引的堆表，执行MERGE时数据库需要对临时表的匹配列（ColumnA、ColumnB、ColumnC、ColumnE）全量排序才能和目标表做匹配。在BULK INSERT完成后给临时表创建聚集索引，源数据会提前按匹配列排序，可完全消除执行计划中的Sort操作：

  CREATE CLUSTERED INDEX IX_#temp_MatchColumns ON #temp (ColumnA, ColumnB, ColumnC, ColumnE)
  

  1000万行数据创建该索引的耗时通常在数十秒级别，远低于原MERGE的数小时开销。

• 给目标表添加覆盖索引
  如果目标表上没有匹配列的索引，可创建非聚集覆盖索引，避免匹配时回表查询数据，进一步降低IO开销：

  CREATE NONCLUSTERED INDEX IX_target_table_MatchColumns ON target_table (ColumnA, ColumnB, ColumnC, ColumnE)
  INCLUDE (ColumnG, ColumnH, ColumnD, ColumnF) -- 包含所有需要比对、更新、插入的字段
  

  如果该表日常写入压力极高，可在MERGE执行完成后删除该临时索引，避免影响业务。

• 拆分MERGE为单独的UPDATE+INSERT操作
  MERGE本身存在额外的校验开销，且大事务会导致锁等待、日志暴涨，拆分后操作更灵活，还可支持分批处理：

  -- 先执行更新操作
  UPDATE t
  SET t.ColumnG = tmp.ColumnG, t.ColumnH = tmp.ColumnH
  FROM target_table t
  INNER JOIN #temp tmp 
    ON t.ColumnA = tmp.ColumnA 
    AND t.ColumnB = tmp.ColumnB 
    AND t.ColumnC = tmp.ColumnC 
    AND t.ColumnE = tmp.ColumnE
  WHERE EXISTS (
    SELECT t.ColumnG, t.ColumnH 
    EXCEPT 
    SELECT tmp.ColumnG, tmp.ColumnH
  ) -- 自动处理NULL值比对，比手动写NULL判断更高效
  
  -- 再执行插入操作
  INSERT INTO target_table (ColumnA, ColumnB, ColumnC, ColumnD, ColumnE, ColumnF, ColumnG, ColumnH)
  SELECT tmp.ColumnA, tmp.ColumnB, tmp.ColumnC, tmp.ColumnD, tmp.ColumnE, tmp.ColumnF, tmp.ColumnG, tmp.ColumnH
  FROM #temp tmp
  WHERE NOT EXISTS (
    SELECT 1 FROM target_table t
    WHERE t.ColumnA = tmp.ColumnA 
    AND t.ColumnB = tmp.ColumnB 
    AND t.ColumnC = tmp.ColumnC 
    AND t.ColumnE = tmp.ColumnE
  )
  

  如果数据量过大，可在UPDATE和INSERT语句中加TOP(N)分批提交，比如每次处理10万行，避免长事务阻塞。

• 额外优化项

  • 执行操作前先更新临时表和目标表的统计信息，避免执行计划因为统计信息不准选择错误的执行策略：UPDATE STATISTICS #temp; UPDATE STATISTICS target_table;
  • 操作时可添加最小日志相关提示，比如插入时用TABLOCK提示，减少事务日志写入量
  • 执行语句时添加OPTION (RECOMPILE)，让数据库根据临时表的实际数据量生成最优执行计划

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