likes
comments
collection
share

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

作者站长头像
站长
· 阅读数 3

一、背景

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

本篇主要介绍在我们的业务中,我们是如何进行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
评论
请登录