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 optimis 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 correpond 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=constante ORDER BY partie_clef2
-
Vous mélangez
ASC
et
DESC
.
SELECT * FROM t1 ORDER BY partie_clef1 DESC,partie_clef2 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 :
-
Read all rows according to key or by table scanning.
Les lignes qui ne répondent pas à la clause
WHERE
sont évitées.
-
Store the sort-key in a buffer (of size
sort_buffer
).
-
Lorsque le tampon est plein, exécuter un qsort dessus et enregistrer le
résultat dans un fichier temporaire. Sauvegarder un pointeur sur le block
enregistré. (Dans le cas où toutes les lignes rentre dans le tampon de tri,
aucun fichier temporaire n'est crée)
-
Répeter ce qui précède jusqu'à ce que toutes les lignes soient lues.
-
Do a multi-merge of up to
MERGEBUFF
(7) regions to one block in
another temporary file. Repeat until all blocks from the first file
are in the second file.
-
Répeter ce qui précède jusqu'à ce qu'il y ait moins de
MERGEBUFF2
(15)
blocks à traiter.
-
On the last multi-merge, only the pointer to the row (last part of
the sort-key) is written to a result file.
-
Now the code in
sql/records.cc
will be used to read through them
in sorted order by using the row pointers in the result file. To
optimise this, we read in a big block of row pointers, sort these and
then we read the rows in the sorted order into a row buffer
(
record_rnd_buffer
) .
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 ajotuez 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;
|
|