MySQL 临时表注意事项

原创 已于 2026-05-24 13:19:54 修改 · 25 阅读 · 0 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
GEO检测
标签
#MySQL 临时表注意事项 #临时表是啥？ #什么时候会用临时表？ #验证一下 #临时表的两种存储方式
于 2026-05-24 13:19:52 首次发布

我刚工作的时候，有个复杂查询：SELECT ... FROM users JOIN orders ON ... JOIN products ON ... WHERE ... GROUP BY ... HAVING ...，结果 MySQL 用了临时表，直接把 16GB 内存干满了。

今天咱们就来聊聊 MySQL 临时表的注意事项，看完这篇，你就能避开 90% 的临时表坑。

临时表是啥？

临时表（Temporary Table） 是 MySQL 在执行某些 SQL 时，自动创建的中间表，用于暂存中间结果。

什么时候会用临时表？

  1. UNION 查询
    1. GROUP BY 和 ORDER BY 的字段不一样
    1. DISTINCT + ORDER BY
    1. 复杂 JOIN（多表关联）
    1. 子查询
    1. 派生表（FROM 里的子查询）

验证一下

-- 强制用临时表
EXPLAIN SELECT DISTINCT age FROM users ORDER BY name;

输出：

+----+-------------+-------+------+---------------+------+---------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+----------+----------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 20000000 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+----------+----------------+

关键点：Extra = Using temporary（用了临时表）。

临时表的两种存储方式

MySQL 的临时表有两种存储方式：内存临时表 和 磁盘临时表。

1. 内存临时表（快！）

条件：

  1. 临时表数据量 < tmp_table_size
    1. 并且 < max_heap_table_size
    1. 并且 字段没有 BLOB/TEXT
      存储位置：内存（快！）
-- 查看当前 tmp_table_size
SHOW VARIABLES LIKE 'tmp_table_size';
-- 默认 16MB

-- 查看当前 max_heap_table_size
SHOW VARIABLES LIKE 'max_heap_table_size';
-- 默认 16MB

优点：内存操作，超快！

缺点：如果临时表数据量超过阈值，会自动转成磁盘临时表（性能炸裂）。

2. 磁盘临时表（慢！）

条件：

  1. 临时表数据量 > tmp_table_size
    1. 或者 > max_heap_table_size
    1. 或者 字段有 BLOB/TEXT
      存储位置：磁盘（慢！）

磁盘临时表的存储引擎：

  • MySQL 5.6 及之前：MyISAM
    • MySQL 5.7 及之后：InnoDB（默认）
      优点：能存大数据量。

缺点：磁盘 I/O，慢 100 倍！

临时表的坑

坑 1：内存临时表转磁盘临时表

问题：如果临时表数据量超过 tmp_table_size 或 max_heap_table_size，会自动转成磁盘临时表，性能炸裂。

-- tmp_table_size = 16MB
-- 临时表数据量 20MB，超过 16MB
-- 自动转成磁盘临时表（慢 100 倍！）
SELECT DISTINCT age FROM users ORDER BY name;

解决方案 1：调大 tmp_table_size 和 max_heap_table_size

-- 设置为 256MB
SET GLOBAL tmp_table_size = 268435456;
SET GLOBAL max_heap_table_size = 268435456;

-- 或者修改配置文件（永久生效）
-- my.cnf:
[mysqld]
tmp_table_size = 256M
max_heap_table_size = 256M

注意：不要调太大，否则多个临时表会把内存干满！

解决方案 2：优化 SQL，避免临时表

-- 优化前：用了临时表
SELECT DISTINCT age FROM users ORDER BY name;  -- Using temporary

-- 优化后：去掉 DISTINCT（如果业务允许）
SELECT age FROM users ORDER BY name GROUP BY age;  -- 可能不用临时表

坑 2：磁盘临时表占满磁盘

问题：如果磁盘临时表太大，会占满磁盘空间，导致 MySQL 崩溃。

-- 磁盘临时表 10GB（磁盘只剩 5GB）
SELECT DISTINCT * FROM users ORDER BY name;  -- 磁盘占满，MySQL 崩溃！

