SQLite做為本地緩存應注意的幾大方面

作者：Tecky Li 2011-02-18 09:34:10

數據庫其他數據庫

今天我們要介紹的就是如何利用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的緩存并及時清理，或者設置緩存應用的上限，當達到上限后自動根據時間戳清理歷史緩存。

原文鏈接：http://www.cnblogs.com/TeckyLi/archive/2011/02/17/1957317.html

責任編輯：彭凡來源：博客園

SQLite