批量更新是数据库维护中绕不开的操作，比如把一批新价格刷回商品表、修正历史订单的状态字段等。当待更新的记录达到数万行甚至更多时，很多写法会明显变慢：有人用游标一行一行更新，有人把几万条UPDATE语句拼成一个超长脚本一次性执行，还有人直接用IN子查询套上一张巨大的来源表。这些做法的共同问题是产生了大量单行日志记录和反复的锁竞争，导致整个操作耗时成倍增长。本文以一个实际场景为例，讲解如何用临时表配合JOIN的方式改写批量更新，并结合索引与分批提交，把更新速度提升一个量级。
一、先搭建测试环境，看清慢在哪里
我们准备两张表：orders是百万行的目标表，price_change是存放新价格的来源数据，模拟一次需要对orders中数万行记录进行更新的场景。建表脚本如下。
-- 目标表：订单表
CREATE TABLE dbo.orders (
order_id INT IDENTITY(1,1) PRIMARY KEY,
product_no VARCHAR(20) NOT NULL,
price DECIMAL(10,2) NOT NULL,
updated_at DATETIME NOT NULL DEFAULT GETDATE()
);
-- 来源表：价格变更清单
CREATE TABLE dbo.price_change (
product_no VARCHAR(20) NOT NULL PRIMARY KEY,
new_price DECIMAL(10,2) NOT NULL
);
-- 向orders插入100万行测试数据
INSERT INTO dbo.orders (product_no, price)
SELECT TOP 1000000
CHAR(65 + (ABS(CHECKSUM(NEWID())) % 26))
+ RIGHT('00000' + CAST(ABS(CHECKSUM(NEWID())) % 100000 AS VARCHAR(5)), 5),
ABS(CHECKSUM(NEWID())) % 1000 * 1.0
FROM sys.all_columns a
CROSS JOIN sys.all_columns b;
-- 模拟5万条价格变更
INSERT INTO dbo.price_change (product_no, new_price)
SELECT DISTINCT TOP 50000 product_no,
ABS(CHECKSUM(NEWID())) % 1000 * 1.0 + 0.5
FROM dbo.orders;
环境准备好后，先看一个典型的慢写法：用IN子查询更新。这种写法语法上没有问题，但当子查询返回的数据量大时，优化器往往会为orders做全表扫描，逐行判断product_no是否命中子查询结果集。在我的测试机上，更新5万行大约需要一分多钟，期间事务日志增长了数百MB。
-- 慢写法：IN 子查询
UPDATE dbo.orders
SET price = pc.new_price,
updated_at = GETDATE()
FROM dbo.orders o
WHERE o.product_no IN (SELECT product_no FROM dbo.price_change);
打开执行计划可以清楚看到问题：UPDATE语句走的是Clustered Index Scan，估算的行数与实际行数偏差很大，而且对price_change的访问是通过Nested Loops配合索引查找完成的，整体代价集中在目标表的全扫描上。orders上只有order_id的聚集索引，product_no没有任何索引可用，扫描百万行只为更新其中5万行，浪费显而易见。
二、临时表加JOIN的改写思路
改写的第一步是把待更新的目标行提前定位出来，而不是在UPDATE语句里临时去全表找。把orders中需要更新的行的主键和对应的新价格一起装进一张临时表，就相当于预先把要改的数据整理成一张干净的清单，UPDATE时只需按主键逐个命中即可。
SET NOCOUNT ON;
-- 把匹配结果落入临时表，只保留主键和新值
SELECT o.order_id, pc.new_price
INTO #tmp_update
FROM dbo.orders o
JOIN dbo.price_change pc ON pc.product_no = o.product_no;
-- 在order_id上建索引，让后续更新走索引查找
CREATE CLUSTERED INDEX ix_tmp ON #tmp_update(order_id);
-- 基于临时表做JOIN更新
UPDATE o
SET o.price = t.new_price,
o.updated_at = GETDATE()
FROM dbo.orders o
JOIN #tmp_update t ON t.order_id = o.order_id;
这个写法的关键收益有两点。第一，JOIN发生在SELECT INTO阶段，此时是纯读操作，不会持有更新锁，扫描代价被隔离在事务之外；第二，临时表的聚集索引正好和目标表的聚集索引键对齐，UPDATE阶段的执行计划变成高效的索引查找加键查找，不再扫描整张orders表。实测同样的5万行更新，耗时从一分多钟降到四秒左右，日志增长也小了很多。
需要说明的是，这种把大操作拆成先整理再更新的思路，本质上和用表变量或者CTE做中间结果是一样的，但临时表有统计信息，优化器能拿到准确的基数估计，而表变量在旧版本里没有统计信息，大结果集时容易得到糟糕的计划，所以数万行以上的场景优先选临时表。
三、分批提交与索引配合进一步提速
即便用了临时表JOIN，如果一次性更新几十万行，仍可能带来长事务、锁升级和日志压力。稳妥的做法是分批提交，每批处理5000到10000行，用TOP控制批次大小，循环直到没有可更新的行为止。
SET NOCOUNT ON;
DECLARE @batch_size INT = 8000,
@rows INT = 1;
WHILE @rows > 0
BEGIN
UPDATE TOP (@batch_size) o
SET o.price = t.new_price,
o.updated_at = GETDATE()
FROM dbo.orders o
JOIN #tmp_update t ON t.order_id = o.order_id
WHERE o.updated_at = o.updated_at -- 占位条件，可按需加批次过滤
AND EXISTS (SELECT 1 FROM #tmp_update t2
WHERE t2.order_id = o.order_id);
SET @rows = @@ROWCOUNT;
-- 删除已处理的数据，保证每批处理不同行
DELETE t
FROM #tmp_update t
WHERE EXISTS (SELECT 1 FROM dbo.orders o
WHERE o.order_id = t.order_id
AND o.updated_at >= DATEADD(SECOND, -5, GETDATE()));
END
上面这个循环的核心是让临时表不断缩小，每批只处理剩余的数据。更简洁的做法是在临时表上加一个批次标记列，或者直接按order_id范围切批，逻辑会更清晰。分批的价值在于：每个小事务持有锁的时间短，不会触发锁升级到表级锁，事务日志可以随着日志备份或简单恢复模式及时截断，主库与Always On备库的同步延迟也可控。
另外别忘了目标表侧的索引。如果业务允许，给orders的product_no建一个非聚集索引，SELECT INTO阶段的匹配也能从扫描变成查找；但要注意，目标表上每多一个索引，UPDATE都要额外维护一份，所以只给真正参与连接的列建索引，避免更新时得不偿失。如果更新的列本身就在某个非聚集索引里，还要评估索引维护成本，必要时可在更新前禁用非必需索引，更新完再重建。
四、几个容易踩坑的细节
第一个坑是忘记SET NOCOUNT ON。批量更新时每批行都会向客户端发送DONE消息，几万行就是几万个往返，纯网络开销就可能拖慢脚本。第二是事务日志管理，如果数据库是完整恢复模式，一次超大UPDATE会让日志暴涨，切换到BULK_LOGGED并不能减少UPDATE的日志量，正确手段还是分批加及时日志备份。第三个是UPDATE的FROM子句写法，SQL Server特有的UPDATE...FROM语法在目标表与来源出现一对多时，更新结果是不确定的，务必保证JOIN键在来源侧唯一，临时表里先做去重或聚合就能避免这个问题。
最后一点是验证。更新完用一条统计语句核对结果，确认更新行数与来源表一致，关键字段无异常。生产环境执行前，建议把整个脚本包在显式事务里先在测试库演练，或者用数据库快照兜底，避免批量操作出错后难以回退。整体来看，临时表加JOIN再加索引配合与分批提交，是SQL Server大批量更新里性价比最高的一套组合拳，掌握之后处理数十万行级别的数据刷新也能从容应对。
SQL Server批量更新临时表JoinUPDATE优化修改时间：2026-09-09 06:42:39