MySQL / 系统课时

使用 EXPLAIN 分析查询

使用 EXPLAIN 分析查询

EXPLAIN 可以在真正优化 SQL 之前告诉你 MySQL 准备怎样访问表、可能使用哪个索引以及预计扫描多少数据。本课通过一个完整查询学会读取最关键字段,并据此判断是否存在明显的全表扫描或额外排序。

学习目标

  • 使用 EXPLAIN 查看查询的访问计划
  • 理解 type、possible_keys、key、rows 和 Extra 的基本含义
  • 根据执行计划发现索引缺失、扫描过多和额外排序等问题

先建立一个可分析的查询

下面创建订单表并建立 (user_id, status, created_at) 组合索引,然后分析一个常见的用户订单查询。

sql
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    INDEX idx_user_status_created (user_id, status, created_at)
);

EXPLAIN
SELECT id, user_id, status, created_at, amount
FROM orders
WHERE user_id = 101
  AND status = 'PAID'
ORDER BY created_at DESC;

预期结果

text
EXPLAIN 返回一行执行计划。possible_keys 应包含 idx_user_status_created;在统计信息和查询条件合适时,key 通常为 idx_user_status_created。rows 的具体值会随表中数据量和统计信息变化。

EXPLAIN 默认不会返回查询数据,而是描述优化器选择的执行方案。因此它适合在修改索引或 SQL 前后做对比。

先看访问方式和实际索引

type 表示 MySQL 计划以什么方式访问表。对初学者来说,可以先把注意力放在是否出现明显的大范围扫描,而不是机械追求某个固定值。

常见访问方式中,const、eq_ref、ref、range 通常意味着能够通过键值或索引范围缩小访问范围;index 表示扫描整个索引;ALL 通常表示全表扫描。访问方式是否合理仍需结合表规模和查询目的判断。

possible_keys 是优化器认为可能可用的索引集合,key 才是最终实际选择的索引。possible_keys 有值但 key 为 NULL,说明存在候选索引,却没有被当前计划采用。

可以故意查询一个没有索引的列进行对比:

sql
EXPLAIN
SELECT *
FROM orders
WHERE amount = 120.00;

预期结果

text
如果 amount 没有可用索引,possible_keys 通常为 NULL,key 通常为 NULL,type 通常为 ALL。rows 的估算值取决于表中实际数据量。

这时不能只看到 ALL 就立即创建索引。若表很小、查询极少执行,或者 amount 选择性很差,新索引未必值得维护成本。

用 rows 判断扫描规模

rows 是优化器根据统计信息估算需要检查的行数,不是执行完成后的精确计数。它适合用来比较不同方案的扫描规模,例如同一条 SQL 在加索引前预计扫描几十万行,加索引后只需扫描几百行,就说明访问路径发生了明显变化。

还要结合过滤条件理解这个数字。扫描行数高但最终返回极少数据,通常意味着大量数据在访问后才被过滤;如果这是高频查询,就值得继续检查索引是否能把过滤条件更早用于定位。

Extra 暴露额外操作

Extra 会显示执行过程中的补充信息。常见的 Using where 表示读取记录后还需要应用条件过滤;Using index 通常表示查询所需数据可以直接从索引取得;Using filesort 表示排序不能直接由当前访问顺序满足,需要额外排序步骤。

例如,如果只有单列 user_id 索引,而查询还要求按 created_at 排序,就可能出现额外排序。可以用下面的片段示例理解这种情况;它依赖已经存在的 orders 表。

sql
ALTER TABLE orders
DROP INDEX idx_user_status_created,
ADD INDEX idx_user (user_id);

EXPLAIN
SELECT id, user_id, status, created_at, amount
FROM orders
WHERE user_id = 101
ORDER BY created_at DESC;

预期结果

text
key 通常可选择 idx_user;Extra 可能出现 Using filesort,表示当前索引顺序不能直接满足 created_at 的排序要求。具体输出可能因数据量、统计信息和 MySQL 版本而不同。

不要把 Using filesort 理解为一定写磁盘文件。它表示 MySQL 需要执行额外排序算法,排序可能在内存中完成,也可能在数据量较大时使用磁盘临时空间。

按固定顺序阅读执行计划

分析简单单表查询时,可以按 type → key → rows → Extra 的顺序阅读:先判断访问范围,再确认实际索引,然后估算扫描规模,最后寻找额外过滤、排序等操作。possible_keys 用来辅助判断是否存在候选索引。

如果发现 ALL、key = NULL、较大的 rows,同时查询又是高频核心请求,就应继续检查过滤列是否有合适索引、组合索引字段顺序是否匹配查询,以及 SQL 是否写出了可被索引利用的条件。

EXPLAIN 的价值不是看到某个字段后立刻下结论,而是把访问方式、索引选择、扫描规模和额外操作连起来判断。优化完成后再次执行同一条 EXPLAIN,确认计划确实发生预期变化,才算完成一次基本的查询分析闭环。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

使用 EXPLAIN 分析查询

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通