mysql临时表的作用是什么

共计 2919 个字符，预计需要花费 8 分钟才能阅读完成。

今天丸趣 TV 小编给大家分享一下 mysql 临时表的作用是什么的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。

mysql 临时表的作用：1、用户自己创建的临时表用于保存临时数据；2、当用户在执行复杂 SQL 时，可以借助临时表进行分组，排序，去重等操作，并且默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁。

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 临时表相关参数

1.max_heap_table_size：用户创建的内存表的最大值，也用于和 tmp_table_size 一起，限制内部临时表在内存中的大小。
2.tmp_table_size：内部临时表在内存中的的最大值，与 max_heap_table_size 参数共同决定，取二者的最小值。如果临时表超过该值，就会从内存转移到磁盘上。
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.Created_tmp_disk_tables：执行 SQL 语句时，MySQL 在磁盘上创建的内部临时表数量，如果这个值很大，可能原因是分配给临时表的最大内存值较小，或者 SQL 中有大量排序、分组、去重等操作，SQL 需要优化。

2.Created_tmp_files：创建的临时表数量

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

4.Slave_open_temp_tables statement 或则 mix 模式下才会看到有使用。
slave_open_temp_tables 的值显示，通过复制，当前 slave 创建了多少临时表，binlog_format 只能是 statement 和 mixed 下有效.
备注：stop slave 也没有用，必须主库手动删除 或则 session 退出 才可以。
以下是从库 binlog 记录信息：

MySQL 临时表注意事项

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

可以为临时表空间设置一个最大值，比如 10G，如下：
innodb_temp_data_file_path = ibtmp1:128M:autoextend:max:10G
当临时表空间达到最大值 10G 时，SQL 执行将会报错，影响应用的正常执行。
对于临时表空间过大的问题，通常也有一些其他方法解决，比如：
将临时表的存储引擎设置为 myisam，虽然可能有一些性能问题，但不会导致磁盘空间问题。

2.SQL 语句：
（1）加上合适的索引
（2）在 where 条件中过滤更多的数据
（3）重写 SQL，优化执行计划
（4）如果不得不使用临时表，那么一定要减少并发。建议使用 SSD 硬盘。

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

就是说超过 128 回滚 segement 的时候，就需要临时表出来救急。

tablespace - segment - extent(64 个 page，1M) - page（16kb）

2）truncate undo
当 innodb_undo_log_truncate 触发的时候，undo 表空间截断操作在服务器日志目录中创建一个临时的 undo_space_number_trunc.log 文件，该日志目录由 innodb_log_group_home_dir 定义。如果在 truncate 操作期间发生系统故障，临时日志文件允许启动进程识别被截断的 undo 表空间，并继续操作。

4.binlog 缓存相关
使用二进制日志缓存并且值达到了 binlog_cache_size 设置的值，用临时文件存储来自事务的变化这样的事务数量。可通过 Binlog_stmt_cache_disk_use 状态变量中单独跟踪。

以上就是“mysql 临时表的作用是什么”这篇文章的所有内容，感谢各位的阅读！相信大家阅读完这篇文章都有很大的收获，丸趣 TV 小编每天都会为大家更新不同的知识，如果还想学习更多的知识，请关注丸趣 TV 行业资讯频道。