MySQL 5.6+优化器对IN/EXISTS子查询自动重写为半连接、物化或派生表等策略，是否触发取决于子查询结构、索引与数据分布；若EXPLAIN显示DEPENDENT SUBQUERY，则表明退化为逐行执行，常见于含NOW()、LIMIT、类型不匹配或无索引等场景。
MySQL优化器对IN/EXISTS子查询的重写策略
MySQL 5.6+ 的优化器不会原样执行大多数 IN 或 EXISTS 子查询，而是根据代价估算自动选择重写路径。是否触发重写、走哪条路径，取决于子查询结构、数据分布和索引情况，不是开发者能直接控制的，但可以引导。
常见重写方向包括：
-
IN (SELECT ...)→ 半连接（SEMI-JOIN），语义等价于“只要匹配一行就保留外表行”，不膨胀结果集 -
NOT IN (SELECT ...)→ 反连接（ANTI-JOIN），用于“排除存在匹配的行” - 若子查询结果集小且稳定，优化器可能选择物化（
MATERIALIZED）：先执行子查询、建临时表（带哈希索引），再与外表连接 - 若子查询含聚合（如
MAX()）、GROUP BY或多表，可能提升为派生表（DERIVED），再参与外层连接
这些改写都发生在执行计划生成阶段，你无法从SQL文本看出，但可通过 EXPLAIN FORMAT=TREE（MySQL 8.0+）或 EXPLAIN EXTENDED + SHOW WARNINGS 观察重写后的逻辑树。
为什么有时看到DEPENDENT SUBQUERY却没被重写
当 EXPLAIN 显示 select_type = DEPENDENT SUBQUERY，说明优化器放弃了重写，退回到逐行执行模式——这不是bug，而是它判断“重写后代价更高”。典型诱因有：
- 子查询中引用了外表的非确定性函数，如
NOW()、RAND()，导致无法物化 - 子查询包含
LIMIT、ORDER BY且未配合GROUP BY，优化器不敢提前固化结果 - 外表字段类型与子查询返回列类型不一致（如
INTvsVARCHAR），隐式转换阻断索引下推和重写 - 子查询本身涉及无索引字段过滤，预估物化成本 > 嵌套循环成本，尤其在外表行数少、子查询表大时
此时强行加 /*+ MATERIALIZE */ 提示（MySQL 8.0.22+）可能生效，但需验证执行计划是否真用了物化，而非忽略提示。
JOIN改写后为何有时比原IN还慢
手动把 IN 改成 INNER JOIN 并不总能提速，甚至更慢。根本原因在于：重写只是手段，索引才是前提。
- 若子查询条件字段（如
customers.city）无索引，JOIN前的customers表扫描仍是全表，和原IN一样慢 - 若关联字段（如
orders.customer_id）无索引，JOIN会退化为 Block Nested-Loop，性能雪崩 -
JOIN可能产生重复行（一对多），而IN天然去重；漏加DISTINCT或GROUP BY不仅结果错，还会让排序/去重成本远超原查询 - 在
WHERE中写的子查询条件（如city = 'Shanghai'）必须下推到ON子句或确保能在JOIN后高效过滤，否则优化器可能延迟执行，失去下推优势
哪些子查询几乎没法被优化器自动重写
以下场景，MySQL 优化器基本放弃重写，只能靠人工干预或接受逐行执行：
- 标量子查询中含
LIMIT 1或ORDER BY ... LIMIT 1，例如SELECT (SELECT name FROM users WHERE dept_id = t.dept_id ORDER BY score DESC LIMIT 1) FROM teams t - 子查询依赖外表的窗口函数值或当前行计算结果，如
(SELECT COUNT(*) FROM logs l WHERE l.user_id = u.id AND l.ts > u.last_login) - 子查询返回多列或多行，但外层只取单值（如用在
=右侧），此时优化器无法安全重写，会直接报错Subquery returns more than 1 row - 子查询嵌套过深（≥3层），尤其含相关引用时，优化器递归分析开销过大，直接禁用物化
这类查询的真实瓶颈不在“是否重写”，而在“是否必须用子查询表达”。很多时候，拆成应用层两步查、或用 CTE + 窗口函数预处理，比硬扛单条SQL更可控。
