临时表 - Temporary Table详解

定义

临时表 (Temporary Table) 是一种特殊的数据库表,用于存储查询的中间结果或临时数据。它在会话期间存在(或事务期间),会话结束后自动删除。临时表可以是内存中的(速度快),也可以是磁盘上的(容量大),由数据库引擎根据数据量自动选择。

核心特征

特征	说明
生命周期	会话级/事务级,自动清理
可见性	仅当前会话可见
存储位置	内存或磁盘(自动选择)
命名空间	可与永久表同名
索引支持	可建立索引加速查询
适用场景	复杂查询、ETL、分批处理

临时表类型

MySQL临时表分类:

1. 用户创建的临时表
 CREATE TEMPORARY TABLE tmp_users AS ...
 • 手动创建
 • 可多次使用
 • 会话结束自动删除

2. 内部临时表(隐式)
 • 优化器自动创建
 • GROUP BY / ORDER BY / DISTINCT
 • UNION查询
 • 派生表
 • 用户不可见

3. 会话级 vs 事务级
 会话级: 整个会话期间存在
 事务级: Oracle GLOBAL TEMPORARY TABLE,事务结束清空

为什么需要临时表?

问题1: 复杂多步查询

场景: 找出消费最高的前10%用户

-- 不用临时表: 嵌套子查询(难以理解和优化)
SELECT u.*, stats.total_spent
FROM users u
JOIN (
  SELECT 
    user_id,
    SUM(amount) as total_spent,
    NTILE(10) OVER (ORDER BY SUM(amount) DESC) as percentile
  FROM orders
  GROUP BY user_id
) stats ON u.id = stats.user_id
WHERE stats.percentile = 1;

-- 执行计划复杂,优化器可能选择不佳


-- 使用临时表: 分步清晰
-- Step 1: 计算每个用户的总消费
CREATE TEMPORARY TABLE tmp_user_stats AS
SELECT 
  user_id,
  SUM(amount) as total_spent
FROM orders
GROUP BY user_id;

-- Step 2: 找到前10%的阈值
SET @threshold = (
  SELECT total_spent 
  FROM tmp_user_stats 
  ORDER BY total_spent DESC 
  LIMIT 1 OFFSET (SELECT COUNT(*) * 0.1 FROM tmp_user_stats)
);

-- Step 3: 获取目标用户
SELECT u.*, t.total_spent
FROM users u
JOIN tmp_user_stats t ON u.id = t.user_id
WHERE t.total_spent >= @threshold;

-- ✓ 逻辑清晰,每步可单独优化

问题2: 分批处理大数据

场景: 更新1亿条订单的状态

-- 直接更新(锁表时间长,风险高)
UPDATE orders SET status = 1 WHERE created_at < '2023-01-01';
-- 影响5000万行,耗时数小时!


-- 使用临时表分批处理
-- Step 1: 找出需要更新的ID
CREATE TEMPORARY TABLE tmp_orders_to_update AS
SELECT id FROM orders WHERE created_at < '2023-01-01';

-- Step 2: 建立索引加速
CREATE INDEX idx_id ON tmp_orders_to_update(id);

-- Step 3: 分批更新
WHILE (SELECT COUNT(*) FROM tmp_orders_to_update) > 0 DO
  UPDATE orders o
  JOIN tmp_orders_to_update t ON o.id = t.id
  SET o.status = 1
  LIMIT 10000;
  
  DELETE FROM tmp_orders_to_update LIMIT 10000;
  
  COMMIT;
END WHILE;

-- ✓ 每批1万行,可控性强

问题3: 递归/层次查询

场景: 组织架构树查询

-- 使用临时表存储递归结果
CREATE TEMPORARY TABLE tmp_org_tree (
  emp_id INT,
  emp_name VARCHAR(100),
  manager_id INT,
  level INT
);

-- 插入根节点
INSERT INTO tmp_org_tree
SELECT emp_id, emp_name, manager_id, 1
FROM employees WHERE manager_id IS NULL;

-- 逐层插入子节点
SET @level = 1;
WHILE ROW_COUNT() > 0 DO
  SET @level = @level + 1;
  
  INSERT INTO tmp_org_tree
  SELECT e.emp_id, e.emp_name, e.manager_id, @level
  FROM employees e
  JOIN tmp_org_tree t ON e.manager_id = t.emp_id
  WHERE t.level = @level - 1;
END WHILE;

-- 查询完整组织架构
SELECT * FROM tmp_org_tree ORDER BY level, emp_name;

实现机制

内存临时表 vs 磁盘临时表

-- MySQL自动选择存储引擎

-- 小数据集 → MEMORY引擎(内存)
CREATE TEMPORARY TABLE tmp_small AS
SELECT id, name FROM users LIMIT 1000;
-- 存储在内存中,速度极快

-- 大数据集 → InnoDB引擎(磁盘)
CREATE TEMPORARY TABLE tmp_large AS
SELECT * FROM orders;
-- 存储在磁盘临时表空间

