提高 SQLite 的每秒插入性能

袁如华

优化 SQLite 很棘手。C 应用程序的批量插入性能可以从每秒 85 插入到每秒 96以上，000 插入不等！
背景：我们使用 SQLite 作为桌面应用程序的一部分。在中存储了大量的配置数据XML 文件中的这些数据被分析并加载到 SQLite 数据库，以便在应用程序初始化时进一步处理。SQLite 非常适合这种情况，因为它速度快，不需要特殊配置，数据库作为单个文件存储在磁盘上。
理由： 一开始，我对我看到的表现感到失望。事实证明，SQLite 的性能可能会有很大的不同(批量插入和选择)，这取决于数据库的配置和您使用 API 的方式。找出所有的选项和技术都不是小事，所以我认为创建这个社区 wiki 条目以和 Stack Overflow 读者分享结果以节省同样调查的麻烦是明智的。
实验：与其简单谈一般意义上的性能技能(即“使用事务！”），我觉得最好写一些 C 代码并实际测量各种选项的影响。我们将从一些简单的数据开始：
一个 28 MB 的 TAB 分隔的文本文件(约865,000 条记录)，包括多伦多市完整的交通时间表
我的测试机正在运行 Windows XP 的 3.60 GHz P4。
该代码使用Visual C 2005 编译成完全优化(/Ox) 和 Favor Fast Code (/Ot) 发布。
我正在使用直接编译到我的测试应用程序中的 SQLite合并。我碰巧拥有 SQLite 版本有点旧(3.6.7），但我怀疑这些结果将与最新版本相当（如果您不这么认为，请发表评论）。让我们写一些代码！
代码：一个简单的 C 程序，逐行读取文本文件，将字符串分成值，然后将数据插入 SQLite 数据库。数据库是在这个基线版本的代码中创建的，但我们实际上不会插入数据：

“控制”实际上，按原始操作代码不执行任何数据库操作，但它会让我们了解原始 C 文件 I/O 和字符串处理有多快。
0.94秒导入864913条记录
4 r: C5 ~$ ^; {/ r" P伟大的！如果我们实际上没有插入，我们可以每秒插入 920，000 次：-)
最坏情况我们将使用从文件中读取的值生成 SQL 字符串，并使用 sqlite3_exec 调用该 SQL 操作：

这会很慢，因为每次插入 SQL 都会被编译成 VDBE 代码，每次插入都会发生在自己的事务中。有多慢？
在 9933. 864913 条记录在61 秒内进口7 s- H' @ ]8 ]7 x+ \4 o: I
哎呀！2小时45分钟！每秒插入只有85 次。
使用事务默认情况下，SQLite 将评估唯一事务中的每一个 INSERT / UPDATE 语句。如有大量插入，建议在事务中包装您的操作：

在 38. 864913 0 z. C! A5 J5 d! k% a' i0 G
这样更好。我们所有的插入包装都可以在事务中提高我们的性能每秒 23，000 次插入。
使用准备好的句子使用事务是一个巨大的改进，但如果我们一次又一次地使用相同的 SQL，然后重新编译 SQL 句子毫无意义。让我们用它。sqlite3_prepare_v一次编译我们的 SQL 句子，然后使用以下命令将我们的参数绑定到句子中sqlite3_bind_text：

16.864913记录27秒导入8 x2 |: K% c! ^ s: k4 ^+ Z
好的！还有一点代码(别忘了调用)sqlite3_clear_bindingsand sqlite3_reset），但是我们的性能提高了一倍多，达到了每秒 53，000 次插入。
PRAGMA 同步 = 关闭默认情况下，SQLite 将在发出操作系统级别的写入命令后暂停。这确保数据被写入磁盘。通过设置synchronous = OFF，我们指示 SQLite 简单地将数据交给操作系统，然后继续。如果计算机在数据写入磁盘之前崩溃（或电源故障），数据库文件可能会损坏：

12.864913记录导入41秒
8 ]4 F# g* P+ ]1 X3 W现在改进更小了，但我们每秒最多可以插入 69，600 次。
PRAGMA journal_mode = MEMORY回滚日志通过评估存储在内存中PRAGMA journal_mode = MEMORY。您的事务会更快，但如果您在事务期间停电或程序崩溃，您的数据库可能会因事务部分完成而损坏：

13.864913记录50秒导入
9 o8 {9 D& `5 w7 P/ C. T1 m比以前的优化慢一点，每秒 64，000 次插入。
PRAGMA synchronous = OFF andPRAGMA journal_mode = MEMORY让我们结合前两个优化。这有点危险(崩溃时)，但我们只导入数据(不是银行):

12.00 秒导入 864913 条记录7 t) i# B8 ]5 m* \1 B
极好的！我们每秒可插入72000 次。
使用内存数据库只是为了好玩，让我们在之前所有优化的基础上重新定义数据库文件名，让我们完全在 RAM 中工作：

10.94秒导入864913条记录
5 d, L3 J2 J y# M9 b将我们的数据库存储在 RAM 不是很实用，但令人印象深刻的是我们79,000 可以每秒插入一次。
重构 C 代码虽然不是专门的 SQLite 改进，但我不喜欢循环char*额外赋值操作。while让我们快速重构代码，输出 strtok()直接传输到sqlite3_bind_text()让编译器试着为我们加速:

注：我们回到使用真实的数据库文件。内存数据库速度快，但不一定实用
8.94秒导入864913条记录! W5 I1 ~2 {& ? Y8 z8 b* n0 q
我们可以轻微重构参数绑定中使用的字符串处理代码每秒插入 96，700 次。我想我可以肯定地说很快。当我们开始调整其他变量（即页面大小、索引创建等）时，这将是我们的基准。
摘要(到目前为止)我希望你还在我身边！之所以开始走这条路，是因为 SQLite 的批量插入性能变化如此之大，需要做哪些改变来加速我们的操作并不总是很明显。使用相同的编译器(和编译器选项)和相同版本的 SQLite 我们优化了我们的代码和相同的数据SQLite 的使用，从每秒 85 次插入的最坏情况到每秒 96以上，000 次插入！
CREATE INDEX 然后 INSERT 与 INSERT 然后 CREATE INDEX我们开始衡量SELECT在性能之前，我们知道我们将创建索引。下面的答案之一建议在批量插入时更快地创建索引（而不是先创建索引，然后插入数据）。让我们试试：
创建索引然后插入数据

18.13秒导入864913条记录6 A! `; ]0 I+ ?" H4 H) ?0 s$ T
插入数据，然后创建索引

13.864913记录导入66秒/ V# M) D1 N0 I; o$ Y
正如预期的那样，如果为列建立索引，批量插入会更慢，但如果在插入数据后创建索引，它确实会有所不同。我们的无索引基线每秒 96,000 次插入。首先创建索引，然后每秒插入数据47700 ，然后每秒插入633300 。
我很高兴为其他场景提供建议......而且很快就会为 SELECT 查询类似数据的编译。

解决方案:
                                                            几个提示：
[ol]在事务中插入/更新。
旧版 SQLite - 考虑一种不那么偏执的日志模式 ( pragma journal_mode)。有NORMAL，然后有OFF，如果您不担心操作系统崩溃会损坏数据库，则可以显著提高插入速度。如果您的应用程序崩溃，数据应该没有问题。请注意，在更新版本中OFF/MEMORY应用程序级崩溃的设置是不安全的。
使用页面的大小也会有所不同（PRAGMA page_size）。由于更大的页面保存在内存中，，因为更大的页面保存在内存中。请注意，您的数据库将使用更多的内存。
如果您有索引，请考虑CREATE INDEX在完成所有插入后调用。这比创建索引然后进行插入要快得多。
如果您对 SQLite 有并发访问权，必须非常小心，因为当写入完成时，整个数据库被锁定，尽管可能有多个读取器，但写入将被锁定。在更新的 SQLite 在版本中本中WAL，这已经改善了。
节省空间…较小的数据库运行得更快。例如，如果您有正确的键值，请尽量将键设置为 an INTEGER PRIMARY KEY，唯一隐含在替换表中的行号列。
如果使用多个线程，可以尝试使用共享页面缓存，允许加载页面在线程之间共享，避免昂贵 I/O 调用。
不要使用!feof(file)！[/ol]

提高 SQLite 的每秒插入性能

袁如华 LV1