解决方案：监控磁盘空间，设置 磁盘临时表目录 到独立分区。

-- 查看当前磁盘临时表目录
SHOW VARIABLES LIKE 'tmpdir';
-- 默认 /tmp

-- 修改配置文件，设置到独立分区
-- my.cnf:
[mysqld]
tmpdir = /data/tmp  -- 独立分区，不影响系统盘

坑 3：临时表导致主从延迟

问题：如果主库用了临时表，不会记录到 binlog（因为临时表只在当前会话可见），但从库可能要执行同样的 SQL，导致主从延迟。

解决方案：尽量不用临时表，或者用汇总表代替。

优化方案 1：优化 SQL，避免临时表

思路：很多临时表是可以避免的，通过优化 SQL。

例子 1：GROUP BY 和 ORDER BY 的字段要一样

-- 优化前：GROUP BY 和 ORDER BY 不一样，用临时表
SELECT age, COUNT(*) FROM users GROUP BY age ORDER BY name;  -- Using temporary

-- 优化后：GROUP BY 和 ORDER BY 一样，不用临时表
SELECT age, COUNT(*) FROM users GROUP BY age ORDER BY age;  -- 没有 Using temporary

例子 2：DISTINCT 和 ORDER BY 的字段要一样

-- 优化前：DISTINCT 和 ORDER BY 不一样，用临时表
SELECT DISTINCT age FROM users ORDER BY name;  -- Using temporary

-- 优化后：DISTINCT 和 ORDER BY 一样，不用临时表
SELECT DISTINCT age FROM users ORDER BY age;  -- 没有 Using temporary

例子 3：用覆盖索引

-- 优化前：没走索引，用临时表
SELECT DISTINCT age FROM users ORDER BY age;  -- Using temporary

-- 优化后：走索引，不用临时表
CREATE INDEX idx_age ON users(age);
SELECT DISTINCT age FROM users ORDER BY age;  -- 没有 Using temporary

优化方案 2：调大 tmp_table_size 和 max_heap_table_size

思路：如果实在避免不了临时表，可以调大 tmp_table_size 和 max_heap_table_size，让临时表尽量在内存里。

-- 设置为 256MB
SET GLOBAL tmp_table_size = 268435456;
SET GLOBAL max_heap_table_size = 268435456;

-- 或者修改配置文件（永久生效）
-- my.cnf:
[mysqld]
tmp_table_size = 256M
max_heap_table_size = 256M

注意：

  1. 不要调太大（比如 1GB），否则多个临时表会把内存干满！
    1. 要监控内存使用（用 SHOW GLOBAL STATUS LIKE 'Created_tmp%'; 查看临时表创建情况）。

优化方案 3：用汇总表代替临时表

思路：如果临时表是为了统计，可以用汇总表代替（定时任务更新，查询时直接读）。

第 1 步：建汇总表

CREATE TABLE user_age_count (
    age TINYINT UNSIGNED NOT NULL,
        user_count INT NOT NULL,
            updated_at DATETIME NOT NULL,
                PRIMARY KEY (age)
                );
                ```
### 第 2 步：初始化汇总表

```sql
INSERT INTO user_age_count (age, user_count, updated_at)
SELECT age, COUNT(*), NOW() FROM users GROUP BY age;

第 3 步：定时更新汇总表

-- MySQL 事件：每小时更新一次
CREATE EVENT update_user_age_count
ON SCHEDULE EVERY 1 HOUR
DO
    TRUNCATE user_age_count;
        INSERT INTO user_age_count (age, user_count, updated_at)
            SELECT age, COUNT(*), NOW() FROM users GROUP BY age;
            ```
### 第 4 步：查询时直接读汇总表

```sql
SELECT age, user_count FROM user_age_count;  -- 0.001 秒，不用临时表

优点：

  • 查询超快（0.001 秒）
    • 不用临时表（不会内存/磁盘爆满）
      缺点：
  • 不是实时数据（最多滞后 1 小时）
    • 要维护汇总表（定时任务）
      适用场景：对实时性要求不高（比如"今日各年龄段用户数"，可以滞后 1 小时）。

