跳过复制到磁盘mysql上的tmp表

Question

对于大型mysql查询，我有一个问题。是否可以跳过mysql用于大型查询的复制到磁盘步骤上的tmp表，或者是否有可能使其运行得更快？因为这个步骤花费的时间太长，无法获得查询的结果。我在MySQL页面上看到mysql执行此操作是为了节省内存，但我不关心内存的节省--我只想快速返回查询的结果，我的机器上有足够的内存。而且，我的表被正确地编入索引，所以这不是我的查询速度慢的原因。

有什么帮助吗？

谢谢

Answer 1

你可以做两件事来减轻这种影响。

选项1:增加变量、tmp、和/或 最大值

这些选项将控制内存中的临时表在被认为太大之前可以有多大，然后将页作为临时的MyISAM表发送到磁盘。这些值越大，“复制到磁盘上的tmp表”的可能性就越小。请确保您的服务器有足够的内存，如果单个DB连接需要大量内存用于自己的临时表，那么最大值的配置是适度的。

选项2:使用RAM磁盘处理tmp表

您应该能够在Linux中配置一个RAM磁盘，然后将mysql中的tmpdir设置为安装了RAM磁盘的文件夹。

首先，在操作系统中配置RAM磁盘

在Linux中创建一个名为/var/tmpfs的文件夹

mkdir /var/tmpfs

接下来，将这一行添加到/etc/fstab (例如，如果您想要一个16 to的RAM磁盘)

none                    /var/tmpfs              tmpfs   defaults,size=16g        1 2

重新启动服务器。

注意:可以在不重新启动的情况下制作RAM磁盘。只需记住，在服务器重新启动后，仍要将前面提到的行添加到/etc/fstab，以使RAM磁盘具有。

现在是MySQL：

在/etc/my.cnf中添加这一行

[mysqld]
tmpdir=/var/tmpfs

然后重新启动mysql。

选项3:尽快将tmp表放入RAM磁盘(假设您首先应用选项#2 )

您可能希望尽快将tmp表强制放入RAM磁盘，这样MySQL就不会旋转其车轮，将内存中的大型tmp表迁移到RAM磁盘中。只需将此添加到/etc/my.cnf：

[mysqld]
tmpdir=/var/tmpfs
tmp_table_size=2K

然后重新启动mysql。这将导致即使是最小的临时表也会出现在RAM磁盘中。您可以定期运行ls -l /var/tmpfs来查看临时表的来来去去。

试试看!！

警告

如果只看到/var/tmpfs 24/7中的临时表，这可能会影响OS功能/性能。要确保/var/tmpfs不被过度填充，请查看查询的调优。这样做之后，您应该可以看到在/var/tmpfs中实现的tmp表较少。

Answer 2

您还可以跳过磁盘部件上的tmp表副本(所选答案中未回答)。

1. 如果您避免某些数据类型：

支持内存不支持的可变长度数据类型(包括BLOB和TEXT)。 来自https://dev.mysql.com/doc/refman/8.0/en/memory-storage-engine.html (如果您正在使用mariadb，也可以使用https://mariadb.com/kb/en/library/memory-storage-engine/ )。

1. 如果临时表足够小:如所选答案中所述，可以增加变量tmp_table_size和/或max_heap_table_size

但是，如果将查询拆分为较小的查询(没有查询无助于分析问题)，则可以将其放入内存临时表中。