一篇读懂 MySQL 索引优化：B+ 树、覆盖索引与最左前缀

「这条 SQL 为什么慢」是后端工程师的高频问题，答案往往藏在索引里：该加的索引没加、加了索引没用上、用了索引但效率低。理解 MySQL 索引的原理（B+ 树的结构、聚簇索引与二级索引的区别、最左前缀原则）是解决这些问题的前提。本篇讲清 MySQL 索引的原理与优化实践。

B+ 树：索引的物理结构

B+ 树是多路平衡搜索树——一棵高度为 3 的 B+ 树可以存储约 2000 万行数据（假设主键为 bigint，每页 16KB，非叶节点每页可存约 1170 个指针，叶节点每页可存约 15 行数据——3 层即可覆盖 1170 × 1170 × 15 ≈ 2000 万行）。B+ 树的关键特性：非叶节点只存键与指针（不存数据——每页可以存更多的键，树更矮），叶节点存数据且通过双向链表连接（范围查询只需遍历链表——不需要回溯到非叶节点）。树矮意味着磁盘 IO 少——每次读取一个节点 = 一次磁盘 IO（InnoDB 的页大小 16KB），3 层树最多 3 次 IO 找到任意一行——「树越矮，查询越快」是索引设计的核心逻辑。

聚簇索引与二级索引：回表的代价

InnoDB 的表是聚簇索引组织表——数据本身就存储在主键的 B+ 树叶节点中（主键索引 = 数据本身）。二级索引（非主键索引）的叶节点存储的是「索引列的值 + 主键值」——通过二级索引查到的不是完整行，而是主键值——需要用主键值回聚簇索引查完整行，这个过程叫「回表」。

回表的代价：如果查询返回 100 行，就需要回表 100 次（每次回表是一次聚簇索引的 B+ 树查找——3 次 IO）——回表 100 次 = 300 次额外 IO。优化手段一：覆盖索引（covering index）——如果查询所需的列全部包含在二级索引中，就不需要回表（索引覆盖了查询的所有列）。例如：SELECT name FROM users WHERE age = 25，如果存在联合索引 (age, name)——索引中已包含 name，无需回表（EXPLAIN 中 Extra 显示 Using index）。设计索引时的黄金法则：尽量让高频查询被覆盖索引覆盖——消除回表就是消除最大的性能瓶颈。

最左前缀原则：联合索引的可用性规则

联合索引（如 (a, b, c)）按照 a → b → c 的顺序排序——只有查询条件从最左列开始连续命中时，索引才能被使用。可用的情况：WHERE a = 1（用到 a）；WHERE a = 1 AND b = 2（用到 a 和 b）；WHERE a = 1 AND b = 2 AND c = 3（全部用到）；WHERE a = 1 AND b > 2（a 精确匹配，b 范围匹配——b 可用于索引但 c 不能用于索引范围查找，因为 b 是范围条件后的列不满足精确匹配）。不可用的情况：WHERE b = 2（跳过了 a，无法使用）；WHERE a = 1 AND c = 3（跳过了 b，c 列无法使用索引——但 a 列的索引仍可用）；WHERE b = 2 AND c = 3（不满足最左前缀，索引完全不可用）。

设计联合索引的原则：区分度最高的列放最前面（区分度 = 不同值的数量 / 总行数——性别只有男/女区分度低、用户 ID 区分度高）；等值查询的列放在范围查询的列前面（WHERE status = 1 AND created_at > xxx——status 放前面做精确匹配，created_at 放后面做范围）；覆盖索引的列追加在索引末尾（避免回表）。一个反直觉的规则：WHERE a = 1 ORDER BY b 可以使用联合索引 (a, b) 避免额外排序（a 确定后 b 天然有序）——索引不仅能加速 WHERE，还能消除 ORDER BY 的 filesort。

MySQL 索引的「失效场景」：写了索引但没用到

索引的失效场景是后端面试的高频题，也是实际开发中最容易踩的坑。失效场景一：对索引列使用函数或运算——WHERE YEAR(created_at) = 2024 对索引列使用了函数，索引失效（MySQL 无法使用 B+ 树的有序性查找——因为函数破坏了索引列的原始值与索引排序的对应关系）。失效场景二：隐式类型转换——WHERE phone = 13800138000（phone 列是 VARCHAR，传入的是数字——隐式类型转换导致索引失效）。失效场景三：LIKE 以通配符开头——LIKE '%关键词' 左模糊匹配无法使用 B+ 树的有序性（B+ 树按前缀排序——以通配符开头意味着前缀不确定）。失效场景四：OR 连接非索引列——WHERE indexed_col = 1 OR non_indexed_col = 2——OR 的一侧没有索引则整个查询不使用索引。失效场景五：不满足最左前缀——联合索引 (a, b, c) 中直接查 b 或 c——不满足最左前缀原则。诊断工具是 EXPLAIN——type 列显示 ALL 表示全表扫描（索引失效），key 列显示使用的索引，Extra 列显示额外信息（Using index 表示覆盖索引、Using filesort 表示额外排序）。

EXPLAIN 的输出详解：type、key 与 Extra 的解读

EXPLAIN 是 MySQL 查询优化的终极裁判——它的输出有三个关键列需要理解。type 列（访问类型）：从好到差依次是 system > const > eq_ref > ref > range > index > ALL——const 是主键或唯一索引的等值查询（最多一行）；ref 是非唯一索引的等值查询；range 是索引的范围扫描（BETWEEN、>、<）；index 是扫描整个索引树；ALL 是全表扫描（最差——需要优化）。key 列（使用的索引）：显示 MySQL 实际选择的索引——NULL 表示没有使用索引。Extra 列（额外信息）：Using index 表示覆盖索引（不需要回表——最好的情况）；Using where 表示服务层过滤（索引不能覆盖所有过滤条件）；Using filesort 表示额外排序（ORDER BY 没有被索引覆盖——需要优化）；Using temporary 表示使用了临时表（GROUP BY 或 DISTINCT 没有被索引覆盖——需要优化）。EXPLAIN 是索引优化的终极裁判。

