dependent subquery 性能极差，因每行主表都重执行子查询；join 通过索引嵌套循环更高效，但改写需谨慎处理 null、重复行及语义一致性。

DEPENDENT SUBQUERY 会触发 N×M 次执行

看到 EXPLAIN 输出里有 DEPENDENT SUBQUERY，基本就确认性能瓶颈在这儿。它意味着子查询依赖外层表字段（比如 orders.user_id = users.id），优化器无法物化结果，只能对主表每一行都重跑一次子查询。10 万行订单？就是 10 万次解析、权限检查、临时表创建与销毁——哪怕子查询本身只查主键，开销也早已指数级放大。

索引难以下推，JOIN 却能走 Index Nested-Loop

子查询中 WHERE 条件常被“锁”在子查询内部，优化器很难把外层过滤条件（如 users.status = 'active'）下推进去，导致子查询全扫；而 INNER JOIN 的连接条件天然支持索引驱动：只要 orders.user_id 和 users.id 都有索引，MySQL 就能用 Index Nested-Loop，每取一行订单，直接用索引定位匹配用户，I/O 和 CPU 都可控。

• 子查询写法：WHERE o.user_id IN (SELECT id FROM users WHERE status = 'active') → 可能先全扫 users 构建临时结果集
• 等价 JOIN：INNER JOIN users u ON o.user_id = u.id WHERE u.status = 'active' → u.status 可走 (status, id) 覆盖索引，避免回表

MySQL 5.7 前几乎不优化，8.0 的 semi-join 仍有前提

MySQL 8.0 确实会尝试把 IN/EXISTS 转成 semi-join 或物化，但不是总生效：

• 若子查询含 GROUP BY、ORDER BY RAND()、UNION 或 STRAIGHT_JOIN，优化器直接跳过转换
• subquery_to_derived=off 或 optimizer_switch 中禁用了 semijoin，也会失效
• 物化后结果集太小（比如只 2 行），反而多一次临时表构建开销，不如原生 NLJ

你看到 EXPLAIN FORMAT=JSON 里没出现 semi_join 或 hash_join 节点，说明它根本没按你期望的路径走。

NULL 和重复行处理让改写容易翻车

语法改了不等于语义一致，最容易忽略的是这三点：

• WHERE col = (SELECT ...) 遇到子查询返回 NULL，结果是 UNKNOWN，该行仍保留；INNER JOIN 会直接过滤掉 col IS NULL 的行，需显式补 AND col IS NOT NULL
• 一对多关系（如一个用户有多条日志）下，INNER JOIN 会让主表行数膨胀，而原 IN 子查询不会；必要时得加 DISTINCT 或换 EXISTS
• 子查询若因数据异常返回多行（如标量子查询），会立刻报错；JOIN 则静默产生笛卡尔积，结果污染却难以察觉

真正卡住你的，往往不是“会不会慢”，而是“改完结果对不对”——尤其在 NULL 处理和聚合语义上，毫厘之差就满盘皆错。