讲师中心 微信公众号

工具推荐

视频效率加速
剧云
剧云

中文短剧剧本制作平台

AniShort
AniShort

AI短剧协同制作平台

Laper
Laper

剧本策划到成片协作

Seko
Seko

AI短剧一键生成成片

如何调优MySQL的tmp_table_size以减少磁盘临时表生成

落墨酱_5206

落墨酱_5206

发布时间:2026-11-19 10:23:16

|

521人浏览过

|

来源于php中文网

原创

全局生效的 tmp_table_size 必须在 my.cnf 的 [mysqld] 段配置,且需与 max_heap_table_size 同步设置为相等值;仅调大前者无效,因实际限制取二者较小值。

如何调优mysql的tmp_table_size以减少磁盘临时表生成

tmp_table_size 设在哪?别只改 session 级别

全局生效的 tmp_table_size 必须在 MySQL 启动时由 my.cnf(或 mysqld.cnf)中的 [mysqld] 段落配置,运行时用 SET GLOBAL tmp_table_size = ... 虽然能改,但仅对后续新连接生效,且重启后丢失。更关键的是:它只限制 内存临时表 的最大尺寸,实际是否用内存表,还取决于 max_heap_table_size —— 两者取较小值才真正生效。

常见错误是只调大 tmp_table_size 却忽略 max_heap_table_size,结果毫无改善。两个值必须同步设置,且建议设为相等:

tmp_table_size = 256M
max_heap_table_size = 256M

若只针对某类查询临时提权,可在会话中同时设两者:

SET SESSION tmp_table_size = 512 * 1024 * 1024;
SET SESSION max_heap_table_size = 512 * 1024 * 1024;

怎么确认是不是真的在用磁盘临时表?

不能只看 Created_tmp_disk_tables 这个状态变量——它统计的是「已落到磁盘的临时表次数」,但你得知道哪些查询触发了它。最直接的方式是开启慢日志并加上 log_queries_not_using_indexes = ON,再配合 log_slow_slave_statements(如用从库做分析),然后重点过滤含 Using temporary 的执行计划。

更准的做法是在目标查询前打开 profiling(MySQL 5.7 及以前)或使用 performance_schema:

  • MySQL 8.0+:查 performance_schema.events_statements_history_long,筛选 SQL_TEXT 含临时表操作,并检查 TEMP_TABLES 字段是否为 Disk
  • 临时加 trace:执行 SET SESSION optimizer_trace="enabled=on,one_line=off";,跑完查询后查 information_schema.OPTIMIZER_TRACE,搜索 "using_temporary_table": true 和 "on_disk": true

注意:Created_tmp_tables 是总临时表数,Created_tmp_disk_tables / Created_tmp_tables 的比值超过 5% 就该警惕了。

win可用的mysql指令skill
win可用的mysql指令skill

安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。

下载

什么操作必然绕过内存限制、直写磁盘?

即使 tmp_table_size 和 max_heap_table_size 足够大,以下情况仍强制走磁盘临时表:

  • 临时表包含 TEXT 或 BLOB 列(哪怕只有一列,哪怕数据很短)
  • GROUP BY 或 DISTINCT 中的字段含有可变长度类型(如 VARCHAR(500)),且排序所需内存超限
  • UNION 查询中各分支字段类型不一致,MySQL 内部需做隐式转换 + 宽松对齐,导致行大小膨胀
  • 使用了 SQL_SMALL_RESULT 提示但优化器判定不可信,回退到磁盘表

这类问题无法靠调大 tmp_table_size 解决,必须重构 SQL:比如把 TEXT 字段移出 GROUP BY,用子查询先聚合 ID 再 JOIN 回原文本;或把长 VARCHAR 改成定长 CHAR(谨慎评估存储代价)。

调太大反而拖慢?别忽视内存碎片和并发压力

tmp_table_size 不是越大越好。每个连接都可能独占一块这么大的内存区域(虽然实际按需分配),当并发连接多、又频繁创建大临时表时,极易引发内存竞争甚至 OOM。尤其在容器环境或小内存实例上,设成 1G 可能导致其他缓冲区(如 innodb_buffer_pool_size)被挤压。

实操建议:

  • 先观察峰值并发下 Threads_connected 和 Created_tmp_tables 的比值,估算平均每个连接可能占用的临时表内存
  • 把 tmp_table_size 设为单次典型查询所需内存的 2–3 倍,而非盲目堆到 1G+
  • 搭配监控 Performance_schema.memory_summary_global_by_event_name 中 memory/sql/TABLE 类事件的分配总量,验证是否真被大量消耗

