MySQL 索引深度剖析——B+树原理、索引失效的10种情况、覆盖索引优化

适读人群：有 MySQL 基础、想彻底搞懂索引原理的后端工程师 | 阅读时长：约18分钟 | 核心价值：从 B+树底层讲到索引失效排查，建立索引优化的系统认知

---

从一次 P0 故障说起

2022年某个周三下午三点，我接到老朋友阿明的电话。他所在公司的订单查询接口突然超时，P99 从 50ms 飙到了 15 秒，监控大盘一片红。

运维第一反应是服务器资源不足，扩容；但扩完容还是慢。后来 DBA 介入，用 SHOW PROCESSLIST 一看：几百个 SELECT 查询全部在 waiting，CPU 正常，但磁盘 IO 拉满了。

最终定位到一个订单列表查询 SQL：

SELECT * FROM orders WHERE status = 1 AND DATE(create_time) = '2022-11-09'

create_time 上有索引，但 DATE() 函数包裹之后，索引直接失效了，全表扫描了 8000 万行数据。这个 SQL 在业务低谷期没问题，一到活动高峰，每秒几千次查询打过来，把数据库打趴了。

"索引明明加了，为什么不走？"阿明问。

这个问题背后，是绝大多数开发者对索引原理理解的盲点。今天，我们从 B+树的底层结构讲起，把索引失效的 10 种情况逐一分析，再看覆盖索引如何进一步压榨性能。

---

一、B+树：索引的底层结构

1.1 为什么是 B+树而不是 B树或红黑树？

红黑树是平衡二叉树，查找效率 O(log n)，但每个节点只存一个键值，树高随数据量增长而增高。1000 万行数据，树高大约是 23-24 层。每次查找需要 23 次磁盘 IO（树的每一层对应一次 IO），代价极高。

B树（多路平衡树）每个节点存多个键值和数据指针，树高更低。100 万行数据，B树高度大约 3-4 层。但 B树的数据指针分布在所有节点上，范围查询时（比如 WHERE id BETWEEN 100 AND 200），需要在树的各层之间反复横跳。

B+树做了关键改进：

1. 非叶子节点只存键值，不存数据，这样每个非叶子节点能容纳更多的键值（InnoDB 默认页大小 16KB，一个非叶子节点可以存几百甚至上千个键值），树高更低
2. 数据全部存在叶子节点，叶子节点之间用双向链表连接
3. 范围查询效率极高：找到范围的起点叶子节点后，沿链表遍历即可

对于 InnoDB：根据页大小 16KB，键值 8 字节（bigint），子节点指针 6 字节，一个非叶子节点能存 ≈ 16384 / (8+6) ≈ 1170 个键值。高度为 3 的 B+树，可以存储 1170 × 1170 × 叶子节点数据量 ≈ 千万级行数据，只需要 3 次磁盘 IO。

1.2 聚簇索引 vs 非聚簇索引

InnoDB 的聚簇索引（主键索引）：叶子节点存的是完整的行数据。也就是说，按主键查找，找到叶子节点就找到了所有数据。

InnoDB 的二级索引（非主键索引）：叶子节点存的是索引键值 + 主键值，而不是完整的行数据。

这个设计有一个重要含义：通过二级索引查找数据时，如果查询的列不在索引中，需要先找到主键值，再用主键值去聚簇索引里查完整行数据，这个过程叫回表。

回表是有代价的：每次回表都是一次 B+树查找，如果需要回表 1000 次，就是 1000 次树查找，每次 3 次磁盘 IO，共 3000 次 IO。这就是为什么 SELECT * 比 SELECT 索引列 慢很多的底层原因。

---

二、索引失效的 10 种情况

情况 1：对索引列使用函数

-- 失效：DATE() 函数包裹了索引列
SELECT * FROM orders WHERE DATE(create_time) = '2022-11-09';

-- 正确：把函数作用转移到常量侧
SELECT * FROM orders WHERE create_time >= '2022-11-09 00:00:00' 
                       AND create_time < '2022-11-10 00:00:00';

原理：索引存的是 create_time 的原始值，DATE() 之后的结果不在索引里，MySQL 只能全扫描。

情况 2：对索引列进行计算

-- 失效：对 age 进行了运算
SELECT * FROM users WHERE age + 1 = 18;

-- 正确：把运算移到常量侧
SELECT * FROM users WHERE age = 17;

情况 3：类型隐式转换

-- phone 字段类型是 VARCHAR，但传入了 INT
-- 失效：MySQL 会对 phone 列做隐式类型转换（转为数字比较）
SELECT * FROM users WHERE phone = 13812345678;

-- 正确：类型匹配
SELECT * FROM users WHERE phone = '13812345678';

这是生产中最常见的索引失效原因之一，代码里用整数传手机号字段极其容易出现。

情况 4：LIKE 通配符前置

-- 失效：前缀通配符，无法利用索引的有序性做范围定位
SELECT * FROM products WHERE name LIKE '%手机%';

