SQLite3效能最佳化

SQLite3效能調整主要通過pragma指令來實現。

比如調整：空間釋放、磁碟同步、Cache大小等。

一.空間釋放
1.如何查詢：

PRAGMA auto_vacuum;

含義：查詢資料庫的auto-vacuum標記。

2.標記含義：

auto-vacuum標記的含義：
正常情況下，當提交一個從資料庫中刪除資料的事務時，資料庫檔案不改變大小。未使用的檔案頁被標記並在以後的添加操作中再次使用。這種情況下使用VACUUM命令釋放刪除得到的空間。但是Vacuum的效率非常低！
3.如何設定：

PRAGMA auto_vacuum = 0 | 1;

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

當提交一個從資料庫中刪除資料的事務時，資料庫檔案自動收縮， (VACUUM命令在auto-vacuum開啟的資料庫中不起作用)。資料庫會在內部儲存一些資訊以便支援這一功能，這使得 資料庫檔案比不開啟該選項時稍微大一些。

4.注意事項：
只有在資料庫中未建任何錶時才能改變auto-vacuum標記。試圖在已有表的情況下修改不會導致報錯。

5.建議：

不要開啟。也就是“PRAGMA auto_vacuum = 0；”執行命令。

二.緩衝大小
1.如何查詢：

PRAGMA cache_size;

查詢SQLite一次儲存在記憶體中的資料庫檔案頁數。

2.標記含義：

每頁使用約1.5K記憶體，預設的緩衝大小是2000. 若需要使用改變大量多行的UPDATE或DELETE命令，並且不介意SQLite使用更多的記憶體的話，可以增大緩衝以提高效能。

3.如何設定

PRAGMA cache_size = Number-of-pages;

修改SQLite一次儲存在記憶體中的資料庫檔案頁數。

4.注意事項：

當使用cache_size pragma改變緩衝大小時，改變僅對目前的交談有效，當資料庫關閉重新開啟時緩衝大小恢複到預設大小。要想永久改變緩衝大小，使用default_cache_size pragma.

5.建議：

修改為8000，也就是執行命令“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.建議：

開啟。也就是執行命令“PRAGMA case_sensitive_like = 1;”命令。不然搜尋中文字串會出錯。

四.LIKE運算子
PRAGMA count_changes; 
PRAGMA count_changes = 0 | 1;
查詢或更改count-changes標記。

正常情況下INSERT, UPDATE和DELETE語句不返回資料。

當開啟count-changes，以上語句返回一行含一個整數值的資料——該語句插入，修改或刪除的行數。

1.注意事項：

返回的行數不包括由觸發器產生的插入，修改或刪除等改變的行數。

2.建議：

開啟，便於調試。也就是執行“PRAGMA count_changes = 1；”該命令。

五.頁面大小
PRAGMA page_size; 
PRAGMA page_size = bytes;
查詢或設定page-size值。

1.注意事項：

只有在未建立資料庫時才能設定page-size。頁面大小必須是2的整數倍且大於等於512小於等於8192。

上限可以通過在編譯時間修改宏定義SQLITE_MAX_PAGE_SIZE的值來改變。上限的上限是32768。
六.磁碟同步
1.如何查詢：

PRAGMA synchronous;

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

2.如何設定：
PRAGMA synchronous = FULL; (2) 
PRAGMA synchronous = NORMAL; (1) 
PRAGMA synchronous = OFF; (0)

3.參數含義：

當synchronous設定為FULL (2), SQLite資料庫引擎在緊急時刻會暫停以確定資料已經寫入磁碟。這使系統崩潰或電源出問題時能確保資料庫在重起後不會損壞。FULL synchronous很安全但很慢。

當synchronous設定為NORMAL, SQLite資料庫引擎在大部分緊急時刻會暫停，但不像FULL模式下那麼頻繁。 NORMAL模式下有很小的幾率(但不是不存在)發生電源故障導致資料庫損壞的情況。但實際上，在這種情況 下很可能你的硬碟已經不能使用，或者發生了其他的不可恢複的硬體錯誤。

設定為synchronous OFF (0)時，SQLite在傳遞資料給系統以後直接繼續而不暫停。若運行SQLite的應用程式崩潰， 資料不會損傷，但在系統崩潰或寫入資料時意外斷電的情況下資料庫可能會損壞。另一方面，在synchronous OFF時 一些操作可能會快50倍甚至更多。
在SQLite 2中，預設值為NORMAL.而在3中修改為FULL。

4.建議：

如果有定期備份的機制，而且少量資料丟失可接受，用OFF。
七.記憶體模式

1.如何查詢：
PRAGMA temp_store;

指令含義：查詢"temp_store"參數的設定；

2.如何設定：
PRAGMA temp_store = DEFAULT; (0) 
PRAGMA temp_store = FILE; (1) 
PRAGMA temp_store = MEMORY; (2)

3.參數含義：

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

當設定為FILE (1)，則存放於檔案中。temp_store_directorypragma 可用於指定存放該檔案的目錄。

當設定為MEMORY (2)，暫存資料表和索引則存放於記憶體中。

4.注意事項：

當改變temp_store設定，所有已存在的暫存資料表，索引，觸發器及視圖將被立即刪除。

5.建議：

使用2，即記憶體模式。

附指令表集：

序號	指令	含義	預設值
1	auto_vacuum	空間釋放	0
2	cache_size	緩衝大小	2000
3	case_sensitive_like	LIKE大小寫敏感	（注意：SQLite3.6.22不支援）
4	count_changes	變更行數	0
5	page_size	頁面大小	1024
6	synchronous	硬碟大小	2
7	temp_store;	記憶體模式	0

（End.）

點擊開啟連結