第 4 章 索引设计与优化

第 4 章 · 索引设计与优化

章节摘要：如果全书只允许保留一章，我选这章。查询慢的第一嫌疑人是索引：没建、建错、建了不用、用了个头。本章从 B+ 树的物理结构讲起——只有知道数据在磁盘上怎么摆的，才能理解索引为什么会失效；然后讲索引类型的选型、复合索引的最左前缀、覆盖索引的免回表魔法，最后汇总八种典型失效场景作为评审会的检查清单。

学习目标

阅读完本章，你应当能够：

1. 画出 B+ 树索引的结构草图，解释为什么高度 3 的 B+ 树两千万行只需三次 IO；
2. 区分聚簇索引与二级索引，说清"回表"这个动作的完整链路；
3. 按业务访问模式设计复合索引的列顺序，用最左前缀原则预判一个索引能服务哪些查询；
4. 识别覆盖索引场景，用"索引里放哪几列"的决策把高频查询压到免回表；
5. 逐条排查八类索引失效写法，把 EXPLAIN 里的 key 为空归因到具体原因；
6. 在评审会上对"要不要给这张表再加一个索引"给出维护成本与查询收益的双向核算。

核心概念速览

索引的本质是用额外的存储和写入开销，换查询时少扫数据。所有索引知识点都是这句话的推论：B+ 树矮胖是为了减少磁盘 IO 次数；最左前缀是复合索引排序方式的必然结果；失效场景是"查询写法让树没法用"的各种形态。

金句：每加一个索引，是在为一部分查询投票，同时向所有写入征税。

子章节导航

4.1 索引原理与创建管理：从"为什么不用二分查找有序数组"引入 B+ 树，拆解节点结构与页分裂；CREATE INDEX 的语法细节、不可见索引等管理特性。

4.2 索引类型：B-Tree、Hash、全文、空间四类的适用边界，重点讲主键即聚簇索引对二级索引的影响，以及 Hash 结构为何在 InnoDB 里只能自适应存在。

4.3 复合索引与覆盖索引：最左前缀原则的推导与验证；把索引列顺序当成"查询模式的排序编码"；覆盖索引消除回表的完整案例。

4.4 索引失效场景：八类高频失效写法逐条演示，每类配 EXPLAIN 症状与修正写法，形成评审会的快速排查单。

子章节之间的逻辑关系

四节是"原理 → 家族 → 策略 → 反例"的递进：4.1 讲清楚索引为什么快，4.2 讲有哪几种快法，4.3 讲怎么组合出最快，4.4 用失效场景反向验证前三节的理解深度。评审会上真正的考题几乎都出在 4.3 和 4.4——结构谁都能背，取舍才见功力。

+---------------------+
| 4.1 B+ 树原理       |   物理结构决定一切
+---------+-----------+
          v
+---------------------+
| 4.2 索引类型        |   聚簇/二级/Hash/全文
+---------+-----------+
          v
+---------------------+    排查失效归因到结构
| 4.3 复合与覆盖索引  | ------------------+
+---------+-----------+                   |
          v                               v
+---------------------+          +----------------+
| 4.4 失效场景八连查  | -------> | 评审会索引清单 |
+---------------------+          +----------------+
​

前置知识与后续延伸

需要第 1 章的存储引擎知识（InnoDB 与缓冲池）和第 3 章的查询写法（尤其 JOIN 与 ORDER BY）。本章产出的索引设计直接决定第 5 章的 EXPLAIN 输出：type 从 ALL 升到 ref、Extra 里出现 Using index，全是本章知识在执行计划里的投影。学完本章后做一次自查：把你项目里最慢的三条查询拿来讲一遍为什么慢——讲不出来的，回到 4.4 重新过。