子查询优化需区分相关与非相关类型：非相关只执行一次，相关则每行重算；优先转为非相关、善用IN/EXISTS/JOIN、以CTE或派生表解耦嵌套、避免select*并验证执行计划。
子查询优化的关键在于理解其执行逻辑，并根据场景选择改写方式。不是所有子查询都需要改写，但不当写法会显著拖慢查询性能，尤其在大数据量或嵌套较深时。
相关子查询 vs 非相关子查询：执行差异明显
非相关子查询（inner query不依赖outer query）只执行一次，结果可缓存复用；相关子查询（如 WHERE salary > (SELECT AVG(salary) FROM emp e2 WHERE e2.dept = e1.dept)）则对主表每行都重新执行，开销呈线性增长。
- 检查是否相关：看子查询中是否引用了外层表的列
- 能转为非相关的尽量转——例如把部门平均工资提前算好，用 JOIN 或 CTE 预先聚合
- 相关子查询若无法避免，考虑加索引覆盖关联字段和筛选字段
IN / EXISTS / JOIN：语义等价但执行计划常不同
IN 和 EXISTS 在多数情况下逻辑一致，但优化器处理方式不同：IN 会先执行子查询生成结果集再做哈希匹配；EXISTS 只判断是否存在，找到即停，适合子查询结果集大而主表小的场景；JOIN 更利于利用索引和并行，且便于后续扩展条件。
- 子查询返回少量值、主表大 → 优先用 IN（配合索引）
- 子查询可能返回大量值、主表小 → EXISTS 更高效
- 需取子查询中的其他字段，或要避免重复行 → 直接 JOIN 更清晰可控
用 CTE 或派生表替代多层嵌套子查询
深层嵌套（如 SELECT * FROM (SELECT … FROM (SELECT …) t1) t2）会让优化器难以生成最优计划，也降低可读性和维护性。CTE（WITH 子句）或内联视图（派生表）可将逻辑分层，帮助优化器更好估算行数和选择连接顺序。
- 把重复使用的子查询提成 CTE，避免多次计算
- 对复杂过滤或聚合逻辑，先在派生表中完成，再与主表关联
- 注意：某些数据库（如 mysql 5.7 及更早）不物化 CTE，仍可能重复执行，需结合 EXPLaiN 验证
避免 SELECT * 在子查询中
子查询里写 SELECT * 不仅带来多余列传输开销，还可能干扰优化器选择索引——尤其当子查询用于 EXISTS 或 IN 时，只需判断存在性或某字段值，却加载了整行数据。
- IN 子查询只保留目标字段，如 WHERE id IN (SELECT user_id FROM logs WHERE …)
- EXISTS 子查询统一用 SELECT 1，明确语义且轻量
- 关联子查询中，只 SELECT 所需列，避免隐式转换或函数包裹导致索引失效
不复杂但容易忽略。改写前先看执行计划，确认瓶颈真在子查询本身，而不是缺失索引或统计信息过期。