MySQL 临时表是怎么产生的？如何优化？

简化版

MySQL 在某些排序、分组、去重、派生表和复杂查询中会使用临时表；优化思路是减少参与计算的数据量、让索引支持过滤和分组排序、避免大字段进入临时表，并拆解过度复杂 SQL。

详细版

EXPLAIN 中看到 Using temporary，通常表示 MySQL 需要中间结果来完成查询。常见场景包括 GROUP BY、DISTINCT、ORDER BY 与 GROUP BY 不一致、派生表、复杂 UNION 等。

临时表不一定落磁盘。小的中间结果可能在内存里，大字段、结果过大或超过内存限制时可能变成磁盘临时表，性能明显下降。

优化时不要只盯参数。更重要的是减少中间结果行数和列宽，避免 SELECT * 进入分组排序，设计合适索引，并让 SQL 的过滤尽量提前发生。

完整版教学

一、临时表的作用是什么

临时表是 MySQL 执行复杂 SQL 时保存中间结果的工作区。

SELECT city, COUNT(*) AS cnt
FROM users
GROUP BY city
ORDER BY cnt DESC;

数据库需要先按城市统计出中间结果，再按 cnt 排序。这个中间结果就可能通过临时表承载。

记忆钩子：临时表是数据库算复杂结果时的“草稿纸”，草稿太大就会慢。

二、哪些 SQL 容易产生临时表

常见触发场景包括分组、去重、复杂排序、派生表、UNION 去重等。

SELECT DISTINCT city FROM users;

SELECT city, COUNT(*)
FROM users
GROUP BY city
ORDER BY COUNT(*) DESC;

SELECT *
FROM (SELECT * FROM orders WHERE amount > 100) t
WHERE t.status = 'paid';

场景	为什么需要中间结果
`GROUP BY`	每组聚合后再输出
`DISTINCT`	去重需要记录已见组合
`ORDER BY` 不匹配索引	需要额外排序
派生表	子查询结果需要被外层使用
`UNION`	默认要去重

UNION ALL 不去重，通常比 UNION 少一步去重成本。

三、内存临时表和磁盘临时表差异

临时表可能在内存里，也可能落到磁盘。磁盘临时表通常比内存临时表慢很多。

可能导致落盘的因素包括：中间结果太大、包含大字段、超过内存临时表限制、某些类型不适合内存表。

示例：

SELECT user_id, remark, COUNT(*)
FROM orders
GROUP BY user_id, remark;

如果 remark 是长文本，把它放进分组中间结果会让临时表变宽。100 万行每行多 1KB，中间数据量就可能接近 1GB。

所以优化临时表时，列宽和行数同样重要。

四、如何通过索引减少临时表

如果索引顺序能支持分组或排序，MySQL 就可能少做额外临时表。

CREATE INDEX idx_status_city ON users(status, city);

SELECT city, COUNT(*)
FROM users
WHERE status = 'active'
GROUP BY city;

status 先等值过滤，city 在索引中有序，数据库可以更高效地按城市聚合。

如果查询写成：

SELECT city, COUNT(*)
FROM users
GROUP BY city
ORDER BY created_at;

分组字段和排序字段不一致，通常更容易产生额外排序或临时表。业务上要确认排序规则是否真的必要。

五、减少中间结果比调参数更重要

很多人看到临时表慢，第一反应是调大临时表内存参数。但如果 SQL 需要处理 5000 万行，参数只是缓解，不能改变根因。

更有效的优化路径：

1. WHERE 条件提前过滤
2. 只选择必要列
3. 避免大字段参与 GROUP BY / DISTINCT
4. 用合适索引支持过滤、分组、排序
5. 必要时拆成阶段性汇总表

例如日报统计可以先按天增量汇总到统计表，而不是每次从订单明细表全量聚合。

六、派生表和 CTE 要看是否物化

派生表或 CTE 在某些情况下会被优化器合并，在某些情况下会物化成临时表。

SELECT *
FROM (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  GROUP BY user_id
) t
WHERE t.total > 1000;

这里必须先得到每个用户的总金额，再过滤 total，中间结果可能很大。如果订单表 1 亿行、用户 1000 万，派生表本身就是重查询。

优化可能是增加时间过滤、预聚合、或按业务拆分计算，而不是简单把子查询换成 CTE。

七、常见误区与追问

误区：临时表一定是手动创建的表。 执行复杂查询时 MySQL 会自动创建内部临时表。
误区：Using temporary 一定不可接受。 小数据量低频查询可以接受，关键看规模和耗时。
误区：调大内存参数就能解决所有临时表问题。 SQL 处理数据量和列宽才是根本。
误区：UNION 和 UNION ALL 性能一样。 UNION 默认去重，可能需要临时表；UNION ALL 直接合并结果。
追问：如何减少磁盘临时表？ 减少行数和列宽，避免大字段，优化索引，必要时预聚合。
追问：怎么看是否用了临时表？ 用 EXPLAIN 看 Extra 中的 Using temporary，再结合慢查询和状态指标判断影响。

八、加强记忆

临时表题用“中间结果”理解。分组、去重、复杂排序都可能需要草稿纸；优化重点是让草稿更小、更窄，最好用索引顺序减少草稿。参数能帮一点，SQL 形态和数据规模才是关键。

简化版

详细版