如何在SQL Server中更新拥有数百万行记录的大表？

Question

我有一条 UPDATE 语句可以更新超过一百万条记录。我想将它们分批更新，每次更新1000或10000条。我尝试使用 @@ROWCOUNT，但无法获得期望的结果。

仅用于测试目的，我选择了一个有14条记录的表，并设置了行计数为5。这个查询应该会更新5条记录、5条记录和4条记录，但实际上它只更新了前5条记录。

查询-1:

SET ROWCOUNT 5

UPDATE TableName 
SET Value = 'abc1' 
WHERE Parameter1 = 'abc' AND Parameter2 = 123

WHILE @@ROWCOUNT > 0
BEGIN
    SET rowcount 5

    UPDATE TableName 
    SET Value = 'abc1' 
    WHERE Parameter1 = 'abc' AND Parameter2 = 123

    PRINT (@@ROWCOUNT)
END

SET rowcount 0

查询-2：

SET ROWCOUNT  5

WHILE (@@ROWCOUNT > 0)
BEGIN
    BEGIN TRANSACTION

    UPDATE TableName 
    SET Value = 'abc1' 
    WHERE Parameter1 = 'abc' AND Parameter2 = 123

    PRINT (@@ROWCOUNT)

    IF @@ROWCOUNT = 0
    BEGIN
        COMMIT TRANSACTION

        BREAK
    END

    COMMIT TRANSACTION
END

SET ROWCOUNT  0

我在这里错过了什么？

- GThree

---

查询2有什么问题？ - Juan Carlos Oropeza

不要像这样使用ROWCOUNT，它已经过时。参考链接：https://msdn.microsoft.com/zh-cn/library/ms188774.aspx - Sean Lange

@JuanCarlosOropeza 这个命令返回"成功完成"的信息，但实际上没有任何记录被更新。 - GThree

所有答案都一遍又一遍地更新相同的记录。您需要区分已更新的记录和未更改的记录。我认为这就是问题所在。也许您想在where子句中添加“AND Value <> 'abc1'”。 - FLICKER

打印语句破坏了逻辑，请删除该行。 - Mahesh

Answer 1

1. 如果不确定操作是否正在获取页面锁（由于每个页面上的多行是UPDATE操作的一部分），则不应在一个集合中更新10k行。问题在于，锁升级（从行或页到表锁）发生在5000个锁时。因此，最安全的方法是将其保持在5000以下，以防万一操作使用行锁。

2. 不应该使用SET ROWCOUNT来限制将被修改的行数，这里有两个问题：

   1. 自从SQL Server 2005发布以来（11年前）就已经弃用了它:

      在未来的SQL Server版本中，使用SET ROWCOUNT不会影响DELETE、INSERT和UPDATE语句。避免在新开发工作中使用SET ROWCOUNT与DELETE、INSERT和UPDATE语句，并计划修改当前使用它的应用程序。对于类似的行为，请使用TOP语法

   2. 它可能会影响更多的语句:

      设置SET ROWCOUNT选项会导致大多数Transact-SQL语句在受指定行数影响后停止处理。这包括触发器。ROWCOUNT选项不会影响动态游标，但它确实限制了键集和敏感游标的行集。应谨慎使用此选项。

   而是使用TOP ()子句。

3. 这里没有明确的事务目的。它使代码复杂化，而且你没有处理ROLLBACK的方式，甚至不需要处理，因为每个语句都是自己的事务（即自动提交）。

4. 假设您找到了保留显式事务的原因，那么您就没有一个TRY/CATCH结构。请参见我在DBA.StackExchange上的答案，其中包含处理事务的TRY/CATCH模板：

   我们是否需要在C#代码中处理事务以及存储过程？

我怀疑真正的WHERE子句没有在问题的示例代码中显示，因此仅仅依靠所展示的内容，一个更好的模型^{(请参见下面有关性能的注释)}将是:

DECLARE @Rows INT,
        @BatchSize INT; -- keep below 5000 to be safe
    
SET @BatchSize = 2000;

SET @Rows = @BatchSize; -- initialize just to enter the loop

