提高SQLite的每秒INSERT性能

优化SQLite很棘手。C应用程序的批量插入性能可以从每秒85次插入到每秒96,000多次插入不等！

背景：我们使用SQLite作为桌面应用程序的一部分。我们有大量存储在XML文件中的配置数据，这些数据被解析并加载到SQLite数据库中，以便在应用程序初始化时进行进一步处理。SQLite非常适合这种情况，因为它速度快，不需要专门的配置，并且数据库作为单个文件存储在磁盘上。

理由：起初我对我看到的表演感到失望。事实证明，SQLite的性能可能会有很大差异（无论是批量插入还是选择），这取决于数据库的配置方式以及您如何使用API。弄清楚所有选项和技术是什么并不是一件小事，所以我认为创建这个社区wiki条目与Stack Overflow阅读器共享结果是谨慎的，以节省其他人进行相同调查的麻烦。

实验：与其简单地谈论一般意义上的性能技巧（即“使用交易！”），我认为最好写一些C代码和实际测量各种选项的影响。我们将从一些简单的数据开始：

  • 多伦多市的完整运输时间表的28 MB选项卡分隔的文本文件（大约865,000条记录）
  • 我的测试机器是运行Windows XP的3.60 GHz P4。
  • 代码使用视觉C++ 2005编译为“发布”，使用“完全优化”（/Ox）和支持快速代码（/Ot）。
  • 我正在使用SQLite“合并”，直接编译到我的测试应用程序中。我碰巧拥有的SQLite版本有点旧（3.6.7），但我怀疑这些结果将与最新版本相当（如果您不这样认为，请留下评论）。

让我们写一些代码！

代码：一个简单的C程序，逐行读取文本文件，将字符串拆分为值，然后将数据插入SQLite数据库。在这个“基线”版本的代码中，数据库被创建，但我们不会实际插入数据：

/*************************************************************Baseline code to experiment with SQLite performance.
Input data is a 28 MB TAB-delimited text file of thecomplete Toronto Transit System schedule/route infofrom http://www.toronto.ca/open/datasets/ttc-routes/
**************************************************************/#include <stdio.h>#include <stdlib.h>#include <time.h>#include <string.h>#include "sqlite3.h"
#define INPUTDATA "C:\\TTC_schedule_scheduleitem_10-27-2009.txt"#define DATABASE "c:\\TTC_schedule_scheduleitem_10-27-2009.sqlite"#define TABLE "CREATE TABLE IF NOT EXISTS TTC (id INTEGER PRIMARY KEY, Route_ID TEXT, Branch_Code TEXT, Version INTEGER, Stop INTEGER, Vehicle_Index INTEGER, Day Integer, Time TEXT)"#define BUFFER_SIZE 256
int main(int argc, char **argv) {
sqlite3 * db;sqlite3_stmt * stmt;char * sErrMsg = 0;char * tail = 0;int nRetCode;int n = 0;
clock_t cStartClock;
FILE * pFile;char sInputBuf [BUFFER_SIZE] = "\0";
char * sRT = 0;  /* Route */char * sBR = 0;  /* Branch */char * sVR = 0;  /* Version */char * sST = 0;  /* Stop Number */char * sVI = 0;  /* Vehicle */char * sDT = 0;  /* Date */char * sTM = 0;  /* Time */
char sSQL [BUFFER_SIZE] = "\0";
/*********************************************//* Open the Database and create the Schema */sqlite3_open(DATABASE, &db);sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
/*********************************************//* Open input file and import into Database*/cStartClock = clock();
pFile = fopen (INPUTDATA,"r");while (!feof(pFile)) {
fgets (sInputBuf, BUFFER_SIZE, pFile);
sRT = strtok (sInputBuf, "\t");     /* Get Route */sBR = strtok (NULL, "\t");            /* Get Branch */sVR = strtok (NULL, "\t");            /* Get Version */sST = strtok (NULL, "\t");            /* Get Stop Number */sVI = strtok (NULL, "\t");            /* Get Vehicle */sDT = strtok (NULL, "\t");            /* Get Date */sTM = strtok (NULL, "\t");            /* Get Time */
/* ACTUAL INSERT WILL GO HERE */
n++;}fclose (pFile);
printf("Imported %d records in %4.2f seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);
sqlite3_close(db);return 0;}

的“控制”

按原样运行代码实际上不会执行任何数据库操作，但它会让我们了解原始C文件I/O和字符串处理操作的速度。

在0.94中导入864913条记录秒

太好了！我们每秒可以执行920,000次插入，前提是我们实际上没有执行任何插入：-）

“最坏的情况”

我们将使用从文件中读取的值生成SQL字符串，并使用sqlite3_exec调用SQL操作：

sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, '%s', '%s', '%s', '%s', '%s', '%s', '%s')", sRT, sBR, sVR, sST, sVI, sDT, sTM);sqlite3_exec(db, sSQL, NULL, NULL, &sErrMsg);

