标量子查询在where或having中依赖外层字段时会强制嵌套循环执行，导致10万行主表触发10万次子查询；优化应先聚合子表再join，避免重复计算与null/多行陷阱。

标量子查询在WHERE里会强制嵌套循环

只要出现在WHERE或HAVING中且依赖外层字段，比如WHERE status = (SELECT default_status FROM config WHERE app = 'order')，数据库就无法物化该子查询——它会在外层每一行都重新执行一次。10万行主表，就是10万次独立执行，哪怕子查询只查一张小表，总耗时也是线性放大。

常见错误现象：EXPLAIN里type列显示DEPENDENT SUBQUERY，rows列数值稳定在子表总行数（比如始终是12），但实际执行时间随主表行数陡增；EXPLAIN ANALYZE显示子查询部分占总耗时90%以上。

• 即使子表只有几十行、也有索引，也挡不住调用次数爆炸
• 子查询里带ORDER BY + LIMIT 1？优化器更难下推，往往退化为全扫描+排序
• MySQL 5.7 及更早版本基本不尝试物化，8.0+ 需满足严格条件（如无外部引用、可静态推导）才可能启用MATERIALIZE

替代方案不是简单换JOIN，而是先聚合再关联

标量子查询本质是“每行触发一次聚合”，正确解法是把聚合提前做，变成结果确定的中间集，再用JOIN或LEFT JOIN回填。例如把WHERE amount > (SELECT AVG(amount) FROM orders WHERE user_id = u.id)改成先算好每个用户的平均值。

关键操作顺序不能错：

• 先用GROUP BY对子表聚合（如SELECT user_id, AVG(amount) AS avg_amt FROM orders GROUP BY user_id）
• 确保聚合结果集远小于原表（否则JOIN开销反超）
• 用LEFT JOIN关联，避免丢失主表无匹配记录的行
• 显式用COALESCE(avg_amt, 0)处理NULL，保持语义一致

容易被忽略的NULL与多行报错陷阱

标量子查询天然带两类安全机制：返回空时给NULL，返回多行直接报错more than one row returned。而一旦改写为JOIN，这两条防线就消失了。

• 子表连接字段允许NULL？LEFT JOIN后ON条件失效，必须加AND sub.id IS NOT NULL显式过滤
• 聚合子查询没加GROUP BY却有重复user_id？JOIN会静默产生重复行，COUNT/SUM全部翻倍
• 原查询用AVG()，空组返回NULL；但COUNT(*)空组返回0——混用时统计口径极易跑偏

真正难的不是改写，是识别哪一层在重复计算

很多SQL看着只有一层子查询，但括号嵌套深处可能藏着隐式相关引用。比如(SELECT COUNT(*) FROM logs l WHERE l.user_id IN (SELECT id FROM users u2 WHERE u2.status = u1.status))，表面是两层，实际u1.status让整个链路变成相关嵌套。

改完之后，务必对比EXPLAIN的rows和Extra字段：原来DEPENDENT SUBQUERY消失、rows从“主表行数 × 子表行数”降到“主表行数 + 子表行数”，才算真正生效。只看执行时间容易被缓存干扰误判。