你的位置:首页 > 软件开发 > 数据库 > 【腾讯Bugly干货分享】移动客户端中高效使用SQLite

【腾讯Bugly干货分享】移动客户端中高效使用SQLite

发布时间:2016-08-19 16:00:04
本文来自于腾讯bugly开发者社区,非经作者同意,请勿转载,原文地址:http://dev.qq.com/topic/57b57f2a0703f7d31b9a3932作者:赵丰导语iOS 程序能从网络获取数据。少量的 KV 类型数据可以直接写文件保存在 Disk 上,App 内部 ...

本文来自于腾讯bugly开发者社区,非经作者同意,请勿转载,原文地址:http://dev.qq.com/topic/57b57f2a0703f7d31b9a3932

作者:赵丰

导语

iOS 程序能从网络获取数据。少量的 KV 类型数据可以直接写文件保存在 Disk 上,App 内部通过读写接口获取数据。稍微复杂一点的数据类型,也可以将数据格式化成 JSON 或

这篇文章主要从 SQLite 数据库的使用入手,介绍如何合理、高效、便捷的将这个桌面数据库和 App 全面结合。避免 App 开发过程中可能遇到的坑,也提供一些在开发过程中通过大量实践和数据对比后总结出的一些参数设置。整篇文章将以一个个具体的技术点作为讲解单元,从 SQLite 数据库生命周期起始讲解到其终结。希望无论是从微观还是从宏观都能给工程师以帮助。

【腾讯Bugly干货分享】移动客户端中高效使用SQLite

一、SQLite 初始化

在写提纲的时候发现,原来 SQLite 初始化竟然是技术点一点也不少。

1. 设置合理的 page_sizecache_size

【腾讯Bugly干货分享】移动客户端中高效使用SQLite

网上有很多的文章提到了,在内存允许的情况下增加 page_size 和 cache_size 能够获得更快的查询速度。但过大的 page_size 也会造成 B-Tree 查询退化到二分查找、CPU 占用增加以及 OS 级 cache 命中率的下降的问题。

通过反复比较测试不同组合的 page_size、cache_size、table_size、存储的数据类型以及各种可能的增删查改比例,我们发现后三者都是引起 page_size 和 cache_size 性能波动的因素。也就是说对于不同的数据库并不存在普遍适用的 page_size 和 cache_size 能一劳永逸的帮我们解决问题。

并且在对比测试中我们发现 page_size 的选取往往会出现一个拐点。拐点以前随着 page_size 增加各种性能指标都会持续改善。但一旦过了拐点,性能将没有明显的改变,各个指标将围绕拐点时的数据值小范围波动。

那么如何选取合适的 page_size 和 cache_size 呢?

上一点我们已经提到了可能影响到 page_size 和 cache_size 最优值选取的三个因素:

  1. table_size
  2. 存储的数据类型
  3. 增删查改比例

我们简单的分析一下看看为什么这三个变量会共同作用于 page_size 和 cache_size。

SQLite 数据库把其所存储的数据以 page 为最小单位进行存储。cache_size 的含义为当进行查询操作时,用多少个 page 来缓存查询结果,加快后续查询相同索引时方便从缓存中寻找结果的速度。

了解了两者的含义,我们可以发现。SQLite 存储等长的 int int64 BOOL 等数据时,page 可以优化对齐地址存储更多的数据。而在存储变长的 varchar blob 等数据时,一则 page 因为数据变长的影响无法提前计算存储地址,二则变长的数据往往会造成 page 空洞,空间利用率也有下降。

下表是设置不同的 page_size 和 cache_size 时,数据库操作中最耗时的增查改三种操作分别与不同数据类型,表列数不同的表之间共同作用的一组测试数据。

其中各列数据含义如下,时间单位为毫秒

【腾讯Bugly干货分享】移动客户端中高效使用SQLite

【腾讯Bugly干货分享】移动客户端中高效使用SQLite

从上表我们看到,放大 page_size 和 cache_size 并不能不断的获得性能的提升,在拐点以后提升带来的优化不明显甚至是副作用了。这一点甚至体现到了数据库大小这方面。从 G 列可以看到,page_size 的增加对于数据库查询的优化明显优于插入操作的优化。从05、06行可以发现,增加 cache_size 对于数据库性能提升并不明显。从 J 列可以看到,当插入操作的数据量比较小的时候,反而是小的 page_size 和 cache_size 更有优势。但 App DB 耗时更多的体现在大量数据增删查改时的性能,所以选取合适的、稍微大点的 page_size 是合理的。

