mysql中临时表的使用与查询性能优化

临时表虽会自动删除，但仍有性能副作用：占用资源、可能落盘、需显式建索引且字段类型须精确控制。

临时表会自动删除，但不意味着它没副作用

mysql 临时表（CREATE TEMPORARY table）只对当前会话可见，连接断开后自动释放，这点常被误认为“完全无害”。实际并非如此：临时表仍占用内存或磁盘空间，且在高并发下可能触发 tmp_table_size 或 max_heap_table_size 限制，导致隐式落盘到磁盘临时表（MyISAM 或 InnoDB），显著拖慢查询。

• 默认情况下，内存临时表上限由 tmp_table_size 和 max_heap_table_size 中的较小值决定，而非两者之和
• 若查询中含 GROUP BY、DISTINCT、ORDER BY 且无法利用索引，MySQL 更倾向用临时表，此时是否为 TEMPORARY 不影响该行为
• SHOW PROCESSLIST 中看到 Creating tmp table 状态，说明已开始建临时表；若变成 Copying to tmp table on disk，性能已受损

什么时候该显式用 CREATE TEMPORARY TABLE？

显式创建临时表不是优化手段，而是逻辑封装或分步计算的必要选择。它适合以下真实场景：

• 复杂报表中需多次引用中间聚合结果（如先算各区域销售额，再与预算表 JOIN），且该中间集超过 10 万行、后续需加索引加速
• 存储过程内需缓存一批 ID 做批量更新，而这些 ID 来自动态构造的子查询（不能用 CTE 替代，因 MySQL 5.7 不支持递归 CTE，8.0+ 虽支持但不可索引）
• etl 过程中需暂存清洗后的脏数据，供多阶段校验，且不希望污染正式库结构

反例：仅为了“让 SQL 看起来更清晰”而把简单子查询拆成临时表——这反而增加解析、创建、销毁开销，且无法被查询优化器重写。

INSERT ... select 到临时表前必须检查字段类型匹配

临时表字段类型若与源查询结果不一致，MySQL 会静默转换（如将 VARCHAR(200) 截断为 VARCHAR(50)），或触发隐式类型转换导致索引失效。更隐蔽的问题是：某些表达式结果无明确类型，MySQL 推导出的宽度远超预期（例如 CONCAT(a, b, c) 可能推导为 VARCHAR(1024)），导致临时表轻易突破内存限制。

CREATE TEMPORARY TABLE tmp_sales AS SELECT    region,   SUM(amount) AS total,   COUNT(*) AS cnt FROM orders  WHERE order_date >= '2024-01-01' GROUP BY region;

上面语句看似安全，但如果 region 是 TEXT 类型，MySQL 可能给临时表的 region 字段分配过宽的 VARCHAR(1024)，哪怕实际值都短于 20 字符。应显式指定宽度：

CREATE TEMPORARY TABLE tmp_sales (   region VARCHAR(64),   total DECIMAL(18,2),   cnt BIGINT ) AS SELECT    CAST(region AS CHAR(64)),   SUM(amount),   COUNT(*) FROM orders  WHERE order_date >= '2024-01-01' GROUP BY region;

临时表上的索引不是可选项，而是必选项

临时表默认无索引，即使你从主表 SELECT * FROM t WHERE id IN (…) 创建，也不会继承原表索引。后续若对临时表做 JOIN、WHERE 或 ORDER BY，没有索引就会全表扫描——而临时表数据已在内存/磁盘上，扫描成本比想象中更高。

• 在 INSERT 完成后立即建索引，不要等到 SELECT 时才想起
• 避免在临时表上建冗余索引：比如已有 (a, b) 复合索引，就别再单独建 a 单列索引
• 若临时表仅用于 IN 子查询（如 WHERE id IN (SELECT id FROM tmp_ids)），优先建 PRIMARY KEY 或 UNIQUE 索引，MySQL 对这类场景优化更好

一个容易被忽略的细节：临时表的索引名可以和正式表重复，但同一会话内不能重复定义同名索引——报错是 Error 1061 (42000): Duplicate key name 'idx_region'，而非“索引已存在”之类友好提示。