数据库性能调优：组合索引与原理

1) 索引的核心原理（快速回顾）

• 目的：用更少的磁盘 IO 找到需要的行——把“全表扫”变成“树上查”或“少量页读”。
• 常见实现：B-Tree（大多数关系型数据库、InnoDB、Postgres 等）。B-Tree 的叶子页存放索引键，树高很低（通常 2–4 层），因此一次索引查找只要读少量页。
• InnoDB 特点（重要）：
  • 主键是 聚簇索引（clustered index）：主键叶子页直接存放整行数据。
  • 二级索引（secondary index）的叶子页只存索引列 + 主键值（作为行指针）。若需要其它列，必须回表（用二级索引存的主键去聚簇索引查行）。
• Hash 索引：只适合等值查找（=），不支持范围（>、<、LIKE 'a%'）。
• 成本：索引加速读，但会：
  • 增加磁盘/内存占用；
  • 增加写入开销（INSERT/UPDATE/DELETE 需要维护索引）。

2) 组合/复合索引（what & how it works）

组合索引 = 在一个索引结构里包含多个列（例如 (a,b,c)）。

关键规则：Left-most prefix（最左前缀规则）

• 索引 (a,b,c) 能用到的查询模式包含：a / a,b / a,b,c 的等值或范围条件。
• 如果查询只用 b 或 c（没有 a），通常无法有效使用该组合索引。

等值与范围的顺序影响后续列使用

• 当组合索引左侧列出现 范围查询，索引右侧的列通常无法再用于索引查找。

覆盖索引（covering index）

• 如果索引包含查询所需的所有列（SELECT 列、WHERE 列、ORDER/BY 列），就可以直接由索引返回结果，无需回表。

3) 设计原则（实务要点）

1. 以查询为中心：先找最常见的慢查询，再为这些查询建索引。
2. 把选择性高且常做等值的列放左边。
3. 相同索引不要重复。
4. 避免为低基数列单独建索引。
5. 覆盖索引优先。
6. 注意写成本。
7. 注意范围查询顺序。

4) 常见使用场景与实例

场景 A：用户的最新订单分页

CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at);

解释：等值列放左边，排序列放右边，避免 filesort；如果 SELECT 列都在索引里则为覆盖索引。

场景 B：JOIN 场景 - 优化 JOIN 列

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

场景 C：范围导致后续列失效

索引 (a, b, c) 查询 WHERE a = ? AND b > ? AND c = ? 时，c 无法用到索引。

场景 D：多个单列索引 vs 一个组合索引

推荐建 (a,b) 而不是单列索引。

5) 诊断工具与命令

• SHOW INDEX FROM table;
• EXPLAIN SELECT ...
• ANALYZE table;
• pt-query-digest

6) Java 层面的实践建议

• 用 PreparedStatement。
• 在 dev 环境用 EXPLAIN 验证。
• Hibernate @Index 注解。

7) 索引维护与变更

• 在线建索引。
• 重建 / 优化。
• 灰度验证。
• 删除索引可先 invisible。

8) 常见误区

• 索引越多越好。
• 为布尔值建索引。
• LIKE '%abc%' 可走索引（错误）。
• ORDER BY 一定走索引（错误）。

9) 快速诊断清单

1. EXPLAIN 分析。
2. SHOW INDEX 查看选择性。
3. 检查覆盖索引。
4. 关注 filesort/temporary。

10) 结论

以慢查询为导向，合理设计组合索引，等值列放左、范围列放右，尽量覆盖索引，平衡读写。