嵌套子查询易触发临时表落盘，因优化器无法下推过滤或复用结果，遇group by、order by、distinct、无索引关联字段或含text/blob/表达式时，mysql强制磁盘临时表，postgresql则依赖代价估算决定是否物化。
为什么嵌套子查询容易触发临时表落盘
MySQL 和 PostgreSQL 在处理多层子查询时，常因优化器无法下推过滤条件或重用中间结果，被迫物化每一层结果——尤其是当子查询含 GROUP BY、ORDER BY、DISTINCT 或关联字段无索引时。这时 EXPLAIN 里会频繁出现 DERIVED 或 MATERIALIZED，且 Extra 列带 Using temporary，意味着数据已进临时表。更糟的是，若子查询返回 TEXT、BLOB 或函数表达式（如 UPPER(name)），MySQL 直接跳过内存表，强制写磁盘。
把5层嵌套改写成JOIN或CTE的实操要点
不是所有嵌套都该扁平化，但超过3层、且内层被外层多次引用时，必须重构。核心是让数据库一次扫描、多路关联，而非反复执行子查询。
- 用
JOIN替代IN (SELECT ...)：把WHERE user_id IN (SELECT id FROM users WHERE region IN (...))改成JOIN users ON orders.user_id = users.id JOIN regions ON users.region = regions.code，并确保users.region和regions.code有索引 - PostgreSQL 可用
WITH提前物化关键中间集，但要加MATERIALIZED提示（v12+）：例如WITH active_regions AS MATERIALIZED (SELECT code FROM regions WHERE name LIKE '%华东%')，避免优化器误判为非物化 - MySQL 5.7+ 不支持 CTE 物化控制，此时优先用派生表显式命名：
SELECT * FROM (SELECT id, name FROM users WHERE status = 'active') AS u JOIN orders o ON o.user_id = u.id
哪些子查询写法会绕过内存限制直接落盘
调大 tmp_table_size 和 max_heap_table_size 没用——只要满足以下任一条件，MySQL 就会无视内存参数，直奔磁盘临时表：
- 子查询 SELECT 列含
TEXT、BLOB或超长VARCHAR（MEMORY 引擎不支持） -
ORDER BY或GROUP BY用了表达式，例如GROUP BY DATE(created_at)、ORDER BY CONCAT(first_name, ' ', last_name) - JOIN 条件字段类型不一致，比如
INT列和字符串常量比较：ON t1.id = '123' - 子查询本身带
LIMIT但外层又ORDER BY：MySQL 无法保证先截断再排序，常全量物化后排序
验证是否真解决问题：看三个关键指标
改完不能只看“快了”，得确认临时表空间压力确实下降：
- 运行
SHOW STATUS LIKE 'Created_tmp%';，重点关注Created_tmp_disk_tables是否明显减少；若它和Created_tmp_tables的比值仍 >15%，说明还有隐式落盘 - MySQL 执行
EXPLAIN FORMAT=JSON，检查query_block->materialized_from_subquery是否为false；若仍是true，说明视图或子查询仍未被合并 - PostgreSQL 运行
EXPLAIN (ANALYZE, BUFFERS)，观察Plan Rows是否接近你期望的 LIMIT 数（如LIMIT 10时 Plan Rows 应为个位数），而非百万级
真正难的不是写出扁平 SQL，而是识别哪一层嵌套在“悄悄吃掉临时空间”——往往最外层看着简单，问题藏在第三层子查询的 GROUP BY 字段没索引里。