BEGIN TRY    
  WHILE (@Rows = @BatchSize)
  BEGIN
      UPDATE TOP (@BatchSize) tab
      SET    tab.Value = 'abc1'
      FROM  TableName tab
      WHERE tab.Parameter1 = 'abc'
      AND   tab.Parameter2 = 123
      AND   tab.Value <> 'abc1' COLLATE Latin1_General_100_BIN2;
      -- Use a binary Collation (ending in _BIN2, not _BIN) to make sure
      -- that you don't skip differences that compare the same due to
      -- insensitivity of case, accent, etc, or linguistic equivalence.

      SET @Rows = @@ROWCOUNT;
  END;
END TRY
BEGIN CATCH
  RAISERROR(stuff);
  RETURN;
END CATCH;

通过将 @Rows 与 @BatchSize 进行测试，您可以避免最后的 UPDATE 查询（在大多数情况下），因为最终集通常比 @BatchSize 少一些行，这种情况下我们知道没有更多要处理的行（这就是您在答案中看到的输出）。仅在最终行集等于 @BatchSize 的情况下，此代码将运行影响0行的最终 UPDATE 。

我还添加了一个条件到 WHERE 子句，以防止已经更新的行再次被更新。

关于性能的注意事项

我强调了上面的“更好”（例如，“这是一个更好的模型”），因为它比O.P.原始代码有几个改进，并且在许多情况下运行良好，但并非所有情况都完美。对于至少某个大小的表格（由于几个因素而变化，因此我无法更具体），如果以下情况之一成立，则性能将随着需要修复的行越来越少而降低：

1.没有支持查询的索引；或者 2.存在索引，但 WHERE 子句中至少有一个列是不使用二进制排序规则的字符串数据类型，因此在此处添加了 COLLATE 子句以强制使用二进制排序规则，并且这样做会使该索引无效（对于此特定查询）。

这就是@mikesigs遇到的情况，因此需要采用不同的方法。更新的方法将要更新的所有行的ID复制到一个临时表中，然后使用该临时表在聚集索引键列上 INNER JOIN 到正在更新的表格上。（无论是否为主键列，捕获并与聚集索引列连接非常重要！）

有关详细信息，请参见下面的@mikesigs答案。该答案中显示的方法是我自己多次使用的一种非常有效的模式。我唯一要做的更改是：

1.显式创建＃targetIds 表，而不是使用 SELECT INTO ... 2.对于＃targetIds 表，在列上声明聚集主键。 3.对于＃batchIds 表，在列上声明聚集主键。 4.对于插入＃targetIds ，请使用 INSERT INTO＃targetIds（column_name（s））SELECT 并且删除 ORDER BY ，因为它是不必要的。

因此，如果您没有可以用于此操作的索引，并且无法暂时创建一个实际起作用的索引（根据UPDATE查询中的WHERE子句，过滤索引可能有效），那么请尝试@mikesigs答案中所示的方法（如果您使用该解决方案，请为其投票）。

- Solomon Rutzky

---

如果我不仅更新一个单独的列，而是10个列怎么办？我必须比较所有列的值吗？最高效的方法是什么？ - asemprini87

尽可能进行比较，以减少不必要的更新，因为它们需要更长时间并增加日志文件的大小。我刚刚更新了我的答案，包括强制二进制排序用于Value过滤器，但是您可以在任何其他字符串列上使用COLLATE Latin1_General_100_BIN2来加速字符串匹配，假设您只寻找精确匹配并且不需要考虑大小写差异等。我想提到为此操作创建一个过滤索引，但如果每个批次都必须更新，则可能不会更快。不过值得测试一下。 - Solomon Rutzky

@SolomonRutzky：假设在更新数百万条记录时，如果有几条记录更新失败，那么您将如何记录这些未能更新的记录。例如，在100万条记录中，有500,000条记录成功更新，接下来的五条记录无法更新，而其余的记录都成功更新，那么您将如何记录这5条失败的记录。 - MiniSu

SolomonRutzky和@mikesigs，感谢你们的回答，它们确实加速了更新查询，以至于我不禁想知道：为什么SQL Server不能在我们使用简单的UPDATE查询时内部进行这些优化呢？也许不是批量优化，但临时表优化似乎很容易在第一眼就包含。你有任何想法还是我漏掉了什么？ - kitsiosk

我的猜测是将操作分解为单独的步骤，包括使用聚集键的临时表来执行主要操作可能过于复杂，并且可能会引入异常，因为它们并不完全相同，即使非常相似。如果批处理，则更加不同，因为这将需要为每个“UPDATE”创建和填充临时表，而不是一次性完成，这减少了该方法的某些好处。 - Solomon Rutzky

