在sqlite3中如何实现更快的批量插入？

Question

我有一个大约30000行数据的文件，我想将其加载到sqlite3数据库中。是否有比为每行数据生成插入语句更快的方法？

这些数据是以空格分隔的，并且直接映射到sqlite3表。是否有任何一种批量插入方法可以添加大量数据到数据库中？

如果没有内置功能，是否有人设计了一些巧妙的方式来完成此操作？

我应该先问一下，是否有从API中使用C++进行此操作的方法？

- scubabbl

---

非常相关：https://dev59.com/reo6XIcBkEYKwwoYTzEw - Ben McIntyre

Answer 1

• 即使只有一个用户，也要将所有的INSERT语句包装在事务中，这样可以提高速度。
• 使用预编译语句。

- Javier

---

对于大多数（所有？）SQL数据库而言，这是正确的。 - stesch

PRAGMA journal_mode = MEMORY;这可能对一些人有所帮助。 - Ted

在Node.js开发中使用SQLite3的事务管理示例 - Prashant Tapase

Answer 2

你想使用.import命令。例如:

$ cat demotab.txt
44      92
35      94
43      94
195     49
66      28
135     93
135     91
67      84
135     94

$ echo "create table mytable (col1 int, col2 int);" | sqlite3 foo.sqlite
$ echo ".import demotab.txt mytable"  | sqlite3 foo.sqlite

$ sqlite3 foo.sqlite
-- Loading resources from /Users/ramanujan/.sqliterc
SQLite version 3.6.6.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from mytable;
col1    col2
44      92
35      94
43      94
195     49
66      28
135     93
135     91
67      84
135     94

请注意，这个批量加载命令不是SQL，而是SQLite的一个自定义功能。因此，它具有奇怪的语法，因为我们通过 echo 将其传递给交互式命令行解释器 sqlite3 。

在PostgreSQL中，等效的命令是COPY FROM： http://www.postgresql.org/docs/8.1/static/sql-copy.html 在MySQL中，等效的命令是LOAD DATA LOCAL INFILE： http://dev.mysql.com/doc/refman/5.1/en/load-data.html 最后一件事：记得小心 .separator 的值。在进行大量插入时，这是非常常见的问题。

sqlite> .show .separator
     echo: off
  explain: off
  headers: on
     mode: list
nullvalue: ""
   output: stdout
separator: "\t"
    width:

在执行.import之前，您应该明确设置分隔符为空格、制表符或逗号。

- ramanujan

---

这太棒了，而且非常快。20分钟缩短到3秒钟。 - Gazzer

这适用于具有自增主键的表吗？我尝试在文件中使用NULL来表示自增列，但是它会抛出错误。 - Aditya Naidu

查看SQLite的shell.c代码，.import仅在事务内部使用了一个预处理语句。 - dlanod

sqlite在命令行中对\t有奇怪的行为，你必须给-separator选项一个真正的制表符。在命令行中，使用Control-v <TAB>插入一个真正的制表符。 - The Demz

Answer 3

我已经测试了一些在这里提出的 pragmas:

• synchronous = OFF
• journal_mode = WAL
• journal_mode = OFF
• locking_mode = EXCLUSIVE
• synchronous = OFF + locking_mode = EXCLUSIVE + journal_mode = OFF

下面是不同事务中插入数量的结果数据：

增加批处理大小可以真正提升性能，而关闭日志、同步、获取独占锁将带来微不足道的收益。大约在 ~110k 附近的点显示了随机背景负载如何影响数据库性能。

另外值得一提的是，journal_mode=WAL 是一个很好的默认值替代方案。它确实提供了一些收益，但并不降低可靠性。

C# 代码。

- astef

---

我在我的一个项目中注意到的一件事是，如果可能的话，批处理应该限制在单个表上。如果您在事务内循环更新表a和表b，则比两次循环慢得多，一次用于表a，然后再用于表b。 - skamradt

Answer 4

您也可以尝试微调一些参数以获得更快的速度。具体来说，您可能需要PRAGMA synchronous = OFF;。

- user42092

---

pragma synchronous = OFF 是一个坏主意 - 它对于大量插入操作几乎不会影响性能，并且在发生电力故障时会破坏你的数据库。一个更好的主意是将插入操作放到一个事务中。 - Eamon Nerbonne

