主页 / dba / 问题 / 208166
Accepted
Eaten by a Grue
Asked: 2018-05-30 10:50:04 +0800 CST

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

  • 772

我一次又一次地注意到，当我EXPLAIN用一个GROUP BY子句查询时，我得到filesort了一个额外的条件。很久以前，我读过一个建议GROUP BY NULL在这些情况下使用以避免文件排序，它确实消除了这种令人讨厌的filesort情况。

我认为，如果不存在ORDER BY子句，则 dbms 只会呈现任意顺序或任何最有效的顺序，而不是按需要文件排序的某些神秘列进行排序。对我来说似乎很奇怪，我需要包含一个额外的方向，基本上相当于说“不要做任何愚蠢的事情”。

我的问题是为什么这甚至是必要的并且ORDER BY NULL实际上增加了性能？

mysql group-by

2 个回答

  1. Best Answer

    MySQL 5.7 参考手册 / ... / SELECT 语法

    如果您使用 GROUP BY，则输出行将根据 GROUP BY 列进行排序，就好像您对相同的列有一个 ORDER BY。为了避免 GROUP BY 产生的排序开销，添加 ORDER BY NULL

    • 12

  2. 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)
    • 4

相关问题