sql - PostgreSQL 中的批量/批量更新/更新插入

Question

我正在编写一个 Django-ORM 增强功能，它尝试缓存模型并将模型保存推迟到事务结束。一切都快完成了，但是我在 SQL 语法方面遇到了意想不到的困难。

我不是 DBA，但据我了解，对于许多小查询，数据库并不能真正有效地工作。很少有更大的查询会好得多。例如，最好使用大批量插入(比如一次插入 100 行)而不是 100 个单行插入。

现在，据我所知，SQL 并没有真正提供任何语句来对表执行批量更新。该术语似乎令人困惑 所以，我将解释我的意思。我有一个任意数据数组，每个条目描述表中的一行。我想更新表中的某些行，每一行都使用数组中对应条目的数据。这个想法与批量插入非常相似。

例如:我的表可能有两列 "id" 和 "some_col"。现在，描述批量更新数据的数组包含三个条目 (1, 'first updated')、(2, 'second updated') 和 (3, '第三次更新')。更新前表包含行:(1, 'first'), (2, 'second'), (3, 'third').

我看到了这个帖子:

Why are batch inserts/updates faster? How do batch updates work?

这似乎做了我想做的事，但最后我无法真正弄清楚语法。

我也可以删除所有需要更新的行并使用批量插入重新插入它们，但是我很难相信这实际上会执行得更好。

我使用 PostgreSQL 8.4，所以这里也可以使用一些存储过程。但是，由于我计划最终开源该项目，因此欢迎任何更具可移植性的想法或方法在不同的 RDBMS 上做同样的事情。

跟进问题:如何执行批量“insert-or-update”/“upsert”语句？

测试结果

我在 4 个不同的表上执行了 100 次 10 次插入操作(因此总共 1000 次插入)。我在带有 PostgreSQL 8.4 后端的 Django 1.3 上进行了测试。

结果如下:

• 通过 Django ORM 完成的所有操作 - 每次通过 ~2.45 秒，
• 相同的操作，但没有使用 Django ORM - 每次通过 ~1.48 秒，
• 仅插入操作，不查询数据库的序列值 ~0.72 秒,
• 仅插入操作，以 10 个 block 为单位执行(总共 100 个 block )~0.19 秒，
• 仅插入操作，一个大的执行 block ~0.13 秒。
• 仅插入操作，每个 block 大约 250 条语句，~0.12 秒。

结论:在单个connection.execute()中执行尽可能多的操作。 Django 本身引入了大量开销。

免责声明:除了默认主键索引之外，我没有引入任何索引，因此插入操作可能因此运行得更快。

Answer 1

批量插入

可以通过@Ketema修改三列的批量插入:

INSERT INTO "table" (col1, col2, col3)
  VALUES (11, 12, 13) , (21, 22, 23) , (31, 32, 33);

它变成了:

INSERT INTO "table" (col1, col2, col3)
  VALUES (unnest(array[11,21,31]), 
          unnest(array[12,22,32]), 
          unnest(array[13,23,33]))

用占位符替换值:

INSERT INTO "table" (col1, col2, col3)
  VALUES (unnest(?), unnest(?), unnest(?))

您必须将数组或列表作为参数传递给此查询。这意味着您可以在不进行字符串连接的情况下进行大量的批量插入(及其所有的麻烦和危险:sql 注入(inject)和引用 hell )。

批量更新

PostgreSQL 已将 FROM 扩展添加到 UPDATE。你可以这样使用它:

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

手册缺少很好的解释，但在 postgresql-admin mailing list 上有一个示例.我试图详细说明:

create table tmp
(
  id serial not null primary key,
  name text,
  age integer
);

insert into tmp (name,age) 
values ('keith', 43),('leslie', 40),('bexley', 19),('casey', 6);

update tmp set age = data_table.age
from
(select unnest(array['keith', 'leslie', 'bexley', 'casey']) as name, 
        unnest(array[44, 50, 10, 12]) as age) as data_table
where tmp.name = data_table.name;
 

还有other posts在 StackExchange 上解释 UPDATE...FROM.. 使用 VALUES 子句而不是子查询。它们可能更易于阅读，但仅限于固定数量的行。