mysql临时表更新数据原理_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  默认/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导致的临时文件残留