如何限制临时表的大小？

Question

我在数据库中有一些较大的（InnoDB）表格；显然，用户能够进行SELECT和JOIN操作，这将导致临时的、大的（因此位于磁盘上的）表格。有时，这些表格会变得非常庞大，以至于耗尽磁盘空间，从而导致各种奇怪的问题。

是否有一种方法可以限制临时表的最大大小，以免表格超出磁盘空间？tmp_table_size仅适用于内存中的表格，尽管名字是这样。我在文档中没有找到相关信息。

- Piskvor left the building

---

是的，我知道存储空间很便宜，最好的解决方案是“防止查询”。然而，这些选项目前不在我的选择范围内。 - Piskvor left the building

有些“巨大”的查询可以重新构造以避免生成大型临时表。您能更改查询吗？如果可以，请为每个相关表提供SELECT ...，EXPLAIN SELECT ...和SHOW CREATE TABLE。缩小临时表可能会_很可能_加快速度。 - Rick James

这里有一些指南(https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html)，将帮助你优化查询。 - user7161651

是的，我知道查询可以进行优化（请参见我的 mysql 标签中的答案）。不幸的是，如果数据集有数百GB，并且查询有些是动态构建的，那么我们已经尝试过很少有用的优化了。（是的，我知道大数据仓库不适合使用MySQL，更好的解决方案正在研究中 - 寻找一个临时解决方案）感谢您提供文档链接，我会将其编辑到问题中。 - Piskvor left the building

Answer 1

MariaDB和MySQL中没有这个选项。几个月前我也遇到了同样的问题，搜索了很多资料，最终通过在NAS上创建一个特殊的存储区域来部分解决了它。

在您的NAS上创建一个文件夹或内部硬盘分区，它将被定义为有限的大小，然后挂载它，在mysql ini中将临时存储分配给此驱动器：（选择Windows / Linux）

tmpdir="mnt/DBtmp/"
tmpdir="T:\"

此更改后应重新启动mysql服务。

采用这种方法，一旦驱动器已满，您仍会遇到磁盘上查询的“奇怪问题”，但其他问题已消失。

- Thomas G

---

尽管不是一个完整的解决方案，但这似乎是唯一可行的权宜之计。谢谢。 - Piskvor left the building

Answer 2

有关选项disk-tmp-table-size的讨论曾经存在过，但看起来提交的内容没有通过审核或因其他原因丢失了（至少该选项在当前代码库中不存在）。

我想你下一步最好的尝试（除了增加存储空间）是调整MySQL以避免生成磁盘上的临时表。DBA上有一些提示可以参考。另一个尝试是为“磁盘上”的临时表创建一个ramdisk，如果你有足够的RAM并且只缺少磁盘存储空间。

- Ulrich Thomas Gabor

---

10多年内可能会发生很多事情；我在任何一个服务器中都没有找到这个选项。感谢您提供的关于内存建议，但是对于一个250 GB的临时表而言，这比“获取磁盘”更不可能实现。 - Piskvor left the building

目前看来这似乎是唯一的选择。我也无法找到更多关于它的信息。 - user7161651

是啊，我也是。因此设置赏金。 - Piskvor left the building

Answer 3

尽管无法解答关于MySQL的问题，MariaDB具有tmp_disk_table_size和潜在有用的max_join_size设置。然而，tmp_disk_table_size仅适用于MyISAM或Aria表，不适用于InnoDB。此外，max_join_size仅适用于联接的预计行数，而不是实际行数。好的方面是，错误几乎立即发出。

- user824276

---

谢谢，那可能会很有用！ - Piskvor left the building