sqlite使用總結

隨便說說，也是一點編程的備忘。Sqlite是一個不錯的嵌入式資料庫，這個嵌入式基本上是精巧和輕量的代名詞，sqlite引用的情境很多，firefox和android的內部都是有應用的，這個小資料庫代碼不多，但是功能比較完備，這裡不說如何使用。如果想知道如何使用，請自己參考資料庫教材，和這個URL http://www.codeproject.com/KB/database/CppSQLite.aspx，當然了，如果有更好的CPP封裝，也不反對。這個小資料庫不光光支援C，也支援很多其他語言，基本上編程top
10 的語言都支援。

我這篇文檔，想先簡單總結一些技術細節。還有其他方面，比如資料類型，資料庫格式。外鍵、insert衝突處理、視圖效能，記憶體資料庫的使用方式。我會慢慢補充的。

      並發和鎖

         程式員一般都是很關心效率的，對於資料庫查詢的時候，都是很關心並發和鎖的問題。到底是行級鎖還是表層級鎖。Sqlite在這個地方是表層級的讀寫鎖，換句話說，寫的時候不能查。查的時候不能寫。我這裡單單說明一下windows的實現，sqlite內部使用的是關鍵區和訊號類比，如果在windows下，關鍵區的效率是很高的。

         我們知道，sqlite是一個單檔案資料庫，在實際應用的時候，會出現多個進程同時訪問一個資料庫檔案的情況。不幸的是，這個情況下，sqlite採取的是鎖住整個檔案的方式，當然實際上採取的是部分鎖的情況，比如這個表佔用了資料庫檔案的一段，那麼sqlite僅僅鎖住這一段。但是即便是這個樣子，依然在高並發的情境下，不能滿足我們的需求。這個時候就要考慮是否採取其他的替代方案，甚至這個時候，採取我們自己寫的資料檔案也是可行的。

         當然以上的情境有一個十分重要的前提，就是你要搞清楚你用sqlite的情境，是否一定要用到sqlite的資料庫功能，如果僅僅是查詢資料，不刪除修改資料，完全沒有必要引入sqlite。

         對於多進程同時讀寫資料庫檔案的情境，sqlite的操作，例如執行sql語句不一定每次都會成功，有可能會返回 SQLITE_BUSY，代表當前資料庫正忙。返回這個數值，其實就應該像返回 IO_PENDING的處理方式一樣，我們要寫一個簡單的迴圈。直到返回SQLITE_OK為止。

         在最新的sqlite實現中，給我們提供了  sqlite3_busy_handler 函數，資料庫返回不忙的時候，會調用這個函數設定的callback。這就不需要我們來寫迴圈了，對於效能是一個比較好的最佳化。詳細見 http://sqlite.org/c3ref/busy_handler.html

         關於多線程，這裡多說一句，就是從3.5以後sqlite可以做到多線程共用一個資料庫連接cache，這個可以有效控制記憶體的數量。見 http://www.sqlite.org/sharedcache.html

        

資料一致性和插入效能

         資料庫一個重要屬性，就是保證資料的一致性。Sqlite在這個上面做了很多的工作，可以保證在斷電的情況下，要不修改資料成功，要不沒有成功。這個我們在一般的編程的時候，基本上可以忽略這個因素。具體細節可見 http://sqlite.org/atomiccommit.html

         但是，在有的情境下，資料庫的一致性和插入效能往往是一對矛盾。在保證一致性的時候，sqlite在每次修改資料庫的時候，會首先產生一個資料修改記錄檔，然後再將這個檔案合并到主要資料庫檔案，這個情境下，對於磁碟IO的效能是很有影響的。我們知道cpu要比磁碟IO快不止一個數量級，在實際應用的時候，你會發現插入往往是sqlite資料庫操作的一個瓶頸。

         其實這個效能瓶頸也很好解決，根據作業系統的屬性，磁碟檔案（包括硬碟和SSD）如果想提高寫的效能，那麼最好是成塊成批的寫。不涉及作業系統底層，我們僅僅從最簡單的sql語句上說，如果在有任何修改的是，都提交一個事務，然後再完成修改後提交事務。這個時候sqlite是會採取批量成塊寫的策略。我在實際應用中，如果不啟動事務，單條修改就提交，和批量修改、最後提交，要慢一個數量級。