-- 查看临时表配置
SHOW VARIABLES LIKE 'tmp_table_size';  -- 内存临时表上限
SHOW VARIABLES LIKE 'max_heap_table_size'; -- HEAP表上限
SHOW VARIABLES LIKE 'tmpdir';      -- 磁盘临时表目录

转换条件:

内存 → 磁盘的触发条件:
1. 临时表大小 > tmp_table_size
2. 临时表大小 > max_heap_table_size
3. 包含BLOB/TEXT字段
4. GROUP BY / DISTINCT 结果集过大

推荐配置:
tmp_table_size = 256M
max_heap_table_size = 256M

内部临时表的使用场景

-- 1. GROUP BY
SELECT category, COUNT(*) FROM products GROUP BY category;
-- 可能需要临时表存储分组结果

-- 2. ORDER BY (无法使用索引)
SELECT * FROM orders ORDER BY created_at DESC;
-- 需要临时表进行文件排序

-- 3. DISTINCT
SELECT DISTINCT category FROM products;
-- 需要临时表去重

-- 4. UNION
SELECT id FROM orders_2023
UNION
SELECT id FROM orders_2024;
-- UNION自动使用临时表合并结果

-- 5. 派生表
SELECT * FROM (SELECT ...) AS derived;
-- 派生表被物化为临时表

-- 监控内部临时表使用
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

-- 理想比例: 磁盘临时表 < 5%

实际应用案例

案例1: ETL数据清洗

-- 从原始数据抽取到临时表
CREATE TEMPORARY TABLE tmp_raw_data AS
SELECT * FROM external_source;

-- 数据清洗
DELETE FROM tmp_raw_data WHERE email IS NULL;
DELETE FROM tmp_raw_data WHERE phone NOT REGEXP '^[0-9]{11}$';
UPDATE tmp_raw_data SET name = TRIM(name);

-- 数据转换
CREATE TEMPORARY TABLE tmp_cleaned AS
SELECT 
  id,
  UPPER(name) as name,
  email,
  phone,
  CASE 
    WHEN age < 18 THEN 'minor'
    WHEN age < 60 THEN 'adult'
    ELSE 'senior'
  END as age_group
FROM tmp_raw_data;

-- 加载到目标表
INSERT INTO final_users
SELECT * FROM tmp_cleaned
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  email = VALUES(email);

-- 会话结束,临时表自动清理 ✓

案例2: 复杂报表生成

CREATE PROCEDURE sp_generate_sales_report()
BEGIN
  -- 临时表1: 每日销售
  CREATE TEMPORARY TABLE tmp_daily_sales AS
  SELECT 
    DATE(created_at) as sale_date,
    COUNT(*) as order_count,
    SUM(amount) as daily_total
  FROM orders
  WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  GROUP BY DATE(created_at);
  
  -- 临时表2: 品类销售
  CREATE TEMPORARY TABLE tmp_category_sales AS
  SELECT 
    p.category,
    COUNT(DISTINCT o.id) as order_count,
    SUM(oi.quantity) as total_qty,
    SUM(oi.price * oi.quantity) as category_total
  FROM orders o
  JOIN order_items oi ON o.id = oi.order_id
  JOIN products p ON oi.product_id = p.id
  WHERE o.created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  GROUP BY p.category;
  
  -- 最终报表
  SELECT 
    d.sale_date,
    c.category,
    d.order_count,
    d.daily_total,
    c.category_total,
    ROUND(c.category_total / d.daily_total * 100, 2) as pct_of_daily
  FROM tmp_daily_sales d
  CROSS JOIN tmp_category_sales c
  ORDER BY d.sale_date, c.category_total DESC;
  
  -- 临时表自动清理,无需手动DROP
END;

最佳实践

1. 合理使用索引

-- 大数据量的临时表,建立索引
CREATE TEMPORARY TABLE tmp_large AS
SELECT * FROM orders WHERE status = 1;

-- 如果后续要JOIN或WHERE,建立索引
ALTER TABLE tmp_large ADD INDEX idx_user (user_id);
ALTER TABLE tmp_large ADD INDEX idx_date (created_at);

2. 及时清理

-- 虽然会话结束会自动清理
-- 但建议尽早手动释放资源
DROP TEMPORARY TABLE IF EXISTS tmp_orders;

-- 或者清空内容
TRUNCATE TABLE tmp_orders;

3. 监控临时表使用

-- 查看当前会话的临时表
SHOW OPEN TABLES WHERE In_use > 0;

-- 监控全局临时表创建情况
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

-- 如果磁盘临时表过多,考虑优化:
-- 1. 增加tmp_table_size
-- 2. 优化查询减少临时表使用
-- 3. 使用索引避免filesort

参考资料

相关术语

• 派生表
• 物化视图
• 执行计划

---

版本历史:

• 2026-04-12: 初始版本,讲解临时表原理与实践