在做数据统计的时候，分组查询配合子查询是非常常见的写法，比如先分组算出每个部门的平均工资，再找出高于本部门平均值的员工。这种写法逻辑清晰，但在数据量上来之后，往往一条SQL要跑十几秒甚至几十秒。问题通常不在分组本身，而在子查询与分组结合后，优化器难以利用索引，导致大量临时表和全表扫描。这篇文章就来拆解这类SQL的优化思路，重点讲如何把子查询改写为JOIN。
为什么分组查询中的子查询容易拖慢性能
先看一段典型的慢SQL。需求是：查询每个部门中工资高于该部门平均工资的员工。很多人第一反应会写成这样：
SELECT e.emp_name, e.salary, e.dept_id
FROM employee e
WHERE e.salary > (
SELECT AVG(salary)
FROM employee
WHERE dept_id = e.dept_id
);这是一个相关子查询：外层每扫描一行员工记录，内层都要重新计算一次该部门的平均工资。假设员工表有50万行，即使每个部门的平均值可以缓存，优化器也可能执行数万次内层聚合。用EXPLAIN查看执行计划时，通常会看到内层查询走的是全表扫描或者无法命中合适的索引，同时伴随"Using temporary"和"Using filesort"的提示。
更麻烦的是当子查询出现在SELECT列表里做分组统计时，比如按部门分组后取每个部门的订单数量，再嵌套一层子查询取部门名称，整个查询会生成多层派生表（DERIVED），派生表在MySQL 5.7之前不会物化索引，JOIN的时候只能全量匹配。理解了这些瓶颈来源，改写方向就明确了：减少重复计算次数，让优化器能走索引，把逐行触发的相关子查询变成一次性的集合运算。
改写方案一：相关子查询改写为派生表JOIN
对于上面那个"高于部门平均工资"的需求，标准改写思路是：先把分组聚合的结果作为一个派生表算出来，再和主表JOIN。这样分组只执行一次，JOIN过程可以走索引。
SELECT e.emp_name, e.salary, e.dept_id
FROM employee e
JOIN (
SELECT dept_id, AVG(salary) AS avg_salary
FROM employee
GROUP BY dept_id
) t ON e.dept_id = t.dept_id
WHERE e.salary > t.avg_salary;改写后的SQL中，内层派生表只对全表做一次分组聚合，产出的结果集行数等于部门数量，通常只有几十到几百行。外层通过dept_id上的索引与派生表关联，整体执行成本从"行数乘以聚合次数"降为"一次聚合加一次索引JOIN"。在实际项目中，50万行的员工表这种改写常见能把耗时从8秒左右压到0.3秒以内，效果非常明显。
需要注意派生表JOIN的适用条件：内层聚合字段上要有索引，本例中就是dept_id。如果dept_id没有索引，JOIN阶段仍然会退化为BNL（Block Nested Loop），性能提升有限。另外派生表里的GROUP BY最好只保留必要的字段，不要SELECT多余的列，减少物化的临时表大小。
改写方案二：IN子查询和EXISTS改写为LEFT JOIN
另一类常见场景是分组后用IN过滤，例如查询"有下单记录的用户的订单汇总"：
-- 原始写法：IN 子查询
SELECT u.user_id, u.user_name, COUNT(o.order_id) AS order_cnt
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.user_id IN (
SELECT user_id FROM orders GROUP BY user_id
)
GROUP BY u.user_id, u.user_name;这个查询里IN子查询和LEFT JOIN做的事情有重叠，可以合并简化。MySQL 8.0对IN子查询有半连接（semijoin）优化，会自动改写，但老版本或复杂条件下优化器常常放弃转换。手动改写为纯JOIN加DISTINCT或分组去重，可控性更高：
-- 改写：直接内连接，靠 GROUP BY 去重 SELECT u.user_id, u.user_name, COUNT(o.order_id) AS order_cnt FROM users u JOIN orders o ON u.user_id = o.user_id GROUP BY u.user_id, u.user_name;
再补充一个"反例"场景：查询没有下过单的用户，很多人写成NOT IN，这时要格外小心NULL值问题——如果子查询结果里包含NULL，NOT IN会导致整个查询返回空集。改写为LEFT JOIN加IS NULL判断既安全又高效：
SELECT u.user_id, u.user_name FROM users u LEFT JOIN orders o ON u.user_id = o.user_id WHERE o.user_id IS NULL;
这类改写的核心原则是：判断"存在性"用JOIN或EXISTS，判断"不存在"用LEFT JOIN加IS NULL，避免NOT IN的NULL陷阱。当外表小、内表大时EXISTS更快；当外表大、内表小时IN往往更优，改写前可以先估算两边的行数。
改写之外的配合优化技巧
改写JOIN只是第一步，还要配合几个手段才能把性能吃满。第一是索引设计：JOIN键和GROUP BY字段要建联合索引，且字段顺序要与查询条件匹配。比如上面的例子，orders表建(user_id, order_id)联合索引后，分组统计可以直接走覆盖索引，不需要回表。
第二是分组前置过滤。GROUP BY之前先用WHERE把不需要的行过滤掉，能显著减少参与聚合的数据量。比如统计近30天数据时，把时间条件写进WHERE而不是HAVING，因为WHERE在聚合前执行，HAVING在聚合后执行，两者处理的行数可能相差一个数量级：
-- 推荐：条件放 WHERE，聚合前过滤 SELECT dept_id, COUNT(*) AS cnt FROM orders WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) GROUP BY dept_id; -- 不推荐：无意义的 HAVING 过滤 SELECT dept_id, COUNT(*) AS cnt FROM orders GROUP BY dept_id HAVING create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);
第三是利用EXPLAIN验证改写效果。重点看三个指标：type是否从ALL变成了ref或range，Extra中"Using temporary"是否消失，扫描行数（rows）是否明显下降。如果改写后执行计划没有变化，多半是JOIN键上缺索引，或者派生表里的条件导致优化器无法下推。此外，MySQL 8.0开启derived_merge特性后，简单派生表会被合并进外层查询，进一步减少物化开销，条件允许的话尽量使用较新版本的数据库。
总结一下，分组查询中的子查询优化有个清晰的路径：先识别相关子查询和多层嵌套的派生表，再把"逐行计算"改成"一次聚合加JOIN"，判断存在性时优先用JOIN替代IN和NOT IN，最后用索引和前置过滤配合收尾。拿到慢SQL不要急着加索引，先用EXPLAIN看清执行计划，很多时候一次结构上的改写比堆索引有效得多。