子查询提前过滤能大幅减少JOIN输入行数，因JOIN计算量与中间结果集大小正相关；将WHERE条件下推至子查询可显著降低驱动表或被驱动表的输入行数，从而断崖式削减嵌套循环、哈希连接等算法开销。

子查询提前过滤能大幅减少JOIN输入行数

因为数据库执行JOIN时，计算量和中间结果集大小直接正相关。如果先全表JOIN再WHERE过滤，很可能已经生成百万级临时行；而把WHERE条件下推到子查询里，驱动表或被驱动表的输入行数可能从10万降到几百——JOIN算法（如嵌套循环、哈希连接）的开销随之断崖式下降。

常见错误现象是：执行计划里rows_examined高达200万，但单表实际只有几万行。问题就出在没做前置剪枝，让无关数据全程参与匹配。

• 对INNER JOIN，子查询过滤后JOIN，等价于缩小了笛卡尔积基数
• 对LEFT JOIN，右表用子查询过滤，既保全左表所有行，又避免扫描全表
• MySQL 8.0+和PostgreSQL虽有自动下推能力，但遇到OR、LIKE '%xx'、DATE(created_at)等表达式时，优化器大概率放弃重排

LEFT JOIN中ON和WHERE放过滤条件，语义和性能都不同

ON决定“怎么连”，WHERE决定“连完留哪些”。尤其对LEFT JOIN，把右表字段的过滤条件写在外层WHERE，会意外把左表无匹配的行全部剔除——逻辑上退化为INNER JOIN，且无法利用右表索引加速。

比如统计每个用户2025年后的订单数，错误写法：LEFT JOIN orders o ON u.id = o.user_id WHERE o.created_at > '2025-01-01'；正确写法应是：LEFT JOIN (SELECT * FROM orders WHERE created_at > '2025-01-01') o ON u.id = o.user_id。

• 前者导致orders全表扫描，且o.user_id索引失效
• 后者让orders扫描行数下降90%以上，user_id索引可被真正用于JOIN匹配
• 若右表过滤后仅剩几百行，数据库更可能选择Nested Loop而非低效的Hash Join

关联子查询重复执行是性能黑洞

像WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id)这类关联子查询，外部表每扫一行，子查询就重跑一次。1000名员工同属一个部门，子查询就被执行1000遍——本质是O(N²)复杂度。

改用子查询转JOIN或窗口函数，能把重复计算压到1次：

• 派生表方式：INNER JOIN (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) d ON e1.dept_id = d.dept_id
• 窗口函数（MySQL 8.0+）：AVG(salary) OVER (PARTITION BY dept_id)，不改变行数，无额外扫描
• 注意：子查询在FROM中默认不物化，若需复用，应显式建临时表并加索引

聚合类查询必须先GROUP BY再JOIN

先JOIN再GROUP BY是典型反模式。三张大表关联后聚合，中间结果可能达千万行；而先按关键字段（如user_id）聚合出汇总表，再JOIN维度信息，JOIN对象只是几百行。

例如查用户姓名与订单总数，慢写法是SELECT u.name, COUNT(o.id) FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id；快写法是SELECT u.name, co.order_count FROM users u LEFT JOIN (SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id) co ON u.id = co.user_id。

• 前者让orders全量参与JOIN，内存和CPU压力陡增
• 后者JOIN对象仅为分组后的小结果集，还能利用user_id索引快速定位
• 若聚合后还需过滤（如只看订单数>5的用户），应把HAVING放在子查询内，而非外层WHERE

实际优化中最容易被忽略的是：过滤条件是否真的下推到了执行计划最底层。光看SQL写法不够，必须用EXPLAIN确认rows和filtered值——哪怕用了子查询，如果字段类型不匹配或隐式转换存在，索引仍可能失效，下推也就形同虚设。