5.2 Optimisation des SELECT s et autres requêtes
5 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 clause DISTINCT . Comment MySQL optimise LEFT JOIN et RIGHT JOIN . Comment MySQL optimise les clauses ORDER BY . Comment MySQL optimise la clause LIMIT . Vitesse des requêtes INSERT . Vitesses des commandes UPDATE . Rapidité des requêtes DELETE . Autres conseils d'optimisation
|
5.2.1 Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT )
EXPLAIN nom_de_table ou EXPLAIN SELECT select_options
|
EXPLAIN nom_de_table
est un synonyme de
DESCRIBE nom_de_table
ou
SHOW COLUMNS FROM nom_de_table
.Lorsque vous ajoutez au début d'une commande
SELECT
le mot
clé
EXPLAIN
, MySQL va expliquer le processus qu'il va suivre
pour exécuter la commande
SELECT
, et donner des détails
sur la façon avec laquelle il va joindre les tables, et dans quel
ordre.
Avec l'aide de
EXPLAIN
, vous pouvez identifier les index à ajouter
pour accélérer les commandes
SELECT
.
Vous devriez souvent utiliser la commande
ANALYZE TABLE
pour mettre à jour
les statistiques de cardinalité de vos tables, qui affectent les choix
de l'optimisateur. Syntaxe de
ANALYZE TABLE
.
Vous pouvez aussi voir si l'optimisateur fait les jointures dans un
ordre vraiment optimal. Pour forcer l'optimisateur à utiliser un ordre
spécifique de jointure dans une commande
SELECT
, ajoutez
l'attribut
STRAIGHT_JOIN
à la clause.
Pour les jointures complexes,
EXPLAIN
retourne une ligne d'information
pour chaque table utilisée dans la commande
SELECT
. Les tables sont
listées dans l'ordre dans lequel elles seront lues. MySQL résout toutes les
jointures avec une seule passe multi-jointure. Cela signifie que MySQL
lit une ligne dans la première table, puis recherche les lignes qui correspondent
dans la seconde, puis dans la troisième, etc. Lorsque toutes les tables ont été
traitées, MySQL affiche les colonnes demandées, et il remonte dans les tables
jusqu'à la dernière qui avait encore des lignes à traiter. La prochaine ligne est
alors traitée de la même façon.
Avec MySQL version 4.1 l'affichage de
EXPLAIN
a été modifié pour mieux
fonctionner avec les structures comme
UNION
, sous-requêtes, et tables dérivées.
La plus importante évolution est l'addition de deux nouvelles colonnes :
id
et
select_type
.
Le résultat de la commande
EXPLAIN
est constitué des colonnes suivantes :
-
id
-
identifiant de
SELECT
, le numéro séquentiel de cette commande
SELECT
dans la requête.
-
select_type
-
Type de clause
SELECT
, qui peut être :
-
SIMPLE
-
Simple
SELECT
(sans
UNION
s ou sous-requêtes).
-
PRIMARY
-
SELECT
extérieur.
-
UNION
-
Second et autres
UNION
SELECT
s.
-
DEPENDENT UNION
-
Second et autres
UNION
SELECTS
s, dépend de la commande
extérieure.
-
SUBSELECT
-
Premier
SELECT
de la sous-requête.
-
DEPENDENT SUBSELECT
-
Premier
SELECT
, dépendant de la requête extérieure.
-
DERIVED
-
Table dérivée
SELECT
.
-
table
-
La table à laquelle la ligne fait référence.
-
type
-
Le type de jointure. Les différents types de jointures sont les suivants,
dans l'ordre du plus efficace au plus lent :
-
system
-
La table a une seule ligne (c'est une table système). C'est un cas spécial
du type de jointure
const
.
-
const
-
La table a au plus une ligne correspondante, qui sera lue dès le début de la
requête. Comme il n'y a qu'une seule ligne, les valeurs des colonnes de cette
ligne peuvent être considérées comme des constantes pour le reste de l'optimisateur.
Les tables
const
sont très rapides, car elles ne sont lues qu'une fois.
-
eq_ref
-
Une ligne de cette table sera lue pour chaque combinaison de ligne des tables
précédentes. C'est le meilleur type de jointure possible, à l'exception
des précédents. Il est utilisé lorsque toutes les parties d'un index sont
utilisées par la jointure, et que l'index est
UNIQUE
ou
PRIMARY KEY
.
-
ref
-
Toutes les lignes avec des valeurs d'index correspondantes seront lues dans
cette table, pour chaque combinaison des lignes précédentes.
ref
est utilisé
si la jointure n'utilise que le préfixe de gauche de la clé, ou si la clé n'est pas
UNIQUE
ou
PRIMARY KEY
(en d'autres termes, si la jointure ne peut
pas sélectionner qu'une seule ligne en fonction de la clé). Si la clé qui est utilisée
n'identifie que quelques lignes à chaque fois, la jointure est bonne.
-
range
-
Seule les lignes qui sont dans un intervalle donné seront lues, en utilisant
l'index pour sélectionner les lignes. La colonne
key
indique quel est
l'index utilisé.
key_len
contient la taille de la partie de la clé qui est
utilisée. La colonne
ref
contiendra la valeur
NULL
pour ce type.
-
index
-
C'est la même chose que
ALL
, sauf que seul l'arbre d'index sera lu et scanné.
C'est généralement plus rapide que
ALL
, car le fichier d'index est généralement
plus petit que le fichier de données.
-
ALL
-
Une analyse complète de la table sera faîte pour chaque combinaison de lignes
issue des premières tables. Ce n'est pas bon si la première table n'est pas
une jointure de type
const
et c'est
très
mauvais dans les autres
cas. Normalement vous pouvez éviter ces situations de
ALL
en ajoutant
des index basée sur des parties de colonnes.
-
possible_keys
-
La colonne
possible_keys
indique quels index MySQL va pouvoir utiliser
pour trouver les lignes dans cette table. Notez que cette colonne est totalement
dépendante de l'ordre des tables. Cela signifie que certaines clés
de la colonne
possible_keys
pourraient ne pas être utilisées dans
d'autres cas d'ordre de tables.
Si cette colonne est vide, il n'y a pas d'index pertinent. Dans ce cas,
vous pourrez améliorer les performances en examinant votre clause
WHERE
pour voir si des colonnes sont susceptibles d'être indexée.
Si c'est le cas, créez un index ad hoc, et examinez le résultat avec
la commande
EXPLAIN
. Syntaxe de
ALTER TABLE
.
Pour connaître tous les index d'une table, utilisez le code
SHOW INDEX FROM nom_de_table
.
-
key
-
La colonne
key
indique l'index que MySQL va décider d'utiliser. Si la clé
vaut
NULL
, aucun index n'a été choisi. Pour forcer MySQL à
utiliser un index listé dans la colonne
possible_keys
, utilisez
USE KEY/IGNORE KEY
dans votre requête.
Syntaxe des
SELECT
.De plus, exécuter
myisamchk --analyze
( Syntaxe d'invocation de
myisamchk
) ou
ANALYZE TABLE
( Syntaxe de
ANALYZE TABLE
) sur la table va aider l'optimiseur
à choisir les index.
-
key_len
-
La colonne
key_len
indique la taille de la clé que MySQL a décidé d'utiliser.
La taille est
NULL
si la colonne
key
vaut
NULL
. Notez que cela vous
indique combien de partie d'une clé multiple MySQL va réellement utiliser.
-
ref
-
La colonne
ref
indique quelle colonne ou quelles constantes sont utilisées
avec la clé
key
, pour sélectionner les lignes de la table.
-
rows
-
La colonne
rows
indique le nombre de ligne que MySQL estime devoir
examiner pour exécuter la requête.
-
Extra
-
Cette colonne contient des informations additionnelle sur comment
MySQL va résoudre la requête. Voici une explication des différentes
chaînes que vous pourriez trouver dans cette colonne :
-
Distinct
-
MySQL ne va pas continuer à chercher d'autres lignes que la ligne courante,
après en avoir trouvé une.
-
Not exists
-
MySQL a été capable d'appliquer une optimisation de type
LEFT JOIN
sur la requête, et ne va pas examiner d'autres lignes de cette table pour la
combinaison de lignes précédentes, une fois qu'il a trouvé une ligne qui satisfait
le critère de
LEFT JOIN
.
Voici un exemple de cela :
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
|
Supposons que
t2.id
est défini comme
NOT NULL
. Dans ce cas,
MySQL va scanner
t1
et rechercher des lignes dans
t2
via
t1.id
. Si MySQL trouve une ligne dans
t2
, il sait que
t2.id
ne peut pas être
NULL
, et il ne va pas scanner le reste des lignes de
t2
qui ont le même
id
. En d'autres termes, pour chaque ligne de
t1
, MySQL n'a besoin que de faire une recherche dans
t2
, indépendamment
du nombre de lignes qui sont trouvées dans
t2
.
-
range checked for each record (index map: #)
-
MySQL n'a pas trouvé d'index satisfaisant à utiliser. Il va, à la place, pour
chaque combinaison de lignes des tables précédentes, faire une vérification
de quel index utiliser (si il en existe), et utiliser cet index pour continuer
la recherche. Ce n'est pas très rapide, mais c'est plus rapide que de faire
une recherche sans aucun index.
-
Using filesort
-
MySQL va avoir besoin d'un autre passage pour lire les lignes dans l'ordre.
Le tri est fait en passant en revue toutes les lignes, suivant le
type de jointure
est stocker la clé de tri et le pointeur de la ligne pour
chaque ligne qui satisfont la clause
WHERE
. Alors, les clés sont triées.
Finalement, les lignes sont triées dans l'ordre.
-
Using index
-
Les informations de la colonne sont lues de la table, en utilisant uniquement
les informations contenues dans l'index, sans avoir à faire d'autres lectures.
Cela peut arriver lorsque toutes les colonnes utilisées dans une table font
partie de l'index.
-
Using temporary
-
Pour résoudre la requête, MySQL va avoir besoin de créer une table temporaire
pour contenir le résultat. C'est typiquement ce qui arrive si vous utilisez
une clause
ORDER BY
sur une colonne différente de celles qui font partie
de
GROUP BY
.
-
Using where
-
Une clause
WHERE
sera utilisée pour restreindre les lignes qui seront
trouvées dans la table suivante, ou envoyée au client. Si vous n'avez pas cette
information, et que la table est de type
ALL
ou
index
,
vous avez un problème dans votre requête (si vous ne vous attendiez pas à tester
toutes les lignes de la table).
Si vous voulez rendre vos requêtes aussi rapide que possible, vous devriez
examiner les lignes qui utilisent
Using filesort
et
Using temporary
.
Vous pouvez obtenir une bonne indication de la qualité de votre jointure en
multipliant toutes les valeurs de la colonne
rows
dans la table de
la commande
EXPLAIN
. Cela est une estimation du nombre de lignes
que MySQL va examiner pour exécuter cette requête. C'est aussi ce nombre
qui sera utilisé pour interrompre votre requête, grâce à la variable
max_join_size
.
Choix des paramètres du serveur .L'exemple ci-dessous illustre comme une requête
JOIN
peut être optimisée
avec les résultats de la commande
EXPLAIN
.
Supposons que vous avez la requête
SELECT
suivante, et que vous l'examinez
avec
EXPLAIN
:
EXPLAIN SELECT tt.TicketNumber, tt.TimeIn, tt.ProjectReference, tt.EstimatedShipDate, tt.ActualShipDate, tt.ClientID, tt.ServiceCodes, tt.RepetitiveID, tt.CurrentProcess, tt.CurrentDPPerson, tt.RecordVolume, tt.DPPrinted, et.COUNTRY, et_1.COUNTRY, do.CUSTNAME FROM tt, et, et AS et_1, do WHERE tt.SubmitTime IS NULL AND tt.ActualPC = et.EMPLOYID AND tt.AssignedPC = et_1.EMPLOYID AND tt.ClientID = do.CUSTNMBR;
|
Pour cette exemple, nous supposons que :
-
Les colonnes utilisées sont déclarées comme ceci :
Table
|
Colonne
|
Type de colonne
|
tt
|
ActualPC
|
CHAR(10)
|
tt
|
AssignedPC
|
CHAR(10)
|
tt
|
ClientID
|
CHAR(10)
|
et
|
EMPLOYID
|
CHAR(15)
|
do
|
CUSTNMBR
|
CHAR(15)
|
-
Les tables ont les index suivants :
Table
|
Index
|
tt
|
ActualPC
|
tt
|
AssignedPC
|
tt
|
ClientID
|
et
|
EMPLOYID
(primary key)
|
do
|
CUSTNMBR
(primary key)
|
-
Les valeurs de
tt.ActualPC
ne sont pas réparties également.
Initialement, avant toute optimisation, la commande
EXPLAIN
produit les informations suivantes :
table type possible_keys key key_len ref rows Extra et ALL PRIMARY NULL NULL NULL 74 do ALL PRIMARY NULL NULL NULL 2135 et_1 ALL PRIMARY NULL NULL NULL 74 tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 range checked for each record (key map: 35)
|
Comme le type
type
vaut
ALL
pour chaque table, le résultat
indique que MySQL fait un scan complet de toutes les tables. Cela va
prendre un très long temps de calcul, car le nombre de lignes à examiner
de cette façon est le produit du nombre de lignes de toutes les tables :
dans notre cas, cela vaut
74 * 2135 * 74 * 3872 =
45,268,558,720
lignes. Si les tables étaient plus grandes, cela serait encore
pire.
Le premier problème que vous avons ici, est que MySQL ne peut pas (encore)
utiliser d'index sur les colonnes, si elles sont déclarées différemment.
Dans ce contexte, les colonnes
VARCHAR
et
CHAR
sont les mêmes,
mais elles ont été déclarée avec des tailles différentes. Comme
tt.ActualPC
est déclarée comme
CHAR(10)
et que
et.EMPLOYID
est déclaré comme
CHAR(15)
, il y a un problème
de taille.
Pour corriger cette disparité, utilisez la commande
ALTER TABLE
pour
agrandir la colonne
ActualPC
de 10 caractères à 15 :
mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
|
Maintenant,
tt.ActualPC
et
et.EMPLOYID
sont tous les deux des colonnes
de type
VARCHAR(15)
. Exécuter la commande
EXPLAIN
produit maintenant
le résultat suivant :
table type possible_keys key key_len ref rows Extra tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 Using where do ALL PRIMARY NULL NULL NULL 2135 range checked for each record (key map: 1) et_1 ALL PRIMARY NULL NULL NULL 74 range checked for each record (key map: 1) et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
|
Ce n'est pas parfait, mais c'est bien mieux. Le produit de toutes les lignes
a été divisé par 74). Cette version s'exécute en quelques secondes.
Une autre modification peut être faîte pour éliminer les problèmes de
taille de colonne pour
tt.AssignedPC = et_1.EMPLOYID
et
tt.ClientID = do.CUSTNMBR
:
mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15), -> MODIFY ClientID VARCHAR(15);
|
Maintenant,
EXPLAIN
produit le résultat suivant :
table type possible_keys key key_len ref rows Extra et ALL PRIMARY NULL NULL NULL 74 tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 where used ClientID, ActualPC et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
|
C'est presque aussi bon que cela pourrait l'être.Le problème final est que, par défaut, MySQL support que les valeurs de la colonne
tt.ActualPC
sont uniformément répartie, et que ce n'est pas le cas
pour la table
tt
. Mais il est facile de le dire à MySQL :
shell> myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt shell> mysqladmin refresh
|
Maintenant, la jointure est parfaite, et la commande
EXPLAIN
produit
ce résultat :
table type possible_keys key key_len ref rows Extra tt ALL AssignedPC NULL NULL NULL 3872 where used ClientID, ActualPC et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1 et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
|
Notez que la colonne
rows
dans le résultat de
EXPLAIN
est une
prédiction éclairée de l'optimisateur de jointure MySQL. Pour optimiser une
requête, vous devriez vérifier si ces nombres sont proches de la réalité.
Si ce n'est pas le cas, vous pourriez obtenir de meilleures performances avec
l'attribut
STRAIGHT_JOIN
dans votre commande
SELECT
, et en
choisissant vous même l'ordre de jointure des tables dans la clause
FROM
.
|