Sqlite資料庫刪除大量資料後的擦屁股工作

SqlCe實在是太慢了，在執行速率和Sqlite沒法比。

所以在項目的下一個版本中，準備用Sqlite替換掉SqlCe。

事前做嘗試工作，畢竟沒有用過Sqlite。

結果試了試確實不錯，有兩點很滿意。

 

第一，執行速率就不用說了，眾所周一的比SqlCe快

 

第二，或許對別人來說是個雞肋，但是我這個還挺需要的（是我想出來的笨解決方案，有什麼好方案，可以幫我提提意見）

什麼優點呢？壓縮率。

不知道有沒有人知道用winZip給sqlite的.db檔案打過包？壓縮率其實是很高的。

舉個例子，同樣的兩個資料庫，一個sqlCe的.sdf檔案，一個Sqlite的.db檔案。

其中的資料內容完全相同，在硬碟上的佔用空間分別是

sqlCe：3,207,168byte，Sqlite：3,117,056byte

 

用winrar進行壓縮，結果分別是

SqlCe：3,158,016byte，Sqlite：1,159,168byte

 

可以看到，SqlCe壓縮後，資料量基本上沒有改變。而Sqlite壓縮後，資料量變成了原來的將近三分之一。

這意味著什麼，這意味著在網路上的傳輸資料量，傳輸時間大大節省了。

按照”中國移不動“的費率，大家可以省很多話費去交人頭稅的。

按照”中國聯不通“的網路，可以大大節省傳輸時間，減少聯不通的機率。

 

呵呵，其實主要是我所在的項目，每次做資料庫同步的時候，如果要在手機端迴圈插入更新一萬條資料，耗時太長，是不能容忍的。於是我愚昧的決定把資料庫檔案傳回伺服器，在伺服器端更新後然後再傳回給手機。於是這個壓縮的問題對我來說很重要。

其次壓縮對我來說還有一點很重要，我是通過soap的方式傳遞資料的。就通過XML，這個方法很笨但是實現起來簡單。問題就在於將3M以上的檔案序列化到xml裡面，傳遞是會無端的出問題。

我就在一台還沒有發售的機器上遇到過StringBuilder的錯誤，Soap底層對XML處理是用到了StringBuilder，這個時候就出過問題。我考慮就是因為3M的資料太大了。（只是猜測，我下午專門對這個機器進行測試）

所以以上種種原因，我需要一個在網路上傳輸最“輕量”的方式。無疑就是壓縮了。

 

說了這麼多，沒進主題，我的主題是給Sqlite在刪除資料後擦屁股。

大家或許發現了，當你在sqlite中刪除了大量資料後，資料庫檔案的大小還是那樣，沒有變。

就用我的項目來說，3M的資料刪除後，sqlite的.db檔案依然是3M，而我要的結果實際上只有35K，在壓縮一下也就10幾K了。為什麼會出現這個問題，SqlCe的.sdf檔案是即刪即減的。

 

原因是：

當你從Sqlite刪除資料後，未使用的磁碟空間被添加到一個內在的”空閑列表”中用於儲存你下次插入的資料。磁碟空間並沒有丟失。但是也不向作業系統返回磁碟空間。（嘿嘿，Sqlite.org的FAQ中提到過這個問題）

 

解決方案：兩種

一，在資料刪除後，手動執行VACUUM命令，執行方式很簡單

 

objSQLHelper.ExecuteNonQuery(CommandType.Text, "VACUUM")

 

VACUUM命令會清空“空閑列表”，把資料庫尺寸壓縮到最小。但是要耗費一些時間。

FQA裡面說，在Linux的環境下，大約0.5秒/M。並且要使用兩倍於資料庫檔案的空間。

我憎恨此FQA，他只說系統內容，不說機器硬體環境。我在測試手機上執行用了將近13秒時間壓縮了將近3M的空間。至於它所佔用的另一部分空間，是產生了一個.db-journal尾碼名的臨時檔案。（這個問題對我現在來說是無所謂的。）

 

二，在資料庫檔案建成中，將auto_vacuum設定成“1”。

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

cmd.CommandText = "PRAGMA auto_vacuum = 1;"

cmd.ExecuteNonQuery()

當開啟auto-vacuum，當提交一個從資料庫中刪除除資料的事物時，資料庫檔案自動收縮。

資料庫會在內部儲存一些資訊以便支援這一功能，這使得資料庫檔案比不開啟該選項時稍微大一些。

我的表結構，不含任何資料是，資料庫檔案大小是25K左右，開了auto_vacuum之後是26K。

插入運行基礎資料後，檔案變成35K，開了auto_vacuum之後是36K。

變化不大，無所謂。

 

但是第二個方法同樣有缺點，只會從資料庫檔案中截斷空閑列表中的頁， 而不會回收資料庫中的片段，也不會像VACUUM 命令那樣重新整理資料庫內容。實際上，由於需要在資料庫檔案中移動頁， auto-vacuum 會產生更多的片段。而且，在執行刪除操作的時候，也有那個.db-journal檔案產生。

要使用 auto-vacuum，需要一些前題條件。 資料庫中需要儲存一些額外的資訊以記錄它所跟蹤的每個資料庫頁都找回其指標位置。 所以，auto-vacumm 必須在建表之前就開啟。在一個表建立之後， 就不能再開啟或關閉 auto-vacumm。

 

其實按照已耗用時間上的比較，兩個在做了大刪除操作後，從3M變到35K的時間其實差不多，執行VACUUM命令稍微長一點，但是也長不了多少，相對而言，這種一點點的長可以忽略不計。

加上AUTO的方式對片段的造成情況，如果資料交換次數多的話，這種方式很不合適。

還是決定用第一種方式，在大資料刪除後，向伺服器送信前。執行VACUUM命令。這樣做比較划算。