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) 设计原则（实务要点）
- 以查询为中心：先找最常见的慢查询，再为这些查询建索引。
- 把选择性高且常做等值的列放左边。
- 相同索引不要重复。
- 避免为低基数列单独建索引。
- 覆盖索引优先。
- 注意写成本。
- 注意范围查询顺序。
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) 快速诊断清单
- EXPLAIN 分析。
- SHOW INDEX 查看选择性。
- 检查覆盖索引。
- 关注 filesort/temporary。
10) 结论
以慢查询为导向，合理设计组合索引，等值列放左、范围列放右，尽量覆盖索引，平衡读写。
网友评论