优化方案 4：用 Redis 代替临时表

思路：如果临时表是为了缓存中间结果，可以用 Redis 代替。

第 1 步：查询时，把结果写 Redis

// 伪代码
public class UserAgeCountService {
    public Map<Integer, Integer> getUserAgeCount() {
            // 先从 Redis 读
                    String json = redis.get("user_age_count");
                            if (json != null) {
                                        return JSON.parseObject(json, Map.class);
                                                }
                                                        
                                                                // Redis 没有，从 MySQL 读，写 Redis
                                                                        Map<Integer, Integer> result = userDao.selectAgeCount();
                                                                                redis.set("user_age_count", JSON.toJSONString(result), 3600);  // 缓存 1 小时
                                                                                        return result;
                                                                                            }
                                                                                            }
                                                                                            ```
### 第 2 步：数据变更时，删除 Redis 缓存

```java
// 伪代码
public class UserService {
    public void updateUser(User user) {
            userDao.update(user);
                    
                            // 删除缓存，下次查询时重新计算
                                    redis.delete("user_age_count");
                                        }
                                        }
                                        ```
**优点**：
- 查询超快（Redis 内存操作）
- - 不用临时表（不会内存/磁盘爆满）
**缺点**：
- 要维护 Redis（额外组件）
- - 要处理缓存一致性（数据变更时删除缓存）
**适用场景**：对实时性要求高，并且能接受缓存一致性复杂度。

## 实战：优化一个用临时表的 SQL

假设有个用户表，要统计各年龄的用户数，并按年龄排序，很慢（用了临时表）：

```sql
SELECT age, COUNT(*) AS user_count
FROM users
GROUP BY age
ORDER BY user_count DESC;
-- 执行 30 秒（Using temporary）

第 1 步：看执行计划

EXPLAIN SELECT age, COUNT(*) AS user_count
FROM users
GROUP BY age
ORDER BY user_count DESC;

输出：

+----+-------------+-------+-------+---------------+----------+---------+------+----------+----------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows     | Extra          |
+----+-------------+-------+-------+---------------+----------+---------+------+----------+----------------+
|  1 | SIMPLE      | users | index | NULL          | idx_age | 5       | NULL | 20000000 | Using temporary |
+----+-------------+-------+-------+---------------+----------+---------+------+----------+----------------+

问题：

  1. Extra = Using temporary（用了临时表）
    1. 执行时间 30 秒

第 2 步：优化 SQL（避免临时表）

-- 优化前：GROUP BY 和 ORDER BY 不一样，用临时表
SELECT age, COUNT(*) AS user_count
FROM users
GROUP BY age
ORDER BY user_count DESC;  -- Using temporary

-- 优化后：先算各年龄的用户数，再排序（可能还是用临时表）
SELECT age, COUNT(*) AS user_count
FROM users
GROUP BY age
ORDER BY NULL;  -- 不对，要按 user_count 排序

-- 优化方案：用子查询（还是可能用临时表）
SELECT age, user_count
FROM (
    SELECT age, COUNT(*) AS user_count
        FROM users
            GROUP BY age
            ) t
            ORDER BY user_count DESC;
            -- 可能还是 Using temporary
            ```
**如果实在避免不了临时表**，用**汇总表**代替。

### 第 3 步：用汇总表代替临时表

```sql
-- 建汇总表
CREATE TABLE user_age_count (
    age TINYINT UNSIGNED NOT NULL,
        user_count INT NOT NULL,
            updated_at DATETIME NOT NULL,
                PRIMARY KEY (age)
                );
-- 初始化汇总表
INSERT INTO user_age_count (age, user_count, updated_at)
SELECT age, COUNT(*), NOW() FROM users GROUP BY age;

-- 定时更新（每小时）
CREATE EVENT update_user_age_count
ON SCHEDULE EVERY 1 HOUR
DO
    TRUNCATE user_age_count;
        INSERT INTO user_age_count (age, user_count, updated_at)
            SELECT age, COUNT(*), NOW() FROM users GROUP BY age;
-- 查询时直接读汇总表（0.001 秒，不用临时表）
SELECT age, user_count
FROM user_age_count
ORDER BY user_count DESC;

