Início / dba / Perguntas / 208166
Accepted
Eaten by a Grue
Asked: 2018-05-30 10:50:04 +0800 CST

GROUP BY precisa de ORDER BY NULL para evitar a classificação de arquivos

  • 772

Tenho notado repetidas vezes que quando EXPLAINconsulto com uma GROUP BYcláusula, recebo filesortcomo condição extra. Há muito tempo eu li uma sugestão para usar GROUP BY NULLnesses casos para evitar o filesort, e de fato elimina essa filesortcondição de aparência desagradável.

Eu pensaria que, se nenhuma ORDER BYcláusula estiver presente, o dbms apresentaria apenas uma ordem arbitrária ou o que for mais eficiente, em vez de ordenar por alguma coluna misteriosa que exija uma classificação de arquivos. Parece estranho para mim que eu precise incluir uma direção extra que basicamente equivale a dizer "não faça nada estúpido" .

Minha pergunta é por que isso é necessário e está adicionando ORDER BY NULLrealmente ajudando o desempenho?

mysql group-by

2 respostas

  1. Best Answer

    Manual de Referência do MySQL 5.7 / ... / Sintaxe SELECT

    Se você usar GROUP BY, as linhas de saída serão classificadas de acordo com as colunas GROUP BY como se você tivesse um ORDER BY para as mesmas colunas. Para evitar a sobrecarga de classificação que GROUP BY produz, adicione ORDER BY NULL

    • 12

  2. Using filesortaparece quando a(s) coluna(s) usada(s) para agrupamento não possui um índice apropriado. Conforme mencionado acima, os resultados retornados por GROUP BYsão ordenados pela(s) mesma(s) coluna(s). Se você conseguir filesortclassificar, também terá o filesort para agrupamento. Esse desempenho insulto da mesma maneira. Portanto, você deve criar o índice, não suprimir a classificação.

    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 |
+----+-------------+-------+-------+---------------+-------+---------+------+----------+-------------+

    Não filesortsem 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. */

    PS

    No que diz respeito ao fidlle falhar, aqui está a mysqlsaída:

    Sem índice de várias colunas:

    +----+-------------+---------------+------+-----------------+------+---------+------+------+----------------------------------------------------+
| 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)

    Índice de várias colunas adicionado:

    +----+-------------+---------------+-------+---------------------------------+-----------+---------+------------------------+------+-------------+
| 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

relate perguntas