标量子查询在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从“主表行数 × 子表行数”降到“主表行数 + 子表行数”，才算真正生效。只看执行时间容易被缓存干扰误判。
