Code
2023-03-01 07:00:12

提高SQLite的每秒INSERT性能

优化SQLite很棘手。C应用程序的大容量插入性能可以从每秒85个插入到每秒96000多个插入不等！

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

理由：最初我对我看到的表现感到失望。事实证明，SQLite的性能可能会有很大的差异（对于批量插入和选择），这取决于数据库的配置方式和API的使用方式。弄清楚所有选项和技术都是什么并不是一件小事，所以我认为创建这个社区wiki条目以与Stack Overflow读者共享结果是明智的，这样可以避免其他人进行同样的调查。

实验：与其简单地谈论一般意义上的性能提示（即“使用事务！”），我认为最好编写一些C代码，并实际衡量各种选项的影响。我们将从一些简单的数据开始：

一个28 MB TAB分隔的文本文件（约865000条记录），其中包含多伦多市的完整运输时间表我的测试机器是运行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条记录秒

太棒了我们可以每秒执行920000次插入，但前提是我们实际上不执行任何插入：-）

“最坏情况”

我们将使用从文件中读取的值生成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条记录秒

那更好。只需在一个事务中包装所有插入，我们的性能就提高到每秒23000个插入。

使用准备好的语句

使用事务是一个巨大的改进，但是如果我们反复使用相同的SQL，那么为每个插入重新编译SQL语句就没有意义了。让我们使用sqlite3_prepare_v2编译一次SQL语句，然后使用sqlite_3_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同步=关闭

默认情况下，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条记录秒

这些改进现在更小了，但我们的插入速度达到了每秒69600次。

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

比之前的优化速度稍慢，每秒64000次插入。

PRAGMA synchronous=关闭，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条记录秒

好极了我们每秒可以进行72000次插入。

使用内存数据库

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

#define DATABASE ":memory:"

10.94年导入864913条记录秒

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

重构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条记录秒

对参数绑定中使用的字符串处理代码进行了轻微的重构，使我们能够每秒执行96700次插入。我认为可以肯定地说，这是非常快的。当我们开始调整其他变量（例如页面大小、索引创建等）时，这将是我们的基准。

摘要（迄今为止）

我希望你还在我身边！我们开始走这条路的原因是，SQLite的大容量插入性能差异很大，而且需要进行哪些更改来加快操作并不总是显而易见的。使用相同的编译器（和编译器选项）、相同版本的SQLite和相同的数据，我们优化了我们的代码和SQLite的使用，从最坏的情况下每秒85次插入到每秒96000次插入！

CREATE INDEX然后INSERT vs.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条记录秒

正如预期的那样，如果对一列进行索引，则大容量插入速度较慢，但如果在插入数据之后创建索引，则会有所不同。我们的无索引基线是每秒96000个插入。首先创建索引，然后插入数据，每秒可以插入47700个数据，而首先插入数据，然后创建索引，每秒可以进行63300个插入。

我很乐意接受其他场景的建议。。。并且将很快为SELECT查询编译类似的数据。

当前回答

在我将cache_size提高到更高的值（即PRAGMA cache_size=10000）之前，我无法从事务中获得任何收益；

2015-04-15 09:47:49

其他回答

批量插入时

受这篇文章和堆栈溢出问题的启发，我来到这里——是否可以在SQLite数据库中一次插入多行？——我发布了我的第一个Git存储库：

https://github.com/rdpoor/CreateOrUpdate

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

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

2011-05-20 15:50:06

您的问题的答案是，更新的SQLite3提高了性能，请使用它。

这个答案为什么SQLAlchemy插入sqlite比直接使用sqlite3慢25倍？由SqlAlchemy Orm Author在0.5秒内完成了100k次插入，我在python-sqlite和SqlAlchem中也看到了类似的结果。这使我相信SQLite3的性能有所提高。

2017-06-11 21:49:34

几个提示：

在事务中插入/更新。对于旧版本的SQLite-考虑一种不那么偏执的日志模式（pragma journal_mode）。有NORMAL（正常），然后有OFF（关闭），如果您不太担心操作系统崩溃时数据库可能会损坏，这可以显著提高插入速度。如果应用程序崩溃，数据应该是正常的。请注意，在较新版本中，OFF/MORY设置对于应用程序级崩溃不安全。玩页面大小也会有所不同（PRAGMA page_size）。拥有更大的页面大小可以使读写速度更快，因为内存中存储的页面更大。请注意，数据库将使用更多内存。如果您有索引，请考虑在执行所有插入之后调用CREATEINDEX。这比创建索引然后执行插入要快得多。如果您可以并发访问SQLite，则必须非常小心，因为在写入操作完成时，整个数据库都被锁定，尽管可以有多个读取器，但写入操作将被锁定。这在较新的SQLite版本中添加了WAL后有所改进。利用节省空间的优势。。。较小的数据库运行速度更快。例如，如果您有键值对，如果可能，请尝试将该键设置为INTEGERPRIMARYKEY，这将替换表中隐含的唯一行号列。如果使用多个线程，可以尝试使用共享页面缓存，这将允许在线程之间共享加载的页面，从而避免昂贵的I/O调用。不要使用！feof（文件）！

我也问过类似的问题。

2009-11-11 03:45:59

读完本教程后，我尝试将其应用到我的程序中。

我有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

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

更新：

除了我上面的答案之外，您还应该记住，每秒插入的数据也取决于您使用的硬盘驱动器。我在3台不同的电脑上用不同的硬盘进行了测试，得到了时间上的巨大差异。PC1（1小时30米），PC2（6小时）PC3（14小时），所以我开始想为什么会这样。

经过两周的研究和检查多种资源：硬盘、内存、缓存，我发现硬盘上的某些设置会影响I/O速率。通过单击所需输出驱动器上的财产，您可以在常规选项卡中看到两个选项。选项1：压缩此驱动器，选项2：允许此驱动器的文件建立内容索引。

通过禁用这两个选项，所有3台电脑现在需要大约相同的时间才能完成（1小时和20至40分钟）。如果遇到插入速度慢的情况，请检查硬盘是否配置了这些选项。这将为您节省大量时间和头疼的问题

2017-04-12 16:35:09

尝试对这些插入使用SQLITE_STATIC而不是SQLITE_TRANSIENT。

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

SQLITE_STATIC告诉它，在执行查询之前，您给它的内存地址是有效的（在这个循环中总是这样）。这将为每个循环节省几个分配、复制和解除分配操作。可能是一个很大的改进。

2014-09-17 16:47:45

推荐文章

aliyun

最新文章

标签

2024 code 京ICP备15047053号-1