mysql - postgresql 批量插入性能问题(相对于 mysql)

Question

我有很多数据，我想至少一次插入到数据库中。我做一些测试。我创建了一个包含 21 列的表(使用下面的脚本 *1)。 1 列是 int，其他 20 列是字符串。没有索引。我编写了一个测试代码，创建了一个随机值并插入到数据库中(使用 insert sql *2)。在运行 sql 命令之前，调用 conn.setAutoCommit(false) 而不是 conn.commit()。这个操作大约需要6-7秒。有官方文档(*3)说使用“COPY”命令进行批量插入。创建一个类似的ascii文件并重新测试它，这个测试完成了大约5秒。在同一台机器上使用相同的测试代码，将相同的数据插入 Mysql，测试不到 1 秒。对于 6-7 秒的巨大性能提升，我真的很惊讶。这是真的存在差异还是我忽略了任何事情。

感谢帮助

我的测试配置是 solaris 10 和 PostgreSQL 9.0.2 以及 Mysql 5.0.85。

(*1) PostgreSQL 创建数据库脚本

CREATE TABLE tablo
(
  id integer,
  column1 character varying(50),
  column2 character varying(50),
  column3 character varying(50),
  ....
  column20 character varying(50)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tablo OWNER TO pgadmin;

(*2)插入查询

INSERT INTO tablo values (1,'column67062724628797','column26007603757271','column73982294239806','column43213154421324','column97722282440805','column79000889379973','column10680880337755','column14322827996050','column80720842739399','column22777514445036','column77771307997926','column92799724462613','column89992937353110','column61693061355353','column43804223262229','column62209656630047','column52150955786400','column85726157993572','column33358888005133','column77743799989746'),(2,'column77383691774831','column67841193885377','column36149612452454','column51161680852595','column91649734476301','column57283307765550','column14997046117948','column29457857794726','column91157683305554','column44413196495111','column40702778794938','column24744999726868','column38356057278249','column16808618337554','column64362413535503','column19577167594144','column72943639162993','column46830376244427','column01942608599939','column66479131355003'),
....
10K lines

(*3) PostgreSql官方文档地址 http://www.postgresql.org/docs/8.3/interactive/populate.html

Answer 1

似乎很奇怪，您没有看到像使用 COPY 这样的东西加速。我生成了一个脚本来创建一个类似的表并用 10,000 行填充它，并发现:

1. 最慢:每次插入 1 行，无事务 block ，fsync=on
2. 更快:将所有语句(创建表、插入....)包装在一个事务中
3. 与 2 相同的速度:与 1 相同，但关闭 fsync=off
4. 最快:使用 pg_dump 创建的脚本恢复，使用 COPY 填充表

方法 2 和 3 比方法 1 快约 4 倍。方法 4 比方法 2 或 3 快约 10 倍。

如果我将相同的数据导入到我机器上的 mysql 中，它花费的时间大约是方法 2 或 3 的一半。转储和重新加载它，相同。使用 -e 转储并重新加载它，相同。使用 InnoDB 将时间提高到与方法 2 或 3 相同。

所以至少在我的硬件/操作系统组合上，两者之间的速度是可比的……虽然我当然会更好地照顾 postgresql 的设置，但对于像这样的小表，我不会期望像缓冲区缓存大小这样的东西重要吗？

现在，至于 JDBC 对批量插入的支持有多好，我不知道。我只使用命令行客户端完成了所有这些事情。