MySQL各版本临时表机制
5墨值下载
一、背景
二、MySQL的临时表和临时文件
2.1 临时表分类
2.2 内存临时表
2.3 磁盘临时表
2.3.1 MySQL5.6中的临时表
2.3.2 MySQL5.7中的临时表
2.3.3 MySQL8.0中的临时表
2.4 优化器隐式创建临时表
2.5 临时文件
三、相关参数
四、总结
一、背景
最近在使用MySQL5.6和5.7的过程中，碰到了两个问题。
问题一
由于一个大操作导致磁盘临时表暴涨，磁盘打满报错。
问题二
由于一个大查询导致临时表空间ibtmp1暴涨，从而导致磁盘打满，数据库无法响应。但是在相同
配置的8.0中却无法复现，为此我分析了MySQL各版本对临时表的处理方式，特此分享。
二、MySQL的临时表和临时文件
2.1 临时表分类
MySQL的临时表分为两种，一种是用户创建的临时表，另一种是由优化器创建的内部临时表；临时
表同时又可以分为内存临时表和磁盘临时表。
2.2 内存临时表
内存临时表有Memory引擎和Temptable引擎，Memory引擎从MySQL5.6开始可以使用，
Temptable引擎是8.0引入的新的引擎。Memory引擎不管实际字符多少，都是用定长的空间存储，
Temptable引擎会用变长的空间存储，提高了内存中的存储效率，有更多的数据可以放在内存中处理而
不是转换成磁盘临时表。
2.3 磁盘临时表
磁盘临时表分为MyISAM临时表、InnoDB临时表。在MySQL5.6以及以前的版本，磁盘临时表和临
时文件都是放在临时目录tmpdir下的，磁盘临时表的undolog都是与普通表的undo放在一起(由于磁盘
临时表在数据库重启后就被删除了，不需要redolog通过崩溃恢复来保证事务的完整性，所以不需要写
redolog，但是undolog还是需要的，因为需要支持回滚)。在MySQL 5.7之前，产生的临时表是
MYISAM，而且只能是MYISAM。从5.7开始提供了参数internal_tmp_disk_storage_engine来定义磁盘
临时表引擎，可选值为MYISAM和INNODB，并且把内部的临时表默认保存在临时表空间ibtmp1(可以
用参数innodb_temp_data_file_path 设置初始大小，最大大小和步长)下，推荐设置最大,否则可能磁盘
空间会因为大查询打满，出现文章开头的问题。
但是在MySQL 5.7中没有解决如下问题:
从MySQL8.0开始，临时表可以使用特有的引擎TempTable,解决了VARCHAR字段的变长存储以及
大对象的内存存储问题。由变量internal_tmp_mem_storage_engine来控制，可选值为TempTable和
Memory；新引擎的大小由参数temptable_max_ram来控制，默认为1G。超过了则存储在磁盘上。并
且计数器由表performance_schema.memory_summary_global_by_event_name来存储。如果设置的
磁盘临时表是InnoDB或者MYISAM，则需要一个转换拷贝的消耗。为了尽可能减少消耗，Temptable提
出了一种overflow机制，即如果内存临时表超过配置大小，则使用磁盘空间map的方式，即打开一个文
件，然后删除，留一个句柄进行读写操作。读写文件格式和内存中格式一样，这样就略过了转换这一
步，进一步提高性能。这个功能是在MySQL8.0.16版本中才有的。
在MySQL5.7中，磁盘临时表的数据和undo都被独立出来，放在临时表空间ibtmp1中。之所以把
临时表独立出来，主要是为了减少创建删除表时维护元数据的开销。
在MySQL8.0中，磁盘临时表的数据单独放在会话临时表空间池(#innodb_temp目录下的ibt文件)
里面，临时表的undo放在全局表空间ibtmp1里面。另外一个大的改进是，8.0的磁盘临时表数据占用的
空间在连接断开后，就能释放给操作系统，而5.7的版本中需要重启才能释放。
2.3.1 MySQL5.6中的临时表
在MySQL5.6中，磁盘临时表位于tmpdir下，磁盘临时表的undolog都是与普通表的undo放在一起
(注意由于磁盘临时表在数据库重启后就被删除了，不需要redolog通过奔溃恢复来保证事务的完整性，
所以不需要写redolog，但是undolog还是需要的，因为需要支持回滚)。文件名类似#sql_4d2b_8_0，
其中#sql是固定的前缀，4d2b是进程号的十六进制表示，8是MySQL线程号的十六进制表示(show
processlist中的id)，0是每个连接从0开始的递增值，ibd是innodb的磁盘临时表(通过参数
default_tmp_storage_engine控制)。临时表名字为#sql_bec0_14.MYD为myisam引擎临时表，#sql:来
自tmp_file_prefix是宏定义，bec0:来自mysqld的当前进程号，14:临时表缓冲区的某种槽号。
在5.6中，磁盘临时表创建好后，对应的frm以及引擎文件就可以在tmpdir下查看到。在连接断开
后，相应文件会自动删除。因此，在5.6的tmpdir里面看到很多类似格式文件名，可以通过文件名来判
断是哪个进程，哪个连接使用的临时表，这个技巧在排查tmpdir目录占用过多空间的问题时尤其适用。
用户显式创建的这种临时表，在连接释放的时候，会自动释放并把空间释放回操作系统。
阿里云RDS有个loose_rds_max_tmp_disk_space参数控制最大磁盘临时表空间，开源MySQL没有这个
参数。RDS这个参数也是为了避免磁盘临时表过大导致磁盘空间被打满了导致实例锁定等问题。
2.3.2 MySQL5.7中的临时表
在MySQL5.7中，临时磁盘表位于ibtmp1文件中，ibtmp1文件位置及大小控制方式由参数
innodb_temp_data_file_path控制，阿里云RDS默认ibtmp1可以无限扩展。显式创建的表的数据和
undo都在ibtmp1里面。用户连接断开后，临时表会释放，但是仅仅是在ibtmp1文件里面标记一下，空
间是不会释放回操作系统的。如果要释放空间，需要重启数据库。另外，需要注意的一点是，5.6可以
在tmpdir下直接看到创建的文件，但是5.7是创建在ibtmp1这个表空间里面，因此是看不到具体的表文
件的。如果需要查看，则需要查看INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO这个表，里
面有一列name，这里可以看到表名。
1、VARCHAR的变长存储
如果临时表的字段定义是VARCHAR(200)，那么映射到内存里处理的字段变为CHAR(200)，造成浪费；
2、大对象的内存存储
比如 TEXT，BLOB， JSON等，都会直接转化为磁盘存储。
of 5
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论