慢速下降临时表
我为报告目的构建临时表时遇到了一个有趣的 MySQL 表问题。
我发现如果没有指定存储引擎，DROP TEMPORARY TABLE 命令将挂起长达半秒。
如果我将表定义为 ENGINE = MEMORY，这个短暂的挂起就会消失。
由于我有解决这个问题的方法（使用 MEMORY 表），我的问题是为什么临时表需要很长时间才能删除？他们默认不使用MEMORY引擎吗？它甚至不是一个很大的表，只有几百行，包含我当前的测试数据。
Ran into an interesting problem with a MySQL table I was building as a temporary table for reporting purposes.
I found that if I didn't specify a storage engine, the DROP TEMPORARY TABLE command would hang for up to half a second.
If I defined my table as ENGINE = MEMORY this short hang would disappear.
As I have a solution to this problem (using MEMORY tables), my question is why would a temporary table take a long time to drop? Do they not use the MEMORY engine by default? It's not even a very big table, a couple of hundred rows with my current test data.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
默认情况下，临时表将在 mysql 配置指定的位置创建，通常是 /tmp 或磁盘上的其他位置。您可以将此位置（甚至多个位置）设置为 RAM 磁盘位置，例如 /dev/shm。
希望这有帮助！
Temporary tables, by default, will be created where ever the mysql configuration tells it to, typically /tmp or somewhere else on a disk. You can set this location (and even multiple locations) to a RAM disk location such as /dev/shm.
Hope this helps!
如果临时文件是使用 InnoDb 引擎创建的，如果您的默认引擎是 InnoDb，并且 InnoDb 缓冲池很大，则 DROP TEMPORARY TABLE 可能需要一些时间，因为它需要扫描所有要丢弃的页面。
在对此的评论中提到了堆栈溢出问题。
另请注意，DROP (TEMPORARY) TABLE 使用的 LOCK 可能会对所有服务器产生巨大影响。例如，请参阅此。
在我的工作中，最近服务器速度变慢，因为我们有 80 Gb 的 InnoDb 缓冲池，并且一些 SQL 请求已使用 InnoDb 临时表进行了优化。
每 5 分钟大约 100 个这样的 DROP TEMPORARY TABLE 请求就足以产生巨大的影响。而且这个问题很难调试，因为慢速查询日志会告诉我们，其他表中主键访问的单行的更新需要两秒钟，并且存在大量此类更新。但即使大部分查询时间都花在这些更新上，问题实际上还是由 DROP TEMPORARY TABLE 请求造成的。
If the temporary file is created with InnoDb engine, which may be the case if your default engine was InnoDb, and the InnoDb buffer pool is large, DROP TEMPORARY TABLE may take some time since it needs to scan all pages to discard.
It was mentionned in a comment to this stack overflow question.
Note also that DROP (TEMPORARY) TABLE uses a LOCK that may have huge impact on all your server. See for example this.
At my work, we recently had a server slow down because we had an InnoDb buffer pool of 80 Gb and some SQL requests had been optimized using InnoDb temporary tables.
About 100 such DROP TEMPORARY TABLE requests every 5 minutes were sufficient to have a huge impact. And the problem was hard to debug since slow query log would tell us that UPDATEs of a single row accessed by primary key in some other table was taking two seconds, and there was an enormous amount of such updates. But even if most query time was spent on these updates, the problem was really because of the DROP TEMPORARY TABLE requests.