这将是缓慢的，因为SQL将被编译为每次插入的VDBE代码，并且每次插入都将发生在自己的事务中。

在9933.61中导入864913条记录秒

哎呀！2小时45分钟！那只是每秒85次插入。

使用事务

默认情况下，SQLite将评估唯一事务中的每个INSERT/UPDATE语句。如果执行大量插入，建议将您的操作包装在事务中：

sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
pFile = fopen (INPUTDATA,"r");while (!feof(pFile)) {
...
}fclose (pFile);
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);

在38.03中导入864913条记录秒

那更好。只需将我们所有的插入包装在一个事务中，就可以将我们的性能提高到每秒23,000次插入。

使用准备好的语句

使用事务是一个巨大的改进，但是如果我们一遍又一遍地使用相同的SQL，为每个插入重新编译SQL语句是没有意义的。让我们使用sqlite3_prepare_v2编译我们的SQL语句一次，然后使用sqlite3_bind_text将我们的参数绑定到该语句：

/* Open input file and import into the database */cStartClock = clock();
sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, @RT, @BR, @VR, @ST, @VI, @DT, @TM)");sqlite3_prepare_v2(db,  sSQL, BUFFER_SIZE, &stmt, &tail);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
pFile = fopen (INPUTDATA,"r");while (!feof(pFile)) {
fgets (sInputBuf, BUFFER_SIZE, pFile);
sRT = strtok (sInputBuf, "\t");   /* Get Route */sBR = strtok (NULL, "\t");        /* Get Branch */sVR = strtok (NULL, "\t");        /* Get Version */sST = strtok (NULL, "\t");        /* Get Stop Number */sVI = strtok (NULL, "\t");        /* Get Vehicle */sDT = strtok (NULL, "\t");        /* Get Date */sTM = strtok (NULL, "\t");        /* Get Time */
sqlite3_bind_text(stmt, 1, sRT, -1, SQLITE_TRANSIENT);sqlite3_bind_text(stmt, 2, sBR, -1, SQLITE_TRANSIENT);sqlite3_bind_text(stmt, 3, sVR, -1, SQLITE_TRANSIENT);sqlite3_bind_text(stmt, 4, sST, -1, SQLITE_TRANSIENT);sqlite3_bind_text(stmt, 5, sVI, -1, SQLITE_TRANSIENT);sqlite3_bind_text(stmt, 6, sDT, -1, SQLITE_TRANSIENT);sqlite3_bind_text(stmt, 7, sTM, -1, SQLITE_TRANSIENT);
sqlite3_step(stmt);
sqlite3_clear_bindings(stmt);sqlite3_reset(stmt);
n++;}fclose (pFile);
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
printf("Imported %d records in %4.2f seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);
sqlite3_finalize(stmt);sqlite3_close(db);
return 0;

在16.27中导入864913条记录秒

不错！代码有点多（别忘了调用sqlite3_clear_bindings和sqlite3_reset），但我们的性能已经翻了一番多，达到了每秒53,000次插入。

PRAGMA同步=OFF

默认情况下，SQLite会在发出操作系统级别的写命令后暂停。这保证了数据被写入磁盘。通过设置synchronous = OFF，我们指示SQLite简单地将数据交给操作系统进行写入，然后继续。如果计算机在数据写入盘片之前遭受灾难性崩溃（或电源故障），数据库文件可能会损坏：

/* Open the database and create the schema */sqlite3_open(DATABASE, &db);sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);

