Explain 执行计划解读

概述

EXPLAIN 是 MySQL 提供的最重要的性能诊断工具。它在 SQL 真正执行之前，模拟优化器生成 SQL 的执行计划，揭示查询是如何使用索引、如何连接表、是否有临时表和文件排序等关键信息。掌握 EXPLAIN 输出各字段的含义，是将经验式调优转化为精准打击的前提。

核心原理

执行计划各字段详解

type 字段 —— 访问类型（性能由好到差）

type 描述了 MySQL 如何访问表中的数据，是评估查询质量的首要指标。

type	名称	含义	性能
`system`	系统表	表仅有一行（system 表），常数级读取	★★★★★
`const`	常量	最多匹配一行，通过主键/唯一索引一次性定位	★★★★★
`eq_ref`	等值引用	前一个表的每一行，当前表通过唯一索引匹配一行	★★★★☆
`ref`	引用	通过非唯一索引或主键的前缀匹配多行	★★★☆☆
`range`	范围	索引范围扫描，如 BETWEEN、IN、>、<	★★☆☆☆
`index`	全索引扫描	遍历整个索引树，不走表数据	★★☆☆☆
`ALL`	全表扫描	逐行扫描表数据，未使用任何索引	☆☆☆☆☆

graph LR
    A["system"] -->|"最优"| B["const"]
    B --> C["eq_ref"]
    C --> D["ref"]
    D --> E["range"]
    E --> F["index"]
    F --> G["ALL"]
    G -->|"最差"| H["需要优化"]

    style A fill:#90ee90
    style B fill:#90ee90
    style C fill:#90ee90
    style D fill:#ffeb99
    style E fill:#ffeb99
    style F fill:#ffcc99
    style G fill:#ff9999
    style H fill:#ff6666

[!TIP] 面试常考点为什么 index 比 ALL 好？index 虽然是全表扫描级别，但它只扫描索引树（通常为有序排列，可利用顺序 IO），而 ALL 需要扫描数据行（随机 IO）。所以 index < ALL。

select_type 字段 —— 查询类型

select_type	含义
`SIMPLE`	简单查询，不包含子查询或 UNION
`PRIMARY`	最外层查询（配合 SUBQUERY/UNION 等出现）
`SUBQUERY`	子查询中的 SELECT（非 FROM 子句中）
`DERIVED`	派生表（FROM 子句中的子查询）
`UNION`	UNION 中的第二个及以后的 SELECT
`UNION RESULT`	UNION 的结果集

-- 示例：复杂查询的 select_type
SELECT * FROM orders o
WHERE o.user_id IN (
    SELECT u.id FROM users u WHERE u.status = 1
)
UNION
SELECT * FROM orders_archive WHERE archived = 1;
-- 输出行的 select_type 依次为：PRIMARY / SUBQUERY / UNION / UNION_RESULT

possible_keys vs key vs key_len

possible_keys：优化器认为可能用到的索引列表（只是"候选"，不代表最终会选用）
key：实际选择的索引
key_len：使用的索引长度（字节数），反映索引列参与的程度

-- 复合索引 (name VARCHAR(64), age INT)
-- utf8mb4 每个字符 4 字节，name 的 key_len = 64 * 4 = 256 字节
-- 加上变长标记 1 字节 + NULL 标记 1 字节 = 258
-- 如果 key_len = 258，说明用了 name 列
-- 如果 key_len = 262 (= 258 + 4)，说明同时用了 name 和 age

SELECT * FROM users WHERE name = 'Alice' AND age = 25;
-- key: idx_name_age, key_len: 262 → 两个列都用上了

rows × filtered —— 行数估算

rows：优化器估算的需要扫描的行数（越小越好）
filtered：按表条件筛选后，留存行的百分比（0~100）
rows × filtered% ≈ 实际需要处理的行数

[!WARNING] 估算偏差 rows 和 filtered 是统计采样估算值，并非精确计数。如果表的统计信息过期，可能导致严重偏差。执行 ANALYZE TABLE t; 可以更新统计信息。

Extra 常见值解析

Extra 值	含义	建议
`Using where`	存储引擎返回数据后，Server 层再做 WHERE 过滤	正常，但如果没走索引说明需要加索引
`Using index`	覆盖索引，无需回表	最好的情况
`Using temporary`	使用了临时表解决查询	通常出现在 GROUP BY 或 DISTINCT，需要优化
`Using filesort`	需要额外排序，无法利用索引顺序	考虑加索引消除排序
`Using index condition`	索引下推（ICP），在存储引擎层过滤	好消息，说明 MySQL 自动优化了
`Using sort_union()` / `Using union()`	使用了多个索引合并后再排序	考虑改造成单索引
`Impossible where`	WHERE 条件永远不成立	检查 SQL 逻辑
`Select tables optimized away`	最小化优化，无需访问表	最优情况

-- 看到 WARNING 时的排查思路
EXPLAIN SELECT * FROM orders
WHERE YEAR(created_at) = 2025;
-- Extra: Using where; Using filesort
-- 问题：YEAR() 函数作用于字段 → 无法使用索引
-- 优化：改用范围查询
-- WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'

代码示例

-- 典型 EXPLAIN 输出解读
EXPLAIN SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 1
ORDER BY o.created_at DESC
LIMIT 10;

-- 解读要点：
-- 第一行：u 表 → select_type=PRIMARY, type=ref (status 上有索引)
--         → key=idx_status, rows≈500, filtered=100%
-- 第二行：o 表 → type=ref (user_id 是外键索引), eq_ref
--         → 对每个 u 的行，通过 user_id 精确匹配一条订单
-- Extra: Using index condition → ICP 生效
--          Using filesort → created_at 上没有合适索引，需额外排序
-- 优化方向：给 orders(created_at) 建索引，消除 filesort

实践场景

场景一：日常慢查询排查

开启慢查询日志（long_query_time=1）
对慢 SQL 加 EXPLAIN 前缀执行
检查 type 是否退化到 ALL 或 index
关注 Extra 中的 Using temporary 和 Using filesort
针对问题点调整索引或改写 SQL

场景二：判断索引是否被有效利用

-- 如果发现某张表的 key 列为 NULL，说明该查询完全没有使用索引
-- 检查 possible_keys 是否有可选索引但未被选中
-- 如果是，可能是统计信息过时，执行 ANALYZE TABLE

ANALYZE TABLE orders;

场景三：JOIN 查询优化

小表驱动大表：JOIN 中较小的表放在前面（EXPLAIN 靠上的表先被执行）
确保 JOIN 条件两侧都有对应的索引
避免在多列上使用 OR 连接 JOIN 条件，这会迫使优化器退化为全表扫描

6.7 KiB Raw Permalink Blame History Unescape Escape