據(jù)游標分頁實戰(zhàn)與性能優(yōu)化)
這次我們直接看一個 Qt 項目里非常實際的性能問題SQLite 單表數(shù)據(jù)量到了千萬級之后原來的列表查詢、CRUD 和界面刷新會越來越卡。很多項目其實不是死在 SQLite 本身而是死在“拿處理萬級數(shù)據(jù)量的分頁方式去處理千萬級數(shù)據(jù)”。本文把 Qt SQLite 的游標分頁實戰(zhàn)完整拆開給出表設(shè)計、索引策略、CRUD 代碼、UI 模型和異步刷新方案目標是讓千萬級數(shù)據(jù)列表在 QTableView 上保持可交互的流暢度。先說結(jié)論。傳統(tǒng) LIMIT OFFSET 分頁在 OFFSET 到幾十萬、上百萬之后SQLite 每次都要掃描并丟棄前面的行耗時隨頁碼上漲游標分頁Keyset Pagination用索引直接定位“上一頁最后一條記錄”下一頁從它后面繼續(xù)取查詢耗時與總數(shù)據(jù)量基本解耦。這是 Qt 客戶端做本地大數(shù)據(jù)量列表最值得先嘗試的方案之一。本文面向 Qt 開發(fā)者和正在做上位機、數(shù)據(jù)采集、工業(yè)組態(tài)、本地歷史數(shù)據(jù)查詢的同學。下面包含核心能力速覽、適用場景、環(huán)境準備、表設(shè)計與索引、游標分頁實現(xiàn)、千萬級 CRUD 實戰(zhàn)、UI 分頁模型與異步加載、性能觀察方法、問題排查和最佳實踐。文中代碼基于 Qt 5.15 / Qt 6 的 QSql 模塊可以直接復(fù)制后用 CMake 工程驗證。1. 核心能力速覽能力項說明技術(shù)棧Qt 5.15 / Qt 6QSqlDatabase QSqlQuery QAbstractTableModel數(shù)據(jù)量目標單表千萬級核心方案游標分頁Keyset Pagination替代 LIMIT OFFSETUI 優(yōu)化分頁加載、后臺線程查詢、只渲染當前頁數(shù)據(jù)寫入優(yōu)化事務(wù) 預(yù)編譯語句批量寫入更新策略UPSERTON CONFLICT支持平臺Windows / Linux / macOS接口形態(tài)本地數(shù)據(jù)庫訪問可封裝為 Qt 內(nèi)部服務(wù)接口批量任務(wù)支持批量導入、批處理更新和刪除硬件建議SSD 8G 內(nèi)存以上具體耗時以本機為準這套方案有一個前提分頁是基于排序鍵的連續(xù)翻頁而不是任意跳頁。如果產(chǎn)品要求“直接跳到第 100 萬條”游標分頁并不合適需要另行設(shè)計索引、緩存或離線統(tǒng)計方案。先認清這一點再決定是否采用。2. 適用場景與使用邊界游標分頁最適合的場景是數(shù)據(jù)持續(xù)增長、用戶按順序翻頁查看、列表需要保持穩(wěn)定響應(yīng)。典型例子包括設(shè)備上報數(shù)據(jù)、溫度采樣曲線、日志檢索、訂單流水、歷史告警記錄。這些場景的共同特征是數(shù)據(jù)量大、寫入以追加為主、讀取是順序翻頁。使用邊界也很明確。第一SQLite 是單文件單寫者數(shù)據(jù)庫不適合多進程高并發(fā)寫入場景。第二游標分頁擅長順序翻頁不支持隨機跳頁如果 UI 提供“跳轉(zhuǎn)到第 N 頁”的輸入框那還需要結(jié)合總數(shù)估算或改用其他方案。第三千萬級數(shù)據(jù)仍然不適合一次性加載到界面上即使改用了游標分頁UI 模型也必須按頁裝載數(shù)據(jù)不能把整表塞進 QTableView。數(shù)據(jù)合規(guī)方面需要單獨說明演示和開發(fā)時建議使用模擬數(shù)據(jù)或脫敏數(shù)據(jù)如果使用真實采集數(shù)據(jù)需要確認數(shù)據(jù)來源合法、不包含未授權(quán)的個人信息或敏感內(nèi)容。尤其是設(shè)備編號、工號、位置信息、鑒權(quán)字段發(fā)布示例代碼前要清理干凈。測試環(huán)境用模擬數(shù)據(jù)驗證邏輯生產(chǎn)庫的 Schema 變更和批量操作要先備份再執(zhí)行。3. 環(huán)境準備與 Qt 項目搭建寫 Qt 工程前先確認環(huán)境里安裝了 Qt 的 SQL 模塊。Qt 官方安裝包默認包含 QSQLITE 驅(qū)動不需要額外編譯 SQLite。如果發(fā)現(xiàn)驅(qū)動加載失敗查看是否缺少 Qt SQL 相關(guān)模塊以及插件目錄路徑是否正確。Windows 下常見問題是 release 程序加載 debug 插件或缺少插件 DLL這個在常見問題章節(jié)統(tǒng)一排查。建議工程用 CMake 管理。確認以下依賴已經(jīng)通過 find_package 引入cmake_minimum_required(VERSION 3.16) project(QtSqliteCursorPaging) set(CMAKE_CXX_STANDARD 17) set(CMAKE_AUTOMOC ON) find_package(Qt6 COMPONENTS Core Gui Widgets Sql REQUIRED) qt_add_executable(QtSqliteCursorPaging main.cpp MainWindow.cpp MainWindow.h MeasureDataModel.cpp MeasureDataModel.h MeasureQueryWorker.cpp MeasureQueryWorker.h ) target_link_libraries(QtSqliteCursorPaging PRIVATE Qt6::Core Qt6::Gui Qt6::Widgets Qt6::Sql )如果還在用 Qt 5把 Qt6 換成 Qt5CMake 用法一致。工程目錄建議按 db / model / worker / ui 分層避免把 SQL 散落在界面代碼里。下面是一套簡單分層src/ db/ SQLiteHelper.cpp SQLiteHelper.h model/ MeasureDataModel.cpp MeasureDataModel.h worker/ MeasureQueryWorker.cpp MeasureQueryWorker.h ui/ MainWindow.cpp MainWindow.h打開數(shù)據(jù)庫時連接名不要用默認的qt_sql_default_connection到處混用。多線程場景下建議為每個線程顯式創(chuàng)建連接并帶上不同 connectionName。下面是 SQLiteHelper 的打開邏輯示例#include QSqlDatabase #include QSqlQuery #include QSqlError #include QSqlRecord #include QVariant #include QDebug bool openDatabase(const QString dbPath, const QString connectionName, QSqlDatabase db) { db QSqlDatabase::addDatabase(QSQLITE, connectionName); db.setDatabaseName(dbPath); if (!db.open()) { qCritical() open database failed db.lastError().text(); return false; } QSqlQuery query(db); query.exec(PRAGMA journal_mode WAL); query.exec(PRAGMA synchronous NORMAL); query.exec(PRAGMA busy_timeout 5000); query.exec(PRAGMA cache_size -20000); // 約 20MB page cache return true; }打開連接后建議先執(zhí)行一遍 WAL、busy_timeout 和 cache_size 設(shè)置。WAL 模式能明顯改善讀寫并發(fā)時的database is locked問題busy_timeout 讓 SQLite 等待鎖而不是立即報錯。cache_size 是示例值具體大小要結(jié)合機器內(nèi)存調(diào)整不是越大越好。4. 表設(shè)計與索引策略千萬級數(shù)據(jù)能不能跑起來首先看表結(jié)構(gòu)。下面是一張設(shè)備采集表字段盡量貼近生產(chǎn)場景CREATE TABLE IF NOT EXISTS measure_data ( id INTEGER PRIMARY KEY AUTOINCREMENT, device_no TEXT NOT NULL, channel INTEGER NOT NULL DEFAULT 0, value REAL NOT NULL, ts TEXT NOT NULL, remark TEXT ); CREATE INDEX IF NOT EXISTS idx_measure_data_ts ON measure_data(ts DESC); CREATE INDEX IF NOT EXISTS idx_measure_data_device_ts ON measure_data(device_no, ts DESC);id 作為 INTEGER PRIMARY KEY在 SQLite 中會作為 ROWID 別名天然適合做游標分頁的排序鍵。ts 是業(yè)務(wù)時間device_no ts 的組合索引用來支撐“按設(shè)備查詢并按時間倒序翻頁”的常見場景。索引不是越多越好。千萬級表上每次寫入都要同步維護索引索引過多會拖慢插入和更新。先根據(jù)實際查詢條件設(shè)計兩三個索引再通過 EXPLAIN QUERY PLAN 驗證是否命中不要盲目給每一個字段都建索引。造數(shù)階段建議用腳本或 Qt 程序批量生成模擬數(shù)據(jù)。千萬級 CSV 手工準備很慢更推薦直接本地生成。生成時保持字段分布接近真實設(shè)備編號幾十臺、通道幾個、時間連續(xù)、數(shù)值隨機。注意模擬數(shù)據(jù)不要包含真實設(shè)備編號、真實地址和真實個人信息。5. 游標分頁原理與 SQL 對照先看傳統(tǒng)分頁的痛點。假設(shè)每頁 100 條要取第 9000 頁的數(shù)據(jù)SELECT id, device_no, channel, value, ts, remark FROM measure_data ORDER BY id ASC LIMIT 100 OFFSET 899900;這條 SQL 在數(shù)據(jù)量小的時候沒問題。但當表里有 1000 萬條記錄時SQLite 為了算出 OFFSET 899900需要掃描并丟棄前 899900 行查詢耗時會隨 OFFSET 越來越大。這是列表越翻越卡的直接原因。游標分頁的思路是記住當前頁最后一條記錄的 id下一頁查詢用 WHERE id :last_id然后 LIMIT 100。SQL 如下-- 第一頁 SELECT id, device_no, channel, value, ts, remark FROM measure_data ORDER BY id ASC LIMIT 100; -- 下一頁last_id 是上一頁最后一條記錄的 id SELECT id, device_no, channel, value, ts, remark FROM measure_data WHERE id :last_id ORDER BY id ASC LIMIT 100;因為 id 是主鍵SQLite 利用索引可以直接跳到最后一條位置不需要掃描前面的大量數(shù)據(jù)。數(shù)據(jù)量從 100 萬漲到 1000 萬后只要翻頁位置接近最新數(shù)據(jù)查詢耗時的變化會遠小于 OFFSET 分頁。多字段排序也可以做游標分頁。比如按 device_no 過濾然后按 ts DESC, id DESC 排序。SQLite 3.15 及以上支持行值比較SELECT id, device_no, channel, value, ts, remark FROM measure_data WHERE device_no DEV-001 AND (ts, id) (:last_ts, :last_id) ORDER BY ts DESC, id DESC LIMIT 100;使用 (ts, id) 行值語法時需要確保排序列和過濾列能被索引覆蓋。以上這條 SQL 對應(yīng)的索引是 idx_measure_data_device_ts。如果只寫 ts 條件而索引順序不匹配SQLite 可能仍然走全表掃描性能就回不到預(yù)期。下面是把游標分頁封裝成 Qt 函數(shù)的完整實現(xiàn)struct MeasureRecord { qlonglong id 0; QString deviceNo; int channel 0; double value 0.0; QString ts; QString remark; }; QVectorMeasureRecord queryPage( qlonglong cursorId, int pageSize, QSqlDatabase db, QString *errorText nullptr) { QVectorMeasureRecord records; // pageSize 來自調(diào)用方常量或整數(shù)參數(shù)拼入前做范圍校驗 if (pageSize 0 || pageSize 5000) { pageSize 100; } QString sql QStringLiteral( SELECT id, device_no, channel, value, ts, remark FROM measure_data WHERE id %1 ORDER BY id ASC LIMIT %2) .arg(cursorId) .arg(pageSize); QSqlQuery query(db); query.setForwardOnly(true); if (!query.exec(sql)) { if (errorText) { *errorText query.lastError().text(); } return records; } while (query.next()) { MeasureRecord rec; rec.id query.value(0).toLongLong(); rec.deviceNo query.value(1).toString(); rec.channel query.value(2).toInt(); rec.value query.value(3).toDouble(); rec.ts query.value(4).toString(); rec.remark query.value(5).toString(); records.append(rec); } return records; }這里 cursorId 是從上一頁最后一條記錄中取出的整數(shù)pageSize 也做了 Qt+SQLite千萬級數(shù)據(jù)游標分頁實戰(zhàn)與性能優(yōu)化
這次我們直接看一個 Qt 項目里非常實際的性能問題SQLite 單表數(shù)據(jù)量到了千萬級之后原來的列表查詢、CRUD 和界面刷新會越來越卡。很多項目其實不是死在 SQLite 本身而是死在“拿處理萬級數(shù)據(jù)量的分頁方式去處理千萬級數(shù)據(jù)”。本文把 Qt SQLite 的游標分頁實戰(zhàn)完整拆開給出表設(shè)計、索引策略、CRUD 代碼、UI 模型和異步刷新方案目標是讓千萬級數(shù)據(jù)列表在 QTableView 上保持可交互的流暢度。先說結(jié)論。傳統(tǒng) LIMIT OFFSET 分頁在 OFFSET 到幾十萬、上百萬之后SQLite 每次都要掃描并丟棄前面的行耗時隨頁碼上漲游標分頁Keyset Pagination用索引直接定位“上一頁最后一條記錄”下一頁從它后面繼續(xù)取查詢耗時與總數(shù)據(jù)量基本解耦。這是 Qt 客戶端做本地大數(shù)據(jù)量列表最值得先嘗試的方案之一。本文面向 Qt 開發(fā)者和正在做上位機、數(shù)據(jù)采集、工業(yè)組態(tài)、本地歷史數(shù)據(jù)查詢的同學。下面包含核心能力速覽、適用場景、環(huán)境準備、表設(shè)計與索引、游標分頁實現(xiàn)、千萬級 CRUD 實戰(zhàn)、UI 分頁模型與異步加載、性能觀察方法、問題排查和最佳實踐。文中代碼基于 Qt 5.15 / Qt 6 的 QSql 模塊可以直接復(fù)制后用 CMake 工程驗證。1. 核心能力速覽能力項說明技術(shù)棧Qt 5.15 / Qt 6QSqlDatabase QSqlQuery QAbstractTableModel數(shù)據(jù)量目標單表千萬級核心方案游標分頁Keyset Pagination替代 LIMIT OFFSETUI 優(yōu)化分頁加載、后臺線程查詢、只渲染當前頁數(shù)據(jù)寫入優(yōu)化事務(wù) 預(yù)編譯語句批量寫入更新策略UPSERTON CONFLICT支持平臺Windows / Linux / macOS接口形態(tài)本地數(shù)據(jù)庫訪問可封裝為 Qt 內(nèi)部服務(wù)接口批量任務(wù)支持批量導入、批處理更新和刪除硬件建議SSD 8G 內(nèi)存以上具體耗時以本機為準這套方案有一個前提分頁是基于排序鍵的連續(xù)翻頁而不是任意跳頁。如果產(chǎn)品要求“直接跳到第 100 萬條”游標分頁并不合適需要另行設(shè)計索引、緩存或離線統(tǒng)計方案。先認清這一點再決定是否采用。2. 適用場景與使用邊界游標分頁最適合的場景是數(shù)據(jù)持續(xù)增長、用戶按順序翻頁查看、列表需要保持穩(wěn)定響應(yīng)。典型例子包括設(shè)備上報數(shù)據(jù)、溫度采樣曲線、日志檢索、訂單流水、歷史告警記錄。這些場景的共同特征是數(shù)據(jù)量大、寫入以追加為主、讀取是順序翻頁。使用邊界也很明確。第一SQLite 是單文件單寫者數(shù)據(jù)庫不適合多進程高并發(fā)寫入場景。第二游標分頁擅長順序翻頁不支持隨機跳頁如果 UI 提供“跳轉(zhuǎn)到第 N 頁”的輸入框那還需要結(jié)合總數(shù)估算或改用其他方案。第三千萬級數(shù)據(jù)仍然不適合一次性加載到界面上即使改用了游標分頁UI 模型也必須按頁裝載數(shù)據(jù)不能把整表塞進 QTableView。數(shù)據(jù)合規(guī)方面需要單獨說明演示和開發(fā)時建議使用模擬數(shù)據(jù)或脫敏數(shù)據(jù)如果使用真實采集數(shù)據(jù)需要確認數(shù)據(jù)來源合法、不包含未授權(quán)的個人信息或敏感內(nèi)容。尤其是設(shè)備編號、工號、位置信息、鑒權(quán)字段發(fā)布示例代碼前要清理干凈。測試環(huán)境用模擬數(shù)據(jù)驗證邏輯生產(chǎn)庫的 Schema 變更和批量操作要先備份再執(zhí)行。3. 環(huán)境準備與 Qt 項目搭建寫 Qt 工程前先確認環(huán)境里安裝了 Qt 的 SQL 模塊。Qt 官方安裝包默認包含 QSQLITE 驅(qū)動不需要額外編譯 SQLite。如果發(fā)現(xiàn)驅(qū)動加載失敗查看是否缺少 Qt SQL 相關(guān)模塊以及插件目錄路徑是否正確。Windows 下常見問題是 release 程序加載 debug 插件或缺少插件 DLL這個在常見問題章節(jié)統(tǒng)一排查。建議工程用 CMake 管理。確認以下依賴已經(jīng)通過 find_package 引入cmake_minimum_required(VERSION 3.16) project(QtSqliteCursorPaging) set(CMAKE_CXX_STANDARD 17) set(CMAKE_AUTOMOC ON) find_package(Qt6 COMPONENTS Core Gui Widgets Sql REQUIRED) qt_add_executable(QtSqliteCursorPaging main.cpp MainWindow.cpp MainWindow.h MeasureDataModel.cpp MeasureDataModel.h MeasureQueryWorker.cpp MeasureQueryWorker.h ) target_link_libraries(QtSqliteCursorPaging PRIVATE Qt6::Core Qt6::Gui Qt6::Widgets Qt6::Sql )如果還在用 Qt 5把 Qt6 換成 Qt5CMake 用法一致。工程目錄建議按 db / model / worker / ui 分層避免把 SQL 散落在界面代碼里。下面是一套簡單分層src/ db/ SQLiteHelper.cpp SQLiteHelper.h model/ MeasureDataModel.cpp MeasureDataModel.h worker/ MeasureQueryWorker.cpp MeasureQueryWorker.h ui/ MainWindow.cpp MainWindow.h打開數(shù)據(jù)庫時連接名不要用默認的qt_sql_default_connection到處混用。多線程場景下建議為每個線程顯式創(chuàng)建連接并帶上不同 connectionName。下面是 SQLiteHelper 的打開邏輯示例#include QSqlDatabase #include QSqlQuery #include QSqlError #include QSqlRecord #include QVariant #include QDebug bool openDatabase(const QString dbPath, const QString connectionName, QSqlDatabase db) { db QSqlDatabase::addDatabase(QSQLITE, connectionName); db.setDatabaseName(dbPath); if (!db.open()) { qCritical() open database failed db.lastError().text(); return false; } QSqlQuery query(db); query.exec(PRAGMA journal_mode WAL); query.exec(PRAGMA synchronous NORMAL); query.exec(PRAGMA busy_timeout 5000); query.exec(PRAGMA cache_size -20000); // 約 20MB page cache return true; }打開連接后建議先執(zhí)行一遍 WAL、busy_timeout 和 cache_size 設(shè)置。WAL 模式能明顯改善讀寫并發(fā)時的database is locked問題busy_timeout 讓 SQLite 等待鎖而不是立即報錯。cache_size 是示例值具體大小要結(jié)合機器內(nèi)存調(diào)整不是越大越好。4. 表設(shè)計與索引策略千萬級數(shù)據(jù)能不能跑起來首先看表結(jié)構(gòu)。下面是一張設(shè)備采集表字段盡量貼近生產(chǎn)場景CREATE TABLE IF NOT EXISTS measure_data ( id INTEGER PRIMARY KEY AUTOINCREMENT, device_no TEXT NOT NULL, channel INTEGER NOT NULL DEFAULT 0, value REAL NOT NULL, ts TEXT NOT NULL, remark TEXT ); CREATE INDEX IF NOT EXISTS idx_measure_data_ts ON measure_data(ts DESC); CREATE INDEX IF NOT EXISTS idx_measure_data_device_ts ON measure_data(device_no, ts DESC);id 作為 INTEGER PRIMARY KEY在 SQLite 中會作為 ROWID 別名天然適合做游標分頁的排序鍵。ts 是業(yè)務(wù)時間device_no ts 的組合索引用來支撐“按設(shè)備查詢并按時間倒序翻頁”的常見場景。索引不是越多越好。千萬級表上每次寫入都要同步維護索引索引過多會拖慢插入和更新。先根據(jù)實際查詢條件設(shè)計兩三個索引再通過 EXPLAIN QUERY PLAN 驗證是否命中不要盲目給每一個字段都建索引。造數(shù)階段建議用腳本或 Qt 程序批量生成模擬數(shù)據(jù)。千萬級 CSV 手工準備很慢更推薦直接本地生成。生成時保持字段分布接近真實設(shè)備編號幾十臺、通道幾個、時間連續(xù)、數(shù)值隨機。注意模擬數(shù)據(jù)不要包含真實設(shè)備編號、真實地址和真實個人信息。5. 游標分頁原理與 SQL 對照先看傳統(tǒng)分頁的痛點。假設(shè)每頁 100 條要取第 9000 頁的數(shù)據(jù)SELECT id, device_no, channel, value, ts, remark FROM measure_data ORDER BY id ASC LIMIT 100 OFFSET 899900;這條 SQL 在數(shù)據(jù)量小的時候沒問題。但當表里有 1000 萬條記錄時SQLite 為了算出 OFFSET 899900需要掃描并丟棄前 899900 行查詢耗時會隨 OFFSET 越來越大。這是列表越翻越卡的直接原因。游標分頁的思路是記住當前頁最后一條記錄的 id下一頁查詢用 WHERE id :last_id然后 LIMIT 100。SQL 如下-- 第一頁 SELECT id, device_no, channel, value, ts, remark FROM measure_data ORDER BY id ASC LIMIT 100; -- 下一頁last_id 是上一頁最后一條記錄的 id SELECT id, device_no, channel, value, ts, remark FROM measure_data WHERE id :last_id ORDER BY id ASC LIMIT 100;因為 id 是主鍵SQLite 利用索引可以直接跳到最后一條位置不需要掃描前面的大量數(shù)據(jù)。數(shù)據(jù)量從 100 萬漲到 1000 萬后只要翻頁位置接近最新數(shù)據(jù)查詢耗時的變化會遠小于 OFFSET 分頁。多字段排序也可以做游標分頁。比如按 device_no 過濾然后按 ts DESC, id DESC 排序。SQLite 3.15 及以上支持行值比較SELECT id, device_no, channel, value, ts, remark FROM measure_data WHERE device_no DEV-001 AND (ts, id) (:last_ts, :last_id) ORDER BY ts DESC, id DESC LIMIT 100;使用 (ts, id) 行值語法時需要確保排序列和過濾列能被索引覆蓋。以上這條 SQL 對應(yīng)的索引是 idx_measure_data_device_ts。如果只寫 ts 條件而索引順序不匹配SQLite 可能仍然走全表掃描性能就回不到預(yù)期。下面是把游標分頁封裝成 Qt 函數(shù)的完整實現(xiàn)struct MeasureRecord { qlonglong id 0; QString deviceNo; int channel 0; double value 0.0; QString ts; QString remark; }; QVectorMeasureRecord queryPage( qlonglong cursorId, int pageSize, QSqlDatabase db, QString *errorText nullptr) { QVectorMeasureRecord records; // pageSize 來自調(diào)用方常量或整數(shù)參數(shù)拼入前做范圍校驗 if (pageSize 0 || pageSize 5000) { pageSize 100; } QString sql QStringLiteral( SELECT id, device_no, channel, value, ts, remark FROM measure_data WHERE id %1 ORDER BY id ASC LIMIT %2) .arg(cursorId) .arg(pageSize); QSqlQuery query(db); query.setForwardOnly(true); if (!query.exec(sql)) { if (errorText) { *errorText query.lastError().text(); } return records; } while (query.next()) { MeasureRecord rec; rec.id query.value(0).toLongLong(); rec.deviceNo query.value(1).toString(); rec.channel query.value(2).toInt(); rec.value query.value(3).toDouble(); rec.ts query.value(4).toString(); rec.remark query.value(5).toString(); records.append(rec); } return records; }這里 cursorId 是從上一頁最后一條記錄中取出的整數(shù)pageSize 也做了