临时表从4.7秒到0.12秒：不是所有Using temporary都需要优化

大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！

技术群里经常看到这样的对话：

A：“我这条SQL执行计划里有Using temporary，是不是要优化一下？”
B：“有临时表肯定慢啊，赶紧加索引！”
C：“不一定吧，我见过很多带Using temporary的查询跑得也挺快的。”

三个人说的都有道理，但都不完整。

Using temporary不等于“慢” 。MySQL创建临时表分三种情况：内存临时表、磁盘临时表、优化器临时表。只有磁盘临时表才是真正的性能杀手，内存临时表的开销其实没那么大。

今天把临时表这件事彻底拆开讲清楚。

一、临时表的三种形态

1. 内存临时表（Memory Temporary Table）

MySQL优先在内存中创建临时表，使用MEMORY存储引擎。数据全部在内存中操作，没有磁盘I/O。

触发条件：临时表数据量小于tmp_table_size和max_heap_table_size的限制。

性能影响：较小。内存操作比磁盘快几个数量级。

2. 磁盘临时表（On-Disk Temporary Table）

当内存临时表的数据量超过了tmp_table_size或max_heap_table_size的限制，MySQL会自动将临时表从内存转到磁盘。磁盘临时表使用InnoDB引擎（MySQL 8.0默认）或MyISAM引擎。

触发条件：临时表数据量超过阈值；或者查询中包含了TEXT/BLOB等无法在内存中处理的字段类型。

性能影响：巨大。磁盘I/O比内存操作慢几十到几百倍。Created_tmp_disk_tables状态变量是核心监控指标。

3. 优化器临时表（派生表/物化临时表）

优化器为了优化查询而主动创建的临时表，比如将子查询物化为临时表、将DISTINCT的结果暂存等。性能影响取决于具体场景。

二、什么时候会触发临时表？

以下场景MySQL可能创建临时表：

GROUP BY的列没有可用索引时，MySQL必须先排序才能分组，而排序需要空间，于是建临时表。

三、怎么判断临时表是内存还是磁盘？

方法一：看状态变量

执行查询前后对比Created_tmp_tables和Created_tmp_disk_tables：

SHOW STATUS LIKE 'Created_tmp%';
-- 执行查询
SHOW STATUS LIKE 'Created_tmp%';

如果Created_tmp_disk_tables增加了，说明临时表落盘了——这是真正的性能警报。如果Created_tmp_disk_tables / Created_tmp_tables > 5%，说明多数临时表已落盘，内存不够用。

方法二：看EXPLAIN FORMAT=TREE（MySQL 8.0+）

EXPLAIN FORMAT=TREE会显示更详细的执行信息，包括temporary table相关的实际行数。

四、4种优化方案

方案一：加合适的索引

大部分Using temporary的问题，一根联合索引就能解决。GROUP BY列必须是某个索引的最左前缀。如果WHERE中有范围查询（>、<、BETWEEN），范围条件涉及的列必须放在索引后面，GROUP BY列必须前置。

方案二：调大临时表内存参数

修改tmp_table_size和max_heap_table_size，让更多临时表留在内存中。两个参数必须同步调整，临时表能否留在内存取决于两者中的较小值。生产环境建议设为64MB到256MB，避免默认16MB过小。

不能盲目调大——调得太大可能导致内存不足，影响整个数据库稳定性。

方案三：减少查询字段

SELECT *会让临时表单行数据变大，更容易触发落盘。只查询必要的字段，减小临时表单行数据大小。

方案四：改写SQL

把大范围聚合拆到离线任务或汇总表。比如日报表可以用定时任务提前计算好，而不是每次实时聚合。

五、一个真实案例

某报表系统，一条GROUP BY查询跑了4.7秒。

SELECT user_id, SUM(amount) as total_amount, COUNT(*) as tx_count
FROM payment_record
WHERE created_at >= '2024-06-01' AND created_at < '2024-07-01'
GROUP BY user_id
ORDER BY total_amount DESC LIMIT 20;

EXPLAIN显示Using temporary; Using filesort，临时表落盘了——临时表数据量超过tmp_table_size（默认16MB），写到了磁盘上。500万行数据，一个月范围80万行，GROUP BY跑了4.7秒。

优化方案：在(user_id, created_at)上建了一个覆盖索引。GROUP BY的字段变成了索引的最左前缀，优化器可以直接利用索引有序性完成分组，不再需要临时表。

优化后：0.12秒。从4.7秒到0.12秒，提升了近40倍。

六、小结

Using temporary不等于“慢”——内存临时表的开销其实没那么大。真正需要警惕的是磁盘临时表。看到Using temporary先别急着加索引，先确认临时表是在内存还是磁盘、数据量有多大、查询本身是否在可接受范围内。大部分filesort + temporary的问题，一根设计合理的联合索引就能解决。优化之前，先搞清楚问题到底出在哪。

小耶在手，SQL 不愁

还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~