今天看到了園友陸敏計(jì)的一篇文章<<C#數(shù)據(jù)本地存儲(chǔ)方案之SQLite>>, 寫到了SQLite的諸多優(yōu)點(diǎn)，尤其適應(yīng)于本地?cái)?shù)據(jù)緩存和應(yīng)用程序。

轉(zhuǎn)自陸兄的內(nèi)容，來夸夸Sqlite：

SQLite官方網(wǎng)站: http://www.sqlite. org/ 時(shí)第一眼看到關(guān)于SQLite的特性。

1. ACID事務(wù)

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

3. 儲(chǔ)存在單一磁盤文件中的一個(gè)完整的數(shù)據(jù)庫

4. 數(shù)據(jù)庫文件可以在不同字節(jié)順序的機(jī)器間自由的共享

5. 支持?jǐn)?shù)據(jù)庫大小至2TB

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

7. 比一些流行的數(shù)據(jù)庫在大部分普通數(shù)據(jù)庫操作要快

8. 簡單, 輕松的API

9. 包含TCL綁定, 同時(shí)通過Wrapper支持其他語言的綁定

10. 良好注釋的源代碼, 并且有著90%以上的測(cè)試覆蓋率

11. 獨(dú)立: 沒有額外依賴

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

13. 支持多種開發(fā)語言,C, PHP, Perl, Java, ASP .NET,Python

正好前一段時(shí)間我做了這方面的應(yīng)用，我就結(jié)合陸兄的這篇文章，談?wù)勎以赟qlite本地緩存業(yè)務(wù)數(shù)據(jù)時(shí)的經(jīng)驗(yàn)，給大家借鑒一下。我開發(fā)時(shí)比較倉促，很多地方請(qǐng)大家多提意見。

解決的問題

首先介紹我用Sqlite解決的實(shí)際問題是什么？

問題1：某個(gè)功能的數(shù)據(jù)需要連接一個(gè)遠(yuǎn)程數(shù)據(jù)庫查詢速度很慢，查一次數(shù)據(jù)不容易，希望能夠重復(fù)利用之前查過的數(shù)據(jù)集。

問題2：非常大的數(shù)據(jù)量比如幾千萬甚至幾億條數(shù)據(jù)，一次性讀取到DataTable中，會(huì)內(nèi)存溢出的，所以在第一次分析時(shí)就是通過Reader的方式，分析完一條后并不在內(nèi)存中保存，但是緊接著用戶的第二次分析、第三次分析還是要用到的第一次分析的數(shù)據(jù)，如果我們重新查詢一次遠(yuǎn)程服務(wù)器，效率可想而知啊。

結(jié)合上面的2個(gè)問題，為了解決效率問題和數(shù)據(jù)重復(fù)利用度，減少數(shù)據(jù)庫服務(wù)器的壓力，我才用Sqlite緩存數(shù)據(jù)（當(dāng)然這不是唯一也不是最好的解決方案） 。

優(yōu)化SQLiteHelper

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

第一個(gè)方法是GetSchema，得到某個(gè)表的表結(jié)構(gòu)。

/// <summary>     
/// 查詢數(shù)據(jù)庫中的所有數(shù)據(jù)類型信息     
/// </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;  
    }  
} 

第二個(gè)方法是IsTableExist，判斷SQLite數(shù)據(jù)庫重某個(gè)表是否存在 。

/// <summary>     
/// 判斷SQLite數(shù)據(jù)庫表是否存在    
/// </summary>     
/// <param name="dbPath">要?jiǎng)?chuàng)建的SQLite數(shù)據(jù)庫文件路徑</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;  
            }  
        }  
    }  
} 

第三個(gè)方法是Query，執(zhí)行查詢語句，返回DataSet

/// <summary> 
/// 執(zhí)行查詢語句，返回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;  
    }  
} 

構(gòu)建緩存對(duì)象模型和緩存控制器

每一塊緩存對(duì)象，在數(shù)據(jù)庫中會(huì)產(chǎn)生一個(gè)表，而表名稱是有緩存控制器自動(dòng)生成的，訪問緩存的工作全部交由緩存控制器完成，通過緩存項(xiàng)的ID和ModuleKey來訪問。

在Sqlite中還需要一個(gè)系統(tǒng)表來維護(hù)每個(gè)緩存項(xiàng)和實(shí)際緩存存儲(chǔ)表之間的對(duì)應(yīng)關(guān)系，我們稱之為配置表，它將在緩存控制器創(chuàng)建Sqlite緩存數(shù)據(jù)庫文件時(shí)創(chuàng)建。

