sql-server - 如何更新在具有超过2.5亿行的表中创建的2个新列

Question

我必须向具有超过2.5亿行的表中添加2个新列col1 char(1) NULL，col2 char(1) NULL。我必须为现有的2.5亿行用值1更新两列。

然后，我的SSIS软件包将每天以递增顺序更新表。 SSIS包将使用源表中的任何内容填充这两列。

由于必须更新2.5亿行，如何快速完成此操作？

谢谢，
男子

Answer 1

您没有说您正在使用的SQL Server版本。从SQL Server 2012开始，在大多数情况下，默认添加新的NOT NULL列为instantaneous：仅更改表元数据，并且不更新任何行。感谢Martin Smith提供此信息。因此，在此版本中，最好删除并重新创建列。

在以前的版本中，您可以尝试这样的操作：

WHILE 1 = 1 BEGIN
   WITH T AS (
      SELECT TOP (10000) *
      FROM dbo.YourTable
      WHERE
         T.Col1 IS NULL
         AND T.COl2 IS NULL
   )
   UPDATE T
   SET
      T.Col1 = '1',
      T.Col2 = '1'
   ;
   IF @@RowCount < 10000 BREAK; -- a trick to save one iteration most times
END;

这可能需要花费很长时间才能运行，但是它的好处是它将不会长时间在表上保持锁。索引的正确组合和通常的行大小也将影响其性能。要更新的行数的最佳位置永远不会恒定。可能是50,000或2,000。过去，我曾在像这样的分块操作中尝试过不同的计数，发现通常有5,000或10,000个接近最佳大小。

根据SQL Server的版本（2008及更高版本），上述查询还可以通过过滤索引而受益：

CREATE UNIQUE NONCLUSTERED INDEX IX_YourTable ON dbo.YourTable (ClusteredColumns)
   WHERE Col1 IS NULL AND COl2 IS NULL;

完成后，删除索引。

请注意，如果您用默认值和 NOT NULL指定了两个新列，它们将在创建列时添加值-然后可以删除默认值：

ALTER TABLE dbo.YourTable ADD Col1 char(1)
   NOT NULL CONSTRAINT DF_YourTable_Col1 DEFAULT ('1');

与在末尾添加 NULL列不同（可以随意拆分），这可能要花费大量时间，因此在250M行表中，这可能不是一个选择。

更新要解决布莱恩的评论：


小批量进行10,000次的理由是，可以大大缓解更新的“开销”带来的负面影响。是的，确实，这将是很多活动，但不会持续很长时间，这就是这样的活动对性能的第一影响：长时间阻止。
我们对该查询的锁定潜力有很多了解：UPDATE EXCLUSIVE锁定，并且先前的点应将由此产生的任何有害影响降至最低。如果还有其他我想念的锁定问题，请分享。
过滤后的索引会有所帮助，因为它仅允许读取索引的几页，然后搜索巨型表。由于更新为true，因此必须维护过滤索引以删除更新的行，因为它们不再符合条件，这确实增加了更新的写部分的成本。这听起来很糟糕，直到您意识到上面批次的 UPDATE的最大部分（没有某种索引）将每次都是表扫描。给定250M行，则需要与对整个表进行12,500次完整扫描相同的资源！！！因此，我的建议是使用索引DOES起作用，并且是手动遍历聚集索引的一种不错而又简便的捷径。
它们对具有大量写操作的表不利的“索引基本定律”在这里不成立。您正在考虑正常的OLTP访问模式，在该模式下，可以通过查找找到要更新的行，然后进行写操作，表上的每个其他索引确实会创建以前不存在的开销。将此与我之前的解释进行比较。即使过滤后的索引使 UPDATE部分每行的I / O占用5倍（可疑），这仍将使I / O减少2500倍以上！！！


评估更新对性能的影响很重要，尤其是在表非常繁忙且不断被使用的情况下。如您所建议的那样，如果需要，将其安排在下班时间（如果有的话）是基本意义。

我建议的一个潜在弱点是，在SQL 2008及更低版本中，添加过滤后的索引可能会花费很长时间-尽管可能不会，因为它是一个非常狭窄的索引，并且将以聚簇顺序写入（可能只有一次扫描） ！）。因此，如果创建时间确实太长，则有另一种选择：手动遍历聚集索引。可能看起来像这样：

DECLARE @ClusteredID int = 0; --assume clustered index is a single int column
DECLARE @Updated TABLE (
   ClusteredID int NOT NULL
);

