当前位置： 首页 > news >正文

MySQL 临时表注意事项

news 2026/6/5 0:08:26

我刚工作的时候，有个复杂查询：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 里的子查询）

验证一下

-- 强制用临时表EXPLAINSELECTDISTINCTageFROMusersORDERBYname;

输出：

+----+-------------+-------+------+---------------+------+---------+------+----------+----------------+ | 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_sizeSHOWVARIABLESLIKE'tmp_table_size';-- 默认 16MB-- 查看当前 max_heap_table_sizeSHOWVARIABLESLIKE'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 倍！）SELECTDISTINCTageFROMusersORDERBYname;

解决方案 1：调大tmp_table_size和max_heap_table_size

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

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

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

-- 优化前：用了临时表SELECTDISTINCTageFROMusersORDERBYname;-- Using temporary-- 优化后：去掉 DISTINCT（如果业务允许）SELECTageFROMusersORDERBYnameGROUPBYage;-- 可能不用临时表

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

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

-- 磁盘临时表 10GB（磁盘只剩 5GB）SELECTDISTINCT*FROMusersORDERBYname;-- 磁盘占满，MySQL 崩溃！

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

-- 查看当前磁盘临时表目录SHOWVARIABLESLIKE'tmpdir';-- 默认 /tmp-- 修改配置文件，设置到独立分区-- my.cnf:[mysqld]tmpdir=/data/tmp-- 独立分区，不影响系统盘

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

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

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

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

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

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

-- 优化前：GROUP BY 和 ORDER BY 不一样，用临时表SELECTage,COUNT(*)FROMusersGROUPBYageORDERBYname;-- Using temporary-- 优化后：GROUP BY 和 ORDER BY 一样，不用临时表SELECTage,COUNT(*)FROMusersGROUPBYageORDERBYage;-- 没有 Using temporary

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

-- 优化前：DISTINCT 和 ORDER BY 不一样，用临时表SELECTDISTINCTageFROMusersORDERBYname;-- Using temporary-- 优化后：DISTINCT 和 ORDER BY 一样，不用临时表SELECTDISTINCTageFROMusersORDERBYage;-- 没有 Using temporary

例子 3：用覆盖索引

-- 优化前：没走索引，用临时表SELECTDISTINCTageFROMusersORDERBYage;-- Using temporary-- 优化后：走索引，不用临时表CREATEINDEXidx_ageONusers(age);SELECTDISTINCTageFROMusersORDERBYage;-- 没有 Using temporary

优化方案 2：调大 tmp_table_size 和 max_heap_table_size

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

-- 设置为 256MBSETGLOBALtmp_table_size=268435456;SETGLOBALmax_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 步：建汇总表

CREATETABLEuser_age_count(ageTINYINTUNSIGNEDNOTNULL,user_countINTNOTNULL,updated_atDATETIMENOTNULL,PRIMARYKEY(age));```### 第 2 步：初始化汇总表 ```sqlINSERTINTOuser_age_count(age,user_count,updated_at)SELECTage,COUNT(*),NOW()FROMusersGROUPBYage;

第 3 步：定时更新汇总表

-- MySQL 事件：每小时更新一次CREATEEVENT update_user_age_countONSCHEDULE EVERY1HOURDOTRUNCATEuser_age_count;INSERTINTOuser_age_count(age,user_count,updated_at)SELECTage,COUNT(*),NOW()FROMusersGROUPBYage;```### 第 4 步：查询时直接读汇总表 ```sqlSELECTage,user_countFROMuser_age_count;-- 0.001 秒，不用临时表

优点：

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

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

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

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

// 伪代码publicclassUserAgeCountService{publicMap<Integer,Integer>getUserAgeCount(){// 先从 Redis 读Stringjson=redis.get("user_age_count");if(json!=null){returnJSON.parseObject(json,Map.class);}// Redis 没有，从 MySQL 读，写 RedisMap<Integer,Integer>result=userDao.selectAgeCount();redis.set("user_age_count",JSON.toJSONString(result),3600);// 缓存 1 小时returnresult;}}``` ### 第2步：数据变更时，删除Redis缓存 ```java// 伪代码publicclassUserService{publicvoidupdateUser(Useruser){userDao.update(user);// 删除缓存，下次查询时重新计算redis.delete("user_age_count");}}```**优点**：-查询超快（Redis内存操作）--不用临时表（不会内存/磁盘爆满）**缺点**：-要维护Redis（额外组件）--要处理缓存一致性（数据变更时删除缓存）**适用场景**：对实时性要求高，并且能接受缓存一致性复杂度。 ## 实战：优化一个用临时表的SQL假设有个用户表，要统计各年龄的用户数，并按年龄排序，很慢（用了临时表）： ```sqlSELECTage,COUNT(*)ASuser_countFROMusersGROUPBYageORDERBYuser_countDESC;--执行30秒（Usingtemporary）

第 1 步：看执行计划

EXPLAINSELECTage,COUNT(*)ASuser_countFROMusersGROUPBYageORDERBYuser_countDESC;

输出：

