SQLite做為本機快取的應用需要注意的地方

今天看到了園友陸敏計的一篇文章<<C#資料本機存放區方案之SQLite>>, 寫到了SQLite的諸多優點，尤其適應於本機資料緩衝和應用程式。

轉自陸兄的內容，來誇誇Sqlite：

SQLite官方網站: http://www.sqlite. org/ 時第一眼看到關於SQLite的特性。

1. ACID事務

2. 零配置 – 無需安裝和管理配置

3. 儲存在單一磁碟檔案中的一個完整的資料庫

4. 資料庫檔案可以在不同位元組順序的機器間自由的共用

5. 支援資料庫大小至2TB

6. 足夠小, 大致3萬行C代碼, 250K

7. 比一些流行的資料庫在大部分普通資料庫操作要快

8. 簡單, 輕鬆的API

9. 包含TCL綁定, 同時通過Wrapper支援其他語言的綁定

10. 良好注釋的原始碼, 並且有著90%以上的測試覆蓋率

11. 獨立: 沒有額外依賴

12. Source完全的Open, 你可以用於任何用途, 包括出售它

13. 支援多種開發語言,C, PHP, Perl, Java, ASP .NET,Python

正好前一段時間我做了這方面的應用，我就結合陸兄的這篇文章，談談我在Sqlite本機快取業務資料時的經驗，給大家借鑒一下。我開發時比較倉促，很多地方請大家多提意見。

解決的問題

首先介紹我用Sqlite解決的實際問題是什嗎？

問題1：某個功能的資料需要串連一個遠端資料庫查詢速度很慢，查一次資料不容易，希望能夠重複利用之前查過的資料集。

問題2：非常大的資料量比如幾千萬甚至幾億條資料，一次性讀取到DataTable中，會記憶體溢出的，所以在第一次分析時就是通過Reader的方式，分析完一條後並不在記憶體中儲存，但是緊接著使用者的第二次分析、第三次分析還是要用到的第一次分析的資料，如果我們重新查詢一次遠程伺服器，效率可想而知啊。

結合上面的2個問題，為瞭解決效率問題和資料重複利用度，減少資料庫伺服器的壓力，我才用Sqlite快取資料（當然這不是唯一也不是最好的解決方案） 。

最佳化SQLiteHelper

陸兄的SQLiteHelper類我增加了幾個有用的方法：

第一個方法是GetSchema，得到某個表的表結構。

        /// <summary>           /// 查詢資料庫中的所有資料類型資訊           /// </summary>           /// <returns></returns>           public DataTable GetSchema()        {            using (SQLiteConnection connection = new SQLiteConnection(connectionString))            {                connection.Open();                DataTable data = connection.GetSchema("TABLES");                connection.Close();                //foreach (DataColumn column in data.Columns)                   //{                   //    Console.WriteLine(column.ColumnName);                   //}                   return data;            }        }

第二個方法是IsTableExist，判斷SQLite資料庫重某個表是否存在 。

        /// <summary>           /// 判斷SQLite資料庫表是否存在          /// </summary>           /// <param name="dbPath">要建立的SQLite資料庫檔案路徑</param>           public bool IsTableExist(string tableName)        {            using (SQLiteConnection connection = new SQLiteConnection(connectionString))            {                connection.Open();                using (SQLiteCommand command = new SQLiteCommand(connection))                {                    command.CommandText = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='" + tableName + "'";                    int iaaa = Convert.ToInt32(command.ExecuteScalar());                    if (Convert.ToInt32(command.ExecuteScalar()) == 0)                    {                        return false;                    }                    else                    {                        return true;                    }                }            }        }

第三個方法是Query，執行查詢語句，返回DataSet

        /// <summary>        /// 執行查詢語句，返回DataSet        /// </summary>        /// <param name="SQLString">查詢語句</param>        /// <returns>DataSet</returns>        public DataSet Query(string SQLString)        {            using (SQLiteConnection connection = new SQLiteConnection(connectionString))            {                DataSet ds = new DataSet();                try                {                    connection.Open();                    SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);                    command.Fill(ds, "ds");                }                catch (System.Data.SQLite.SQLiteException ex)                {                    throw new Exception(ex.Message);                }                return ds;            }        }

構建緩衝物件模型和緩衝控制器

每一塊緩衝對象，在資料庫中會產生一個表，而表名稱是有緩衝控制器自動產生的，訪問緩衝的工作全部交由緩衝控制器完成，通過快取項目的ID和ModuleKey來訪問。

在Sqlite中還需要一個系統資料表來維護每個快取項目和實際緩衝儲存表之間的對應關係，我們稱之為配置表，它將在緩衝控制器建立Sqlite快取資料庫檔案時建立。

配置表共有以下幾個欄位，分別和緩衝物件模型CdlCacheItem類映射：

列名稱	說明
Id	緩衝的唯一數字編號
ModuleKey	緩衝模組名稱，一個模組可以有多個快取資料，ID可以區分。實際應用時，某個功能時會經常快取資料的，所以通過ModuleKey就可以得到這個功能所有的緩衝列表，然後選定其中的部分緩衝來進行使用。
Comments	緩衝說明
TableName	快取資料儲存的資料表名稱
AddDate	緩衝時間戳記

