临时表触发磁盘存储是因为超出 tmp_table_size 与 max_heap_table_size 较小值，或含 TEXT/BLOB/JSON、字段过宽、UNION 类型不一致、显式加锁等强制落盘场景。
临时表 为什么 会触发 On disk temporary table
MySQL 在执行某些查询时会自动创建内部临时表，比如含 GROUP BY、DISTINCT、ORDER BY 配合非索引字段、或子查询结果集较大时。一旦临时表超出 tmp_table_size 和 max_heap_table_size 中的较小值，就会从内存（MEMORY 引擎）退化为磁盘（MyISAM 或 InnoDB），这时你会在 SHOW STATUS LIKE 'Created_tmp_disk_tables' 中看到计数上升——这是性能拐点。
关键不是“用了临时表”，而是“被迫写磁盘”。常见诱因包括：
-
SELECT DISTINCT字段未被联合索引覆盖，且结果行数多 -
ORDER BY的字段不在WHERE条件使用的索引中，导致无法利用索引排序 - 聚合查询中
GROUP BY字段类型过大（如长VARCHAR(500)），或包含TEXT/BLOB列 - 连接查询返回宽表（列数多 + 单行大），内存临时表容量不够
CREATE TEMPORARY TABLE 手动建表要注意什么
显式创建临时表能绕过优化器的自动决策，但容易误用。它只对当前会话可见，断开即销毁，但生命周期和锁行为仍需谨慎对待。
实操建议：
- 优先用
ENGINE=InnoDB，而非默认的MyISAM：避免锁表、支持事务、崩溃恢复更稳 - 显式定义主键或唯一索引，尤其当后续要
JOIN或WHERE查询时；否则全表扫描不可避免 - 避免
SELECT * INTO TEMPORARY TABLE：只选必要字段，压缩行宽，减少磁盘 / 内存压力 - 建表后立刻
ANALYZE TABLE（对临时表有效），让优化器获取准确行数统计
CREATE TEMPORARY TABLE temp_user_stats (user_id BIGINT PRIMARY KEY, login_count INT, last_login DATETIME) ENGINE=InnoDB;哪些 SQL 模式会让临时表无法走内存
即使调大了 tmp_table_size，以下情况仍强制落盘：
- 临时表中包含
TEXT、BLOB、JSON或超过 512 字节 的VARCHAR字段（MySQL 8.0+ 对VARCHAR宽度限制更松，但仍受行格式影响） - 使用了
UNION且各分支字段类型不一致，触发 隐式转换 后宽度膨胀 - 查询中存在
SELECT …… FOR UPDATE或其他显式加锁操作（临时表会被降级为磁盘表以支持锁管理） - 启用了
innodb_file_per_table=OFF且临时表由 InnoDB 管理时，可能因系统表空间碎片导致分配失败而回退
验证方式：执行完查询后立即查 SHOW STATUS LIKE 'Created_tmp%'，对比 Created_tmp_tables 和 Created_tmp_disk_tables 的差值。
替代临时表的轻量方案
很多场景下，临时表是“习惯性解法”，但代价高。可考虑这些更低开销的路径：
- 用
CTE（WITH 子句）替代简单中间结果：MySQL 8.0+ 支持物化控制（MATERIALIZED/NOT MATERIALIZED），比临时表更可控 - 将多次
INSERT …… SELECT拆成单次聚合 + 应用层分批处理，避免大临时表累积 - 对高频中间计算，改用持久表 +
TRUNCATE+ 索引预热，比反复建删临时表更稳定 - 用应用层缓存（如 Redis）暂存聚合结果，尤其适用于低频更新、高频读取的维度统计
临时表不是坏东西，但它的“临时”二字常让人忽略其资源开销。真正难的不是建表，是判断该不该建、建多大、用什么引擎、以及有没有更直接的路绕过去。
