MySQL临时表在很多场景中都会用到,比如用户自己创建的临时表用于保存临时数据,以及MySQL内部在执行复杂SQL时,需要借助临时表进行分组、排序、去重等操作。下面将会对MySQL临时表的一些概念、分类和常见问题进行描述。
MySQL临时表类型:
外部临时表,通过createtemporarytable语法创建的临时表,可以指定存储引擎为memory,innodb,myisam等等,这类表在会话结束后,会被自动清理。如果临时表与非临时表同时存在,那么非临时表不可见。showtables命令不显示临时表信息,可通过information_schema.temporary_tables系统表可以查看外部临时表的相关信息。内部临时表,通常在执行复杂SQL,比如groupby,orderby,distinct,union等等,执行计划中如果包含Usingtemporary,那么MySQL内部将使用自动生成的临时表,以辅助SQL的执行。MySQL临时表相关参数:
max_heap_table_size:用户创建的内存表的最大值,也用于和tmp_table_size一起,限制内部临时表在内存中的大小。tmp_table_size:内部临时表在内存中的的最大值,与max_heap_table_size参数共同决定,取二者的最小值。如果临时表超过该值,就会从内存转移到磁盘上。default_tmp_storage_engine:外部临时表(createtemporarytable创建的表)默认的存储引擎。internal_tmp_disk_storage_engine:磁盘上的内部临时表存储引擎,可选值为myisam或者innodb。使用innodb表在某些场景下,比如临时表列太多,或者行大小超过限制,可能会出现“RowsizetoolargeorToomanycolumns”的错误,这时应该将临时表的innodb引擎改回myisam。tmpdir:临时表目录,当临时表大小超过一定阈值,就会从内存转移到磁盘上,tmpdir变量表示磁盘上临时表所在的目录。MySQL临时表相关状态变量:
Created_tmp_disk_tables:执行SQL语句时,MySQL在磁盘上创建的内部临时表数量,如果这个值很大,可能原因是分配给临时表的最大内存值较小,或者SQL中有大量排序、分组、去重等操作,SQL需要优化。Created_tmp_tables:执行SQL语句时,MySQL创建的内部临时表数量。MySQL临时表可能导致磁盘可用空间减少
在MySQL5.7版本之前,临时表的存储引擎默认为myisam,myisam临时表在SQL执行结束后,会自动删除临时表。然而从5.7版本开始,临时表的默认存储引擎变为innodb,虽然在性能上有了一定的提升,但是由于innodb引擎的临时表共用表空间ibtmp1,导致在高并发下,多个session同时创建临时表时,该表空间会变得非常大,并且不能动态缩小,除非重启MySQL,否则无法释放。
我们可以为临时表空间设置一个最大值,比如10G,如下:
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:10G
当临时表空间达到最大值10G时,SQL执行将会报错,影响应用的正常执行。
对于临时表空间过大的问题,我们通常也有一些其他方法解决,比如:
将临时表的存储引擎设置为myisam,虽然可能有一些性能问题,但不会导致磁盘空间问题。所谓鱼和熊掌不可兼得。优化SQL,比如(1)加上合适的索引(2)在where条件中过滤更多的数据(3)重写SQL,优化执行计划(4)如果不得不使用临时表,那么一定要减少并发。如果经费不是问题,那么增大磁盘,也许是最快捷有效的方式。