乐趣区

关于mysql8:技术分享-MySQL-内部临时表是怎么存放的

jiezi

作者：胡呈清

爱可生 DBA 团队成员，善于故障剖析、性能优化，集体博客：https://www.jianshu.com/u/a95…，欢送探讨。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

如果 SQL 在执行过程中读到的数据无奈间接失去后果，那么就须要额定的内存来保留两头后果，得出最终后果，这个额定的内存就是外部长期表。比方 group by 执行时，就须要构建一个长期表，须要额定的字段保留聚合函数的后果，当然为了避免内存应用过大，个别超出某个限度后就会放到磁盘上。对于哪些操作会产生外部长期表，能够查看官网文档：https://dev.mysql.com/doc/ref…，上面次要介绍 MySQL8.0 外部长期表寄存形式的变动。

MySQL 5.6

MySQL 5.6 中，外部长期表大小超过内存限度后是在长期目录中的单个表文件表空间中创立的，如果禁用了 innodb_file_per_table，则在数据目录中的 InnoDB 共享表空间（ibdata1）中创立，很容易造成 ibdata1 过大，并且无奈开释，只能逻辑导出数据迁徙到新实例解决。

MySQL 5.7

MySQL 5.7 在长期表空间上做了改良，曾经实现将长期表空间从 InnoDB 共享表空间或者独立表空间中拆散，当初叫共享长期表空间。益处有二：

  1. 能够打消为每个长期表创立和删除的性能老本；
  2. 是一块独自为外部长期表划分的表空间，重启 mysqld 能够重置其大小，防止 MySQL5.6 时 ibdata1 难以开释的问题。

其体现是 MySQL 启动时 datadir 下会创立一个 ibtmp1 文件，默认值下会有限扩大。例如，如果某个 SQL 执行时创立了一个大小为 20MB 的外部磁盘长期表，则创立时默认大小为 12MB 的长期表空间文件会扩大到 20MB 以适应该表。当 SQL 执行完删除长期表时，开释的空间能够从新用于新的长期表，但 ibtmp1 文件放弃扩大大小，只有重启 MySQL 时才会真正回收共享长期表空间变成初始大小 12MB。

相干参数：

  • tmp_table_size&max_heap_table_size，外部长期表是存在内存中的，应用 MEMORY 存储引擎，如果大小超过了这两者较小的值，则会转化为磁盘长期表；
  • internal_tmp_disk_storage_engine：如果外部长期表转化为磁盘长期表，则这个参数指定了磁盘长期表的存储引擎，默认是 INNODB，还能够设置为 MYISAM；
  • innodb_temp_data_file_path：指定了长期表空间的地位和大小，默认值为 ibtmp1:12M:autoextend，即 datadir/ibtmp1，初始大小 12M 能够有限扩大，倡议限度一个最大值避免把磁盘撑满。

毛病：SQL 执行完产生的外部长期表可能很大，必须要重启能力开释。这点曾一度让我很困惑，为什么不能做的更好一点执行完就开释呢？所幸 MySQL8.0 优化了这个问题。

MySQL 8.0

MySQL 8.0 又有较大变动，新增了一些参数：

  • internal_tmp_mem_storage_engine：用来指定在内存中的外部长期表的存储引擎，默认值 TempTable，而非以前默认的 MEMORY
  • temptable_max_ram：定义 TempTable 存储引擎开始在磁盘上存储数据之前能够占用的最大内存量，默认值 1G
  • temptable_use_mmap：定义当 TempTable 存储引擎占用的内存量超过 temptable_max_ram 变量定义的限度时，TempTable 存储引擎是否为内存中的外部长期表调配空间作为内存映射的临时文件。禁用 temptable_use_mmap 时，将应用 InnoDB 磁盘外部长期表代替。默认值 ON，8.0.16 引入，8.0.26 弃用。
  • temptable_max_mmap：定义 TempTable 存储引擎在开始将数据存储到磁盘上的 InnoDB 外部长期表之前，被容许从内存映射的临时文件调配的最大内存量（以字节为单位）。设置为 0 将禁用从内存映射的临时文件分配内存。默认值 1G，8.0.23 引入。