在12.41中导入864913条记录秒

改进现在较小，但我们达到了每秒69,600次插入。

PRAGMAjournal_mode=记忆

考虑通过评估PRAGMA journal_mode = MEMORY将回滚日志存储在内存中。您的事务会更快，但如果您在事务期间断电或程序崩溃，您的数据库可能会处于损坏状态，并且事务部分完成：

/* Open the database and create the schema */sqlite3_open(DATABASE, &db);sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);

在13.50中导入864913条记录秒

比之前每秒64,000次插入。的优化慢一点

PRAGMA同步=OFF和 PRAGMAjournal_mode=内存

让我们结合前面的两个优化。这有点风险（以防崩溃），但我们只是导入数据（而不是运行银行）：

/* Open the database and create the schema */sqlite3_open(DATABASE, &db);sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);

在12.00中导入864913条记录秒

太棒了！我们能做到每秒72,000个插入。

使用内存数据库

只是为了刺激，让我们建立在所有先前的优化基础上并重新定义数据库文件名，以便我们完全在RAM中工作：

#define DATABASE ":memory:"

在10.94中导入864913条记录秒

将我们的数据库存储在RAM中并不是非常实用，但令人印象深刻的是我们可以执行每秒79,000次插入。

重构C代码

虽然不是特别的SQLite改进，但我不喜欢while循环中额外的char*赋值操作。让我们快速重构该代码以将strtok()的输出直接传递到sqlite3_bind_text()，并让编译器尝试为我们加快速度：

