7.2.1 Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT )

EXPLAIN tbl_name

Ou :

EXPLAIN SELECT select_options

EXPLAIN nom_de_table est un synonyme de DESCRIBE nom_de_table ou SHOW COLUMNS FROM nom_de_table .

• La syntaxe EXPLAIN tbl_name est synonyme de DESCRIBE tbl_name ou SHOW COLUMNS FROM tbl_name .
• Lorsque vous faites précéder une commande SELECT avec le mot clé EXPLAIN , MySQL vous explique comment il va traiter la commande SELECT , choisir les tables et index pour les jointures.

Avec l'aide de EXPLAIN , vous pouvez identifier les index à ajouter pour accélérer les commandes SELECT .

Vous pouvez aussi voir si l'optimiseur fait les jointures dans un ordre vraiment optimal. Pour forcer l'optimiseur à utiliser un ordre spécifique de jointure dans une commande SELECT , ajoutez l'attribut STRAIGHT_JOIN à la clause.

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 .

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

SELECT simple (sans utiliser de clause UNION ou de 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.

SUBQUERY

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'optimiseur. Les tables const sont très rapides, car elles ne sont lues qu'une fois.

const est utilisé lorsque vous comparez toutes les parties d'une clé PRIMARY / UNIQUE avec des constantes :

SELECT * FROM const_table WHERE primary_key=1; SELECT * FROM const_table WHERE primary_key_part1=1 AND primary_key_part2=2;

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 . eq_ref peut être utilisé pour les colonnes indexées, qui sont comparées avec l'opérateur = . L'élément comparé doit être une constante ou une expression qui utiliser les colonnes de la table qui est avant cette table.

Dans l'exemple suivant, ref_table sera capable d'utiliser eq_ref :

SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

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. ref peut être utilisé pour les colonnes indexées, qui sont comparées avec l'opérateur = .

Dans les exemples suivants, ref_table sera capable d'utiliser ref .

SELECT * FROM ref_table WHERE key_column=expr; SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

ref_or_null

Comme ref , mais avec le coût supplémentaire pour les recherches couvrant les valeurs NULL . Ce type de jointure est nouveau en MySQL 4.1.1 est sert essentiellement à la résolution des sous-requêtes.Dans les exemples suivants, MySQL peut utiliser une jointure ref_or_null pour traiter ref_table :

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

Comment MySQL optimise les clauses IS NULL .

index_merge

Ce type de jointure indique que l'optimisation de type Index Merge est utilisée. Dans ce cas, la colonne key contient une liste d'index utilisés, et key_len contient la liste des plus longues parties de clés utilisées. Pour plus d'informations, voyez Comment MySQL optimise les clauses OR .

unique_subquery

Ce type remplace le type ref dans certaines sous-requêtes IN de la forme suivante :

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery est simplement une analyse d'index, qui remplace complètement la sous-requête pour une meilleure efficacité.

index_subquery

Ce type de jointure est similaire à unique_subquery . Elle remplace des sous-requêtes IN , mais elle fonctionne pour les index non-uniques dans les sous-requêtes de la forme suivante :

value IN (SELECT key_column FROM single_table WHERE some_expr)

range

Seules 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. range peut être utilisé lorsqu'une colonne indexée est comparée avec une constante comme = , <> , > , >= , < , <= , IS NULL , <=> , BETWEEN ou IN .

SELECT * FROM tbl_name WHERE key_column = 10; SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; SELECT * FROM tbl_name WHERE key_column IN (10,20,30); SELECT * FROM tbl_name WHERE key_part1= 10 AND key_part2 IN (10,20,30);

index

C'est la même chose que ALL , hormis le fait que seul l'arbre d'index est étudié. C'est généralement plus rapide que ALL , car le fichier d'index est plus petit que le fichier de données.

Cette méthode peut être utilisée lorsque la requête utilise une colonne qui fait partie d'un index.

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 approprié, 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 de SELECT .Pour les tables MyISAM et BDB , la commande ANALYZE TABLE va aider l'optimiseur à choisir les meilleurs index. Pour les tables MyISAM , myisamchk --analyze fera la même chose. Voyez Syntaxe de ANALYZE TABLE et Utilisation de myisamchk pour maintenir les tables et recouvrir les données .

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 .

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;

• 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 (clé primaire)
  do	CUSTNMBR (clé primaire)

• Les valeurs de tt.ActualPC ne sont pas réparties également.

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,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      range checked for each record (key map: 35)

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);

table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
             ClientID,                                         where
             ActualPC
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

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

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   Using
             ClientID,                                         where
             ActualPC
et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

Le problème final est que, par défaut, MySQL supporte 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 :

mysql> <userinput>ANALYZE TABLE tt;</userinput>

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 Using
             ClientID,                                        where
             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