GROUP BY 需要 ORDER BY NULL 以避免文件排序

我一次又一次地註意到，當我EXPLAIN用一個GROUP BY子句查詢時，我得到filesort了一個額外的條件。很久以前，我讀過一個建議GROUP BY NULL在這些情況下使用以避免文件排序，它確實消除了這種令人討厭的filesort情況。

我認為，如果不存在ORDER BY子句，則 dbms 只會呈現任意順序或任何最有效的順序，而不是按需要文件排序的某些神秘列進行排序。對我來說似乎很奇怪，我需要包含一個額外的方向，基本上相當於說*“不要做任何愚蠢的事情”*。

我的問題是為什麼這甚至是必要的並且ORDER BY NULL實際上增加了性能？

MySQL 5.7 參考手冊 / … / SELECT 語法

如果您使用 GROUP BY，則輸出行將根據 GROUP BY 列進行排序，就好像您對相同的列有一個 ORDER BY。為了避免 GROUP BY 產生的排序成本，添加 ORDER BY NULL

Using filesort當用於分組的列沒有適當的索引時出現。如上所述，返回的結果GROUP BY按相同的列排序。如果您filesort進行排序，則還可以使用文件排序進行分組。以同樣的方式侮辱表現。因此，您必須創建索引，而不是抑制排序。

EXPLAIN 
SELECT w.t_id
    , count(1) AS counter
 FROM points AS w
GROUP BY w.t_id
;

+----+-------------+-------+-------+---------------+-------+---------+------+----------+-------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows     | Extra       |
+----+-------------+-------+-------+---------------+-------+---------+------+----------+-------------+
|  1 | SIMPLE      | w     | index | t_id          | t_id  | 2       | NULL | 27228500 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+----------+-------------+

沒有。filesort_ORDER BY NULL

SELECT w.t_id, count(1) AS counter FROM points AS w GROUP BY w.t_id;
/* Affected rows: 0  Found rows: 606  Warnings: 0  Duration for 1 query: 6,922 sec. */
SELECT w.t_id, count(1) AS counter FROM points AS w GROUP BY w.t_id ORDER BY NULL;
/* Affected rows: 0  Found rows: 606  Warnings: 0  Duration for 1 query: 6,781 sec. */

附言

至於小提琴失敗這裡是mysql輸出：

沒有多列索引：

+----+-------------+---------------+------+-----------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table         | type | possible_keys   | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+---------------+------+-----------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | animals       | ALL  | PRIMARY         | NULL | NULL    | NULL |    3 | Using temporary; Using filesort                    |
|  1 | SIMPLE      | animal_colors | ALL  | animal_id,color | NULL | NULL    | NULL |    6 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+---------------+------+-----------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

添加了多列索引：

+----+-------------+---------------+-------+---------------------------------+-----------+---------+------------------------+------+-------------+
| id | select_type | table         | type  | possible_keys                   | key       | key_len | ref                    | rows | Extra       |
+----+-------------+---------------+-------+---------------------------------+-----------+---------+------------------------+------+-------------+
|  1 | SIMPLE      | animals       | index | PRIMARY                         | PRIMARY   | 4       | NULL                   |    3 | NULL        |
|  1 | SIMPLE      | animal_colors | ref   | animal_id,color,animal_id_color | animal_id | 4       | test.animals.animal_id |    1 | Using where |
+----+-------------+---------------+-------+---------------------------------+-----------+---------+------------------------+------+-------------+
2 rows in set (0.00 sec)

引用自：https://dba.stackexchange.com/questions/208166