7.2 Optimisation des commandes SELECT et autres requêtes
7 Optimisation de MySQL
Manuel de Référence MySQL 4.1 : Version Française
. Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT )
. Mesurer les performances d'une requête
. Vitesse des requêtes SELECT
. Comment MySQL optimise les clauses WHERE
. Comment MySQL optimise la condition OR
. Comment MySQL optimise IS NULL
. Comment MySQL optimise DISTINCT
. Comment MySQL optimise les clauses LEFT JOIN et RIGHT JOIN
->Comment MySQL optimise ORDER BY
. Comment MySQL optimise LIMIT
. Comment éviter les scans de tables,,,
. Vitesse des requêtes INSERT
. Vitesses des commandes UPDATE
. Rapidité des requêtes DELETE
. Autres conseils d'optimisation
|
7.2.9 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 :
- 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.
- 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
.
- 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éé.
- Répète les étapes précédentes jusqu'à ce que toutes les lignes aient été lues.
- 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.
- Répête la suite jusqu'à ce qu'il y ait moins de
MERGEBUFF2
(15)
bloc libres.
- 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.
- 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
. Syntaxe de
EXPLAIN
(Obtenir des informations
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;
|
|