17370845950

临时表触发磁盘存储是因为超出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）暂存聚合结果，尤其适用于低频更新、高频读取的维度统计

临时表不是坏东西，但它的“临时”二字常让人忽略其资源开销。真正难的不是建表，是判断该不该建、建多大、用什么引擎、以及有没有更直接的路绕过去。