作者：Corrado Pandiani
背景
最近遇到了一个关于 MySQL 完全耗尽磁盘空间的问题。这是个严重的问题，因为会导致服务不断地中断，客户会不断的重启服务并在不久后再次宕机。
究竟发生了什么？在本文中，将解释原因并找到解决方案。
隐式临时表
MySQL 需要创建隐式临时表来解决某些类型的查询。往往查询的排序阶段需要依赖临时表。例如，当您使用 GROUP BY，ORDER BY 或DISTINCT 时。这样的查询分两个阶段执行：首先是收集数据并将它们放入临时表中，然后是在临时表上执行排序。
tmp_table_size 或 max_heap_table_size 值定义，以较小者为准。MySQL 5.7 中的默认大小为 16MB。如果运行查询的数据量较大，或者尚未查询优化，则可以增加该值。设置阈值时，请考虑可用的 RAM 大小以及峰值期间的并发连接数。你无法无限期地增加变量，因为在某些时候你需要让 MySQL 使用磁盘上的临时表。注意：如果涉及的表具有 TEXT 或 BLOB 列，则即使大小小于配置的阈值，也会在磁盘上创建临时表。
临时表的存储引擎
在 MySQL 5.6 之前，所有磁盘上的临时表都默认创建为 MyISAM 类型。临时表是在内存中，还是在磁盘上创建，具体取决于配置，并在查询结束时立即删除。从 MySQL 5.7 开始，它们默认创建为 InnoDB 类型。
可以使用新的配置项来设置临时表的存储引擎：internal_tmp_disk_storage_engine ，可选值为 InnoDB（默认）或 MyISAM。
InnoDB 类型的临时表存在的潜在问题
尽管使用 InnoDB 是性能最佳的，但可能会出现新的潜在问题。在某些特定情况下，您可能会出现磁盘耗尽和服务器中断。
存在未优化的查询，需要在磁盘上创建非常大的的临时表
存在优化的查询，但他们正在磁盘上创建非常大的临时表，因为你正在对此数据集进行计算（统计，分析）
高并发连接时，运行相同的查询，伴随临时表的创建
没有很多可用空间
那么，如何避免磁盘耗尽和中断呢？
简单的解决方案：使用更大的磁盘
[mysqld]
innodb_temp_data_file_path = ../../tmp/ibtmp1:12M:autoextend
需要重启 MySQL 。注意，必须将路径指定为相对于数据目录。
设置 ibtmp1 大小的上限
[mysqld]
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G
在这种情况下，文件不能超过 10GB。可以降低宕机概率，但也是一个危险的解决方案。当数据文件达到最大值时，会查询失败并显示一个错误，提示表已满。
这个解决方案似乎违反直觉，但它可能是快速避免中断的最佳方法，并保证使用所有需要的临时表。
internal_tmp_disk_storage_engine = MYISAM
SET GLOBAL internal_tmp_disk_storage_engine = MYISAM;
虽然总是有可能看到相同的问题，以防你可以在同一时间运行查询或非常接近。在我的实际案例中，这是避免所有中断的解决方案。
优化你的查询
在表上创建缺少的索引
如果不需要，可以在查询中添加更多过滤条件以更少收集的数据
重写查询以优化执行计划
可以在应用程序中使用队列管理器来序列化它们的执行或减少并发性
但希望在所有优化之后，您可以返回将临时存储引擎设置为 InnoDB 以获得更好的性能。
结论
有时这些改进会产生意想不到的副作用。用于磁盘上临时表的 InnoDB 存储引擎是一个很好的改进，但在某些特定情况下，例如，如果您有未优化查询和很少的可用空间，则可能因“磁盘已满”错误而中断。将 tmp 存储引擎退回到 MyISAM 是避免中断的最快方法，但是为了返回到 InnoDB，查询的优化是更重要的事情。更大或专用的磁盘也可能有所帮助。但这是一个微不足道的建议。
顺便提一下，关于这个问题的 issue ：
https://bugs.mysql.com/bug.php?id=82556
进一步阅读：
http://mysqlserverteam.com/mysql-5-7-innodb-intrinsic-tables/ https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html
阅读原文： https://www.percona.com/blog/2019/07/17/mysql-disk-space-exhaustion-for-implicit-temporary-tables/
近期社区动态
