子查询易触发磁盘临时表，因优化器在无法下推条件或关联字段无索引时会隐式物化结果；一旦超出tmp_table_size/max_heap_table_size限制，便退化为磁盘表，引发高i/o。
为什么子查询容易触发磁盘临时表
MySQL 在执行 IN、NOT IN、ANY/SOME、ALL 等子查询时，若优化器判断无法直接下推条件或关联字段无索引，会隐式创建内部临时表来缓存子查询结果。一旦该临时表体积超过 tmp_table_size 或 max_heap_table_size（取较小值），就会退化为磁盘表（.IBD 文件），引发大量随机 I/O。
典型信号包括：EXPLAIN 输出中出现 Using temporary，且 rows 值远高于预期；SHOW STATUS LIKE 'Created_tmp_disk_tables' 计数持续上升；慢日志里出现 Copying to tmp table 阶段耗时占比高。
用 JOIN 替代 IN 子查询的实操要点
多数 IN (SELECT ...) 场景可安全转为 INNER JOIN，前提是子查询结果集不为空或逻辑允许去重。
- 原写法：
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active'); - 改写后：
SELECT o.* FROM orders o INNER JOIN customers c ON o.customer_id = c.id WHERE c.status = 'active';
关键点：
- 必须确保
customers.id和orders.customer_id上都有索引，否则 JOIN 本身会全表扫描 - 如果子查询含
GROUP BY或聚合，JOIN 后需加DISTINCT或调整逻辑，避免重复行 -
NOT IN必须换为NOT EXISTS，因为NOT IN遇到NULL会整个条件返回UNKNOWN，结果为空
当必须用子查询时，如何控制临时表大小
无法彻底消除子查询时，要主动约束其输出规模和结构，避免膨胀成磁盘表。
- 在子查询中尽早加
WHERE过滤，不要把“先查全量再过滤”留到外层 - 显式指定需要的列，避免
SELECT *—— 字段越少，临时表内存占用越小 - 对子查询结果加
LIMIT（如业务允许）：例如IN (SELECT id FROM logs WHERE type='error' ORDER BY ts DESC LIMIT 100) - 检查并调大当前会话的
tmp_table_size和max_heap_table_size，但注意全局设置过大会挤占 Buffer Pool
警惕那些看似简单却暗藏临时表的写法
以下写法常被忽略，但 MySQL 内部仍会建临时表：
-
SELECT DISTINCT col FROM t：无索引时必走临时表去重 -
SELECT ... GROUP BY a, b ORDER BY b：若ORDER BY字段不在GROUP BY列中，且无覆盖索引，会强制建临时表排序 -
UNION（非UNION ALL）：去重过程需临时表 + 排序 -
ORDER BY中使用函数，如ORDER BY UPPER(name)：无法利用索引，大概率触发Using filesort+ 临时表
真正难处理的不是“要不要用子查询”，而是子查询嵌套在 GROUP BY 或 ORDER BY 上游时，优化器失去下推能力——这时候往往得拆成显式 CREATE TEMPORARY TABLE 并手动建索引，反而更可控。
