SQLITE高速插入数据

SQLITE数据库在写入数据时，写入数据库的速度非常重要，如果写入的速度慢，有大量数据待写入时，一是会造成程序卡顿，二是数据写入会失败，数据缺失。

传统的往数据库里插入数据是执行一条SQL语句，多条数据就是执行多条SQL语句。执行SQL语句意味着打开数据库再关闭数据库，数据库本身是一个文件，打开和关闭IO操作是非常耗时的。

    QSqlQuery query(_mes_db);
    auto start = std::chrono::steady_clock::now();
    for(int i = 0; i < 100000; ++i)
    {
        QString sql = "insert into test_table(log_type,log_level,log_info) values('WARN',1,'test alarm')";
        query.exec(sql);
    }
    auto end = std::chrono::steady_clock::now();
    auto duration = std::chrono::duration_cast<std::chrono::milliseconds>(end - start).count();
    qDebug() << "consuming time=" << duration;

上面代码插入10 0000条数据耗时828816ms，也就是13.8分钟

如果在操作数据库时，只打开一次数据库，将所有待写入的数据准备好也即插入，最后再一起提交，关闭数据库，则会大大提高数据库IO效率。这种操作就是数据库的事务操作。

在SQLite中，数据库配置的参数都由编译指示（pragma）来实现的，而其中synchronous写同步选项有三种可选状态，分别是full、normal、off。

    PRAGMA synchronous = FULL;

    PRAGMA synchronous = NORMAL;

    PRAGMA synchronous = OFF;

当synchronous设置为FULL (2), SQLite数据库引擎在紧急时刻会暂停以确定数据已经写入磁盘。这使系统崩溃或电源出问题时能确保数据库在重起后不会损坏。FULL synchronous很安全但很慢。

当synchronous设置为NORMAL, SQLite数据库引擎在大部分紧急时刻会暂停，但不像FULL模式下那么频繁。 NORMAL模式下有很小的几率(但不是不存在)发生电源故障导致数据库损坏的情况。但实际上，在这种情况 下很可能你的硬盘已经不能使用，或者发生了其他的不可恢复的硬件错误。

设置为synchronous OFF (0)时，SQLite在传递数据给系统以后直接继续而不暂停。若运行SQLite的应用程序崩溃， 数据不会损伤，但在系统崩溃或写入数据时意外断电的情况下数据库可能会损坏。另一方面，在synchronous OFF时 一些操作可能会快50倍甚至更多。在SQLite3中，缺省值为FULL。

因此可以关闭写同步，加快写入速度（速度小幅提升）。

SQLITE执行SQL语句一种方法是上面例子一样调用exec函数，SQLITE对每一条语句进行词法分析和语法分析，在插入大量数据时很耗时，另一种方法执行准备操作，即先将SQL语句编译好，再一步步执行，提高执行效率。  

    QSqlQuery query(_mes_db);
    query.exec("PRAGMA synchronous = OFF; "); //关闭写同步
    QString sql = "insert into test_table(log_type,log_level,log_info) values(?,?,?)";
    QVariantList list1,list2,list3;
    _mes_db.transaction(); //开启事务
    query.prepare(sql);//将SQL语句准备好
    auto start = std::chrono::steady_clock::now();
    for(int i = 0; i < 100000; ++i)
    {
        list1 << "WARN";
        list2 << 1;
        list3 << "test alarm";
    }
    query.addBindValue(list1);
    query.addBindValue(list2);
    query.addBindValue(list3);
    query.execBatch();
    _mes_db.commit();
    auto end = std::chrono::steady_clock::now();
    auto duration = std::chrono::duration_cast<std::chrono::milliseconds>(end - start).count();
    qDebug() << "consuming time=" << duration << "ms";

同样插入10 0000条数据，所需时间只有357ms，速度提升了2324倍之多。

参考文章：(4条消息) 提升SQLite数据插入效率低、速度慢的方法_swazer_z-CSDN博客