关于mysql：运行10,000个查询每个更新单行，还是运行100个查询每个更新100行更快？

Is it faster to run 10,000 queries each updating a single row, or 100 queries each updating 100 rows?

假设我有一个主键列表，对于每一行，一个值需要更新。跑起来更好吗：

1 2	— run 10,000 of these queries UPDATE mytable SET myflag = 1 WHERE id = [each_id]

或者像这样将更新组合成批处理查询：

1 2	— run 100 of these queries, where the IN () list contains about 100 elements UPDATE mytable SET myflag = 1 WHERE id IN (3, 4, 5, 9, 99, … 7887 )

100 个 IN () 项的 100 个查询怎么样？

相关讨论

做一些基准测试？如果您可以在您的系统上对其进行测试，它将是最准确的
它在很大程度上取决于您是否使用事务/每个 UPDATE 或大约每 100 个事务等。
好吧，测试这个非常容易……但是，只要你的 IN() < max_allowed_packet，特别是如果 myflag 上有一个键，它可能会更快。
nitpick：第一个案例更新 10k 行，第二个 100k 行，不确定这是否是故意的？
如果每次更新都使用单独的事务，那么一次多条记录将在 PostgreSQL 中以压倒性优势获胜，这仅仅是因为事务数量减少了。似乎这个长长的数字列表可能包含在某个表中，如果可能，请在 WHERE 子句中对该表使用选择。
@fvu：我修正了错误的数字。

也没有。在 PostgreSQL 中，我会改为：

1
2
3
4
5
6
7
8
9
10
11
12

WITH x AS (
SELECT unnest(‘{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20
,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40
,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60
,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80
,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100
}’::int[]) AS id
)
UPDATE mytable t
SET myflag = 1
FROM x
WHERE t.id = x.id;

我在我的示例中放置了这么多 ID，以提供一个直观的线索，即 10000 个 ID 很多。问题中提出的两个想法是：

必须解析列表并将 10000 条语句放在一起并将它们发送到服务器，这可能比 UPDATE 本身花费更长的时间。

必须在包含 10000 个项目的列表(数组)中为 mytable 中的每个单独的 id 搜索匹配的 id。不能使用标准索引。这将非常缓慢。性能随着 mytable.

的大小而降低

mytable.id 上的索引是所有提出的替代方案都需要优于两个变体一个数量级。

CTE 对数组进行一次解析(子查询也有效——MySQL 没有 CTE)——而 unnest() 在这方面相当快。在一个语句中完成所有操作比 10000 个语句高出一个数量级。如果这些语句在单个事务中运行，则再增加一个数量级。如果您应该使用单独的会话，请添加另一个。

罕见的例外情况适用于在高写入负载下存在锁定问题的数据库。只是按照建议进行基准测试。 EXPLAIN ANALYZE 是你在 PostgreSQL 中的朋友。

如果操作变得庞大，并且大部分表都已更新和/或您的磁盘空间或 RAM 不足，将操作分成几个逻辑块可能仍然是个好主意 – 只是不要太多，找到甜蜜点。主要是为了让 HOT 更新从以前的 UPDATE 运行中回收表膨胀。考虑这个相关的问题。

相关讨论

仅出于完整性考虑：可写 CTE 仅适用于 PostgreSQL 9.1 或更高版本。
我可能会选择 VALUES (1),(2),(3),… 而不是创建和取消嵌套数组。

我发现第二种方法在对非常大的数据集进行插入时速度要快几个数量级。这非常依赖于您的系统，但是查询的 IN 部分的效率或多或少取决于表大小、索引等。

自己做简单的基准测试确实是唯一的方法。

在正常情况下，运行一条更新语句是最有效的。例如，

1	UPDATE mytable set myflag=1 where id IN (select id from someothertable where stuff).

根据您的架构，这可能会更慢。您应该进行基准测试并找出答案。

注意，从客户端到数据库服务器运行 10,000 条语句几乎肯定会更慢。在存储过程中运行 10,000 次更新和从客户端运行 10,000 次更新是两个非常不同的事情。如果您要走 10,000 次更新路线，请确保在 SP 中进行。

通常，RDBMS 往返是一个主要因素，但在这种情况下，解析 in 列表的成本也可能会很高。但是，如果您将查询参数化，则第二种解决方案很可能会更快，因为解析只会进行一次。

相关讨论

可能跑题了，但是如何参数化一个变量列表参数呢？换句话说，类似于 IN (?) 然后将可变长度绑定到 ?。
@MichaelButler 不幸的是，不：您创建了一个包含 100 个单独 ? 的列表；然后你分别绑定每个变量。当您执行最后一个”奇数”批处理时，您将生成另一个 SQL，其中 ? 的数量与剩余数量一样多。

这主要取决于硬盘的 fsync 数量：这是系统中最慢的部分。

对于 PostgreSQL：在少量事务中进行，如果可能的话，只在一个事务中。但是要注意行锁定，两个事务不能同时更新同一行。

相关讨论