提高 SQLite 每秒 INSERT 的性能?

优化 SQLite 是棘手的。 C 应用程序的大容量插入性能可以从每秒 85 次插入到每秒超过 96,000 次插入!

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

基本原理: 最初,我对看到的性能感到失望。事实证明,取决于数据库的配置方式和使用 API 的方式,SQLite 的性能可能会发生很大的变化(批量插入和选择)。弄清楚所有选项和技术是什么都不是一件容易的事,因此,我认为创建此社区 Wiki 条目与 Stack Overflow 读者共享结果以节省其他人的麻烦是审慎的做法。

实验:我认为,最好是编写一些 C 代码并实际衡量各种选择的影响,而不是简单地谈论一般意义上的性能提示(即“使用事务!” )。我们将从一些简单的数据开始:

  • 28 MB TAB 分隔的文本文件(约 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 和字符串处理操作的速度。

在 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 条记录

kes! 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_bindingssqlite3_reset ),但是我们的性能提高了一倍以上,达到每秒 53,000 次插入。

PRAGMA 同步 = OFF

默认情况下,SQLite 将在发出 OS 级写命令后暂停。这样可以确保将数据写入磁盘。通过设置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 = 内存

考虑通过评估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 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);

在 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 查询编译类似的数据。

答案

几个技巧:

  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)

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

对于这些插入,请尝试使用SQLITE_STATIC而不是SQLITE_TRANSIENT

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

SQLITE_STATIC告诉您,您给它提供的内存地址在执行查询之前将一直有效(在此循环中始终如此)。这将为您节省每个循环几个分配,复制和取消分配操作。可能会有很大的改善。

避免使用sqlite3_clear_bindings(stmt)

测试中的代码每次设置绑定就足够了。

SQLite 文档中的C API 简介说:

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

sqlite3_clear_bindings的文档中,没有任何内容表明您必须调用它,而不仅仅是设置绑定。

详细信息: 避免使用_sqlite3_clear_bindings()

在散装刀片上

受到这篇文章以及导致我在此处出现的堆栈溢出问题的启发 - 是否可以一次在 SQLite 数据库中插入多行? - 我发布了我的第一个Git存储库:

https://github.com/rdpoor/CreateOrUpdate

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

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

如果可以对INSERT / UPDATE语句进行分块,则批量导入似乎表现最佳。在只有几行的表 YMMV 上,值 10,000 左右对我来说效果很好。

如果只关心读取,则从多个线程的多个连接(每个线程的连接)中读取速度会稍快一些(但可能会读取过时的数据)。

首先在表中找到项目:

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),这可以提高 50-75%的速度(在 Windows 7 上为 3.8.0.2 64 位)。我们的表是高度非规范化的(1000-1500 列,大约 100,000 或更多行)。

太多或太少的线程将无法执行此操作,您需要对自己进行基准测试和分析。

同样对我们来说,SHAREDCACHE 降低了性能,所以我手动设置了 PRIVATECACHE(因为它是为我们全局启用的)

在将 cache_size 提高到更高的值之前,我不会从交易中获得任何收益,即PRAGMA cache_size=10000;

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

我有 4-5 个包含地址的文件。每个文件有大约 3000 万条记录。我使用与您建议的配置相同的配置,但是每秒的 INSERT 数量非常低(每秒约 10.000 条记录)。

这是您的建议失败的地方。您对所有记录使用一个事务,并使用一个插入而没有错误 / 失败。假设您将每个记录拆分为不同表上的多个插入。如果唱片被打破了怎么办?

ON CONFLICT 命令不适用,因为如果一条记录中有 10 个元素,并且您需要将每个元素插入到不同的表中,如果元素 5 出现 CONSTRAINT 错误,那么前面的所有 4 个插入也都需要执行。

所以这就是回滚的地方。回滚的唯一问题是您丢失了所有插入内容并从顶部开始。你怎么解决呢?

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

该解决方案帮助我绕开了处理包含不良 / 重复记录(我的不良记录几乎占 4%)的文件时遇到的问题。

我创建的算法帮助我将流程减少了 2 个小时。文件的最终加载过程 1hr 30m 仍然很慢,但与最初花费的 4hrs 相比没有。我设法将插入速度从 10.000 / s 加快到〜14.000 / s

如果有人对如何加快速度有其他想法,我欢迎您提出建议。

更新

除了上面我的回答,您还应该记住每秒插入数也取决于您使用的硬盘驱动器。我在具有不同硬盘的 3 台不同 PC 上进行了测试,但时间差异很大。 PC1(1hr 30m),PC2(6hrs)PC3(14hrs),所以我开始怀疑为什么会这样。

经过两周的研究并检查了多种资源:硬盘,Ram,缓存,我发现硬盘上的某些设置会影响 I / O 速率。通过单击所需输出驱动器上的属性,可以在常规选项卡中看到两个选项。 Opt1:压缩该驱动器,Opt2:允许对该驱动器的文件进行索引。

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

您的问题的答案是,较新的 sqlite3 可以提高性能,请使用该功能。

这个答案为什么 SQLAlchemy 用 sqlite 插入比直接使用 sqlite3 慢 25 倍?作者:SqlAlchemy Orm 作者在 0.5 秒内插入了 10 万次插入,我在 python-sqlite 和 SqlAlchemy 中也看到了类似的结果。这使我相信 sqlite3 的性能有所提高

使用 ContentProvider 在 db 中插入批量数据。以下用于将批量数据插入数据库的方法。这应该提高 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();

}

调用 bulkInsert 方法:

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

链接: https : //www.vogella.com/tutorials/AndroidSQLite/article.html检查 “使用 ContentProvider” 部分以了解更多详细信息