主页 / dba / 问题 / 318105
Accepted
TheProgrammer
Asked: 2022-10-12 18:53:55 +0800 CST

在单个表上运行数千个更新查询的最佳方法

  • 772

我有一个脚本，它运行大约 60k 更新查询，其中包含近 500 万条记录的表上的 where 子句。更新查询很可能会更新每个案例的所有行。有没有更好的方法来优化它，因为它目前已经运行了几个小时？（myTable 中的 where 子句列没有索引，但它确实有主键）

update myTable set Col1 = Val1 where Col1 = Unq1
update myTable set Col2 = Val4 where Col2 = Unq23
update myTable set Col3 = Val8 where Col3 = Unq45
.......
postgresql postgresql-performance

2 个回答

  1. Best Answer

    myTable 中的 where 子句列上没有索引

    这是一个问题，因为您的桌子不是很小。

    有一个主键，但是我想如果 where 子句中的列上有一个索引，它会使更新变慢吗？

    这会使实际更新变慢，因为索引页面和基础数据页面一样需要更新，但是数据库引擎首先需要找到要更新的行，而索引的存在极大地帮助了这一点。

    col2如果语句上没有索引，update myTable set Col2 = Val4 where Col2 = Unq23则会扫描整个表，所有 5,000,000 行，以查找Col2 = Unq23. 即使表完全在内存中，因此很少或根本不涉及 IO，这也会消耗 CPU 时间，因此需要花费大量时间，然后您需要将其乘以 60,000 次执行。

    没有索引可以为您节省每次页面写入次数update，但每次读取页面将花费您数万或数十万次update。

    有没有更好的方法来优化这个？

    在谓词中引用同一列的批处理更新WHERE可能会给您带来一些性能优势，但我希望添加索引以支持单个更新将有更大的好处，所以我建议在尝试做任何更复杂的事情之前解决这个问题。一旦索引到位，如果性能仍然太慢，则考虑重构该过程以一起批量更新。

    评论中要求的额外细节：

    多谢告诉我这个消息！我在 where 子句中添加了索引。运行 1000 个行块似乎没问题，但有些块需要几毫秒，有些则需要 10 分钟。

    您需要进行一些诊断来验证，但我首先想到的是，如果数据库正在主动服务其他请求，那么运行时间较长的批处理被其他长时间运行的语句所持有的锁所阻塞。当其中一个慢速运行时，您可以通过在该数据库中运行来检查这EXEC sp_who2一点，您将在列中看到您的任务BlkBy（该信息是持有它的连接的 SPID）。有关更多信息，您可以深入了解各种系统视图，或使用sp_whoisactive †，它会为您完成大量工作‡。

    [†] 有几个类似的实用程序脚本/程序，这是我用过的一个

    [‡] 我确实建议您自己（在时间允许的情况下）浏览一下系统管理视图，以更好地了解它们在做什么，而不是仅仅将这些脚本视为有用的黑匣子，如果可以的话，您就会知道应该手动执行更多操作' t 在将来的某个地方使用 sp_whoisactive 之类的东西

    如果您当时是该数据库的唯一用户，或者问题似乎不是由这样的锁定引起的，请检查 CPUTime 和 DiskIO 列sp_who*- 那些累积将意味着某些更新仍在扫描而不是在索引中查找，或者某些更新正在修改比其他更新更多的行数。

    将我更新的值输入到表中然后从那里进行更新连接对我来说会更好吗？

    最有可能的是，因为它会减少读取的页面数量并且能够将写入合并为更少，但请注意您正在向填充此保持表的数据库添加额外的写入，因此也要注意高效地执行此操作。还要确保该表具有适当的索引以帮助 update-from-join 语句并将插入批处理到该表中。如果这是一次性（或罕见）的一组更新，如果速度缓慢但可以接受，我可能会避免使过程复杂化。

    • 3

  2. 我想如果 where 子句中的列上有索引，它会使更新变慢吗？

    不，实际上恰恰相反。数据库引擎需要根据您的WHERE子句找到您要更新的行。索引组织数据，通常采用 B-Tree 数据结构，按您在其中指定的字段排序。B 树具有O(Log(n))搜索时间复杂度。索引谓词字段（JOIN、、WHERE和HAVING子句）可以组织数据，以便WHERE子句可以最有效地运行。

    如果没有索引，则需要扫描整个表以查找您的WHERE子句正在过滤的行。扫描 500 万行表 6 万次不会很快。事实上，这是一个O(n)搜索时间复杂度操作，这意味着它比您的表具有适当的索引来搜索要慢得多。

    与索引的权衡是，当在表中插入新行或索引的字段发生更改时，需要进行额外的写入操作以保持更新。所以是的，根据您的查询，您正在更新与索引相同的字段。但额外的写入开销可能是值得的，以加快定位要更新的行的时间。

    此外，由于索引的写入开销，索引过多和过少一样都是一个问题。我不确定您的 6 万条更新语句是否都在一组独特的字段上进行了过滤。（尽管一个表中的 60000 个字段无论如何都是糟糕的设计，所以我希望不会。）但是您肯定也不希望向表中添加 60000 个索引。精心设计索引以包含正确的字段组合，以最大限度地增加它们覆盖的查询数量，这一点很重要。

    • 2

相关问题