在 MySQL 的日常运维和面试中，临时表是一个绕不开的话题。很多开发者对它的印象停留在“用 CREATE TEMPORARY TABLE 创建的表”，但实际上，MySQL 在你不经意间就可能创建了大量临时表，甚至因此引发磁盘 I/O 飙升、查询变慢等性能问题。本文将从临时表的类型、产生场景、识别方法以及优化手段几个方面展开，帮助你彻底理解这个知识点。
一、临时表的两种类型
MySQL 中的临时表分为两类：
1. 用户显式创建的临时表
通过 CREATE TEMPORARY TABLE 语句创建，仅在当前会话可见，会话结束后自动删除。这类临时表通常用于存储中间结果集，在复杂业务逻辑或存储过程中比较常见。
2. 内部临时表（Internal Temporary Table）
这是本文的重点。MySQL 在执行某些 SQL 时，会自动在内存或磁盘上创建临时表来辅助完成查询。用户无法直接看到它，但它对性能的影响往往更大。
内部临时表又有两种存储形式：
- 内存临时表：使用 MEMORY 存储引擎，基于内存，速度快，但受
tmp_table_size和max_heap_table_size限制。 - 磁盘临时表：当内存临时表超过限制，或包含 BLOB/TEXT 等大字段时，会转为磁盘临时表。MySQL 5.7 及之前默认使用 MyISAM，8.0 之后默认使用 InnoDB。
二、内部临时表的产生场景
理解哪些 SQL 会触发内部临时表，是优化的前提。常见场景包括：
1. 使用 UNION 查询
当执行 UNION（而非 UNION ALL）时，MySQL 需要去重，会创建内部临时表来合并结果集。例如：
SELECT id FROM t1 UNION SELECT id FROM t2;
2. 使用 GROUP BY
如果 GROUP BY 的列没有合适的索引，MySQL 无法利用索引完成分组，就会创建临时表来分组和聚合。尤其是 MySQL 5.7 之前，GROUP BY 默认会隐式排序，进一步加重开销。
3. 使用 ORDER BY 与 GROUP BY 组合
当 ORDER BY 的列与 GROUP BY 的列不一致，或者排序无法利用索引时，也可能产生临时表。
4. 使用 DISTINCT
SELECT DISTINCT 在无法通过索引去重时，会借助临时表完成去重操作。
5. 多表关联中的派生表
子查询出现在 FROM 子句中（派生表），MySQL 可能会将其物化为临时表。例如：
SELECT * FROM (SELECT id, name FROM users WHERE age > 18) AS t;
6. 使用窗口函数或 CTE（MySQL 8.0+）
窗口函数和公用表表达式（CTE）在执行过程中，也常常需要临时表来存储中间结果。
7. INSERT … SELECT
当目标表和源表是同一张表时，MySQL 会创建临时表来避免边读边写的数据不一致问题。
三、如何识别临时表
MySQL 提供了几个关键状态变量和工具：
SHOW STATUS LIKE 'Created_tmp%';
Created_tmp_tables：创建的内存临时表总数。Created_tmp_disk_tables：创建的磁盘临时表总数。Created_tmp_files：创建的临时文件数。
如果 Created_tmp_disk_tables 占 Created_tmp_tables 的比例较高，说明大量临时表落盘，需要重点关注。
此外，通过 EXPLAIN 查看执行计划时，如果 Extra 列出现 Using temporary，就说明该查询使用了内部临时表。
在 MySQL 8.0 中，还可以通过 performance_schema 中的 memory_summary_by_thread_by_event_name 等表进一步定位。
四、优化方法
针对内部临时表带来的性能问题，可以从以下几个层面优化：
1. 优化 SQL 与索引
- 为 GROUP BY、ORDER BY、DISTINCT 的列建立合适索引，让 MySQL 能利用索引完成分组和排序，避免临时表。
- 尽量使用 UNION ALL 替代 UNION，如果业务上不需要去重，可以省去临时表开销。
- 避免在 WHERE 子句中对字段做函数操作，否则索引失效，可能间接导致临时表。
- 拆分复杂查询，将派生表改为 JOIN，或分步处理。
2. 调整内存临时表大小
适当增大 tmp_table_size 和 max_heap_table_size（两者取较小值生效），可以让更多临时表留在内存中：
tmp_table_size = 64M
max_heap_table_size = 64M
但要注意，这两个参数是会话级别的，设置过大会导致内存占用过高，需根据服务器内存和并发量权衡。
3. 避免 BLOB/TEXT 字段进入临时表
MEMORY 引擎不支持 BLOB/TEXT 类型，一旦查询涉及这些字段，临时表会直接落盘。可以通过只 SELECT 必要字段、将大字段拆分到单独表等方式规避。
4. 使用磁盘临时表的优化
MySQL 8.0 中，磁盘临时表默认使用 InnoDB，可以通过 internal_tmp_disk_storage_engine（5.7）或相关参数调整。同时，确保 tmpdir 指向高速磁盘（如 SSD），并监控临时目录空间。
5. 监控与告警
将 Created_tmp_disk_tables 的增长率纳入监控，当磁盘临时表比例超过阈值（如 10%）时触发告警，及时排查慢查询。
五、面试常见追问
-
问：内存临时表和磁盘临时表如何选择？
答：由tmp_table_size和max_heap_table_size的较小值决定，超过则落盘；另外，包含 BLOB/TEXT 时直接落盘。 -
问：UNION 和 UNION ALL 的区别？
答：UNION 会去重并排序，产生临时表；UNION ALL 直接合并，不产生临时表，性能更好。 -
问：如何判断一个查询是否用了临时表？
答：通过 EXPLAIN 的 Extra 列查看Using temporary，或对比Created_tmp_tables状态变量的变化。
六、总结
临时表是 MySQL 优化器为了实现复杂查询而采用的辅助手段，本身并非“坏东西”，但频繁的磁盘临时表会显著拖慢查询。掌握其产生场景，结合 EXPLAIN 和状态变量进行定位，再通过索引优化、参数调整和 SQL 重写来减少临时表的使用，是提升 MySQL 性能的重要一环。在面试中，能清晰说出“哪些场景会产生临时表”以及“如何优化”，往往就能体现出你对 MySQL 执行机制的深入理解。
未经允许不得转载：任鹏个人博客 » MySQL 中的临时表产生场景与优化方法
