mysql和postgresql优化器对on中子查询普遍不推过滤条件，易导致全量连接与隐式笛卡尔积；须确保子查询显式关联外层列并建立索引，优先改用left join+聚合或exists替代。
ON里写子查询，优化器大概率放弃下推过滤
MySQL 和 PostgreSQL 的优化器对 ON 中含子查询的语句普遍保守：它无法预估子查询结果集大小和选择性，更难将外层条件“下推”到子查询执行阶段，于是退而求其次——先全量连接左表每行与右表（或派生表）所有行，再逐行执行子查询判断。这等效于隐式笛卡尔积，尤其当左表行数多、子查询又没索引支撑时。
常见表现是 EXPLAIN 中某次 JOIN 的 rows 值远超右表真实行数，且 Extra 字段出现 Using where; Using join buffer（MySQL）或 Nested Loop 下 Actual Rows 异常高（PostgreSQL）。
- 子查询若含
GROUP BY或聚合但未关联外层字段，会被识别为UNCORRELATED SUBQUERY，优化器直接物化成静态结果集，再跟左表每行配对 -
ON t1.id = (SELECT t2.id FROM t2 WHERE t2.ref = t1.ref LIMIT 1)看似安全，但LIMIT不保证可下推，MySQL 5.7 及更早版本常放弃半连接路径 - PostgreSQL 对
LATERAL支持较好，但若子查询里用了RAND()或窗口函数，仍可能触发重复执行+膨胀
子查询未显式关联外层变量，等于没写ON
所谓“关联子查询”，核心是子查询 WHERE 或 ON 中必须引用外层表的列（如 t1.order_id），且该引用不能被别名污染或解析错误。一旦漏掉前缀、拼错字段名、或误用同名列导致解析到错误作用域，优化器就收不到关联信号，视其为无约束查询。
验证方法很简单：把子查询单独拿出来，把外层变量替换成一个固定值（比如 123）跑一遍；如果返回行数不变，说明那个变量根本没生效。
- 错误写法：
ON a.id = (SELECT id FROM b WHERE name = a.name)→ 若a.name在b表中重复率高，实际匹配多行，但优化器无法提前剪枝 - 更糟的是：
ON a.id = (SELECT id FROM b WHERE status = 'active')→ 完全没引用a，纯静态子查询，必然每行都查同一组数据 - 正确做法：确保子查询中至少有一个谓词明确绑定外层列，且该列在右表上有索引（如
b.order_id）
替代方案比硬塞子查询进ON更可控
绝大多数业务场景下，把子查询从 ON 拆出来、改用 LEFT JOIN + 聚合或 EXISTS，不仅逻辑清晰，还能让执行计划暴露真实膨胀点。
例如想补上每个订单的最新支付时间，不要写：ON o.id = (SELECT order_id FROM payments p2 WHERE p2.order_id = o.id ORDER BY created_at DESC LIMIT 1)；而应写成：
LEFT JOIN ( SELECT order_id, MAX(created_at) AS latest_payment FROM payments GROUP BY order_id ) p ON o.id = p.order_id
这样 ON 条件干净、可索引，EXPLAIN 里能直接看到 p 表的 rows 和 filtered 指标是否异常。
- 若子查询必须保留（比如含
LIMIT或非确定函数），优先用WHERE EXISTS替代IN或=子查询 - 避免在
ON中嵌套多层子查询，每多一层，优化器推导能力就降一级 - MySQL 8.0+ 可开启
optimizer_switch='subquery_to_derived=on'尝试自动转成派生表，但不如手动重构可靠
EXPLAIN 里的 rows 和 Extra，而不是只看 SQL 写得“看起来合理”。