Answer 2

WHILE EXISTS (SELECT * FROM TableName WHERE Value <> 'abc1' AND Parameter1 = 'abc' AND Parameter2 = 123)
BEGIN
UPDATE TOP (1000) TableName
SET Value = 'abc1'
WHERE Parameter1 = 'abc' AND Parameter2 = 123 AND Value <> 'abc1'
END

- Kramb

---

我已将此值从1000更新为4000，目前似乎正常工作。在一个表中，我正在更新500万个记录（每10分钟更新约744,000个记录）。我正在开发服务器上运行此操作，并将尝试更新下一个拥有2600万个记录的表。目前正在寻找通过“多线程”加速此过程的方法。 - PHBeagle

这是低效的，因为存在检查是多余的。相反，您可以在运行UPDATE之后检索@@ROWCOUNT，如果@@ROWCOUNT <> BatchSize，则完成并退出循环。 - Shiv

@Shiv @@ROWCOUNT 是一个全局变量。诸如 USE、SET <option>、DEALLOCATE CURSOR、CLOSE CURSOR、PRINT、RAISERROR、BEGIN TRANSACTION 或 COMMIT TRANSACTION 等语句会将 ROWCOUNT 值重置为 0。 - Kramb

@Kramb 我知道 - 你可以像这个答案https://dev59.com/YlsV5IYBdhLWcg3wsQrf#55054293中所述的那样缓存行数。 - Shiv

Answer 3

昨天我遇到了这个线程，并根据被接受的答案编写了一个脚本。结果表现非常缓慢，处理了3300万行中的2500万行需要12个小时。今天早上我取消了它并与DBA一起改进。

DBA指出我的UPDATE查询中的"is null"检查在PK上使用聚集索引扫描，而正是这个扫描减慢了查询速度。基本上，查询运行的时间越长，它需要浏览索引以找到正确的行就越远。

他提出的方法事后看来很明显。基本上，您将要更新的行的ID加载到临时表中，然后在更新语句中将其连接到目标表。这将使用索引搜索而不是扫描。它真的加快了速度！更新最后的800万条记录只需要2分钟。

使用临时表进行分批处理

SET NOCOUNT ON

DECLARE @Rows INT,
        @BatchSize INT,
        @Completed INT,
        @Total INT,
        @Message nvarchar(max)

SET @BatchSize = 4000
SET @Rows = @BatchSize
SET @Completed = 0

-- #targetIds table holds the IDs of ALL the rows you want to update
SELECT Id into #targetIds 
FROM TheTable 
WHERE Foo IS NULL 
ORDER BY Id

-- Used for printing out the progress
SELECT @Total = @@ROWCOUNT

-- #batchIds table holds just the records updated in the current batch
CREATE TABLE #batchIds (Id UNIQUEIDENTIFIER);

