02 · 索引原理与优化：B+树 / 覆盖索引 / 最左匹配
后端慢查询十有八九是索引没建对。本篇讲清楚索引为什么能让查询变快（B+树）、什么时候该建（覆盖索引）、复合索引的规则（最左匹配），以及索引的隐藏代价。
速览卡
| 项 | 内容 |
|---|---|
| 一句话定位 | 索引的底层原理 + 建索引的判断框架，让慢查询变成"看得懂、改得动" |
| 核心内容 | B+树为什么适合磁盘、覆盖索引、最左匹配、回表、索引失效的真相 |
| 读完能回答 | 为什么这个 WHERE 不用索引？复合索引列的顺序怎么定？索引越多越好吗？ |
| 相关板块 | DB01 关系模型与 SQL ｜ DB04 Schema 设计 ｜ C10 数据建模与访问 |
一、索引的本质：用空间换"少读磁盘"
没有索引 = 全表扫描：一行行读，O(n)。 有索引 = 先查"目录"定位，再按指针取行，O(log n)。
磁盘是慢的：一次随机读约 0.1ms，CPU 一条指令约 1ns，差约 10 万倍。 索引的核心目标不是"少算"，而是少读磁盘块。
二、为什么是 B+树，不是二叉搜索树
| 结构 | 为什么不适合磁盘 |
|---|---|
| 二叉搜索树 | 树太高，一次查询要读很多层，每层一次磁盘 IO |
| B+树 | 一个节点存多个键，树矮；叶节点串成链表，范围查询友好 |
B+树 特点：
- 非叶节点只存"键"，不存数据 → 节点更紧凑，一层能装更多键
- 所有数据在叶节点，且按序串成链表 → 范围查询一次定位连读
- 树高通常 3~4 层 → 千万级数据 3~4 次磁盘 IO 定位到行你不需要背 B+树的细节，但要知道：矮树 + 顺序链表 = 点查快、范围查快、稳定 O(log n)。
三、主键索引 vs 二级索引：回表
| 索引类型 | 叶子存什么 |
|---|---|
| 主键/聚簇索引 | 整行数据（InnoDB 主键即聚簇） |
| 二级索引 | 索引键 + 主键值（不是整行） |
二级索引查到主键 → 再用主键回聚簇索引取整行 = 回表回表是"查到位置又去取数据"的额外 IO。能避免就避免——这就是覆盖索引的价值。
四、覆盖索引：查询所需列全在索引里
-- 索引 idx(a,b)：只有 a、b 两列
SELECT a, b FROM t WHERE a = 1; -- 覆盖：只读索引，不回表
SELECT a, b, c FROM t WHERE a = 1; -- 需要 c → 回表判断框架：SELECT 的列和 WHERE 的条件列都落在同一个索引里，就避免了回表。高频查询可以考虑"把常用列做成覆盖索引"来省一次 IO。
代价：索引要存这些列 → 空间变大、写入变慢。只为真正高频的查询做覆盖索引。
五、复合索引与最左匹配
CREATE INDEX idx_user_status ON orders(user_id, status, created_at);最左匹配：查询的等值条件必须从最左列开始连续使用，才能命中该索引前缀。
| 查询 | 命中情况 |
|---|---|
WHERE user_id = 1 | ✅ 用 user_id 前缀 |
WHERE user_id = 1 AND status = 'paid' | ✅ 用前两列 |
WHERE user_id = 1 AND status='paid' AND created_at > ... | ✅ 全用（范围列在最后） |
WHERE status = 'paid' | ❌ 跳过了最左列，用不上 |
WHERE user_id = 1 AND created_at > ... | ⚠️ 只用到 user_id，created_at 断在范围 |
复合索引列的顺序怎么定
| 规则 | 说明 |
|---|---|
| 先放等值条件列 | = 能继续向前匹配 |
| 再放范围条件列 | >/</BETWEEN 之后的列用不上 |
| 区分度高的放前面 | 但要注意"区分度"不是唯一标准，结合查询模式 |
| 一个索引服务"最常见查询" | 不要为每个字段单独建索引 |
六、索引失效：别信玄学，看执行计划
以下"常见说法"很多是看版本和优化器的，正确姿势是 EXPLAIN：
| 场景 | 实际情况 |
|---|---|
WHERE name LIKE '%xx' | 前导通配符通常用不上索引 |
WHERE YEAR(created_at) = 2024 | 对列做函数/运算通常用不上（要改成范围） |
| 隐式类型转换 | varchar 列配数字可能失效或全扫 |
| 结果集很大（超过一定比例） | 优化器认为全扫更划算，即使有索引也不用 |
判断框架：永远用
EXPLAIN看type（const/ref/range/index/ALL）。ALL= 全表扫描，是需要警惕的信号；但"结果集大时全扫"是合理决策，不是 bug。
七、索引的代价与取舍
| 收益 | 代价 |
|---|---|
| 读更快 | 写入变慢（每插一行要维护所有索引） |
| 覆盖查询 | 占磁盘/内存 |
| 约束（唯一索引） | 建错索引拖慢高频写 |
判断框架：
- 读多写少 → 大胆建索引
- 写多读少 → 只建必要索引
- 大表加索引要评估在线 DDL（
ALGORITHM=INPLACE）- 不是索引越多越好，一个复合索引常能顶多个单列索引
八、自检清单
- [ ] 能说出 B+树适合磁盘的两个原因（矮树 + 顺序链表）
- [ ] 知道主键索引叶子存整行、二级索引叶子存主键
- [ ] 知道回表是什么，以及覆盖索引如何避免
- [ ] 知道最左匹配，能把等值列放前面、范围列放后面
- [ ] 慢查询会用
EXPLAIN看type，而不是猜 - [ ] 知道索引有写入与空间代价，不为每个字段都建索引
九、常见误区
| 误区 | 事实 |
|---|---|
| "索引越多越快" | 每个索引都拖慢写入、占空间 |
| "建了索引就一定会用" | 优化器会评估，结果集大时可能全扫 |
| "复合索引顺序无所谓" | 顺序决定能命中哪些查询（最左匹配） |
| "用函数包一下没关系" | 对列做运算通常让索引失效 |
| "二级索引能直接取整行" | 二级索引叶子是主键，要回表 |
| "加索引零成本" | 高频写表要谨慎，必要时在线 DDL |
十、动手练习
练习 A：设计复合索引
表 orders(user_id, status, created_at, amount)，最常用查询是"某用户的某状态订单按时间倒序取最近 20 条"。你会怎么设计索引？为什么？
练习 B：EXPLAIN 体检
造 10 万行数据，分别对 WHERE user_id=1、WHERE status='x'、WHERE user_id=1 AND status='x' 跑 EXPLAIN，对比 type 与 rows。
练习 C：找出"自以为走了索引"的查询
给一个 WHERE DATE(created_at) = '2024-01-01' 和一个 WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'，EXPLAIN 对比，说出为什么后者能用索引。
相关文档
本区
- 01 · 关系模型与 SQL 基础 —— 先有集合思维再看索引
- 04 · Schema 设计与范式反范式 —— 模型设计决定索引怎么建
- 03 · 事务与隔离级别 —— 索引与锁的相互作用
其他域
- 10 · 数据建模与数据访问 —— ORM 使用侧的索引考虑
- 07 · 服务端性能与容量 —— 连接池与慢查询治理