mysql临时表相关参数

MySQL临时表类型

1.外部临时表：通过create temporary table语法创建的临时表，可以指定存储引擎为memory，innodb, myisam等等，这类表在会话结束后，会被自动清理。如果临时表与非临时表同时存在，那么非临时表不可见。show tables命令不显示临时表信息。

可通过information_schema.INNODB_TEMP_TABLE_INFO系统表可以查看外部临时表的相关信息，这部分使用的还是比较少。

2.内部临时表：通常在执行复杂SQL，比如group by, order by, distinct, union等，执行计划中如果包含Using temporary，还有undo回滚的时候，但空间不足的时候，MySQL内部将使用自动生成的临时表，以辅助完成工作。

MySQL临时表相关参数

1.max_heap_table_size：用户创建的内存表的最大值，也用于和tmp_table_size一起，限制内部临时表在内存中的大小；

2.tmp_table_size：内部临时表在内存中的的最大值，与max_heap_table_size参数共同决定，取二者的最小值。如果临时表超过该值，就会从内存转移到磁盘上；

max_heap_table_size和tmp_table_size区别：

该参数用于决定内部内存临时表的最大值，每个线程都要分配，实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表。

3.innodb_tmpdir：online ALTER TABLE operations that rebuild the table max_tmp_tables；

4.default_tmp_storage_engine：外部临时表（create temporary table创建的表）默认的存储引擎；

5.innodb_temp_data_file_path：innodb引擎下temp文件属性。建议限制innodb_temp_data_file_path = ibtmp1:1G:autoextend:max:30G；

6.Internal_tmp_disk_storage_engine：磁盘上的内部临时表存储引擎，可选值为myisam或者innodb。使用innodb表在某些场景下，比如临时表列太多，或者行大小超过限制，可能会出现“ Row size too large or Too many columns”的错误，这时应该将临时表的innodb引擎改回myisam。tmpdir：临时表目录，当临时表大小超过一定阈值，就会从内存转移到磁盘上；

7.tmpdir变量表示磁盘上临时表所在的目录。

MySQL临时表注意事项

1.MySQL临时表可能导致磁盘可用空间减少：

在MySQL5.7版本之前，临时表的存储引擎默认为myisam，myisam临时表在SQL执行结束后，会自动删除临时表。然而从5.7版本开始，临时表的默认存储引擎变为innodb，虽然在性能上有了一定的提升，但是由于innodb引擎的临时表共用表空间ibtmp1，导致在高并发下，多个session同时创建临时表时，该表空间会变得非常大，并且不能动态缩小，除非重启MySQL，否则无法释放。

ibtmp1是非压缩的innodb临时表的独立表空间,通过innodb_temp_data_file_path参数指定文件的路径，文件名和大小，默认配置为ibtmp1:12M:autoextend，也就是说在支持大文件的系统这个文件大小是可以无限增长的。

临时表释放后，空间会释放，但是磁盘空间不会释放，空闲空间可以被复用。释放磁盘空间只能重启。