高频访问SQLite数据库
高频访问SQLite数据库
高频访问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](https://www.sqlite.org/whentouse.html)**
至此，是不是说解决的出路就只有使用 client/server 这样的数据库了？小应用拖一个巨无霸数据库，有种头重脚轻的感觉。
记得数据库课程的学习中，有提到大型数据库访问的 **多层模型（N-tier）**，目的就是更高效地处理数据。那我们的文件型数据库有没有可能拥有 **N-tier** 的思想？尽管与大型数据库的方法不一样，但目的是一致的。我们分析一下现有应用对 SQLite 的读写情况，先看图：
<img src="https://img-blog.csdnimg.cn/20190527170629159.png#pic_center" alt="pic">
- 操作1 收到文件系统中的变更信息，并写入到数据库。由于文件变更信息是逐条发生的，无法预估事件的开始和结束，来一条写一条的方式，导致开启SQLite的事务模式也没有啥效果。 - 操作2 读取一条记录并进行业务操作，这里的读取并非只读，需要将该条记录标记为已选取，防止被其他业务处理线程读取而引发重复处理。因此，这一步也存在写操作。这里是读一条处理一条。 - 操作3 业务处理完毕后，从数据库中删除。这里也是逐条删除。
回顾应用的业务操作方式后发现，这些操作都是写操作，而且还是逐条进行的。问题摆在这里，技术问题还是需要通过技术来解决。在优化的过程中，我们是分步骤进行的——
>
- **优化操作1** 使用缓存；好不容易准备好数据库查询语句，只检索了一条，太浪费时机，将符合检索要求的记录缓存起来。同时将记录被选取的标记放在内存中而不写数据库，这样对数据库来说仅是读操作。 - **优化操作3**<br> 同样采用延迟写，将收到的删除信息缓存起来，当累积到一定量或者时间后，再进行批量操作。这样就可以充分利用 SQLite 的事务功能，大大提升写操作的效率。
增加了这些数据库访问层后，数据库的读写性能提升明显，业务处理能力也达到了预期，超过了旧系统，主要的优化工作差不多就到此结束了。这里引入了延迟写和缓存机制，增加了程序的复杂度，带来的新挑战是如何保持缓存记录同数据库记录的一致性。为解决这个问题，使用了SQLite的自定义函数：sqlite3_create_function(...);
```
通过创建自定义函数，来同步缓存记录和数据库记录。比如：在从数据库读取业务记录时，需要排除已经被标为"删除"的记录。
经历这个项目，我们让 SQLite 多读多写的并发访问也成为了可能，算是一个收获。
