MySQL 索引优化与执行计划分析-mrgr.cn编程知识平台

慢查询是后端最常背的"锅"。一条 SQL 从几毫秒退化到几秒，往往不是数据量增长的错，而是索引没用对。MySQL 的 EXPLAIN 是诊断 SQL 性能的听诊器，但它输出的一堆字段让很多人望而却步。本文用一张订单表的真实案例，逐字段讲透执行计划，并落到索引设计的几条铁律上。

一、索引的本质：用空间换时间的有序结构

InnoDB 的索引是 B+ 树。聚簇索引（主键）的叶子节点存整行数据，二级索引的叶子节点存主键值。这意味着通过二级索引查到的不是数据本身，而是主键，还需要"回表"到聚簇索引取数据。理解"回表"，是理解索引优化的起点。

索引优化的核心矛盾：建索引能加速查询，但每多一个索引就多一份写入开销与存储。目标不是"索引越多越好"，而是"让该用的查询用上索引，且尽量不回表"。

聚簇索引：按主键组织的 B+ 树，叶子节点存整行，一张表只有一个。
二级索引：按非主键列组织的 B+ 树，叶子节点存主键，查询需回表。
覆盖索引：查询所需的列全在二级索引里，无需回表，性能最佳。

二、EXPLAIN：执行计划的听诊器

在 SQL 前加 EXPLAIN，MySQL 会返回执行计划而非执行结果。重点看以下几个字段。

EXPLAIN SELECT * FROM orders
WHERE user_id = 10086 AND status = 'PAID'
ORDER BY create_time DESC LIMIT 20;

-- 输出关键列：
-- id | select_type | table  | type | key          | rows | Extra
-- 1  | SIMPLE      | orders | ref  | idx_user_status | 1200 | Using where; Using filesort

2.1 type：访问类型（最重要）

type 反映 MySQL 找数据的方式，从快到慢大致为：system > const > eq_ref > ref > range > index > ALL。

const/eq_ref：主键或唯一索引等值查询，最快。
ref：普通二级索引等值查询，常见且可接受。
range：索引范围扫描（>、<、BETWEEN、IN），需要警惕扫描行数。
index：扫描整棵索引树（比扫描全表好，但仍偏慢）。
ALL：全表扫描，最慢，生产环境基本要消灭。

2.2 key 与 rows

key 是实际使用的索引，rows 是 MySQL 估算要扫描的行数。rows 越小越好，但它是个估算值（基于统计信息），不一定精确。如果 key 是 NULL，说明没用索引，多半是全表扫描。

2.3 Extra：额外信息（藏着优化空间）

Using index：覆盖索引，无需回表，理想状态。
Using where：在 server 层过滤，通常伴随回表。
Using filesort：无法用索引完成排序，需额外排序，需优化。
Using temporary：用了临时表（如 GROUP BY、DISTINCT），需关注。

判断 SQL 健康度的速查口诀：type 别是 ALL，Extra 别有 filesort/temporary，rows 越小越好，最好能看到 Using index。

三、联合索引与最左前缀原则

联合索引是把多列合并成一个 B+ 树，按"列顺序"排序。最左前缀原则：查询必须从联合索引最左列开始，按顺序使用，才能走索引。一旦中间跳过某列或某列用了范围查询，其右的列就用不上索引了。

-- 联合索引 idx_user_status_time(user_id, status, create_time)

-- ✅ 能用全索引：按最左前缀连续使用
WHERE user_id = 10086 AND status = 'PAID' AND create_time > '2026-01-01'

-- ✅ 能用前两列
WHERE user_id = 10086 AND status = 'PAID'

-- ⚠️ 只能用 user_id，status 之后断掉（范围导致）
WHERE user_id = 10086 AND create_time > '2026-01-01'

-- ❌ 用不上索引（缺最左列 user_id）
WHERE status = 'PAID' AND create_time > '2026-01-01'

3.1 联合索引的列顺序设计

列顺序决定了哪些查询能用上索引。设计原则：

等值查询的列放前面，范围查询的列放后面（避免范围查询截断后续列）。
区分度高的列放前面，过滤掉更多行。
排序列尽量纳入索引，避免 filesort。

上文案例中，ORDER BY create_time 出现了 Using filesort，把 create_time 纳入联合索引 idx_user_status_time(user_id, status, create_time)，排序就能直接走索引，filesort 消失。

四、索引失效的常见场景

对索引列做函数/运算：WHERE YEAR(create_time)=2026 失效，改 WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01'。
隐式类型转换：列是 varchar，查询传了数字 WHERE order_no = 123，MySQL 会把列转数字导致全表扫描。保持类型一致。
LIKE 以 % 开头：LIKE '%abc' 无法用索引；LIKE 'abc%' 可以。
OR 两边不全有索引：一边没索引就退化全表。两边都有索引才会用 Index Merge。
不满足最左前缀：联合索引跳过最左列。

-- ❌ 失效：对列套函数
WHERE DATE(create_time) = '2026-07-05'
-- ✅ 改写为范围
WHERE create_time >= '2026-07-05 00:00:00'
  AND create_time <  '2026-07-06 00:00:00'

-- ❌ 失效：隐式转换（order_no 是 varchar）
WHERE order_no = 20260705001
-- ✅ 加引号
WHERE order_no = '20260705001'

五、覆盖索引：消灭回表

回表是二级索引查询的隐性成本：先查二级索引拿主键，再回聚簇索引取整行。如果一个高频查询只需要少量列，把这些列都建进联合索引，就能"覆盖"查询，完全跳过回表。代价是索引变宽、写入略慢，但对高频读收益巨大。

-- 查询只需要 user_id 和 amount
SELECT user_id, amount FROM orders WHERE user_id = 10086;

-- 建联合索引 idx_user_amount(user_id, amount)
-- Extra 出现 Using index = 覆盖索引成功，无需回表

六、慢查询治理流程

开启慢查询日志：设 long_query_time（如 0.5s），收集慢 SQL。
EXPLAIN 分析：看 type、key、rows、Extra，定位是否走索引、是否回表、是否排序。
改写 SQL 或加索引：消除函数运算、修正类型、按最左前缀建联合索引。
验证收益：对比优化前后的 rows 与耗时，确认无回归。
持续监控：慢查询要长期采集，防止业务变更引入新的慢 SQL。

索引优化不是一次性的活，而是持续工程。表结构、数据量、查询模式都在变，今天的最优索引明天可能就失效。把"慢查询监控 + 定期 EXPLAIN 巡检"纳入常态化运维，才是治本之道。

结语

读懂 EXPLAIN，索引优化就入门了一半。type 看访问方式、rows 看扫描量、Extra 看是否有额外开销，三者结合就能定位绝大多数慢查询。再叠加最左前缀、覆盖索引、避免索引失效这几条铁律，多数 SQL 性能问题都能解决。mrgr.cn 数据库组会持续分享索引与执行计划的实战案例，欢迎在问答社区贴出你的慢 SQL 一起分析。

原创文章

MySQL 索引优化与执行计划分析