一 简介: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

   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导致的临时文件残留