mysql数据写入速度和什么有关数据库写入速度

转载

蓝月亮 2023-12-10 15:51:36

文章标签 mysql数据写入速度和什么有关 sqlite Test sql 文章分类 MySQL 数据库

最近项目中使用到了Sqlite3来保存结果数据，大约100万条数据，插入到sqlite数据库中竟然耗时5分钟，这在真个数据处理流程中占用了太多的时间，是不可接受的，那么如何优化sqlite的写数据的性能呢？

优化方式

通过查阅资料和其他大牛们的博客，确定有几个点可以尝试：

关闭写同步，PRAGMA synchronous = OFF，在 sqlite3 中 synchronous 有三种模式，分别是 FULL，NORMAL 和 OFF，在系统意外终止的时候，安全性逐级减弱，FULL模式下，保证数据不会损坏，安全性最高，写入速度也最慢。OFF 模式会比 FULL 模式快50倍以上。
使用事务，如果有许多数据需要插入数据库，逐条插入，导致频繁的提交以及磁盘IO，使用事务机制，可以批量插入数据，可以极大的提升写入速度。实际测试中的情况是，开启事务之后，写入速度也可以提升近50倍。
执行准备，执行准备相当于将sql语句提前编译，省去每次执行sql语句时候的语法检查等操作，可以极大的优化sql语句的执行效率，其原理有点像 LuaJit 将 Lua 语言成静态机器码，提高运行速度。实测情况中，使用执行准备可以提升40倍的写入速度。
内存模式，sqlite3 支持内存模式，将数据库直接创建到内存中，打开地址传入”:memory:”即可，内存模式相比正常模式，可以省区IO的时间，使用内存模式的加速思路是，先将数据库创建到内存中，数据写入完整之后，再调用 “VACUUM INTO ‘out.db3’;” 语句将其写入到磁盘，在开启了执行准备的情况下，这种方式会稍微快上一点点。

效率对比

使用上面提到的方法，测试下来速度对比如下所示：

优化方法	无优化	关闭写同步	开启事务	执行准备	内存模式
每秒插入	13条	1321条	5万条	213万条	215万条

测试代码

错误检查宏定义：

#define CHECKZERO(a) if((a)!=0) throw("error.");

无优化

sqlite3* db = nullptr;
CHECKZERO(sqlite3_open(path, &db));
CHECKZERO(sqlite3_exec(db, "CREATE TABLE Test(ID INTEGER,var0 INTEGER,var1 REAL,var2 TEXT);", 0, 0, 0));
const int maxcount = 100;
for (int i = 0; i < maxcount; i++) {
    CHECKZERO(sqlite3_exec(db, "INSERT INTO Test (ID,var0,var1,var2) VALUES (0,1,2.0,\\"hello sqlite3.\\");", 0, 0, 0));
}
CHECKZERO(sqlite3_close(db));

关闭写同步

sqlite3* db = nullptr;
CHECKZERO(sqlite3_open(path, &db));
CHECKZERO(sqlite3_exec(db, "PRAGMA synchronous = OFF", 0, 0, 0));
CHECKZERO(sqlite3_exec(db, "CREATE TABLE Test(ID INTEGER,var0 INTEGER,var1 REAL,var2 TEXT);", 0, 0, 0));
const int maxcount = 10000;
for (int i = 0; i < maxcount; i++) {
    CHECKZERO(sqlite3_exec(db, "INSERT INTO Test (ID,var0,var1,var2) VALUES (0,1,2.0,\\"hello sqlite3.\\");", 0, 0, 0));
}
CHECKZERO(sqlite3_close(db));

开启事务

sqlite3* db = nullptr;
CHECKZERO(sqlite3_open(path, &db));
CHECKZERO(sqlite3_exec(db, "PRAGMA synchronous = OFF", 0, 0, 0));
CHECKZERO(sqlite3_exec(db, "CREATE TABLE Test(ID INTEGER,var0 INTEGER,var1 REAL,var2 TEXT);", 0, 0, 0));
CHECKZERO(sqlite3_exec(db, "BEGIN", 0, 0, 0));
const int maxcount = 1000000;
for (int i = 0; i < maxcount; i++) {
    CHECKZERO(sqlite3_exec(db, "INSERT INTO Test (ID,var0,var1,var2) VALUES (0,1,2.0,\\"hello sqlite3.\\");", 0, 0, 0));
    if (i % 10000 == 9999) {
        CHECKZERO(sqlite3_exec(db, "COMMIT", 0, 0, 0));
        CHECKZERO(sqlite3_exec(db, "BEGIN", 0, 0, 0));
    }
}
CHECKZERO(sqlite3_exec(db, "COMMIT", 0, 0, 0));
CHECKZERO(sqlite3_close(db));

