sql子查询不直接耗尽内存，但其nested-loop执行模型会触发重复扫描、临时结果物化及哈希匹配，几万行即可能突破tmp_table_size（mysql默认64mb）或work_mem（pg默认4mb），导致oom killer杀进程或报error 2013。
SQL子查询本身不直接耗尽内存，但它的执行模型会触发重复扫描、临时结果物化、哈希匹配或嵌套循环——几万行数据就可能突破 tmp_table_size（MySQL 默认 64MB）或 work_mem（PostgreSQL 默认 4MB），最终被 OOM Killer 杀掉进程，或连接断开报 ERROR 2013 (HY000): Lost connection to MySQL server during query。
IN/EXISTS 子查询为什么实际执行 N × M 次扫描
数据库优化器（尤其 MySQL 5.7、PostgreSQL 12 前）默认采用 nested-loop 策略：对主表每行都独立执行一次子查询。这不是语法错，而是执行计划选择。
- 若主表有 10 万行，子查询未加索引（如
WHERE status = 'active'但users(status)缺失），就会真实全表扫 10 万次 - 每次扫描哪怕只返回几个
id，MySQL 也会为它建内存临时表；累积几十次就超tmp_table_size - PostgreSQL 中每个子查询调用都会分配结构体，
work_mem是 per-operation 的，10 万次 × 1KB ≈ 100MB，远超默认 4MB - 现象常表现为卡死数分钟、日志出现
Spill to disk或直接断连，而非明确报 “OOM”
JOIN 改写后反而更占内存的典型写法
把 IN (SELECT ...) 直接改成 JOIN 并不安全，容易从“重复扫描”滑向“中间结果爆炸”。
- 错误写法：
FROM orders o JOIN users u ON o.user_id = u.id WHERE u.status = 'active'→ 先笛卡尔积再过滤，orders 和 users 各 10 万行，中间结果可达 100 亿行 - 正确写法：
FROM orders o JOIN (SELECT id FROM users WHERE status = 'active') u ON o.user_id = u.id→ 子查询先走索引筛出几百行，再 join - 必须确保
users(status, id)有覆盖索引，否则子查询本身又变慢、还可能回表生成更大中间集 -
NULL行行为不等价：原IN自动跳过o.user_id IS NULL，JOIN同样丢弃；但若业务依赖NOT IN的三值逻辑，改写后结果可能不一致
临时表爆满的核心诱因是“无索引 + 排序 + 内存不足”三叠加
子查询返回多行（非标量）时，MySQL 会建内存临时表（由 tmp_table_size 和 max_heap_table_size 中较小者控制），但它默认无索引。一旦外层要 ORDER BY 或 GROUP BY，就得对这个无索引表再排序 —— 超 sort_buffer_size 就落盘生成 #sql_* 文件。
- 常见错误：
SELECT * FROM orders WHERE user_id IN (SELECT user_id FROM users WHERE status = 'active')→ 子查询返回全量 ID，外层聚合/排序无索引可依 - 隐蔽陷阱：
SELECT (SELECT COUNT(*) FROM logs l WHERE l.order_id = o.id) cnt FROM orders o→ 每行都触发一次子查询，每次都在内存建临时表，累积占用高 - 真正该调的参数是
tmp_table_size和max_heap_table_size（必须同步设，改一个等于白改），不是sort_buffer_size - 查当前值：
SHOW VARIABLES LIKE 'tmp_table_size'；会话级生效：SET SESSION tmp_table_size = 67108864
视图和派生表让问题更难察觉
MySQL 视图含 GROUP BY、DISTINCT、UNION 或子查询时，会强制物化为 DERIVED 表；PostgreSQL 中视图带 ORDER BY + 外层 LIMIT，排序常无法下推，导致千万级结果先全排再截断。
- 识别信号：
EXPLAIN中select_type = DERIVED（MySQL）或Materialize（PostgreSQL），且rows接近底层表总行数 - 视图里写
ORDER BY created_at DESC是危险的：它对外层LIMIT无效，只增加 spill 风险 - 删掉视图里的
SELECT *，只选必要字段；避开TEXT、BLOB、函数表达式（如UPPER(name)）参与排序/分组 - 建联合索引覆盖所有
JOIN、WHERE、ORDER BY字段，例如CREATE INDEX idx_status_time ON orders (status, created_at)
最易被忽略的是：物化行为是否真被触发，取决于优化器估算与实际数据分布的偏差。哪怕你写了看似合理的子查询，只要统计信息陈旧、索引缺失或字段顺序不匹配，数据库仍可能放弃优化、全量加载——所以每次改写后，必须用 EXPLAIN FORMAT=JSON 或 EXPLAIN (ANALYZE, BUFFERS) 看真实 Plan Rows 和物化节点，而不是只信 SQL 表面结构。
