如何从<我的DBMS> language>中加速插入<language> SQL数据库？

Question

关于加速插入到SQL数据库中存在许多问题，例如这、这、这、这和(我最喜欢的) 这。许多问题伪装成依赖于语言的问题，但问题通常减少到：

-

Answer 1

插入过程中最大的低效来自两个来源:单个数据事务需要时间，而语言和数据库之间的ORM适配器并不总是特别有效。

这个答案使用Rails作为它的示例语言，但是这里显示的技术适用于几乎所有具有底层数据库接口的高级语言。

结论(TL;DR)

通过下面列出的任何一种大容量插入方法一次插入500条记录，都会使您的速度超过20倍。通过调优，这可能会变得更高。

测验

让我们从内存中的123001条记录开始。(这些数据来自多伦多市交通数据集的"trips.txt“文件.1)

dataset = Utilities.load_csv(Rails.root.join("datasets", "trips.txt")

基线:一次插入

在中，您可以这样做：

dataset.each {|record| Trip.create!(record) }

它实质上转换为表单的123 000个单独的SQL调用：

INSERT INTO "trips" (<column_names>) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)  [<column name/value pairs>]
(123000 more times...)

加速比1:包装在事务中

这几乎是相同的，但是将内部循环封装在一个SQL事务中：

begin transaction
INSERT INTO "trips" (<column_names>) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)  [<column name/value pairs>]
(123000 more times...)
commit transaction

加速2:批量插入版本A

此方法将原始数组分解为500批(以避免SQLite和潜在字符串缓冲区大小的限制)，其中每个事务都具有以下表单：

INSERT INTO trips (comma_separated_column_names) VALUES
    (comma_separated_values_for_row_1),
    (comma_separated_values_for_row_2),
    ...
    (comma_separated_values_for_row_500);
... repeated 246 times

加速3:批量插入版本B

一些DBMS不支持批量插入版本A的语法(特别是较早版本的SQLite)。以下表单在功能上是相同的，并得到许多数据库的支持(尽管不是严格符合SQL-92标准)：

     INSERT INTO trips (comma_separated_column_names)
          SELECT comma_separated_values_for_row_1
UNION ALL SELECT comma_separated_values_for_row_2
...
UNION ALL SELECT comma_separated_values_for_row_500
...repeated 246 times

加速比4: DBM专用方法

正如https://stackoverflow.com/users/20860/bill-karwin所指出的，我们可以

利用特定于供应商的批量加载命令，例如为MySQL加载数据INFILE，为PostgreSQL加载副本，或为Oracle加载SQL*Loader等。每个品牌都有自己的命令或工具，因此没有与供应商无关的方法，但这些方法的性能通常会提高数量级，因此不应忽视它们。

虽然这些不是一般的技术，但在具体情况下将是有用的。在下面的测试中，我们没有对其中任何一个进行基准测试。

相对加速

我们测试了上面在MySQL、PostgreSQL和SQLite中显示的各种技术。下面的数字显示了相对于基线情况的各种方法的速度。第一个数字是相对用户+系统时间，括号中的数字是相对经过的时间。

(注:我选择不显示绝对时间，因为这不是关于哪个数据库最快的讨论-变量太多，无法对此做出合理的断言。)如果有提示，我将在github上发布这两种代码--您可以运行自己的测试并得出自己的结论。)

MySQL

• 交易包装: 1.2x (1.4x)
• 批量插入版本A: 24.3x (19.0x)
• 批量插入版本B: 24.3x (17.1x)

PostgreSQL

• 交易包装: 1.2x (1.6x)
• 批量插入版本A: 27.2x (16.7x)
• 批量插入版本B: 27.2x (13.9x)

SQLite

• 事务包装: 1.6x (2.4x)
• 批量插入版本A: 25.8x (24.7x)
• 批量插入版本B: 24.1x (34.1x)

测试笔记

Processing environment: 2.66 GHz Intel Core i7, 8GB 1067 MHz DDR3
Operating System: OS X v 10.9.5 
Ruby version: 2.0.0 (64 bit) 
Rails version: 4.0.2 
MySQL: Server version: 5.1.49  
PostgreSQL: psql (9.3.1, server 8.3.14) 
SQLite: SQLite version 3.7.12 2012-04-03 19:43:07