第三章 索引设计与分析

索引是数据库性能调优的核心手段之一，合理设计索引可以将查询耗时从全表扫描的数秒级降至毫秒级。本章从索引类型、命中规则、分析工具、SQL 操作到 Room 注解实践，完整覆盖索引设计与分析的全链路。

核心观点：索引不是越多越好，而是越“精确”越好。每张表的索引应针对高频查询的 WHERE 条件、JOIN 字段和 ORDER BY 字段设计，并利用 EXPLAIN QUERY PLAN 验证索引是否被有效使用。

索引类型与命中规则框架图 索引类型与命中规则框架 单列索引 对单个字段排序存储 复合索引（多列） 按最左前缀原则匹配 唯一索引 保证键值唯一性 全值匹配 → 最高效 WHERE 条件与索引列完全一致 最左前缀 → 复合索引核心 必须从索引最左列开始匹配 范围查询 → 后续列失效 > < BETWEEN 会使右边列无法命中 EXPLAIN QUERY PLAN 分析 查看 SEARCH TABLE 是否走索引 / 扫描行数 Room 索引注解方式 @Entity(indices = @Index(value = "column", unique = false)) 💡 索引设计 = 分析查询模式 → 选择索引类型 → 验证执行计划 → 迭代优化

3.1 索引类型详解

Room 底层基于 SQLite，支持的索引类型包括三种：单列索引、复合索引与唯一索引。它们各自适用于不同的查询模式，设计错误会导致索引无效甚至拖慢写入。

索引类型 存储结构 适用场景 典型开销
单列索引 对单个字段建立 B-Tree 单一条件过滤、排序 写入 +1，存储较小
复合索引 按多列顺序组合建树 多条件查询、覆盖索引 写入 +N，存储较大
唯一索引 B-Tree + 唯一约束 业务唯一性（如 userId + type） 写入 +1 且需判断重复

设计经验：单列索引适合高基数字段（如 id、phone）；复合索引适合多字段组合查询，列顺序按选择性从高到低排列；唯一索引不仅加速查询，还能替代应用层重复校验。

3.2 索引命中规则（最左前缀与失效场景）

复合索引遵循最左前缀原则：查询条件必须从索引的最左列开始连续匹配，跳过中间列或使用右侧列会导致索引部分或完全失效。

-- 假设索引 (a, b, c)
-- ✅ 命中：WHERE a=1
-- ✅ 命中：WHERE a=1 AND b=2
-- ✅ 命中：WHERE a=1 AND b=2 AND c=3
-- ✅ 部分命中：WHERE a=1 AND c=3  (只用到 a)
-- ❌ 失效：WHERE b=2            (缺少 a)
-- ❌ 失效：WHERE c=3            (缺少 a,b)
-- ❌ 失效：WHERE a=1 AND b>10 AND c=3 (b 范围导致 c 失效)
查询条件 索引 (a,b,c) 命中情况 扫描行数
WHERE a=1 完全匹配 a 少量行
WHERE a=1 AND b=2 完全匹配 a,b 极少行
WHERE a=1 AND c=3 仅匹配 a 中等
WHERE b=2 全表扫描 全部

常见错误：很多开发者对 (userId, createTime) 建立复合索引，却频繁使用 WHERE createTime > ? 查询——未带 userId 导致索引完全失效，触发全表扫描。务必让最左列出现在 WHERE 中。

3.3 使用 EXPLAIN QUERY PLAN 分析索引

SQLite 提供 EXPLAIN QUERY PLAN 命令，可以查看查询执行计划中是否使用了索引、扫描行数以及搜索方式。

-- 分析一个带索引的查询
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE age > 25 AND city = '北京';

-- 输出示例：
-- SEARCH TABLE users USING INDEX idx_age_city (age>? AND city=?)  (~25 rows)

-- 分析一个无索引的查询（观察差异）
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE name = '张三';

-- 输出示例：
-- SCAN TABLE users  (~10000 rows)   ← 全表扫描，无索引

关键关键字含义：

  • SEARCH TABLE … USING INDEX → 成功使用索引，后面列出索引名及匹配条件
  • SCAN TABLE → 全表扫描，无可用索引或索引未被命中
  • ~N rows → 预估扫描行数，行数越少性能越好
  • USING COVERING INDEX → 覆盖索引，无需回表查询，性能最优
-- 检查覆盖索引
EXPLAIN QUERY PLAN
SELECT age, city FROM users WHERE age > 25;

-- 可能输出：
-- SEARCH TABLE users USING COVERING INDEX idx_age_city (age>?)  (~25 rows)
-- 注意 "COVERING" 关键字，表示只读索引不读原表

调试技巧：在 Room 中，开启 Room.databaseBuilder(context, AppDatabase.class, "db").enableQueryCallback() 或查看 logcat 中 SQLite 的 query plan 日志。也可以在开发阶段在 DB Browser for SQLite 中手动执行 EXPLAIN QUERY PLAN。

3.4 创建/删除索引的 SQL 命令

