数据库进阶

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

一, SQLite 索引与查询优化

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

B+Tree 为什么够快 (推演, 数字为示意): 页大小常见 4KB (SQLite 编译期可选, 需为 2 的幂). 每个节点能放多少 key 由 key/子指针大小决定, 假设约 100~200 个 key/节点 (扇出示意); 百万行 (约 2^20) 只需要约 log_200(1e6) ≈ 2.6 层, 即 3 层 B+Tree: 根 + 一层中间节点 + 一层叶子, 一次点查约 3 次页 I/O. 相比 B-Tree, 非叶节点只存 key 不存数据, 扇出更高, 树更矮; 叶子节点按 key 逻辑有序, 天然支持范围查询 (SQLite 的叶页之间并无兄弟指针, 续扫依赖沿父页路径定位下一叶页). 相比哈希索引 (只支持等值), B+Tree 支持范围, 前缀匹配和排序, 这正好覆盖移动端最常见的 where + order by 场景.

常见不命中 (不是 “必然失效”): 索引是否被选中由 SQLite 版本, 统计信息, 绑定参数和数据分布共同决定; 不能只凭 SQL 外形下结论. 每次以同一 schema 和真实绑定参数运行 EXPLAIN QUERY PLAN, 并保留对照证据:

OR, 否定条件和低选择性条件也常不命中. 优化前后同时核对返回行正确性, 避免为了看到 SEARCH 改变查询语义.

二, 事务, 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 文件, 读者可继续读旧快照, 写者追加日志, 读写并发更好.
• 事务隔离级别 (审查点名补齐): SQLite 默认隔离级别是 SERIALIZABLE. PRAGMA read_uncommitted = ON 可请求 READ UNCOMMITTED (读未提交), 但该 pragma 在 WAL 模式下是 no-op (WAL 读者读各自快照, 不会看到未提交数据); 仅在 rollback journal 模式 (通常配合 shared-cache) 下才可能读出未提交数据. 在 WAL 下读者读快照, 写者追加 WAL, 读者与写者互不阻塞, 这是 WAL 提升并发的主要原因.
• Checkpoint: 把 WAL 内容合并回主库; WAL 过大可能影响磁盘与启动恢复.
• Room 实践: 批量 insert/update 用事务包住, 避免每条 SQL 都 fsync, 显著降低耗时和卡顿风险.
• PRAGMA synchronous 的持久性取舍: 默认通常是 FULL, 在关键提交点 fsync, 保证已提交事务在掉电后不丢 (对应 ACID 的 D). 性能敏感时降为 NORMAL: 在 WAL 模式下可能丢失最近一次提交, 但不会损坏数据库; 取舍本质是 fsync 频率与吞吐 / 卡顿的权衡, 具体默认值以目标 SQLite 编译选项为准.

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

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

1. Rollback journal 使用 SHARED, RESERVED, PENDING, EXCLUSIVE 锁状态; 写入提交需要排它阶段, 读写互斥更明显.
2. WAL 是快照读加 WAL writer 锁模型: 读者读各自 end mark, writer 追加 WAL; 通常仍只有一个 writer, 不应把 rollback 的 PENDING/EXCLUSIVE 图直接套用到 WAL.
3. Android 约束: 不要在主线程做大查询或大事务; Room 默认禁止主线程数据库访问是为了防 ANR.

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

database is locked / SQLITE_BUSY 的定位

SQLITE_BUSY 表示当前连接在可等待的时间内无法取得所需锁, 常见于长事务, 两个连接竞争 writer, 跨进程同时打开同一数据库或 checkpoint 受长期 reader 阻塞. 它不是 “加一个无限重试” 就能修复的问题.

读事务开始 ── shared snapshot ─────────────────── commit
写事务 A    ── acquire writer ─ write WAL ── commit
写事务 B    ── SQLITE_BUSY / 等待 busy_timeout ── acquire writer

该图是机制示意: WAL 允许读者读旧快照, 但一般仍只允许一个 writer. PRAGMA busy_timeout 设置的是当前物理 SQLite 连接的 busy handler, 可为短暂竞争留出等待时间; 它不是数据库文件级或 Room 全连接池级开关. 具体默认值和连接创建方式取决于 SQLite driver, Room 和应用配置, 不能把某个毫秒数当通用标准.

排查顺序:

