跳到主要内容
约 7 分钟4 张卡

执行计划

执行计划是优化器为一条查询选定的执行方式:先读哪张表、用哪个索引、预计扫描多少行、要不要额外排序或建临时表。MySQL 用 EXPLAIN 展示这个计划,用 EXPLAIN ANALYZE 真正执行一次并给出实际耗时和行数。

查询慢的时候,执行计划是最直接的证据。「用了索引」只说明选中了某个索引,至于扫了多少行、循环了多少次、估算准不准,都要从计划里读出来。阅读计划时,需要分清关键字段和访问类型各说明什么,把估计值和实际值对照起来,同时留意哪些成本计划本身看不到。下文以 MySQL 8.0 为准。

1. 面试核心​

先记住这几条,再读正文
  • possible_keys 是候选索引,key 才是优化器实际选中的索引。卡 · 面试核心
  • rows × filtered / 100 估计的是这一步传给后续连接的行数。卡 · 实际统计与估算偏差
  • EXPLAIN ANALYZE 会真正执行语句;一个节点被多次循环调用时,显示的时间和行数是每次循环的平均值。卡 · 实际统计与估算偏差
  • Using filesort 表示当前访问路径的顺序用不上,需要额外排序。排序数据可以留在内存里,名字里的 file 不代表一定写磁盘。
  • eq_ref 对每个外层行组合至多匹配一行,ref 可以匹配多行。卡 · 访问类型

2. 查询条件与访问路径​

看计划之前,先根据查询条件推一推它应该怎么走,再拿计划来对照。下面的查询按用户过滤,再取最早的 20 条记录:

CREATE TABLE orders_plan (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
created_at TIMESTAMP NOT NULL,
amount DECIMAL(10,2) NOT NULL,
KEY idx_user_created (user_id, created_at)
);
EXPLAIN SELECT id, created_at FROM orders_plan
WHERE user_id = 7 ORDER BY created_at LIMIT 20;

user_id = 7 固定了索引 idx_user_created 的第一列,这一段记录正好按 created_at 排好,所以可以顺着索引读前 20 条,不用额外排序。二级索引还带着主键 id,SELECT 要的两列都在索引里,也不用回表。这是理想情况;优化器实际选哪条路径,还取决于数据分布、统计信息和成本估算,所以要看计划确认。

3. 执行计划字段​

读传统 EXPLAIN 输出时,按这个顺序看比较省力:先看实际用了哪个索引、用什么方式访问,再用行数估计判断扫描范围,最后看 Extra 里有没有额外操作。

字段含义
possible_keys能参与查找的候选索引。
key优化器实际选择的索引。
key_len所用索引键的最大长度,单位为字节。
rows单次访问预计检查的行数。
filtered表条件过滤后预计保留的百分比。
Extra覆盖、下推、排序和临时表等信息。

key_len 可以帮你判断联合索引用到了前几列,但要结合列类型、字符集和是否可空来算,可空列还要多算 NULL 标记的开销。它只说明用了哪些键列,扫描了多少行还得看 rows。

Extra 里最常见的几项:

  • Using index:查询需要的信息都能从索引拿到,不用回表。
  • Using index condition:在索引层先过滤,再按需回表取行。两者的执行区别见联合索引与覆盖索引。
  • Using filesort:需要额外排序。内存够用时,排序可以全部在内存中完成。
  • Using temporary:需要内部临时表。它同样不一定落盘,要结合实际的内存和落盘指标判断。

filesort 不是某一种固定排序算法的名字。待排序数据能放进排序缓冲区时可以在内存完成;放不下时,可以先排序一批数据、写出有序段,再归并这些段,这体现了把大问题拆小后合并的思路。ORDER BY ... LIMIT 还可能采用保留少量候选的优化。因此,看到 Using filesort 不能直接断言使用了快速排序、归并排序,或一定发生磁盘 I/O;需要结合优化器跟踪和 Sort_merge_passes 等统计验证。

4. 访问类型​

EXPLAIN 的 type 列是访问类型,描述这张表的行是怎样取到的。下表大致从高效到低效排列,但成本最终还要看扫描量:

类型访问方式
const通过主键或非空唯一键与常量比较,至多取得一行。
eq_ref每个外层行组合通过完整唯一键,至多匹配一行。
ref按非唯一键或索引前缀等值查找,可匹配多行。
range扫描索引中的一个或多个范围。
index扫描索引树,仍需检查大量条目。
ALL扫描整张表。

index 容易被误读成「用上了索引所以很快」,它表示索引扫描,通常仍需检查大量条目;若上层 LIMIT 等条件提前停止,实际不一定读完整棵索引。覆盖查询可以少读数据列,但不能仅凭 index 判断扫描量小。对于嵌套循环连接,内层访问会随外层传入的行组合重复,即使内层是 ref,外层行很多时总成本也会放大。Hash Join 等路径要按实际执行树分析,不能一概套用逐行内层查找。

5. 实际统计与估算偏差​

传统 EXPLAIN 里的行数都是估计值,要知道实际情况,就用 EXPLAIN ANALYZE。它从 MySQL 8.0.18 开始提供,会真正执行查询,并以树的形式展示每个迭代器的实际统计;8.0.21 起可以显式写 FORMAT=TREE。

读这棵树时注意几点:

  • actual time=a..b 的单位是毫秒,a 是返回第一行的时间,b 是这个迭代器全部执行完的时间。
  • 一个节点被循环调用多次时,显示的时间和行数是每次循环的平均值,loops 是调用次数。单次 0.1ms 的内层查找循环 10000 次,就累积到 1 秒左右。用 rows × loops 可以估出总输出行数。
  • 父节点的时间已经包含子节点,把整棵树各节点的时间加起来会重复计算。

因为会真正执行,EXPLAIN ANALYZE 要小心使用。MySQL 8.0 还支持用它分析多表 UPDATE 和 DELETE,这些语句会实际修改数据,实验应该在独立的练习库里做。

估计行数和实际行数差得很远时,优化器可能因此选错索引或连接顺序。排查时先看实际参数和数据倾斜:同一条 SQL 换一个热点值,命中行数可能差几个数量级。再看统计信息是不是过时了,过滤列之间是否相关。ANALYZE TABLE 用来更新表的统计信息,和执行查询并计时的 EXPLAIN ANALYZE 是两回事。

直方图可以补充单列的值分布,但它主要服务于没有索引的列:列上有索引、可以用索引下探(index dive)估算等值条件时,优化器优先用下探的结果,直方图不起作用。它也表达不了多列之间的相关性,所以仍然要用实际查询验证。

6. 使用边界​

计划里出现全表扫描不一定是问题。小表全表扫描省去了索引定位的开销;查询要返回大部分行时,顺序扫描也比逐条回表便宜。比较优化前后的效果,要用相同的参数、数据规模和缓存条件。

执行计划解释的是数据库内部怎样访问数据。普通 EXPLAIN 不测量运行等待;EXPLAIN ANALYZE 的实际耗时可能包含执行期间的等待,但不能单凭一个耗时数字区分锁、I/O 或调度原因,也不代表客户端取数和网络传输的完整耗时。定位这些成本还要结合运行指标。新增索引对写入的代价,由索引设计来评估。

7. 面试题卡片​

8. 参考资料​

来源:Coding 101 原文 · 免费阅读,引用请保留出处。内容使用说明