嵌套group by性能差的本质是多次全表扫描、临时排序和重复聚合；应拆为两步：先物化中间聚合结果到临时表并建索引，再对外层查询优化。
为什么嵌套 GROUP BY 会慢到无法接受
本质不是语法问题，是执行路径失控：数据库被迫对同一张表反复扫描、排序、聚合。比如外层 GROUP BY region 依赖内层子查询结果，而该子查询本身又含 JOIN 和 GROUP BY user_id，执行计划里必然出现 Using temporary; Using filesort——这不是提示，是性能已崩的实锤。
常见错误现象包括：查询耗时从毫秒级跳到几十秒、EXPLAIN 显示临时表和文件排序、CPU 和磁盘 IO 持续拉满。根本原因在于优化器无法复用中间结果，每次都要重算。
用派生表（Derived Table）提前物化中间聚合
把内层聚合结果先“落地”成一个逻辑上的临时结果集，让外层只做简单聚合，彻底切断嵌套链路。
- 必须显式写出
SELECT ... FROM (SELECT ... GROUP BY ...) AS tmp，不能省略别名AS tmp，否则 MySQL 会报错 - 在派生表里就加上过滤条件，比如
WHERE status = 'active'，别拖到外层HAVING；否则数据库会先算完全部分组再筛，浪费 90% 计算 - 如果中间结果集较大（比如百万行以上），建议在外部建临时表并加索引：
CREATE TEMPORARY TABLE tmp_agg AS SELECT ...，再对tmp_agg建INDEX(region) - MySQL 5.7 不支持 CTE，但支持派生表；MySQL 8.0+ 可用
WITH，语义更清晰，但底层仍是物化逻辑
CTE 写法更可读，但要注意物化行为差异
WITH 看似只是语法糖，实际在不同引擎中行为不一：PostgreSQL 默认物化，MySQL 8.0 默认不物化（除非加 MATERIALIZED 提示），SQL Server 则取决于统计信息和查询复杂度。
- 写 CTE 时，务必在内部子查询里完成所有过滤、去重、字段裁剪，避免把大宽表直接扔进 CTE
- MySQL 中想强制物化 CTE，得加
/*+ MATERIALIZE */优化器提示，否则可能被内联展开，回到嵌套原点 - PostgreSQL 中，若 CTE 被多次引用，且未加
MATERIALIZED，它可能被重复执行——相当于写了几次相同子查询 - 别在 CTE 里写
ORDER BY或LIMIT，除非你明确需要截断；它们不会影响外层逻辑，还可能干扰优化器选择索引
GROUP BY 字段顺序与索引设计必须严格对齐
即使用了派生表或 CTE，外层 GROUP BY 如果字段顺序和索引不一致，依然会触发 Using temporary。
- 假设你要
GROUP BY region, dept，那索引必须是KEY idx_region_dept (region, dept)，反过来就不行 - 如果查询还带
ORDER BY region DESC, dept ASC，索引列顺序仍为(region, dept)，但需确认存储引擎是否支持混合方向排序（MySQL 8.0+ 支持，5.7 不支持） - 覆盖索引很关键：把
SELECT中用到的聚合字段（如SUM(sales)）、WHERE条件字段、GROUP BY字段全包进去，避免回表 - 注意
NULL值分布：如果region列大量为NULL，B+ 树索引效率会下降，考虑用COALESCE(region, '_unknown')统一处理后再分组
最易被忽略的一点：嵌套层级本身不是问题，问题是中间结果没有被真正“固定”。无论用派生表还是 CTE，只要没落地或没索引，优化器随时可能放弃你的意图，回归全表扫描。动手前先 EXPLAIN FORMAT=TREE（MySQL 8.0+）或 EXPLAIN (ANALYZE, BUFFERS)（PostgreSQL）看一眼真实执行路径，比猜强十倍。
