SQL 子查询与派生表性能优化

子查询在 where 中比 join 慢，因 mysql 5.7 及更早版本对相关子查询优化弱，每行重复执行且无法利用外层索引；派生表默认物化、无索引；in 受 null 影响且结果大时性能骤降；exists 更稳但嵌套不宜过深；cte 在多引用或递归时才有优势。

子查询在 WHERE 中为什么比 JOIN 慢？MySQL 5.7 及更早版本对 WHERE 子句里的相关子查询（correlated subquery）优化能力很弱，每次外层行都要重新执行一次子查询，没有缓存、无法下推条件。比如 SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE region = 'CN')，如果子查询不加索引或返回大量 ID，实际会退化成 N×M 次扫描。

• 相关子查询默认无法利用外层表的索引做关联，优化器常选错执行计划
• 非相关子查询（如 (SELECT MAX(created_at) FROM logs)）会被提前物化，影响小
• EXPLAIN 显示 type 为 ALL 或 DEPENDENT SUBQUERY 就是危险信号
• 替换思路：把子查询改写成 JOIN 或 EXISTS，让优化器有机会用上索引

SELECT o.* FROM orders o INNER JOIN customers c ON o.customer_id = c.id AND c.region = 'CN';

派生表（Derived Table）被强制物化就完蛋 MySQL 5.7 默认把 FROM 中的子查询当作派生表处理，并强制物化（materialize）——也就是先执行完、建临时表、再查。哪怕你只查一列、加了索引，它也不走索引，全表扫描后再过滤。

• 物化后临时表无索引，ORDER BY / LIMIT 失效，排序和分页成本陡增
• MySQL 8.0+ 引入了“派生合并”（derived_merge）优化，默认开启，能将简单派生表自动合并进外层查询
• 但只要派生表里有 GROUP BY、DISTINCT、聚合函数或 UNION，优化器就会放弃合并，退回物化
• 查看执行计划时注意 Extra 列是否出现 Using temporary

关闭派生合并（仅调试用）：SET optimizer_switch='derived_merge=off';

EXISTS 比 IN 更稳，但别乱套嵌套 IN 对空值敏感，子查询返回 NULL 会导致整行丢失；EXISTS 是布尔语义，不受 NULL 影响，且通常能走半连接（semi-join）优化。

• IN 在子查询结果少时快，但结果集超过几百行后，MySQL 倾向转成物化表，性能断崖下跌
• EXISTS 总是“找到一个就停”，适合“是否存在”的场景，但写法稍啰嗦
• 嵌套多层 EXISTS 容易让优化器放弃优化，建议最多两层，深层逻辑尽量提前提取到中间表

SELECT * FROM products p WHERE EXISTS (SELECT 1 FROM order_items oi    WHERE oi.product_id = p.id AND oi.qty > 10);

什么时候该用 CTE 而不是子查询？MySQL 8.0+ 的 CTE 不是语法糖，它是真正可复用、可递归的命名结果集。但要注意：非递归 CTE 默认仍可能被物化，除非启用 cte_materialization=OFF（8.0.22+）。

• 单次引用的 CTE 和子查询性能几乎一样，别以为用了 CTE 就自动优化
• 多次引用同一逻辑（比如在 SELECT、WHERE、ORDER BY 都用到同一个统计值），CTE 才体现价值
• 递归 CTE（WITH RECURSIVE）必须有终止条件，否则报错 Recursive query aborted after 1001 iterations
• CTE 的列名必须显式声明，否则可能因类型推导失败导致隐式转换，拖慢 JOIN

强制不物化 CTE：SET cte_max_recursion_depth = 1000; + SET optimizer_switch='cte_materialization=off';

复杂点在于：优化效果高度依赖 MySQL 版本、数据分布、索引覆盖度和优化器开关状态。同一句 SQL，在 5.7 和 8.0.33 上执行计划可能完全不同，别只看文档结论，EXPLAIN FORMAT=TREE 才是真实答案。