1. 记录异常, 数据库版本, 事务开始 / 结束时间, 线程与写入队列长度, 禁止记录业务敏感字段.
2. 找出持锁最长的事务, 缩短其中网络, 文件 I/O 或大对象转换等非 SQL 工作.
3. 将同一库的业务写入串行化, 例如由 repository 单写入协程 / 队列提交短事务; 不要在多个进程各自 “重试到成功”.
4. 评估 WAL 与 checkpoint 行为, 并为长时间 Flow/cursor 读取设计及时释放和分页.
5. 在目标设备用并发读写压测复现, 验证 busy 计数, 写入延迟和数据不变量, 而非只验证 “不再抛异常”.

DEFERRED 读转写的 rollback-journal trace (机制示意): 连接 A BEGIN DEFERRED; SELECT ... 持有 SHARED; 连接 B BEGIN IMMEDIATE; UPDATE ... 持有 RESERVED, 并等待提交时取得 EXCLUSIVE; A 随后 UPDATE ... 需要升级到写锁, 但 B 已有 RESERVED, A 直接收到 SQLITE_BUSY. 此时 B 又要等 A 释放 SHARED 才能完成提交, 等待不会让双方都前进; busy_timeout 不能解决这种互相等待, 也不应把它当作重试策略. 修复是把需写的事务一开始就用 BEGIN IMMEDIATE 取得写意图, 缩短读写事务, 或让业务 writer 串行化. WAL 的旧快照写入冲突是另一套诊断路径, 不能把本 trace 的锁状态照搬过去.

以下为当前执行连接的验证 / 实验片段, 不是 Room 全池生产配置方案: 以项目所用 Room androidx.room:room-runtime 和 androidx.sqlite 的公开 API 为准. RoomDatabase.Callback.onOpen 提供的 SupportSQLiteDatabase 只能对该回调拿到的物理连接执行 PRAGMA busy_timeout, 不能保证初始化 Room 连接池中的每一条连接; 若所锁定的 Room/SQLite driver 版本没有可靠的 per-connection 配置 hook, 就不能将它作为全池配置方案. WAL 可在 builder 上用公开的 setJournalMode(RoomDatabase.JournalMode.WRITE_AHEAD_LOGGING) 选择. 生产治理仍以短事务和业务 writer 串行化为主; busy handler 仅是已确认连接入口上的有限等待策略, 必须按目标版本选择明确的连接配置入口并在设备上验证.

val callback = object : RoomDatabase.Callback() { // 仅验证本次回调拿到的连接
    override fun onOpen(db: SupportSQLiteDatabase) { db.execSQL("PRAGMA busy_timeout=2500") }
}
Room.databaseBuilder(context, AppDb::class.java, "app.db")
    .setJournalMode(RoomDatabase.JournalMode.WRITE_AHEAD_LOGGING)
    .addCallback(callback).build()

自测: (1) 两连接复现上述 DEFERRED trace, 预期 A 得 SQLITE_BUSY;(2) 在 callback 拿到的连接查询 PRAGMA busy_timeout, 预期为设置值, 但不得推论池内其他连接已设置;(3) 直接执行下一节的 SQL, 保存 EXPLAIN QUERY PLAN 输出并验证结果集.

四, Explain Query Plan 排查慢查询

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

可直接执行的索引计划自测

以下 SQL 可直接粘贴到 SQLite shell 或数据库检查工具执行; 它会重建 message 表. 建索引前无索引可用, 预期为 SCAN message; 建索引后左前缀等值命中, 预期为 SEARCH message USING COVERING INDEX .... ANALYZE 只影响多个候选计划间的行数估计, 不改变 “无索引 vs 单索引等值” 的选择, 因此本例不依赖 ANALYZE. SQLite 版本和代价模型可能让输出附带 COVERING 等字样, 但检查重点是 SEARCH/SCAN 与所列索引名.

DROP TABLE IF EXISTS message;
CREATE TABLE message (
  id INTEGER PRIMARY KEY,
  conversation_id INTEGER NOT NULL,
  created_at TEXT NOT NULL,
  title TEXT NOT NULL
);
WITH RECURSIVE n(i) AS (
  VALUES(1) UNION ALL SELECT i + 1 FROM n WHERE i < 1000
)
INSERT INTO message(id, conversation_id, created_at, title)
SELECT i, (i % 10) + 1, printf('2026-08-%02d', (i % 28) + 1),
       CASE WHEN i % 2 = 0 THEN printf('alpha-%04d', i) ELSE printf('beta-%04d', i) END
