MySQL的四种GROUP BY用法

  知数堂第5期MySQL实战班学员,第10期MySQL优化班学员,现任职助教。

  在我的上一篇文章中,我们知道了通过索引或者其他的方式获取数据可能不是语句执行最耗时的操作。比如,MySQL的GROUP BY可能会占据语句执行时间的90%.

  详见:。简单的说,UDF函数会一个接着一个的获取构成单个组的所有行,这样就可以在处理下个组之前,计算出当前组的聚合值。

  这样处理有一个明显的问题。在大多数情况下,源数据并不是根据GROUP BY的组顺序进行保存的。我们需要特殊的步骤去处理MySQL的GROUP BY.

  在该查询当中,GROUP BY字段上面有索引,就可以在扫描索引数据的同时进行聚合计算。(索引上,数据是按照组来排序的)

  当我们使用limit来限制组的数量或者当覆盖索引被使用的时候,语句执行效率会特别高,因为只对索引进行顺序扫描是非常快速的操作。

  有时候,虽然聚合的组数很少,但是由于没有使用到覆盖索引,索引顺序扫描会造成大量的IO。所以这个方式可能不是最优的。

  如果我们没有一个允许我们按照组顺序扫描数据的索引,我们可以通过外部排序来排序数据(在MySQL中也被称为“filesort”)。

  你可能注意到我是用了SQL_BIG_RESULT来获取这个执行计划。没有它,MySQL不会选择这个执行计划。

  一般来说,只有当我们需要处理大量的组数时,MySQL才会使用这个计划,因为在这种情况下,外排序比用临时表处理更有效率(我们将在下面讨论)。

  在这种情况下,MySQL 也会进行全表扫描。但不会进行额外的排序,而是会创建一张临时表。该临时表当中每一个组会先保存一行记录,在处理剩余的行的时候,会把对应的行更新到临时表当中。 但如果结果表太大,更新可能会导致大量的磁盘IO。在这种情况下,外部排序会更有优势。

  请注意,虽然MySQL在此用例中默认选择了此计划,但如果我们不提供任何hint,它将比使用SQL_BIG_RESULT hint的计划慢10倍。

  你可能会注意到我添加了“ORDER BY NULL”.这是为了让执行计划只使用临时表进行GROUP BY操作。不然我们会得到其他的执行计划。

  上面的执行计划当中,同时使用了临时表和文件排序,这样的结果是非常糟糕的。

  MySQL 5.7 总是会对GROUP BY的结果按照组的顺序进行排序,即使语句并没有要求他这么做。ORDER BY NULL 可以取消这种默认排序。

  在某些情况下, 比如使用集合函数访问不同表中的列的JOIN查询,使用临时表可能是处理GROUP BY的唯一选择。

  前面3种GROUP BY的 执行方式适用于所有的聚合函数。但是有些聚合函数会使用第四种方法。

  这种方式只适用于MIN()和MAX()。再有索引的情况下,求最大最小值不需要对所有的值进行处理。

  如何直接的获取最大的ID值呢,如果索引是创建在k列上?这是InnoDB表。记住InnoDB会把所有主键值扩展到其他索引上面。(k)变成了(k,ID),允许我们使用Skip-Scan来优化这个语句。

  这种优化方式只有在每个组有大量数据的情况下才会生效。否则,MySQL更偏向于常规的方式来执行这个语句(就像文章开头第一种方法)

  MIN()/MAX()还有其他的优化方式。比如,在没有GROUP BY的情况下使用聚合函数(整张表就是一个组),MySQL在统计分析阶段就从索引中获取这些值,避免了在执行阶段读取表。

  上面已经介绍了4种MySQL执行GROUP BY的方式。为了简单,我直接对整张表进行了GROUP BY,并没有过滤任何数据。如果使用了WHERE进行数据过滤,上面方法还是适用的。

  在这个例子当中,我们使用了k列索引的范围扫描来过滤数据,在有临时表的时候进行GROUP BY操作。

  有些情况,这些方法并不冲突。但其他的情况下,我们必须二选一。要么用索引来GROUP BY,要么用来过滤数据。

  根据这个查询中k列使用的特定常量,我们可以看到,我们要么使用g列索引进行GROUP BY(放弃使用k列索引快速的过滤数据),要么使用k列索引进行数据过滤(使用临时表来处理GROUP BY),没办法同时使用到两个索引。

  根据我的经验,MySQL在这种情况下可能无法做出正确的选择。那时就需要使用FORCE INDEX hint来让语句按照你想要的方式执行。

  我希望这篇文章可以让大家更好的理解MySQL执行GROUP BY的方法。在下一篇博客当中,我将介绍一些优化GROUP BY的方法。

相关阅读