临时表及如何避免
什么是 MySQL 临时表
临时表（Temporary Table）是 MySQL 在执行某些查询时，在内存或磁盘上创建的中间结果集。它的生命周期很短——查询执行结束后就自动释放。
两种临时表
| 类型 | 位置 | 触发条件 |
|---|---|---|
| 内存临时表 | 内存 | 数据量小于 tmp_table_size/max_heap_table_size |
| 磁盘临时表 | 磁盘 | 数据量超过阈值，由 InnoDB 创建 |
磁盘临时表的性能远低于内存临时表。如果查询产生了磁盘临时表，查询速度会慢几个数量级。
哪些操作会创建临时表
1. GROUP BY
-- GROUP BY 的字段如果没有索引，会创建临时表
SELECT status, COUNT(*) FROM orders GROUP BY status;
2. DISTINCT
-- DISTINCT 如果没有合适的索引，需要临时表去重
SELECT DISTINCT user_id FROM orders;
3. UNION（非 UNION ALL）
-- UNION 默认要去重，需要临时表
SELECT * FROM orders_2024_01
UNION
SELECT * FROM orders_2024_02;
-- 改为 UNION ALL 避免临时表（前提：数据不重叠）
4. ORDER BY 复杂场景
-- ORDER BY 字段与 GROUP BY 字段不一致时，可能需要临时表
SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY COUNT(*) DESC;
5. 子查询（派生表）
-- 派生表（FROM 中的子查询）会创建临时表
SELECT * FROM (SELECT * FROM orders WHERE amount > 100) tmp;
如何判断查询使用了临时表
使用 EXPLAIN 分析：
EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;
关注 Extra 列：
Using temporary ← 查询使用了临时表
Using filesort ← 查询需要额外的排序
Using temporary; Using filesort ← 双重打击！
如何避免临时表
方法一：创建合适的索引
-- GROUP BY + ORDER BY 的字段建立复合索引
CREATE INDEX idx_status ON orders(status);
执行计划变化：
Before: Using temporary; Using filesort
After: Using index
如果 GROUP BY 的字段有索引，MySQL 可以直接遍历索引完成分组，完全不需要临时表。
方法二：ORDER BY 与 GROUP BY 一致
-- 不一致 → 需要临时表
SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY COUNT(*) DESC;
-- 一致 → 不需要临时表
SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY status;
方法三：UNION 改 UNION ALL
-- UNION 需要临时表去重
SELECT id FROM orders_2024_01 WHERE amount > 100
UNION
SELECT id FROM orders_2024_02 WHERE amount > 100;
-- UNION ALL 不需要临时表（你知道两个结果集不会重复）
SELECT id FROM orders_2024_01 WHERE amount > 100
UNION ALL
SELECT id FROM orders_2024_02 WHERE amount > 100;
方法四：增大临时表阈值
如果临时表确实无法避免，可以增大内存临时表阈值，让它尽量在内存中完成：
-- 默认 16MB ~ 64MB，可以适当调大
SET GLOBAL tmp_table_size = 256 * 1024 * 1024; -- 256MB
SET GLOBAL max_heap_table_size = 256 * 1024 * 1024;
注意：min(tmp_table_size, max_heap_table_size) 中较小的值才是实际阈值。
方法五：拆分复杂查询
将需要临时表的复杂查询拆分为多次简单查询，在应用层完成聚合：
// 原 SQL（产生临时表）
// SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY status;
// 拆分：
// 第一步：按 status 分组统计
Map<String, Long> countMap = orderDao.queryGroupByStatus();
// 第二步：应用层排序
return countMap.entrySet().stream()
.sorted(Map.Entry.comparingByKey())
.collect(toList());
优化前后对比
-- 优化前：产生临时表 + 文件排序，扫描全表
SELECT DISTINCT category_id FROM products ORDER BY category_id;
-- Using temporary; Using filesort
-- 优化后：直接在索引上遍历
ALTER TABLE products ADD INDEX idx_category(category_id);
SELECT DISTINCT category_id FROM products ORDER BY category_id;
-- Using index for group-by
总结
临时表本身不是问题，问题在于磁盘临时表和不必要的临时表。优化点如下：
- 为 GROUP BY、DISTINCT 字段建立索引——最有效
- 尽量用 UNION ALL 代替 UNION
- ORDER BY 和 GROUP BY 用同一组字段
- 无法避免时，增大 tmp_table_size 让临时表留在内存
- 用 EXPLAIN 中的 “Using temporary” 信号检查每一条 SQL
记住：EXPLAIN 输出中 Extra 列出现 Using temporary 就是一个需要优化的信号。
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
暂无评论内容