本文由云+社区发表

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

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

• 事后发现，负面影响已经发生。
• 关注的只是没这么差。eg. 监控阈值为 500ms ，那么一条可优化为 20ms 而平均耗时只有 490ms 的 sql 就被忽略了。

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

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

简述

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

检测流程十分简单：

\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 语句去用上索引的。举个例子：

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

这里看到已经是使用索引来 SEARCH TABLE ，避免了全表扫描。但值得注意的是并不是所有 like 的情况都可以这样优化，如 like '%lo' 或 like '%lo%' ，不等式就做不到了。

再看个位操作导致索引不生效的例子：

位操作是最常见的导致索引不生效的语句之一。但有些时候也是有些技巧的利用上索引的，假如这个 case 里 flag 的业务取值只有 0x1，0x2，0x4，0x8 ，那么这条语句就可以通过穷举值的方式等效：

以上看到，把位操作转成 in 穷举就能利用索引了。

解决这类索引未生效导致的全表扫描 的问题，需要结合实际业务好好优化sql语句，甚至使用一些比较trick的技巧。也有可能没办法优化，这时需要添加到白名单。

3. 不必要的临时建树排序（对应查询计划的 USE TEMP B-TREE... )。

比如sql语句中 order by 、distinct 、group by 等就有可能引起对结果集临时额外建树排序，当然很多情况都是可以通过建立恰当的索引去优化的。举个例子：

以上看到，即便id和mark都分别建立了索引，即便只需要一行结果，依然会引起重新建树排序（ USE TEMP B-TREE FOR ORDER BY ）。当然这个case非常简单，不过如果对 SQLite 的索引不熟悉或者开发时松懈了，确实很容易发生这样的问题。同样这个问题也很容易优化:

这样就避免了重新建树排序，这对于数据量大的表查询，优化效果是立竿见影的好。

解决这类问题，一般就是建立合适的索引。

4. 不足够的索引组合

这个主要指已经建立了索引，但索引组合的列并没有覆盖足够 where 子句的条件式中的列。SQLiteLint 检测出这种问题，建议先关注该 sql 语句是否有性能问题，再决定是否建立一个更长的索引。举个例子：

以上看到，确实是利用了索引 genderIndex 来查询，但看到where子句里还有一个 mark=60 的条件，所以还有一次遍历判断操作才能得到最终需要的结果集。尤其对于这个 case，gender 也就是性别，那么最多 3 种情况，这个时候单独的 gender 索引的优化效果的已经不明显了。而同样，优化也是很容易的：

解决这类问题，一般就是建立一个更大的组合索引。

5. 怎么降低误报

现在看到 SQLiteLint 主要根据查询计划的某些关键字去发现这些问题，但SQLite支持的查询语法是非常复杂的，而对应的查询计划也是无穷变化的。所以对查询计划自动且正确的分析，不是一件容易的事。SQLiteLint 很大的功夫也在这件事情上

所以对查询计划自动且正确的分析，不是一件容易的事。SQLiteLint 很大的功夫也在这件事情上。SQLiteLint 这