Android(安卓)存储选项之 SQLite 优化那些事儿
Android 存储优化系列专题
- SharedPreferences 系列
《Android 之不要滥用 SharedPreferences》
《Android 之不要滥用 SharedPreferences（2）— 数据丢失》
- ContentProvider 系列（待更）
《Android 存储选项之 ContentProvider 启动过程源码分析》
《Android 存储选项之 ContentProvider 深入分析》
- 对象序列化系列
《Android 对象序列化之你不知道的 Serializable》
《Android 对象序列化之 Parcelable 深入分析》
《Android 对象序列化之追求完美的 Serial》
- 数据序列化系列（待更）
《Android 数据序列化之 JSON》
《Android 数据序列化之 Protocol Buffer 使用》
《Android 数据序列化之 Protocol Buffer 源码分析》
- SQLite 存储系列
《Android 存储选项之 SQLiteDatabase 创建过程源码分析》
《Android 存储选项之 SQLiteDatabase 源码分析》
《数据库连接池 SQLiteConnectionPool 源码分析》
《SQLiteDatabase 启用事务源码分析》
《SQLite 数据库 WAL 模式工作原理简介》
《SQLite 数据库锁机制与事务简介》
《SQLite 数据库优化那些事儿》
该篇文章属于 SQLite 存储系列的最后一篇，简单回顾下前面， Android 系统为支撑 SQLite 提供了 SQLiteDatabase 框架，它可以说是整个数据库框架最重要的一个类，内部维护了数据库连接池管理、并发访问、事务等核心管理。整体上看这套框架可以较高效的完成 SQLite 数据库的访问操作。不过它仍然存在一些注意和优化的地方，今天就来聊一聊 SQLite 优化相关内容。
关于 SQLite 的优化内容真的非常多，个人也在不断地学习和探索中，好在它有大量的资料供我们参考，遇到陌生或者不懂的地方还需要结合参考资料反复学习理解。今天我就选择一些相对比较重要的优化点整理出来供大家参考。
1. ORM
可能大部分应用为了提高开发效率，会引入 ORM 框架。ORM（Object Relational Mapping）也就是对象关系映射，用面向对象的概念把数据库中表和对象关联起来，可以让我们不用关心数据库底层的实现。
Android 中最常用的 ORM 框架有开源 greenDAO 和 Google 官方的 Room，那使用 ORM 框架会带来什么问题呢？
使用 ORM 框架真的非常简单，但是这种简单易用性是需要牺牲部分执行效率为代价的，具体的损耗跟 ORM 框架写的好不好很有关系。但可能更大的问题是让很多的开发者的思维固化，不但不能正确地写出高效的 SQL 语句，最后可能连简单的 SQL 语句都不会写了。
为了提高开发效率，应用的确应该引入 ORM 框架。但是这不能是我们不去学习数据库基础知识的理由，只有理解底层的一些机制，我们才能更加得心应手地解决疑难问题。
2. 进程与线程并发
如果我们在项目中使用 SQLite，那么在下面这个 SQLiteDatabaseLockedExecption 就是经常会出现的一个问题。
android.database.sqlite.SQLiteDatabaseLoekedException: database is locked at android.database.sqlite.SQLiteDatabase.dbopen at android.database.sqlite.SQLiteDatabase.openDatabase at android.database.sqlite.SQLiteDatabase.openDatabaseSQLiteDatabaseLockedException 归根到底是因为并发导致，而 SQLite 的并发有两个维度，一个是多进程并发，一个是多线程并发。
多进程并发
SQLite 默认是支持多进程并发操作的，它通过文件锁来控制多进程并发。SQLite 锁的粒度并没有非常细，它针对的是整个 DB 文件，内部有 5 个状态，具体你可以参考下面的文章。
- 官方文档：SQLite Locking
- SQLite 源码分析：SQLite 锁机制简介
- SQLite 封锁机制
简单来说，多进程可以同时获取 SHARED 锁来读取数据，但是只有一个进程可以获取 EXCLUSIVE 锁来写数据库。并且 EXCLUSIVE 会阻止其它进程再获取 SHARED 锁来读取数据。对于 iOS 来说可能没有多进程访问数据库的场景，可以把 locking_mode 的默认值改为 EXCLUSIVE。
PRAGMA locking_mode = EXCLUSIVE在 EXCLUSIVE 模式下，数据库连接在断开前都不会释放 SQLite 文件锁，从而避免不必要的冲突，提高数据库访问的速度。
多线程并发
相比多进程，多线程的数据库访问可能会更加常见。SQLite 支持多线程并发模式，需要开启下面的配置，当然系统 SQLite 会默认开启多线程 Multi-thread模式。
PRAGMA SQLITE_THREADSAFE = 2跟多进程的锁机制一样，为了实现简单，SQLite 锁的粒度都是数据库文件级别，并没有实现表级甚至行级的锁。还有需要说明的是，同一个句柄同一时间只有一个线程在操作，这个时候我们需要打开数据库连接池 Connection Pool。
跟多进程类似，多线程可以同时读取数据库数据，但是写数据库依然是互斥的。SQLite 提供了 Busy Retry 的方案，即发生阻塞时会触发 Busy Handler，此时可以让线程休眠一段时间后，重新尝试操作。
需要说明的是，首先 SQLite 的 Busy Retry 的方案虽然基本能解决问题，但对性能的压榨做的不够极致，可以参考 微信 iOS SQLite 源码优化实践。它的核心问题在 Retry 过程中，休眠时间的长短和重试次数，是决定性能和操作成功率的关键。不过在 Android 平台提供的 SQLiteConnectionPool 中通过休眠-唤醒的方式能够保证第一时间唤醒休眠中的线程，来提高数据库执行效率。可以参考前面的分析《Android 数据库之 SQLiteConnectionPool 源码分析》。
为了进一步提高并发性能，我们可以打开 WAL （Write-Ahead-Logging）模式。WAL 模式会将修改的数据单独写到一个 WAL 文件中，而读操作开始时，会记下当前的 WAL 文件状态，并且只访问在此之前的数据，同时也会引入 WAL 日志文件锁。通过 WAL 模式读和写也可以完全地并发执行，不会互相阻塞。
PRAGMA schema.journal_mode = WAL但是需要注意的是，写之间是仍然不能并发。如果出现多个写并发操作的情况，依然有可能出现 SQLiteDatabaseLockedException。这个时候我们可以让应用中捕获这个异常，然后等待一段时间再重试。
} catch (SQLiteDatabaseLockedException e) { if (sliteLockedExceptionTimes < (tryTimes - 1)) { try{ Thread.sleep(100); }catch(InterruptedException el){ } } sliteLockedExceptionTimes++;}这里还需要说明的是，Android 平台提供的数据连接池 SQLiteConnectionPool，由于其内部保证只有一个主连接，多个写操作通过等待-唤醒方式竞争该连接以获得数据库写操作，故在单进程情况下不会发生上述的 SQLiteDatabaseLockedException 异常，但是多进程情况下依然有可能发生。另外关于连接池大小设置建议使用 4，不过系统默认好像并没有提供设置连接池大小的接口，默认与 WAL 模式一起开启。
这里推荐在 2017 年微信开源了内部使用 SQLite 数据库 WCDB，由于 Android 系统版本的不同导致 SQLite 的实现也有所差异，经常会出现一些兼容性问题，所以 WCDB 单独引入了自己的 SQLite 版本。这样就有了”源码在手，天下我有“。例如 SQLiteDatabase 框架查询数据库使用的是 Cursor 接口，Cursor 的实现是分配一个固定 2MB 大小的缓冲区 Cursor Window，这在查询数据量较小时可能不一定划算；对于结果集大于 2MB 的情况，遍历途中还会引发 Cursor 重查询，这个消耗就相当大了，而且数据的获取中间要经历两次内存拷贝。 WCDB 就对此作了优化，你可以参考 Cursor 优化实现。
总的来说通过连接池与 WAL 模式，我们可以很大程度上提高 SQLite 的读写并发，大大减少由于并发导致的等待耗时，建议大家在应用中尝试开启。
掌握了 SQLite 数据库并发的机制，在某些时候我们可以更好地决策应该拆数据表还是拆数据库。新建一个数据库好处是可以隔离其它库并发或者损坏的情况，而坏处是数据库初始化耗时以及更多的内存占用。一般来说，单独的业务都会使用独立数据库。
3. 查询优化
说到数据库的查询优化，你第一个想到的肯定是建索引，那就先聊聊 SQLite 的索引优化。
（1） 索引优化
正确使用索引在大部分场景可以大大降低查询速度，下面是索引使用非常简单的例子，我们先从索引表找到数据对应的 rowid，然后再从原数据表直接通过 rowid 查询结果。
索引的使用关于 SQLite 索引的原理网上有很多文章，这里推荐一些参考资料
- SQLite 索引的原理
- 官方文档：Query Planning
- MySQL 索引背后的数据结构及算法原理
重点要说的是很多时候我们以为已经建立了索引，但事实上并没有真正生效。这里关键在于如何正确的建立索引。例如使用了 BETWEEN、LIKE、OR 这些操作符、使用表达式或者 case when 等。更详细的规则可以参考官方文档 The SQLite Query Optimizer Overview，下面是一个通过优化转换达到使用索引的例子。
BETWEEN:myfied1 索引无法生效SELECT * FROM mytable WHERE myfield BETWEEN 10 and 20;转换成:myfied1 索引可以生效SELECT * FROM mytable WHERE myfield >= 10 AND myfield <= 20;建立索引是有代价的，需要一直维护索引表的更新，比如对于一个很小的表来说就没有必要建索引；如果一个表经常是执行插入更新操作，那么也需要节制的建立索引。总的来说有几个原则：
建立正确的索引。这里不仅需要确保索引在查询中真正生效，我们还希望可以选择最高效的索引。如果一个表建立太多的索引，那么在查询的时候 SQLite 可能不会选择最好的来执行。
单列索引、多列索引与复合索引的选择。索引要综合数据表中不同的查询与排序语句一起考虑，如果查询结果集过大，还是希望可以通过符合索引直接在索引表返回查询结果。
索引字段的选择。整型类型索引效率会远高于字符串索引，而对于主键 SQLite 会默认帮我们建立索引，所以主键尽量不要使用复杂字段。
总的来说索引优化是 SQLite 优化中最简单同时也是最有效的，但是它并不是简单的建一个索引就可以了，有的时候我们需要进一步调整查询语句甚至是表的结构，这样才能达到最好的效果。
关于索引优化这里再补充说明下
- EXPLAIN QUERY PLAN
通过 EXPLAIN QUERY PLAN 指令我们可以轻松解决大部分明显 SQL 设计上的问题。（该指令是查看 SQLite 在执行 SQL 时所采用的计划，例如可以看到执行时所采用的 index，并且可以看到执行 SQL 过程前 SQLite 对整个查询所涉及的元数据条数的预估）。但是也有例外的情况是无法检测到的，EXPLAIN QUERY PLAN 无法检测到索引页的加载数量，以至于即便使用了索引，效率也会变得低下。
关于 SQLite 命令行的使用可以参考 Command Line Shell For SQLite。
- 索引字段的选择
上一条说到使用 EXPLAIN QUERY PLAN 检测看到实际已经采用了索引，看上去是没什么问题，但最后可能还是会出现很多耗时的查询操作。
其实在整个 SQLite 的查询过程中有两个比较大的瓶颈需要解决，一个是磁盘 I/O 的数量，另外一个是引擎的计算量，而引擎计算量与查询过程所需的用到 Page 的数量是成线性正比关系的，也就是说，要降低整个查询时常，必须先想办法降低整个查询过程中需要用到的 Page 数量。关于这部分你可以参考《微信ANDROID客户端-会话速度提升70%的背后》。
简单点说就是单条索引占用越大，用于存储索引的 Page 数量就越多，用于查询加载的 Page 量增加导致整个查询时间越长。不建议用大 String 作为索引列，这里在介绍下 SQLite 可变长整数：
可变长整数是 SQLite 的特色之一，使用它既可以处理大整数，又可以节省存储空间。由于单元中大量使用可变长整数。可变长整数由 1 ~ 9 个字节组成，每个字节的低 7 位有效，第 8 位是标志位。在组成可变长整数的各字节中，前面字节（整数的高位字节）的第 8 位置 1，只有最低一个字节的第 8 位置 0，表示整数结束。可变长可用于存储 rowid、字段的字节数或 BTree 单元中的数据。故实际每个 byte 能够表示的证书个数为 128（只有低 7 位可用）
（2）页大小与缓存大小
数据库就像一个小文件系统一样，事实上它内部也有页和缓存的概念。
对于 SQLite 的 DB 文件来说，页（page）是最小的存储单位，如下图所示每个表对应数据在整个 DB 文件中都是通过一个一个的页存储，属于同一个表不同的页以 B 树（B-tree）的方式组织索引，每一个表都是一棵 B 树。
page 查找过程跟文件系统的页缓存（Page Cache）一样，SQLite 会将读过的页缓存起来，用来加快下一次读取速度。页大小默认是 1024Byte，缓存大小默认是 1000 页。更多的编译参数你可以查看官方文档PRAGMA Statements。
PRAGMA page_size = 1024PRAGMA cache_size = 1000每个页永远只存放一个表或者一组索引的数据，即不可能同一个页存放多个表或索引的数据，表在整个 DB 文件的第一个页就是这棵 B 树的根页。继续已上图为例，如果想查询 rowID 为 N+2 的数据，我们首先要从 sqlite_master 查找出 table 的 root page 的位置，然后读取 root page、page4 这两个页，所以一共会需要 3 次 I/O。
| page size（Byte） | 插入 60000 行数据（ms） |
|---|---|
| 1024 | 3426 |
| 2048 | 2772 |
| 4096 | 2506 |
| 8192 | 2304 |
| 32768 | 2673 |
从上表可以看到，增大 page size 并不能不断地提升性能，在拐点以后可能还会有副作用。我们可以通过 PRAGMA 改变默认 page size 的大小，也可以在创建 DB 文件的时候进行设置。但是需要注意如果存在老的数据，需要 vacuum 对数据表对应的节点重新计算分配大小。这里建议大家在新建数据库的时候，就提前选择 4KB 作为默认的 page size 以获得更好的性能。
其实这个优化的原理就是让 page 存储更多的数据，从而减少 page 页的查找次数，也就是降低 I/O 次数。但是页过大（拐点位置）则会导致页内容过多而 I/O 变慢。
其他优化
关于 SQLite 的使用优化还有很多很多，下面再简单提几个点：
慎用“SELECT *”，需要使用多少列，就选取多少列。
正确使用事务。
预编译与参数绑定，缓存被编译后的 SQL 语句。
对于 BLOB 或超大的 Text 列，可能会超出一个页的大小，导致出现超大页。建议将这列单独拆表，或者放大表字段的后面。
定期整理或者清理无用或可删除的数据，例如删除数据库比较久远的数据，如果用户访问到这部分数据，重新从网络拉取即可。
在日常的开发中，我们都应该对这些知识有所了解，再来复习一下上面整理的 SQLite 优化方法，通过引进 ORM，可以大大的提升我们的开发效率。通过 WAL 模式和连接池，可以提高 SQLite 的并发性能。通过正确的建立索引，可以提升 SQLite 查询速度。通过调整默认的页大小和缓存大小，可以提升 SQLite 的整体性能。
SQLite 监控
正确使用索引，正确使用事务。对于大型项目来说，参与的开发人员可能有几十上百人，开发人员水平参差不齐，很难保证每个人都可以正确而高效的使用 SQLite，所以这时候需要建立完善的监控体系。
- 本地测试
作为一名靠谱的开发工程师，我们每写一条 SQL 语句，都应该先在本地测试。我们可以通过上面提到的 EXPLAIN QUERY PLAN 测试 SQL 语句的查询计划，是全表扫描还是使用了索引，以及具体使用了哪个索引等。
sqlite> EXPLAIN QUERY PLAN SELECT * FROM name WHERE age = 20 AND sex = '男';QUERY PALN| -- SEARCH TABLE t1 USING INDEX name-index (age=? AND sex=?)- 耗时监控
不过本地测试过于依赖开发人员的自觉性，所以很多时候我们需要建立线上大数据的监控。微信开源的 WCDB 集成了自己的 SQLite 源码，所以可以非常方便的增加自己想要的监控模块。它内部默认增加了 SQLiteTrace 的监控模块，有以下四个接口：
/** * 当某条 SQL 语句执行完毕 * * @param db database on which the statement was executed * @param sql statement executed * @param type type of the statement. See {@link com.tencent.wcdb.DatabaseUtils#getSqlStatementType} * @param time time spent on execution, in milliseconds */void onSQLExecuted(SQLiteDatabase db, String sql, int type, long time);/** * 当线程成功获得数据库连接时调用。 * * @param db database on which the connection was obtained * @param sql statement about to be executed * @param waitTime time spent on waiting for available connection, in milliseconds * @param isPrimary whether the primary connection (write connection) is obtained */void onConnectionObtained(SQLiteDatabase db, String sql, long waitTime, boolean isPrimary);/** * 当前出现连接池被其他执行语句阻塞时间过长 * * @param db database on which connection pool is blocked * @param sql statement to be executed * @param requests list of statement being executed * @param message message generated by the connection pool */void onConnectionPoolBusy(SQLiteDatabase db, String sqlWaiting, long waitTime, boolean wantPrimaryConnection, List> sqlRunning, List> longLastingActions);/** * 当出现数据库损坏 * * @param db the corrupted database */void onDatabaseCorrupted(SQLiteDatabase db); 我们可以通过这些接口监控数据库 busy、损耗以及执行耗时。针对耗时比较长的 SQL 语句，需要进一步检查是 SQL 语句写的不好，还是需要建立索引。
- 智能监控
跟随 WCDB 开源的还包括一个智能化分析 SQLite 语句的工具 Matrix SQLiteLint -- SQLite 使用质量检测。虽然名字带 “lint”，但它并不是静态代码检查，它在 APP 运行时进行检测，而且大部分检测算法与数据量无关，即不依赖线上的数据状态。只要你触发了某条 SQL 语句的执行，SQLiteLint 就会帮你 review 这条语句。它根据分析 SQL 语句的语法树，结合我们日常数据库使用的经验，抽象出索引使用不当、SELECT * 等六大问题。
SQLiteLint它内部通过收集 APP 运行时的 SQL 执行信息包括执行语句、创建的表信息等。如果使用 Android 默认的 DB 框架，SQLiteLint 提供了一种无侵入的获取执行 SQL 语句以及耗时等信息的方式。内部通过 hook 向 SQLite3 C 层注册回调。从而无需开发者额外的打点统计代码。
另外美团也开源了它们内部的 SQL 优化工具 SQLAdvisor，你可以参考这些资料：
- SQL 解析在美团中的应用
- 美团点评 SQL 优化工具 SQLAdvisor
总结
数据库存储应该是每一个开发人员掌握的基本功，比掌握更重要的是，清楚 SQLite 的底层机制对我们的工作会有很大的指导意义。SQLite 优化真的是一个很大的话题，可能我们还需要结合参考资料再进一步反复学习理解。另外推荐一些 SQLite 进阶学习资料，感兴趣的朋友可以继续深入学习。
- SQLite 官方文档
- SQLite 源码分析
- 全面解析 SQLite
以上便是个人在学习 SQLite 优化过程中的体会和总结，文中如有不妥或有更好的分析结果，还请大家指出。
文章如果对你有帮助，就请留个 赞 吧！
更多相关文章
- Android(安卓)数据库之 SQLiteConnectionPool 源码分析
- Android(安卓)数据库技术
- 【置顶】用Eclipse开发Android应用程序索引贴
- android 塔防游戏汇总 及android 游戏开发索引
- android 杂疑汇总
- Android(安卓)程式开发（原创）の目录索引
- android 数据库 备份还原
- Android——SQLite/数据库 相关知识总结贴
- Android(安卓)教程例子列表