WHILE 1 = 1 BEGIN
   WITH T AS (
      SELECT TOP (10000) *
      FROM dbo.YourTable
      WHERE ClusteredID > @ClusteredID -- the "walking" part
      ORDER BY ClusteredID -- also crucial for "walking"
   )
   UPDATE T
   SET
      T.Col1 = '1',
      T.Col2 = '1'
   OUTPUT Inserted.ClusteredID INTO @Updated
   ;

   IF @@RowCount = 0 BREAK;

   SELECT @ClusteredID = Max(ClusteredID)
   FROM @Updated
   ;

   DELETE @Updated;
END;

到此为止：没有索引，一直搜索，只有对整个表的一次有效扫描（处理表变量的开销很小）。如果 ClusteredID列排得很紧，您甚至可以省去table变量，而只需在每个循环的末尾手动添加10,000。

您提供了一个更新，您的聚集索引中有5列。这是一个更新的脚本，显示了您可能如何适应它：

DECLARE -- Five random data types seeded with guaranteed low values
   @Clustered1 int = 0,
   @Clustered2 int = 0,
   @Clustered3 varchar(10) = '',
   @Clustered4 datetime = '19000101',
   @Clustered5 int = 0
;

DECLARE @Updated TABLE (
   Clustered1 int,
   Clustered2 int,
   Clustered3 varchar(10),
   Clustered4 datetime,
   Clustered5 int
);

WHILE 1 = 1 BEGIN
   WITH T AS (
      SELECT TOP (10000) *
      FROM dbo.YourTable
      WHERE
         Clustered1 > @Clustered1
         OR (
            Clustered1 = @Clustered1
            AND (
               Clustered2 > @Clustered2
               OR (
                  Clustered2 = @Clustered2
                  AND (
                     Clustered3 > @Clustered3
                     OR (
                        Clustered3 = @Clustered3
                        AND (
                           Clustered4 > @Clustered4
                           OR (
                              Clustered4 = @Clustered4
                              AND Clustered5 > @Clustered5
                           )
                        )
                     )
                  )
               )
            )
         )
      ORDER BY
         Clustered1, -- also crucial for "walking"
         Clustered2,
         Clustered3,
         Clustered4,
         Clustered5
   )
   UPDATE T
   SET
      T.Col1 = '1',
      T.Col2 = '1'
   OUTPUT
      Inserted.Clustered1,
      Inserted.Clustered2,
      Inserted.Clustered3,
      Inserted.Clustered4,
      Inserted.Clustered5
   INTO @Updated
   ;

   IF @@RowCount < 10000 BREAK;

   SELECT TOP (1)
     @Clustered1 = Clustered1
     @Clustered2 = Clustered2,
     @Clustered3 = Clustered3,
     @Clustered4 = Clustered4,
     @Clustered5 = Clustered5
   FROM @Updated
   ORDER BY
      Clustered1,
      Clustered2,
      Clustered3,
      Clustered4,
      Clustered5
   ;

   DELETE @Updated;
END;

如果您发现一种特定的方式行不通，请尝试另一种方式。更深入地了解数据库系统将带来更好的想法和更好的解决方案。我知道深度嵌套的 WHERE条件很笨拙。您也可以尝试以下方法来确定大小-效果完全相同，但是很难理解，因此即使添加额外的列很容易，我也不推荐这样做。

WITH T AS (
   SELECT TOP (10000) *
   FROM
      dbo.YourTable T
   WHERE
      122 <=
         CASE WHEN Clustered1 > @Clustered1 THEN 172 WHEN Clustered1 = @Clustered1 THEN 81 ELSE 0 END
         + CASE WHEN Clustered2 > @Clustered2 THEN 54 WHEN Clustered1 = @Clustered2 THEN 27 ELSE 0 END
         + CASE WHEN Clustered3 > @Clustered3 THEN 18 WHEN Clustered3 = @Clustered3 THEN 9 ELSE 0 END
         + CASE WHEN Clustered4 > @Clustered4 THEN 6 WHEN Clustered4 = @Clustered4 THEN 3 ELSE 0 END
         + CASE WHEN Clustered5 > @Clustered5 THEN 2 WHEN Clustered5 = @Clustered5 THEN 1 ELSE 0 END
   ORDER BY
      Clustered1, -- also crucial for "walking"
      Clustered2,
      Clustered3,
      Clustered4,
      Clustered5
)
UPDATE T
SET
   T.Col1 = '1',
   T.Col2 = '1'
OUTPUT
   Inserted.Clustered1,
   Inserted.Clustered2,
   Inserted.Clustered3,
   Inserted.Clustered4,
   Inserted.Clustered5
INTO @Updated
;

我已经多次使用这种精确的“小批量聚集索引”策略对巨型表执行更新，而对生产数据库没有不良影响。