一 简介:mysql内部的临时表
二 磁盘表分类
1 内存临时表(引擎)
1 memory
2 Temptable(8.0+)
2 磁盘临时表
1 myisam
2 innodb
3 map表(8.0+)
三 创建方式
1 显式创建
create temporary table
1 指定引擎
2 默认引擎(default_tmp_storage_engine(默认innodb))
2 隐式创建
常见于查询,DDL
三 临时文件:
1 tmpdir
1 #sql_631a_1.MYD #sql_631a_1.MYI
2 #sql4d2b_8_0.ibd sql-6bd4_c.frm
2 文件解析
#sql是固定的前缀
#4d2b是进程号的十六进制表示
#8是MySQL线程号的十六进制表示(show processlist中的id)
#0 代表累加
四 临时文件常见场景
1 select慢查询
Using temporary
1 代表已经用到了临时表,但是不是一定会使用磁盘临时文件,除非超过tmp_table_size设置参数大小
2 在5.6中这种隐式创建的临时表，只能用MyISAM引擎,没有internal_tmp_disk_storage_engine这个参数可以控制
2 copy to tmp情况下的DDL操作
一旦语句被执行完或者kill掉临时文件就会消失
inplace情况下的DDL操作
这个记录的地方就是online log，当然如果改动少的话，直接存在内存里(参数innodb_sort_buffer_size可控制，同时这个参数也控制online log每个读写块的大小)面即可。这个onlinelog也是用临时文件存，创建在innodb_tmpdir，最大大小为参数innodb_online_alter_log_max_size控制，如果超过这个 大小了，DDL就会失败。临时文件的名字也类似上述的排序临时文件的名字。
五 临时表目录控制参数
5.6 tmpdir 默认/tmp/目录 是只读参数,不能在线调整
5.7 innodb_temp_data_file_path(临时表如果需要在磁盘释放必须重启数据库服务)
8.0 innodb_temp_tablespaces_dir
六 总结:
1 升级你的mysql版本 5.7使用独立临时表空间 不要设置为压缩方式,可以很大程度避免问题
2 tmp_table_size 设置一个合适的值,尽量避免应用磁盘的临时表文件
3 对于ddl-copy方式 尽量采用pt-osc方式,能不走临时表就不要走
4 优化你的慢语句,尽量减少use temporay
七 常见问题
1 由于慢查询堆积引起的大量临时文件导致磁盘暴涨
2 由于DDL执行期间mysql cash导致的临时文件残留