Android 数据库系列三：复杂项目SQL治理与数据库的优化总结

站长

2024年04月12日 20:41 · 阅读数 4

一、背景

在我们的业务中需要大量使用数据库存储数据，在业务初期由于缺少开发经验、人力不足等原因在数据库相关逻辑的开发中，存在各种不足。后面为了提高用户体验，我们集中推进了数据库相关的优化。

本篇主要介绍在我们的业务中，我们是如何进行SQL语句相关优化的以及修复数据库使用过程带来的崩溃问题。业务中使用的数据库是微信开源的WCDB，对于SQLite读写性能等问题WCDB已经做了很好的优化。本次我们要做的是在WCDB基础上解决自身数据库业务待优化的问题。

本篇是数据库系列的第三篇，之前文章地址：

二、SQL治理与优化

一段时间以来，我们发现数据库所引发的崩溃占比非常高。尤其是在我们将其他模块引发的崩溃收敛之后，数据库引发的崩溃占比非常高，在每日崩溃数量60%以上，其中以数据库2048问题占比最高。

1、数据库2048问题

2048是我们内部针对这一类问题的简称，具体的崩溃堆栈如下：

com.tencent.wcdb.CursorWindowAllocationException: Cursor window allocation of 2048 kb failed. total:8159,active:49 
at com.tencent.wcdb.CursorWindow.(SourceFile:127)

2、关于 CursorWindowAllocationException异常

它通常发生在Android的SQLite数据库操作中，由于CursorWindow对象无法分配足够的内存控件导致的。

内存不足通常就是两个原因：

CursorWindow 存在内存泄露
短时间内大量的内存申请引发

CursorWindow 内存泄露

如果在代码中中使用CursorWindow 不调用其close()方法，那么就会引发内存泄露。但是在我们业务中不存在这个场景，原因是上层使用的Room框架，其为我们生成的代码中每次执行完检索语句后，都会调用close方法。

以工程中一个检索的语句为例：

@Override
public String getFriend(final String key) {
    final String _sql = "xxxxxxx";
    final RoomSQLiteQuery _statement = RoomSQLiteQuery.acquire(_sql, 1);
    int _argIndex = 1;
    if (key == null) {
        _statement.bindNull(_argIndex);
    } else {
        _statement.bindString(_argIndex, key);
    }
    __db.assertNotSuspendingTransaction();
    final Cursor _cursor = DBUtil.query(__db, _statement, false, null);
    try {
        final String _result;
        if(_cursor.moveToFirst()) {
            if (_cursor.isNull(0)) {
                _result = null;
            } else {
                _result = _cursor.getString(0);
            }
        } else {
            _result = null;
        }
        return _result;
    } finally {
        _cursor.close();
        _statement.release();
    }
}

这里主要是示例Room框架为我们生成的代码，SQL语句与业务相关被我删掉了。可以看到在最后finally中，框架已经添加了curson.close()的调用。

短时间内大量的内存申请引发

排除CursorWindow内存泄露，那么我们怀疑2048主要的原因应该就是短时间内大量的内存申请引发的。而解决这个问题通常要减少查询次数或者使用分页查询等逻辑。具体到我们的业务中，初期要如何优化这个问题，我们并没有什么头绪，直到看到WCDB的文章介绍了其SQL监控接口。

我们思考可以通过这个SQL监控接口统计的数据针对线上SQL进行治理。

3、引入SQL监控

public interface SQLiteTrace {

    /**
     * Called when a SQL statement finished execution.
     *
     * @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);

    /**
     * Called when database connection is successfully obtained by a thread.
     *
     * @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);

    /**
     * Called when connection pool is blocked by other statement execution
     * for a long time.
     *
     * @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<TraceInfo<String>> sqlRunning,
                              List<TraceInfo<StackTraceElement[]>> longLastingActions);

    /**
     * Called on database corruption detected. This is valid on default error handler only.
     * See {@link com.tencent.wcdb.DatabaseErrorHandler}
     *
     * @param db    the corrupted database
     */
    void onDatabaseCorrupted(SQLiteDatabase db);
}

onSQLExecuted 每次执行完一条SQL语句回调

db 数据库对象
sql 执行的SQL语句
type 语句类型
time 执行所花费的时间（ms）

onConnectionObtained 成功获取数据库连接

db 数据库对象
sql 即将执行SQL语句
waitTime 等待可用连接所花费的时间（ms）
isPrimary是否是主连接

onConnectionPoolBusy 当连接池被其他语句阻塞许久之后调用