建立資料庫的方法如下：

        static void CreateDB()        {            //總共有ID、ModuleKey、Comments、AddDate這幾列            string sql = "CREATE TABLE SYSCDLTABLES(ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,MODULEKEY VARCHAR(200),COMMENTS VARCHAR(500),TABLENAME VARCHAR(100),ADDDATE DATETIME)";            SQLiteDBHelper.CreateDB(CACHEFILEPATH, sql);        }

每個快取項目（緩衝物件模型）定義如下，和配置表對應：

    /// <summary>    /// 快取項目對象    /// </summary>    /// <Author>Tecky Lee</Author>    /// <Date>2011-1-11 15:11</Date>    public class CdlCacheItem    {        int m_id;        public int Id        {            get { return m_id; }            set { m_id = value; }        }        string m_moduleKey;        public string ModuleKey        {            get { return m_moduleKey; }            set { m_moduleKey = value; }        }        string m_comments;        public string Comments        {            get { return m_comments; }            set { m_comments = value; }        }        string m_tableName;        public string TableName        {            get { return m_tableName; }            set { m_tableName = value; }        }        DateTime m_timestamp;        public DateTime Timestamp        {            get { return m_timestamp; }            set { m_timestamp = value; }        }    }

下面是控制器的介面定義：

public interface ICdlCacheController    {        void BeginLoadRow();        void EndLoadRow();        System.Collections.Generic.IList<CdlCacheItem> GetCdlCacheItems(string moduleKey);        CdlCacheItem GetCdlCacheItems(int id);        void LoadRow(System.Data.DataRow row, string tableName);        void LoadRow(IEnumerable<object> row, string tableName);        string LoadTable(System.Data.DataTable dt, string moduleKey, string comments);        System.Data.Common.DbDataReader QueryCdlTableReader(CdlCacheItem item);        System.Data.DataTable QueryCdlTables(CdlCacheItem item);        System.Data.DataTable QueryCdlTables(string sql);        void RemoveAllTables();        void RemoveCdlTables(string moduleKey);        void RemoveCdlTables(System.Collections.Generic.IList<CdlCacheItem> items);        void RemoveCdlTables(CdlCacheItem item);        void RemoveCdlTables(int id);    }

上面的函數下面來做個說明：

1、BeginLoadRow、LoadRow和EndLoadRow，三個函數組為了在我們查詢主要資料庫時使用Reader方式讀取資料時，可以一條條將資料同時存放在緩衝中。

2、RemoveAllTables和RemoveCdlTables是用來刪除快取項目的。

3、GetCdlCacheItems，通過moduleKey得到多個快取項目。比如使用者想基於這幾天內儲存的某個功能的資料做一次快速分析，那麼我們就可以通過這個函數得到緩衝列表，由使用者挑選清單中的一個來繼續。

4、QueryCdlTableReader，得到某個快取資料的Reader對象，這樣可以一行行的分析，一次讀出大資料量的資料到DataTable中，記憶體可能會溢出的。

5、QueryCdlTables，將某個快取項目查詢並裝載到DataTable中。

 

提高快取資料寫入效率

Sqlite在儲存資料的時候，比如一次儲存一個億條的資料，一條條插入效率非常低下，網上也有人對其進行討論。

效率低下的主要原因在於IO操作次數過於頻繁，所以在LoadTable或者是使用BeginLoadRow·EndLoadRow的時候，使用了事務來減少資料提交的次數，結果儲存的效率非常的高，我測試的結果是400萬條資料查詢，只需要幾十秒鐘，這點時間相對於重新查一次遠程伺服器那是可以忽略了。

下面給出BeginLoadRow和EndLoadRow的具體代碼（只有在EndRow的時候才會提交一次資料）：

        SQLiteConnection m_connection;        SQLiteCommand m_command;        DbTransaction m_transaction;        public void BeginLoadRow()        {            m_connection = new SQLiteConnection("Data Source=" + CACHEFILEPATH);            m_connection.Open();            m_transaction = m_connection.BeginTransaction();            m_command = new SQLiteCommand(m_connection);        }        public void EndLoadRow()        {            try            {                if (m_command != null)                    m_command.Dispose();                if (m_transaction != null)                {                    m_transaction.Commit();                }                if (m_connection != null)                {                    m_connection.Close();                    m_connection.Dispose();                }            }            catch (System.Exception ex)            {                LogHandle.Error(ex);            }        }

LoadTable函數內部也是調用BeginLoadRow·EndLoadRow模式來完成的。

 

資料庫檔案如何建立：

Sqlite資料庫檔案如果不存在，在執行sql語句的時候，會自動根據ConnetionString中指定的位置建立資料庫檔案，預設建立的空資料庫只有4K。

其他有待討論的問題：

1、我是將所有的緩衝做到一個資料庫檔案中了，實際應用根據業務的不同，可以一份快取資料一個檔案也是很好管理的，維護也方便，資源管理員中就可以拷貝刪除等。

2、當我們儲存一億條資料到Sqlite的時候，因為Sqlite沒有壓縮資料，結果資料庫檔案就可以會有好幾個G（這也不一定，適合資料庫欄位的多少，欄位類型有關的）。

檔案太大就消耗了磁碟空間，而且使用者或者程式如果不及時清理的，可能會耗盡磁碟空間。

這裡就必須建立一個機制，檢查sqlite的緩衝並及時清理，或者設定緩衝應用的上限，當達到上限後自動根據時間戳記清理曆史緩衝。