将INSERTS语句包含在TRANSACTION事务中，并使用PRAGMA journal_mode = MEMORY;可以避免INSERTs语句直到事务结束之前不会写入磁盘。 - Ted

请注意，内存在停电时可能会破坏数据库。 - Anders Rune Jensen

使用"PRAGMA journal_mode = WAL;"可以允许多个编写者同时编写数据，你也最终可以使用线程来编写数据。 需要注意的是，启用“写日志记录”（Write-Ahead Logging），数据库在断电后不会受损。 - Amine Zaine

Answer 5

• 将 PRAGMA cache_size 增加至更大的数字。这将增加内存中缓存页面的数量。注意： cache_size 是每个连接的设置。

• 将所有插入操作都包装在单个事务中，而不是每行一个事务。

• 使用编译的SQL语句进行插入操作。
• 最后，如前所述，如果您愿意放弃完全的ACID兼容性，请设置 PRAGMA synchronous = OFF;。

- ceretullis

---

PRAGMA default_cache_size现在已经弃用。 - david

cache_size 可以代替已弃用的 default_cache_size。但是，cache_size 仅适用于单个连接。 - golmschenk

Answer 6

回复：“是否有更快的方法来生成每行数据的插入语句？”

首先：通过利用Sqlite3的虚拟表API将其缩减为2个SQL语句，例如：

create virtual table vtYourDataset using yourModule;
-- Bulk insert
insert into yourTargetTable (x, y, z)
select x, y, z from vtYourDataset;

这里的想法是实现一个C接口，读取你的源数据集，并将其呈现为SQLite的虚拟表，然后一次性地从源表复制到目标表中。听起来比实际上要难，但我已经通过这种方式测量了巨大的速度提升。

第二：利用这里提供的其他建议，即设置pragma和使用事务。

第三：也许可以看看能否取消目标表上的某些索引。这样，每插入一行，SQLite需要更新的索引就会少一些。

- Hannes de Jager

---

+1 这个实际上是从 API 中以 "c" 的方式完成的（如所请求的），不错。 - AlexD

Answer 7

没有批量插入的方法，但是有一种方式可以将大块数据写入内存中，然后再提交到数据库。对于C/C++ API，只需执行以下操作：

sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);

...(INSERT statements)

sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL, NULL);

假设db是您的数据库指针。

- scott

Answer 8

一个不错的折衷方案是在你的INSERTS语句之间加上BEGIN;和END;关键字，即：

BEGIN; 你的INSERTS语句; END;

BEGIN;
INSERT INTO table VALUES ();
INSERT INTO table VALUES ();
...
END;

- Flavien Volken

---

还有一种写法是 INSERT INTO table VALUES (),(),();。 - ZN13

Answer 9

根据数据大小和可用的RAM量，将sqlite设置为使用全内存数据库而不是写入磁盘，可以获得最佳性能提升。

对于内存数据库，将文件名参数传递给sqlite3_open时传递NULL，并确保适当定义TEMP_STORE。

（以上所有文本摘自我对一个与sqlite相关的问题的回答）

- que que

---

该链接指向一个不完整的文档。其中包含的信息比人们期望的要少。 - Richard

Answer 10

我认为这是一个适合一次性较长的导入的良好选择混合物。

.echo ON

.read create_table_without_pk.sql

PRAGMA cache_size = 400000; PRAGMA synchronous = OFF; PRAGMA journal_mode = OFF; PRAGMA locking_mode = EXCLUSIVE; PRAGMA count_changes = OFF; PRAGMA temp_store = MEMORY; PRAGMA auto_vacuum = NONE;

.separator "\t" .import a_tab_seprated_table.txt mytable

BEGIN; .read add_indexes.sql COMMIT;

.exit

来源: http://erictheturtle.blogspot.be/2009/05/fastest-bulk-import-into-sqlite.html

一些额外信息：http://blog.quibb.org/2010/08/fast-bulk-inserts-into-sqlite/

这篇文章介绍了如何使用SQLite进行最快速的批量导入。通过使用SQLite的特定功能，例如跳过索引和触发器，以及事务控制，可以大大提高导入效率。另外，也可以使用其他方法，例如使用CSV文件和命令行工具等，来加速批量导入操作。

- maazza