跳过复制到磁盘 mysql 上的 tmp 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7532307/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
skip copying to tmp table on disk mysql
提问by user765368
I have a question for large mysql queries. Is it possible to skip the copying to tmp table on diskstep that mysql takes for large queries or is it possible to make it go faster? because this step is taking way too long to get the results of my queries back. I read on the MySQL page that mysql performs this to save memory, but I don't care about saving memory I just want to get the results of my queries back FAST, I have enough memory on my machine. Also, my tables are properly indexed so that's not the reason why my queries are slow.
我有一个关于大型 mysql 查询的问题。是否可以跳过复制到 tmp 表的磁盘步骤，mysql 执行大型查询，或者是否可以使其运行得更快？因为这一步需要太长时间才能恢复我的查询结果。我在 MySQL 页面上读到 mysql 执行此操作以节省内存，但我不在乎节省内存我只想快速获取查询结果，我的机器上有足够的内存。此外，我的表已正确编制索引，因此这不是我的查询缓慢的原因。
Any help?
有什么帮助吗？
Thank you
谢谢
回答by RolandoMySQLDBA
There are two things you can do to lessen the impact by this
您可以做两件事来减轻这种影响
OPTION #1 : Increase the variables tmp_table_sizeand/or max_heap_table_size
选项 #1：增加变量tmp_table_size和/或max_heap_table_size
These options will govern how large an in-memory temp table can be before it is deemed too large and then pages to disk as a temporary MyISAM table. The larger these values are, the less likely you will get 'copying to tmp table on disk'. Please, make sure your server has enough RAM and max_connectionsis moderately configured should a single DB connection need a lot of RAM for its own temp tables.
这些选项将控制内存中临时表在被认为太大之前可以有多大，然后将页面作为临时 MyISAM 表写入磁盘。这些值越大，“复制到磁盘上的 tmp 表”的可能性就越小。请确保您的服务器有足够的 RAM，并且如果单个 DB 连接需要大量 RAM 用于其自己的临时表，请确保适当配置max_connections。
OPTION #2 : Use a RAM disk for tmp tables
选项 #2：为 tmp 表使用 RAM 磁盘
You should be able to configure a RAM disk in Linux and then set the tmpdirin mysql to be the folder that has the RAM disk mounted.
你应该能够在 Linux 中配置一个 RAM 磁盘，然后将mysql 中的tmpdir设置为安装了 RAM 磁盘的文件夹。
For starters, configure a RAM disk in the OS
首先，在操作系统中配置一个 RAM 磁盘
Create a folder in the Linux called /var/tmpfs
在 Linux 中创建一个名为 /var/tmpfs 的文件夹
mkdir /var/tmpfs
Next, add this line to /etc/fstab (for example, if you want a 16GB RAM disk)
接下来，将此行添加到 /etc/fstab（例如，如果您想要 16GB RAM 磁盘）
none /var/tmpfs tmpfs defaults,size=16g 1 2
and reboot the server.
并重新启动服务器。
Note : It is possible to make a RAM disk without rebooting. Just remember to still add the aforementioned line to /etc/fstab to have the RAM disk after a server reboot.
注意：可以在不重新启动的情况下制作 RAM 磁盘。请记住，在服务器重新启动后仍将上述行添加到 /etc/fstab 以获取 RAM 磁盘。
Now for MySQL:
现在对于 MySQL：
Add this line in /etc/my.cnf
在 /etc/my.cnf 中添加这一行
[mysqld]
tmpdir=/var/tmpfs
and restart mysql.
并重新启动mysql。
OPTION #3 : Get tmp table into the RAM Disk ASAP (assuming you apply OPTION #2 first)
选项 #3：尽快将 tmp 表放入 RAM 磁盘（假设您先应用选项 #2）
You may want to force tmp tables into the RAM disk as quickly as possible so that MySQL does not spin its wheels migrating large in-memory tmp tables into a RAM disk. Just add this to /etc/my.cnf:
您可能希望尽快将 tmp 表强制放入 RAM 磁盘，以便 MySQL 不会旋转其将大型内存中的 tmp 表迁移到 RAM 磁盘的轮子。只需将此添加到/etc/my.cnf：
[mysqld]
tmpdir=/var/tmpfs
tmp_table_size=2K
and restart mysql. This will cause even the tiniest temp table to be brought into existence right in the RAM disk. You could periodically run ls -l /var/tmpfsto watch temp tables come and go.
并重新启动mysql。这将导致即使是最小的临时表也会直接存在于 RAM 磁盘中。您可以定期运行ls -l /var/tmpfs以观察临时表的来来去去。
Give it a Try !!!
试一试 ！！！
CAVEAT
警告
If you see nothing but temp tables in /var/tmpfs 24/7, this could impact OS functionality/performance. To make sure /var/tmpfs does not get overpopulated, look into tuning your queries. Once you do, you should see less tmp tables materializing in /var/tmpfs.
如果您在 /var/tmpfs 24/7 中只看到临时表，这可能会影响操作系统功能/性能。为确保 /var/tmpfs 不会过度填充，请考虑调整您的查询。完成后，您应该会看到 /var/tmpfs 中出现的 tmp 表更少。
回答by hsibboni
You can also skip the copy to tmp table on diskpart (not answered in the selected answer)
您也可以跳过复制到磁盘部分的tmp 表（未在所选答案中回答）
- If you avoid some data types :
- 如果您避免某些数据类型：
Support for variable-length data types (including BLOB and TEXT) not supported by MEMORY.
from https://dev.mysql.com/doc/refman/8.0/en/memory-storage-engine.html(or https://mariadb.com/kb/en/library/memory-storage-engine/if you are using mariadb).
支持 MEMORY 不支持的可变长度数据类型（包括 BLOB 和 TEXT）。
来自https://dev.mysql.com/doc/refman/8.0/en/memory-storage-engine.html（或https://mariadb.com/kb/en/library/memory-storage-engine/，如果你正在使用 mariadb）。
- If your temporary table is small enough : as said in selected answer, you can
Increase the variables tmp_table_size and/or max_heap_table_size
- 如果您的临时表足够小：如所选答案中所述，您可以
增加变量 tmp_table_size 和/或 max_heap_table_size
But if you split your query in smaller queries (not having the query does not help to analyze your problem), you can make it fit inside a memory temporary table.
但是，如果您将查询拆分为较小的查询（没有查询无助于分析您的问题），您可以使其适合内存临时表。
