MySQL / 系统课时
使用 EXPLAIN 分析查询
使用 EXPLAIN 分析查询
EXPLAIN 可以在真正优化 SQL 之前告诉你 MySQL 准备怎样访问表、可能使用哪个索引以及预计扫描多少数据。本课通过一个完整查询学会读取最关键字段,并据此判断是否存在明显的全表扫描或额外排序。
学习目标
- 使用
EXPLAIN查看查询的访问计划 - 理解
type、possible_keys、key、rows和Extra的基本含义 - 根据执行计划发现索引缺失、扫描过多和额外排序等问题
先建立一个可分析的查询
下面创建订单表并建立 (user_id, status, created_at) 组合索引,然后分析一个常见的用户订单查询。
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;预期结果
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,说明存在候选索引,却没有被当前计划采用。
可以故意查询一个没有索引的列进行对比:
EXPLAIN
SELECT *
FROM orders
WHERE amount = 120.00;预期结果
如果 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 表。
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;预期结果
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,确认计划确实发生预期变化,才算完成一次基本的查询分析闭环。