Using temporary 创建临时表

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

目录

前言

一、临时表创建时机

二、临时表类型

三、创建过程

四、资源消耗

五、优化建议

总结

前言

创建临时表，通常可以在查看SQL执行计划中能看到，对性能有一定影响。

提示：以下是本篇文章正文内容，下面案例可供参考

一、临时表创建时机

MySQL在以下场景需要创建临时表：

• ​GROUP BY​：GROUP BY a
• ​DISTINCT 操作​：SELECT DISTINCT a, b FROM table
• ​UNION 操作​：SELECT a FROM t1 UNION SELECT a FROM t2
• ​子查询或复杂连接​：需要中间结果集存储

二、临时表类型

磁盘临时表​：使用 MyISAM 或 InnoDB 存储引擎，数据量超过内存限制

三、创建过程

-- 示例查询
SELECT a, COUNT(*) FROM table GROUP BY a ORDER BY b;

-- MySQL内部执行：
-- 1. 创建临时表结构
CREATE TEMPORARY TABLE tmp_table (
    a VARCHAR(255),
    count_val INT,
    b VARCHAR(255)
);

-- 2. 扫描原表，填充临时表
INSERT INTO tmp_table (a, count_val, b)
SELECT a, COUNT(*), b FROM table GROUP BY a;

-- 3. 在临时表上执行排序
SELECT * FROM tmp_table ORDER BY b;

四、资源消耗

• ​内存占用​：临时表数据存储在内存或磁盘
• ​CPU 消耗​：表结构创建和数据填充
• ​磁盘 I/O​：如果使用磁盘临时表，涉及文件读写

五、优化建议

# 增大临时表大小
tmp_table_size = 64M
max_heap_table_size = 64M

# 使用 SSD 存储临时文件
tmpdir = /ssd/tmp

总结

在工作中处理慢SQL的时候，索引只是一个方面，即使有了索引，也会因为执行过程中要创建临时表，进行大量磁盘I/O，也是耗时的一大因素。

如果执行过程中会创建临时表，会在执行计划中 Extra里体现。

应对创建临时表，可以分为以下几种情况：

1）数据量可预见性小，不处理

2）如果数据据量比较大或者超大，则要避免，比如改写SQL或者通过其他的方式来实现。