mysql 排序序列,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只是抑制了對結果的排序。