我的MariaDB UPDATE中是否有任何明显的瓶颈？-解网

问：

我有一个包含 2800 万行的 MariaDB 表。我需要用列中的新值更新所有行（本地）。这是 Python 中批量更新的代码：

update_query = "UPDATE table SET column = %s WHERE `index` = %s"
%time cursor.executemany(update_query, update_data)

column具有索引，并且是主键。index

当我使用一批 100,000 行时，运行 .我不确定这是很慢还是很正常。如果它很慢，我不知道从哪里开始加速。executemany()

蟒蛇 mysql mariadb

评论

0赞 user207421 11/9/2023

你可以从更小和更大的批量开始。钥匙有多长？

0赞 Tangentially Perpendicular 11/9/2023

如果已编制索引，则每次更新也需要更新索引。您可能会发现，在执行此更新时删除索引，然后在完成后重新创建索引会更快。columncolumn

0赞 Askhat Bilyal 11/9/2023

我想你需要将这个任务划分为并行任务。获取 2800 万行，并将它们划分为将更新 100k 行或更少的任务，并在不同的线程中运行它们

0赞 Dima 11/9/2023

@user207421 无论批量大小如何，完成时间都非常一致。100,000 大约是 10,000 的 10 倍。不过，我不确定你说的钥匙是什么意思。

1赞 Tangentially Perpendicular 11/9/2023

@Dima 不一定。如果对表执行 100,000 次更新，则需要对索引进行 100,000 次更新。重新索引整个表只会更新一次，尽管是一次重大更新。这不是保证的修复，但您可能会发现它更快。试试看。

答：

0赞 Barmar 11/9/2023 #1

与不同，无法自动批处理查询，因此瓶颈是执行 100,000 次查询。INSERTexecutemany()UPDATE

请改用 .这将自动批处理。INSERT INTO ... ON DUPLICATE KEY UPDATE ...

update_query = 'INSERT INTO table (column, index) VALUES (%s, %s) ON DUPLICATE KEY UPDATE column = VALUES(column)'
%time cursor.executemany(update_query, update_data)

评论

0赞 Dima 11/9/2023

太好了，这确实将它从 50 秒缩短到 35 秒！我希望我能把它控制在5秒以上，我只是不确定我得到的时间是否合理，或者我的服务器是否有问题。但是，这仍然是一个很大的改进，谢谢！

0赞 ysth 11/9/2023

您可以尝试将所有索引和新值批量插入到临时表中，然后执行单个更新以连接该表。fwiw 如果它之前进行了 100000 次单独更新，现在正在批处理插入物，我本来预计会有比 50 到 35 秒更大的改进

0赞 Dima 11/9/2023

@ysth好的，谢谢，我会试一试。

1赞 Barmar 11/9/2023

在某些时候，您受到 I/O 速度的限制，无法实际更新所有这些行。

上一个：在 mysql 命令行中使用 mysqldump 进行备份时出错

下一个：MySQL数据库自动转换为MariaDB [重复]