执行准备

sqlite3* db = nullptr;
CHECKZERO(sqlite3_open(path, &db));
CHECKZERO(sqlite3_exec(db, "PRAGMA synchronous = OFF", 0, 0, 0));
CHECKZERO(sqlite3_exec(db, "CREATE TABLE Test(ID INTEGER,var0 INTEGER,var1 REAL,var2 TEXT);", 0, 0, 0));
// 执行准备
sqlite3_stmt *pPrepare = nullptr;
auto sql = "INSERT INTO Test (ID,var0,var1,var2) VALUES (?,?,?,?);";
CHECKZERO(sqlite3_prepare_v2(db, sql, strlen(sql), &pPrepare, 0));
CHECKZERO(sqlite3_exec(db, "BEGIN", 0, 0, 0));
const int maxcount = 10000000;
for (int i = 0; i < maxcount; i++) {
    CHECKZERO(sqlite3_reset(pPrepare));
    CHECKZERO(sqlite3_bind_int(pPrepare, 1, 0));
    CHECKZERO(sqlite3_bind_int(pPrepare, 2, 1));
    CHECKZERO(sqlite3_bind_double(pPrepare, 3, 2.0));
    const char* str = "hello sqlite3.";
    CHECKZERO(sqlite3_bind_text(pPrepare, 4, str, strlen(str), 0));
    int err = sqlite3_step(pPrepare);
    assert(SQLITE_DONE == err);
    if (i % 10000 == 9999) {
        CHECKZERO(sqlite3_exec(db, "COMMIT", 0, 0, 0));
        CHECKZERO(sqlite3_exec(db, "BEGIN", 0, 0, 0));
    }
}
CHECKZERO(sqlite3_exec(db, "COMMIT", 0, 0, 0));
CHECKZERO(sqlite3_finalize(pPrepare)); // 释放
CHECKZERO(sqlite3_close(db));

内存模式

sqlite3* db = nullptr;
CHECKZERO(sqlite3_open(":memory:", &db));
CHECKZERO(sqlite3_exec(db, "PRAGMA synchronous = OFF", 0, 0, 0));
CHECKZERO(sqlite3_exec(db, "CREATE TABLE Test(ID INTEGER,var0 INTEGER,var1 REAL,var2 TEXT);", 0, 0, 0));
// 执行准备
sqlite3_stmt *pPrepare = nullptr;
auto sql = "INSERT INTO Test (ID,var0,var1,var2) VALUES (?,?,?,?);";
CHECKZERO(sqlite3_prepare_v2(db, sql, strlen(sql), &pPrepare, 0));
CHECKZERO(sqlite3_exec(db, "BEGIN", 0, 0, 0));
const int maxcount = 10000000;
for (int i = 0; i < maxcount; i++) {
    CHECKZERO(sqlite3_reset(pPrepare));
    CHECKZERO(sqlite3_bind_int(pPrepare, 1, 0));
    CHECKZERO(sqlite3_bind_int(pPrepare, 2, 1));
    CHECKZERO(sqlite3_bind_double(pPrepare, 3, 2.0));
    const char* str = "hello sqlite3.";
    CHECKZERO(sqlite3_bind_text(pPrepare, 4, str, strlen(str), 0));
    int err = sqlite3_step(pPrepare);
    assert(SQLITE_DONE == err);
    if (i % 10000 == 9999) {
        CHECKZERO(sqlite3_exec(db, "COMMIT", 0, 0, 0));
        CHECKZERO(sqlite3_exec(db, "BEGIN", 0, 0, 0));
    }
}
CHECKZERO(sqlite3_exec(db, "COMMIT", 0, 0, 0));
CHECKZERO(sqlite3_finalize(pPrepare)); // 释放
// 导出
CHECKZERO(sqlite3_exec(db, "VACUUM INTO 'out.db3';", 0, 0, 0));
CHECKZERO(sqlite3_close(db));