嵌套查询因nested-loop执行策略导致重复扫描和临时结果膨胀，易触发oom；in/exists可能引发10万次全表扫描，join改写需防笛卡尔积、null逻辑差异及left join重复行；验证须盯紧explain行数、临时表创建量和结果一致性。
嵌套查询本身不直接耗尽内存，但它的执行模型会触发重复扫描、临时结果膨胀和索引失效，最终让 tmp_table_size 或 work_mem 在几万行数据上就撑不住。
IN/EXISTS 子查询为什么实际执行 10 万次全表扫描
数据库优化器常把 SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 'active') 拆成“对 orders 每一行，都独立执行一次子查询”。这不是语法错误，而是多数引擎（MySQL 5.7、PG 12 之前）默认的 nested-loop 执行策略。
- 如果
orders有 10 万行，而users表没建status索引，就会真扫 10 万次users全表 - 每次扫描还可能生成中间结果集（哪怕只返回几个
id），MySQL 默认tmp_table_size是 64MB，累积几十次就爆 - PostgreSQL 的
work_mem若设为 4MB，10 万次 × 每次分配 1KB 结构体，也早超限 - 现象不是报错明确说 OOM，而是
ERROR 2013 (HY000): Lost connection to MySQL server during query或查询卡死数分钟
用 JOIN 替代时最容易踩的三个坑
很多人以为只要把 IN 改成 INNER JOIN 就安全了，其实只是把问题从“重复扫描”换成了“结果爆炸”。
- 别写
FROM orders o JOIN users u ON o.user_id = u.id WHERE u.status = 'active'—— 这等于先做全量 JOIN 再过滤，orders和users各 10 万行，中间笛卡尔积就是 100 亿行 - 必须前置子查询：写成
FROM orders o JOIN (SELECT id FROM users WHERE status = 'active') u ON o.user_id = u.id，先筛出几百个id，再 join，内存峰值能压到 50MB 以内 -
NULL行为不等价：IN遇到orders.user_id IS NULL会整行跳过；JOIN同样丢弃，但如果业务逻辑显式依赖三值逻辑（比如WHERE NOT (user_id IN (...)) OR user_id IS NULL），结果就不一致
EXISTS 改 LEFT JOIN + IS NOT NULL 的隐藏翻车点
把 WHERE EXISTS (SELECT 1 FROM logs l WHERE l.order_id = o.id) 直接套成 LEFT JOIN logs l ON l.order_id = o.id WHERE l.order_id IS NOT NULL，语义看似一样，但执行时完全不是一回事。
- 如果一个
order_id在logs表里有 5 条记录，LEFT JOIN后orders对应行就被复制 5 次 —— 原EXISTS只返回 1 次 - 后续加
GROUP BY或聚合，要么报错，要么结果行数少于预期，且内存占用翻倍 - 正确做法只有两个：
INNER JOIN（天然去重），或加DISTINCT（但需额外排序内存，代价高） - 别迷信
EXISTS的“短路”特性：现代优化器大多会重写，实际执行计划未必真短路；不如建好logs(order_id)索引，让 JOIN 走ref
验证改写是否真有效的三个硬指标
光改 SQL 不算完。上线前必须盯住这三点，否则容易在高峰期突然 OOM。
- 用
EXPLAIN FORMAT=TRADITIONAL看rows列：驱动表预估行数应从 “100000” 降到 “几千”，而不是 “100000 × N” - 查
SHOW STATUS LIKE 'Created_tmp_tables'：改写后临时表创建次数应明显下降（尤其对比Created_tmp_disk_tables） - 比对结果一致性：用小样本（比如
LIMIT 100）跑原 SQL 和改写 SQL，检查字段类型、NULL 行、总行数是否一致 —— 很多翻车发生在隐式类型转换或NULL处理差异上
真正难的不是写出能跑的 SQL，而是确认它在 10 万行、100 万行、1000 万行时，内存增长是不是线性的。很多“优化”只是把 OOM 从 10 万行拖到了 50 万行，上线后照样崩。
