如何优化MySQL以插入数百万行数据？

Question

我需要将数百万行数据插入到MySQL数据库（InnoDB引擎）。当表格大小很大时，时间会成为问题。几乎所有的时间都用在插入查询上。也许有人知道如何优化它？

- Igor Timoshenko

---

你的查询是什么？ - John Woo

在你插入数据的同时，还有对该表进行其他操作吗？ - Kermit

只是为了确认：您想知道如何设置MySQL以处理大量插入（或一般的写入），还是如何设置它以处理存储的大量数据？ - c2h5oh

再问一句：您需要处理大批量数据的性能（每个事务中有大量插入）还是许多小事务？ - c2h5oh

一个包含数百万个插入查询的大型事务。 - Igor Timoshenko

Answer 1

将大批量数据导入InnoDB：

1. 在MySQL配置中设置

   • innodb_doublewrite = 0
   • innodb_buffer_pool_size = 50%+系统内存
   • innodb_log_file_size = 512M
   • log-bin = 0
   • innodb_support_xa = 0
   • innodb_flush_log_at_trx_commit = 0

2. 在事务开始后立即添加：

   SET FOREIGN_KEY_CHECKS = 0;

   SET UNIQUE_CHECKS = 0;

   SET AUTOCOMMIT = 0;

3. 在事务结束前进行设置：

   SET UNIQUE_CHECKS = 1;

   SET FOREIGN_KEY_CHECKS = 1;

- c2h5oh

---

这些优化让我从每10分钟处理1万条记录提升到了每10分钟处理50万条记录，令人印象深刻。 - jzahedieh

如果sync_binlog被设置为1，请将其设置为0（默认值为0）。 - zogby

@zogby使用log_bin = 0禁用了binlog，因此sync_binlog = 0没有任何影响。 - c2h5oh

选项 innodb_support_xa 不再可禁用。来自手册的说明：禁用 innodb_support_xa 不再被允许，因为它会使复制不安全，并阻止与二进制日志组提交相关的性能提升。 - greggles

Answer 2

如果你要处理大量的INSERT语句，可以考虑使用事务。我相信大多数（如果不是全部）支持 SQL 的编程语言都支持事务。使用事务可以加速任何涉及写入数据库的操作。另一个好处是，如果出现问题，你可以回滚更改。

- PaulG