mysql临时表使用场景

MySQL临时表在很多场景中都会用到，比如用户自己创建的临时表用于保存临时数据，以及MySQL内部在执行复杂SQL时，需要借助临时表进行分组、排序、去重等操作。下面将会对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临时表注意事项

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

加上合适的索引

在where条件中过滤更多的数据

重写SQL，优化执行计划

如果不得不使用临时表，那么一定要减少并发。建议使用SSD硬盘。

使用innodb_rollback_segments配置选项定义回滚segment的数量，默认设置是128，也是最大值。一个回滚segment总是分配给系统表空间，32个回滚segment预留给临时表空间(ibtmp1)。因此，要分配回滚段来撤消表空间，将innodb_rollback_segments设置为大于33的值。配置单独的undo表空间时m，syste表空间中的回滚段将呈现为非活动状态。

从上述了解来看MySQL临时表，平时的监控和优化是避免不了的。

除此之外，在业务实现中也可以适当的使用，如作为中间表临时保存少量信息 等。

在复制环节中binlog_format等于ROW模式时，临时表相关是不记录binlog日志的（除了drop命令），这部分需要注意。