第4章 复合索引的列顺序原则

4.1 核心原理

复合索引（联合索引）是建立在多列上的B-Tree索引，其列顺序决定了索引的利用效率。遵循两条基本原则：

• 最左前缀匹配：索引只能从最左列开始连续匹配查询条件，跳过左列或使用非等值条件会导致后续列失效。
• 高选择性列在前：选择性（cardinality / 总行数）越高的列放在越左边，能快速缩小扫描范围。

示例：索引 (city, age, salary)，查询条件 city = 'Beijing' AND age > 25 AND salary = 10000 只能使用 city 和 age（范围条件后停止），salary 无法使用索引。若将 age 与 salary 交换为 (city, salary, age)，则等值条件 salary = 10000 可继续使用索引。

4.2 复合索引定义语法与Room实现

SQLite 原生语法：

CREATE INDEX idx_employee_city_age_salary 
ON employee(city, age, salary);

Room 实体类：通过 @Index 注解定义复合索引，列的顺序由数组顺序决定。

@Entity(indices = {
    @Index(value = {"city", "age", "salary"}, unique = false, name = "idx_employee_cas")
})
public class Employee {
    @PrimaryKey(autoGenerate = true)
    public long id;
    public String city;
    public int age;
    public double salary;
    public String name;
}

生成后的实际索引名在数据库 schema 中为 idx_employee_cas，列顺序为 city→age→salary。

4.3 查询条件顺序对执行计划的影响

Room 查询时，条件顺序 不影响 Where 子句的语义，但索引利用完全取决于索引列顺序。我们通过 EXPLAIN QUERY PLAN 分析不同写法。

4.3.1 测试数据准备

-- 插入 10 万行示例数据
INSERT INTO employee (city, age, salary, name) VALUES
('Beijing', 30, 15000, 'Alice'),
('Shanghai', 28, 12000, 'Bob'),
('Beijing', 35, 18000, 'Charlie'),
('Guangzhou', 22, 8000, 'David');
-- 实际数据量应足够大以使索引生效

4.3.2 最左前缀匹配验证

查询1：符合最左前缀，优先使用索引。

EXPLAIN QUERY PLAN
SELECT * FROM employee 
WHERE city = 'Beijing' AND age = 30 AND salary = 15000;

输出：

SEARCH TABLE employee USING INDEX idx_employee_cas (city=? AND age=? AND salary=?)

查询2：跳过最左列 city，索引失效。

EXPLAIN QUERY PLAN
SELECT * FROM employee 
WHERE age = 30 AND salary = 15000;

输出：

SCAN TABLE employee

4.3.3 范围条件后列失效

EXPLAIN QUERY PLAN
SELECT * FROM employee 
WHERE city = 'Beijing' AND age > 25 AND salary = 15000;

输出：

SEARCH TABLE employee USING INDEX idx_employee_cas (city=? AND age>?)

说明：salary 列未被索引使用，因为 age 是范围条件，后续列停止匹配。

4.4 列选择性对比实验

假设 city 有 5 种值（选择性 = 5/100000 = 0.005%），age 有 40 种值（选择性 0.04%），salary 有 10,000 种值（选择性 10%）。按选择性由高到低排序应为：salary(10%) > age(0.04%) > city(0.005%)。

但实际业务查询常以 city 作为过滤前提（如“北京地区”），此时必须将 city 放在最左位置以保证最左前缀匹配。如果 city 选择性极低，可考虑将 city 后移，改用其他高选择性列开头。

索引列顺序	查询条件顺序	索引使用情况	扫描行数（10万行）	耗时 (ms)
(city, age, salary)	city='Beijing' AND age=30 AND salary=15000	完全匹配	1	0.2
(city, age, salary)	salary=15000 AND age=30（缺少city）	全表扫描	100000	45
(salary, age, city)	salary=15000 AND age=30 AND city='Beijing'	完全匹配	1	0.1
(salary, age, city)	city='Beijing'（缺少salary）	全表扫描	100000	47

4.5 常见错误与解决方案

• 错误1：复合索引列顺序与查询条件不一致，导致大量回表。
  解决方案：使用 EXPLAIN QUERY PLAN 检查索引使用情况，按实际查询模式调整列顺序。
• 错误2：在选择性极低的列上建立复合索引首列（如性别、布尔值）。
  解决方案：将高选择性列前置，或将低选择性列与高频查询列组合但保持最左匹配。
• 错误3：在范围条件后放置等值条件列，导致索引停止匹配。
  解决方案：将等值条件列全部放在范围条件之前。

4.6 优化结果验证流程

1. 在原数据库上运行慢查询，记录耗时和扫描行数。
2. 创建符合最左前缀且列选择性合理的复合索引。
3. 再次运行相同查询，对比执行计划（SEARCH vs SCAN）和性能。
4. 使用 Room 的 @Query 定义具体 SQL，配合 Android Profiler 或 SQLiteOpenHelper 的 EXPLAIN QUERY PLAN 验证。

4.7 复合索引匹配规则图

下图展示复合索引 (A, B, C) 在不同查询条件下的匹配深度与范围：

4.8 总结与最佳实践

• 复合索引列顺序 = 按查询中 等值条件出现频率 + 列选择性 综合排序。
• 最左前缀是硬性约束；若业务查询多变，可考虑多个复合索引分别覆盖不同模式。
• 使用 EXPLAIN QUERY PLAN 定期审计，避免索引“部分使用”或“未使用”。
• Room 的 @Index 注解中列顺序即索引列顺序，不可随意调整。