内存映射临时文件

也就是说，默认状况下执行 SQL 产生外部长期表，应用的存储引擎从 MEMORY 变成了 TempTable，当然 TempTable 仍然是一种内存表，能够应用的最大内存是 1G（默认）。当大小超过 1G，会应用 内存映射临时文件作为外部长期表的溢出机制，大白话就是避免内存应用太大，把内存中的数据放在临时文件中。

然而你想想，关系型数据库设计了存储引擎这么好的货色来存放数据，这时候用文件来存是不是过分了点？预计官网是这么想的：哎呀外部长期表很小的，我就长期放放，你忍忍。起初发现有些外部长期表太大了忍不了，为了避免内存映射临时文件过大，8.0.23 版本引入一个新参数 temptable_max_mmap 来限度其大小，如果超过其大小（默认 1G），则转化为磁盘长期表（这点和 MySQL 5.7 统一）。值得注意的是 temptable_use_mmap 参数 8.0.26 标记被弃用了，官网文档也提醒倡议设置为 0 将其敞开，所以集体了解 应用内存映射临时文件作为外部长期表的溢出机制 是一个蹩脚的计划。

TempTable

为什么要把外部长期表默认引擎换成 TempTable？它与 MEMORY 最大的不同是：

  • 能够反对变长类型，例如 varchar(100)的数据”abcd”应该只占用 4 个字节而非 100 个字节，节俭内存；
  • 反对二进制大对象，例如 blob, text 等。如果应用 MEMORY 引擎，这样的外部长期表会间接应用磁盘长期表，这个是为了晋升性能。

那么真的那么好用吗？目前最新版本是 8.0.26，还是存在一些问题的，例如：
https://bugs.mysql.com/bug.ph…
https://bugs.mysql.com/bug.ph…
https://bugs.mysql.com/bug.ph…
https://bugs.mysql.com/bug.ph…

前 3 个都是性能问题，前面一个则可能会导致 SQL 执行时报错：The table ‘/tmp/#sql639b7_13_4’ is full，所以在这些问题解决前，倡议设置internal_tmp_mem_storage_engine=MEMORY。

长期表空间

MySQL 8.0 长期表空间也产生了变动，分为了 会话长期表空间 和全局长期表空间 内，全局长期表空间内和 MySQL 5.7 时没什么两样，不过 SQL 产生的外部长期表将存储在 会话长期表空间 中。

新参数：

  • innodb_temp_tablespaces_dir：定义了创立会话长期表空间的地位，默认地位是数据目录中 #innodb_temp 的目录
shell> ls datadir/#innodb_temp
temp_10.ibt  temp_2.ibt  temp_4.ibt  temp_6.ibt  temp_8.ibt
temp_1.ibt   temp_3.ibt  temp_5.ibt  temp_7.ibt  temp_9.ibt

会话长期表空间其实是个蕴含 10 个长期表空间的池，会话长期表空间在第一次申请创立磁盘长期表时从长期表空间池中调配给会话。一个会话最多调配两个表空间，一个用于用户创立的长期表，另一个用于优化器创立的外部长期表。当会话断开连接时，其长期表空间被革除并开释回池中。

测试景象

temptable_use_mmap = ON 时，如果外部长期表超过了 temptable_max_ram 大小，应用内存映射的临时文件用作外部长期表的溢出机制，临时文件放在 tmpdir 目录下：

能够看到临时文件数量 +1，磁盘长期表数量不变：

temptable_use_mmap = OFF 时，如果外部长期表超过了 temptable_max_ram 大小，应用 InnoDB 磁盘外部长期表用作外部长期表的溢出机制，寄存在 innodb 会话长期表空间中，与 MySQL 5.7 的区别是，session 断开后就会开释空间，不须要重启 MySQL：

能够看到临时文件数量不变，磁盘长期表数量 +1：

退出移动版