提高SQLite的每秒INSERT性能？-Java 学习之路

2663

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

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

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

The Experiment: 我不是简单地谈论一般意义上的性能提示（即"Use a transaction!"），而是认为最好编写一些C代码并实际测量各种选项的影响 . 我们将从一些简单的数据开始：

complete transit schedule for the city of Toronto的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），但我怀疑这些结果与最新版本相当（如果你不这么想请发表评论） .

我们来写一些代码吧！

The Code: 一个简单的C程序，它逐行读取文本文件，将字符串拆分为值，然后将数据插入到SQLite数据库中 . 在这个"baseline"版本的代码中，创建了数据库，但我们实际上不会插入数据：

/*************************************************************
    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 inserts per second.

使用交易

默认情况下，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 inserts per second.

使用准备好的声明

使用事务是一个巨大的改进，但重新编译每个插入的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条记录

太好了！有's a little bit more code (don'忘记打电话给 sqlite3_clear_bindings 和 sqlite3_reset ），但我们的表现增加了一倍以上 53,000 inserts per second.

PRAGMA synchronous = OFF

默认情况下，SQLite将在发出操作系统级写入命令后暂停 . 这可以保证将数据写入磁盘 . 通过设置 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 inserts per second.

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 inserts per second. 上的优化要慢一点

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

在12.00秒内导入864913条记录

太棒了！我们能做 72,000 inserts per second.

使用内存数据库

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

#define DATABASE ":memory:"

在10.94秒内导入864913条记录

它是令人印象深刻的，我们可以执行 79,000 inserts per second.

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

Note: We are back to using a real database file. In-memory databases are fast, but not necessarily practical

在8.94秒内导入864913条记录

对我们的参数绑定中使用的字符串处理代码进行了轻微的重构，这使我们能够执行 96,700 inserts per second. 我认为可以安全地说这很快 . 当我们开始调整其他变量（即页面大小，索引创建等）时，这将成为我们的基准 .

摘要（目前为止）

我希望你还和我在一起！我们开始沿着这条道路前进的原因是，SQLite的批量插入性能变化如此之大，并且它已经优化了我们的代码和SQLite的使用方法 from a worst-case scenario of 85 inserts per second to over 96,000 inserts per second!

CREATE INDEX然后INSERT与INSERT然后CREATE INDEX

在我们开始测量 SELECT 性能之前，我们知道在下面的一个答案中我们已经建议'll be creating indexes. It'在进行批量插入时，在插入数据后创建索引会更快（而不是先创建索引然后插入数据） . 我们试试吧：

Create Index then Insert Data

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

Insert Data then Create Index

...
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次插入 . Creating the index first then inserting data gives us 47,700 inserts per second, whereas inserting the data first then creating the index gives us 63,300 inserts per second.

我很乐意为其他场景尝试建议......并且很快就会为SELECT查询编译类似的数据 .

9 回答

87

批量插入

灵感来自这篇文章以及引发我的Stack Overflow问题 - Is it possible to insert multiple rows at a time in an SQLite database? - 我发布了我的第一个Git存储库：

https://github.com/rdpoor/CreateOrUpdate

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

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

回复于 2024-04-26T13:19:55+08:00
43

如果您可以对 INSERT/UPDATE 语句进行分块，则批量导入似乎表现最佳 . 在一张只有几排YYV的 table 上， Value 10,000左右的价格对我来说效果很好......

回复于 2024-04-26T13:19:55+08:00
692

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

回复于 2024-04-26T13:19:55+08:00
35

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

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

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

回复于 2024-04-26T13:19:55+08:00
112

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

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

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

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

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

我的解决方案是使用 multiple 交易 . 我每隔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速率 . 通过单击所需输出驱动器上的属性，您可以在常规选项卡中看到两个选项 . 选项1：压缩此驱动器，选项2：允许此驱动器的文件将内容编入索引 .

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

回复于 2024-04-26T13:19:55+08:00
24
几个提示：
- 在事务中放置插入/更新 .
- 对于旧版本的SQLite - 考虑一种不那么偏执的日记模式（ pragma journal_mode ） . 有 NORMAL ，然后有 OFF ，如果您不太担心如果操作系统崩溃可能会损坏数据库，则可以显着提高插入速度 . 如果您的应用程序崩溃，数据应该没问题 . 请注意，在较新的版本中， OFF/MEMORY 设置对于应用程序级别崩溃是不安全的 .
- 使用页面大小也会产生影响（ PRAGMA page_size ） . 具有较大的页面大小可以使读取和写入更快，因为较大的页面保存在内存中 . 请注意，您的数据库将使用更多内存 .
- 如果您有索引，请考虑在完成所有插入后调用 CREATE INDEX . 这比创建索引然后执行插入要快得多 .
- 如果您具有对SQLite的并发访问权限，则必须非常小心，因为在写入完成时整个数据库都被锁定，尽管可能有多个读取器，但写入将被锁定 . 通过在较新的SQLite版本中添加WAL，这有所改善 .
- 利用节省空间......较小的数据库更快 . 例如，如果您有键值对，请尽可能使键成为 INTEGER PRIMARY KEY ，这将替换表中隐含的唯一行号列 .
- 如果您使用多个线程，可以尝试使用shared page cache，这将允许在线程之间共享加载的页面，这可以避免昂贵的I / O调用 .
- Don't use !feof(file)!
我也问过类似的问题here和here .
回复于 2024-04-26T13:19:55+08:00
15

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

SqlAlchemy Orm作者的回答Why is SQLAlchemy insert with sqlite 25 times slower than using sqlite3 directly?在0.5秒内有100k插入，我在python-sqlite和SqlAlchemy中看到了类似的结果 . 这让我相信sqlite3的性能有所提升

回复于 2024-04-26T13:19:55+08:00
8

Avoid sqlite3_clear_bindings(stmt);

测试中的代码每次都应该设置绑定就足够了 .

来自SQLite文档的C API简介说

在第一次调用sqlite3_step（）之前或在sqlite3_reset（）之后立即调用之前，应用程序可以调用其中一个sqlite3_bind（）接口来将值附加到参数 . 每次调用sqlite3_bind（）都会覆盖对同一参数的先前绑定

（见：sqlite.org/cintro.html） . that function的文档中没有任何内容表示除了简单地设置绑定外，还必须调用它 .

更多细节：http://www.hoogli.com/blogs/micro/index.html#Avoid_sqlite3_clear_bindings()

回复于 2024-04-26T13:19:55+08:00
50
如果你只关心阅读，那么更快（但可能读取过时的数据）版本是从多个线程的多个连接中读取（每个线程的连接） .

首先找到表中的项目：
```
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（因为它是全局启用的）
回复于 2024-04-26T13:19:55+08:00

提高SQLite的每秒INSERT性能？