主页 / dba / 问题 / 90208
Accepted
Andy
Asked: 2015-01-27 08:19:24 +0800 CST

更新大表（约 650 万行）和索引

  • 772

我试图在合理的时间内更新一个包含大约 650 万行的表，但我遇到了一些问题。我们正在向现有表中添加一个新列，然后根据另一个表中的列中的数据为所有行设置一个值。 

UPDATE  TOP (20000) c
SET     c.NewColumn = ISNULL(p.Col1, p.Col2)
FROM    dbo.Child c
    INNER JOIN dbo.Parent p on c.FKId = p.Id
WHERE   c.NewColumn IS NULL

在一个循环中，就像这篇文章。2.5 小时后更新仍在运行。我想知道禁用索引是否dbo.Child会产生影响。NewColumn 没有索引，也不会，但是上面还有其他索引（大约5个）dbo.Child

SQL Server 是否足够聪明，可以看到它不需要更新其他索引（因为它们不是 UPDATE 的一部分），或者我们是否会在执行更新语句时从暂时禁用索引中受益？

这是 SQL Server 2012，但有问题的数据库处于 2008 兼容模式。

index sql-server-2012

2 个回答

  1. 您说 UPDATE 正在循环运行，并且在 2.5 小时后仍在运行。该循环是否更新了任何行？是循环本身需要很长时间还是单个 UPDATE 语句？这是一个有点重要的区别，目前在给定的信息中是模棱两可的。但是，根据问题中所说的内容，需要注意以下几点：

    • 不，SQL Server 不应该更新不使用新列的索引

    • 禁用索引不仅仅是关闭它们的问题。它实际上删除了所有索引页并仅保留结构，因此您无需再次运行完整的 CREATE 语句。但是重新启用索引将不得不重建它。

    这里有一些事情要寻找：

    • 表上是否有任何 UPDATE 触发器Child？

    • 事务日志上的自动增长设置是什么？如果设置为非常低的数字，则可能是 UPDATE 受到大量自动增长操作的阻碍。

    • 在Child表上的这 5 个索引中，它们中的任何一个都有FKId作为前导列吗？

    • 您是否尝试过较小的批量大小，或者只有 20k？我看到有几个人随机推荐这些类型的操作使用大批量（10k - 50k），而没有考虑到它不仅将操作限制在该数量，而且还向查询优化器提示该数量可能命中第一名。因此，如果只有 5000 行要查找，那么它可能会继续查看这些行以查看是否有其他行符合c.NewColumn IS NULL条件。并且考虑到该字段没有被索引，所有 QO 必须继续是自动生成的统计信息，它不会告诉它在哪里可以找到剩余的NULL行。假设循环的前几次迭代确实成功了，SQL Server 仍然需要在 650 万行中找到 20,000 行为 NULL，方法是在找到它们之前不分特定顺序地扫描尽可能多的行。因此，最初的几次迭代可能发生得很快，但是这种类型的操作很快就会变慢，因为每次连续通过需要扫描越来越多的记录才能找到符合IS NULL条件的 20,000 条记录。

      因此，需要考虑两件事：

      • 从一个小批量开始，可能是 10 行，然后单独运行该语句（即不在循环中）。它会回来吗？如果不是，那么您需要回答@Aaron 关于 WAIT_RESOURCE 是什么的问题。如果它确实返回，则将批量大小增加到 100，然后是 500、1000、2000、5000，然后是 10000。这将需要更多的循环才能完成，但是找到要更新的所需记录数将更容易完成。一旦你找到一个看起来反应灵敏的尺寸，你就可以再次运行循环（我猜在 2000 - 5000 范围内的某个地方会起作用:)

      • 您可能实际上想要临时创建一个索引来支持这个特定的 UPDATE 操作，而不是摆脱索引。过滤索引（在 SQL Server 2008中引入，因此兼容模式不应该造成问题）将允许您定位剩余的行以进行更新。就像是：

        CREATE NONCLUSTERED INDEX [IX_Child_FKId_temp]
  ON dbo.Child ([FKId] ASC) -- you might need to include [NewColumn] ASC after [FKId]
  WHERE [NewColumn] IS NULL
  WITH ( FILLFACTOR = 100 );
    • 2
  2. Best Answer

    我忘记了这个问题，对不起。但是我们解决了这个问题。使用 MERGE 语句将查询时间减少到一个小时以下。禁用索引没有效果。

    • 0

相关问题