MySQL在处理复杂查询时，如果无法通过索引直接获取结果，往往会借助临时表来完成排序、分组或去重等操作。当这些临时表占用过多内存甚至溢出到磁盘时，就会成为性能瓶颈，导致查询变慢，甚至系统资源耗尽。排查这类问题，核心在于识别哪些查询正在创建临时表，以及这些临时表为何会变得庞大，然后针对性地进行优化。
解决方案要系统地排查MySQL临时表相关问题，我们需要从监控、定位、分析到优化，形成一个闭环。首先，通过全局状态变量了解临时表的整体使用情况。
SHOW GLOBAL STATUS LIKE 'Created_tmp%';是一个很好的起点，
Created_tmp_tables表示创建的内存临时表数量，
Created_tmp_disk_tables则表示因为内存不足或其他原因溢出到磁盘的临时表数量。如果
Created_tmp_disk_tables的比例很高，或者增长速度很快，那无疑是一个明确的信号。
接下来，我们需要深入到具体查询层面。
EXPLAIN是我们最常用的工具，当查询计划中出现
Using temporary时，就说明这个查询正在使用临时表。结合慢查询日志 (
slow_query_log)，我们可以找出那些执行时间长且使用了临时表的罪魁祸首。对于正在运行的查询，
SHOW PROCESSLIST可以帮助我们实时观察到
Creating temporary table的状态，这对于排查突发性性能问题特别有效。
一旦定位到问题查询，就需要分析其具体逻辑。通常，
GROUP BY、
ORDER BY、
DISTINCT、复杂的
UNION或子查询，以及包含
BLOB/
TEXT列的聚合操作，都是临时表的常见触发器。理解这些操作如何与数据量、索引和配置参数（如
tmp_table_size和
max_heap_table_size）相互作用，是解决问题的关键。优化策略包括但不限于：创建合适的索引以避免文件排序和临时表、重写查询以简化逻辑、调整MySQL配置参数以允许更大的内存临时表，或者在可能的情况下，重新设计表结构以减少对大字段的聚合操作。 临时表何时从内存溢出到磁盘？
我个人觉得，理解临时表从内存溢出到磁盘的机制，是排查这类问题的基础。MySQL在创建内部临时表时，会优先尝试在内存中创建
HEAP表。这个内存限制主要受两个参数控制：
tmp_table_size和
max_heap_table_size。其中，
tmp_table_size是每个会话可以使用的内存临时表的最大大小，而
max_heap_table_size则是所有
HEAP表（包括用户自定义的和内部创建的）的全局最大大小。实际生效的是这两个参数中的较小值。
当一个内存临时表的大小超过了这个限制时，MySQL就会将其自动转换为磁盘上的
MyISAM或
InnoDB临时表。这就像你往一个杯子里倒水，水满了自然就会溢出来。除了大小限制，还有另一个重要的因素：如果临时表中包含
BLOB或
TEXT这样的长文本/二进制列，MySQL会直接在磁盘上创建临时表，因为它认为这些数据类型不适合存储在内存
HEAP表中。
磁盘临时表的性能开销是显而易见的，它涉及到磁盘I/O操作，这比内存操作慢了几个数量级。所以，一旦我们发现
Created_tmp_disk_tables数量异常，就得警惕了，这意味着有很多查询正在遭受磁盘I/O的拖累。 如何识别哪些查询正在使用临时表？
要找出具体哪些查询正在使用临时表，有几种非常实用的方法，我的经验是结合使用效果最好。
首先，也是最直接的，是使用
EXPLAIN命令。当你对一个查询执行
EXPLAIN后，观察
Extra列。如果里面出现了
Using temporary，那就明确无误地告诉你，这个查询正在使用临时表。如果还看到
Using filesort，那通常意味着MySQL需要对数据进行排序，而这个排序操作也可能涉及到临时表，或者至少是文件排序缓冲区，同样是性能消耗点。
Post AI
博客文章AI生成器
50
查看详情
其次，慢查询日志 (
slow_query_log) 是一个宝藏。在MySQL配置文件中开启慢查询日志，并设置一个合理的
long_query_time。更重要的是，可以设置
log_queries_not_using_indexes和
log_temporary_tables（这个在某些版本中可能需要通过
log_output='FILE'和
log_slow_admin_statements=1间接实现，或者直接通过
performance_schema监控）。这样，所有执行时间超过阈值且使用了临时表的查询都会被记录下来，方便你批量分析。
最后，
performance_schema提供了更细粒度的监控能力。通过查询
performance_schema.events_statements_summary_by_digest或
performance_schema.events_statements_current等表，你可以找到那些创建了临时表的查询的详细信息，包括它们的执行次数、平均执行时间，以及是否使用了临时表。例如，
events_statements_summary_by_digest表中的
SUM_CREATED_TMP_TABLES和
SUM_CREATED_TMP_DISK_TABLES列可以帮助你聚合哪些查询模板最常创建临时表。结合
SHOW PROCESSLIST观察
State列，当看到
Creating temporary table时，你可以立即获取到正在执行的查询语句。 优化策略：除了增加内存，还能做些什么？
当然，简单地增加
tmp_table_size和
max_heap_table_size往往只是治标不治本，甚至可能掩盖了更深层次的问题。真正的优化，需要我们从查询本身和表结构入手。
一个核心的策略是优化索引。很多时候，临时表的创建是为了完成
GROUP BY或
ORDER BY操作，如果能为这些操作涉及的列创建合适的复合索引，MySQL就能直接利用索引的有序性，避免创建临时表进行排序。例如，
SELECT col1, COUNT(*) FROM my_table GROUP BY col1 ORDER BY col1;如果
my_table.col1上有索引，MySQL很可能直接使用索引进行分组和排序，而不需要临时表。
重写查询也是一个非常有效的手段。有时候，一个复杂的查询可以通过拆分成几个简单的步骤，或者改变连接顺序、使用派生表等方式来避免临时表。比如，
DISTINCT操作如果应用在大结果集上，很容易触发临时表。如果你的目标只是去重并计数，可以考虑使用
GROUP BY代替
DISTINCT，在某些场景下，
GROUP BY的优化空间更大。此外，避免在
WHERE子句中对索引列进行函数操作，这会导致索引失效，进而可能需要全表扫描和临时表。
调整其他相关配置参数也值得关注。
sort_buffer_size影响着
ORDER BY和
GROUP BY操作在内存中进行排序的缓冲区大小。虽然它不直接控制临时表大小，但如果排序缓冲区不足，MySQL可能会转向磁盘文件排序，这与磁盘临时表一样，都会带来性能损耗。适当增加这个值，可能减少对磁盘的依赖。
最后，表结构设计也有一席之地。如果你的业务场景频繁需要对包含
BLOB/
TEXT列的表进行聚合或排序，那么重新审视这些大字段的设计和使用方式就很有必要了。例如，是否可以只在必要时才加载这些大字段，或者将它们存储在单独的表中，通过ID关联，避免它们进入临时表，从而迫使临时表溢出到磁盘。这些都是需要结合实际业务场景来权衡的。
以上就是mysql如何排查临时表相关问题的详细内容，更多请关注知识资源分享宝库其它相关文章！
相关标签： mysql 工具 ssl ai 配置文件 mysql 数据类型 count select union using table 大家都在看： mysql如何查看安装后的版本信息 mysql如何实现异地备份 mysql如何配置master服务器 mysql如何删除用户账户 mysql安装后如何配置默认字符集
发表评论:
◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。