SQLite做为本地缓存应注意的几大方面_sql

概述 SQLite做为本地缓存应注意的几大方面 2011-02-18 09:34 Tecky Li 博客园字号： T | T 今天我们要介绍的就是如何利用SQLite作为本地缓存的方法。其拥有适应于本地数据缓存和应用程序等诸多优点。 AD：WOT2015 互联网运维与开发者大会热销抢票今天看到了园友陆敏计的一篇文章<<C#数据本地存储方案之SQLite>>, 写到了SQLite的诸多优点，尤 sqlite做为本地缓存应注意的几大方面 2011-02-18 09:34 Tecky li 博客园字号： T | T

今天我们要介绍的就是如何利用sqlite作为本地缓存的方法。其拥有适应于本地数据缓存和应用程序等诸多优点。

AD：WOT2015 互联网运维与开发者大会热销抢票

今天看到了园友陆敏计的一篇文章<<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>           publicdatatableGetSchema()           {           using(sqliteConnectionconnection=newsqliteConnection(connectionString))           {           connection.open();           Datatabledata=connection.GetSchema("tableS");           connection.Close();           //foreach(DataColumncolumnindata.Columns)           //{           //Console.Writeline(column.Columnname);           //}           returndata;           }           }

第二个方法是IstableExist，判断sqlite数据库重某个表是否存在。

                  ///<summary>           ///判断sqlite数据库表是否存在           ///</summary>           ///<paramname="dbPath">要创建的sqlite数据库文件路径</param>           publicboolistableExist(stringtablename)           {           using(sqliteConnectionconnection=newsqliteConnection(connectionString))           {           connection.open();           using(sqliteCommandcommand=newsqliteCommand(connection))           {           command.CommandText="SELECTCOUNT(*)FROMsqlite_masterwheretype='table'andname='"+tablename+"'";           intiaaa=Convert.ToInt32(command.ExecuteScalar());           if(Convert.ToInt32(command.ExecuteScalar())==0)           {           returnfalse;           }           else           {           returntrue;           }           }           }           }

第三个方法是query，执行查询语句，返回DataSet

                  ///<summary>          ///执行查询语句，返回DataSet           ///</summary>          ///<paramname="sqlString">查询语句</param>          ///<returns>DataSet</returns>          publicdataSetquery(stringsqlString)           {           using(sqliteConnectionconnection=newsqliteConnection(connectionString))           {           DataSetds=newDataSet();           try           {           connection.open();           sqliteDataAdaptercommand=newsqliteDataAdapter(sqlString,connection);           command.Fill(ds,"ds");           }           catch(System.Data.sqlite.sqliteExceptionex)           {           thrownewException(ex.Message);           }           returnds;           }           }

构建缓存对象模型和缓存控制器

每一块缓存对象，在数据库中会产生一个表，而表名称是有缓存控制器自动生成的，访问缓存的工作全部交由缓存控制器完成，通过缓存项的ID和ModuleKey来访问。

在sqlite中还需要一个系统表来维护每个缓存项和实际缓存存储表之间的对应关系，我们称之为配置表，它将在缓存控制器创建sqlite缓存数据库文件时创建。

配置表共有以下几个字段，分别和缓存对象模型CdlCacheItem类映射：

列名称	说明
ID	缓存的唯一数字编号
ModuleKey	缓存模块名称，一个模块可以有多个缓存数据，ID可以区分。实际应用时，某个功能时会经常缓存数据的，所以通过ModuleKey就可以得到这个功能所有的缓存列表，然后选定其中的部分缓存来进行使用。
Comments	缓存说明
tablename	缓存数据存储的数据表名称
AddDate	缓存时间戳

创建数据库的方法如下

                  staticvoIDCreateDB()           {           //总共有ID、ModuleKey、Comments、AddDate这几列           stringsql="CREATEtableSYSCDLtableS(ID          INTEGERNOTNulLPRIMARYKEYautoINCREMENTUNIQUE,MODulEkeyvARCHAR(200),          COMMENTSVARCHAR(500),tablenameVARCHAR(100),ADDDATEDATETIME)";           sqliteDBHelper.CreateDB(CACHEfilePATH,sql);           }

每个缓存项（缓存对象模型）定义如下，和配置表对应：

                  ///<summary>          ///缓存项对象           ///</summary>          ///<Author>TeckyLee</Author>          ///<Date>2011-1-1115:11</Date>          publicclassCdlCacheItem           {           intm_ID;                     publicintID           {           get{returnm_ID;}           set{m_ID=value;}           }           stringm_moduleKey;                     publicstringModuleKey           {           get{returnm_moduleKey;}           set{m_moduleKey=value;}           }           stringm_comments;                     publicstringComments           {           get{returnm_comments;}           set{m_comments=value;}           }           stringm_tablename;                     publicstringtablename           {           get{returnm_tablename;}           set{m_tablename=value;}           }           DateTimem_timestamp;                     publicdateTimeTimestamp           {           get{returnm_timestamp;}           set{m_timestamp=value;}           }           }

下面是控制器的接口定义：

                  publicinterfaceICdlCacheController           {           voIDBeginLoadRow();           voIDEndLoadRow();           System.Collections.Generic.IList<CdlCacheItem>GetCdlCacheItems(stringmoduleKey);           CdlCacheItemGetCdlCacheItems(intID);           voIDLoadRow(System.Data.DaTarowrow,stringtablename);           voIDLoadRow(IEnumerable<object>row,stringtablename);           stringLoadtable(System.Data.Datatabledt,stringmoduleKey,stringcomments);           System.Data.Common.DbDataReaderqueryCdltableReader(CdlCacheItemitem);           System.Data.DatatablequeryCdltables(CdlCacheItemitem);           System.Data.DatatablequeryCdltables(stringsql);           voIDRemoveAlltables();           voIDRemoveCdltables(stringmoduleKey);           voIDRemoveCdltables(System.Collections.Generic.IList<CdlCacheItem>items);           voIDRemoveCdltables(CdlCacheItemitem);           voIDRemoveCdltables(intID);           }

上面的函数下面来做个说明：

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的时候才会提交一次数据）：

                  sqliteConnectionm_connection;           sqliteCommandm_command;           DbTransactionm_transaction;           publicvoIDBeginLoadRow()           {           m_connection=newsqliteConnection("DataSource="+CACHEfilePATH);                     m_connection.Open();           m_transaction=m_connection.BeginTransaction();           m_command=newsqliteCommand(m_connection);           }           publicvoIDEndLoadRow()           {           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.Exceptionex)           {           LogHandle.Error(ex);           }           }