如何优化SQL中跨表更新列的查询？现有语句耗时9小时

原语句采用逐行子查询的方式更新，每次更新tableA的一行都要单独查询tableB，数据量较大时极易引发全表扫描，导致执行效率极低。以下是针对性优化方案：

1. 改用JOIN方式替换逐行子查询 #

将子查询逻辑提前通过分组取数，再通过JOIN关联更新，彻底避免逐行查询的开销。以SQL Server为例，优化后的语句如下：

UPDATE a
SET a.id = b.id
FROM tableA a
INNER JOIN (
    -- 先给tableB按bin分组，取每组第一个id（如需特定排序，修改ORDER BY内容）
    SELECT bin, id
    FROM (
        SELECT 
            bin, 
            id,
            ROW_NUMBER() OVER (PARTITION BY bin ORDER BY (SELECT NULL)) AS rn
        FROM tableB
    ) AS b_rn
    WHERE rn = 1
) b ON a.bin = b.bin;

注：原语句的TOP 1未指定排序规则，结果可能不稳定。如果业务需要固定取某一条（比如最新/最早的id），请将ORDER BY (SELECT NULL)替换为实际排序规则，例如ORDER BY b.id DESC。

2. 添加索引消除全表扫描 #

索引能大幅提升JOIN和分组查询的效率，建议创建以下索引：

• 给tableB的bin列建覆盖索引（包含id列，避免回表查询）：

  CREATE NONCLUSTERED INDEX IX_tableB_bin_id ON tableB (bin) INCLUDE (id);
  

• 如果tableA的bin列数据量大或查询频繁，也可以给tableA的bin列建索引：

  CREATE NONCLUSTERED INDEX IX_tableA_bin ON tableA (bin);
  

3. 超大表场景：分批更新 #

如果tableA数据量极大，一次性更新会导致长时间锁表、资源占用过高，可采用分批更新的方式：

DECLARE @BatchSize INT = 10000; -- 可根据服务器性能调整批次大小
DECLARE @MaxRowID INT = (SELECT MAX(id) FROM tableA); -- 假设tableA有自增id作为分段依据
DECLARE @CurrentRowID INT = 0;

WHILE @CurrentRowID < @MaxRowID
BEGIN
    UPDATE a
    SET a.id = b.id
    FROM tableA a
    INNER JOIN (
        SELECT bin, id
        FROM (
            SELECT 
                bin, 
                id,
                ROW_NUMBER() OVER (PARTITION BY bin ORDER BY (SELECT NULL)) AS rn
            FROM tableB
        ) AS b_rn
        WHERE rn = 1
    ) b ON a.bin = b.bin
    WHERE a.id > @CurrentRowID AND a.id <= @CurrentRowID + @BatchSize;

    SET @CurrentRowID = @CurrentRowID + @BatchSize;
    WAITFOR DELAY '00:00:01'; -- 可选，减轻服务器瞬时压力
END;

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