如何做好SQLite 使用质量检测,让事故消灭在摇篮里

SQLite 在移动端开发中广泛使用,其使用质量直接影响到产品的体验。

常见的 SQLite 质量监控一般都是依赖上线后反馈的机制,比如耗时监控或者用户反馈。这种方式问题是:

事后发现,负面影响已经发生。

关注的只是没这么差。eg. 监控阈值为 500ms ,那么一条可优化为 20ms 而平均耗时只有 490ms 的 sql 就被忽略了。

能否在上线前就进行SQLite使用质量的监控?于是我们尝试开发了一个工具: SQLiteLint 。虽然名带 “lint ” ,但并不是代码的静态检查,而是在 APP 运行时对 sql 语句、执行序列、表信息等进行分析检测。而和 “lint” 有点类似的是:在开发阶段就介入,并运用一些最佳实践的规则来检测,从而发现潜在的、可疑的 SQLite 使用问题。

本文会介绍 SQLiteLint 的思路,也算是 SQLite 使用经验的分享,希望对大家有所帮助。

简述

SQLiteLint 在 APP 运行时进行检测,而且大部分检测算法与数据量无关即不依赖线上的数据状态。只要你触发了某条 sql 语句的执行,SQLiteLint 就会帮助你 review 这条语句是否写得有问题。而这在开发、测试或者灰度阶段就可以进行。

检测流程十分简单:

img

\1. 收集 APP 运行时的 sql 执行信息 包括执行语句、创建的表信息等。其中表相关信息可以通过 pragma 命令得到。对于执行语句,有两种情况: a)DB 框架提供了回调接口。比如微信使用的是 WCDB ,很容易就可以通过MMDataBase.setSQLiteTrace 注册回调拿到这些信息。 b) 若使用 Android 默认的 DB 框架,SQLiteLint 提供了一种无侵入的获取到执行的sql语句及耗时等信息的方式。通过hook的技巧,向 SQLite3 C 层的 api sqlite3_profile 方法注册回调,也能拿到分析所需的信息,从而无需开发者额外的打点统计代码。

\2. 预处理 包括生成对应的 sql 语法树,生成不带实参的 sql ,判断是否 select* 语句等,为后面的分析做准备。预处理和后面的算法调度都在一个单独的处理线程。

\3. 调度具体检测算法执行 checker 就是各种检测算法,也支持扩展。并且检测算法都是以 C++ 实现,方便支持多平台。而调度的时机包括:最近未分析 sql 语句调度,抽样调度,初始化调度,每条 sql 语句调度。

\4. 发布问题 上报问题或者弹框提示。

可以看到重点在第 3 步,下面具体讨论下 SQLiteLint 目前所关注的质量问题检测。

检测问题简介 一、检测索引使用问题

索引的使用问题是数据库最常见的问题,也是最直接影响性能的问题。SQLiteLint 的分析主要基于 SQLite3 的 "explain query plan" ,即 sql 的查询计划。先简单说下查询计划的最常见的几个关键字:

SCAN TABLE: 全表扫描,遍历数据表查找结果集,复杂度 O(n) SEARCH TABLE: 利用索引查找,一般除了 without rowid 表或覆盖索引等,会对索引树先一次 Binary Search 找到 rowid ,然后根据得到 rowid 去数据表做一次 Binary Search 得到目标结果集,复杂度为 O(logn) USE TEMP B-TREE: 对结果集临时建树排序,额外需要空间和时间。比如有 Order By 关键字,就有可能出现这样查询计划

通过分析查询计划,SQLiteLint 目前主要检查以下几个索引问题:

1. 未建索引导致的全表扫描(对应查询计划的 SCAN TABLE... )

虽然建立索引是最基本优化技巧,但实际开发中,很多同学因为意识不够或者需求太紧急,而疏漏了建立合适的索引,SQLiteLint 帮助提醒这种疏漏。问题虽小,解决也简单,但最普遍存在。 这里也顺带讨论下一般不适合建立索引的情况:写多读少以及表行数很小。但对于客户端而言,写多读少的表应该不常见。而表行数很小的情况,建索引是有可能导致查询更慢的(因为索引的载入需要的时间可能大过全表扫描了),但是这个差别是微乎其微的。所以这里认为一般情况下,客户端的查询还是尽量使用索引优化,如果确定预估表数量很小或者写多读少,也可以将这个表加到不检测的白名单。

解决这类问题,当然是建立对应的索引。

2. 索引未生效导致的全表扫描(对应查询计划的 SCAN TABLE... )

有些情况即便建立了索引,但依然可能不生效,而这种情况有时候是可以通过优化 sql 语句去用上索引的。举个例子:

img

以上看到,即便已建立了索引,但实际没有使用索引来查询。 如对于这个 case ,可以把 like 变成不等式的比较:

img

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/zyjyws.html