db 数据库对象
sql 需要执行SQL语句
requests 正在执行的语句列表
message 连接池生成的信息

onDatabaseCorrupted 检测到数据库损坏时调用

基于以上四个方法，我们可以统计一段时间内SQL执行数量、每条SQL执行耗时、当连接池发生阻塞时及时上报。

4、SQL执行数量较多

基于SQL执行监控回调，我们每执行2000次SQL语句，会触发埋点数据上报。

每次上报的数据如下：

关键字	说明
total sql	进程启动到统计期间执行的SQL总数
wait time	触发上报的这次SQL执行获取到数据库连接（额外提一句，每个SQL执行都必须获取到数据库连接，之后才能通过连接执行相关SQL）的等待时间
max total	执行最多的10条SQL总的执行次数
sql cat count	目前为止执行的SQL语句个数，注意这个不是次数，例如一条SQL语句可能执行了100次，但它依然是同一条SQL，计数依然为1.
time diff	进程启动到目前为止运行了多少时间，不足1小时按秒输出，超过1小时按小时输出
thread-id	执行当前SQL的线程ID
name	执行当前线程的线程名
thread count	从进程启动到目前为止执行过SQL的线程总数
active	当前尚未被回收的CursorWindow的数量
max executed sql	执行最多的10条SQL，如果SQL语句超过50个字符则截断取其前50字符，不足50字符不做截断
SQL语句后面的数字	代表这条SQL语句执行的次数

有了上面的统计，我们发现一些账号中，首次登录3分钟之内会执行的SQL数量达到了80000次。

而通过业务梳理发现其中90%的SQL是没有必要执行的，可以直接删除。经过处理，同样的场景SQL执行数量锐减至8000次左右。

类似的业务场景，我们优化了多处，经过多个版本不断的优化，线上数据库2048崩溃已经不再出现。下面列举一些我们优化的场景：

在for循环中调用SQL语句
随着业务逻辑变更，一些字段已经不再使用，但获取该字段的SQL语句逻辑还在执行
在可以使用内存缓存的情况，没有直接使用内存缓存，调用数据库SQL语句执行查询逻辑等

5、SQL执行耗时

SQL执行耗时统计的来源主要是SQL耗时监控（onSQLExecuted()方法）以及少量用户日志。在我们的业务中SQL耗时主要是两个原因：

数据库中较多的表未加索引
索引没有命中，检索特别慢

关于索引

SQLite 索引的原理

正确使用索引在大部分的场景可以大大降低查询速度，索引优化是SQL优化中最简单同时也是最有效的的方式，所以针对SQL耗时优化，推荐从索引开始。但它并不是简单的建一个索引就可以了，有的时候我们需要进一步调整查询语句设置表的结构，这样才能达到最好的效果。

确保索引有效提升性能并避免不必要的开销，需要注意几点：

选择正确的列

为经常用于查询条件（如WHERE子句）、排序（如ORDER BY子句）和连接（如JOIN）的列创建索引。对具有高选择性的列（即字段中具有大量唯一值的列）创建索引通常更有效；

避免冗余索引

不要为经常更新的列或包含大量重复数据的列创建索引，这可能会降低写入性能且不会显著提高查询速度。

限制表上的索引数量

对一个存在大量更新操作的表，所建索引的数据一般不要超过3个，最多不要超过5个。索引太多会影响数据的更新操作。

索引字段的选择

整型类型索引效率远高于字符串索引，索引应该建在小字段上，对于大的文本字段不要建索引。

在我们的业务中，使用索引的主要是消息表。我们根据业务需求，在消息中创建了两条索引（由于这里涉及具体的业务就不罗列消息表的字段了），一条是单列索引，一条是联合索引。分别是匹配不同场景的检索需要，实践下来完全可以满足当前的业务场景需求。

基于此我们将线上SQL语句整体优化了一遍，确保检索语句都可以使用索引。我们优化的方式比较简单，就是通过开发者工具开发一个测试工具，开发者点击按钮就可以向某张表中插入百万条数据，插入完成之后再执行所有的检索语句查看耗时。针对耗时高的SQL语句进行优化。

目前线上SQL语句执行耗时都在正常范围值之内。用户体验得到了较大的提升。

6、数据库业务引发OOM问题

线上APP从某个版本开始出现了大量的OOM问题，既然从某个版本或者某个时间开始出现，那么说明不是偶然现象，必然是某个逻辑出发了该问题，且具有可排查性。

但排查过OOM的人都知道，触发OOM的位置大多数情况下是压死骆驼的最后一根稻草，例如你调用了某个对象的toString方法，字符串长度就50个字节却触发了OOM，你要把责任归罪到这个业务逻辑上，怕是光凭这一点还不行。