pFile = fopen (INPUTDATA,"r");while (!feof(pFile)) {
fgets (sInputBuf, BUFFER_SIZE, pFile);
sqlite3_bind_text(stmt, 1, strtok (sInputBuf, "\t"), -1, SQLITE_TRANSIENT); /* Get Route */sqlite3_bind_text(stmt, 2, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Branch */sqlite3_bind_text(stmt, 3, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Version */sqlite3_bind_text(stmt, 4, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Stop Number */sqlite3_bind_text(stmt, 5, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Vehicle */sqlite3_bind_text(stmt, 6, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Date */sqlite3_bind_text(stmt, 7, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Time */
sqlite3_step(stmt);        /* Execute the SQL Statement */sqlite3_clear_bindings(stmt);    /* Clear bindings */sqlite3_reset(stmt);        /* Reset VDBE */
n++;}fclose (pFile);

注意：我们回到使用真实的数据库文件。内存数据库很快，但不一定实用

8.94中导入864913条记录秒

对参数绑定中使用的字符串处理代码进行了轻微的重构，使我们能够执行每秒96,700次插入。我认为可以肯定地说这是足够快。当我们开始调整其他变量（即页面大小、索引创建等）时，这将是我们的基准。

总结（到目前为止）

我希望你还和我在一起！我们开始走这条路的原因是SQLite的大容量插入性能差异很大，并且需要做哪些更改来加快我们的操作并不总是很明显。使用相同的编译器（和编译器选项）、相同版本的SQLite和相同的数据，我们优化了我们的代码和SQLite的使用，以达到从每秒85次插入的最坏情况到每秒超过96,000次插入！

创建索引，然后插入vs.插入，然后创建索引

在我们开始衡量SELECT性能之前，我们知道我们将创建索引。下面的一个答案中建议，在进行批量插入时，在插入数据后创建索引更快（而不是先创建索引然后插入数据）。让我们尝试：

创建索引然后插入数据

sqlite3_exec(db, "CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);...

在18.13中导入864913条记录秒

插入数据，然后创建索引

...sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);sqlite3_exec(db, "CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);

在13.66中导入864913条记录秒

正如预期的那样，如果索引一列，批量插入会更慢，但如果索引是在插入数据之后创建的，则会有所不同。我们的无索引基线是每秒96,000次插入。首先创建索引然后插入数据每秒为我们提供47,700次插入，而先插入数据然后创建索引每秒为我们提供63,300次插入。

我很乐意为其他场景提供建议，并将很快为SELECT查询编译类似的数据。

469946 次浏览
小开

如果您可以将插入/更新语句分块，批量导入似乎表现最佳。10,000左右的值对我来说在只有几行的表上效果很好，YMMV…

小开
最佳答案

几个小贴士：

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

我也问过类似的问题这里和这里。

小开

在批量插入

受到这篇文章和Stack Overflow问题的启发-是否可以在SQLite数据库中一次插入多行？-我发布了我的第一个git存储库：

https://github.com/rdpoor/CreateOrUpdate

批量将ActiveRecords数组加载到mysql、SQLite或PostgreSQL数据库中。它包括忽略现有记录、覆盖它们或引发错误的选项。我的基本基准测试显示与顺序写入相比速度提高了10倍--YMMV。

我在生产代码中使用它，我经常需要导入大型数据集，我对它非常满意。

小开

避免sqlite3_clear_bindings(stmt)

测试中的代码每次都设置绑定，这应该足够了。

SQLite文档中的C API介绍表示：

在第一次或立即调用sqlite3_step（）之前在sqlite3_reset（）之后，应用程序可以调用sqlite3_bind（）接口将值附加到参数。每个对sqlite3_bind（）的调用会覆盖相同参数的先前绑定

除了简单地设置绑定之外，#0的文档中没有任何内容表明您必须调用它。

更多细节：Avoid_sqlite3_clear_bindings（）

小开

如果你只关心读取，稍微快一点（但可能会读取陈旧的数据）的版本是从多个线程的多个连接读取（每个线程的连接）。

首先在表中找到项目：

SELECT COUNT(*) FROM table

然后在页面中读取（LIMIT/OFFSET）：

SELECT * FROM table ORDER BY _ROWID_ LIMIT <limit> OFFSET <offset>

其中和每个线程计算，像这样：

int limit = (count + n_threads - 1)/n_threads;

对于每个线程：

int offset = thread_index * limit

对于我们的小（200mb）db，这使速度提高了50-75%（在Windows 7上3.8.0.264位）。我们的表是非标准化的（1000-1500列，大约100,000行或更多行）。

线程太多或太少都不行，你需要对自己进行基准测试和分析。

对于我们来说，SHAREDCACHE使性能变慢，所以我手动将PRIVATECACHE（因为它为我们全局启用）

小开

尝试对这些插入使用#0而不是#1。

SQLITE_TRANSIENT将导致SQLite在返回之前复制字符串数据。

SQLITE_STATIC告诉它，您给它的内存地址在执行查询之前将是有效的（在这个循环中总是如此）。这将为您节省每个循环的几个分配、复制和释放操作。可能是一个很大的改进。

小开

我无法从交易中获得任何收益，直到我将cache_size提高到更高的值，即PRAGMA cache_size=10000;

小开

阅读本教程后，我尝试将其实现到我的程序中。

我有4-5个包含地址的文件。每个文件大约有3000万条记录。我使用的配置与您建议的相同，但我每秒的INSERT数量很低（每秒约10,000条记录）。

这就是你的建议失败的地方。你对所有记录使用单个事务和单个插入，没有错误/失败。假设你将每个记录拆分为不同表上的多个插入。如果记录坏了会发生什么？

ON CONFLICT命令不适用，因为如果记录中有10个元素并且需要将每个元素插入到不同的表中，如果元素5出现CONSTRAINT错误，则之前的所有4次插入也需要进行。

所以这就是回滚的地方。回滚的唯一问题是您丢失了所有插入并从顶部开始。如何解决这个问题？

我的解决方案是使用多个事务。我每10,000条记录开始和结束一个事务（不要问为什么这个数字，它是我测试的最快的一个）。我创建了一个大小为10.000的数组并在那里插入成功的记录。当错误发生时，我执行回滚，开始一个事务，从我的数组中插入记录，提交，然后在损坏的记录之后开始一个新事务。

这个解决方案帮助我绕过了处理包含不良/重复记录的文件时遇到的问题（我有近4%的不良记录）。

我创建的算法帮助我减少了2个小时的处理过程。文件的最终加载过程1hr30m这仍然很慢，但与最初花费的4hrs相比。我设法将插入速度从10.000/s提高到~14.000/s

如果有人对如何加快速度有任何其他想法，我愿意接受建议。

更新：

除了我上面的回答之外，你应该记住，每秒插入的速度也取决于你使用的硬盘驱动器。我在3台不同的硬盘驱动器上测试了它，得到了巨大的时间差异。PC1（1小时30m），PC2（6小时）PC3（14小时），所以我开始想知道为什么会这样。

经过两周的研究和检查多个资源：硬盘驱动器、内存、缓存，我发现硬盘驱动器上的某些设置会影响I/O速率。通过单击所需输出驱动器的属性，您可以在常规选项卡中看到两个选项。Opt1：压缩此驱动器，Opt2：允许此驱动器的文件索引内容。

通过禁用这两个选项，所有3台PC现在需要大约相同的时间来完成（1小时和20到40分钟）。如果您遇到缓慢的插入，请检查您的硬盘驱动器是否配置了这些选项。这将为您节省大量时间和寻找解决方案的麻烦

小开

您问题的答案是较新的SQLite 3提高了性能，请使用它。

这个答案为什么使用sqlite的SQLAlchemy插入比直接使用sqlite3慢25倍？ by SqlAlchemy Orm作者在0.5秒内100k插入，我在python-sqlite和SqlAlchemy中看到了类似的结果。这让我相信SQLite 3的性能有所提高。

小开

使用ContentProvider在数据库中插入批量数据。以下方法用于将批量数据插入数据库。这应该会提高SQLite的每秒INSERT性能。

private SQLiteDatabase database;database = dbHelper.getWritableDatabase();
public int bulkInsert(@NonNull Uri uri, @NonNull ContentValues[] values) {
database.beginTransaction();
for (ContentValues value : values)db.insert("TABLE_NAME", null, value);
database.setTransactionSuccessful();database.endTransaction();
}

调用bulkInett方法：

App.getAppContext().getContentResolver().bulkInsert(contentUriTable,contentValuesArray);

链接：https://www.vogella.com/tutorials/AndroidSQLite/article.html有关详细信息，请检查使用ContentProvider部分

小开

像@Jimmy_A那样将任务拆分为多个事务是要走的路。否则，您可能会使用大量事务和繁重的COMMIT任务来饱和您的RAM。

为了进一步调整性能，您还可以在硬盘上启用回写缓存，前提是您使用某种电池支持的系统（笔记本电脑、UPS、带电池的RAID控制器…）。

小开

在我的例子中，使用PRAGMA journal_mode = WAL将INSERT的速度提高了一倍，因为在内部它与批处理INSERTS正如这里所建议的相同。

在我的情况下，我需要将数据导入索引而不仅仅是表。SQLite有没有鲁威这个很棒的功能，它允许组合表和索引。默认情况下，SQLite中的表也是一个B树，任何索引都存储在单独的B树页面中。使用WITHOUT ROWID对表和索引只使用一个B树。

我还使用了PRAGMA auto_vacuum = 0，因为从技术上讲，它应该防止SQLite以牺牲数据库大小为代价来提高空间利用率，但它似乎在性能上没有任何明显的差异。

虽然我的情况与OP的要求有点不同，但使用WAL的第一个建议应该对他的情况有所不同。


提交答案