含GROUP BY的子查询中,优化器因语义安全不推WHERE条件,且默认物化为派生表导致索引失效;应改用HAVING预过滤或CTE显式控制,避免嵌套过深。

子查询里写GROUP BY,优化器根本不敢下推WHERE条件
因为优化器无法保证语义安全——一旦把外层 WHERE 条件下推到已聚合的子查询内部,可能漏掉本该满足条件的原始行。比如子查询是 SELECT region, SUM(sales) FROM orders GROUP BY region,外层加了 WHERE sales > 10000,但 sales 在子查询结果里已不存在,更别说它原本是被聚合过的字段。此时 MySQL 直接放弃下推,EXPLAIN 中 Extra 字段会显示 Using where,而非 Using index condition。
MySQL 5.7/8.0 对含GROUP BY的子查询默认物化为派生表
只要子查询里有 GROUP BY、DISTINCT 或聚合函数,优化器大概率会把它当成一个临时结果集(<derivedn></derivedn>),而不是尝试内联展开。这意味着:
- 原始基表的索引结构彻底丢失
- 外层
WHERE只能在 Server 层过滤,无法触发 ICP - 联合索引中非最左列的条件(如
INDEX (a, b)上查WHERE b = 1)完全失效 - 即使你加了
/*+ NO_MERGE */或/*+ MATERIALIZE */,也改变不了物化事实
HAVING 比 WHERE 更适合在子查询内做预过滤
真正能减少中间数据量的操作,不是靠外层 WHERE,而是把过滤逻辑提前到聚合阶段:
- 错误写法:
SELECT * FROM (SELECT user_id, SUM(amount) FROM orders GROUP BY user_id) t WHERE SUM(amount) > 1000—— 语法报错,SUM(amount)不在子查询结果中 - 正确写法:
SELECT user_id, SUM(amount) FROM orders GROUP BY user_id HAVING SUM(amount) > 1000—— 聚合后立刻筛,避免生成无用分组 - 若还需关联其他表,优先让
HAVING先缩容,再JOIN,别等全量聚合完再过滤
替代方案:用 CTE + 显式过滤,比嵌套子查询更可控
MySQL 8.0+ 的 WITH CTE 在含 GROUP BY 时默认不物化(除非加提示),但依然不保证下推。更稳妥的做法是:
- 把聚合逻辑拆出来,用 CTE 命名,然后在外层
WHERE中重复关键过滤字段(哪怕冗余)——例如 CTE 中已有region,外层再写WHERE region IN ('East', 'West'),可触发基表索引 - 对高频聚合维度,直接建汇总表 + 索引,绕过视图和子查询层级
- 避免在子查询里套子查询,三层以上嵌套基本等于放弃优化器信任
复杂点在于:优化器是否物化、是否下推、是否合并,不只看 SQL 写法,还取决于统计信息是否准确、临时表大小阈值、甚至 optimizer_switch 中 derived_merge 是否开启——这些细节常被忽略,却直接决定执行计划走向。

















