凌晨 1 点，告警铃声砸醒了我
企业微信群里一条消息：“磁盘满了，1 小时涨了 1TB！”
我脑子嗡地一声 —— 之前见过这阵仗，都不是什么好事儿。
最快的一次，是某个 SaaS 厂商的 MySQL 实例在 30 分钟内直接炸了，原因竟然是一条 JOIN 查询 + ORDER BY 引发的临时表失控。
当时的场景是这样的：业务方没事儿来一个关联 10 张表的复杂查询，MySQL 一看 “哎呀，我需要在内存里排序”，结果内存装不下，它就说 “好吧，我写磁盘上去”—— 然后，磁盘缓存文件就像喝了激素一样，每秒钟往上蹿。
这就是on-disk 临时表的 “杀招”—— 无声、无息、直到你的硬盘尖叫！
现象：诡异的目录暴涨
打开监控截图一，var/lib/mysql这个文件夹就好像着了魔一样，早晨 8 点的时候还一切正常，到了 9 点整，容量表直接从 100G 猛增到 900G，并且这速度…… 实在是太令人吃惊了。
第一时间我的反应就是，binlog 肯定出问题了，可一检查，binlog 的 rotate，日志完完全全是正常的，这就挺奇怪的 —— 文件大小一直在不断往上涨，不过 binlog 自己却老老实实地按照预想的那样进行 rotate，没有一点儿不正常的地方。
那东西哪儿来的？
定位：#sql 打头的 “幽灵文件”
用了个硬核的命令：
bash
运行
lsof +D /var/lib/mysql
这一查不得了 —— 屏幕上闪过一堆文件句柄，我找到了凶手： #sql-ibxxx这一堆临时表文件，占了 900G！
问题就藏在这三个字母的组合里：#sql代表 MySQL 的内部临时表。
以#sql开头的文件是 MySQL 在处理复杂查询时生成的临时表，用于防止内存溢出，通常情况下，这类文件会在查询完成后立即被删除，不过这一次，这个垃圾清理员显然没有正常工作。
根因：JOIN+ORDER BY 在作妖
接下来是扒根源的环节。我去翻了一下业务的 SQL 日志，发现了这条 “定时炸弹”：
sql
SELECT * FROM orders JOIN customers ON orders.cid = customers.id JOIN products ON orders.pid = products.id ORDER BY products.description LIMIT 100;
这条 SQL 看起来再正常不过了。但从 MySQL 的角度，事情完全不一样 ——
- JOIN 三张表，结果集有数百万行
- ORDER BY 要排序，排序列是
products.description这个 BLOB 字段 - 这个 BLOB 字段太大了，MySQL 的内存无法装下整个结果集
- 内存被
tmp_table_size限制死了（默认 16M，真的太小了） - MySQL 的处理办法：“哎呀，内存装不下，那我写磁盘上去吧”—— 于是，on-disk 临时表就被创建了，而且没完没了地写、写、写……
关键的坑在这儿：tmp_table_size这个参数在这种情况下彻底失效！
我当时就想骂人。这个参数被做了这么多年的文档宣传，但你看，它在 BLOB 排序面前就成了 “纸老虎”。
验证：EXPLAIN 拆穿真相
为了确认这个 “罪魁祸首”，我跑了一个 EXPLAIN：
sql
EXPLAIN SELECT * FROM orders JOIN customers ON orders.cid = customers.id JOIN products ON orders.pid = products.id ORDER BY products.description LIMIT 100\G
结果里赫然出现了这两个标志：Using temporary ✗Using filesort ✗
还有一个关键的数值：Select_tables>2
这三个信号加在一起，翻译成人话就是：“我要在磁盘上创建临时表来干这件事，准备好了吗？”
解决：重构 SQL，让临时表滚蛋
光知道问题不行，得改！我采取了一个激进的策略：走覆盖索引，去掉SELECT *
重写后的 SQL 长这样：
sql
-- 先只查主键和需要排序的列，改用pid避免BLOB排序
SELECT oid, cid, pid FROM orders ORDER BY pid LIMIT 100;
-- 再通过主键Join一次获得完整数据
SELECT o.*, c.*, p.* FROM orders o JOIN customers c ON o.cid = c.id JOIN products p ON o.pid = p.id WHERE o.oid IN (SELECT oid FROM ...);
结果？临时文件从 900G 直接跌到 2G！效果好到不行。
预防：一条 my.cnf，救你十次命
为了防止这种 “黑天鹅” 事件再发生，我在my.cnf里加了这一行：
ini
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:50G
这组配置所传达的含义为，InnoDB 的临时表文件最长只会达到 50G，要是超过了这个界限，MySQL 会直接出现报错情况，而不是无限制地增加。
与其等着磁盘爆炸，不如提前给它加个 “天花板”。
总结与建议
现在回过头去审视，这场磁盘危机的实质其实挺简单 —— 就是一条编写得不够简洁的 SQL，在特定数据量的情况下，引发了 MySQL 的应急机制。
你可能没意识到，这类问题，在实际生产环境中远比想象中普遍，不少团队并未发现，他们每天执行的那些看似正常的 Join Query，实际上如同在钢丝上行走，只要数据量增大、并发量提升，系统会立刻面临崩溃风险。
所以我想给你的建议就三点：
- 定期用
EXPLAIN检查你的热点 SQL，看看有没有Using temporary和Using filesort这俩组合拳 - 有事没事就调调
tmp_table_size和innodb_temp_data_file_path，给自己留点 “呼吸空间” - 最关键的 —— 养成 “不写
SELECT *” 的习惯，用覆盖索引替代，这才是根本的解决方案
当再度看到磁盘告警时，不要惊慌，运用lsof +D来进行定位、借助EXPLAIN去确认、通过索引优化来解决。采用这三个办法去处理，便没有解决不了的问题。
你的关注是对我写作最大的鼓励，我们下期见！
声明
这篇文章当中九成是我自己撰写，仅有少量素材借助 AI 来生成，而且所有内容我都认真检查过了，图片素材要么是真实存在的，要么是 AI 原创的，这篇文章就是想要传播正能量，没有那些低俗不良的引导，希望读者能够明白。
