数据库进阶

移动端数据库面试不是背 SQL,而是解释清楚 SQLite 在单文件、弱资源、强一致本地存储下如何工作。能把索引、事务、WAL、锁、Room 迁移和缓存一致性串起来,就能从“会用 Room”升级成“能治理本地数据层”。

一、SQLite 索引与查询优化

SQLite 常用 B+Tree 组织表和索引。Android 端最常见的优化目标是:列表页首屏快、搜索条件可命中索引、分页不扫全表、写入不要被过多索引拖慢。

主题	面试要点	Android/Room 关联
单列索引	加速 `where userId = ?`、`order by time`	`@Index("userId")`
联合索引	遵循最左前缀,适合多条件查询	`@Index(value=["uid","createdAt"])`
覆盖索引	查询列都在索引里,减少回表	列表摘要页只查必要字段
索引代价	占空间、写入/更新变慢	埋点/日志表不要给每个字段建索引

索引失效常见场景:

对索引列做函数或计算:where date(createdAt)=?。
like '%keyword' 前缀模糊无法利用普通 BTree 范围。
联合索引 (a,b,c) 跳过 a 只查 b。
隐式类型转换,如文本列传数字参数。

二、事务、WAL 与持久性

事务保证一组本地状态变更要么一起成功,要么一起回滚。移动端常见场景是“网络响应入库 + 更新本地缓存版本 + 删除旧分页游标”必须在同一事务内完成。

@Transaction
suspend fun replacePage(page: Int, items: List<ItemEntity>, nextKey: String?) {
    remoteKeyDao.upsert(RemoteKeyEntity(page, nextKey))
    itemDao.deletePage(page)
    itemDao.insertAll(items)
}

Rollback Journal:修改前先备份旧页,提交后删除 journal;读写互斥更明显。
WAL(Write-Ahead Logging):先写入 WAL 文件,读者可继续读旧快照,写者追加日志,读写并发更好。
Checkpoint:把 WAL 内容合并回主库;WAL 过大可能影响磁盘与启动恢复。
Room 实践:批量 insert/update 用事务包住,避免每条 SQL 都 fsync,显著降低耗时和卡顿风险。

三、锁、并发与 Android 线程模型

SQLite 是嵌入式数据库,不是多进程数据库服务器。它的锁粒度会影响“UI 查询、后台同步、埋点写入”之间的互相阻塞。

共享锁:多个读事务可并发读。
保留锁/待提交锁:写事务准备提交时逐步升级。
排它锁:真正写主库时需要排它访问。
WAL 模式下读写并发更好:读者看快照,写者追加 WAL,但同一时间通常仍只有一个 writer。
Android 约束:不要在主线程做大查询或大事务;Room 默认禁止主线程数据库访问是为了防 ANR。

面试回答可以强调:SQLite 适合本地轻量存储,但不适合把所有模块都当成高并发中心库;日志、缓存、业务状态最好按表职责拆清楚,写入队列化。

四、Explain Query Plan 排查慢查询

EXPLAIN QUERY PLAN 用来确认 SQL 是否走索引、是否全表扫描、是否临时排序。它不是“猜测优化”,而是用证据定位慢查询。

EXPLAIN QUERY PLAN
SELECT id, title, createdAt
FROM message
WHERE conversationId = ?
ORDER BY createdAt DESC
LIMIT 20;

-- 关注输出里是否出现:
-- SEARCH TABLE message USING INDEX index_message_conversation_createdAt
-- 避免: SCAN TABLE message 或 USE TEMP B-TREE FOR ORDER BY

Android 排查路径:

先用日志记录慢 SQL、参数、耗时和线程。
用 EXPLAIN QUERY PLAN 判断是否 SCAN TABLE。
对 where + order by 组合设计联合索引。
只查询 UI 需要的列,避免把大字段一次性读出。
回归验证首屏、翻页、搜索三个关键路径。

五、Room Migration 与数据演进

Room 迁移考的是“线上旧数据怎么安全升级”,不是只会改 entity。中级面试要说明 schema 版本、迁移 SQL、回滚策略和测试。

显式 Migration:从版本 N 到 N+1 写清 ALTER TABLE、建新表、搬数据、删旧表。
AutoMigration:适合简单加列/改名,复杂数据变换仍建议手写。
破坏性迁移风险:fallbackToDestructiveMigration() 会清库,只适合非核心缓存库。
迁移测试:用旧版本 schema 创建数据库,插入旧数据,跑 migration,再校验新 DAO 能正常读写。

val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE User ADD COLUMN riskLevel INTEGER NOT NULL DEFAULT 0")
        db.execSQL("CREATE INDEX IF NOT EXISTS index_User_riskLevel ON User(riskLevel)")
    }
}

六、N+1 查询、分页与缓存一致性

N+1 查询:先查 1 次列表,再对每个 item 查一次关联数据,列表 50 条就变成 51 次数据库访问。Android 列表滑动时会放大成卡顿。

用 JOIN、@Relation + @Transaction、批量 where id in (...) 解决。
对 RecyclerView/Compose 列表只暴露聚合后的 UI model,不要在 bind 阶段查库。
Room Flow 监听表变化时要避免过宽查询,否则任意字段变更都触发大列表重算。

分页策略:

Offset 分页:LIMIT 20 OFFSET 10000 越往后越慢,因为仍要跳过大量行。
Keyset/Cursor 分页:where createdAt < ? order by createdAt desc limit 20,适合消息/Feed。
Paging3 + RemoteMediator:网络页、本地 Room、RemoteKey 统一管理,离线也能展示。

缓存一致性:

单一可信源:UI 优先观察 Room,网络结果先入库再由数据库驱动 UI。
版本号/时间戳:解决本地缓存和服务端增量同步冲突。
事务更新:数据表和分页 key 同事务提交。
过期策略:TTL、etag、服务端版本号结合,不要永久相信本地缓存。

高频面试题

Q1:SQLite WAL 为什么能提升并发? WAL 把写入追加到日志文件,读事务继续读主库旧快照,因此读写不必像 rollback journal 那样频繁互斥。但通常仍只有一个 writer,且需要 checkpoint 把 WAL 合并回主库。

Q2:如何排查 Room 列表查询慢? 先记录 SQL 耗时和线程,再用 EXPLAIN QUERY PLAN 看是否全表扫描或临时排序;根据 where/order by 设计联合索引,只查必要列,最后用首屏和分页场景回归。

Q3:Room Migration 为什么不能随便 destructive migration? 线上用户的本地业务数据、离线缓存、登录态关联数据可能被清空。除非是可重建缓存库,否则要写显式 migration 并用旧 schema 数据测试。

Q4:N+1 查询在 Android 为什么危险? 它把列表渲染放大成大量小查询,在 RecyclerView/Compose 滑动和 Flow 重新计算时容易造成 IO 抖动和掉帧。应改成 JOIN、批量查询或一次性聚合。

易错点 / 追问

追问:联合索引 (uid, createdAt) 能否支持只按 createdAt 查?一般不能,因为不满足最左前缀。
易错:以为 WAL 下写入完全并行;实际多读一写更友好,但 writer 之间仍会竞争。
追问:Offset 分页为什么越翻越慢?数据库仍要扫描/跳过前面大量记录,移动端大表应优先考虑 keyset 分页。
易错:把 Room @Transaction 只理解成注解,忽略它对一致性和批量写性能的价值。

Keyboard shortcuts

Android 中级面试复习手册