回到最开始说的，才开始出现说明是我们的某个业务触发了该问题，于是我们收集了线上OOM时候的堆栈数据，当收集到一定数量之后我们对堆栈数据进行了归纳整理，此时我们惊奇的发现 80 %的OOM是在本地搜索业务代码里触发的，且是消息搜索部分，于是我们开始构造测试场景开始复现。

由于之前搜索这里出现过内存泄露的问题，出现场景是：进入搜索然后退出，再次进入搜索，执行多次会发现内存一直增长，所以我们尝试通过这种方式复现，但很遗憾没有出现问题。

于是我们开始从线埋点统计的SQL执行数据入手，发现某些用户在进程启动之后10分钟左右就OOM了，此时我们有理由怀疑OOM并不是多次进入搜索触发的，肯定有别的场景，我们提取了有问题用户的日志发现该用户本地的消息量较大，这个时候我们调整了怀疑方向：认为消息量大的某个场景触发了OOM。

我们首先构造了一个拥有2000w条消息的数据库，然后进入搜索页面使用本地搜索触发消息搜索，测试过程中偶然发现退出搜索页之后内存一直在增长，这得益于我们自研的开发者工具（开发者工具开启后有一个悬浮窗，会实时展示当前内存占用总量），这立马引起了我们的注意，于是开始在这个场景测试，发现可以稳定复现。

我们知道从数据库里读数据出来，都是在CursorWindow中缓存几页数据，读取完cursor也会释放，而同一时间cursor也不会占用过多内存，因此对于内存一直增长我们一时无法理解，只好通过Profiler抓取内存数据，等到内存增长到一定程度我们开始dump出堆内存：

Android 数据库系列三：复杂项目SQL治理与数据库的优化总结

从结果可以看出有两个消息相关的对象居然产生了46w个。至此问题很明确了：页面退出之后本地的搜索任务没有停下来，一直从cursor中读取数据。

这有两个问题：

退出没有取消任务
读取数量应该做上限限制，否则无限制读取必然撑爆内存

搞清楚原因之后我们结合业务场景很顺利的解决了一直以来存在的OOM问题，修复版本上限之后仅看到过一次OOM案例，这次OOM是APP长期运行7天触发，这个属于内存泄露引发，我们在内存泄露环节进行了相关修复，从此之后的版本OOM发生的概率大幅度的降低

7、存储空间不足

在我们的业务中，存储空间不足主要是有两类的崩溃堆栈，分别是

第一个：

Caused by: com.tencent.wcdb.database.SQLiteFullException:database or disk is full (code 13,errno 0): 
at com.tencent.wcdb.database.SQLiteConnection.nativeExecute(Native Method) at 
com.tencent.wcdb.database.SQLiteConnection.execute(SourceFile:728) at 
com.tencent.wcdb.database.SQLiteSession.endTransactionUnchecked(SourceFile:436) at 
com.tencent.wcdb.database.SQLiteSession.endTransaction(SourceFile:400) at 
com.tencent.wcdb.database.SQLiteDatabase.endTransaction(SourceFile:533) at 
com.tencent.wcdb.room.db.WCDBDatabase.endTransaction(SourceFile:100)

这个异常堆栈本身提示的比较明显了，比较隐蔽的是第二类堆栈。

第二个：

com.tencent.wcdb.database.SQLiteDiskIOException: disk I/O error (code 4874, errno 28): while compiling: PRAGMA journal_mode at 
com.tencent.wcdb.database.SQLiteConnection.nativePrepareStatement(Native Method) at 
com.tencent.wcdb.database.SQLiteConnection.acquirePreparedStatement(SourceFile:1004) at 
com.tencent.wcdb.database.SQLiteConnection.executeForString(SourceFile:807) at 
com.tencent.wcdb.database.SQLiteConnection.setJournalMode(SourceFile:424) at 
com.tencent.wcdb.database.SQLiteConnection.setWalModeFromConfiguration(SourceFile:414) at 
com.tencent.wcdb.database.SQLiteConnection.open(SourceFile:289) at 
com.tencent.wcdb.database.SQLiteConnection.open(SourceFile:254) at 
com.tencent.wcdb.database.SQLiteConnectionPool.openConnectionLocked(SourceFile:603) at 
com.tencent.wcdb.database.SQLiteConnectionPool.open(SourceFile:225) at 
com.tencent.wcdb.database.SQLiteConnectionPool.open(SourceFile:217) at 
com.tencent.wcdb.database.SQLiteDatabase.openInner(SourceFile:1002) at 
com.tencent.wcdb.database.SQLiteDatabase.open(SourceFile:983) at 
com.tencent.wcdb.database.SQLiteDatabase.openDatabase(SourceFile:712) at 
com.tencent.wcdb.support.Context.openOrCreateDatabase(SourceFile:151)

