MySQL Manuel de Référence pour 3.23.5-alpha. - 7.21 EXPLAIN (Détails sur SELECT)

7.21 `EXPLAIN` (Détails sur `SELECT`)

  EXPLAIN SELECT select_options

L'option EXPLAIN force MySQL à expliquer la façon avec laquelle il va traiter la requête SELECT, en détaillant les opérations de regroupement.

Avec ces informations, il est possible de determiner les tables qui requièrent des indexes pour accélerer les SELECT, ainsi que l'ordre optimal dans lequel les tables reseront regroupées. Pour obliger l'optimiseur à respecter l'ordre de regroupement, il suffit d'ajouter l'option STRAIGHT_JOIN.

Pour les regroupements complexes, EXPLAIN retourne une ligne d'informations pour chaque table utilisée par la commande SELECT. Les tables sont listées dans l'ordre de lecture. MySQL résoud les regroupements en utilisant une méthode d'aggrégation en un seul passage. Cela signifie que MySQL lit une ligne de la première table, puis il recherche les lignes correspondantes dans la seconde table, et ainsi de suite jusqu'à la dernière table. Quand toutes les tables ont été traitées, il retourne la ligne selectionnée, et remonte progressivement jusqu'à la table initiale. Puis, la ligne suivante est selectionnée, et le processus continue avec la ligne suivante.

La réponse de EXPLAIN inclus les colonnes suivantes :

table La table qui est utilisée par la commande.

type Le type de regroupement. Le détails sur les différents types est donnés plus bas.

possible_keys Les possible_keys indiques quels index MySQL peut utiliser pour rechercher des lignes dans la table. Si la colonne est vide, il n'y a pas d'index. Dans ce cas, il est surement possible d'améliorer les performances de la requête en éxaminant la clause WHERE pour voir quelles colonnes sont utilisées, et quelles colonnes mériteraient un indexage. Dans ce cas, il suffit de créer l'index adéquat, et de reverifier la requête avec EXPLAIN . Pour voir quels sont les index disponibles pour une table, il faut utiliser la commande SHOW INDEX FROM Nom_table.

key La colonne clé indique quelle clé MySQL a décidé d'utiliser. La clé est NULLsi aucun index n'est choisi.

key_len key_len indique la longueur de la clé que a décidé d'utiliser. La longueur sera NULL si la clé est NULL.

ref ref est le ou les numéros de colonne ou constantes utilisée avec la clé pour rechercher les lignes dans la table.

rows rows indique le nmbre de ligne que MySQL doit examiner pour éxécuter la requête.

Extra Si la colonne inclus le texte Only index , cela signifie que les informaions sont renvoyées par la table en utilisant uniquement l'index. Généralement, c'est beaucoup plus rapide que de scanner la table entière. Si cette colonne contient le texte where used, cela signifie que la clause WHERE a été utilisée pour restreindre le nombre de ligne à retourner au client.

Voici maintenant la liste des différents types de regroupement, du plus efficace au moins efficace :

system La table n'a qu'une seule ligne (= table système). C'est un cas spécial de regroupement de type const.

const La table a au maximum une ligne à traiter, qui sera lue au début de la requête. Etant donné qu'il n'y a qu'une seule ligne, les valeurs de cette ligne peuvent être considérées comme des constantes pour l'optimisateur. Les tables de type const sont extrêmement rapide à lire!

eq_ref Une ligne sera lue de cette table pour chaque combinaisons de ligne des tables précédentes. C'est le meilleur type de regroupement possible, en dehors du type const. Il sera utilissé lorsque toutes les colonnes d'un index sont utilisées par un regroupement, et que l'index est UNIQUE ou PRIMARY KEY.

