Dans le cadre de la maintenance des bases de données, il est courant d'activer le log des requêtes lentes pour identifier les instructions SQL qui prennent beaucoup de temps. Une fois ces requêtes repérées, l'étape suivante consiste à les analyser avec la commande EXPLAIN. Celle-ci permet de visualiser le plan d'exécution d'une requête SQL, de vérifier l'utilisation des index, de détecter les parcours complets de table, etc. En comprenant le fonctionnement de l'optimiseur basé sur les coûts de MySQL, on peut également obtenir des informations sur les différentes stratégies d'accès envisagées et celle qui sera probablement retenue (QEP : Query Execution Plan).

mysql> EXPLAIN SELECT * FROM serveurs;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | serveurs | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+

Le résultat de EXPLAIN contient dix colonnes : id, select_type, table, type, possible_keys, key, key_len, ref, rows et Extra. Voici la signification de chacune :

1. id

Indique l'ordre d'exécution des différentes parties de la requête. Plusieurs règles s'appliquent :

• Lorsque les id sont identiques, l'exécution se fait de haut en bas.
• Pour les sous-requêtes, l'id augmente : plus l'id est élevé, plus la priorité est grande et plus tôt la partie est exécutée.
• Un même groupe d'id correspond à un ensemble traité séquentiellement.

2. select_type (type de sélection)

Décrit le rôle de chaque clause SELECT dans la requête :

• SIMPLE : SELECT simple, sans UNION ni sous-requête.
• PRIMARY : SELECT le plus externe d'une requête complexe.
• UNION : deuxième ou dernier SELECT d'une UNION.
• DEPENDENT UNION : UNION dépendante du résultat extérieur.
• UNION RESULT : résultat de l'UNION.
• SUBQUERY : premier SELECT d'une sous-requête.
• DEPENDENT SUBQUERY : sous-requête dépendante du SELECT externe.
• DERIVED : SELECT d'une table dérivée (sous-requête dans FROM).
• UNCACHEABLE SUBQUERY : sous-requête non mise en cache, réévaluée à chaque ligne.

3. table

Nom de la table concernée par la ligne. Parfois il s'agit d'un alias comme <derivedN> où N représente le numéro d'exécution.

mysql> EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE id=2602) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  3 | DERIVED     | t1         | const  | PRIMARY,idx_t1_id | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

4. type (type d'accès)

Indique comment MySQL parcourt les lignes. Les types les plus courants, du moins performant au plus performant :

• ALL : parcour complet de la table.
• index : parcours complet d'un index.
• range : recherche par intervalle utilisant un index.
• ref : recherche par valeur non unique via un index.
• eq_ref : recherche par valeur unique (clé primaire ou unique) dans une jointure.
• const / system : accès par constante, par exemple sur la clé primaire. system est un cas particulier pour une table d'une seule ligne.
• NULL : MySQL n'a pas besoin d'accéder à la table ni à l'index, par exemple SELECT MIN(col) sur un index.

5. possible_keys

Liste les index que MySQL pourrait utiliser pour trouver les lignes. Cette colonne n'est pas liée à l'ordre d'exécution ; certains index listés peuvent ne pas être réellement utilisables dans le contexte.

6. key

Indique l'index effectivement choisi par l'optimiseur. S'il est NULL, aucun index n'est utilisé. Pour forcer un index, on peut utiliser FORCE INDEX, USE INDEX ou IGNORE INDEX.

7. key_len

Longueur en octets de l'index utilisé. Cette valeur est calculée à partir de la défniition de la table et représente la longueur maximale possible, pas la longueur réelle. Une valeur plus petite est généralement meilleure.

8. ref

Colonnes ou constantes utilisées pour la comparaison avec l'index mentionné dans la colonne key.

9. rows

Estimation du nombre de lignes que MySQL devra lire pour satisfaire la requête, basée sur les statistiques et les index.

10. Extra

Informations supplémentaires sur l'exécution :

• Using where : les lignes sont filtrées après lecture depuis le moteur de stockage.
• Using temporary : utilisation d'une table temporaire pour le résultat, souvent pour GROUP BY ou ORDER BY.
• Using filesort : tri sans utiliser d'index.
• Using join buffer : pas d'index pour la jointure, nécessite un buffer.
• Impossible where : la clause WHERE ne peut jamais être vraie.
• Select tables optimized away : l'optimiseur a pu retourner une ligne directement à partir des index, sans accéder aux tables.

Remarques importantes :

• EXPLAIN ne tient pas compte des déclencheurs, procédures stockées ou fonctions utilisateur.
• Il n'intègre pas l'effet des caches (par exemple le cache de requêtes).
• Il ne montre pas toutes les optimisations effectuées par MySQL lors de l'exécution.
• Certaines statistiques sont approximatives.
• EXPLAIN ne s'applique qu'aux instructions SELECT. Pour d'autres commandes, il faut les réécrire en SELECT pour obtenir le plan d'exécution.