SQLITE3大量简单数据简单写测试

本文参考sqlite性能优化之路-配置优化进行尝试

结果前置

无索引，平均每62W/s的简单写入
简单ID自增索引，平均每51W/s的简单写入

本机配置

维护索引也需要时间，首先无索引测试

简单代码

#pragma once

#include <QCoreApplication>
#include <QDebug>
#include <QElapsedTimer>
#include <QRandomGenerator>
#include <QSqlDatabase>
#include <QSqlDriver>
#include <QSqlQuery>

class SqliteHelper : public QObject {
    Q_OBJECT

public:
    SqliteHelper(QObject* parent = nullptr);
    ~SqliteHelper();

public:
    QSqlDatabase db;
    size_t sum = 0;
    bool create()
    {
        db = QSqlDatabase::addDatabase("QSQLITE");
        QString dbPath = QCoreApplication::applicationDirPath() + "/test.db";
        // QString dbPath = ":memory:";
        db.setDatabaseName(dbPath);
        if (!db.open()) {
            qWarning() << "数据库创建失败";
        }
        QSqlQuery query(db);
        // 创建Point表：x,y,z,rx,ry,rz,dx,dy,dz,xl,yl,zl->类型浮点数
        QString sql = "create table  Point ("
                      "x float,"
                      "y float,"
                      "z float,"
                      "rx float,"
                      "ry float,"
                      "rz float,"
                      "dx float,"
                      "dy float,"
                      "dz float,"
                      "xl float,"
                      "yl float,"
                      "zl float)";

        this->db.exec(sql);
        // 写同步
        this->db.exec("PRAGMA synchronous = normal");
        // 日志
        this->db.exec("PRAGMA journal_mode = WAL");
        // mmap最大3000MB
        this->db.exec("PRAGMA mmap_size = 3145728000");
        // 内存缓存最大10GB
        this->db.exec("PRAGMA cache_size = 10000000");
        // 优先内存临时文件
        this->db.exec("PRAGMA temp_store = memory");
        // 页面大小
        this->db.exec("PRAGMA page_size = 3276800");
        return true;
    }

    // 生成随机数据
    QVariantList buildRandomData(size_t count)
    {
        QVariantList list;
        QRandomGenerator it;
        list.reserve(count);
        for (size_t i = 0; i < count; i++) {
            list.append(it.bounded(100));
        }
        return list;
    }

    bool insert(QVariantList list)
    {

        QElapsedTimer sqlReady;
        sqlReady.start();
        QSqlQuery query;
        query.prepare("insert into Point(x,y,z,rx,ry,rz,dx,dy,dz,xl,yl,zl) values(?,?,?,?,?,?,?,?,?,?,?,?)");
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        qInfo() << "sql准备时间：" << sqlReady.elapsed() << "毫秒";
        QElapsedTimer ElapsedTimer;
        ElapsedTimer.start();
        if (!this->db.transaction()) {
            return false;
        }
        query.execBatch();
        bool s = this->db.commit();
        if (s) {
            sum += list.size();
            qInfo() << QString("已插入%1条信息").arg(sum);
            qInfo() << "本次插入" << list.size() << "条信息，共耗时" << ElapsedTimer.elapsed() << "毫秒";
        } else {
            qInfo() << QString("插入失败");
        }
        return s;
    }
};

测试结果

平均每62W/s的简单写入

简单自增ID索引测试

简单代码

#pragma once

#include <QCoreApplication>
#include <QDebug>
#include <QElapsedTimer>
#include <QRandomGenerator>
#include <QSqlDatabase>
#include <QSqlDriver>
#include <QSqlQuery>

class SqliteHelper : public QObject {
    Q_OBJECT

public:
    SqliteHelper(QObject* parent = nullptr);
    ~SqliteHelper();

public:
    QSqlDatabase db;
    size_t sum = 0;
    bool create()
    {
        db = QSqlDatabase::addDatabase("QSQLITE");
        QString dbPath = QCoreApplication::applicationDirPath() + "/test.db";
        // QString dbPath = ":memory:";
        db.setDatabaseName(dbPath);
        if (!db.open()) {
            qWarning() << "数据库创建失败";
        }
        QSqlQuery query(db);
        // 创建Point表：x,y,z,rx,ry,rz,dx,dy,dz,xl,yl,zl->类型浮点数
        QString sql = "create table  Point ("
                      "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                      "x float,"
                      "y float,"
                      "z float,"
                      "rx float,"
                      "ry float,"
                      "rz float,"
                      "dx float,"
                      "dy float,"
                      "dz float,"
                      "xl float,"
                      "yl float,"
                      "zl float)";

        this->db.exec(sql);
        // 写同步
        this->db.exec("PRAGMA synchronous = normal");
        // 日志
        this->db.exec("PRAGMA journal_mode = WAL");
        // mmap最大3000MB
        this->db.exec("PRAGMA mmap_size = 3145728000");
        // 内存缓存最大10GB
        this->db.exec("PRAGMA cache_size = 10000000");
        // 优先内存临时文件
        this->db.exec("PRAGMA temp_store = memory");
        // 页面大小
        this->db.exec("PRAGMA page_size = 3276800");
        return true;
    }

    // 生成随机数据
    QVariantList buildRandomData(size_t count)
    {
        QVariantList list;
        QRandomGenerator it;
        list.reserve(count);
        for (size_t i = 0; i < count; i++) {
            list.append(it.bounded(100));
        }
        return list;
    }

    bool insert(QVariantList list)
    {

        QElapsedTimer sqlReady;
        sqlReady.start();
        QSqlQuery query;
        query.prepare("insert into Point(x,y,z,rx,ry,rz,dx,dy,dz,xl,yl,zl) values(?,?,?,?,?,?,?,?,?,?,?,?)");
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        query.addBindValue(list);
        qInfo() << "sql准备时间：" << sqlReady.elapsed() << "毫秒";
        QElapsedTimer ElapsedTimer;
        ElapsedTimer.start();
        if (!this->db.transaction()) {
            return false;
        }
        query.execBatch();
        bool s = this->db.commit();
        if (s) {
            sum += list.size();
            qInfo() << QString("已插入%1条信息").arg(sum);
            qInfo() << "本次插入" << list.size() << "条信息，共耗时" << ElapsedTimer.elapsed() << "毫秒";
        } else {
            qInfo() << QString("插入失败");
        }
        return s;
    }
};

测试结果

平均每51W/s的简单写入