mysql 5.7 临时表_MySQL 5.7 内部临时表

最新推荐文章于 2024-11-18 20:39:03 发布

认知能力训练

最新推荐文章于 2024-11-18 20:39:03 发布

阅读量292

点赞数

CC 4.0 BY-SA版权

文章标签： mysql 5.7 临时表

本文链接：https://blog.csdn.net/weixin_33372672/article/details/113218985

本文探讨了一种特殊情况下的InnoDB性能问题，即在没有明显写操作的情况下，InnoDB行插入数量出现突然增长的现象。通过对客户案例的研究，发现这并非由DML语句引起，而是由SELECT查询导致。进一步分析表明，这是由于MySQL使用InnoDB引擎作为内部磁盘临时表的默认存储引擎，从而在执行某些SELECT查询时创建了大量的InnoDB行。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本文研究了在没有写查询的情况下，InnoDB行插入时，因内部临时表的问题而发生性能尖刺的情形。

In this blog post, I investigate a case of spiking InnoDB Rows inserted in the absence of a write query, and find internal temporary tables to be the culprit.

事情发生在我研究一个客户的案例时，在”InnoDB行插入“指标图上，发现了从1k行每秒激增到6K行的尖刺，但却无法和其他活动或者现象连接起来，PMM监控图形上也有同样的反映。

Recently I was investigating an interesting case for a customer. We could see the regular spikes on a graph depicting “InnoDB rows inserted” metric (jumping from 1K/sec to 6K/sec), however we were not able to correlate those spikes with other activity. The innodb_row_inserted graph (picture from PMM demo) looked similar to this (but on a much larger scale):

其他例如句柄和接口的图形都没有显示同样的尖刺，在无法开启general log的情况下，我们尝试检查了所有的日志，performance_schema，触发器，存储过程，预编译语句，甚至包括binlog后发现没有任何单个的写查询语句可以导致每秒插入飙升到6K行。

Other graphs (Com*, Handler) did not show any spikes like that. I’ve examined the logs (we were not able to enable general log or change the threshold of the slow log), performance_schema, triggers, stored procedures, prepared statements and even reviewed the binary logs. However, I was not able to find any single **\write* query which could have caused the spike to 6K rows inserted.

在最后才发现，行插入飙升一定和DML有关的这种想法是错误的，出乎意料的是，尖刺是由于SELECT查询导致的，但为何SELECT查询会导致大量的InnoDB行插入操作呢？

Finally, I figured out that I was focusing on the wrong queries. I was trying to correlate the spikes on the InnoDB Rows inserted graph to the DML queries (writes). However, the spike was caused by SELECT queries! But why would SELECT queries cause the massive InnoDB insert operation? How is this even possible?

原来是与磁盘临时表有关。在MySQL 5.7版本中，内部磁盘临时表的默认引擎是InnoDB引擎，这就意味着当SELECT操作需要在磁盘上创建临时表时(例如GROUP BY操作)，就会使用到InnoDB引擎。