题 提高SQLite的每秒INSERT性能?


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

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

理由:  最初我对我看到的表现感到失望。 事实证明,SQLite的性能可能会有很大差异(对于批量插入和选择),具体取决于数据库的配置方式以及您使用API​​的方式。弄清楚所有选项和技术是什么并不是一件小事,所以我认为创建这个社区wiki条目与Stack Overflow读者分享结果是谨慎的,以便为其他人节省相同调查的麻烦。

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

  • 一个28 MB的TAB分隔文本文件(大约865,000条记录) 多伦多市完整的运输时间表
  • 我的测试机器是运行Windows XP的3.60 GHz P4。
  • 代码是用。编译的 Visual C ++ 2005年作为“完全优化”(/ Ox)和赞成快速代码(/ 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和字符串处理操作的速度。

在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次插入。

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);

在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改进,但我不喜欢额外的 char*分配操作 while 循环。让我们快速重构该代码以传递输出 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次插入!


创建INDEX然后INSERT与INSERT然后CREATE INDEX

在我们开始测量之前 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查询编译类似的数据。


2573


起源


好点子!在我们的例子中,我们处理从XML和CSV文本文件读取的大约150万个键/值对到200k记录。与运行SO等网站的数据库相比较小 - 但足够大,调整SQLite性能变得很重要。 - Mike Willekes
“我们有大量的配置数据存储在XML文件中,这些数据被解析并加载到SQLite数据库中,以便在初始化应用程序时进行进一步处理。”为什么不首先将所有内容保存在sqlite数据库中,而不是以XML格式存储,然后在初始化时加载所有内容? - CAFxX
你试过不打电话吗? sqlite3_clear_bindings(stmt);?您每次都应该设置绑定就足够了: 在第一次调用sqlite3_step()之前或在sqlite3_reset()之后立即调用之前,应用程序可以调用其中一个sqlite3_bind()接口来将值附加到参数。每次调用sqlite3_bind()都会覆盖对同一参数的先前绑定 (看到: sqlite.org/cintro.html)。这里什么都没有 该功能的文档 说你必须打电话给它。 - ahcox
ahcox:绑定是指向的地址而不是变量,因此不起作用 strtok 每次都返回一个新指针。你要么必须这样做 strcpy 每次之后 strtok 或者创建自己的tokenizer,它总是在沿着字符串读取时进行复制。 - nemetroid
你做过重复测量了吗?避免7个本地指针的4s“胜利”是奇怪的,即使假设一个混乱的优化器。 - peterchen


答案:


几个提示:

  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)

我也问了类似的问题 这里 和 这里


672



Docs不知道PRAGMA journal_mode NORMAL sqlite.org/pragma.html#pragma_journal_mode - OneWorld
已经有一段时间了,我的建议在引入WAL之前应用于旧版本。看起来DELETE是新的正常设置,现在还有OFF和MEMORY设置。我认为OFF / MEMORY会以牺牲数据库完整性为代价来提高写入性能,而OFF会完全禁用回滚。 - Snazzer
对于#7,你有一个如何启用的例子 共享页面缓存 使用c#system.data.sqlite包装器? - Aaron Hudon
#4带回了古老的记忆 - 在之前的时间里至少有一个案例,在一组添加之前删除索引并重新创建它之后显着加速了插入。对于某些添加,您可能仍然可以在现代系统上更快地运行,您知道在此期间您可以单独访问该表。 - Bill K


尝试使用 SQLITE_STATIC 代替 SQLITE_TRANSIENT 对于那些插入物。

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

SQLITE_STATIC 告诉它你给它的内存地址在查询执行之前是有效的(在这个循环中总是如此)。这将为每个循环节省几次分配,复制和取消分配操作。可能是一个很大的改进。


102





避免使用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()


80



非常正确:“与许多人的直觉相反,sqlite3_reset()不会重置预准备语句上的绑定。使用此例程将所有主机参数重置为NULL。” - sqlite.org/c3ref/clear_bindings.html - Francis Straccia


批量插入

灵感来自这篇帖子和Stack Overflow问题让我在这里 - 是否可以在SQLite数据库中一次插入多行?  - 我发布了我的第一个 混帐 库:

https://github.com/rdpoor/CreateOrUpdate

哪个批量加载ActiveRecords数组 MySQL的,SQLite或 PostgreSQL的 数据库。它包括一个忽略现有记录,覆盖它们或引发错误的选项。我的基本测试表明,与顺序写入相比,速度提高了10倍 - YMMV。

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


47



@Jess:如果你按照链接,你会发现他的意思是批量插入语法。 - Alix Axel


如果你可以大块进口,批量进口似乎表现最好 INSERT / UPDATE 声明。在一张只有几排YYV的桌子上,价值10,000左右的价格对我来说效果很好......


40



您需要调整x = 10,000,以便x = cache [= cache_size * page_size] /插入的平均大小。 - Alix Axel


如果你只关心阅读,那么更快(但可能读取过时的数据)版本是从多个线程的多个连接中读取(每个线程的连接)。

首先找到表中的项目:

 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(因为它是全局启用的)


32





在将cache_size提升到更高的值之前,我不能从事务中获得任何收益,即 PRAGMA cache_size=10000;


20





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

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

这是你的建议失败的地方。您对所有记录使用单个事务,并且没有错误/失败的单个插入。假设您将每条记录拆分为不同表上的多个插入。如果记录坏了会怎么样?

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

所以这里是回滚的来源。回滚的唯一问题是您丢失了所有插入并从顶部开始。你怎么解决这个问题?

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

这个解决方案帮助我绕过了处理包含错误/重复记录的文件时遇到的问题(我的记录差不多有4%)。

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

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

UPDATE

除了上面的答案,你应该记住,每秒插入次数取决于你使用的硬盘驱动器。我在具有不同硬盘驱动器的3台不同PC上进行了测试,并且在时间上有很大差异。 PC1(1小时30分钟),PC2(6小时)PC3(14小时),所以我开始想知道为什么会这样。

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

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


11





您的问题的答案是,较新的sqlite3已经提高了性能,使用它。

这个答案 为什么SQLAlchemy使用sqlite插入比直接使用sqlite3慢25倍? 通过SqlAlchemy Orm作者在0.5秒内有100k插入,我在python-sqlite和SqlAlchemy上看到了类似的结果。这让我相信sqlite3的性能有所提升


5





保罗贝茨关于他如何制作C#的演讲形式很棒 akavache 很快: https://www.youtube.com/watch?v=j7WnQhwBwqA

也许你可以找到一些线索给你。在这里做简短摘要太长了


3