SQLite3 寫入效能瓶頸與交易優化

SQLite 以檔案形式儲存資料，每次 INSERT 等寫入操作都需開啟、寫入、關閉檔案，大量資料操作時 I/O 開銷極大，導致效能嚴重下降。

問題根因

• 單筆寫入觸發完整的檔案同步 (fsync) 確保資料持久化
• 大量操作時重複開關檔案、同步磁碟造成嚴重效能損耗

解決方案：使用交易批次提交

將大量操作包裹在交易中，操作先在記憶體累積，提交時一次性寫入磁碟，檔案僅開啟關閉一次，效能可提升數百至上千倍。

C 語言實作範例

// 開始交易
rc = sqlite3_exec(db, "BEGIN TRANSACTION;", 0, 0, &szErrMsg);

// 大量插入迴圈
for (int i = 0; i < count; i++) {
    // INSERT 操作
    sqlite3_exec(db, insert_sql, 0, 0, &szErrMsg);
}

// 提交交易
rc = sqlite3_exec(db, "COMMIT;", 0, 0, &szErrMsg);

關鍵點補充

• 交易模式：BEGIN IMMEDIATE 或 BEGIN EXCLUSIVE 可減少鎖競爭
• 批次大小：單次交易建議 1,000~10,000 筆，避免記憶體過大或鎖定過久
• WAL 模式：啟用 PRAGMA journal_mode=WAL; 可大幅提升並發寫入效能
• 同步設定：PRAGMA synchronous=NORMAL; 平衡效能與安全性
• 參數化查詢：使用 sqlite3_prepare_v2 + sqlite3_bind 避免重複編譯 SQL

效能對比參考

測試環境：10 萬筆資料插入

方式	耗時	提升倍數
單筆自動提交	~45 秒	1x (基準)
交易批次 (1萬/批)	~0.8 秒	~56x
WAL + 交易 + 參數化	~0.3 秒	~150x