-- 有效：后缀通配符，能定位到起始位置
SELECT * FROM products WHERE name LIKE '华为%';

如果业务需要 LIKE '%xxx%'，需要考虑全文索引（FULLTEXT）或 Elasticsearch。

情况 5：联合索引不满足最左前缀原则

-- 假设联合索引 INDEX(a, b, c)
-- 有效：满足最左前缀
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;
SELECT * FROM t WHERE a = 1 AND b = 2;
SELECT * FROM t WHERE a = 1;

-- 失效：跳过了 a
SELECT * FROM t WHERE b = 2 AND c = 3;

-- 部分有效：a 列用到索引，但 c 列的索引失效（b 有范围查询打断了）
SELECT * FROM t WHERE a = 1 AND b > 5 AND c = 3;

最左前缀原则的本质：B+树按照索引列从左到右排序，如果跳过了左侧的列，就无法确定起始位置，只能全扫。

情况 6：OR 条件中有非索引列

-- 假设 name 有索引，age 没有索引
-- 失效：OR 的一侧无法用索引，整个查询退化为全扫
SELECT * FROM users WHERE name = '张三' OR age = 25;

-- 改写为 UNION
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 25;

情况 7：NOT IN / NOT EXISTS

-- 失效（通常）：NOT IN 无法利用索引
SELECT * FROM orders WHERE status NOT IN (1, 2, 3);

-- 改写：用 < > 替代，或者反向逻辑
SELECT * FROM orders WHERE status >= 4;

情况 8：索引列存在 NULL 值

-- IS NULL / IS NOT NULL 是否用索引取决于数据分布
-- 如果 NULL 值比例很高，MySQL 可能选择全扫
SELECT * FROM users WHERE deleted_at IS NULL;

建议：业务上尽量避免 NULL，用默认值替代，比如 deleted = 0 而不是 deleted = NULL。

情况 9：数据量太少或统计信息不准

MySQL 的查询优化器基于代价估算选择执行计划。如果一个查询命中的行数超过全表的 30%，优化器可能认为全扫比走索引更快（减少 B+树遍历和回表开销）。

-- 如果 status=1 的数据占全表 80%，这个查询可能不走索引
SELECT * FROM orders WHERE status = 1;

另一个常见情况：表数据量很小（几百行），优化器直接选全扫，因为全扫比走索引还快。这时 EXPLAIN 显示 type=ALL 是正常的，不用强制加索引。

情况 10：字符集/排序规则不匹配

-- 两张表关联，一张是 utf8，另一张是 utf8mb4
-- 关联字段的排序规则不一致，关联时发生隐式转换，索引失效
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id;
-- 如果 orders.user_id 是 utf8，users.id 是 utf8mb4，关联时索引失效

---

三、覆盖索引：消灭回表的利器

覆盖索引（Covering Index） 的定义：查询需要的所有列都在索引中，不需要回表。

// 典型的覆盖索引场景
// 假设有联合索引 INDEX(user_id, status, create_time)

// 这个查询只需要 user_id, status, create_time 三列
// 全部在索引里，无需回表
@Select("SELECT user_id, status, create_time FROM orders " +
        "WHERE user_id = #{userId} AND status = #{status}")
List<OrderSummary> findOrderSummary(@Param("userId") Long userId, 
                                     @Param("status") Integer status);

// 对比：这个查询需要所有列，必须回表
@Select("SELECT * FROM orders WHERE user_id = #{userId}")
List<Order> findAllByUserId(@Param("userId") Long userId);

EXPLAIN 中识别覆盖索引：如果 Extra 列显示 Using index，就是覆盖索引，没有回表。

EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 12345;
-- Extra: Using index  ← 覆盖索引，无回表
-- type: ref          ← 使用了索引

EXPLAIN SELECT * FROM orders WHERE user_id = 12345;
-- Extra: (empty)     ← 需要回表
-- type: ref

覆盖索引的设计原则：高频查询的 SELECT 列，考虑纳入联合索引。但注意不要过度，索引列越多，写入性能越低，每次写操作都要更新所有相关索引。

---

四、实战建议

经过多个项目的实战，我总结了几条索引设计的基本原则：

优先给 WHERE 条件、JOIN 关联、ORDER BY 列加索引，但顺序要按选择性（Cardinality，即不重复值的比例）从高到低排列——选择性高的列放在联合索引的左侧，过滤效果更好。

避免重复索引：INDEX(a) 和 INDEX(a, b) 同时存在时，前者完全被后者包含，INDEX(a) 是冗余的。

定期 ANALYZE TABLE：更新统计信息，让优化器做出更准确的代价估算。特别是在批量导入数据或大量删除数据之后，统计信息可能严重偏差。

用 EXPLAIN 验证每一个重要 SQL：重点关注 type（ALL 是全扫，危险）、key（实际使用的索引）、rows（预估扫描行数）、Extra（Using filesort、Using temporary 都是需要优化的信号）。