高频访问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 的读写情况,先看图:

高频访问SQLite数据库

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

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

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

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

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

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

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


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

sqlite3_create_function(...);

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

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

上一篇:SQLite学习之常用SQL语句(02)


下一篇:基于SSM框架的医疗物资生产与销售管理系统