学步园

随便说说，也是一点编程的备忘。Sqlite是一个不错的嵌入式数据库，这个嵌入式基本上是精巧和轻量的代名词，sqlite引用的场景很多，firefox和android的内部都是有应用的，这个小数据库代码不多，但是功能比较完备，这里不说如何使用。如果想知道如何使用，请自己参考数据库教材，和这个URL http://www.codeproject.com/KB/database/CppSQLite.aspx，当然了，如果有更好的CPP封装，也不反对。这个小数据库不光光支持C，也支持很多其他语言，基本上编程top
10 的语言都支持。

我这篇文档，想先简单总结一些技术细节。还有其他方面，比如数据类型，数据库格式。外键、insert冲突处理、视图性能，内存数据库的使用方式。我会慢慢补充的。

      并发和锁

         程序员一般都是很关心效率的，对于数据库查询的时候，都是很关心并发和锁的问题。到底是行级锁还是表级别锁。Sqlite在这个地方是表级别的读写锁，换句话说，写的时候不能查。查的时候不能写。我这里单单说明一下windows的实现，sqlite内部使用的是关键区和信号模拟，如果在windows下，关键区的效率是很高的。

         我们知道，sqlite是一个单文件数据库，在实际应用的时候，会出现多个进程同时访问一个数据库文件的情况。不幸的是，这个情况下，sqlite采取的是锁住整个文件的方式，当然实际上采取的是部分锁的情况，比如这个表占用了数据库文件的一段，那么sqlite仅仅锁住这一段。但是即便是这个样子，依然在高并发的场景下，不能满足我们的需求。这个时候就要考虑是否采取其他的替代方案，甚至这个时候，采取我们自己写的数据文件也是可行的。

         当然以上的场景有一个十分重要的前提，就是你要搞清楚你用sqlite的场景，是否一定要用到sqlite的数据库功能，如果仅仅是查询数据，不删除修改数据，完全没有必要引入sqlite。

         对于多进程同时读写数据库文件的场景，sqlite的操作，例如执行sql语句不一定每次都会成功，有可能会返回 SQLITE_BUSY，代表当前数据库正忙。返回这个数值，其实就应该像返回 IO_PENDING的处理方式一样，我们要写一个简单的循环。直到返回SQLITE_OK为止。

         在最新的sqlite实现中，给我们提供了  sqlite3_busy_handler 函数，数据库返回不忙的时候，会调用这个函数设定的callback。这就不需要我们来写循环了，对于性能是一个比较好的优化。详细见 http://sqlite.org/c3ref/busy_handler.html

         关于多线程，这里多说一句，就是从3.5以后sqlite可以做到多线程共享一个数据库连接cache，这个可以有效的控制内存的数量。见 http://www.sqlite.org/sharedcache.html

数据一致性和插入性能

         数据库一个重要属性，就是保证数据的一致性。Sqlite在这个上面做了很多的工作，可以保证在断电的情况下，要不修改数据成功，要不没有成功。这个我们在一般的编程的时候，基本上可以忽略这个因素。具体细节可见 http://sqlite.org/atomiccommit.html

         但是，在有的场景下，数据库的一致性和插入性能往往是一对矛盾。在保证一致性的时候，sqlite在每次修改数据库的时候，会首先生成一个数据修改日志文件，然后再将这个文件合并到主数据库文件，这个场景下，对于磁盘IO的性能是很有影响的。我们知道cpu要比磁盘IO快不止一个数量级，在实际应用的时候，你会发现插入往往是sqlite数据库操作的一个瓶颈。

         其实这个性能瓶颈也很好解决，根据操作系统的属性，磁盘文件（包括硬盘和SSD）如果想提高写的性能，那么最好是成块成批的写。不涉及操作系统底层，我们仅仅从最简单的sql语句上说，如果在有任何修改的是，都提交一个事务，然后再完成修改后提交事务。这个时候sqlite是会采取批量成块写的策略。我在实际应用中，如果不启动事务，单条修改就提交，和批量修改、最后提交，要慢一个数量级。

所以说，我们在实际应用中，对于任何修改，包括修改表和都需要按照事务的方式来处理。这样无论从性能上，还是一致性性上都是正确的做法。多说一句，Sqlite对着这个地方，只要你要豁得出去死，他也能埋，你可以将事务通过PRAGMA关闭。当然了，一般没有人会这么干，如果要真的对日志写入的性能有要求，其实可以采取Trancate 方式设定日志删除方式。默认情况下是通过delete方式删除，Trancate仅仅将文件清除成0，不修改jourel文件的相关目录，性能有些许的提高。

在多进程场景下，建议大家采取wal日志，这个日志方式可以获取更好的多线程读写并行性，但是有一点需要注意，就是这个wal格式的数据库文件，可必须从一而终，wal格式的数据库，只能是wal方式打开。他对于3.7以前版本的数据库支持不好，但是在新项目中是可以使用的。

这里跟大家说一个tip，开始事务之后，你的实际操作都是在内存中执行的，暂时不会同步到硬盘文件上去。你可能担心，如果我提交了1W个插入，是不是我就要占用很大的内存啊？实际上不是，因为在sqlite内部是会定期将你的操作同步到数据库文件的，你这个不用担心，这个操作就是数据库里面标准的checkpoint 操作， 到达1000 个页面就生成一个checkpoint。checkpoint在提交后，会自动销毁。

         说到事务提交，使用sqlite要有一个纪律，就是提交失败就要回滚。因为，如果不回滚会对产生的修改日志文件或者checkpoint造成不良影响，有可能导致下一次打开数据库文件不成功。而且从程序逻辑语义上，也应该是那里自己干的事情，自己负责，尽量不要让别的逻辑或者代码帮你擦屁股。

