高频访问SQLite数据库

sqlite 是一款开源的 SQL 数据库引擎，由于其自包含、无服务、零配置和友好的使用许可（完全免费）等特点，在桌面和移动平台被广泛使用。

在应用开发过程中，如果想保存点数据，自然而然地就会想到 SQLite，毕竟它拥有非常多的实践者。这里分享一个在项目开发过程中遇到的 SQLite 读写问题——在开发一个小型桌面应用系统时，需求是跟踪文件系统中的变更，同时对变更文件进行相关操作，我们毫不犹豫地采用了 SQLite 来存储文件变更信息。

在开发过程中，SQLite 的数据读写都非常顺利，没有什么障碍。然而，当业务逻辑一切就绪开始跑业务时，我们发现软件处理业务的性能很差，每秒钟只能处理 10 个左右的业务量，比数据放在内存的老系统还慢得多。老系统也还可以达到每秒三十几个业务，而现在只有三分之一的水平。在有几千几万个文件变更事件同时涌入的情况下，系统几近停滞，会出现几秒钟一个业务的荒凉场景。这是不能容忍的事情。

经过技术排查，我们发现对 SQLite 的读和写都非常慢，最差的情况是从数据库中获取一条记录要花掉 7 秒钟，十分离谱。于是我们收罗学习了各种 SQLite 的优化技术并应用到了系统之中：

• SQL 操作时采用事务机制

sqlite3_exec(db,"BEGIN TRANSACTioN;",0,0,0);
...
sqlite3_exec(db,"END TRANSACTION;",0,0,0);

• 批量操作时，使用sqlite3_prepare而不是sqlite3_exec

sqlite3_prepare_v2(db, zSQL, -1,&stmt, &pzTail);
sqlite3_step(stmt);
...

• 关闭数据库的磁盘同步写，降低数据安全性

sqlite3_exec(db,"PRAGMA synchronous = OFF; ",0,0,0); 

常见的优化技术都已使用，效果有但不太理想，还是没有达到老系统的性能，更不要说超过了。

这里需要回顾一下我们的应用模型。业务有并发处理的要求，系统中使用了多线程机制，这就出现了对 SQLite 并发多读多写的情况。我们查阅 SQLite 的官方文档，多写者的情况是不适用的。

https://www.sqlite.org/whentouse.html

至此，是不是说解决的出路就只有使用 client/server 这样的数据库了？小应用拖一个巨无霸数据库，有种头重脚轻的感觉。

记得数据库课程的学习中，有提到大型数据库访问的 多层模型（N-tier），目的就是更高效地处理数据。那我们的文件型数据库有没有可能拥有 N-tier 的思想？尽管与大型数据库的方法不一样，但目的是一致的。我们分析一下现有应用对 SQLite 的读写情况，先看图：

• 操作1
  收到文件系统中的变更信息，并写入到数据库。由于文件变更信息是逐条发生的，无法预估事件的开始和结束，来一条写一条的方式，导致开启SQLite的事务模式也没有啥效果。

• 操作2
  读取一条记录并进行业务操作，这里的读取并非只读，需要将该条记录标记为已选取，防止被其他业务处理线程读取而引发重复处理。因此，这一步也存在写操作。这里是读一条处理一条。

• 操作3
  业务处理完毕后，从数据库中删除。这里也是逐条删除。

回顾应用的业务操作方式后发现，这些操作都是写操作，而且还是逐条进行的。问题摆在这里，技术问题还是需要通过技术来解决。在优化的过程中，我们是分步骤进行的——

• 优化操作1
  采用延迟写的机制，收到文件变更信息后，不立即写入数据库，先放入缓存队列，等到达一定时间后再进行批量写入，这样在大量事件涌入时效果明显，大大减少了数据库的写操作次数。

• 优化操作2
  使用缓存；好不容易准备好数据库查询语句，只检索了一条，太浪费时机，将符合检索要求的记录缓存起来。同时将记录被选取的标记放在内存中而不写数据库，这样对数据库来说仅是读操作。

• 优化操作3
  同样采用延迟写，将收到的删除信息缓存起来，当累积到一定量或者时间后，再进行批量操作。这样就可以充分利用 SQLite 的事务功能，大大提升写操作的效率。

增加了这些数据库访问层后，数据库的读写性能提升明显，业务处理能力也达到了预期，超过了旧系统，主要的优化工作差不多就到此结束了。这里引入了延迟写和缓存机制，增加了程序的复杂度，带来的新挑战是如何保持缓存记录同数据库记录的一致性。为解决这个问题，使用了SQLite的自定义函数：

sqlite3_create_function(...);

通过创建自定义函数，来同步缓存记录和数据库记录。比如：在从数据库读取业务记录时，需要排除已经被标为"删除"的记录。

经历这个项目，我们让 SQLite 多读多写的并发访问也成为了可能，算是一个收获。（徐品华 | 天存信息）