mysql3张表关联查询_故障分析 | MySQL 临时表空间数据过多导致磁盘空间不足的问题排查

一、活动背景

合作客户和现场人员反映，某RDS实例出现磁盘不足报警，需要排查。

报警信息：

报警内容：

数据库数据盘不足，磁盘使用率超过80%。

数据库磁盘不足，磁盘使用率超过80%。

二、调查过程

登录报警服务器，检查磁盘空间，搜索大容量文件，发现端口号4675的实例临时表空间大小为955G，导致86%的磁盘被占用。用过的;

推测与库中执行长SQL，产生大量临时数据写入临时表空间有关。

看到这样一条SQL，继续分析它的执行计划；

很明显，图中标记的点是临时计算的使用，说明临时表空间的快速增长与其有关。这条SQL执行了三表关联，每张表有几十万行数据。三表关联在where条件中没有设置关联字段，形成笛卡尔积，所以会产生大量的临时数据；而且都是全表扫描加载了太多的临时数据；它还涉及排序和生成临时数据；这些方面导致空间很快就被填满。

3、解决方案

与项目组沟通后，杀死会话，解决问题；

但是这条SQL停止了，临时表空间中的临时数据没有释放；

最后通过重启mysql数据库，临时表空间中的临时数据被释放。这只能通过重新启动来释放。

4. 分析原理

通过查看官方文档，官方的解释是这样的：

翻译：

根据官网文档，临时表空间将在正常关闭或初始化中止期间被删除，并在每次服务器启动时重新创建。之所以重启可以释放空间，是因为数据库正常关闭时，临时表空间被删除，重启后重新创建。也就是说，重启会导致临时表空间被重建并重新初始化。因此重建后的大小为12M。

上述一点可以从错误日志中得到验证：

5.官网尺寸说明

6. 如何避免

1.限制临时表空间的大小，允许自动增长，但最大容量有上限。本例中，由于设置了自动增长，但没有上限，结果为

有955G。

正确的参数配置方法：

[]

=:12M::最大:500M

参考官方文档：

大小的上限已设定。当数据文件达到最大大小时，查询将失败，并显示错误消息，指示表已满，无法执行查询，以避免太大。

2、发送如本例这样的多表相关SQL时，应保证有相关字段和索引，避免笛卡尔积式全表扫描。临时数据应针对具有 group by、order by 和多表相关 SQL 的 SQL 进行评估。根据金额，对SQL进行审计。未经审核，不允许上线执行。

3、特别注意使用，执行前检查执行计划。

7.其他补充品

1> 检查SQL的执行生成临时表并通过字典表使用临时表空间：

查询字典表：sys.x$

* 来自 sys.x$，其中像 '%' 和 db='test' 这样的查询 order by ct, desc\G;

查询字典表：sys.

* 来自系统。其中像 '%' 和 db='test' 这样的查询 order by ct, desc\G;

两表查询结果相同，各列含义如下：

查询：规范化语句字符串。

db：语句的默认数据库，如果没有则为 NULL。

：该语句已执行的总次数。

：定期出现的语句的总等待时间。

：该语句出现时创建的内存临时表的总数。

：该语句出现时创建的内部磁盘临时表的总数。

uery：每次该语句发生时创建的内部临时表的平均数量。

ct：内存临时表已转换为磁盘表的百分比。

：该声明首次出现的时间。

：最近发布声明的时间。

：声明摘要。

参考链接：

从字典表ct的结果可以看出，内存临时表转为磁盘表的比例为100%，说明通过复现这个查询，它的临时计算结果已经放到了磁盘上，进一步证明说明这个查询和临时表空间容量的快速增长有关。

2> 调查.7中杀死长SQL会话但容量不缩水的问题；

源码链接：

从文章中的解释来看，会话被杀死后，临时表被释放，但被标记为删除。该空间不会返回给操作系统。只有重新启动才能释放空间。

3> 接下来，使用.0运行相同的查询，看看是否有什么不同；

mysql版本：8.0.18

当这个sql跑满磁盘的时候，发现这个sql生成的临时数据和5.7不一样。 7保存在 中，由于磁盘已满，SQL执行失败，磁盘空间很快被释放；

问：8.0版本的临时表空间如何使用？

通过查看8.0的官方文档，我们了解到8.0的临时表空间分为会话临时表空间和全局临时表空间。会话临时表空间存储用户创建的临时表，在配置为磁盘内部临时表的存储引擎时进行优化。服务器创建的内部临时表，当会话断开时，其临时表空间将被截断并释放回池中；也就是说，在8.0中有一个专用的会话临时表空间，当会话被杀死时，磁盘空间可以被回收；原来的是当前的全局临时表空间，存放的是改变用户创建的临时表的回滚段。 5.7中，它存储用户创建的临时表和磁盘上的内部临时表；

也就是说8.0和5.7中目的发生了变化。 5.7版本中临时表的数据存储在。8.0版本中临时表的数据存储在会话临时表空间中。如果临时表发生改变，则改变的undo数据存储在中间；

实验验证：将之前的查询结果保存到临时表中，对应的会话为45号。通过查看对应的字典表，可以看到45号会话使用.ibt表空间。通过将查询保存到临时表中，可以使用会话临时表空间，如下所示：

接下来杀掉45号会话，发现.ibt空间已经被释放，变成初始大小，状态为。这证明可以通过杀死.0中的会话来释放临时表空间。

总结：在.7版本中，如果kill了，临时表就会被释放，但是如果只是在ibtmp文件中进行标记，空间不会被释放回操作系统。如果想释放空间，需要重启数据库；在.0中，可以通过杀死会话来释放临时表空间。

八、参考文件

关于艾克森

是国内开源数据库解决方案的领导者、工业互联网高维数据应用的创新者。 Axon为工业互联网创新应用提供高性价比、快速实施的多数据库管理平台、分布式数据库系统、数据库容器云平台、多地多中心跨云容灾解决方案。

在工业互联网相关垂直行业，深入分析数据价值，构建数据中台、业务中台基础软件PaaS平台，用数据技术驱动企业高质量成长。公司产品已广泛应用于各行业，累计用户超过400家，其中包括工商银行、中国人寿、中国太保、国家电网、上汽集团、中国移动等30多家世界500强企业，华为。