dbvisualizer 创建临时表_多场景下MySQL临时表有什么用？

MySQL有一些平常会在一些配置里看到，但似乎没有太多的关注的参数或则功能，都是默默支持着整个MySQL体系运转，这里所介绍的就是临时表。

MySQL临时表在很多场景中都会用到，比如用户自己创建的临时表用于保存临时数据，以及MySQL内部在执行复杂SQL时，需要借助临时表进行分组、排序、去重等操作。下面将会对MySQL临时表的一些概念、分类和常见问题进行整理。

MySQL临时表类型

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

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

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

MySQL临时表相关参数

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

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

3.innodbtmpdir：online ALTER TABLE operations that rebuild the table maxtmp_tables；

4.defaulttmpstorage_engine：外部临时表(create temporary table创建的表)默认的存储引擎；

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

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

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

MySQL临时表相关状态变量

1.Createdtmpdisk_tables：执行SQL语句时，MySQL在磁盘上创建的内部临时表数量，如果这个值很大，可能原因是分配给临时表的最大内存值较小，或者SQL中有大量排序、分组、去重等操作，SQL需要优化；

2.Createdtmpfiles：创建的临时表数量；

3.Createdtmptables：执行SQL语句时，MySQL创建的内部临时表数量；

4.Slaveopentemp_tables statement 或则 mix模式下才会看到有使用；

slaveopentemptables 的值显示，通过复制，当前slave创建了多少临时表，binlogformat只能是statement 和 mixed 下有效。

备注：stop slave 也没有用，必须主库手动删除 或则 session退出 才可以。

以下是从库binlog记录信息：

MySQL临时表注意事项

...

✨ 接下来内容请访问原文(https://www.modb.pro/db/27858?YYF)进行查看~

更多数据库相关内容，可访问墨天轮(https://www.modb.pro/?YYF)进行浏览。