所以通过表格分析以后,我们倾向于选择 DB 线程总耗时以及线程内部耗时最多的三个方法,作为衡量 page_size 优劣的参考标准。

page_size 有两种设置方法。一是在创建 DB 的时候进行设置。二是在初始化时设置新的 page_size 后,需要调用 vacuum 对数据表对应的节点重新计算分配大小。这里可参考 pragma_page_size 官方文档

https://www.sqlite.org/pragma.html#pragma_page_size

2. 通过 timer 控制数据库事务定时提交

Transaction 是任何一个数据库中最核心的功能,但其对 Server 端和客户端的意义却不尽相同。对 Server 而言,一个 Transaction 是主备容灾分片的最小单位(当然还有其他意义)。对客户端而言,一个 Transaction 能够大大的提升其内部的增删查改操作的速度。SQLite 官方文档以及工程实测的数据都显示,事务的引入能提升性能 两个数量级 以上。

实现方案其实非常简单。程序初始化完毕以后,启动一个事务,并创建一个 repeated 的 Timer

【腾讯Bugly干货分享】移动客户端中高效使用SQLite

在 Timer 的回调函数 RenewTransaction 中,提交事务,并新启动一个事务

【腾讯Bugly干货分享】移动客户端中高效使用SQLite

这样就能实现自动化的事务管理,将优化的实现黑盒化。逻辑使用方能将更多精力集中在逻辑实现方面,不用关心性能优化、数据丢失方面的问题。

从手动事务管理到自动事务管理会引发一个问题:

当两份数据必须拥有相同的生命周期,同时写入 DB、同时从 DB 删除、同时被修改时,通过时间作为提交事务的唯一标准,就有可能引发两份数据的操作进入了不同的事务。而第二个事务如果不能正确的提交,就会造成数据丢失或错误。

解决这个问题,可以利用 SQLite 的事务嵌套功能,设计一组开启事务和关闭提交事务的接口,供逻辑使用者按照其需求调用事务的开始、提交和关闭。让内层事务保证两(多)份数据的完整性。

3. 缓存被编译后的 SQL 语句

和其他很多编程语言一样,数据库使用的 SQL 语句也需要经过编译后才能被执行使用。SQL 语句的编译结果如果能够被缓存下来,第二次及以后再被使用时就能直接利用缓存结果,大大减少整个操作的执行时间。与此同理的还有 Java 数学库优化,通过把极其复杂的 Java 数学库实现翻译成 byte code,在调用处直接执行机器码,能大大优化 Java 数学库的执行速度和 C++ 持平甚至优于其。而对 SQLite 而言,一次 compile 的时间根据语句复杂程度从几毫秒到十几毫秒不等,对于批量操作性能优化是极其明显的。

【腾讯Bugly干货分享】移动客户端中高效使用SQLite

其实在上面的第2点中,已经是用一个专门的类将编译结果保存下来。每次根据文件名称和行号为索引,获得对应位置的 SQL 语句编译结果。为了便于大家理解,我在注释中也将 SQLIite 内部最底层的方法写出来供大家参考和对比性能数据。

4. 数据库完整性校验

移动客户端中的数据库运行环境要远复杂于桌面平台和服务器。掉电、后台被挂起、进程被 kill、磁盘空间不足等原因都有可能造成数据库的损坏。SQLite 提供了检查数据库完整性的命令

 PRAGMA integrity_check

该 SQL 语句的执行结果如果不为 OK ,则意味着数据库损坏。程序可以通过 ROLLBACK 到一个稍老的版本等方法来解决数据库损坏带来的不稳定性。

5. 数据库升级逻辑

代码管理可以用 git、svn,数据库如果要做升级逻辑相对来说会复杂很多。好在我们可以利用 SQLite,在内部用一张 meta 表专门用于记录数据库的当前版本号、最低兼容版本号等信息。用好了这张表,我们就可以对数据库是否需要升级、升级的路径进行规范。

原标题:【腾讯Bugly干货分享】移动客户端中高效使用SQLite

关键词:sql

sql
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。