虽然 Room 注解会自动生成索引 DDL，但理解底层 SQL 有助于手动调整和排查问题。

-- 创建单列索引
CREATE INDEX IF NOT EXISTS idx_users_age ON users(age);

-- 创建复合索引（注意列顺序）
CREATE INDEX IF NOT EXISTS idx_users_age_city ON users(age, city);

-- 创建唯一索引
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_phone ON users(phone);

-- 删除索引
DROP INDEX IF EXISTS idx_users_age;
命令 说明 注意事项
CREATE INDEX 创建普通索引 允许重复值，可指定 ASC/DESC
CREATE UNIQUE INDEX 创建唯一索引 插入重复值时抛出 SQLiteConstraintException
DROP INDEX 删除索引 删除后相关查询退化到全表扫描
IF NOT EXISTS / IF EXISTS 安全操作 避免重复创建或删除报错

3.5 Room 中创建索引的注解方式

Room 通过 @Entity(indices = {…}) 声明索引，支持单列索引、复合索引和唯一索引，编译时自动生成对应的 DDL。

@Entity(tableName = "users",
        indices = {
            @Index(value = "phone", unique = true),          // 唯一单列索引
            @Index(value = {"age", "city"}),                  // 普通复合索引
            @Index(value = {"country", "province", "city"})   // 三列复合索引
        })
public class User {
    @PrimaryKey
    public long id;

    public String phone;
    public int age;
    public String city;
    public String country;
    public String province;
}

注解参数详解：

  • value：字符串数组，指定索引列名（必须与 Entity 字段名一致）
  • unique：boolean，是否为唯一索引，默认 false
  • name：String，索引名称，默认由 Room 自动生成
  • orders：Order[]，指定每列的排序方向，默认 ASC
// 带排序方向和自定义名称的复合索引
@Entity(tableName = "orders",
        indices = {
            @Index(value = {"userId", "createTime"},
                   name = "idx_user_create",
                   orders = {Index.Order.ASC, Index.Order.DESC})
        })
public class Order {
    @PrimaryKey
    public long orderId;

    public long userId;
    public long createTime;
    public double amount;
}

迁移陷阱：如果数据库已存在，新增索引需要编写 Migration，否则 Room 会抛异常。例如 new Migration(1, 2) { … execSQL("CREATE INDEX …") }。仅有注解而没 Migration，Room 在运行时无法自动变更已有数据库索引。

3.6 索引设计性能对比

场景 无索引耗时 有索引耗时 索引命中条件
WHERE age=25（精确匹配） ~120 ms ~2 ms 单列索引直接命中
WHERE age>20 AND city='深圳' ~150 ms ~5 ms 复合索引 (age, city) 命中
WHERE city='深圳'（无 age） ~130 ms ~130 ms（全表扫描） 复合索引 (age, city) 失效
INSERT 1000 条数据 ~8 ms ~35 ms（维护索引额外开销） 每多一个索引写入越慢

优化原则：单表索引数量控制在 4~6 个以内，写入密集的表应更少。使用 EXPLAIN QUERY PLAN 验证每个关键查询是否命中索引，避免“僵尸索引”占用写入资源。

3.7 常见错误与排查

  1. 索引列顺序错误：复合索引列顺序与 WHERE 条件不匹配，导致最左前缀失效。应按照查询频率最高的列排在前面。
  2. 隐式类型转换：WHERE phone = 13800138000（phone 字段为 TEXT 类型）会导致索引失效。确保 Java 类型与数据库列类型一致。
  3. LIKE 前置通配符：WHERE name LIKE '%张' 无法使用索引，改为 WHERE name LIKE '张%' 可命中。
  4. 索引冗余：已有 (a, b) 复合索引，再建 (a) 单列索引是冗余的，前者已覆盖后者。
  5. 未添加 Migration：新增索引时遗漏 Migration，Room 启动时抛出 IllegalStateException。
// 正确迁移示例（版本 2 → 3）
static final Migration MIGRATION_2_3 = new Migration(2, 3) {
    @Override
    public void migrate(@NonNull SupportSQLiteDatabase database) {
        database.execSQL("CREATE INDEX IF NOT EXISTS idx_user_create ON orders(userId, createTime DESC)");
    }
};

// 在构建数据库时添加 migration
Room.databaseBuilder(context, AppDatabase.class, "app.db")
    .addMigrations(MIGRATION_2_3)
    .build();

3.8 本章小结

  • 索引类型选择：高选择性单列用单列索引，多条件组合用复合索引，业务唯一用唯一索引。
  • 复合索引必须遵循最左前缀，范围查询会导致后续列失效。
  • EXPLAIN QUERY PLAN 是验证索引是否生效的黄金工具，关注关键字 USING INDEX 和 COVERING。
  • Room 中通过 @Entity(indices = @Index(...)) 声明索引，新增索引必须配合 Migration。
  • 索引并非免费：写入性能会随索引数量增加而下降，需在查询加速与写入开销之间找到平衡。
← 上一章 📖 返回目录 下一章 →