执行计划
执行计划是优化器为一条查询选定的执行方式:先读哪张表、用哪个索引、预计扫描多少行、要不要额外排序或建临时表。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 原文 · 免费阅读,引用请保留出处。内容使用说明