所以說，我們在實際應用中，對於任何修改，包括修改表和都需要按照事務的方式來處理。這樣無論從效能上，還是一致性性上都是正確的做法。多說一句，Sqlite對著這個地方，只要你要豁得出去死，他也能埋，你可以將事務通過PRAGMA關閉。當然了，一般沒有人會這麼幹，如果要真的對日誌寫入的效能有要求，其實可以採取Trancate 方式設定日誌刪除方式。預設情況下是通過delete方式刪除，Trancate僅僅將檔案清除成0，不修改jourel檔案的相關目錄，效能有些許的提高。

在多進程情境下，建議大家採取wal日誌，這個日誌方式可以擷取更好的多線程讀寫並行性，但是有一點需要注意，就是這個wal格式的資料庫檔案，可必須從一而終，wal格式的資料庫，只能是wal方式開啟。他對於3.7以前版本的資料庫支援不好，但是在新項目中是可以使用的。

這裡跟大家說一個tip，開始事務之後，你的實際操作都是在記憶體中執行的，暫時不會同步到硬碟檔案上去。你可能擔心，如果我提交了1W個插入，是不是我就要佔用很大的記憶體啊？實際上不是，因為在sqlite內部是會定期將你的操作同步到資料庫檔案的，你這個不用擔心，這個操作就是資料庫裡面標準的checkpoint 操作， 到達1000 個頁面就產生一個checkpoint。checkpoint在提交後，會自動銷毀。

         說到事務提交，使用sqlite要有一個紀律，就是提交失敗就要復原。因為，如果不復原會對產生的修改記錄檔或者checkpoint造成不良影響，有可能導致下一次開啟資料庫檔案不成功。而且從程式邏輯語義上，也應該是那裡自己乾的事情，自己負責，盡量不要讓別的邏輯或者代碼幫你擦屁股。

Try –catch 不能解決問題，只能屏蔽問題。

      Sqlite查詢效能

         說道資料庫，我們必須要說明一下B-tree，sqlite內部的索引結構大部分用的是B-Tree（當然，新版本裡面又有了一個R-tree。給空間資料庫用的，暫時不討論。）。這裡不討論B-Tree的實現和演算法，大家可以自己查詢先關資料。我只想說明大家在這個地方不用擔心，不在萬不得已的情況下，基本上可以不考慮這個地方，我這裡轉載一個評測結果

http://blog.csdn.net/mynicedream/article/details/2252398

是和Berkeley DB 對比

	Berkeley DB	Sqlite
插入10000條記錄耗時	0.08秒	0.42秒
插入100000條記錄耗時	2.31秒	3.81秒
插入7200000條記錄耗時	1024.34秒	249秒
插入57600000條記錄耗時	12860.78秒	2155.14秒
插入172800000條記錄耗時	48039.64秒	6352.06秒
10000條記錄查1記錄耗時	少於0.01秒	少於0.01秒
100000條記錄查1記錄耗時	少於0.01秒	少於0.01秒
7200000條記錄查1記錄耗時	少於0.01秒	少於0.01秒
57600000條記錄查1記錄耗時	0.03秒	0.16秒
172800000條記錄查1記錄耗時	0.03秒	0.09秒
10000條記錄資料庫大小	0.628M	0.527M
100000條記錄資料庫大小	5.29M	5.32M
7200000條記錄資料庫大小	516M	405M
57600000條記錄資料庫大小	3087.13M	3925.8M
172800000條記錄資料庫大小	11890.7M	10621.2M

這個評測的資料庫表也很簡單，是一個表上僅僅一個欄位建立索引。所以說，如果發現你查詢的效能很慢，請輕易不要懷疑sqlite的實現，請看看你寫的sql語句和表的結構。一般都能找到問題所在。

Sql查詢的最佳化是一個比較大的話題，這裡不想具體展開，請參考

http://www.sqlite.org/optoverview.html

Sqlite架構

前面說道，sqlite麻雀雖小，五髒俱全。其實完全可以這樣說。我第一次接觸sqlite的時候，驚豔於他的架構設計。這麼小的嵌入式資料庫，基本上將大型資料庫該有的模組和功能涵蓋的比較全面。上一個真相

這裡需要說明一下就是他的虛擬機器，sqlite的虛擬機器有自己的虛擬位元組碼，採取這種結構，可以說對於sql語句的調試、跟蹤和最佳化都是很有好處的。這個也就是下面要說明的，使用sqlite的小技巧的前提

更多架構詳細請見：http://sqlite.org/arch.html

