如何通过GROUPING SETS或CUBE语法优化多维聚合统计，实现高效长尾词查询？

本文共计1066个文字，预计阅读时间需要5分钟。

MySQL 8.0.33版本中，不支持GROUPING SETS、CUBE或ROLLUP之外的多维分组语句。执行如下语句：

PostgreSQL、SQL Server、Oracle、StarRocks 等支持这些语法，但 MySQL 的 GROUP BY 仅支持基础分组 + WITH ROLLUP（且行为有限，不能自由组合维度）。

• WITH ROLLUP 只能按指定顺序生成层级汇总（如 GROUP BY a, b WITH ROLLUP → (a,b), (a,NULL), (NULL,NULL)），无法跳过中间层或交叉枚举
• 想实现 “按部门、按月份、按部门+月份、按月份+产品类目” 这类任意组合，MySQL 原生 GROUP BY 无解
• 别在存储过程中硬写 GROUP BY CUBE(...) —— 它不会运行，也不会给你提示性错误，而是直接语法拒绝

替代方案：UNION ALL 拼接多个 GROUP BY 查询

这是 MySQL 存储过程中最可控、最易调试的多维聚合写法。虽然 SQL 看起来长，但每块逻辑独立，索引可分别优化，执行计划清晰。

例如要统计「部门」、「月份」、「部门+月份」三个粒度：

SELECT 'dept' AS level, department AS dim1, NULL AS dim2, COUNT(*) AS cnt FROM sales GROUP BY department UNION ALL SELECT 'month' AS level, DATE_FORMAT(sale_time, '%Y-%m') AS dim1, NULL AS dim2, COUNT(*) AS cnt FROM sales GROUP BY DATE_FORMAT(sale_time, '%Y-%m') UNION ALL SELECT 'dept_month' AS level, department AS dim1, DATE_FORMAT(sale_time, '%Y-%m') AS dim2, COUNT(*) AS cnt FROM sales GROUP BY department, DATE_FORMAT(sale_time, '%Y-%m');

• 务必给每个子查询加 WHERE 过滤条件（如时间范围），避免重复扫描全表
• 所有子查询的字段数、类型、顺序必须严格一致，否则 UNION ALL 报错
• 如果某维度需要高精度（如小时级），别用 DATE_FORMAT，改用生成列 + 索引，否则无法走索引
• 结果集无序，外层需加 ORDER BY level, dim1, dim2 才能稳定输出

为什么不用存储过程里嵌套游标或循环？

因为性能和锁风险会急剧放大。比如你想遍历 5 个维度组合，用游标跑 5 次 INSERT INTO ... SELECT GROUP BY，本质是 5 次全表扫描 + 5 次临时表写入 + 5 次索引重建。

• 每次 GROUP BY 若没覆盖索引，就会触发 Using temporary; Using filesort，CPU 和磁盘 I/O 翻倍
• 若存储过程在事务中执行，5 次扫描可能长期持有间隙锁，阻塞其他写操作
• 游标逐行处理无法利用 MySQL 的向量化执行路径，纯属自降性能
• 除非中间结果少于 50 行且维度固定，否则不要用 DECLARE CURSOR 做聚合驱动

真正值得投入的优化点：预计算表 + 查询路由

当多维组合超过 3 种、单次查询响应要求 UNION ALL 也扛不住。这时该切到预计算路线——不是“把所有组合都算出来”，而是按业务真实高频路径建表。

比如报表常查：地区+月份、产品类目+季度、渠道+年份，那就只建三张预计算表：

CREATE TABLE sales_agg_region_month ( region_id INT, year_month CHAR(7), sale_amt DECIMAL(18,2), updated_at DATETIME, PRIMARY KEY (region_id, year_month) );

• 每张表加 updated_at 字段，应用层查前先判断时间范围是否落在已计算区间内
• 增量更新用 INSERT ... ON DUPLICATE KEY UPDATE，避免全量重刷
• 存储过程里不做实时聚合，只做“路由判断”：该查预计算表，还是退回到原始表
• 千万别给预计算表留空值字段（如 product_id NULL 表示“全部产品”），这会让索引失效、去重变慢

复杂点不在语法，而在区分哪些维度组合真被高频使用——埋点日志、慢查询日志、BI 工具的 query log，比任何文档都准。