提高 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。
• 该代码使用 Visual 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 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 代码，并且每次插入都会在自己的事务中发生。有多慢？

导入的 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);

导入的 864913 38.03 中的记录 秒

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

使用预准备语句

使用事务是一个巨大的改进，但是如果我们一遍又一遍地使用相同的 SQL，那么为每次插入重新编译 SQL 语句是没有意义的。让我们使用 SQL 语句编译一次，然后使用以下命令将参数绑定到该语句：sqlite3_prepare_v2sqlite3_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;

导入的 864913 记录 in 16.27 秒

好！代码多了一点（别忘了调用和），但我们的性能提高了一倍多，达到每秒 53,000 次插入。sqlite3_clear_bindingssqlite3_reset

PRAGMA 同步 = OFF

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

/* 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 = 记忆

考虑通过计算 将回滚日志存储在内存中。您的事务会更快，但是如果在事务期间断电或程序崩溃，则数据库可能会处于损坏状态，事务已部分完成：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 记录 in 13.50 秒

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

PRAGMA 同步 = OFF，PRAGMA journal_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 次插入。

使用 In-Memory 数据库

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

#define DATABASE ":memory:"

导入的 864913 记录 在 10.94 秒

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

重构 C 代码

虽然不是专门针对 SQLite 的改进，但我不喜欢循环中的额外赋值操作。让我们快速重构该代码，将 的输出直接传递到 ，并让编译器尝试为我们加快速度：char*whilestrtok()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);

导入的 864913 记录 在 13.66 秒

正如预期的那样，如果对一列编制索引，则大容量插入速度会较慢，但如果在插入数据后创建索引，则确实会有所不同。我们的无索引基线是每秒 96,000 次插入。首先创建索引，然后插入数据，每秒插入 47,700 次，而先插入数据，然后创建索引，每秒插入 63,300 次。

我很乐意接受其他方案的建议来尝试......并将很快为 SELECT 查询编译类似的数据。