第42问：MySQL 8.0 的临时表会让一片磁盘空间"消失"

在 MySQL 8.0 中, 使用临时表时, 会发现有1G的磁盘空间"消失"了

实验

我们先宽油做一个 MySQL 8.0.25 的实例. 此处我们忽略创建的步骤, 大家可参考以前的实验.

还是用我们熟悉的翻倍法, 造一张表:

不停执行最后一句 SQL , 让表中含有足够多的记录:

这里我们设置两个临时表的配置参数, 稍后再解释其作用:

我们还需要设置好 performance_schema , 用来观察整个过程:

还需要记录一下目前的磁盘容量:

现在我们下一个使用临时表的 SQL , 参考实验6:

在 SQL 执行的过程中, 观察一下磁盘空间:

数据库的磁盘总量全程并没有变化, 而磁盘总量会逐渐增长, 增长1G左右, 而后又会降下来

这段时间到底发生了什么呢?

我们来梳理一下 MySQL 8.0.25 中临时表的使用过程:

1. 在 8.0.25 中, 临时表默认的引擎为 TempTable , 会先在内存里创建内存临时表
2. 当所有内存临时表的总大小达到 temptable_max_ram 限制后, MySQL 会使用 mmap 机制, 将一部分磁盘映射作为内存使用, 这个过程中磁盘使用量会上升。 (我们在实验中将 temptable_max_ram 设置为最小值, 是为了让 MySQL 尽早使用 mmap 机制, 实验会方便一点)
3. 当所有内存临时表通过 mmap 分配的内存量 (实际是磁盘) 达到 temptable_max_mmap 限制后, MySQL 会将内存临时表转换成磁盘临时表(引擎为 InnoDB 或 MyISAM ). (我们在实验中将 temptable_max_ram 设置为1G)
4. SQL 结束后, 临时表会被清理, 这个过程中, 磁盘使用量会下降

我们重新做一次这个实验, 研究一下怎么观察这个过程:

当然还是通过 performance_schema , 可以看到通过 mmap 分得的内存大小(实际是磁盘大小)

除了使用 performance_schema , 还有其他手段观察么?

我们还可以通过 procfs 的 smaps 观察到这片空间

可以看到: 通过 mmap 分配的空间的两个特点:

1. 其分配的区域大小会逐步翻倍
2. 其对应了一个已经删除的文件

当达到 temptable_max_mmap 限制后, 内存临时表会转换为磁盘临时表 (InnoDB/MyISAM 表) , 也可以通过 performance_schema 观察到这个步骤:

这就是 "消失的磁盘" 的真相: MySQL 使用了 mmap , 将磁盘空间映射到了内存中, 作为内存使用.

小贴士 当 SQL 执行完成后, 我们再仔细观察一下 performance_schema :

当 SQL 执行完后, 可以观察到临时表已经回收 (磁盘空间已经下降), 但 CURRENT_NUMBER_OF_BYTES_USED 并不会归零. 这个原因是 MySQL 并没有将这片空间的回收计在 SQL 的线程上, 而是计入了全局统计:

所以会导致线程级别的统计值看上去 "只增不减", 使用该值做统计时需小心