+----+-------------+-------+-------+---------------+----------+---------+------+----------+----------------+ | 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 不一样，用临时表SELECTage,COUNT(*)ASuser_countFROMusersGROUPBYageORDERBYuser_countDESC;-- Using temporary-- 优化后：先算各年龄的用户数，再排序（可能还是用临时表）SELECTage,COUNT(*)ASuser_countFROMusersGROUPBYageORDERBYNULL;-- 不对，要按 user_count 排序-- 优化方案：用子查询（还是可能用临时表）SELECTage,user_countFROM(SELECTage,COUNT(*)ASuser_countFROMusersGROUPBYage)tORDERBYuser_countDESC;-- 可能还是 Using temporary```**如果实在避免不了临时表**，用**汇总表**代替。 ### 第 3 步：用汇总表代替临时表 ```sql-- 建汇总表CREATETABLEuser_age_count(ageTINYINTUNSIGNEDNOTNULL,user_countINTNOTNULL,updated_atDATETIMENOTNULL,PRIMARYKEY(age));-- 初始化汇总表INSERTINTOuser_age_count(age,user_count,updated_at)SELECTage,COUNT(*),NOW()FROMusersGROUPBYage;-- 定时更新（每小时）CREATEEVENT update_user_age_countONSCHEDULE EVERY1HOURDOTRUNCATEuser_age_count;INSERTINTOuser_age_count(age,user_count,updated_at)SELECTage,COUNT(*),NOW()FROMusersGROUPBYage;-- 查询时直接读汇总表（0.001 秒，不用临时表）SELECTage,user_countFROMuser_age_countORDERBYuser_countDESC;

优化效果：执行时间从 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，让临时表尽量在内存里。

-- 设置为 256MBSETGLOBALtmp_table_size=268435456;SETGLOBALmax_heap_table_size=268435456;

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

3. 监控临时表使用情况

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

-- 查看临时表创建情况SHOWGLOBALSTATUSLIKE'Created_tmp%';

重点看这几个：

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

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

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

-- 建汇总表CREATETABLEuser_age_count(ageTINYINTUNSIGNEDNOTNULL,user_countINTNOTNULL,updated_atDATETIMENOTNULL,PRIMARYKEY(age));-- 定时更新（每小时）CREATEEVENT update_user_age_countONSCHEDULE EVERY1HOURDOTRUNCATEuser_age_count;INSERTINTOuser_age_count(age,user_count,updated_at)SELECTage,COUNT(*),NOW()FROMusersGROUPBYage;-- 查询时直接读汇总表SELECTage,user_countFROMuser_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、监控临时表使用情况、用汇总表代替临时表（对实时性要求不高）
      如果你能把临时表的两种存储方式、三个坑、四种优化方案讲清楚，面试官绝对觉得你有实战经验。

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

http://www.cnnetsun.cn/news/2555003.html

相关文章：

  • 当机房环境管理面临挑战时，如何通过动环监控系统实现精准预警？
  • 颠覆传统字体体验：思源宋体CN如何重塑中文排版新标准
  • 像搭积木一样玩转Halcon视觉开发：HDevelop程序窗口与算子窗口联动实操（从read_image开始）
  • 终极PDF对比指南：3分钟掌握diff-pdf高效文档核对技巧
  • 网盘下载效率提升300%：八大平台直链获取工具终极指南
  • 别再只改PATH了！解决pytesseract报错的三个关键配置点：环境变量、代码路径与语言数据
  • 揭秘CuCl超低热导率：四声子散射与温度重正化的关键作用
  • NT5CB512M8EQ-FL：南亚4Gb DDR3-2133内存颗粒，x8组织，0°C~95°C，FBGA-78封装
  • Python日志框架设计：从基础到高级配置
  • 5.18~5.24补题
  • Awoo Installer：任天堂Switch游戏安装的高效一站式解决方案
  • 大麦网自动抢票脚本：Python自动化抢票终极指南
  • 抖音批量下载终极指南：5分钟掌握专业级无水印视频下载
  • 5分钟快速解锁中兴光猫：终极免费工具zteOnu完整指南
  • 别再混淆了！泊松分布数‘人数’，伽马分布看‘时间’：一张图讲清核心区别与选用指南
  • 5分钟快速上手：D3KeyHelper暗黑3技能连点器完全指南
  • 创业团队如何利用Taotoken统一管理多个AI项目模型成本
  • Wireshark实战20技：网络安全分析与威胁狩猎核心能力
  • CNN 卷积神经网络面试全集｜卷积、池化、感受野
  • 突破百度网盘速度壁垒：Python直链解析工具的技术实现与应用
  • SISSO符号回归算法：革命性可解释AI模型的3大技术突破
  • 5分钟掌握Redis：无需安装的在线学习工具全攻略
  • C51开发中的查表值验证方法与优化技巧
  • Unity里用VideoPlayer做个随机视频播放器，像刷短视频一样切换（附完整C#脚本）
  • 告别EasyConnect兼容性烦恼：一份给Ubuntu/WSL2用户的终极配置备忘录
  • 怎样高效对比PDF文档：diff-pdf工具实用指南
  • 终极指南：WSABuilds错误代码完全解决方案：从0x80073CF6到0x80073D10深度解析
  • 别再只会用轮询了！STM32CubeMX配置ADC单通道中断采集，让你的F407更高效
  • OneMore：终极OneNote插件，彻底改变你的笔记管理方式
  • Scroll Reverser：解决Mac多设备滚动混乱的终极方案