Dans MySQL, historiquement GROUP BY était également utilisé pour fournir le tri. Si une requête spécifiée GROUP BY, le résultat a été trié comme si ORDER BY était présent dans la requête.

MySQL here implicitly sorts the results from GROUP BY (i.e. in the absence of ASC or DESC designators for GROUP BY columns ).

MySQL also supported explicit sorting with GROUP BY (i.e. by using explicit ASC or DESC designators for GROUP BY columns).

Cela a changé dans la version 8.0 car il ne prend plus en charge le tri implicite ou explicite pour GROUP BY. Dans cet article de blog, je vais vous expliquer pourquoi ce changement est devenu nécessaire et aussi le travail effectué en tant que précurseur de ce changement.

GROUPER PAR dans MySQL

Pour regrouper un ensemble de lignes, MySQL optimizer choisit différentes méthodes. L’un d’eux consiste à trier les lignes avant de les regrouper. Cela facilite le regroupement d’un groupe après l’autre. Cela devient également peu coûteux s’il existe un index qui pourrait être utilisé pour obtenir des lignes triées. S’il n’y a pas d’index, l’optimiseur MySQL peut toujours décider de faire un tri externe (filesort) avant le regroupement.

Comme on le voit dans l’exemple, avant d’ajouter un index à la table, MySQL utilise un tri externe pour GROUPER PAR. Pour l’exemple de requête, j’ai forcé le plan en utilisant SQL_BIG_RESULT (car MySQL ne choisira pas ce plan pour l’ensemble de données que nous avons). Mais MySQL utiliserait ce plan pour regrouper en l’absence d’index pour obtenir des lignes triées et l’utilisation d’une table temporaire devient coûteuse en raison du grand nombre de groupes. Une fois l’index ajouté, il a recours à l’index pour GROUPER.

Mais avoir trié les lignes avant le regroupement n’est pas une nécessité. L’optimiseur peut décider d’utiliser une table temporaire pour le faire. Chaque ligne de cette table serait une ligne groupée et avec chaque ligne entrante, la ligne correspondant à ce groupe dans la table est mise à jour. Le tri n’est pas nécessaire ici. Cependant, comme GROUP BY dans MySQL était censé trier, il a été forcé de trier les lignes groupées même dans ce cas.

Dans l’exemple de requête, nous pouvons voir que bien que la table temporaire est utilisé, MySQL fait toujours un tri externe. Les utilisateurs devraient spécifier explicitement ORDER BY NULL pour que MYSQL sache que GROUP BY n’a pas besoin de trier. Une syntaxe non standard (ORDER BY NULL) était donc nécessaire pour contrer l’effet d’une autre extension non standard (GROUP BY tri). C’est beaucoup plus propre maintenant que nous avons éliminé ce désordre.

Suppression du tri implicite pour le GROUPE PAR

Il y a quelque temps, j’essayais de corriger le bogue 71804. Le journaliste s’attendait à ce que MySQL ne fasse pas le tri de fichiers inutile qu’il faisait pour GROUP BY. Essayer de créer un correctif pour le bogue nous a fait réaliser que l’optimisation de cette situation particulière n’est pas très simple en raison du support du tri implicite et explicite fourni par ce GROUPE. Nous avons donc conclu qu’avant que cette optimisation puisse être effectuée, nous devrions re-factoriser le code lié au tri pour GROUP BY.

La première étape pour ce faire a été de supprimer le tri implicite pour GROUP BY. Comme mentionné dans le manuel d’utilisation ici, il a été décidé de supprimer le support il y a quelque temps. Cela a été fait dans le cadre de la fonction d’index descendant de la version 8.0.

Comme on le voit dans l’exemple ci-dessus, le tri n’est pas effectué pour la requête. Par conséquent, les lignes groupées ne sont pas triées dans le résultat final. Si les utilisateurs ont besoin de lignes triées, ils doivent spécifier ORDER BY dans la requête.

Dans MySQL 5.7 et versions ci-dessous, les utilisateurs trouvent l’avertissement suivant dans le manuel.

« GROUP BYtrie implicitement par défaut (c’est-à-dire en l’absence de ASC ou DESC désignateurs pour les colonnes GROUP BY). Cependant, en s’appuyant sur un tri implicite GROUP BY (c’est-à-dire en l’absence de désignateurs ASC ou DESC) ou un tri explicite pour GROUP BY (c’est-à-dire en utilisant un ou DESC désignateurs pour les colonnes GROUP BY) est obsolète. Pour produire un ordre de tri donné, fournissez une clause ORDER BY. « 

Suppression du tri explicite pour GROUP BY

En ce qui concerne la suppression du tri explicite, c’était un peu plus difficile à faire. Nous ne pouvions pas le supprimer à moins que MySQL ne prenne en charge l’ORDRE avec ROLLUP. Le CUMUL avec ORDER BY n’était pas autorisé dans MySQL 5.7 et les versions antérieures. Ainsi, comme alternative, les utilisateurs utiliseraient GROUP BY ASC / DESC pour obtenir des données triées avec ROLLUP (Bien que le tri soit très restrictif avec des lignes super agrégées toujours placées après les lignes utilisées pour les calculer en cas d’ASC et vice versa pour DESC). Nous avons dû lever cette limitation avant de supprimer le support du tri explicite pour GROUP BY.

MySQL permet désormais de COMMANDER par avec ROLLUP. J’ai expliqué en détail comment utiliser cette amélioration ici. Comme expliqué dans le même blog, si les utilisateurs veulent exactement le même ordre de tri des valeurs nulles que celui de MySQL 5.7 pour le CUMUL, ils doivent utiliser la fonction GROUPING() pour réécrire la requête de manière simple.

Donc, en bref, nous avons fait les choses suivantes en tant que précurseurs de la suppression du tri explicite pour GROUP BY.

1. Ajout de la fonction GROUPING()

2. Suppression du tri implicite pour le GROUPE PAR

3. Autoriser ORDER BY avec ROLLUP

Et enfin nous avons supprimé le tri explicite pour GROUP BY dans MySQL 8.0.13.

Nous avons demandé l’avis des communautés il y a quelque temps. Nous avons conclu que les utilisateurs qui étaient au courant de cette extension non standard fournie par MySQL étaient d’accord pour qu’elle disparaisse.

Conclusion

Bien que nous ayons encore du travail à faire avant de corriger le bogue 71804, nous sommes heureux d’avoir réussi. Veuillez nous faire part de vos pensées. Merci d’utiliser MySQL!