Try –catch 不能解决问题，只能屏蔽问题。

      Sqlite查询性能

         说道数据库，我们必须要说明一下B-tree，sqlite内部的索引结构大部分用的是B-Tree（当然，新版本里面又有了一个R-tree。给空间数据库用的，暂时不讨论。）。这里不讨论B-Tree的实现和算法，大家可以自己查询先关资料。我只想说明大家在这个地方不用担心，不在万不得已的情况下，基本上可以不考虑这个地方，我这里转载一个评测结果

http://blog.csdn.net/mynicedream/article/details/2252398

是和Berkeley DB 对比

这个评测的数据库表也很简单，是一个表上仅仅一个字段建立索引。所以说，如果发现你查询的性能很慢，请轻易不要怀疑sqlite的实现，请看看你写的sql语句和表的结构。一般都能找到问题所在。

Sql查询的优化是一个比较大的话题，这里不想具体展开，请参考

http://www.sqlite.org/optoverview.html

Sqlite架构

前面说道，sqlite麻雀虽小，五脏俱全。其实完全可以这样说。我第一次接触sqlite的时候，惊艳于他的架构设计。这么小的嵌入式数据库，基本上将大型数据库该有的模块和功能涵盖的比较全面。上一个真相

这里需要说明一下就是他的虚拟机，sqlite的虚拟机有自己的虚拟字节码，采取这种结构，可以说对于sql语句的调试、跟踪和优化都是很有好处的。这个也就是下面要说明的，使用sqlite的小技巧的前提

更多架构详细请见：http://sqlite.org/arch.html

使用技巧

采取绑定方式插入或者修改数据

         我们知道sqlite是的sql执行引擎是一个虚拟，他也有编译sql的过程，编译出来的字节码也是虚拟机执行的最终指令。如果每次采取 ：

char buf[128];

            sprintf(buf, "insert into emp (empname) values ('Empname%06d');", i+1);

            db.execDML(buf);

      这种方式来执行插入操作，那么每次都要重新编译一边sql语句，这个对于大量数据插入的时候还是有性能损耗的。所以最好采取bind方式来插入，这个时候，仅仅需要编译一次sql语句，执行效率也是会有提升的。

      这里说一点，就是sql编译都是多线程安全的，如果对一个数据库连接进行修改或者查询，在sqlite内部实现是首先进入关键区。这里不需要担心，但是对于并行性要求比较高的场景，预先编译是很划算的。

加密数据库文件

         在实际应用中，我们需要加密数据库文件，这个没有什么好说的。我们可以采取两种方案，一种是通过修改代码，提供一个sqllite_key 和 sqlite_rekey 函数，来做数据库加密。这个方案我个人不推荐，因为要修改数据库代码。

         个人推荐，提供一个VFS文件系统（虚拟文件系统），插入到sqlite中。Sqlite真正写文件的时候，是会调用系统注册的VFS来做真正的插入操作，其实我们只要将加密和解密函数放入到VFS的读写操作里面。这个也是经典的层模式，这里不再啰嗦。具体如何编写可以见相关文档 http://www.sqlite.org/vfs.html，这个是标准接口，完全可以满足我们的加密需求。

         因为是层模式，可以自由的调配层的顺序，可以搭配出不同的层结构。相对比较灵活。

异步写文件

         在有些场景下，比如记录日志到sqlite数据库中，一个或者多个线程生成日志，一个线程负责将数据insert到数据库中。在以前的版本中，我们一般都是采取手工写一个队列，然后写数据库线程不停的从队列里面取出数据。这个队列往往需要我们自己实现。

现在最新的sqlite，提供了异步写方式，其实也很简单，就是一个VFS系统，只不过你要使用这个功能，必要在打开数据库文件前调用一个 sqlite3async_initialize 函数，同时你也要提供一个线程，要调用 sqlite3async_run 函数来真正实现读写功能。具体可以见http://www.sqlite.org/asyncvfs.html

可能有人问了，这个跟我自己实现的队列有区别吗？都是队列，最后写入。其实区别很大。

1.         你在写程序的时候，不用自己关心多线程的问题，正常读写数据库就ok了

2.         这个VFS是在整个架构的最下层，实际上，写那个文件page，写那个部分，完全是数据库引擎自己说了算。如果采取自己实现的队列方案，每次在写数据的时候，必须完全同步到硬盘上，然后再执行下一步。没有真正发挥优化的功能。

3.         在select中，实际上是从磁盘和异步文件的写队列里面读取的。这个时候，不涉及硬盘操作，对于频繁插入的数据库，可以缓解一点磁盘IO。

说了这么多好处，坏处在那里？

1.         一致性有损失，如果断电了，在写队列里面的数据会丢失。

2.         多进程访问数据库时候，如果写队列比较大，其他读数据库的进程会有查询的延迟，不能实时响应修改。

当然，天下没有免费的午餐，具体的需求决定具体的设计。在使用的时候一定要注意。

重要的PRAGMA

         这个我不想多说，这个是sql语句，基本上sqlite的总控制开关。具体见文档

http://www.sqlite.org/pragma.html#syntax

你可以用他做到

l  控制使用内存大小

l  控制字符串编码方式

l  连接cache大小

l  文件同步方式

l  等等

压缩数据库时候要注意的问题

数据库文件大了，中间肯定有碎片（也许吧~）。我们就用 VACUUM 语句来压缩一下，这里需要注意一点，就是 VACUUM 命令有可能改变 INTEGER PRIMARY KEY 的数值，所以说在写程序的时候，对于 INTEGER PRIMARY KEY 不能有任何的假设，也千万不要认为他一定就是增加的。