许多个人查询诉一个大的 [英] Many Individual Queries v. One Big One

问题描述

我处于表中的整个列（用于用户令牌）需要擦除的情况，即所有用户令牌都会同时重置。有两种方法：使用单独的UPDATE查询重置每个用户的令牌;或者做一个影响所有行的大查询。

I'm in a situation where an entire column in a table (used for user tokens) needs to be wiped, i.e., all user tokens are reset simultaneously. There are two ways of going about it: reset each user's token individually with a separate UPDATE query; or make one big query that affects all rows.

一个大查询的优点是它显然会更快，但我担心大UPDATE查询时数据库大。在查询期间发生的请求会受到影响吗？

The advantage of one big query is that it will obviously be much faster, but I'm worried about the implications of a large UPDATE query when the database is big. Will requests that occur during the query be affected?

推荐答案

恐怕不是那么简单。即使启用脏读取，运行一个大更新也有很多缺点：

Afraid it's not that simple. Even if you enable dirty reads, running one big update has a lot of drawbacks:

• 更新一个列的长时间运行事务将有效阻止其他插入，更新和删除事务。


• 长时间运行的事务会导致 enourmous 加载磁盘，因为服务器必须写入日志文件中发生的一切，以便您可以回滚这个巨大的事务。


• 如果交易失败，您必须完全重新执行，不能重新启动。

• long running transaction that updates one column will effectively block other insert, update and delete transactions.
• long running transaction causes enourmous load on disk because server is having to write to a log file everything that is taking place so that you can roll back that huge transaction.
• if a transaction fails, you would have to rerun it entirely, it is not restartable.

因此，如果同时要求可以解释为在一个批处理中可能需要一段时间运行，我将选择批处理。关于MySql中DELETE的性能的良好研究在这里： http://mysql.rjweb.org /doc.php/deletebig ，我认为大多数发现适用于UPDATE。

So if simultaneous requirement can be interpreted "in one batch that may take a while to run", I would opt for batching it. A good research write up on performance of DELETEs in MySql is here: http://mysql.rjweb.org/doc.php/deletebig, and I think most of the findings are applicable to UPDATE.

诀窍将是寻找最佳的批量大小。

The trick will be finding the optimal "batch size".

添加批处理的好处是，您可以使此过程适应故障和重新启动友好。

Added benefits of batching is that you can make this process resilient to failures and restart-friendly.

这篇关于许多个人查询诉一个大的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！