Skip navigation links
7.2.11 Comment MySQL optimise les clauses GROUP BY »
Section Navigation      [Toggle]

7.2.10. Comment MySQL optimise ORDER BY

Dans certain cas, MySQL peut utiliser un index pour répondre à une requête ORDER BY ou GROUP BY sans faire aucun tri.

L'index peut être utilisé même si le ORDER BY ne correspond pas exactement à l'index, tant que toutes les parties inutilisée de l'index et les colonnes du ORDER BY sont constantes dans la clause WHERE. Les requêtes suivantes utilisent l'index pour répondre aux parties ORDER BY / GROUP BY : 

SELECT * FROM t1 ORDER BY partie_clef1,partie_clef2,...
SELECT * FROM t1 WHERE partie_clef1=constante ORDER BY partie_clef2
SELECT * FROM t1 WHERE partie_clef1=constante GROUP BY partie_clef2
SELECT * FROM t1 ORDER BY partie_clef1 DESC,partie_clef2 DESC
SELECT * FROM t1 WHERE partie_clef1=1 ORDER BY partie_clef1 DESC,partie_clef2 DESC

Quelques cas où MySQL ne peut pas utiliser les index pour répondre à ORDER BY: (Notez que MySQL utilisera quand même les indexes pour trouver les lignes qui correspondent à la clause WHERE) :

  • Vous effectuez un ORDER BY sur des clefs différentes : 

    SELECT * FROM t1 ORDER BY key1, key2;

  • Vous effectuez un ORDER BY en utilisant des parties de clef non consécutives. 

    SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

  • Vous mélangez ASC et DESC. 

    SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

  • La clef utilisée pour extraire les résultats n'est pas la même que celle utilisée lors du groupement ORDER BY : 

    SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

  • Vous faites une jointure entre plusieurs tables et les colonnes sur lesquelles vous faites un ORDER BY ne font pas toutes parties de la première table non-const qui est utilisée pour récupérer les lignes (C'est la première table dans l'affichage d'EXPLAIN qui n'utilise pas une méthode de récupération sur une ligne constante).

  • Vous avez plusieurs expressions ORDER BY et GROUP BY.

  • L'index de table utilisé est un type d'index qui n'enregistre pas les lignes dans l'ordre. (comme le type d'index HASH dans les tables HEAP).

Dans les cas où MySQL doit trier les résultats, il utilisera l'algorithme suivant :

  1. Lit toutes les lignes en fonction d'un index ou par scan de la table. Les lignes qui ne vérifient pas la condition WHERE sont ignorées.

  2. Stocke les valeurs des clés de tri dans un buffer. La taille du buffer est la valeur de la variable système sort_buffer_size.

  3. Lorsque le buffer se remplit, fait un tri rapide et stocke le résultat dans une fichier temporaire. Sauve le pointeur dans un bloc trié. Si toutes les lignes tiennent dans le buffer de tri, aucun fichier temporaire n'est créé.

  4. Répète les étapes précédentes jusqu'à ce que toutes les lignes aient été lues.

  5. Fait une combinaison multiple jusqu'à MERGEBUFF (7) régions en un bloc, dans un autre fichier temporaire. Répête l'opération jusqu'à ce que le premier fichier soit dans le second.

  6. Répête la suite jusqu'à ce qu'il y ait moins de MERGEBUFF2 (15) bloc libres.

  7. Dans la dernière combinaison multiple, seul le pointeur de ligne (la dernière partie de la clé de tri), est écrite dans le fichier de résultat.

  8. Lit les lignes triées en utilisant les pointeurs de lignes du fichier de résultat. Pour optimiser cela, on lit un gros bloc de pointeur, on les trie, et on les utilise pour lire les lignes en ordre dans un buffer. La taille du buffer est la valeur de la variable système read_rnd_buffer_size. Le code de cette étape est dans le fichier source sql/records.cc.

Vous pouvez vérifier avec EXPLAIN SELECT ... ORDER BY si MySQL peut utiliser des index pour répondre à cette requête. Si vous obtenez un Using filesort dans la colonne extra, c'est que MySQL ne peut utiliser d'index pour résoudre cet ORDER BY. See Section 7.2.1, « Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT) ».

Si vous voulez plus de rapidité avec les ORDER BY, vous devez d'abord voir si vous pouvez faire en sorte que MySQL utilises des index au lieu de passer par des phases de tri en plus. Si cela se révèle impossible, vous pouvez :

  • Augmenter la taille de la variable sort_buffer.

  • Augmenter la taille de la variable record_rnd_buffer.

  • Changer tmpdir pour qu'il pointe vers un disque dédié avec beaucoup d'espace libre. Si vous utilisez MySQL version 4.1 ou plus récent, vous pouvez répartir la charge entre plusieurs disques physiques en donnant à l'option tmpdir une liste de chemin, séparés par des deux-points (‘:’) ou des points-virgules ‘;’ sous Windows). Ils seront utilisés circulairement. Note : ces chemins doivent aboutir à différents disques physiques, et non pas différentes partitions du même disque.

Par défaut, MySQL trie les requêtes GROUP BY x,y[,...] comme si vous aviez spécifié l'ordre ORDER BY x,y[,...]. Si vous ajoutez une clause ORDER BY explicite, MySQL l'optimise aussi sans perte de vitesse, même si un tri a lieu. Si la requête inclut une clause GROUP BY mais que vous voulez éviter le surcoût du tri, vous pouvez supprimer le tri en spécifiant ORDER BY NULL : 

INSERT INTO foo SELECT a,COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

This is a translation of the MySQL Reference Manual that can be found at dev.mysql.com. The original Reference Manual is in English, and this translation is not necessarily as up to date as the English version.