大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！
上周我们讲了执行计划怎么读，这周往底层走一步——索引到底是怎么工作的？
很多人用索引，但不知道索引为什么能快。加了索引查询就快了，但为什么有时候加了反而没效果？为什么联合索引的顺序那么重要？这些问题，不懂B+Tree结构和回表机制，你就永远只能“背口诀”而不是“懂原理”。
B+Tree是什么？用生活中的例子理解
想象你有一本1000页的书，没有目录。你想找“索引优化”这个章节，只能一页一页翻——这就是全表扫描。如果书前面有一个按字母排序的目录，你先在目录里找到“索引优化”在第800页，然后直接翻到800页——这就是索引查找。B+Tree就是数据库的“目录”。
B+Tree有几个关键特点：
- 所有数据都存放在叶子节点，非叶子节点只存“路标”（索引值和指针）。
- 叶子节点之间用双向链表连接，方便范围扫描。
- 树的高度通常在2-4层，所以一次索引查找只需要2-4次磁盘I/O。
聚簇索引：数据本身就是索引
InnoDB表是索引组织表——数据就是按照主键组织的B+Tree。叶子节点存放完整的数据行。如果表没有定义主键，InnoDB会隐式生成一个6字节的ROWID作为主键。
二级索引：先找主键，再找数据
二级索引的叶子节点存储的是：索引列的值 + 主键值。当你通过二级索引查找数据时，流程是：
- 在二级索引树中找到目标值，拿到主键。
- 用主键去聚簇索引树中查找完整的数据行。
这第二步就是回表。
回表的代价
回表不是免费的。每次回表都是一次B+Tree查找，也就是一次磁盘I/O。如果查询扫描了1000行，就需要回表1000次——1000次I/O。
用一个具体场景来理解：一张订单表有500万行数据，在user_id上建立了二级索引。执行SELECT * FROM orders WHERE user_id = 12345，假设user_id=12345有200条记录。
- 先在二级索引上找到这200条记录的主键值（快速）。
- 然后回表200次，去聚簇索引取出完整行（200次I/O）。
- 如果这200条记录在磁盘上分布很分散，每次回表都需要随机I/O，代价更高。
如果业务高峰期这样的查询每秒执行100次，每秒就是20000次随机I/O——磁盘很快会成为瓶颈。
覆盖索引：消除回表，性能翻倍
如果二级索引的叶子节点已经包含了查询需要的所有列，就不需要回表了。这就是覆盖索引。
还是上面的例子，但查询改为SELECT user_id, order_date FROM orders WHERE user_id = 12345。
如果我们在(user_id, order_date)上建立复合索引，二级索引的叶子节点已经包含了user_id和order_date，查询可以直接从二级索引返回结果，不需要回表。EXPLAIN的Extra列会显示Using index。
覆盖索引为什么快？ 因为它把“二级索引查找 + 回表”两步变成了一步，减少了I/O次数。尤其在扫描行数较多的查询中，覆盖索引带来的性能提升非常显著。
联合索引为什么要讲究顺序？
联合索引(a, b, c)本质上是一棵B+Tree，数据的排序规则是：先按a排序，a相同再按b排序，b相同再按c排序。
- 查询
WHERE a = 1 AND b = 2：能用到a和b，因为a和b的排序规则符合查询条件。 - 查询
WHERE b = 2：用不到索引，因为b不是第一排序依据。 - 查询
WHERE a = 1 AND c = 3：能用到a，但用不到c，因为b被跳过了。
这就是最左前缀原则的底层逻辑。
一个完整案例：从索引设计到执行验证
假设你有用户事件表user_events，数据量1000万行。常见的查询是“查询某用户最近7天的行为记录”。
sql
SELECT user_id, event_type, event_time, device_id
FROM user_events
WHERE user_id = 123456
AND event_time > '2026-06-10'
ORDER BY event_time DESC;
索引方案评估：
| 方案 | 索引设计 | 能否回表 | 能否走索引排序 | 代价评估 |
|---|---|---|---|---|
| 方案A | 不建索引 | 全表扫描 | 否 | 扫描1000万行，极慢 |
| 方案B | (user_id) |
需回表 | 否（需filesort） | 回表+排序，较慢 |
| 方案C | (user_id, event_time) |
需回表（还要取device_id） | 是 | 回表，但排序走索引 |
| 方案D | (user_id, event_time, device_id) |
不需要（覆盖所有列） | 是 | 最优 |
方案D是覆盖索引，查询列user_id、event_time、device_id都在索引中，不需要回表，排序也走索引，是最高效的方案。但需要权衡存储空间——如果device_id是VARCHAR(255)，索引会变得很胖，写入性能会下降。
使用覆盖索引的注意事项
覆盖索引的核心逻辑是“用空间换时间”——把查询需要的列都塞进索引，消除回表。但它不是没有代价的：
- 索引变胖：索引列越多，每个索引条目占用的空间越大，内存中能缓存的索引页越少。
- 写入变慢：每次INSERT/UPDATE都需要维护更多的索引列。
- 收益递减：当索引已经包含了大部分常用列时，继续添加列的边际收益会下降。
因此，覆盖索引的设计需要在查询性能和写入性能之间做权衡。适合覆盖索引的场景是：查询频率高、扫描行数多、对响应时间敏感的核心查询。对于低频查询或只扫描几行的点查，回表的代价完全可以接受。
总结
理解B+Tree结构、聚簇索引与二级索引的差异、回表的代价，是做对索引设计的前提。覆盖索引是消除回表的关键手段，但它需要权衡存储和写入成本。联合索引的顺序由B+Tree的排序规则决定，违背最左前缀原则的查询无法使用索引。掌握这些底层逻辑，你就能从“背口诀”升级到“懂原理”，在做索引设计时更有把握。
小耶在手，SQL 不愁
还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~