FROM n;

-- 尚无索引:预期 SCAN message,且可能有 USE TEMP B-TREE FOR ORDER BY.
EXPLAIN QUERY PLAN
SELECT id, created_at FROM message
WHERE conversation_id = 3 ORDER BY created_at DESC LIMIT 20;

CREATE INDEX i_message_conversation_created ON message(conversation_id, created_at DESC);
-- 左前缀命中:预期 SEARCH message USING COVERING INDEX i_message_conversation_created (conversation_id=?).
EXPLAIN QUERY PLAN
SELECT id, created_at FROM message
WHERE conversation_id = 3 ORDER BY created_at DESC LIMIT 20;

-- 跳过联合索引最左列:预期 SCAN message,而不是按 created_at 的直接 SEARCH.
EXPLAIN QUERY PLAN
SELECT id FROM message WHERE created_at = '2026-08-03';

-- 普通索引不能匹配函数表达式:预期 SCAN message.
EXPLAIN QUERY PLAN
SELECT id FROM message WHERE date(created_at) = '2026-08-03';
CREATE INDEX i_message_created_day ON message(date(created_at));
-- expression index 与查询表达式一致:预期 SEARCH message USING INDEX i_message_created_day (<expr>=?).
EXPLAIN QUERY PLAN
SELECT id FROM message WHERE date(created_at) = '2026-08-03';

CREATE INDEX i_message_title ON message(title);
PRAGMA case_sensitive_like = ON;
-- 前缀 LIKE 与 BINARY 索引一致:预期 SEARCH message USING COVERING INDEX i_message_title (title>? AND title<?).
EXPLAIN QUERY PLAN SELECT id FROM message WHERE title LIKE 'alpha%';
-- 前导通配符没有 B-tree 前缀范围:预期 SCAN message.
EXPLAIN QUERY PLAN SELECT id FROM message WHERE title LIKE '%0001';

该组 SQL 同时覆盖联合索引左前缀, expression index 和 LIKE/collation 条件. 若目标 SQLite 的输出与预期不同, 记录 sqlite_version(), PRAGMA compile_options, 是否已执行 ANALYZE, 完整 schema 和数据规模后再判断, 不要只因某次计划出现 SCAN 就改写业务语义.

EXPLAIN QUERY PLAN
SELECT id, title, created_at
FROM message
WHERE conversation_id = 3
ORDER BY created_at DESC
LIMIT 20;

-- 关注输出里是否出现:
-- SEARCH message USING INDEX i_message_conversation_created (conversation_id=?)
-- 避免: SCAN TABLE message 或 USE TEMP B-TREE FOR ORDER BY

线上慢→定位→建索引→验证的串联模板: 线上反馈列表慢, 先抓慢 SQL 与真实绑定参数; 跑 EXPLAIN QUERY PLAN 看是否 SCAN/回表 (查询列不全在索引里时会回表取行); 若命中回表或全表扫描, 按 where + order by 建联合索引或覆盖索引; 再用真实参数重跑 EXPLAIN 并以 SEARCH ... USING INDEX 和耗时下降作为证据回归, 而不是只看 SQL 外形.

Android 排查路径:

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

五, Room Migration 与数据演进

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

• 显式 Migration: 从版本 N 到 N+1 写清 ALTER TABLE, 建新表, 搬数据, 删旧表.
• AutoMigration: 可自动处理加列, 新建表, 改默认值等简单变更; 列 / 表重命名须在 AutoMigrationSpec 中显式声明, 复杂数据变换仍建议手写 migration.
• 破坏性迁移风险: 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)")
    }
}

数据库文件作为存储体系的一部分, 其备份, 清理与迁移责任属于存储选型范畴, 见 28 存储体系与 Scoped Storage.

六, 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 统一管理, 离线也能展示.

缓存一致性:

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

七, SQLite/Room 与 MySQL/InnoDB 的边界

本章 Android 实践默认是 SQLite/Room. MySQL/InnoDB 的聚簇索引, 隔离实现, 锁和服务端连接模型不能直接套到 SQLite. Android 慢查询应保存真实参数并使用 EXPLAIN QUERY PLAN, 关注是否全表扫描, 索引选择, 临时 B-tree 和返回行数; Room 升级必须用导出的 schema 做 migration test, 同时覆盖跨多个历史版本升级, 失败回滚和数据不变量.

高频面试题

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 只理解成注解, 忽略它对一致性和批量写性能的价值.