FULL OUTER JOIN 易致内存溢出,因需同时缓存两表未匹配行;MySQL 模拟时通过 LEFT+RIGHT JOIN+UNION ALL 加剧物化开销,PG 原生实现则依赖双哈希表大幅消耗 work_mem。

FULL OUTER JOIN 本身不直接导致内存溢出,但它的执行方式强制数据库同时持有左右两表的全部未匹配行,极易触发哈希表膨胀、物化临时表或磁盘溢出——尤其在 MySQL 这种不原生支持该语法的引擎中,模拟实现时更危险。
MySQL 模拟 FULL OUTER JOIN 会强制物化两张大表
MySQL 不支持原生 FULL OUTER JOIN,常见写法是:LEFT JOIN + RIGHT JOIN + UNION ALL。问题在于:
- 两个
JOIN子查询各自独立执行,无法共享中间状态,每边都可能全量加载右表(如RIGHT JOIN中的左表)进内存建哈希表 -
UNION ALL阶段需合并两个结果集,若两边各返回 50 万行,内存中就要暂存百万行宽记录(含 NULL 填充列) - 如果任一
JOIN的 ON 字段无索引,type=ALL触发join_buffer_size全局占用,多并发下直接挤爆
PostgreSQL 原生 FULL OUTER JOIN 默认走 Hash Join,work_mem 吃得狠
PG 执行 FULL OUTER JOIN 时,必须为左右两表分别构建哈希表(因要保留双方 unmatched 行),这意味着:
- 单次查询至少申请
2 × work_mem;若还带ORDER BY或GROUP BY,再加一份,实际消耗常达3–4 × work_mem - 哪怕只查 10 行,只要执行计划选了 Hash Join,它仍会尝试把整个小表(比如 users)全载入内存——没索引时,几百万行就吃掉 512MB+
-
EXPLAIN (ANALYZE, BUFFERS)中看到Hash节点下Rows Removed by Filter: 0且Buffers: shared hit=xxx read=yyy很高,说明哈希表已撑满
别用视图封装 FULL OUTER JOIN,否则物化不可控
把 FULL OUTER JOIN 写进视图,在 MySQL 或 PG 中都极可能被强制物化(DERIVED 或 MATERIALIZED):
- MySQL 视图含
UNION或非相关子查询 → 立刻放弃 MERGE,先生成完整临时表再处理外层LIMIT - PG 视图里嵌套
FULL OUTER JOIN+ORDER BY→ 外层LIMIT几乎从不下推,全量排序后再截断 - 字段含
TEXT或多个VARCHAR(2000)→ 物化行宽翻倍,内存/磁盘临时文件暴涨更快
替代方案:用 UNION ALL 显式分步控制内存
真需要全外语义,与其依赖引擎自动处理,不如手动拆解、逐段约束数据规模:
- 先查左表独有行:
SELECT 'left' AS src, a.* FROM table_a a LEFT JOIN table_b b ON a.id = b.a_id WHERE b.a_id IS NULL,加LIMIT和索引 - 再查右表独有行:
SELECT 'right' AS src, b.* FROM table_b b LEFT JOIN table_a a ON b.a_id = a.id WHERE a.id IS NULL - 最后查交集:
SELECT 'both', a.*, b.* FROM table_a a INNER JOIN table_b b ON a.id = b.a_id - 三者用
UNION ALL拼接,每部分都可独立加WHERE过滤、ORDER BY id LIMIT 1000,避免一次性加载
真正危险的不是 FULL OUTER JOIN 这个概念,而是你没意识到:它要求数据库“记住所有没匹配上的行”,而这个“记住”的代价,在缺乏索引和分页控制时,就是内存里堆满 NULL 填充的宽记录。

















