MySQL 索引设计深度实践：从 B+Tree 到慢查询治理

MySQL 性能优化里，索引永远是最先被提到、也最容易被误用的工具。很多团队遇到慢查询就加索引，短期看延迟下降，长期却带来写入变慢、磁盘膨胀、优化器选择异常和维护成本上升。好的索引设计不是“越多越好”，而是让最重要的访问路径稳定、低成本、可解释。

索引的核心价值是减少扫描范围。关系型数据库强在通用查询，但生产系统真正高频的查询通常很固定。索引设计要围绕这些高频路径，而不是围绕所有可能的 SQL。

B+Tree 的基本理解

InnoDB 默认索引结构是 B+Tree。它适合等值查询、范围查询和排序。主键索引是聚簇索引，叶子节点保存整行数据；普通二级索引叶子节点保存主键值，查询普通索引后可能需要回表读取完整行。

这解释了两个关键现象：

• 主键查询通常很快，因为直接命中聚簇索引。
• 普通索引如果不能覆盖查询字段，可能产生大量回表。

因此，高频列表查询可以考虑覆盖索引，让查询只访问二级索引就返回结果。

联合索引与最左前缀

联合索引的字段顺序非常重要。常见原则是：等值过滤字段在前，范围字段和排序字段在后，并结合查询频率排序。

例如订单列表查询：

SELECT id, status, amount, created_at
FROM orders
WHERE user_id = ?
  AND status = ?
ORDER BY created_at DESC
LIMIT 20;

适合的索引可能是：

CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at DESC);

如果把 created_at 放在最前面，按用户查询时可能无法有效缩小范围。联合索引不是字段集合，而是有序路径。

覆盖索引

覆盖索引指查询字段都能从索引中取得，不需要回表。对高频分页列表很有价值。

CREATE INDEX idx_orders_list
ON orders(user_id, status, created_at DESC, id, amount);

但覆盖索引会让索引变宽，增加写入成本和磁盘占用。不要为了低频查询创建超宽索引。更稳妥的方式是先用慢查询和业务指标确认收益。

索引失效

常见索引失效原因包括：

• 对索引字段使用函数。
• 隐式类型转换。
• 前导模糊查询。
• OR 条件无法有效使用索引。
• 联合索引不满足最左前缀。
• 范围条件后面的字段无法继续充分利用。

例如：

WHERE DATE(created_at) = '2026-06-05'

这会让数据库对字段计算函数。更好的写法是：

WHERE created_at >= '2026-06-05 00:00:00'
  AND created_at <  '2026-06-06 00:00:00'

EXPLAIN 要看什么

EXPLAIN 是分析 SQL 的入口，但不要只看有没有使用索引。重点看：

• type：访问类型，range、ref、eq_ref 通常比 ALL 好。
• key：实际使用的索引。
• rows：估算扫描行数。
• Extra：是否 Using filesort、Using temporary、Using index。

如果 rows 很大，即使命中了索引，也可能仍然慢。命中低选择性索引不等于高效。

分页优化

深分页是高频性能陷阱：

SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

数据库需要扫描并丢弃大量行。更好的方式是基于游标：

SELECT *
FROM orders
WHERE id > ?
ORDER BY id
LIMIT 20;

对于按时间排序的列表，可以使用 (created_at, id) 组合游标，避免重复和遗漏。

索引治理

生产环境要定期治理索引：

• 清理长期未使用索引。
• 合并重复前缀索引。
• 分析慢查询新增索引。
• 检查索引选择性。
• 评估写入路径成本。

索引变更也要谨慎。大表加索引可能锁表或产生巨大 IO，应使用在线 DDL、低峰执行，并准备回滚方案。

总结

MySQL 索引设计是一门访问路径工程。理解 B+Tree、联合索引、覆盖索引和优化器行为，才能让查询稳定变快。真正成熟的索引体系不是一次性加完，而是基于慢查询、业务变化和数据增长持续治理。