真正难调的不是数值本身,而是得在「避免磁盘落表」和「不让内存成为瓶颈」之间反复校准——这需要结合具体查询模式、数据分布、硬件规格一起看,没法一锤定音。

热门AI工具

更多
墨刀AI
墨刀AI Hot

一款AI图像与设计工具,主要用于产品经理的专属智能体,适合需要提升相关任务效率的用户。

超级麦吉

一款通过数字员工协作完成调研、数据分析与文档制作的AI办公平台。

WorkBuddy

一款AI办公效率工具,主要用于腾讯云推出的AI原生桌面智能体工作台,适合需要提升相关任务效率的用户。

UpDream
UpDream Hot

一款AI视频创作工具,主要用于哔哩哔哩推出的自研AI视频创作工具,适合需要提升相关任务效率的用户。

Laper
Laper Hot

Laper是专为编剧、导演和制片人推出的 AI 原生剧本创作工具。

Flowith
Flowith Hot

Flowith是一款基于画布和智能体工作流的 AI 协作工作空间。

豆包大模型

豆包大模型是一款由字节跳动推出的企业级大语言模型服务平台。

DeepSeek

DeepSeek是一款面向对话、写作、编程和推理场景的AI大模型工具。

SkildArt
SkildArt Hot

SkildArt是一款AI文本写作工具,一站式 AI 视觉创作平台。

相关专题

更多
mysql修改数据表名
mysql修改数据表名

MySQL修改数据表:1、首先查看数据库中所有的表,代码为:‘SHOW TABLES;’;2、修改表名,代码为:‘ALTER TABLE 旧表名 RENAME [TO] 新表名;’。php中文网还提供MySQL的相关下载、相关课程等内容,供大家免费下载使用。

2673

2023.06.20

MySQL创建存储过程
MySQL创建存储过程

存储程序可以分为存储过程和函数,MySQL中创建存储过程和函数使用的语句分别为CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句调用存储过程智能用输出变量返回值。函数可以从语句外调用(通过引用函数名),也能返回标量值。存储过程也可以调用其他存储过程。php中文网还提供MySQL创建存储过程的相关下载、相关课程等内容,供大家免费下载使用。

1599

2023.06.21

mongodb和mysql的区别
mongodb和mysql的区别

mongodb和mysql的区别:1、数据模型;2、查询语言;3、扩展性和性能;4、可靠性。本专题为大家提供mongodb和mysql的区别的相关的文章、下载、课程内容,供大家免费下载体验。

935

2023.07.18

mysql密码忘了怎么查看
mysql密码忘了怎么查看

MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS 应用软件之一。那么mysql密码忘了怎么办呢?php中文网给大家带来了相关的教程以及文章,欢迎大家前来阅读学习。

3852

2023.07.19

mysql创建数据库
mysql创建数据库

MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS 应用软件之一。那么mysql怎么创建数据库呢?php中文网给大家带来了相关的教程以及文章,欢迎大家前来阅读学习。

6728

2023.07.25

mysql默认事务隔离级别
mysql默认事务隔离级别

MySQL是一种广泛使用的关系型数据库管理系统,它支持事务处理。事务是一组数据库操作,它们作为一个逻辑单元被一起执行。为了保证事务的一致性和隔离性,MySQL提供了不同的事务隔离级别。php中文网给大家带来了相关的教程以及文章欢迎大家前来学习阅读。

1399

2023.08.08

sqlserver和mysql区别
sqlserver和mysql区别

SQL Server和MySQL是两种广泛使用的关系型数据库管理系统。它们具有相似的功能和用途,但在某些方面存在一些显著的区别。php中文网给大家带来了相关的教程以及文章,欢迎大家前来学习阅读。

7011

2023.08.11

mysql忘记密码
mysql忘记密码

MySQL是一种关系型数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。那么忘记mysql密码我们该怎么解决呢?php中文网给大家带来了相关的教程以及其他关于mysql的文章,欢迎大家前来学习阅读。

5962

2023.08.14

GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

20

2026.11.17

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
关于我们 免责申明 举报中心 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号
PHP中文网订阅号
每天精选资源文章推送

Copyright 2014-2026 https://www.php.cn/ All Rights Reserved | php.cn