EXPLAIN 的输出详解与优化实践

EXPLAIN 是 MySQL 查询优化的终极裁判。type 列从好到差是 system > const > eq_ref > ref > range > index > ALL。key 列显示 MySQL 实际选择的索引 NULL 表示没有使用索引。Extra 列显示额外信息 Using index 表示覆盖索引 Using filesort 表示额外排序 Using temporary 表示使用了临时表。索引优化的实践：EXPLAIN 查看查询计划分析 type 与 key 与 Extra 根据分析结果调整索引或 SQL——索引优化的终极裁判是 EXPLAIN 不是直觉。

索引的失效场景与 EXPLAIN 的使用

索引的失效场景是后端面试的高频题。失效场景一对索引列使用函数或运算导致索引失效。失效场景二隐式类型转换导致索引失效。失效场景三 LIKE 以通配符开头无法使用 B+ 树的有序性。失效场景四 OR 连接非索引列整个查询不使用索引。失效场景五不满足最左前缀联合索引中直接查第二或第三列。诊断工具是 EXPLAIN。type 列从好到差是 system > const > eq_ref > ref > range > index > ALL。key 列显示实际选择的索引。Extra 列显示额外信息 Using index 表示覆盖索引 Using filesort 表示额外排序。

索引设计的「覆盖索引」实战：从查询分析到索引设计

覆盖索引的实战需要从「查询分析」开始：分析业务的高频查询需要哪些列（SELECT 的列 + WHERE 的列 + ORDER BY 的列），然后设计联合索引覆盖这些列。覆盖索引的设计原则：索引的列顺序遵循最左前缀原则（等值查询的列在前、范围查询的列在后、SELECT 的列追加在末尾）；覆盖索引的验证：EXPLAIN 的 Extra 列显示 Using index 表示命中覆盖索引（不需要回表——本库正文的覆盖索引逻辑）。覆盖索引的局限：索引的列数不能太多（MySQL 单行索引最大 767 字节或 3072 字节取决于行格式——列太多超出限制），且频繁更新的列不适合加入索引（索引的维护成本与更新频率成正比）。覆盖索引的设计是在「查询性能」与「写入开销」之间找平衡——查询越快写入越慢是覆盖索引的 tradeoff。

索引设计的「反模式」：常见的索引错误

索引设计的反模式值得单独列出。反模式一：每个查询都建一个索引——索引数量过多导致写入开销大（每次 INSERT/UPDATE/DELETE 都要维护所有索引）且优化器的索引选择变难。反模式二：在低区分度的列上建索引——性别只有男/女两个值（区分度 = 2 / 1400000000 ≈ 0），索引的选择性极差，优化器不会使用。反模式三：前导列是范围查询的联合索引——(created_at, status) 的联合索引中 created_at 用了范围查询则 status 无法使用索引（范围查询后的列索引失效）。反模式四：索引列参与计算或函数——WHERE YEAR(created_at) = 2024 对索引列使用函数索引失效（应改写为 WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'）。反模式五：过长的字符串列建完整索引——VARCHAR(255) 的完整索引占用大量空间，应该使用前缀索引（INDEX(col(20))——只索引前 20 个字符）。

MySQL 索引的「覆盖索引」进阶：联合索引的列顺序设计

覆盖索引的联合索引设计有一个精确的方法。假设高频查询是 WHERE status = 1 AND created_at > xxx ORDER BY id，需要 SELECT id, name, amount。联合索引的设计：第一列放等值条件列 status，第二列放范围条件列 created_at，末尾追加 SELECT 中需要的非索引列 name 和 amount。最终索引为 (status, created_at, name, amount)。这个索引的设计让查询可以完全在索引树上完成（不需要回表——本篇正文的覆盖索引逻辑）：status 的等值过滤缩小范围，created_at 的范围过滤进一步缩小，name 和 amount 在索引中直接获取。联合索引的列顺序设计的黄金法则：等值条件的列放前面，范围条件的列放其次，ORDER BY 的列再次，覆盖 SELECT 的列放最后——这个顺序决定了索引能否被高效使用。

小结

MySQL 索引优化收成四条：B+ 树是多路平衡搜索树——非叶节点只存键与指针（树更矮），叶节点存数据且链表连接（范围查询友好），3 层树约 2000 万行；聚簇索引的数据在主键 B+ 树的叶节点，二级索引需要回表查完整行——覆盖索引消除回表是索引优化的第一法则；最左前缀原则——联合索引只有从最左列开始连续命中才可用，等值查询的列放前面、范围查询的列放后面；索引不仅能加速 WHERE 还能消除 ORDER BY 的 filesort——(a, b) 联合索引在 WHERE a = 1 ORDER BY b 时天然有序。索引优化的最终检验工具是 EXPLAIN——看 type（访问类型：const > ref > range > index > ALL）、key（使用的索引）、Extra（Using index = 覆盖索引 / Using filesort = 额外排序）——EXPLAIN 是索引优化的终极裁判。

参考资料

• MySQL 官方文档：Optimization and Indexes —— 索引优化的权威说明
• 本库前篇：《InnoDB 中的事务隔离级别和锁的关系》《pgvector 实战》
• 《高性能 MySQL》第 5 章—— 索引优化的经典讲解