第二个堆栈起初我们一直没有搞懂，直到ChatGPT横空出世，我们向ChatGPT提问，其提示：

【errno28标识No Space Left On Device，即存储空间不足。个错误通常会在执行SQLite操作时出现，因为SQLite需要足够的磁盘控件来执行某些操作，例如journal_mode。因此需要检查磁盘控件是否足够，并确保有足够的控件来执行所需的操作。】

基于ChatGPT提示，我们查看所有同类崩溃数据里APP的存储空间发现确实基本上没有存储空间了，有的可用控件甚至不足1MB。

针对用户手机存储空间不足，开发者能做除了控制自家APP所占用的空间不要太大之外，其他在技术上能做的有限。所以我们主动找到产品经理希望新增针对存储空间不足的交互逻辑，引导用户跳转到系统的清理APP中清理存储空间。

我们的策略：

存储空间剩余 >= 500MB 我们认为存储空间充足，不做任何提示
500MB > 存储空间剩余 >= 300MB 我们认为存储空间不足，每次进程启动提示一次
300MB > 存储空间剩余 >= 100MB 我们认为存储空间严重不足，每进入一个页面都会给用户提示
存储空间剩余 < 100MB 我们认为存储空间已经不足以支持使用APP，需要用户清理空间之后，将存储空间剩余至少大于100M才可以继续使用。

将这个逻辑上线后，线上存储空间不足的问题得到解决。

8、数据库损坏修复

关于数据库损坏，在我们工程中数据库损坏的堆栈其中一种如下：

com tencent wcdb database.SQLiteDatabaseCorruptException: database disk image is malformed (code 11, errno 0): at 
com.tencent.wcdb.database.SQLiteConnection.nativePrepareStatement(Native Method) at 
com.tencent.wcdb.database.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1004) at 
com,tencent.wcdb.database.SQLiteConnection.executeForString(SQLiteConnection.java:807) at 
com.tencent.wcdb.database.SQLiteConnection.setJournalMode(SQLiteConnection.java:424) at 
com.tencent.wcdb.database.SQLiteConnection.setWalModeFromConfiguration(SQLiteConnection.java:414) at 
com.tencent.wcdb.database.SQLiteConnection.open(SQLiteConnection.java:289) at 
com.tencent.wcdb.database.SQLiteConnection.open(SQLiteConnection.java:254) at 
com,tencent.wcdb.database.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:603) at 
com.tencent.wcdb.database.SQLiteConnectionPool.open(SQLiteConnectionPool.java:225) at 
com.tencent.wcdb.database.SQLiteConnectionPool.open(SQLiteConnectionPool.java:217) at 
com.tencent.wcdb.database.SQLiteDatabase.openInner(SQLiteDatabase.java:1002)

主要的信息是：database disk image is malformed (code 11, errno 0) 通常出现这条错误信息表明 SQLite 数据库文件已损坏，因此无法正确读取或写入。

