MySQL EXPLAIN ANALYZE定位排序临时表的排查方法

MySQL 查询同时出现排序和临时表线索时，不要只看到 Using filesort 就把问题归结为“磁盘排序”。更稳妥的排查顺序是：先用普通 EXPLAIN 或 JSON 计划找到 Using filesort、Using temporary，再用 EXPLAIN ANALYZE 实际执行查询，比较每个迭代器的估算行数、实际行数、耗时和循环次数。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/

一句话结论：临时表或排序是否值得优化，要看它前面接收了多少行、实际花了多少时间，以及改写或索引后这些数字是否同步下降。

• 先定位：用传统或 JSON EXPLAIN 找线索，不把 Extra 当成耗时结论。
• 再确认：用 EXPLAIN ANALYZE 观察 Sort、Aggregate、Table scan 等节点。
• 后复测：索引要同时服务过滤和排序，参数调大不能替代错误的访问路径。

先用传统 EXPLAIN 标出排序和临时表线索

先把生产查询缩小到可控数据集，在测试库执行下面的计划检查。示例里的列名只是演示，实际排查时替换成自己的表和条件。

-- 先看传统计划：Extra 用来发现排序和临时表线索
EXPLAIN
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE created_at >= '2026-09-01'
GROUP BY customer_id
ORDER BY order_count DESC
LIMIT 20;

-- 再看 JSON 属性，便于程序化记录同一条计划
EXPLAIN FORMAT=JSON
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE created_at >= '2026-09-01'
GROUP BY customer_id
ORDER BY order_count DESC
LIMIT 20;

传统输出中看到 Using filesort，表示 MySQL 需要额外步骤得到排序结果；它不等于一定发生了磁盘 I/O。看到 Using temporary，说明执行过程中用了临时表策略。JSON 计划可关注 using_filesort 和 using_temporary_table。这一步只负责圈出可疑节点，不能证明它就是最慢的节点。

用 EXPLAIN ANALYZE 对照估算与实际执行

EXPLAIN ANALYZE 会执行被分析的语句，并以 TREE 形式展示迭代器的 cost、估算 rows、实际耗时、实际 rows 和 loops。对上面的聚合查询，可以这样运行：

-- ANALYZE 会真正执行 SELECT，先在测试库确认过滤范围和 LIMIT
EXPLAIN ANALYZE
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE created_at >= '2026-09-01'
GROUP BY customer_id
ORDER BY order_count DESC
LIMIT 20;

重点不是盯着最外层总耗时，而是沿树向下看：如果扫描节点实际 rows 远高于估算值，先怀疑统计信息或过滤条件；如果扫描行数正常但 Sort 节点耗时明显，才继续检查排序键和结果规模；如果 Aggregate 前后的行数膨胀，则应检查分组粒度和连接条件。loops 大时，单次看起来很小的耗时也可能被重复放大。

把慢点映射回 SQL 的过滤、分组和排序条件

这类查询通常有三个容易混淆的原因。第一，WHERE 放行了大量行，排序只是最后暴露问题；第二，ORDER BY 排的是聚合别名，索引不能直接跳过聚合后的排序；第三，分组结果或中间行过宽，临时表的内存和转换成本变高。

MySQL 文档还提醒，内部临时表超过 tmp_table_size 和 max_heap_table_size 的限制时，可能转为磁盘格式；这解释了为什么要结合结果规模和行宽判断，而不是看到临时表就统一加参数。

用索引或查询改写验证改进是否成立

先针对过滤条件建立候选索引，再用同一条查询复测，不要同时改索引、SQL 和服务器参数，否则无法知道收益来自哪里。若查询是按时间过滤且经常按客户聚合，可以先评估 (created_at, customer_id) 这类组合索引；最终列顺序仍要根据选择性、分组方式和其他查询共同决定。

-- 只在评估过写入成本后创建候选索引
CREATE INDEX idx_orders_created_customer
ON orders (created_at, customer_id);

-- 复用完全相同的查询，比较实际 rows、Sort 耗时和总耗时
EXPLAIN ANALYZE
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE created_at >= '2026-09-01'
GROUP BY customer_id
ORDER BY order_count DESC
LIMIT 20;

如果扫描行数明显下降而 Sort 仍存在，不代表索引失败：聚合后的 order_count 仍可能需要排序。相反，如果总耗时下降但实际 rows 没有变化，可能是缓存、并发或数据分布造成的偶然波动，应多次在相近负载下复测。

建立结果记录和生产边界

每次记录 SQL 摘要、数据时间范围、索引版本、总耗时、关键节点的 actual time/rows/loops，以及是否仍出现临时表或排序线索。正式环境的大查询不要直接运行 EXPLAIN ANALYZE：它会执行语句，可能带来真实扫描和资源压力；优先使用脱敏数据、只读副本或缩小范围的复现语句。

常见问题

看到 Using filesort 就一定要删掉吗？不一定。小结果集的额外排序可能很便宜，应该以 ANALYZE 的实际耗时和输入行数决定。

为什么 EXPLAIN ANALYZE 没有传统 Extra 列？它固定使用 TREE 格式，适合看迭代器和实际统计；需要明确的 Using temporary、Using filesort 线索时，再配合普通或 JSON EXPLAIN。