提高SQLite每秒INSERT的性能？

最新推荐文章于 2024-05-12 00:28:17 发布

asdfgh0077

最新推荐文章于 2024-05-12 00:28:17 发布

阅读量2.4k

点赞数 1

文章标签： c performance sqlite optimization

原文链接：https://stackoom.com/question/7BGx/提高SQLite每秒INSERT的性能

版权

这篇博客介绍了如何通过优化SQLite的配置和使用C代码来大幅提升INSERT操作的性能。从每秒85次提升到96,000次，涉及的优化包括使用事务、准备好的语句、调整PRAGMA设置，以及在内存中操作数据库等。通过实验，作者展示了这些优化措施如何显著提高SQLite在大容量插入时的速度。" 135898436,671655,手写数据库toadb：物理存储模型与行列混合存储解析,"['数据库', 'C语言', '架构', '数据库内核']

摘要由CSDN通过智能技术生成

优化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_bindings和sqlite3_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楼

避免使用sqlite3_clear_bindings(stmt) 。

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

SQLite文档中的C API简介说：

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

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

详细信息：避免使用_sqlite3_clear_bindings（）

#2楼

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

#3楼

几个技巧：

将插入/更新放入事务中。
对于较旧版本的SQLite-考虑较少的偏执日志模式（ pragma journal_mode ）。有NORMAL ，然后有OFF ，如果您不太担心数据库可能因操作系统崩溃而损坏，则可以显着提高插入速度。如果您的应用程序崩溃了，数据应该没问题。请注意，在较新的版本中， OFF/MEMORY设置对于应用程序级崩溃不安全。
使用页面大小也会有所不同（ PRAGMA page_size ）。由于较大的页面保留在内存中，因此具有较大的页面大小可以使读取和写入的速度更快。请注意，更多的内存将用于您的数据库。
如果有索引，请在完成所有插入操作后考虑调用CREATE INDEX 。这比创建索引然后进行插入要快得多。
如果您可以并发访问SQLite，则必须非常小心，因为写入完成后整个数据库将被锁定，尽管可能有多个读取器，但写入将被锁定。通过在较新的SQLite版本中添加WAL，已对此进行了一些改进。
利用节省空间的优势...较小的数据库运行更快。例如，如果您具有键值对，请尝试尽可能使键成为INTEGER PRIMARY KEY ，它将替换表中隐含的唯一行号列。
如果使用多个线程，则可以尝试使用共享页面缓存，这将允许在线程之间共享已加载的页面，从而避免了昂贵的I / O调用。
不要使用!feof(file) ！

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

#4楼

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

首先在表中找到项目：

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（因为它是为我们全局启用的）

#5楼

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

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

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

#6楼

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

#7楼

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

我有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分钟）完成。如果遇到插入缓慢的情况，请检查硬盘驱动器是否配置了这些选项。这将为您节省大量时间和寻找解决方案的麻烦

#8楼

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

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

#9楼

使用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”部分以了解更多详细信息

#10楼

在散装刀片上

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

~~https://github.com/rdpoor/CreateOrUpdate~~

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

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

asdfgh0077

关注

1
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