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. 血的教训总结
- 临时表不是洪水猛兽，但要用在刀尖上
- 监控Created_tmp_disk_tables指标如同检查汽车油表
- 复杂的多步骤操作，CTE可能是更好的选择
- 永远在开发环境测试真实数据量
- 定期检查隐式类型转换和字符集问题
最后分享一个真实案例：某电商平台通过优化临时表使用，将订单导出性能从45分钟提升到3分钟。他们做了三件事：
- 将循环内的临时表改为批处理
- 增加复合索引减少中间结果集
- 调整tmpdir到内存文件系统
记住：临时表就像手术刀，用得好能救命，用不好会要命。保持敬畏，善用工具，方能在数据库优化的江湖中立于不败之地。