配置表共有以下幾個(gè)字段，分別和緩存對(duì)象模型CdlCacheItem類映射：

創(chuàng)建數(shù)據(jù)庫的方法如下

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);  
} 

每個(gè)緩存項(xiàng)（緩存對(duì)象模型）定義如下，和配置表對(duì)應(yīng)：

/// <summary> 
/// 緩存項(xiàng)對(duì)象  
/// </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);  
    } 

上面的函數(shù)下面來做個(gè)說明：

1、BeginLoadRow、LoadRow和EndLoadRow，三個(gè)函數(shù)組為了在我們查詢主數(shù)據(jù)庫時(shí)使用Reader方式讀取數(shù)據(jù)時(shí)，可以一條條將數(shù)據(jù)同時(shí)存放在緩存中。

2、RemoveAllTables和RemoveCdlTables是用來刪除緩存項(xiàng)的。

3、GetCdlCacheItems，通過moduleKey得到多個(gè)緩存項(xiàng)。比如用戶想基于這幾天內(nèi)保存的某個(gè)功能的數(shù)據(jù)做一次快速分析，那么我們就可以通過這個(gè)函數(shù)得到緩存列表，由用戶選擇列表中的一個(gè)來繼續(xù)。

4、QueryCdlTableReader，得到某個(gè)緩存數(shù)據(jù)的Reader對(duì)象，這樣可以一行行的分析，一次讀出大數(shù)據(jù)量的數(shù)據(jù)到DataTable中，內(nèi)存可能會(huì)溢出的。

5、QueryCdlTables，將某個(gè)緩存項(xiàng)查詢并裝載到DataTable中。

提高緩存數(shù)據(jù)寫入效率

Sqlite在保存數(shù)據(jù)的時(shí)候，比如一次保存一個(gè)億條的數(shù)據(jù)，一條條插入效率非常低下，網(wǎng)上也有人對(duì)其進(jìn)行討論。

效率低下的主要原因在于IO操作次數(shù)過于頻繁，所以在LoadTable或者是使用BeginLoadRow·EndLoadRow的時(shí)候，使用了事務(wù)來減少數(shù)據(jù)提交的次數(shù)，結(jié)果保存的效率非常的高，我測(cè)試的結(jié)果是400萬條數(shù)據(jù)查詢，只需要幾十秒鐘，這點(diǎn)時(shí)間相對(duì)于重新查一次遠(yuǎn)程服務(wù)器那是可以忽略了。

下面給出BeginLoadRow和EndLoadRow的具體代碼（只有在EndRow的時(shí)候才會(huì)提交一次數(shù)據(jù)）：

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函數(shù)內(nèi)部也是調(diào)用BeginLoadRow·EndLoadRow模式來完成的。

數(shù)據(jù)庫文件如何創(chuàng)建：

Sqlite數(shù)據(jù)庫文件如果不存在，在執(zhí)行sql語句的時(shí)候，會(huì)自動(dòng)根據(jù)ConnetionString中指定的位置創(chuàng)建數(shù)據(jù)庫文件，默認(rèn)創(chuàng)建的空數(shù)據(jù)庫只有4K。

其他有待討論的問題：

1、我是將所有的緩存做到一個(gè)數(shù)據(jù)庫文件中了，實(shí)際應(yīng)用根據(jù)業(yè)務(wù)的不同，可以一份緩存數(shù)據(jù)一個(gè)文件也是很好管理的，維護(hù)也方便，資源管理器中就可以拷貝刪除等。

2、當(dāng)我們存儲(chǔ)一億條數(shù)據(jù)到Sqlite的時(shí)候，因?yàn)镾qlite沒有壓縮數(shù)據(jù)，結(jié)果數(shù)據(jù)庫文件就可以會(huì)有好幾個(gè)G（這也不一定，適合數(shù)據(jù)庫字段的多少，字段類型有關(guān)的）。

文件太大就消耗了磁盤空間，而且用戶或者程序如果不及時(shí)清理的，可能會(huì)耗盡磁盤空間。

這里就必須建立一個(gè)機(jī)制，檢查sqlite的緩存并及時(shí)清理，或者設(shè)置緩存應(yīng)用的上限，當(dāng)達(dá)到上限后自動(dòng)根據(jù)時(shí)間戳清理歷史緩存。

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