背景：我们正在使用SQLite作为桌面应用程序的一部分。我们有大量的配置数据存储在XML文件中，在应用程序初始化时被解析并加载到SQLite数据库中进行进一步处理。SQLite非常适合这种情况，因为它快速，不需要专门的配置，并且数据库存储在磁盘上作为单个文件。
理由：最初我对看到的性能感到失望。事实证明，SQLite的性能可以根据如何配置数据库以及如何使用API而显着变化（对于批量插入和选择）。找出所有选项和技术并不是一件微不足道的事情，因此我认为创建这个社区wiki条目与Stack Overflow读者分享结果是明智的，以便节省其他人做同样调查的麻烦。
实验：与其仅在一般意义上谈论性能技巧（例如“使用事务！”），不如编写一些C代码并实际测量各种选项的影响。我们将从一些简单的数据开始。
- 一个28 MB的TAB分隔文本文件（大约865,000条记录），包含多伦多市的完整交通时间表
- 我的测试机是一台运行Windows XP的3.60 GHz P4。
- 代码使用Visual C++ 2005编译为“Release”，使用“Full Optimization” (/Ox)和Favor Fast Code (/Ot)。
- 我使用了SQLite“Amalgamation”，直接编译到我的测试应用程序中。我手头上的SQLite版本有点旧（3.6.7），但我认为这些结果与最新版本相当（如果您认为不同，请留言）。
让我们写些代码吧！
代码：一个简单的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和字符串处理操作有多快。
导入864913条记录，用时0.94秒
太棒了！我们可以每秒插入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代码和事务处理。每次插入都需要编译SQL成VDBE代码，且每个插入都在自己的事务中进行，因此速度会比较慢。 有多慢呢？
导入了864913条记录，用时9933.61秒
天哪！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），但我们的性能已经提高了一倍以上，达到每秒53000次插入。
PRAGMA synchronous = 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条记录
改进现在变得更小了，但我们已经达到了每秒69600次插入。
PRAGMA journal_mode = MEMORY
考虑通过评估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);
导入864913条记录，耗时13.50秒
比之前的优化稍慢，每秒只能插入64000条数据。
PRAGMA synchronous = OFF 和 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 synchronous = OFF", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);
导入了864913条记录，耗时12.00秒。
太棒了！我们能够每秒执行72000次插入操作。
使用内存数据库
仅出于好奇，让我们在之前所有的优化基础上构建，并重新定义数据库文件名，以便完全在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);
注意：我们已经回到使用真实的数据库文件。内存数据库虽然快，但不一定实用。
导入了864913条记录，用时8.94秒
对我们参数绑定中使用的字符串处理代码进行了轻微的重构，使我们能够每秒执行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);
...
导入了864913条记录，用时18.13秒
插入数据然后创建索引
...
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查询编制类似的数据。
sqlite3_clear_bindings（stmt）;吗？由于在每次调用时都设置了绑定，因此这应该足够了：在第一次调用sqlite3_step（）之前或立即在sqlite3_reset（）之后，应用程序可以调用其中一个sqlite3_bind（）接口将值附加到参数。每次调用sqlite3_bind（）会覆盖同一参数上先前的绑定（请参见：https://www.sqlite.org/cintro.html）。该函数的文档中没有说明必须调用它。 - ahcoxfeof()控制输入循环的终止。使用fgets()返回的结果来控制。https://dev59.com/VGUp5IYBdhLWcg3wJU9J#15485689 - Keith Thompson