-- Loop until #targetIds is empty
WHILE EXISTS (SELECT 1 FROM #targetIds)
BEGIN
    -- Remove a batch of rows from the top of #targetIds and put them into #batchIds
    DELETE TOP (@BatchSize)
    FROM #targetIds
    OUTPUT deleted.Id INTO #batchIds  

    -- Update TheTable data
    UPDATE t
    SET Foo = 'bar'
    FROM TheTable t
    JOIN #batchIds tmp ON t.Id = tmp.Id
    WHERE t.Foo IS NULL
    
    -- Get the # of rows updated
    SET @Rows = @@ROWCOUNT

    -- Increment our @Completed counter, for progress display purposes
    SET @Completed = @Completed + @Rows

    -- Print progress using RAISERROR to avoid SQL buffering issue
    SELECT @Message = 'Completed ' + cast(@Completed as varchar(10)) + '/' + cast(@Total as varchar(10))
    RAISERROR(@Message, 0, 1) WITH NOWAIT    

    -- Quick operation to delete all the rows from our batch table
    TRUNCATE TABLE #batchIds;
END

-- Clean up
DROP TABLE IF EXISTS #batchIds;
DROP TABLE IF EXISTS #targetIds;

批处理的慢速方式，不要使用！

参考下面这个性能较差的原始查询：

SET NOCOUNT ON

DECLARE @Rows INT,
        @BatchSize INT,
        @Completed INT,
        @Total INT

SET @BatchSize = 4000
SET @Rows = @BatchSize
SET @Completed = 0
SELECT @Total = COUNT(*) FROM TheTable WHERE Foo IS NULL

WHILE (@Rows = @BatchSize)
BEGIN

    UPDATE t
    SET Foo = 'bar'
    FROM TheTable t
    JOIN #batchIds tmp ON t.Id = tmp.Id
    WHERE t.Foo IS NULL

SET @Rows = @@ROWCOUNT
SET @Completed = @Completed + @Rows
PRINT 'Completed ' + cast(@Completed as varchar(10)) + '/' + cast(@Total as varchar(10))

END

- mikesigs

---

+1 我同意这是一个非常有效的方法，人们应该尝试一下。如果读者遇到像你在我的方法中遇到的性能问题，我已经更新了我的答案，直接引导他们到这里来。我以前用过这种模式，所以可能我更专注于解决 O.P. 方法中的缺陷。我对你的方法有一些小改进的建议，详见我的答案结尾。我没有提到的一件事是简化输出，可以通过以下方式实现：DECLARE @Completed INT = 5, @Total INT = 37; RAISERROR('Completed %d / %d', 10, 1, @Completed, @Total) WITH NOWAIT; - Solomon Rutzky

原始版本和新版本中的更新语句是相同的，它们都包含了你说是问题的“IS NULL”检查。为什么一个比另一个慢？此外，我没有看到在第二个（较慢的）版本中创建您的#batchIds表。那是不是应该引用其他表？ - Tyler Jones

我现在只使用SQL工作了几个月，所以我还不是很有经验。然而，如果我们可以在临时表上执行子查询而不是连接操作，关于索引id的解决方案应该是O(nlogm)而不是O(2*nlogm)，因为在连接发生后您不需要重新扫描表。因此，它将不再是一个连接，而是像这样：UPDATE t SET Foo = 'bar' FROM TheTable t WHERE t.Foo IS NULL AND EXISTS (SELECT id FROM #batchIds tmp WHERE t.id = tmp.id)。 - Anon

Answer 4

我希望分享我的经验。几天前，我需要在一张拥有7600万条记录的表格中更新2100万条记录。我的同事建议采用以下方案。 例如，我们有如下表格“人员”：

Id | FirstName | LastName | Email            | JobTitle
1  | John      |  Doe     | [e-mail]     | Software Developer
2  | John1     |  Doe1    | [e-mail]     | Software Developer
3  | John2     |  Doe2    | [e-mail]     | Web Designer

任务: 将职位为“软件开发人员”的人员更新为新的职位名称：“Web开发人员”。

1. 创建临时表 'Persons_SoftwareDeveloper_To_WebDeveloper (Id INT Primary Key)'

2. 选择要更新为新职位的人员，并将其插入到临时表中：

INSERT INTO Persons_SoftwareDeveloper_To_WebDeveloper SELECT Id FROM
Persons WITH(NOLOCK) --avoid lock 
WHERE JobTitle = 'Software Developer' 
OPTION(MAXDOP 1) -- use only one core

根据行数的不同，该语句需要一些时间来填充您的临时表，但它可以避免锁定。在我的情况下，它大约需要5分钟（2100万行）。

主要思路是生成微型SQL语句来更新数据库。因此，让我们将它们打印出来：

DECLARE @i INT, @pagesize INT, @totalPersons INT
    SET @i=0
    SET @pagesize=2000
    SELECT @totalPersons = MAX(Id) FROM Persons

    while @i<= @totalPersons
    begin
    Print '
    UPDATE persons 
      SET persons.JobTitle = ''ASP.NET Developer''
      FROM  Persons_SoftwareDeveloper_To_WebDeveloper tmp
      JOIN Persons persons ON tmp.Id = persons.Id
      where persons.Id between '+cast(@i as varchar(20)) +' and '+cast(@i+@pagesize as varchar(20)) +' 
        PRINT ''Page ' + cast((@i / @pageSize) as varchar(20))  + ' of ' + cast(@totalPersons/@pageSize as varchar(20))+'
     GO
     '
     set @i=@i+@pagesize
    end

执行此脚本后，您将收到数百个批次，可以在MS SQL Management Studio的一个选项卡中执行。 4. 运行打印的SQL语句并检查表上的锁定。您始终可以停止进程并使用@pageSize加速或减速更新(不要忘记在暂停脚本后更改@i)。 5. 删除Persons_SoftwareDeveloper_To_AspNetDeveloper。删除临时表。 小提示: 此迁移可能需要一些时间，并且可能会在迁移过程中插入包含无效数据的新行。因此，首先修复添加行的位置。在我的情况下，我修复了UI，“软件开发人员”->“Web开发人员”。有关此方法的更多信息，请参见我的博客https://yarkul.com/how-smoothly-insert-millions-of-rows-in-sql-server/。

- Yara

Answer 5

这是比@Kramb方案更高效的一个版本。存在检查是冗余的，因为更新子句已经处理了这个问题。相反，你只需要获取行数并与批量大小进行比较。

此外，请注意@Kramb的解决方案没有过滤掉下一次迭代中已更新的行，因此会导致无限循环。

还使用了现代的批处理大小语法，而不是使用行数。

DECLARE @batchSize INT, @rowsUpdated INT
SET @batchSize = 1000;
SET @rowsUpdated = @batchSize; -- Initialise for the while loop entry

WHILE (@batchSize = @rowsUpdated)
BEGIN
    UPDATE TOP (@batchSize) TableName
    SET Value = 'abc1'
    WHERE Parameter1 = 'abc' AND Parameter2 = 123 and Value <> 'abc1';

    SET @rowsUpdated = @@ROWCOUNT;
END

- Shiv

---

循环不会无限，因为它正在使用相同的参数更新结果集合并进行过滤。因此，以下结果集将不包括先前已更新的行。 - Kramb

@Kramb，你在更新Value并查看Parameter1和Parameter2。所以你并没有过滤你实际上正在更新的字段。我添加了第三个过滤条件来检查Value <> 'abc1'，这是你在答案中遗漏的。 - Shiv

请重试...我的回答清楚地表明，我“Exists”查询中的第一个条件实际上是检查确定“Value”是否等于内部查询正在设置“Value”的值的检查。 - Kramb

@Kramb 如果你的更新查询中前1000行已经设置了值，但是前1000行之外的行没有设置值，那么你的解决方案将会进入无限循环。你的解决方案存在缺陷，因为你在实际的UPDATE调用中缺少了where检查。exists检查不是问题所在。 - Shiv

Answer 6

你的print语句会导致问题，因为它重置了@@ROWCOUNT。每次使用@@ROWCOUNT时，我的建议是立即将其设置为变量。所以：

DECLARE @RC int;
WHILE @RC > 0 or @RC IS NULL
    BEGIN
        SET rowcount 5;

        UPDATE TableName
            SET Value  = 'abc1'
            WHERE Parameter1  = 'abc' AND Parameter2  = 123 AND Value <> 'abc1';

        SET @RC = @@ROWCOUNT;
        PRINT(@@ROWCOUNT)
    END;

SET rowcount = 0;

另一个不错的功能是您无需重复编写 update 代码。

- Gordon Linoff

---

请参考我上面发布的链接，了解如何使用ROWCOUNT来控制更新的行数。 - Sean Lange

@Gordon 我使用这个逻辑，查询运行了2分钟（仅针对14条记录！！！）。它进入了无限循环。 - GThree

@CSharper...嗯，如果没有更新行，则@@ROWCOUNT应为0，而不是NULL。无限循环的原因并不明显。 "print"产生了什么？如果“update”产生了“NULL”，那么可以通过将@RC设置为某个任意值，然后从“WHILE”中删除@RC IS NULL条件来修复它。 - Gordon Linoff

@GordonLinoff 的 print 语句会无限地输出 (5 行受影响) 1。 - GThree

@CSharper...“where”子句需要排除已经更新的行。 - Gordon Linoff

最后一行应该是 SET rowcount 0，没有等号。 - NibblyPig

Answer 7

首先，感谢大家的建议。我调整了我的查询-1并获得了我想要的结果。Gordon Linoff是正确的，PRINT搞乱了我的查询，所以我进行了以下修改：

修改后的查询-1：

SET ROWCOUNT 5
WHILE (1 = 1)
  BEGIN
    BEGIN TRANSACTION

        UPDATE TableName 
        SET Value = 'abc1' 
        WHERE Parameter1 = 'abc' AND Parameter2 = 123

        IF @@ROWCOUNT = 0
          BEGIN
                COMMIT TRANSACTION
                BREAK
          END
    COMMIT TRANSACTION
  END
SET ROWCOUNT  0

输出：

(5 row(s) affected)

(5 row(s) affected)

(4 row(s) affected)

(0 row(s) affected)

- GThree