优化 SQLite 很棘手。 C 应用程序的批量插入性能可以从每秒 85 次插入到每秒超过 96,000 次插入！
背景：我们使用 SQLite 作为桌面应用程序的一部分。我们有大量的配置数据存储在 XML 文件中，这些数据被解析并加载到 SQLite 数据库中，以便在初始化应用程序时进一步处理。 SQLite 非常适合这种情况，因为它速度快，不需要专门配置，数据库作为单个文件存储在磁盘上。
理由： 最初我对我所看到的表现感到失望。事实证明，SQLite 的性能可能会有很大差异（对于批量插入和选择），具体取决于数据库的配置方式以及您使用 API的方式。弄清楚所有选项和技术是什么并不是一件小事，所以我认为创建这个社区 wiki 条目与 Stack Overflow 读者分享结果是谨慎的，以便为其他人节省相同调查的麻烦。
实验：我不是简单地谈论一般意义上的性能提示（即“使用事务！” ），而是认为最好编写一些 C 代码并实际测量各种选项的影响。我们将从一些简单的数据开始：
我们来写一些代码吧！
代码：一个简单的 C 程序，它逐行读取文本文件，将字符串拆分为值，然后将数据插入到 SQLite 数据库中。在代码的这个 “基线” 版本中，创建了数据库，但我们实际上不会插入数据：
/*************************************************************
Baseline code to experiment with SQLite performance.
Input data is a 28 MB TAB-delimited text file of the
complete Toronto Transit System schedule/route info
from 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 次插入。
默认情况下，SQLite 将在发出操作系统级写入命令后暂停。这可以保证将数据写入磁盘。通过设置synchronous = OFF ，我们指示 SQLite 简单地将数据切换到 OS 进行写入然后继续。如果计算机在将数据写入盘片之前遭受灾难性崩溃（或电源故障），则数据库文件可能会损坏：
/* 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 次插入。
考虑通过评估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 次插入。
让我们结合前两个优化。风险更大（如果发生崩溃），但我们只是导入数据（不运行银行）：
/* 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 次插入。
虽然不是特别的 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 次插入！
在我们开始测量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 查询编译类似的数据。
几个提示：
pragma journal_mode ）。有NORMAL ，然后有OFF ，如果您不太担心如果操作系统崩溃可能会损坏数据库，则可以显着提高插入速度。如果您的应用程序崩溃，数据应该没问题。请注意，在较新版本中， OFF/MEMORY设置对于应用程序级别崩溃是不安全的。 PRAGMA page_size ）。具有较大的页面大小可以使读取和写入更快，因为较大的页面保存在内存中。请注意，您的数据库将使用更多内存。 CREATE INDEX 。这比创建索引然后执行插入要快得多。 INTEGER PRIMARY KEY ，这将替换表中隐含的唯一行号列。 !feof(file) ！ 尝试对这些插入使用SQLITE_STATIC而不是SQLITE_TRANSIENT 。
SQLITE_TRANSIENT将导致 SQLite 在返回之前复制字符串数据。
SQLITE_STATIC告诉它你给它的内存地址在查询执行之前是有效的（在这个循环中总是如此）。这将为每个循环节省几次分配，复制和取消分配操作。可能是一个很大的改进。
避免使用 sqlite3_clear_bindings（stmt）;
测试中的代码每次都应该设置绑定就足够了。
来自 SQLite 文档的 C API 简介说
在第一次调用 sqlite3_step（）之前或在 sqlite3_reset（）之后立即调用之前，应用程序可以调用其中一个 sqlite3_bind（）接口来将值附加到参数。每次调用 sqlite3_bind（）都会覆盖对同一参数的先前绑定
（参见： sqlite.org/cintro.html ）。 该函数的文档中没有任何内容表明除了简单地设置绑定外，还必须调用它。
更多细节： http ： //www.hoogli.com/blogs/micro/index.html#Avoid_sqlite3_clear_bindings（）