Mysql序列（八）—— group by排序问题

在mysql中，group by默认会执行排序：

By default, MySQL sorts GROUP BY col1, col2, ... queries as if you also included ORDER BY col1, col2, ... in the query. If you include an explicit ORDER BY clause that contains the same column list, MySQL optimizes it away without any speed penalty, although the sorting still occurs.

上述是mysql官方手册描述：

默认情况下，mysql会像order by col1, col2,....一样排序group by col1, col2,...查询。如果查询中明确包含了与group by同样列的order by子句，mysql将不做任何加速优化。

If a query includes GROUP BY but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying ORDER BY NULL. For example:

INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

如果你对结果不想做任何group by排序，可以通过order by null抑制其进行排序。

虽然抑制了对结果的排序，但是优化器可能任然使用排序来实现group by分组操作，order by null只是抑制了对结果的排序。

转载于:https://www.cnblogs.com/lxyit/p/9448495.html