SQLite 是一個輕量級的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，廣泛應(yīng)用于嵌入式設(shè)備、移動應(yīng)用和小型桌面程序中。它的一個強大特性是通過 PRAGMA 命令，用戶可以查詢和設(shè)置數(shù)據(jù)庫的配置參數(shù)，以優(yōu)化查詢性能和數(shù)據(jù)庫管理。PRAGMA 命令提供了多種控制數(shù)據(jù)庫行為的功能，包括緩存大小、索引設(shè)置、查詢優(yōu)化等。在這篇文章中，我們將探討如何使用 SQLite 的 PRAGMA 命令來優(yōu)化查詢性能，提升數(shù)據(jù)庫的響應(yīng)速度。

在 SQLite 中，PRAGMA 是一個特殊的命令，用來控制數(shù)據(jù)庫的行為、查詢優(yōu)化、以及性能調(diào)優(yōu)。PRAGMA 命令可以用來設(shè)置數(shù)據(jù)庫的各種配置，如緩存大小、數(shù)據(jù)庫模式、事務(wù)行為等。

一.空間釋放

1.如何查詢：

PRAGMA auto_vacuum;

含義：查詢數(shù)據(jù)庫的auto-vacuum標記。

2.標記含義：

auto-vacuum標記的含義：
正常情況下，當提交一個從數(shù)據(jù)庫中刪除數(shù)據(jù)的事務(wù)時，數(shù)據(jù)庫文件不改變大小。未使用的文件頁被標記并在以后的添加操作中再次使用。這種情況下使用VACUUM命令釋放刪除得到的空間。但是Vacuum的效率非常低！

3.如何設(shè)置：

PRAGMA auto_vacuum = 0 | 1;

當開啟auto-vacuum，也就是執(zhí)行pragma auto_vacuum=1;

當提交一個從數(shù)據(jù)庫中刪除數(shù)據(jù)的事務(wù)時，數(shù)據(jù)庫文件自動收縮， (VACUUM命令在auto-vacuum開啟的數(shù)據(jù)庫中不起作用)。數(shù)據(jù)庫會在內(nèi)部存儲一些信息以便支持這一功能，這使得 數(shù)據(jù)庫文件比不開啟該選項時稍微大一些。

4.注意事項：

只有在數(shù)據(jù)庫中未建任何表時才能改變auto-vacuum標記。試圖在已有表的情況下修改不會導致報錯。

不要打開。也就是“PRAGMA auto_vacuum = 0；”執(zhí)行命令。

二.緩存大小

1.如何查詢：

PRAGMA cache_size;

查詢SQLite一次存儲在內(nèi)存中的數(shù)據(jù)庫文件頁數(shù)。

2.標記含義：

每頁使用約1.5K內(nèi)存，缺省的緩存大小是2000. 若需要使用改變大量多行的UPDATE或DELETE命令，并且不介意SQLite使用更多的內(nèi)存的話，可以增大緩存以提高性能。

3.如何設(shè)置

PRAGMA cache_size = Number-of-pages;

修改SQLite一次存儲在內(nèi)存中的數(shù)據(jù)庫文件頁數(shù)。

4.注意事項：

當使用cache_size pragma改變緩存大小時，改變僅對當前對話有效，當數(shù)據(jù)庫關(guān)閉重新打開時緩存大小恢復到缺省大小。要想永久改變緩存大小，使用default_cache_size pragma.

修改為8000，也就是執(zhí)行命令“PRAGMA cache_size =8000；”即可；
 

三.LIKE運算符PRAGMA case_sensitive_like; 

PRAGMA case_sensitive_like = 0 | 1;

標記含義：LIKE運算符的缺省行為是忽略latin1字符的大小寫。因此在缺省情況下'a' LIKE 'A'的值為真?？梢酝ㄟ^打開 case_sensitive_like pragma來改變這一缺省行為。當啟用case_sensitive_like，'a' LIKE 'A'為假而'a' LIKE 'a'依然為真。

1.注意事項：

SQLite3.6.22版本不支持。

2.建議：

打開。也就是執(zhí)行命令“PRAGMA case_sensitive_like = 1;”命令。不然搜索中文字串會出錯。

四.LIKE運算符PRAGMA count_changes; 

PRAGMA count_changes = 0 | 1;
查詢或更改count-changes標記。

正常情況下INSERT, UPDATE和DELETE語句不返回數(shù)據(jù)。

