1. 一个真实的性能事故现场

去年双十一大促时，我们的订单系统突然出现响应延迟。监控显示数据库服务器CPU飙到90%，磁盘IO持续爆红。DBA抓取到一条诡异的SQL正在疯狂创建临时表：

-- 错误示例：在循环中重复创建临时表（MySQL 8.0）
DELIMITER $$
CREATE PROCEDURE batch_process()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 1000 DO
    CREATE TEMPORARY TABLE temp_orders 
    SELECT * FROM orders WHERE status = 'unpaid' LIMIT 1000;
    
    UPDATE temp_orders SET remark = 'processed' WHERE id > 0;
    
    INSERT INTO order_log SELECT * FROM temp_orders;
    
    DROP TEMPORARY TABLE temp_orders; -- 看似规范的清理操作
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

这个存储过程在测试环境运行良好，却在生产环境酿成灾难。根本原因是：临时表创建频率过高导致内存碎片化，最终触发磁盘临时表转换。接下来我们详细拆解这个"血案"。

2. 临时表的双重面孔

2.1 内存临时表（The Good）

当结果集较小时，MySQL使用Memory引擎创建临时表：

-- 健康示例：小规模数据排序（MySQL 8.0）
EXPLAIN 
SELECT * FROM user_login_log 
WHERE login_time > '2023-01-01'
ORDER BY user_id 
LIMIT 100;

执行计划显示"Using temporary"，但整个过程在内存完成，速度堪比高铁。

2.2 磁盘临时表（The Bad）

当数据超过tmp_table_size（默认16MB）时，画风突变：

-- 危险示例：大字段分组（MySQL 8.0）
SELECT text_content, COUNT(*) 
FROM customer_feedback 
GROUP BY text_content;

此时MySQL会改用MyISAM引擎创建磁盘临时表，性能断崖式下跌，就像早高峰的地铁换乘。

3. 六大作死姿势排行榜

3.1 连环创建案

文章开头的存储过程就是典型反模式。每次循环都创建/销毁临时表，相当于在高速公路上频繁启停汽车。

优化方案：

-- 正确姿势：单次创建重复使用（MySQL 8.0）
DELIMITER $$
CREATE PROCEDURE optimized_process()
BEGIN
  CREATE TEMPORARY TABLE temp_orders (
    id INT PRIMARY KEY,
    order_no VARCHAR(20),
    INDEX (status)
  ) ENGINE=Memory;
  
  DECLARE i INT DEFAULT 0;
  WHILE i < 1000 DO
    TRUNCATE TABLE temp_orders; -- 清空复用
    
    INSERT INTO temp_orders 
    SELECT id, order_no FROM orders 
    WHERE status = 'unpaid' LIMIT 1000;
    
    UPDATE orders o 
    JOIN temp_orders t ON o.id = t.id
    SET o.remark = 'processed';
    
    SET i = i + 1;
  END WHILE;
  
  DROP TEMPORARY TABLE temp_orders;
END$$
DELIMITER ;

3.2 隐式转换陷阱

-- 致命操作：字符集不一致（MySQL 8.0）
CREATE TEMPORARY TABLE temp_users 
SELECT * FROM users WHERE dept_id = '1001'; -- dept_id是INT类型

SELECT * FROM orders o
JOIN temp_users u ON o.user_code = u.employee_no; 
-- user_code是utf8mb4, employee_no是utf8

这个隐式转换会导致全表扫描，解决方案：

ALTER TABLE temp_users MODIFY employee_no VARCHAR(20) 
CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

4. 高阶玩家必备技巧

4.1 查询重写艺术

原查询：

SELECT * FROM (
  SELECT * FROM orders WHERE create_time > '2023-01-01'
) AS t1
JOIN (
  SELECT user_id, MAX(login_time) FROM user_logins 
  GROUP BY user_id
) AS t2 ON t1.user_id = t2.user_id;

优化后：

WITH cte_login AS (
  SELECT user_id, MAX(login_time) AS last_login
  FROM user_logins
  GROUP BY user_id
)
SELECT o.*, l.last_login
FROM orders o
JOIN cte_login l ON o.user_id = l.user_id
WHERE o.create_time > '2023-01-01';

4.2 引擎选择策略

强制使用内存临时表：

SET SESSION internal_tmp_mem_storage_engine=MEMORY;

但要注意Memory引擎的短板：

  • 不支持BLOB/TEXT类型
  • 哈希索引不适合范围查询
  • 默认使用固定长度行存储

5. 关联技术：索引的救赎

当看到"Using temporary"时，先别急着甩锅给临时表。合适的索引可能让临时表消失：

-- 原始查询
SELECT product_id, COUNT(*) 
FROM order_items 
GROUP BY product_id 
ORDER BY COUNT(*) DESC;

-- 优化方案
ALTER TABLE order_items ADD INDEX idx_product (product_id);

更好的复合索引：

ALTER TABLE order_items ADD INDEX idx_product_count (product_id, quantity);

6. 监控与调优工具箱

6.1 实时诊断命令

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
/* 
Created_tmp_tables       : 内存临时表数量
Created_tmp_disk_tables  : 磁盘临时表数量
Created_tmp_files        : 临时文件数量
*/

6.2 性能参数调优

# my.cnf 优化项
tmp_table_size=64M       # 单个临时表最大内存
max_heap_table_size=64M  # 必须与tmp_table_size相同
internal_tmp_mem_storage_engine=MEMORY
tmpdir=/dev/shm          # 使用内存文件系统

7. 避坑指南：什么时候该用临时表

7.1 推荐场景

  • 分页缓存：缓存复杂查询的中间结果
  • 数据清洗：ETL过程中的暂存操作
  • 会话级计算：用户自定义报表生成

7.2 替代方案

  • 使用CTE（公共表表达式）
  • 利用物化视图（MySQL需通过触发器实现）
  • 应用层缓存（Redis/Memcached）

8. 血的教训总结

  1. 临时表不是洪水猛兽，但要用在刀尖上
  2. 监控Created_tmp_disk_tables指标如同检查汽车油表
  3. 复杂的多步骤操作，CTE可能是更好的选择
  4. 永远在开发环境测试真实数据量
  5. 定期检查隐式类型转换和字符集问题

最后分享一个真实案例：某电商平台通过优化临时表使用，将订单导出性能从45分钟提升到3分钟。他们做了三件事：

  1. 将循环内的临时表改为批处理
  2. 增加复合索引减少中间结果集
  3. 调整tmpdir到内存文件系统

记住：临时表就像手术刀，用得好能救命，用不好会要命。保持敬畏，善用工具，方能在数据库优化的江湖中立于不败之地。