数据库损坏原因SQLite官网(www.sqlite.org/howtocorrup…

文件错写
文件锁 bug
文件 sync 失败
设备损坏
内存覆盖
操作系统 bug
SQLite bug

在实际的生产过程中，数据库损坏导致的崩溃占比非常低，所以我们一直将其作为优先级低的任务放在优化任务列表中，但是某天线上突然出现了大量的数据库损坏上报，我们紧急联系用户了解情况。发现用户是将我们的APP安装到了一个没有电池的Android 一体机上。而他们会使用这个设备做业务测试，会经常直接将设备断电...突然断电就导致我们APP出现数据库损坏的概率变高。

针对数据库损坏，WCDB提供了两种解决方案：

提前备份，损坏之后通过备份进行恢复
基于损坏的DB文件恢复

Android数据库修复：github.com/Tencent/wcd…

方案选择：

由于我们的应用属于IM类，本地数据比较多。备份恢复方案会导致占用过多用户存储空间，且我们需要解决损坏之后的一系列异常，将对用户的影响降至最低，所以我们采用了损坏之后修复的方案。

使用Repair Kit进行修复的步骤，WCDB的WIKI中详细介绍，这里我就不赘述了。值得一提的是，我们会在崩溃容灾库中将数据库损坏的崩溃抓住，然后弹出弹框提示用户选择修复数据库。修复完成之后，会返回到登录页面，同时将本地支持增量同步的接口对应的时间戳全部清零，通信同步相关信息，防止修复时部分数据无法恢复导致输出缺失的情况。

9、连接池Busy问题

某天用户反馈进入会话页面白屏，加载不出来消息。最开始我们以为是之前优化搜索本地消息时，没有优化彻底导致的。但后面用户反馈一旦出现这个情况App便不会恢复了，一些业务表现都异常了，需要杀进程重新进入才行。查看用户日志发现我们添加的SQLite监控 onConnectionPoolBusy()方法一直在回调，这说明获取数据库连接的线程多于正在执行SQL的线程了，并且一定时间内没有连接被释放出来，导致其他线程只需等待空闲连接。

最开始面对这个问题我们是完全懵逼的，没有入手方向。难道WCDB存在BUG？组内喜欢翻源码的同学立即开始翻WCDB的源码，计划从源码的角度看看能够找到蛛丝马迹。虽然当时我担心这样会陷入到WCDB的源码中无法自拔，但是也确实没有其他解决的方向，所以也就期待同学可以找到什么惊天BUG，后面还可以给wcdb提个PR。不过事与愿违整理了WCDB 获取连接、打开连接、SQL执行、链接释放等部分代码后，依然没有头绪。（源码比较枯燥，这里就不贴当时我们总结的逻辑了，看完一遍过段时间也还是会忘，没啥意义）

问题解决

后面某一天我在开发者工具中查看APP内所有线程的堆栈时，发现某个db线程的堆栈一直不变。这就引起了我的注意，因为正常来讲，一条SQL语句执行耗时绝不应该这么久才对。如果是检索语句那么没有命中索引，这业务体验相当差了。如果db线程执行完成SQL任务之后会阻塞，阻塞后的堆栈通常如下：

sun.misc.Unsafe.park(Native Method)
java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:230) 
java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.awaitNanos(AbstractQueuedSynchronizer.java:210 )
java.util.concurrent.LinkedBlockingQueue.poll(LinkedBlockingQueue.java:467) 
java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1091) 
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1152) 
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641) 
java.lang.Thread.run(Thread.java:930)

立即查看那条DB线程的堆栈，发现其定位到了onConnectionObtained()方法，进入查看 onConnectionObtained() 内的逻辑发现其内部存在一个循环。经过检查发现在某些情况下，这个循环逻辑会变成死循环...那么破案了，导致连接池busy的原因是，在线程进入到 onConnectionObtained() 方法中，开始死循环，这个线程刚好持有了WCDB的连接，由于死循环所以无法释放连接。当所有持有连接的线程都在onConnectionObtained()方法中死循环时，新的DB线程希望获取连接执行SQL语句时就需要阻塞等待，一段时候之后WCDB开始回调 onConnectionPoolBusy() 方法，业务开始表现异常。

回调onConnectionObtained方法的具体代码逻辑见WCDB源码acquireConnection()方法：

public SQLiteConnection acquireConnection(String sql, int connectionFlags,
                                          CancellationSignal cancellationSignal) {
    long startTime = SystemClock.uptimeMillis();
    SQLiteConnection connection = waitForConnection(sql, connectionFlags, cancellationSignal);
    SQLiteTrace callback = mTraceCallback;
    if (callback != null) {
        long waitTime = SystemClock.uptimeMillis() - startTime;
        SQLiteDatabase db = mDB.get();
        if (db != null) {
            //CONNECTION_FLAG_PRIMARY_CONNECTION_AFFINITY是定义在SQLiteConnectionPool类内部的常量
            //定义如下：
            //public static final int CONNECTION_FLAG_PRIMARY_CONNECTION_AFFINITY = 1 << 1;
            final boolean isPrimary =
                    (connectionFlags & CONNECTION_FLAG_PRIMARY_CONNECTION_AFFINITY) != 0;
            callback.onConnectionObtained(db, sql, waitTime, isPrimary);
        }
    }
    return connection;
}

至此解决这个问题只需要将 onConnectionObtained() 方法中的死循环逻辑优化掉就可以了。

三、总结

本篇主要是介绍我们如何基于WCDB优化相关的业务逻辑。经过以上优化后，数据库相关的崩溃在APP中得到了控制，目前线上崩溃数据中，数据相关问题基本销声匿迹，崩溃率也得到了有效的控制。用户体验也得到了较大的提升。

值得一提的是，我的同事小红对本篇贡献较多，在此向他表示感谢。

转载自:https://juejin.cn/post/7338306790173704192