提示採取綁定方式插入或者修改資料

         我們知道sqlite是的sql執行引擎是一個虛擬，他也有編譯sql的過程，編譯出來的位元組碼也是虛擬機器執行的最終指令。如果每次採取 ：

char buf[128];

            sprintf(buf, "insert into emp (empname) values ('Empname%06d');", i+1);

            db.execDML(buf);

      這種方式來執行插入操作，那麼每次都要重新編譯一邊sql語句，這個對於大量資料插入的時候還是有效能損耗的。所以最好採取bind方式來插入，這個時候，僅僅需要編譯一次sql語句，執行效率也是會有提升的。

      這裡說一點，就是sql編譯都是多安全執行緒的，如果對一個資料庫連接進行修改或者查詢，在sqlite內部實現是首先進入關鍵區。這裡不需要擔心，但是對於並行性要求比較高的情境，預先編譯是很划算的。

加密資料庫檔案

         在實際應用中，我們需要加密資料庫檔案，這個沒有什麼好說的。我們可以採取兩種方案，一種是通過修改代碼，提供一個sqllite_key 和 sqlite_rekey 函數，來做資料庫加密。這個方案我個人不推薦，因為要修改資料庫代碼。

         個人推薦，提供一個VFS檔案系統（虛擬檔案系統），插入到sqlite中。Sqlite真正寫檔案的時候，是會調用系統註冊的VFS來做真正的插入操作，其實我們只要將加密和解密函數放入到VFS的讀寫操作裡面。這個也是經典的層模式，這裡不再囉嗦。具體如何編寫可以見相關文檔 http://www.sqlite.org/vfs.html，這個是標準介面，完全可以滿足我們的加密需求。

         因為是層模式，可以自由的調配層的順序，可以搭配出不同的層結構。相對比較靈活。

非同步寫檔案

         在有些情境下，比如記錄日誌到sqlite資料庫中，一個或者多個線程組建記錄檔，一個線程負責將資料insert到資料庫中。在以前的版本中，我們一般都是採取手工寫一個隊列，然後寫資料庫線程不停的從隊列裡面取出資料。這個隊列往往需要我們自己實現。

現在最新的sqlite，提供了非同步寫方式，其實也很簡單，就是一個VFS系統，只不過你要使用這個功能，必要在開啟資料庫檔案前調用一個 sqlite3async_initialize 函數，同時你也要提供一個線程，要調用 sqlite3async_run 函數來真正實現讀寫功能。具體可以見http://www.sqlite.org/asyncvfs.html

可能有人問了，這個跟我自己實現的隊列有區別嗎？都是隊列，最後寫入。其實區別很大。

1.         你在寫程式的時候，不用自己關心多線程的問題，正常讀寫資料庫就ok了

2.         這個VFS是在整個架構的最下層，實際上，寫那個檔案page，寫那個部分，完全是資料庫引擎自己說了算。如果採取自己實現的隊列方案，每次在寫資料的時候，必須完全同步到硬碟上，然後再執行下一步。沒有真正發揮最佳化的功能。

3.         在select中，實際上是從磁碟和非同步檔案的寫隊列裡面讀取的。這個時候，不涉及硬碟操作，對於頻繁插入的資料庫，可以緩解一點磁碟IO。

說了這麼多好處，壞處在那裡？

1.         一致性有損失，如果斷電了，在寫隊列裡面的資料會丟失。

2.         多進程訪問資料庫時候，如果寫隊列比較大，其他讀資料庫的進程會有查詢的延遲，不能即時響應修改。

 

當然，天下沒有免費的午餐，具體的需求決定具體的設計。在使用的時候一定要注意。

重要的PRAGMA

         這個我不想多說，這個是sql語句，基本上sqlite的總控制開關。具體見文檔

http://www.sqlite.org/pragma.html#syntax

你可以用他做到

l  控制使用記憶體大小

l  控制字元串編碼方式

l  串連cache大小

l  檔案同步方式

l  等等

壓縮資料庫時候要注意的問題

資料庫檔案大了，中間肯定有片段（也許吧~）。我們就用 VACUUM 語句來壓縮一下，這裡需要注意一點，就是 VACUUM 命令有可能改變 INTEGER PRIMARY KEY 的數值，所以說在寫程式的時候，對於 INTEGER PRIMARY KEY 不能有任何的假設，也千萬不要認為他一定就是增加的。