mysql 原理 ~ 临时表

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

转载于:https://www.cnblogs.com/danhuangpai/p/10812844.html