相关子查询改写为 join 可大幅提升 sql 性能，因其将每行重复执行的子查询转为一次聚合 + 等值连接，避免大量随机 i /o；多条件场景宜用预聚合 + 窗口函数，并需验证 null 处理、group by 完整性及执行计划优化效果。
相关子查询改写为 JOIN 是提升 SQL 性能最有效的方法之一，核心在于消除对主表每行都重复执行子查询的开销。
为什么相关子查询慢？
相关子查询会为外层查询的每一行，重新执行一次内层查询。例如：
SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_cnt FROM users;
如果 users 表有 10 万行，orders 表有 50 万行，该语句可能触发 10 万次独立的 COUNT 扫描，即使有索引也难以避免大量随机 I / O 和重复计算。
用 LEFT JOIN + GROUP BY 替代
将聚合逻辑前置，一次性关联并分组统计，大幅减少扫描次数：
SELECT u.name, COALESCE(o.order_cnt, 0) AS order_cnt
FROM users u
LEFT JOIN (
SELECT user_id, COUNT(*) AS order_cnt
FROM orders
GROUP BY user_id
) o ON u.id = o.user_id;
- 子查询只执行一次，生成中间结果集（user_id → 订单数）
- JOIN 基于等值条件，可利用 user_id 索引快速匹配
- COALESCE 处理无订单用户，语义与原查询一致
多条件相关子查询：用 JOIN + 窗口函数或预聚合
当子查询含多个关联条件（如时间范围、状态过滤），直接 JOIN 可能产生笛卡尔积。此时优先预聚合：
— 原查询（查每个用户最近一笔已完成订单金额）
SELECT u.name,
(SELECT amount FROM orders o
WHERE o.user_id = u.id AND o.status = ‘done’
ORDER BY o.created_at DESC LIMIT 1)
AS last_done_amount
FROM users u;
优化方案：
SELECT u.name, o.last_done_amount
FROM users u
LEFT JOIN (
SELECT user_id,
FIRST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY created_at DESC) AS last_done_amount
FROM orders
WHERE status = ‘done’
) o ON u.id = o.user_id;
- WHERE 先过滤出有效订单，缩小窗口函数输入集
- PARTITION BY user_id 保证每个用户独立排序取值
- 避免在 JOIN 后对全量 orders 做多次 ORDER BY + LIMIT
注意事项与验证方法
改写后务必验证结果正确性和执行计划变化：
- 检查 NULL 处理：LEFT JOIN 需用 COALESCE/IFNULL 补零，避免丢失空关联行
- 确认 GROUP BY 字段完整：若 JOIN 键不唯一，需在子查询中明确去重或聚合逻辑
- 查看 EXPLAIN 输出：确保 JOIN 使用了预期索引，rows 扫描数显著下降
- 对比实际运行时间与逻辑读：用 SET profiling= 1 或数据库自带性能视图验证收益
