相关子查询改写为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_amountFROM 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或数据库自带性能视图验证收益
