1. 一个被忽视的磁盘“爆仓”元凶如果你负责的服务器磁盘空间突然告急排查了日志文件、数据文件、备份文件甚至清理了各种缓存但空间依然在神秘地减少直到服务器因为磁盘写满而宕机那你很可能遇到了MySQL里一个“隐形”的吞食兽——临时表。这不是指那些你显式创建的CREATE TEMPORARY TABLE而是MySQL在执行查询时在背后默默创建的、用于内部处理的临时表。它们通常安静地来安静地去但一旦某些查询“失控”它们就会在磁盘上具体来说是在ibtmp1文件中疯狂生长最终吞噬掉所有可用空间。我遇到过不止一次生产环境报警监控显示磁盘使用率在几分钟内从70%飙升到100%导致数据库完全不可写。紧急登录服务器用du命令一层层排查最终发现罪魁祸首是/var/lib/mysql/ibtmp1这个文件大小已经膨胀到了几百个GB把整个数据盘都塞满了。重启MySQL服务后这个文件会释放空间但问题根源的查询还在它就像一颗定时炸弹随时可能再次引爆。这个问题的隐蔽性在于ibtmp1文件是InnoDB引擎用于存储临时表数据的专用表空间。与常规的数据表空间ibdata1或独立表空间.ibd文件不同它默认是“自扩展”且“不收缩”的。也就是说当查询需要磁盘临时表时ibtmp1文件会自动扩大以满足需求但当查询结束临时表被删除这部分磁盘空间并不会自动返还给操作系统而是被标记为空闲留给后续的临时表使用。只有重启MySQL实例这个文件才会被重建并恢复初始大小。因此一个需要大量磁盘临时表的复杂查询就可能一次性将ibtmp1文件撑得巨大并且这个“巨大”的状态会一直持续到下次重启。2. 临时表何时会从内存“逃逸”到磁盘要理解这个问题我们得先知道MySQL临时表的工作机制。MySQL在处理一些复杂查询时如果无法直接通过索引在内存中完成排序、分组或连接等操作就需要创建一个中间结果集这就是临时表。临时表优先在内存中创建使用的是MEMORY存储引擎速度极快。MySQL有一个关键的系统变量tmp_table_size以及它的最大值版本max_heap_table_size它定义了内存临时表的最大大小。默认值通常是16M或32M。那么什么情况下临时表会从内存“逃逸”到磁盘呢主要有以下几个触发器临时表的大小超过了tmp_table_size的限制这是最常见的原因。当查询生成的中间结果集太大内存放不下时MySQL就会将其转换为磁盘临时表。磁盘临时表默认使用InnoDB引擎由internal_tmp_disk_storage_engine变量控制默认为InnoDB数据就写入ibtmp1文件。临时表中包含BLOB或TEXT类型字段MEMORY存储引擎不支持这两种数据类型。因此只要查询的中间结果里包含了BLOB或TEXT列无论结果集多小MySQL都会直接使用磁盘临时表。使用了UNION查询UNION操作非UNION ALL需要去重这个去重过程通常需要借助临时表来完成。如果UNION的结果集较大就很容易触发磁盘临时表。GROUP BY和ORDER BY子句的列不同例如SELECT a, COUNT(*) FROM t GROUP BY a ORDER BY b。GROUP BY a产生一个临时结果但ORDER BY b要求按另一个字段排序这通常需要另一个临时表来完成排序增加了使用磁盘临时表的概率。子查询或派生表Derived Table特别是FROM子句中的子查询派生表如果未被优化器优化掉例如合并到外层查询它会被物化成一个临时表。如果这个派生表很大就会成为磁盘临时表。我们可以通过一个简单的实验来验证。首先查看当前临时表相关的设置SHOW VARIABLES LIKE ‘tmp_table_size’; SHOW VARIABLES LIKE ‘max_heap_table_size’; SHOW VARIABLES LIKE ‘internal_tmp_disk_storage_engine’;假设tmp_table_size是16M。我们创建一个有足够数据的表然后执行一个会产生大结果集的GROUP BY查询并使用EXPLAIN来观察-- 创建一个测试表并插入大量数据 CREATE TABLE test_large ( id INT AUTO_INCREMENT PRIMARY KEY, category VARCHAR(10), value INT, KEY idx_category (category) ); -- 这里可以插入几十万行数据让category的重复值很多 -- 执行一个会产生大结果集的GROUP BY EXPLAIN SELECT category, SUM(value), AVG(value), COUNT(*) FROM test_large GROUP BY category ORDER BY SUM(value) DESC;在EXPLAIN的输出中关注Extra列。如果你看到Using temporary 说明该查询需要创建临时表。如果同时看到Using filesort 说明还有文件排序也可能用临时表。但这还不能区分是内存还是磁盘临时表。更准确的方法是使用性能模式Performance Schema来监控。在MySQL 5.7及以上版本可以开启相关的事件观察器-- 确保性能模式已开启 UPDATE performance_schema.setup_consumers SET ENABLED ‘YES’ WHERE NAME LIKE ‘events_statements%’; UPDATE performance_schema.setup_instruments SET ENABLED ‘YES’, TIMED ‘YES’ WHERE NAME LIKE ‘statement/sql/%’; -- 执行你的查询 SELECT category, SUM(value), AVG(value), COUNT(*) FROM test_large GROUP BY category ORDER BY SUM(value) DESC; -- 然后查询最近语句的详细信息寻找临时表相关的指标 -- 注意具体表名可能因版本而异例如events_statements_summary_by_digest SELECT DIGEST_TEXT, SUM_CREATED_TMP_TABLES, SUM_CREATED_TMP_DISK_TABLES FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST_TEXT LIKE ‘%SELECT%test_large%’ ORDER BY LAST_SEEN DESC LIMIT 1;如果SUM_CREATED_TMP_DISK_TABLES大于0就说明这个查询模式使用了磁盘临时表。3. 如何定位正在“作恶”的查询当磁盘空间被ibtmp1快速撑满时我们急需找到是哪个或哪些查询导致的。因为临时表的生命周期很短查询结束即释放直接抓现行比较困难但我们可以通过以下几种方式定位元凶3.1 使用SHOW PROCESSLIST和INFORMATION_SCHEMA在问题发生时快速执行SHOW FULL PROCESSLIST;观察当前正在执行的所有连接和SQL语句。重点关注那些执行时间Time列很长的查询特别是状态State列中包含“Copying to tmp table”、“Creating sort index”或“Sending data”的。这些状态往往伴随着临时表的创建和大量数据处理。更详细的信息可以从INFORMATION_SCHEMA.PROCESSLIST表或SHOW PROCESSLIST的底层表中获取。此外INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO表仅当存在活动的磁盘临时表时才有数据可以显示当前正在使用的InnoDB磁盘临时表信息但它显示的信息比较有限。3.2 分析慢查询日志Slow Query Log这是事后分析最有力的工具。确保你的MySQL已经开启了慢查询日志并且合理设置了long_query_time参数比如设置为2秒。那些执行时间过长、导致创建巨大临时表的查询很大概率会被记录到慢日志中。在慢查询日志中除了执行时间还要关注几个关键指标Query_time: 查询总耗时。Lock_time: 锁等待时间。Rows_examined: 检查的行数。一个巨大的值可能意味着全表扫描极易产生大临时表。Rows_sent: 返回的行数。Created_tmp_disk_tables:创建的磁盘临时表数量。这是最直接的证据。Created_tmp_tables:创建的临时表总数包括内存和磁盘。Tmp_table_sizes: 临时表使用的总大小需要特定版本和配置。一个典型的“嫌疑犯”在慢日志中可能长这样# Time: 2023-10-27T08:15:00.123456Z # UserHost: app_user[app_host] [10.0.0.1] # Query_time: 45.672 Lock_time: 0.000 Rows_examined: 2500000 Rows_sent: 5000 # Created_tmp_disk_tables: 3 Created_tmp_tables: 3 SET timestamp1698387300; SELECT t1.*, t2.name, COUNT(t3.id) FROM large_table_a t1 LEFT JOIN large_table_b t2 ON t1.b_id t2.id LEFT JOIN detail_table t3 ON t1.id t3.a_id WHERE t1.status ‘active’ GROUP BY t1.id ORDER BY t1.created_at DESC LIMIT 1000;这个查询检查了250万行创建了3个临时表且全部在磁盘上执行了45秒嫌疑非常大。3.3 使用性能模式Performance Schema进行趋势分析对于MySQL 5.6及以上版本性能模式提供了更精细的监控能力。你可以定期查询events_statements_summary_by_digest表。这个表按SQL语句的摘要Digest进行聚合记录了诸如执行次数、总耗时、以及创建磁盘临时表的总次数和总大小等统计信息。通过以下查询你可以找出历史上创建磁盘临时表最多的那些SQL模式SELECT DIGEST_TEXT, COUNT_STAR AS exec_count, SUM_CREATED_TMP_DISK_TABLES AS disk_tmp_tables, SUM_CREATED_TMP_TABLES AS tmp_tables, ROUND(IFNULL(SUM_CREATED_TMP_DISK_TABLES / NULLIF(SUM_CREATED_TMP_TABLES, 0), 0) * 100, 2) AS pct_disk, AVG_TIMER_WAIT AS avg_latency FROM performance_schema.events_statements_summary_by_digest WHERE SUM_CREATED_TMP_DISK_TABLES 0 ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC LIMIT 10;这个结果能清晰地告诉你哪些类型的SQL语句是磁盘临时表的“惯犯”。注意DIGEST_TEXT是标准化后的SQL去掉具体值可能很长且可读性差。你需要结合业务逻辑来理解它对应的是哪个功能点的查询。4. 优化策略将“磁盘巨兽”关回“内存牢笼”找到问题查询后我们的目标就是优化它尽量避免或减少磁盘临时表的使用。下面是一些经过实战检验的优化策略按推荐顺序排列。4.1 增加tmp_table_size和max_heap_table_size这是最直接的方法但治标不治本。如果查询确实需要处理大量中间数据增大内存缓冲区可以延缓或避免向磁盘的转换。例如将其设置为256M或512MSET GLOBAL tmp_table_size 268435456; -- 256M SET GLOBAL max_heap_table_size 268435456; -- 256M重要提示max_heap_table_size必须大于等于tmp_table_size。修改全局变量后只对新建立的会话生效。永久生效需要写入配置文件my.cnf。盲目设置过大会占用过多内存影响其他进程需根据服务器总内存权衡。4.2 优化索引这是根本绝大多数情况下使用磁盘临时表是因为查询无法有效利用索引。优化索引是解决问题的核心。为GROUP BY和ORDER BY创建复合索引回顾第2节的原因如果GROUP BY a ORDER BY b导致临时表可以尝试创建索引(a, b)。这样数据库可以直接按索引顺序读取数据可能无需额外的排序和临时表。为连接条件和WHERE子句创建索引确保JOIN、WHERE中的字段都有合适的索引减少需要扫描和处理的数据量。数据量小了临时表自然就小了。使用覆盖索引Covering Index如果索引包含了查询所需的所有字段那么InnoDB可以直接从索引中获取数据无需回表查询数据行。这能极大减少IO和内存占用。例如对于SELECT category, COUNT(*) FROM test_large GROUP BY category一个(category)的索引就是覆盖索引。4.3 重写SQL查询有时换一种写法就能让优化器选择更高效的执行路径。避免SELECT *只选择需要的列特别是要避免选择BLOB/TEXT列它们会强制使用磁盘临时表。谨慎使用DISTINCT和UNION思考是否真的需要去重是否可以用UNION ALL代替UNIONUNION ALL不去重通常不需要临时表。优化子查询和派生表尝试将派生表FROM (SELECT ...) AS t重写为JOIN。对于关联子查询看是否能改用JOIN。使用EXISTS或IN时注意子查询返回的结果集大小。拆分复杂查询将一个复杂的多表关联、分组排序查询拆分成几个简单的步骤用程序逻辑或中间表来处理。虽然增加了复杂度但可控性更强。4.4 利用SQL_BIG_RESULT和SQL_SMALL_RESULT提示这是MySQL提供的优化器提示Hint告诉优化器你期望的结果集大小。SQL_BIG_RESULT告诉优化器结果集会很大建议直接使用磁盘临时表进行排序对于GROUP BY或DISTINCT。这听起来反直觉但有时优化器在内存和磁盘间犹豫不决会带来额外开销直接指明路径反而更高效。SQL_SMALL_RESULT告诉优化器结果集很小建议使用内存临时表。使用示例SELECT SQL_BIG_RESULT category, COUNT(*) FROM huge_table GROUP BY category;这个提示要慎用除非你非常确定结果集的大小特征否则可能适得其反。4.5 调整internal_tmp_disk_storage_engine在MySQL 5.7及以前磁盘临时表可以使用MyISAM或InnoDB。MyISAM的磁盘临时表会创建独立的.MYD和.MYI文件查询结束后会自动删除不会导致ibtmp1膨胀。但MyISAM不支持事务且表级锁在并发时可能有问题。从MySQL 8.0开始这个变量被移除强制使用InnoDB作为磁盘临时表引擎。如果你的版本是5.7且受困于ibtmp1膨胀可以尝试临时切换SET GLOBAL internal_tmp_disk_storage_engine ‘MyISAM’;但这只是一个缓解措施根本还是要优化查询。5. 紧急处置与日常防控管理ibtmp1文件当ibtmp1文件已经膨胀并占满磁盘时你需要紧急处理。同时也需要建立日常的防控机制。5.1 紧急情况下的“救火”步骤定位并终止罪魁祸首会话使用SHOW PROCESSLIST找到正在创建大临时表的查询用KILL [connection_id];命令终止它。这能立即停止ibtmp1的继续增长。重启MySQL实例终极手段重启MySQL服务是释放ibtmp1文件空间的唯一方法在正常操作下。因为重启时ibtmp1文件会被重建。这是生产环境的高风险操作务必在业务低峰期或有完整备份和故障转移方案的情况下进行。systemctl restart mysql # 或者 service mysql restart临时增加磁盘空间如果无法立即重启且能快速给服务器挂载新磁盘可以通过软链接将ibtmp1指向一个更大空间的位置。但这需要停机或非常谨慎的操作不推荐新手在生产环境尝试。5.2 日常配置与监控为ibtmp1设置大小上限这是最重要的预防措施。在MySQL配置文件my.cnf中为InnoDB临时表空间设置一个最大值防止其无限膨胀。[mysqld] innodb_temp_data_file_path ibtmp1:12M:autoextend:max:5G这个配置意味着ibtmp1文件初始大小为12M自动扩展但最大不超过5G。当达到5G上限后如果还有查询需要更多临时表空间将会收到“The table ‘/tmp/#sql_xxx’ is full”的错误。这虽然会导致查询失败但保护了磁盘不被撑爆是一种“熔断”机制。你需要根据服务器磁盘大小和业务情况设定一个合理的max值比如50G或100G。监控临时表使用情况监控ibtmp1文件大小通过监控系统如Zabbix, Prometheus定期采集/var/lib/mysql/ibtmp1文件的大小。监控状态变量定期收集MySQL状态变量。SHOW GLOBAL STATUS LIKE ‘Created_tmp%’;Created_tmp_disk_tables: 自启动以来创建的磁盘临时表总数。观察其增长速率。Created_tmp_tables: 创建的临时表总数。计算磁盘临时表占比Created_tmp_disk_tables / Created_tmp_tables如果这个比例持续很高说明很多查询都在用磁盘临时表需要系统性优化。Created_tmp_files: 创建的临时文件数注意这不完全是临时表文件。定期分析慢查询日志使用pt-query-digestPercona Toolkit中的工具等日志分析工具定期分析慢查询日志重点关注那些Created_tmp_disk_tables大于0的查询并将其纳入优化待办清单。使用更现代的版本MySQL 8.0在临时表方面做了不少改进例如引入了会话临时表空间每个会话可能有自己独立的临时表空间文件查询结束后自动回收在某些场景下可能对管理临时表空间更有帮助。考虑在测试后升级。6. 实战案例一个GROUP BYORDER BY的优化过程让我分享一个最近处理的真实案例。业务系统有一个报表查询速度越来越慢最终在高峰期导致磁盘空间告警。原始查询如下SELECT user_id, product_type, COUNT(order_id) as order_count, SUM(amount) as total_amount FROM orders WHERE create_time BETWEEN ‘2023-10-01’ AND ‘2023-10-31’ AND status ‘completed’ GROUP BY user_id, product_type ORDER BY total_amount DESC LIMIT 1000;orders表有数千万行数据create_time上有索引status选择性一般。问题分析EXPLAIN显示使用了create_time索引进行范围扫描但WHERE过滤后仍有上百万行数据。需要按(user_id, product_type)分组但这两个字段上没有联合索引。分组后需要按聚合结果total_amount即SUM(amount)排序这是一个派生列没有索引。Extra列清晰地显示了Using temporary; Using filesort。慢查询日志证实Created_tmp_disk_tables为1且临时表大小巨大。优化步骤第一反应是加索引。但这里有个矛盾GROUP BY的列是(user_id, product_type)而ORDER BY的是聚合函数结果。创建(user_id, product_type, amount)的索引对GROUP BY有帮助但对ORDER BY SUM(amount)没有直接帮助因为SUM是在分组后计算的。考虑利用覆盖索引减少数据量。我们创建了一个索引(status, create_time, user_id, product_type, amount)。这个索引包含了WHERE条件的所有列status, create_time以及查询中需要的所有其他列user_id, product_type, amount, order_id。order_id用于COUNTamount用于SUM。ALTER TABLE orders ADD INDEX idx_stat_time_cover (status, create_time, user_id, product_type, amount, order_id);重写查询在这个案例中查询逻辑已经比较清晰重写余地不大。我们曾尝试将ORDER BY total_amount DESC改为ORDER BY NULL先不做排序在应用层排序但业务要求必须在数据库层面完成。执行效果创建索引后EXPLAIN的type变成了range并且Extra列出现了Using index 这是一个极好的信号说明查询现在完全可以通过索引来获取数据无需回表。虽然Using temporary和Using filesort仍然存在因为最终还是要排序但由于需要从磁盘读取的数据量IO急剧减少内存中可以容纳更多的中间结果。实测下来查询时间从原来的近30秒降到了3秒以内并且Created_tmp_disk_tables从1变成了0临时表被成功限制在了内存中。这个案例的启示对于分组排序查询即使无法为ORDER BY的派生列直接创建索引通过创建覆盖索引来最大化减少需要处理的数据行数和数据量是避免使用磁盘临时表的最有效手段之一。优化器在内存中处理少量数据时游刃有余一旦数据量超过阈值它就会求助于磁盘性能便急转直下。我们的工作就是通过索引和SQL写法尽可能让数据量保持在那个阈值之下。