mysql临时表有什么用

千磊酱_4998

千磊酱_4998

2023-04-04

3652人浏览

原创

mysql临时表的作用:1、用户自己创建的临时表用于保存临时数据;2、当用户在执行复杂SQL时,可以借助临时表进行分组,排序,去重等操作,并且默认情况下,当你断开与数据库的连接后,临时表就会自动被销毁。

本教程操作环境:Windows10系统、MySQL5.7版本、Dell G3电脑。

mysql临时表有什么用?

MySQL临时表的作用

MySQL临时表在很多场景中都会用到,比如用户自己创建的临时表用于保存临时数据,以及MySQL内部在执行复杂SQL时,需要借助临时表进行分组、排序、去重等操作。下面将会对MySQL临时表的一些概念、分类和常见问题进行整理。
image.png

MySQL临时表类型

1.外部临时表,通过create temporary table语法创建的临时表,可以指定存储引擎为memory,innodb, myisam等等,这类表在会话结束后,会被自动清理。如果临时表与非临时表同时存在,那么非临时表不可见。show tables命令不显示临时表信息。
可通过information_schema.INNODB_TEMP_TABLE_INFO系统表可以查看外部临时表的相关信息,这部分使用的还是比较少。
image.png

2.内部临时表,通常在执行复杂SQL,比如group by, order by, distinct, union等,执行计划中如果包含Using temporary,还有undo回滚的时候,但空间不足的时候,MySQL内部将使用自动生成的临时表,以辅助完成工作。

MySQL临时表相关参数

1.max_heap_table_size:用户创建的内存表的最大值,也用于和tmp_table_size一起,限制内部临时表在内存中的大小。
2.tmp_table_size:内部临时表在内存中的的最大值,与max_heap_table_size参数共同决定,取二者的最小值。如果临时表超过该值,就会从内存转移到磁盘上。
3.innodb_tmpdir:online ALTER TABLE operations that rebuild the table max_tmp_tables

4.default_tmp_storage_engine:外部临时表(create temporary table创建的表)默认的存储引擎。

5.innodb_temp_data_file_path:innodb引擎下temp文件属性。建议限制innodb_temp_data_file_path = ibtmp1:1G:autoextend:max:30G

6.Internal_tmp_disk_storage_engine:磁盘上的内部临时表存储引擎,可选值为myisam或者innodb。使用innodb表在某些场景下,比如临时表列太多,或者行大小超过限制,可能会出现“ Row size too large or Too many columns”的错误,这时应该将临时表的innodb引擎改回myisam。tmpdir:临时表目录,当临时表大小超过一定阈值,就会从内存转移到磁盘上,
7.tmpdir变量表示磁盘上临时表所在的目录。

MySQL临时表相关状态变量

1.Created_tmp_disk_tables:执行SQL语句时,MySQL在磁盘上创建的内部临时表数量,如果这个值很大,可能原因是分配给临时表的最大内存值较小,或者SQL中有大量排序、分组、去重等操作,SQL需要优化。

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

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

下载

2.Created_tmp_files:创建的临时表数量

3.Created_tmp_tables:执行SQL语句时,MySQL创建的内部临时表数量。

4.Slave_open_temp_tables statement 或则 mix模式下才会看到有使用。
slave_open_temp_tables 的值显示,通过复制,当前slave创建了多少临时表,binlog_format只能是statement 和 mixed 下有效.
备注:stop slave 也没有用,必须主库手动删除 或则 session退出 才可以。
以下是从库binlog记录信息:
image.png

MySQL临时表注意事项

1.MySQL临时表可能导致磁盘可用空间减少:
在MySQL5.7版本之前,临时表的存储引擎默认为myisam,myisam临时表在SQL执行结束后,会自动删除临时表。然而从5.7版本开始,临时表的默认存储引擎变为innodb,虽然在性能上有了一定的提升,但是由于innodb引擎的临时表共用表空间ibtmp1,导致在高并发下,多个session同时创建临时表时,该表空间会变得非常大,并且不能动态缩小,除非重启MySQL,否则无法释放。
image.png

可以为临时表空间设置一个最大值,比如10G,如下:
innodb_temp_data_file_path = ibtmp1:128M:autoextend:max:10G
当临时表空间达到最大值10G时,SQL执行将会报错,影响应用的正常执行。
对于临时表空间过大的问题,通常也有一些其他方法解决,比如:
将临时表的存储引擎设置为myisam,虽然可能有一些性能问题,但不会导致磁盘空间问题。

2.SQL语句:
(1)加上合适的索引
(2)在where条件中过滤更多的数据
(3)重写SQL,优化执行计划
(4)如果不得不使用临时表,那么一定要减少并发。建议使用SSD硬盘。

3.undo相关
1)使用innodb_rollback_segments配置选项定义回滚segment的数量,默认设置是128,也是最大值。一个回滚segment总是分配给系统表空间,32个回滚segment预留给临时表空间(ibtmp1)。因此,要分配回滚段来撤消表空间,将innodb_rollback_segments设置为大于33的值。配置单独的undo表空间时,system表空间中的回滚段将呈现为非活动状态。

就是说超过128回滚segement的时候,就需要临时表出来救急。

tablespace -> segment -> extent(64个page,1M) -> page(16kb)

2)truncate undo
当innodb_undo_log_truncate触发的时候,undo表空间截断操作在服务器日志目录中创建一个临时的undo_space_number_trunc.log文件,该日志目录由innodb_log_group_home_dir定义。如果在truncate操作期间发生系统故障,临时日志文件允许启动进程识别被截断的undo表空间,并继续操作。

4.binlog 缓存相关
使用二进制日志缓存并且值达到了binlog_cache_size设置的值,用临时文件存储来自事务的变化这样的事务数量。可通过Binlog_stmt_cache_disk_use状态变量中单独跟踪。

总结

1.从上述了解来看MySQL临时表,平时的监控和优化是避免不了的。
2.除此之外,在业务实现中也可以适当的使用,如作为中间表临时保存少量信息 等。
3.在复制环节中binlog_format等于ROW模式时,临时表相关是不记录binlog日志的(除了drop命令),这部分需要注意。
【相关推荐:mysql视频教程】

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

mysql

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

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

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

2026.11.17

20

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

60

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

60

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

20

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

60

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

40

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

180

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

140

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

160

15

热门下载

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

精品课程

更多
相关推荐
/
热门推荐
/
最新课程