當開啟count-changes，以上語句返回一行含一個整數(shù)值的數(shù)據(jù)——該語句插入，修改或刪除的行數(shù)。

1.注意事項：

返回的行數(shù)不包括由觸發(fā)器產(chǎn)生的插入，修改或刪除等改變的行數(shù)。

2.建議：

打開，便于調(diào)試。也就是執(zhí)行“PRAGMA count_changes = 1；”該命令。

五.頁面大小PRAGMA page_size; 

PRAGMA page_size = bytes;
查詢或設(shè)置page-size值。

注意事項：

只有在未創(chuàng)建數(shù)據(jù)庫時才能設(shè)置page-size。頁面大小必須是2的整數(shù)倍且大于等于512小于等于8192。

上限可以通過在編譯時修改宏定義SQLITE_MAX_PAGE_SIZE的值來改變。上限的上限是32768。
 

六.磁盤同步

1.如何查詢：

PRAGMA synchronous;

指令含義：查詢"synchronous"標記的設(shè)定，返回整數(shù)值；

2.如何設(shè)置：

PRAGMA synchronous = FULL; (2) 

PRAGMA synchronous = NORMAL; (1) 
PRAGMA synchronous = OFF; (0)

3.參數(shù)含義：

當synchronous設(shè)置為FULL (2), SQLite數(shù)據(jù)庫引擎在緊急時刻會暫停以確定數(shù)據(jù)已經(jīng)寫入磁盤。這使系統(tǒng)崩潰或電源出問題時能確保數(shù)據(jù)庫在重起后不會損壞。FULL synchronous很安全但很慢。

當synchronous設(shè)置為NORMAL, SQLite數(shù)據(jù)庫引擎在大部分緊急時刻會暫停，但不像FULL模式下那么頻繁。 NORMAL模式下有很小的幾率(但不是不存在)發(fā)生電源故障導致數(shù)據(jù)庫損壞的情況。但實際上，在這種情況 下很可能你的硬盤已經(jīng)不能使用，或者發(fā)生了其他的不可恢復的硬件錯誤。

設(shè)置為synchronous OFF (0)時，SQLite在傳遞數(shù)據(jù)給系統(tǒng)以后直接繼續(xù)而不暫停。若運行SQLite的應(yīng)用程序崩潰， 數(shù)據(jù)不會損傷，但在系統(tǒng)崩潰或?qū)懭霐?shù)據(jù)時意外斷電的情況下數(shù)據(jù)庫可能會損壞。另一方面，在synchronous OFF時 一些操作可能會快50倍甚至更多。
在SQLite 2中，缺省值為NORMAL.而在3中修改為FULL。

4.建議：

如果有定期備份的機制，而且少量數(shù)據(jù)丟失可接受，用OFF。

七.內(nèi)存模式

1.如何查詢：

PRAGMA temp_store;

指令含義：查詢"temp_store"參數(shù)的設(shè)置；

2.如何設(shè)置：

PRAGMA temp_store = DEFAULT; (0) 
PRAGMA temp_store = FILE; (1) 
PRAGMA temp_store = MEMORY; (2)

3.參數(shù)含義：

當temp_store設(shè)置為DEFAULT (0),使用編譯時的C預處理宏 TEMP_STORE來定義儲存臨時表和臨時索引的位置。

當設(shè)置為FILE (1)，則存放于文件中。temp_store_directorypragma 可用于指定存放該文件的目錄。

當設(shè)置為MEMORY (2)，臨時表和索引則存放于內(nèi)存中。

4.注意事項：

當改變temp_store設(shè)置，所有已存在的臨時表，索引，觸發(fā)器及視圖將被立即刪除。

5.建議：

使用2，即內(nèi)存模式。

附指令表集：

八.總結(jié)

SQLite 的PRAGMA命令為開發(fā)者提供了靈活的配置選項，可以顯著提升數(shù)據(jù)庫的查詢和寫入性能。通過調(diào)整緩存大小、同步模式、WAL 模式、索引優(yōu)化等配置，你可以針對特定的應(yīng)用場景對 SQLite 進行定制化優(yōu)化。合理使用 PRAGMA 命令不僅能提高性能，還能確保數(shù)據(jù)庫在高負載環(huán)境下的穩(wěn)定性和可靠性。

到此這篇關(guān)于SQLite3數(shù)據(jù)庫訪問性能優(yōu)化7個建議的文章就介紹到這了,更多相關(guān)SQLite3性能優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論