windows下sqlite安裝使用和效能最佳化及設定

windows下sqlite安裝使用和效能最佳化及設定 時間:2008-12-31 21:25:48 來源:http://blog.51soump3.com/?action=showlog&gid=51 作者:小五 點擊:3940次

sqlite是嵌入式SQL資料庫引擎SQLite（SQLite Embeddable SQL DatabaseEngine）的一個擴充。SQLite是一個實現嵌入式SQL資料庫引擎小型C語言庫（Clibrary），實現了獨立的，可嵌入的，零配置的SQL資料庫引擎。特性包括：事務操作是原子，一致，孤立，並且持久的，即使在系統崩潰和電源故障之後。零配置——不需要安裝和管理。實現了絕大多數SQL92標準。整個資料庫儲存在一個單一的檔案中。資料庫檔案可以在不同位元組序的機器之間自由地共用。支援最大可達2T的資料庫。字串和BLOB類型的大小隻受限於可用記憶體。完整配置的少於250KB，忽略一些可選特性的少於150KB。在大多數常見操作上比流行的客戶/伺服器資料庫引擎更快。 簡單便於使用的API。 內建TCL綁定。另外提供可用於許多其他語言的綁定。具有良好注釋的原始碼，代碼95%有較好的注釋。獨立：沒有外部依賴。原始碼位於公用域，可用於任何用途。用SQLite串連的程式可以使用SQL資料庫，但不需要運行一個單獨的關係型資料庫管理系統進程（separate RDBMSprocess）。SQLite不是一個用於串連到大型資料庫伺服器（big database server）的用戶端庫（clientlibrary），而是非常適合傳統型程式和小型網站的資料庫伺服器。SQLite直接讀寫（reads and writesdirectly）在硬碟上的資料庫檔案。

下面是我自己的使用過程:
SQLite安裝方案
SQLite的最新版本可以從這裡下載。下面我們以Windows版本sqlite-3_5_1.zip 為例介紹其安裝方法。
（大家可以選擇下載安裝適合自己的版本）
下載後，將sqlite-3_5_1.zip解壓縮至C:\sqlite目錄即完成安裝。
C:\sqlite目錄構造為：
C:\sqlite
  |
  +--sqlite3.exe
開啟一個CMD命令視窗
C:\>cd sqlite
C:\sqlite>sqlite3.exe mydb.db
SQLite version 3.5.1
Enter ".help" for instructions
sqlite> create table user ( id integer primary key, name varchar(32) );
sqlite> .schema
CREATE TABLE user ( id integer primary key, name varchar(32) );
sqlite>.quit
退出後，查看C:\sqlite目錄，會發現多出一個mydb.db檔案：
C:\sqlite
  |
  +--sqlite3.exe
  +--mydb.db

SQLite效能最佳化
很多人直接就使用了，並未注意到SQLite也有配置參數，可以對效能進行調整。有時候，產生的結果會有很大影響。
主要通過pragma指令來實現。
比如： 空間釋放、磁碟同步、Cache大小等。
不要開啟。前文提高了，Vacuum的效率非常低！
PRAGMA auto_vacuum;
PRAGMA auto_vacuum = 0 | 1;
查詢或設定資料庫的auto-vacuum標記。
正常情況下，當提交一個從資料庫中刪除資料的事務時，資料庫檔案不改變大小。未使用的檔案頁被標記並在以後的添加操作中 再次使用。這種情況下使用VACUUM命令釋放刪除得到的空間。
當開啟auto-vacuum，當提交一個從資料庫中刪除資料的事務時，資料庫檔案自動收縮， (VACUUM命令在auto-vacuum開啟的資料庫中不起作用)。資料庫會在內部儲存一些資訊以便支援這一功能，這使得 資料庫檔案比不開啟該選項時稍微大一些。
只有在資料庫中未建任何錶時才能改變auto-vacuum標記。試圖在已有表的情況下修改不會導致報錯。
建議改為8000
PRAGMA cache_size;
PRAGMA cache_size = Number-of-pages;
查詢或修改SQLite一次儲存在記憶體中的資料庫檔案頁數。每頁使用約1.5K記憶體，預設的緩衝大小是2000. 若需要使用改變大量多行的UPDATE或DELETE命令，並且不介意SQLite使用更多的記憶體的話，可以增大緩衝以提高效能。
當使用cache_size pragma改變緩衝大小時，改變僅對目前的交談有效，當資料庫關閉重新開啟時緩衝大小恢複到預設大小。 要想永久改變緩衝大小，使用default_cache_size pragma.
開啟。不然搜尋中文字串會出錯。
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'依然為真。
開啟。便於調試
PRAGMA count_changes;
PRAGMA count_changes = 0 | 1;
查詢或更改count-changes標記。正常情況下INSERT, UPDATE和DELETE語句不返回資料。當開啟count-changes，以上語句返回一行含一個整數值的資料——該語句插入，修改或刪除的行數。返回的行數不包括由觸發器產生的插入，修改或刪除等改變的行數。
PRAGMA page_size;
PRAGMA page_size = bytes;
查詢或設定page-size值。只有在未建立資料庫時才能設定page-size。頁面大小必須是2的整數倍且大於等於512小於等於8192。 上限可以通過在編譯時間修改宏定義SQLITE_MAX_PAGE_SIZE的值來改變。上限的上限是32768.
如果有定期備份的機制，而且少量資料丟失可接受，用OFF
PRAGMA synchronous;
PRAGMA synchronous = FULL; (2)
PRAGMA synchronous = NORMAL; (1)
PRAGMA synchronous = OFF; (0)
查詢或更改"synchronous"標記的設定。第一種形式(查詢)返回整數值。 當synchronous設定為FULL (2),SQLite資料庫引擎在緊急時刻會暫停以確定資料已經寫入磁碟。 這使系統崩潰或電源出問題時能確保資料庫在重起後不會損壞。FULLsynchronous很安全但很慢。 當synchronous設定為NORMAL,SQLite資料庫引擎在大部分緊急時刻會暫停，但不像FULL模式下那麼頻繁。NORMAL模式下有很小的幾率(但不是不存在)發生電源故障導致資料庫損壞的情況。但實際上，在這種情況下很可能你的硬碟已經不能使用，或者發生了其他的不可恢複的硬體錯誤。 設定為synchronous OFF(0)時，SQLite在傳遞資料給系統以後直接繼續而不暫停。若運行SQLite的應用程式崩潰，資料不會損傷，但在系統崩潰或寫入資料時意外斷電的情況下資料庫可能會損壞。另一方面，在synchronous OFF時一些操作可能會快50倍甚至更多。
在SQLite 2中，預設值為NORMAL.而在3中修改為FULL.
使用2，記憶體模式。
PRAGMA temp_store;
PRAGMA temp_store = DEFAULT; (0)
PRAGMA temp_store = FILE; (1)
PRAGMA temp_store = MEMORY; (2)
查詢或更改"temp_store"參數的設定。當temp_store設定為DEFAULT (0),使用編譯時間的C預先處理宏TEMP_STORE來定義儲存暫存資料表和臨時索引的位置。當設定為MEMORY (2)暫存資料表和索引存放於記憶體中。 當設定為FILE(1)則存放於檔案中。temp_store_directorypragma可用於指定存放該檔案的目錄。當改變temp_store設定，所有已存在的暫存資料表，索引，觸發器及視圖將被立即刪除。