SQLite常用性能優(yōu)化配置

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net



文章目錄

        日志模式
        同步提交
        臨時文件
        內(nèi)存映射
        頁面大小
        其他操作

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。

SQLite 是一個嵌入式數(shù)據(jù)庫引擎，通常以代碼庫的形式運行在其他程序中，因此我們不需要進行任何服務(wù)器配置。很多人都認為 SQLite 只能用于沒有性能要求的小型數(shù)據(jù)庫，但是實際情況并非如此。雖然 SQLite 只支持一個并發(fā)的寫入操作，但是多個進程可以同時連接和查詢相同的數(shù)據(jù)庫。通過一些簡單的配置和操作，我們完全可以使用 SQLite 創(chuàng)建 GB 級別的數(shù)據(jù)庫并且支持高達每秒 10 萬次的并發(fā)查詢。

簡單來說，優(yōu)化 SQLite 性能的配置如下：

pragma journal_mode = WAL;
pragma synchronous = normal;
pragma temp_store = memory;
pragma mmap_size = 30000000000;

 
以上配置中的部分內(nèi)容可以永久生效，但另一部分在每次連接時都會重置為默認值，因此推薦每次連接數(shù)據(jù)庫之后都執(zhí)行這些配置命令。

接下來我們具體解釋一下每個參數(shù)的作用。
日志模式

pragma journal_mode = WAL;


I通過以上配置，SQLite 不再直接寫入數(shù)據(jù)庫文件（隨機寫入），而是先寫入預(yù)寫式日志（順序?qū)懭耄┎⒍ㄆ谔峤坏綌?shù)據(jù)庫文件。這種模式可以在寫入事務(wù)進行的同時支持并發(fā)的讀取操作，同時可以極大地改善性能。

注意：在某些寫入壓力下，WAL 模式可能存在一些問題，導(dǎo)致 WAL 文件無限增長，從而大大降低性能。通常是因為寫入過于頻繁，導(dǎo)致 SQLite 無法執(zhí)行檢查點操作。緩解這一問題的方法有幾個：

    減少 wal_autocheckpoint 間隔。由于自動檢查點是被動觸發(fā)的操作，因此這種方法不能確保一定有效。
    定期執(zhí)行 pragma wal_checkpoint(full) 或者 pragma wal_checkpoint(truncate) 命令。對于 full 方式，如果其他進程正在打開文件，WAL 大小不會發(fā)生改變，但是仍然會提交所有內(nèi)容，確保新的數(shù)據(jù)不會導(dǎo)致 WAL 文件增長。對于 truncate 方式，SQLite 會阻塞其他進程并將 WAL 文件重置為空。我們可以通過一個單獨的進程執(zhí)行以上命令。

同步提交

pragma synchronous = normal;

pragma synchronous = off;

   

synchronous 的默認值為 full，意味著每個更新操作都需要等待 FSYNC 完成內(nèi)存數(shù)據(jù)到磁盤文件的同步。在 WAL 模式下，normal 方式仍然是完全安全的，同時意味著只有 WAL 檢查點操作需要等待 FSYNC 完成。off 可能導(dǎo)致數(shù)據(jù)庫損壞，雖然我們極少遇到這種問題。更多信息可以參考官方文檔。
臨時文件

pragma temp_store = memory;



以上指令表示使用內(nèi)存存儲臨時索引和表。SQLite 可以自動為某些查詢語句創(chuàng)建臨時索引。無法確定這種操作可以提供多少性能幫助，如果 SQLite 創(chuàng)建了臨時索引（通過 EXPLAIN QUERY PLAN 命令查看），我們應(yīng)該主動創(chuàng)建相應(yīng)的索引。
內(nèi)存映射

pragma mmap_size = 30000000000;



當(dāng)數(shù)據(jù)庫的大小小于 mmap_size 字節(jié)時使用內(nèi)存映射替代讀/寫調(diào)用。操作系統(tǒng)管理的 syscall、頁面以及緩存將會更少，此時的性能取決于使用的操作系統(tǒng)。注意，該設(shè)置不會使用物理內(nèi)存，而是保留虛擬內(nèi)存。然后，操作系統(tǒng)將根據(jù)常用的“磁盤緩存”邏輯決定哪些頁面被逐出，哪些頁面留在內(nèi)存中。至少是在 Linux 上，如果為 SQLite 進程分配了足夠的內(nèi)存，性能將會得到明顯提升。如果數(shù)據(jù)庫的大小比指定的 mmap_size 更大，數(shù)據(jù)庫的一部分將會使用內(nèi)存映射，其他仍然使用 read() / write() 系統(tǒng)調(diào)用。

對于 32 位操作系統(tǒng)，可能只能設(shè)置小于 4 GB 的 mmap_size。

內(nèi)存映射還會在出現(xiàn) I/O 錯誤時產(chǎn)生影響，具體可以參考官方文檔。
頁面大小

pragma page_size = 32768;



對于存儲大型 BLOB 數(shù)據(jù)而言，增加頁面大小可以改進數(shù)據(jù)庫的性能；但是對于一般的應(yīng)用場景而言，這種方法可能并不適用。對于寫入操作，SQLite 總是替換整個頁面，因此增加頁面大小會增加寫入操作的開銷。
其他操作

除了以上配置之外，我們還可以通過定期執(zhí)行某些操作改善數(shù)據(jù)庫的性能。

首先，我們可以定期重新組織數(shù)據(jù)庫：

pragma vacuum;



每次執(zhí)行以上命令都會重新寫入數(shù)據(jù)庫文件，如果數(shù)據(jù)庫大小超過 100 MB 將會非常耗時。

其次，我可以定期重新分析數(shù)據(jù)庫：

pragma optimize;



為了在不需要對應(yīng)用程序模式和 SQL 進行詳細分析的情況下獲得最佳的長期查詢性能，推薦應(yīng)用程序每次關(guān)閉數(shù)據(jù)庫連接時運行以上命令。對于長期運行的應(yīng)用程序，也可以通過幾個小時一次定期運行以上命令獲得性能提升。相信信息可以參考官方文檔。

另外，我們還可以定期清理數(shù)據(jù)庫：

pragma auto_vacuum = incremental; -- 創(chuàng)建數(shù)據(jù)庫之后
pragma incremental_vacuum; -- 定期


以上命令只有當(dāng)數(shù)據(jù)庫的大小會定期顯著縮減時才會明顯提升性能。

執(zhí)行清理命令時，數(shù)據(jù)庫文件中的空閑列表頁會被移動到文件結(jié)尾并且被刪除。需要注意，自動清理只會截斷空閑列表頁，而不會執(zhí)行 VACUUM 命令的文件碎片整理和頁面修復(fù)。實際上，由于它會移動數(shù)據(jù)頁，自動清理反而會增加碎片化。相信內(nèi)容可以參考官方文檔。