问为什么批量插入/更新更快？批量更新是如何工作的？

EN

我在寻找关于“批量/批量”更新的相同主题的答案。人们经常通过将其与具有多个值集的insert子句( "bulk“部分)进行比较来描述问题。

INSERT INTO mytable (mykey, mytext, myint)
VALUES 
  (1, 'text1', 11),
  (2, 'text2', 22),
  ...

明确的答案仍然在回避我，但我在这里找到了解决方案：http://www.postgresql.org/docs/9.1/static/sql-values.html

为了说明这一点：

UPDATE mytable
SET 
  mytext = myvalues.mytext,
  myint = myvalues.myint
FROM (
  VALUES
    (1, 'textA', 99),
    (2, 'textB', 88),
    ...
) AS myvalues (mykey, mytext, myint)
WHERE mytable.mykey = myvalues.mykey

它具有相同的属性，即“批量”，也就是在一条语句中包含大量数据。

为什么批量插入更快？

原因有很多，但主要有三点：

• 查询不需要重新解析。
• 这些值在一个往返过程中传输到服务器
• 命令位于单个事务

内

是因为插入单行的连接和设置开销对于一组行是相同的吗？

部分是这样的，见上。

批量更新是如何工作的？

这取决于RDBMS。

在Oracle中，您可以将所有值作为集合传输，并将此集合用作JOIN中的表。

在PostgreSQL和MySQL中，您可以使用以下语法：

INSERT
INTO    mytable
VALUES 
        (value1),
        (value2),
        …

您还可以准备一次查询，然后在某种循环中调用它。通常，在客户端库中有一些方法可以做到这一点。

假定表没有唯一性约束，insert语句实际上对批处理中的其他insert语句没有任何影响。但是，在批处理更新期间，update可能会改变表的状态，因此可能会影响批处理中其他update查询的结果。

是的，你可能会从这种行为中受益，也可能不会从中受益。

我知道batch insert查询的语法是将所有的insert值放在一个大查询中。批量更新查询是什么样子的？

在Oracle中，您可以在联接中使用集合：

MERGE
INTO    mytable
USING   TABLE(:mycol)
ON      …
WHEN MATCHED THEN
UPDATE
SET     …

在PostgreSQL中

UPDATE  mytable
SET     s.s_start = 1
FROM    (
        VALUES
        (value1),
        (value2),
        …
        ) q
WHERE   …

其他帖子解释了为什么批量语句更快，以及如何使用字面值来实现。

我认为知道如何使用占位符是很重要的。不使用占位符可能会导致巨大的命令字符串，引用/转义错误，从而导致容易出现SQL注入的应用程序。

PostgreSQL >= 9.1中带占位符的大容量插入

要在表"mytable“中插入任意数量的行(由列"col1”、"col2“和"col3”组成)，所有行都放在一个got中(一个语句，一个事务)：

INSERT INTO mytable (col1, col2, col3)
 VALUES (unnest(?), unnest(?), unnest(?))

您需要为该语句提供三个参数。第一个必须包含第一列的所有值，依此类推。因此，所有参数都必须是长度相等的列表/向量/数组。

在PostgreSQL >= 9.1中使用占位符进行批量更新

比方说，您的表名为"mytable“。它由"key“和"value”两列组成。

update mytable 
  set value = data_table.new_value
  from 
    (select unnest(?) as key, unnest(?) as new_value) as data_table
  where mytable.key = data_table.key

我知道，这不容易理解。它看起来像是模糊的SQL。另一方面:它工作，它的规模，它的工作没有任何字符串连接，它是安全的，它是惊人的快速。

您需要为该语句提供两个参数。第一个必须是一个列表/向量/数组，其中包含列"key“的所有值。当然，第二个必须包含列"value“的所有值。

如果遇到大小限制，您可能需要查看COPY INTO ... FROM STDIN (PostgreSQL)。