VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
GROUP BY,在真实的表上可能要花几分钟,而查询文本完全相同。区别在于服务器是可以按分组顺序遍历索引,还是必须先构建一个临时表。本指南讲的就是这个区别,以及决定您得到哪一种结果的那些选择。
如果您还在学习这些子句本身,教程会用示例数据库讲解它们:聚合函数、GROUP BY、HAVING、COUNT DISTINCT、GROUP_CONCAT 和 WITH ROLLUP。
本页上的每个查询和执行计划都来自 1 台针对 Sakila 示例数据库的服务器。开销和行数估计来自 InnoDB 的采样统计信息和您的 optimizer_switch 设置,因此您的数字会有所不同。可以沿用的是每个计划的形状。
没有索引的分组会构建临时表
要对行分组,服务器必须把每个分组的行聚到一起。当分组列上没有索引时,它通过构建一个临时表来做到这一点:Using temporary 就是要找的那一行。在 1,000 行上它不花任何开销。这个表在放得下时保存在内存中,放不下时就溢出到磁盘,而这正是分组报表不再快速的地方。
哪个设置限制这部分内存,取决于服务器用于内部临时表的引擎。在更改任何东西之前,先检查您的设置:
TempTable 下全部生效。temptable_max_ram 和 temptable_max_mmap 限制该引擎在所有会话中可持有的总量。tmp_table_size 限制每个单独的内部临时表:设为 1 KB 时,一个针对 1,000 行的分组查询使 Created_tmp_disk_tables 从 0 变为 1;设为 64 MB 时,同一个查询留在内存中。请观察这个计数器,而不是猜测:
Using temporary,也没有 Using filesort。服务器按顺序读取索引,对每一段相等的值计数,然后输出一行。Using index 表示它根本没有触碰表本身。
因此,对于一个慢的 GROUP BY,首先要检查的是:是否有一个索引以分组列开头,并且顺序与您分组的顺序相同。(a, b) 上的复合索引可以服务 GROUP BY a 和 GROUP BY a, b。对于 GROUP BY b,服务器仍然可以把它当作覆盖索引来扫描,报告为 type: index 和 Using index,但行不是按分组顺序到达的,所以 Using temporary 又随之出现。
WHERE 和 HAVING 不是风格选择
两者都可以表达过滤条件。它们的开销不同,因为WHERE 在分组之前运行,而 HAVING 在分组之后运行。
针对分组列本身的条件可以用两种方式写,并且含义相同,这使它成为一个公平的比较。放在 WHERE 中时,服务器先缩小索引范围,然后只对读到的内容分组:
HAVING 中时,同样的限制来得太晚,无法缩小任何范围:
WHERE 版本读取估计 2,710 个索引条目;HAVING 版本读取整个索引,然后把大多数分组丢掉。
这些估计来自 InnoDB 的采样统计信息和 mysql.server_cost 中的开销常量,因此您的数字会与这些不同。可以沿用的是这 2 个计划的形状。
规则是:只要条件检验的是行,就放在 WHERE 中。只对检验分组的条件使用 HAVING,在实践中这意味着任何包含聚合的条件。一个不含聚合的 HAVING 子句几乎总是一个放错了地方的 WHERE 子句,而含有聚合的 WHERE 会得到 ERROR 1111 (HY000): Invalid use of group function。
ONLY_FULL_GROUP_BY 与逃生口
only_full_group_by 默认开启,它会以 ERROR 1055 拒绝选择了既未分组也未聚合的列的查询。原因是这样的查询没有确定的答案:分组中有很多行,而没有任何东西指明要显示哪一行。
ANY_VALUE() 告诉服务器您接受任意一行,查询随即可以运行:
ANY_VALUE(),这是正当的用例。不要用它来压制一个在分组内确实会变化的列上的错误:您得到的值不是第一个、不是最小的,也不是任何您可以依赖的东西,并且它会随着计划的改变而改变。MIN() 或 MAX() 说明了您的意图,而且开销相同。
在全局范围关闭 only_full_group_by,会把每一个这样的错误变成一个没人看见的错误答案。这是错误的修复方式。
该检查能看穿建立在分组列之上的表达式,因此 SELECT COALESCE(rating, 'unrated') ... GROUP BY rating 会被接受。它看不穿建立在分组表达式之上的表达式。按 DATE_FORMAT(payment_date, '%Y-%m') 分组并选择 COALESCE(DATE_FORMAT(payment_date, '%Y-%m'), 'ALL'),同样的 ERROR 1055 会指出 payment_date,因为服务器比较的是这 2 个表达式,而不是它们内部的列。这是常见的报表场景,解决办法是在派生表中分组,然后在外层包装结果:
聚合一对多联接
在分组之前联接会使行数成倍增加,因此针对结果的SUM 会重复计数。症状是总数高出一个没人能解释的倍数。
在各自的子查询中分别聚合每一侧,然后联接结果,而不是先联接再分组:
当您不想折叠行时
GROUP BY 用每个分组 1 行来替换原来的行。当您想让聚合值伴随每一行时,窗口函数可以做到这一点而不折叠任何东西,并且通常不需要临时表:
故障排除
另请参阅
- MySQL 8.4 中的窗口函数——不折叠行的聚合
- MySQL JOIN 性能与常见错误——为什么一对多联接会放大总数
- MySQL CTE 何时优于派生表——在主查询之前预先聚合
- 常见 MySQL 错误——上下文中的 ERROR 1055 和 ERROR 1111
- GROUP BY——如果您想先学习子句本身,请看教程