临时表触发磁盘存储是因为超出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，以下情况仍强制落盘：
免费的开源程序长期以来，为中国的网上交易提供免费开源的网上商店系统一直是我们的初衷和努力奋斗的目标，希望大家一起把MvMmall网上商店系统的免费开源进行到底。2高效的执行效率由资深的开发团队设计，从系统架构,数据库优化，配以通过W3C验证的面页模板，全面提升页面显示速度和提高程序负载能力。3灵活的模板系统MvMmall网店系统程序代码与网页界面分离，灵活的模板方案，完全自定义模板，官方提供免费模
- 临时表中包含
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）暂存聚合结果，尤其适用于低频更新、高频读取的维度统计
临时表不是坏东西，但它的“临时”二字常让人忽略其资源开销。真正难的不是建表，是判断该不该建、建多大、用什么引擎、以及有没有更直接的路绕过去。
