子查询本身不直接写临时表，真正触发tmp_table_size耗尽的是它引发的物化、排序、哈希等操作；例如IN(SELECT...)默认生成无索引内存临时表，ORDER BY未配LIMIT则全量排序，EXPLAIN出现Using temporary即为明确信号。
为什么子查询会让临时表突然爆满
子查询本身不直接写临时表，真正触发 tmp_table_size 耗尽的是它引发的物化、排序、哈希等操作。比如 ORDER BY 写在子查询里，外层又没用 LIMIT，MySQL 就得先把千万行全排好——哪怕最后只取 10 条；IN (SELECT ...) 在 MySQL 5.7 及以前默认生成无索引的内存临时表，一超限立刻落盘。
- 常见信号：
EXPLAIN输出里出现Using temporary，且Extra列没带索引提示 -
SHOW PROCESSLIST状态卡在Creating tmp table或Copying to tmp table on disk - 慢查日志中
Rows_examined是Rows_sent的几十倍以上
用 JOIN 替代 IN/NOT IN 子查询
这是最直接有效的改写方式，能绕过临时表物化环节，让优化器走哈希连接或索引查找。
- 原写法（低效）：
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM logins WHERE login_time > NOW() - INTERVAL 30 DAY) - 改写后（高效）：
SELECT u.* FROM users u LEFT JOIN logins l ON u.id = l.user_id AND l.login_time > NOW() - INTERVAL 30 DAY WHERE l.user_id IS NULL - 关键点：把过滤条件
login_time > ...下推到ON子句，而不是留在WHERE；确保logins(user_id, login_time)有联合索引 - 注意：如果
logins表太大且user_id选择性差，先建CREATE TEMPORARY TABLE temp_active (user_id BIGINT UNSIGNED PRIMARY KEY) ENGINE=Memory预聚合再JOIN
子查询里别写 ORDER BY，除非配 LIMIT
ORDER BY 在子查询中纯属浪费资源——外层查询不会继承排序结果，优化器也无法下推，只会强制内存排序再丢弃。
- 错误示范：
SELECT * FROM (SELECT * FROM orders ORDER BY created_at DESC) t LIMIT 10→ 全表排序后截断 - 正确做法：
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10→ 直接走索引 + limit pushdown - 若必须分步（如先按用户聚合再排序），用 CTE 或临时表显式控制生命周期：
WITH top_orders AS (SELECT user_id, MAX(created_at) AS last_order FROM orders GROUP BY user_id) SELECT * FROM top_orders ORDER BY last_order DESC LIMIT 10 - MySQL 8.0+ 支持 CTE 物化控制，但老版本建议用
CREATE TEMPORARY TABLE显式建表并加索引
临时表字段类型和索引必须严格匹配主表
很多人建了 TEMPORARY TABLE 却没提速，问题常出在隐式转换或缺失索引上。
- 字段类型要一致：主表
id是BIGINT UNSIGNED，临时表也必须声明为BIGINT UNSIGNED NOT NULL PRIMARY KEY，否则JOIN会失效 - 必须加主键或唯一索引：
ENGINE=Memory不自动建索引，没索引的JOIN就是嵌套循环，10 万 × N 行直接卡死 - 字符集陷阱：主表用
utf8mb4_0900_as_cs，临时表建表时没指定校对集，默认可能是utf8mb4_general_ci，大小写或重音匹配失败 - 别盲目调大
tmp_table_size：如果字段含TEXT或VARCHAR(1024)，MEMORY 引擎会按最大长度分配内存，很快撑爆
实际执行时，最容易被忽略的是子查询中 GROUP BY 字段的基数控制——比如对 user_agent（高基数字符串）分组，比对 user_id 分组多出几万倍中间桶，临时表空间就在这一步悄悄炸开。
