相关子查询因引用外层字段无法物化，导致外层每行触发一次内表扫描；改写为join需满足语义等价、不膨胀、null逻辑正确；cte/派生表可预聚合避免重复扫描；窗口函数适用于单行聚合但受基数与版本限制。
为什么JOIN里的子查询会反复扫描同一张表
只要子查询里引用了外层表字段（比如 WHERE o.user_id = u.id），它就是相关子查询，MySQL 优化器无法物化或缓存结果——外层每扫一行，子查询就真执行一次。10万用户行 + 1次子查询 = 10万次全表或索引扫描，I/O直接打满。
常见错误现象：EXPLAIN 输出中 select_type 列出现 DEPENDENT SUBQUERY，且该行的 rows 值和外层表行数一致；Extra 里反复出现 Using where; Using temporary; Using filesort。
- 加索引没用：索引只能加速单次查找，不能阻止“执行10万次”
- 子查询写成
SELECT user_id FROM orders WHERE status = 'paid'，但user_id没索引 → 内表仍全扫 - 复合索引顺序错：
INDEX(status, user_id)有效，INDEX(user_id, status)在WHERE status = 'paid'时用不上
把相关子查询改写成JOIN的适用条件
不是所有子查询都能安全转 JOIN，必须满足语义可等价、结果不膨胀、NULL逻辑正确这三点。
- 子查询出现在
WHERE中，且只返回单列（如user_id）、无GROUP BY、无聚合函数 - 语义是存在性判断（
IN/EXISTS）或值匹配（= (SELECT ...)） - 主表与子表是 1:N 关系，且你只关心主表逻辑 → 此时
EXISTS比JOIN更安全（避免重复行） - 改写后必须加
DISTINCT或控制JOIN条件，否则一对多关系会导致主表行被放大
示例：SELECT * FROM users u WHERE u.id IN (SELECT user_id FROM orders WHERE status = 'paid') → 改为：SELECT DISTINCT u.* FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE o.status = 'paid'
用CTE或派生表预聚合替代多次子查询
当一个子查询被多个地方引用（比如同时要 COUNT、AVG、MAX），直接复制粘贴只会让扫描次数翻倍。必须把它提到外层，只算一次。
- 先用
WITH把明细表按关联键压缩：例如WITH user_stats AS (SELECT user_id, COUNT(*) AS cnt, AVG(amount) AS avg_amt FROM orders GROUP BY user_id) - 再用
LEFT JOIN关联主表，避免重复扫描 - 如果聚合条件复杂（如只统计
status = 'paid'的订单），务必在 CTE 内部过滤，而不是留到 JOIN 后再WHERE - MySQL 5.7 不支持
WITH？用派生表替代：(SELECT user_id, COUNT(*) AS cnt FROM orders WHERE status = 'paid' GROUP BY user_id) stats
窗口函数替代标量子查询的边界情况
COUNT() OVER(PARTITION BY ...) 是避免重复扫描最干净的方式，但它不是万能的——内存和基数限制必须提前评估。
- 适合场景：你要的是每个主表行对应的聚合值（如每个用户的订单数），且不关心明细行
- 必须搭配
LEFT JOIN使用，否则零记录用户会被丢掉 - PARTITION BY 字段基数太高（比如毫秒级时间戳、UUID）→ 窗口函数缓冲区溢出，可能落盘，比标量子查询还慢
- MySQL 5.7 及更早版本不支持
OVER，强行用会报错，别硬套 - 别在
WHERE里嵌套窗口函数：语法不允许，必须放在 CTE 或 SELECT 列表中
真正容易被忽略的点是：窗口函数只解决“扫描一次”，但不解决“JOIN 膨胀”。如果后续还要关联其他明细表，得继续用预聚合切断链路——没有银弹，只有分层拆解。
