10、SQLite性能优化：索引原理与创建、EXPLAIN查询计划分析、批量插入优化（事务+编译）、查询缓存策略、数据库大小控制

说实话，SQLite 在移动端的表现，很多时候被低估了。

我见过不少项目，数据库就几十张表，数据量也不大，但用户一操作就卡顿。问题出在哪？不是 SQLite 不行，而是我们没把它「伺候好」。今天这一章，我就把压箱底的优化经验掏出来，跟你聊聊索引、查询计划、批量插入、缓存和数据库瘦身这些硬核话题。

10.1 索引原理与创建

索引这东西，说白了就是数据库的「目录」。你想想看，一本几百页的书，如果没有目录，你想找某个关键词，只能一页一页翻。有了目录，直接定位到页码，效率天差地别。

SQLite 默认用 B-Tree 结构来组织索引。每次插入、删除、更新数据时，索引树都要同步维护。所以索引不是越多越好——它是一把双刃剑。

什么时候该建索引？

• WHERE 子句中频繁使用的字段——比如按用户ID查询
• JOIN 操作的关联字段——比如外键
• ORDER BY 或 GROUP BY 的字段——避免文件排序
• 唯一性约束的字段——UNIQUE 约束会自动创建索引

什么时候不该建索引？

• 数据量很小（几百条以内），全表扫描比索引查找还快
• 字段值重复率极高，比如性别字段（只有男/女）
• 频繁写入的表，索引维护成本太高

我个人习惯是：先跑业务，再通过 EXPLAIN 分析慢查询，最后针对性建索引。不要一开始就拍脑袋建一堆索引。

-- 创建单列索引
CREATE INDEX idx_user_name ON users(name);

-- 创建复合索引（注意字段顺序）
CREATE INDEX idx_user_status_date ON users(status, created_at);

-- 唯一索引
CREATE UNIQUE INDEX idx_user_email ON users(email);

10.2 EXPLAIN 查询计划分析

你写的 SQL 到底怎么执行的？SQLite 不会骗你，它会告诉你查询计划。用 EXPLAIN QUERY PLAN 就能看到。

我曾经接手过一个项目，用户反馈列表加载越来越慢。我一看 SQL，发现没有索引，每次都是全表扫描。加上索引后，查询时间从 800ms 降到了 5ms。嗯，这就是 EXPLAIN 的价值。

-- 查看查询计划
EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = '张三';

-- 输出示例
-- |--SEARCH TABLE users USING INDEX idx_user_name (name=?)

常见的查询计划类型：

计划类型	含义	优化建议
SCAN TABLE	全表扫描	考虑加索引
SEARCH TABLE USING INDEX	走索引查找	良好，检查是否覆盖所有查询条件
SEARCH TABLE USING COVERING INDEX	覆盖索引（无需回表）	最优，数据直接从索引获取
USE TEMP B-TREE FOR ORDER BY	排序用了临时表	考虑建复合索引包含排序字段

10.3 批量插入优化（事务 + 编译）

批量插入是性能杀手。我见过有人循环 1000 次 insert，每次单独提交，耗时十几秒。优化后不到 100ms。差距在哪？两个关键点：事务和预编译。

事务的作用

默认情况下，每条 INSERT 语句都是一个独立事务。每次提交都要写磁盘、同步日志。把多条插入包在一个事务里，只提交一次，性能提升几十倍。

db.beginTransaction();
try {
    for (User user : userList) {
        db.execSQL("INSERT INTO users(name, age) VALUES(?, ?)", 
                   new Object[]{user.name, user.age});
    }
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}

预编译（Prepared Statement）

每次执行 SQL，SQLite 都要解析、编译、优化。预编译只做一次，后面重复执行，省掉了解析开销。

String sql = "INSERT INTO users(name, age) VALUES(?, ?)";
SQLiteStatement stmt = db.compileStatement(sql);

db.beginTransaction();
try {
    for (User user : userList) {
        stmt.bindString(1, user.name);
        stmt.bindLong(2, user.age);
        stmt.executeInsert();
        stmt.clearBindings();
    }
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
    stmt.close();
}

10.4 查询缓存策略

SQLite 本身有页面缓存（默认 2MB），但那是底层机制。应用层我们也可以做缓存，减少重复查询。

我常用的策略是「内存缓存 + 失效标记」。比如用户列表，第一次查询后缓存到 LruCache 里，当数据变更时清空缓存。

public class UserCache {
    private static final int MAX_SIZE = 100;
    private LruCache<String, List<User>> cache = new LruCache<>(MAX_SIZE);
    
    public List<User> getUsers(String key) {
        return cache.get(key);
    }
    
    public void putUsers(String key, List<User> users) {
        cache.put(key, users);
    }
    
    public void invalidate() {
        cache.evictAll();
    }
}

10.5 数据库大小控制

数据库文件越来越大，查询自然越来越慢。控制数据库大小，是长期维护的必修课。

定期清理无用数据

-- 删除过期日志
DELETE FROM logs WHERE created_at < date('now', '-30 days');

-- 注意：DELETE 不会释放磁盘空间，只是标记为可重用
-- 需要执行 VACUUM 才能真正回收空间
VACUUM;

VACUUM 的原理

VACUUM 会重建整个数据库文件，把空闲空间释放掉。但注意，它需要两倍磁盘空间来执行。我建议在应用空闲时（比如 App 进入后台）执行。

使用 auto_vacuum

SQLite 支持自动回收空间，但默认是关闭的。

-- 开启自动回收（需要在建库前设置）
PRAGMA auto_vacuum = 1;  -- 0=关闭, 1=完整, 2=增量

-- 查看当前设置
PRAGMA auto_vacuum;

auto_vacuum = 1 时，每次删除数据都会触发页面回收，但会有性能开销。我一般用默认的关闭状态，然后定期手动 VACUUM。

数据库文件大小监控

File dbFile = new File(db.getPath());
long sizeInMB = dbFile.length() / (1024 * 1024);
Log.d("DB_SIZE", "数据库大小: " + sizeInMB + " MB");

我习惯在 App 启动时检查数据库大小，如果超过 100MB 就触发一次 VACUUM 或清理任务。