ref Toutes les lignes qui correspondent aux valeurs de l'index seront lues dans cette table, pour chaque combinaison de lignes des tables précédentes. ref est utilisé si le regroupement utilise un préfixe comme clé, ou si la clé n'est pas UNIQUE ou PRIMARY KEY( en d'autres termes, si le regroupement ne peut pas sectionner une ligne unique à partir de la clé). Si la clé qui est utilisée ne rassemble que très peu de lignes, le regroupement est bon.

range Seules les lignes dans l'intervalle considéré seront renvoyée, en utilisant un index pour selectionner les lignes. La colonne ref indiquera quelles index sera utilisé.

index Ce type est indentique à ALL, sauf que seul l'index est scanné. C'est généralement plus rapide que le type ALL, car un fichier d'index est généralement plus petit que le fichier de données.

ALL Une recherche sur la table complète va être faite, pour chaque combinaison des tables précédentes. C'est généralement très mauvais si la première table n'est pas marquée const, et encore plus mauvais dans les autres cas. On peut éviter d'utiliser ce mode de recherche en ajoutant des index, afin de transformer les lignes en constantes.

Un bon critère pour evaluer l'efficacité d'un regroupement est de multiplier toutes les valeurs dans la colonne rows d'une requête avec EXPLAIN. Cela mesure approximativement le nombre de ligne que MySQL doit examiner pour résoudre la requête. Ce nombre sera aussi utilisé pour restreindre la taille du regroupement, avec max_join_size.

L'exemple suivant montre comme une clause peut être optimisée progressivement gr…e aux informations fournies par EXPLAIN. On supposera que l'on souhaite exécuter la commande SELECTci-dessous, et qu'on l'examine 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 cet exemple, on supposera par hypothèse :

Les colonnes comparées sont déclarées comme suit :

Table Column Column type

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 n'ont pas encore été assignées.

Au démarrage, avant la moindre optimisation, la clause produit la réponse suivante :

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)

Etant donné que le type de regroupement est ALL pour toutes les tables, ces informations indique que MySQLfait un regroupement sur toutes les tables! Cela va prendre un temps énorme, vu le nombre de lignes à étudier dans chaque table. Pour le cas présent, cela représente 74 * 2135 * 74 * 3872 = 45,268,558,720 rows. Et encore, ces tables pourraient être encore plus grosses...

Un des problèmes posés ici est que MySQL ne peut pas encore (encore) utiliser d'index pour des colonnes déclarées de manière différentes. Dans l'exemple, VARCHAR et CHAR sont identiques, à moins qu'ils ne soient déclarés sur des longueurs différentes. Or, tt.ActualPC est de type CHAR(10) et et.EMPLOYID est de type CHAR(15) : les longueur ne concordent pas.

Pour consolider la base, on utilise la commande ALTER TABLE pour rallonger le champs ActualPC de 10 caractères à 15 caractères:

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

Maintenant, tt.ActualPC et et.EMPLOYID sont tous les deux de type VARCHAR(15). L'éxecution de 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    where used
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 nettement mieux (le produit des colonnes rows a été réduit d'un facteur 74). Cette version s'éxecute maintenant en quelques secondes.

Une autre amélioration peut être apportée en éliminant les disparités de longueur entre les colonnes tt.AssignedPC et et_1.EMPLOYID, et d'autres part , tt.ClientID et do.CUSTNMBR comparisons:

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
                      MODIFY ClientID   VARCHAR(15);

Maintenant EXPLAIN produit maintenant le résultat suivant :

table type   possible_keys   key     key_len ref            rows     Extra
et    ALL    PRIMARY         NULL    NULL    NULL           74
tt    ref    AssignedPC,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where used
et_1  eq_ref PRIMARY         PRIMARY 15      tt.AssignedPC  1
do    eq_ref PRIMARY         PRIMARY 15      tt.ClientID    1

C'est déjà très bien.

Le problème final est que, par défaut, MySQL suppose que les valeurs dans la colonne tt.ActualPC sont réparties uniformément. Or, ce n'est pas le cas de la table tt. Heureusement, il est facile de le préciser à MySQL :

shell> isamchk --analyze PATH_TO_MYSQL_DATABASE/tt
shell> mysqladmin refresh

Le résultat est maintenant parfait, et maintenant 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    where used
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

On peut noter que la colonne rows est une estimation de la part de l'optimisateur de regroupement de MySQL : pour optimiser une commande, il faudrait maintenant vérifier qu'elle a des chiffres proche de la vérité. Si non, il faudrait améliorer les performances avec la clause STRAIGHT_JOIN dans la commande SELECT, et essayer de proposer différents ordres pour la liste des tables .