临时表如何优化查询

在关系型数据库中，临时表（Temporary Table）常用于拆分复杂查询、缓存中间结果、减少重复计算。合理使用可以显著提升性能，但用不好反而会成为瓶颈。下面从什么时候用、怎么建、怎么用、以及优化技巧几个维度说明。

一、什么时候适合用临时表优化查询

✅ 适合场景：

• 复杂报表 / 多表 JOIN 后还要多次使用结果
• 同一子查询在 SQL 中重复出现多次
• 需要分步处理（先过滤、再聚合、再关联）
• ETL / 批处理任务
• 游标或存储过程中中间结果较大

❌ 不适合场景：

• 简单查询（直接写 SQL 更快）
• 数据量极小（内存开销不划算）
• 高并发短事务（临时表有创建/销毁成本）

二、临时表的基本优化原则

1️⃣ 只存“必要字段”

❌ 错误：

SELECT * INTO #tmp FROM orders;

✅ 正确：

SELECT order_id, user_id, amount
INTO #tmp
FROM orders
WHERE create_time >= '2024-01-01';

➡ 减少 IO、内存、后续排序成本

2️⃣ 建索引（非常关键）

临时表默认没有索引，大表会全表扫描。

CREATE INDEX ix_tmp_user ON #tmp(user_id);

或（MySQL）：

CREATE TEMPORARY TABLE tmp (
  id INT,
  user_id INT,
  INDEX ix_user(user_id)
);

✅ 在以下情况一定要加索引：

• JOIN 字段
• WHERE 过滤字段
• GROUP BY / ORDER BY 字段

3️⃣ 先过滤，再进临时表

❌：

INSERT INTO #tmp SELECT * FROM big_table;

✅：

INSERT INTO #tmp
SELECT *
FROM big_table
WHERE status = 1
  AND date > '2024-01-01';

➡ 临时表越小，后续越快

三、临时表 vs 其他方案对比

四、不同数据库的临时表优化建议

MySQL

• 尽量使用 MEMORY 引擎（小数据）

CREATE TEMPORARY TABLE tmp ENGINE=MEMORY ...

• 避免 TEXT/BLOB（会落盘）
• 临时表过大 → 调大 tmp_table_size

SQL Server

• #temp 局部临时表优于 @table
• 统计信息自动维护
• 可用 OPTION (RECOMPILE) 解决参数嗅探

Oracle

• 使用 GLOBAL TEMPORARY TABLE
• 可配置 ON COMMIT PRESERVE ROWS
• 支持索引和统计信息

五、实战示例（优化前 vs 优化后）

优化前（重复子查询）

SELECT *
FROM orders o
WHERE user_id IN (
  SELECT user_id FROM vip_users
)
AND order_id IN (
  SELECT order_id FROM big_log
);

优化后（临时表）

SELECT user_id INTO #vip FROM vip_users;
CREATE INDEX ix ON #vip(user_id);

SELECT order_id INTO #log FROM big_log;
CREATE INDEX ix2 ON #log(order_id);

SELECT o.*
FROM orders o
JOIN #vip v ON o.user_id = v.user_id
JOIN #log l ON o.order_id = l.order_id;

✅ 执行计划更稳定，IO 明显减少

六、常见坑提醒

• 临时表未加索引 → 全表扫描
• 临时表太大 → 磁盘临时表
• 并发高时频繁建表 → 锁 / 资源争用
• 忘记清理（SQL Server 会自动清，MySQL 会话结束清）

如果你能告诉我：

• 使用的数据库（MySQL / SQL Server / Oracle / PostgreSQL）
• 具体 SQL 或执行计划

我可以帮你针对性优化某条查询。