c++ - 使用 SQLite 提高性能

Question

我用 Qt 编写了一个 C++ 可执行文件来计算基于 SQLite 数据库的数据并将其再次存储在数据库中。使用 Qt，我正在执行 SELECT，计算数据并通过 UPDATE 再次存储它。最初我用大约 5000 行的 block 测试了它并且它工作得非常快(每行大约 1 毫秒)。现在我已经实现了我需要对大约 600000 行的数据库应用相同计算的所有内容。现在每行的平均时间大约慢了 200 倍。在改进我的代码时，我首先验证了它与表大小有关，而不是与计算细节有关，因此我将长表中的数据截断为 100000，这仅比具有 5000 行的小表慢 20 倍。

我从一开始就在代码中添加了 pragma 以提高整体性能:

query.exec("PRAGMA page_size = 16384");
    query.exec("PRAGMA cache_size = 131072");
    query.exec("PRAGMA temp_store = MEMORY");
    query.exec("PRAGMA journal_mode = OFF");
    query.exec("PRAGMA locking_mode = EXCLUSIVE");
    query.exec("PRAGMA synchronous = OFF");

我还尝试使用 100 或 1000 行的事务，但这没有帮助:

database.transaction();
...
database.commit();

有人可以建议怎么做吗？我已经在考虑拆分数据库文件，但有没有简单的方法可以做到？

编辑:根据要求，这是一个简化的最小示例。在我的真实示例中，表格布局更为复杂(17 列)并且有大约 600000 个条目，但是这很好地引入了问题:

widget.h

#ifndef WIDGET_H
#define WIDGET_H

#include <QtCore>
#include <QtGui>
#include <QtWidgets>
#include <QtSql>

class Widget : public QWidget
{
    Q_OBJECT

public:
    Widget(QWidget *parent = 0)
        : QWidget(parent)
    {
        QPushButton *createSmall = new QPushButton("Create Small");
        connect(createSmall, SIGNAL(clicked()), this, SLOT(createSmallDataBase()));
        QPushButton *createBig = new QPushButton("Create Big");
        connect(createBig, SIGNAL(clicked()), this, SLOT(createBigDataBase()));
        QPushButton *calculateSmall = new QPushButton("Calculate Small");
        connect(calculateSmall, SIGNAL(clicked()), this, SLOT(calculateSmallDataBase()));
        QPushButton *calculateBig = new QPushButton("Calculate Big");
        connect(calculateBig, SIGNAL(clicked()), this, SLOT(calculateBigDataBase()));
        QVBoxLayout *layout = new QVBoxLayout();
        layout->addWidget(createSmall);
        layout->addWidget(createBig);
        layout->addWidget(calculateSmall);
        layout->addWidget(calculateBig);
        this->setLayout(layout);
    }

    ~Widget()
    {
    }

    void createDataBase(quint32 size, QString name)
    {
        QSqlDatabase database;
        database = QSqlDatabase::addDatabase("QSQLITE");
        database.setDatabaseName(name);
        if(database.open())
        {
            QSqlQuery query(database);
            query.exec("PRAGMA page_size = 4096");
            query.exec("PRAGMA cache_size = 16384");
            query.exec("PRAGMA temp_store = MEMORY");
            query.exec("PRAGMA journal_mode = OFF");
            query.exec("PRAGMA locking_mode = EXCLUSIVE");
            query.exec("PRAGMA synchronous = OFF");
            qDebug() << "DROP" << query.exec("DROP TABLE Scenario");
            qDebug() << "CREATE" << query.exec("CREATE TABLE IF NOT EXISTS Scenario(id INTEGER, time REAL, prob REAL)");
            for(quint32 i = 0; i < size; i++)
            {
                query.exec(QString("INSERT INTO Scenario (id, time, prob) VALUES(%1, %2, %3)").arg(i).arg(i).arg(-1));
            }
        }
        database.close();
        database.removeDatabase("QSQLITE");
    }

    void calculateDataBase(QString name)
    {
        QSqlDatabase database;
        database = QSqlDatabase::addDatabase("QSQLITE");
        database.setDatabaseName(name);
        if(database.open())
        {
            QSqlQuery query(database);
            query.exec("PRAGMA page_size = 4096");
            query.exec("PRAGMA cache_size = 16384");
            query.exec("PRAGMA temp_store = MEMORY");
            query.exec("PRAGMA journal_mode = OFF");
            query.exec("PRAGMA locking_mode = EXCLUSIVE");
            query.exec("PRAGMA synchronous = OFF");

            query.exec("SELECT MAX(id) FROM Scenario");
            quint32 maxID = 0;

            if(query.next())
            {
                maxID = query.value(0).toUInt();
            }

            for(quint32 id = 0; id <= maxID; id++)
            {
                query.exec(QString("SELECT id, time, prob FROM Scenario WHERE id = %1").arg(QString::number(id)));
                if(query.first())
                {
                    double prob = query.value(0).toDouble();
                    query.exec(QString("UPDATE Scenario SET prob = %1 WHERE id = %2").arg(qSqrt(prob)).arg(QString::number(id)));
                }
            }
        }
        database.close();
        database.removeDatabase("QSQLITE");
    }

public slots:
    void createSmallDataBase()
    {
        QTime time;
        time.start();
        createDataBase(1000, "small.sqlite");
        qDebug() << "Create Small" << time.elapsed()/1000.0;
    }
    void createBigDataBase()
    {
        QTime time;
        time.start();
        createDataBase(10000, "big.sqlite");
        qDebug() << "Create Big" << time.elapsed()/10000.0;
    }
    void calculateSmallDataBase()
    {
        QTime time;
        time.start();
        calculateDataBase("small.sqlite");
        qDebug() << "Calculate Small" << time.elapsed()/1000.0;
    }
    void calculateBigDataBase()
    {
        QTime time;
        time.start();
        calculateDataBase("big.sqlite");
        qDebug() << "Calculate Big" << time.elapsed()/10000.0;
    }
};

#endif // WIDGET_H

main.cpp

#include <QApplication>

#include "widget.h"

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    Widget w;
    w.show();

    return a.exec();
}

在我的机器上，调用 calculateSmallDataBase() 和 calculateBigDataBase() 之间的差异是从 0.518ms/line 到 3.0417ms/line 而这只是从 1000 到 10000线!所以我已经达到了这两者之间的因素 6。

期待您的建议。

Answer 1

从数据库引擎获取查询计划并优化其访问路径。请参阅:sqlite.org/eqp.html 当数据大小从小型测试数据集变为较大的实际数据集时，数据库速度变慢的常见原因是未优化对表数据的访问，例如缺少索引；不使用索引，因为查询以相反的顺序列出键；对于最佳插入等的索引太多。