临时表与如何避免
什么是 MySQL 临时表
临时表是 MySQL 在执行某些查询时，在内存或磁盘上创建的中间结果集。当 Extra 列中出现 Using temporary 时，说明该查询使用了临时表。
EXPLAIN SELECT status, COUNT(*) FROM order GROUP BY status;
-- Extra: Using temporary; Using filesort
哪些操作会产生临时表
1. GROUP BY 与 ORDER BY 字段不同
-- 以下操作会产生临时表
SELECT user_id, COUNT(*)
FROM order
GROUP BY user_id
ORDER BY create_time DESC;
-- GROUP BY 用于分组，ORDER BY 用于排序
-- 两者字段不同，MySQL 需要用临时表完成分组后再排序
-- 不需要临时表
SELECT user_id, COUNT(*)
FROM order
GROUP BY user_id
ORDER BY user_id;
-- GROUP BY 和 ORDER BY 字段一致，可用索引排序
2. DISTINCT + ORDER BY 字段不同
SELECT DISTINCT user_id
FROM order
ORDER BY create_time DESC;
-- 去重需要临时表
3. UNION（非 UNION ALL）
SELECT id, name FROM user_a
UNION
SELECT id, name FROM user_b;
-- UNION 需要去重，必须创建临时表
-- 用 UNION ALL 避免去重开销
4. 派生表（FROM 子句中的子查询）
SELECT * FROM (
SELECT * FROM order WHERE status = 1
ORDER BY create_time DESC
LIMIT 100
) tmp
JOIN user ON tmp.user_id = user.id;
-- 子查询结果作为临时表（派生表）
5. 多表 UPDATE/DELETE
DELETE FROM user USING user
JOIN order ON user.id = order.user_id
WHERE order.create_time < '2020-01-01';
-- 特殊的 DELETE JOIN 语法可能产生临时表
临时表存储的位置
MySQL 8.0 使用 TempTable 引擎（之前是 MEMORY 引擎）：
临时表优先存储在内存中：
innodb_temp_table_size / temptable_max_ram = 1GB（默认）
↓ 超过内存限制
磁盘临时表（存储在 tmpdir 指定的目录）
磁盘临时表的性能：
– 磁盘 IO 是临时表最主要的性能瓶颈
– 大临时表可能导致磁盘空间不足
– tmpdir 如果指向 HDD，性能更差
如何判断查询使用了临时表
EXPLAIN SELECT status, COUNT(*)
FROM order
GROUP BY status
ORDER BY create_time;
-- Extra: Using temporary; Using filesort
-- 还可以查看状态变量
FLUSH STATUS;
SELECT status, COUNT(*)
FROM order
GROUP BY status
ORDER BY create_time;
SHOW STATUS LIKE '%tmp%';
-- Created_tmp_tables: 临时表总数
-- Created_tmp_disk_tables: 磁盘临时表数量
临时表的优化策略
策略 1：使用索引避免临时表
-- 创建合适的索引让 GROUP BY + ORDER BY 走索引
CREATE INDEX idx_status_create ON order(status, create_time);
-- 优化后
SELECT status, COUNT(*)
FROM order
GROUP BY status;
-- Extra: Using index（索引覆盖）
-- 不再需要临时表，直接在索引中完成 GROUP BY
策略 2：使用 UNION ALL 替代 UNION
-- UNION（产生临时表）
SELECT id FROM order_2023
UNION
SELECT id FROM order_2024;
-- UNION ALL（不产生临时表）
SELECT id FROM order_2023
UNION ALL
SELECT id FROM order_2024;
-- 如果确认数据无重复，直接使用 UNION ALL
策略 3：让 GROUP BY 和 ORDER BY 使用相同字段
-- 避免临时表的写法
SELECT user_id, COUNT(*) as cnt
FROM order
GROUP BY user_id
ORDER BY cnt DESC, user_id ASC;
-- 或者使用 user_id 排序
ORDER BY user_id;
-- 一定要按其他字段排序怎么办？
-- 子查询先 GROUP BY，外层再 ORDER BY
SELECT * FROM (
SELECT user_id, COUNT(*) as cnt
FROM order
GROUP BY user_id
) tmp
ORDER BY tmp.cnt DESC;
策略 4：增大临时表内存限制
# my.cnf
# MySQL 8.0 的 TempTable 引擎
temptable_max_ram = 2G
innodb_temp_tablespaces_dir = /fast_disk/temp # 指向 SSD
# MySQL 5.7 及以下
tmp_table_size = 256M
max_heap_table_size = 256M
策略 5：优化 DISTINCT 查询
-- 替代方案：用 EXISTS 代替 DISTINCT
SELECT DISTINCT user_id FROM order WHERE status = 1;
-- 改为
SELECT id FROM user WHERE EXISTS (
SELECT 1 FROM order WHERE user_id = user.id AND status = 1
);
-- 后者可能避免临时表（取决于其他条件和索引）
临时表对性能的实际影响
| 临时表类型 | 小数据量（< 1000 行） | 大数据量（> 100 万行） |
|---|---|---|
| 内存临时表 | 几乎无影响 | 一定影响 |
| 磁盘临时表 | 可接受 | 严重（IO 瓶颈） |
磁盘临时表是最应该避免的。可以通过监控 Created_tmp_disk_tables 来判断系统中是否有大量磁盘临时表。
面试要点
- Extra: Using temporary 表示使用了临时表，是性能优化的信号
- 临时表的本质是在 MySQL 完成分组/去重/排序时的中间存储
- 磁盘临时表是真正的性能杀手，需要重点优化
- 最有效的解决手段：创建合适的索引（让 GROUP BY + ORDER BY 同字段）
- 可以问出”你查看过系统中 Created_tmp_disk_tables 的状态吗？”来判断候选人的实战经验
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
暂无评论内容