优化效果：执行时间从 30 秒降到 0.001 秒（30000 倍提升！）

实战建议

1. 尽量避免临时表（最重要！）

这是最重要的建议。临时表要么占内存，要么占磁盘，性能都差。

优化 SQL：

  • GROUP BY 和 ORDER BY 的字段要一样
    • DISTINCT 和 ORDER BY 的字段要一样
    • 用覆盖索引

2. 如果避免不了，调大 tmp_table_size 和 max_heap_table_size

如果实在避免不了临时表，可以调大 tmp_table_size 和 max_heap_table_size，让临时表尽量在内存里。

-- 设置为 256MB
SET GLOBAL tmp_table_size = 268435456;
SET GLOBAL max_heap_table_size = 268435456;

注意：不要调太大（比如 1GB），否则多个临时表会把内存干满！

3. 监控临时表使用情况

一定要监控临时表使用情况，有问题立马报警。

-- 查看临时表创建情况
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

重点看这几个：

  • Created_tmp_tables：创建的临时表总数
    • Created_tmp_disk_tables：创建的磁盘临时表总数（如果这个值很大，说明 tmp_table_size 太小了）

4. 用汇总表代替临时表（对实时性要求不高）

如果临时表是为了统计，可以用汇总表代替（定时任务更新，查询时直接读）。

-- 建汇总表
CREATE TABLE user_age_count (
    age TINYINT UNSIGNED NOT NULL,
        user_count INT NOT NULL,
            updated_at DATETIME NOT NULL,
                PRIMARY KEY (age)
                );
-- 定时更新（每小时）
CREATE EVENT update_user_age_count
ON SCHEDULE EVERY 1 HOUR
DO
    TRUNCATE user_age_count;
        INSERT INTO user_age_count (age, user_count, updated_at)
            SELECT age, COUNT(*), NOW() FROM users GROUP BY age;
-- 查询时直接读汇总表
SELECT age, user_count FROM user_age_count;

总结

  • 临时表是 MySQL 在执行某些 SQL 时，自动创建的中间表
    • 什么时候会用临时表？ UNION 查询、GROUP BY 和 ORDER BY 的字段不一样、DISTINCT + ORDER BY、复杂 JOIN、子查询、派生表
    • 临时表的两种存储方式：内存临时表（快！）、磁盘临时表（慢！）
    • 临时表的坑：内存临时表转磁盘临时表、磁盘临时表占满磁盘、临时表导致主从延迟
    • 优化方案 1：优化 SQL，避免临时表（GROUP BY 和 ORDER BY 的字段要一样、DISTINCT 和 ORDER BY 的字段要一样、用覆盖索引）
    • 优化方案 2：调大 tmp_table_size 和 max_heap_table_size
    • 优化方案 3：用汇总表代替临时表
    • 优化方案 4：用 Redis 代替临时表
    • 实战建议：尽量避免临时表、如果避免不了就调大 tmp_table_size 和 max_heap_table_size、监控临时表使用情况、用汇总表代替临时表（对实时性要求不高）
      如果你能把临时表的两种存储方式、三个坑、四种优化方案讲清楚，面试官绝对觉得你有实战经验。

实战代码都在我本地跑过，你可以放心复制。 如果有问题，欢迎评论区交流！

易语言源码易语言超级表格自动完成源码
06-23
易语言源码易语言超级表格自动完成源码
【Python + 半导体】设备日志自动解析与异常提取工具
最新发布
06-23
本资源提供半导体设备日志自动解析与异常提取的完整解决方案，适用于设备维护、故障诊断、日志审计等场景。
FH8A15G灯串闪灯IC.pdf
06-23
FH8A15G灯串闪灯IC
Build PDF translation backend and frontend (3).md
06-23
Build PDF translation backend and frontend (3).md
stm32单片机项目资料课程设计文档C语言程序代码原理图电路PCB实例AVR精简学习板DXP资料及其相关资料
06-23
stm32单片机项目资料课程设计文档C语言程序代码原理图电路PCB实例AVR精简学习板DXP资料及其相关资料
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

乱码字符

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值