子查询在where中比join更耗内存，因其常触发逐行物化临时表且无法复用中间结果；而join（如index nested-loop）可流式处理，内存峰值可控。
子查询在WHERE中比JOIN更消耗内存，核心原因是它常触发物化临时表 + 无法复用中间结果，而JOIN（尤其Index Nested-Loop）能流式处理、避免全量缓存。
DEPENDENT SUBQUERY 强制逐行物化，内存随主表行数线性暴涨
当EXPLAIN显示select_type为DEPENDENT SUBQUERY时，数据库对主表每一行都重跑一次子查询——每次执行都可能新建临时结果集（哪怕只返回1个值）。例如：
SELECT u.name FROM users u WHERE u.id IN (SELECT o.user_id FROM orders o WHERE o.status = 'shipped')
若users有10万行，且orders没走索引，MySQL可能为每行生成一个独立的临时哈希表或内存排序缓冲区。这不是“一次物化、反复查”，而是10万次物化。
- 内存分配不可复用：每个子查询实例独占内存空间，GC压力大
- 临时表无索引：物化结果默认不建索引，后续匹配靠全扫描或哈希查找，CPU和内存双吃紧
- 优化器无法预估大小：
rows列在EXPLAIN里常严重低估，实际内存占用远超tmp_table_size阈值，直接落盘→I/O雪崩
IN/EXISTS子查询的物化策略受配置与数据分布强约束
MySQL 8.0虽支持semi-join，但物化是否发生、用哪种策略（DUPS_WEEDOUT还是FIRSTMATCH），取决于optimizer_switch开关和真实数据特征：
-
subquery_to_derived=off或semijoin=off→ 强制退化为DEPENDENT SUBQUERY - 子查询含
GROUP BY、LIMIT、ORDER BY→ 物化失效，改走逐行执行 - 物化结果集太小（如仅2行）→ 构建临时表开销反而高于NLJ，但内存仍被占用
- 物化结果集太大（如上万行）→ 触发
Using temporary; Using filesort，内存不够就写磁盘临时文件
你看到EXPLAIN FORMAT=JSON里"materialization": true，不代表省内存——它只说明“结果被缓存了”，没说缓存多大、是否索引、是否落盘。
JOIN的内存行为更可控，但前提是驱动表选对、索引到位
Index Nested-Loop Join（NLJ）是内存友好的典型：驱动表每行取值后，直接用被驱动表索引定位匹配行，无需缓存整个中间结果集。
- 内存峰值≈驱动表单行大小 × 并发数，与被驱动表总行数无关
- 若驱动表是
orders（100万行）、被驱动表是users（1万行），只要orders.user_id有索引，内存压力远小于反向JOIN - Hash Join（MySQL 8.0+）需把小表全载入内存建哈希表，此时内存消耗取决于小表大小——但至少是“一次加载、全局复用”
- 没索引？NLJ退化为Block Nested-Loop，会启用
join_buffer_size缓存块，但仍是批量读、非逐行物化
真正卡住你的不是“JOIN or not JOIN”，而是EXPLAIN里type是不是ref/eq_ref、key有没有命中、rows是否接近实际扫描量。
FROM子句子查询不等于物化，CTE更是幻觉
很多人以为WITH tmp AS (SELECT ...)或FROM (SELECT ...) t能自动缓存结果，实际在MySQL和SQL Server里，它们只是语法糖，执行计划中仍会展开多次——每次引用都重跑一遍，内存照样炸。
- PostgreSQL 12+需显式写
WITH tmp AS MATERIALIZED (SELECT ...)才强制物化 - SQL Server必须用
#temp_table，且要立刻建索引：CREATE INDEX IX_user_id ON #tmp(user_id) - MySQL只能用
CREATE TEMPORARY TABLE tmp AS SELECT ...，再手动ALTER TABLE tmp ADD INDEX(...)
不落地、不索引，所谓“子查询优化”就是把内存压力从运行时搬到了解析时——看起来快了，实则换了个地方爆。
