L'instruction EXPLAIN est un outil fondamental pour comprendre et optimiser les requêtes SQL dans MySQL. Lorsque vous utilisez EXPLAIN devant une instruction SELECT, MySQL fournit des informations détaillées sur la manière dont l'optimiseur de requêtes planifie d'exécuter la requête. Ces informations sont cruciales pour identifier les goulots d'étranglement et améliorer les performances.

Utilité de l'analyse avec EXPLAIN

L'analyse du plan d'exécution permet de visualiser plusieurs aspects clés du traitement d'une requête :

• L'ordre de chargement des tables et des jointures.
• Le type de requête (simple, sous-requête, union, etc.).
• Les index que MySQL pourrait utiliser et ceux qu'il utilise réellement.
• Les relations établies entre les tables.
• Le nombre estimé de lignes que l'optimiseur doit examiner.

Structure des résultats EXPLAIN

Le résultat de EXPLAIN est une table comportant généralement 12 colonnes, chacune offrant un éclairage sur le plan d'exécution : id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, et Extra. Nous allons détailler chaque colonne avec des exemples pratiques. Pour les démonstrations, nous utiliserons MySQL 5.7+ et des tables génériques comme articles, categories, utilisateurs, avec des relations comme articles.categorie_id = categories.id_categorie et articles.utilisateur_id = utilisateurs.id_utilisateur.

Détail des colonnes du plan d'exécution

1. id

Cette colonne indique l'ordre dans lequel les opérations SELECT sont exécutées. Plus la valeur de id est élevée, plus l'opération correspondante est exécutée tôt. Trois scénarios principaux peuvent se présenter :

• id identiques : Lorsque plusieurs lignes ont le même id, elles font partie du même groupe logique et sont exécutées de haut en bas, l'ordre précis étant déterminé par l'optimiseur.

EXPLAIN SELECT a.titre, c.nom_categorie FROM articles a JOIN categories c ON a.categorie_id = c.id_categorie;

+----+-------------+-----------+------------+-------+---------------+------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key              | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | c         | NULL       | ALL   | PRIMARY       | NULL             | NULL    | NULL              |    5 |      100 | NULL        |
|  1 | SIMPLE      | a         | NULL       | ref   | idx_cat_id    | idx_cat_id       | 4       | db.c.id_categorie |   10 |      100 | Using where |
+----+-------------+-----------+------------+-------+---------------+------------------+---------+-------------------+------+----------+-------------+

Ici, les deux lignes ont le même id=1, signifiant qu'elles sont traitées comme un seul groupe. L'ordre d'exécution est généralement du haut vers le bas, mais l'optimiseur peut choisir l'ordre le plus efficace.

• id différents : Si la requête contient des sous-requêtes imbriquées, les id s'incrémentent. L'opération avec le id le plus élevé est exécutée en premier.

EXPLAIN SELECT * FROM articles WHERE utilisateur_id IN (SELECT id_utilisateur FROM utilisateurs WHERE nom_utilisateur = 'Admin');

+----+-------------+-------------+------------+-------+-------------------+-------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys     | key               | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+-------------------+-------------------+---------+-------------------+------+----------+-------------+
|  1 | PRIMARY     | articles    | NULL       | ALL   | idx_user_id       | NULL              | NULL    | NULL              |   10 |      100 | Using where |
|  2 | SUBQUERY    | utilisateurs| NULL       | const | PRIMARY,idx_name  | PRIMARY           | 4       | const             |    1 |      100 | Using index |
+----+-------------+-------------+------------+-------+-------------------+-------------------+---------+-------------------+------+----------+-------------+

Dans cet exemple, la sous-requête (id=2) est exécutée avant la requête principale (id=1) car elle a un id plus élevé.

• Combinaison des deux : Il est courant de trouver un mélange des deux situations, où des groupes d'opérations partagent le même id et sont imbriqués dans d'autres opérations avec des id différents.

2. select_type

Indique le type de l'opération SELECT, permettant de distinguer les requêtes complexes :

• SIMPLE : Une requête SELECT simple, sans sous-requêtes ni opérations UNION.
• PRIMARY : La requête SELECT la plus externe dans une instruction contenant des sous-requêtes.
• SUBQUERY : Une sous-requête dans la clause SELECT ou WHERE.
• DERIVED : Une sous-requête dans la clause FROM. Le résultat de cette sous-requête est matérialisé en une table temporaire.
• UNION : La deuxième (ou suivante) instruction SELECT dans une clause UNION.
• UNION RESULT : Le résultat d'une UNION, provenant d'une table temporaire.

3. table

Nom de la table à laquelle la ligne de sortie EXPLAIN se réfère. Il peut s'agir d'un nom réel, d'un alias, ou d'une table temporaire générée (par exemple, <derived2> pour une table dérivée de la requête avec id=2, ou <union2,3> pour le résultat d'une union entre les requêtes 2 et 3).

4. partitions

Affiche les partitions correspondantes pour les tables partitionnées. Pour les tables non partitionnées, cette valeur est NULL.

5. type

C'est l'un des indicateurs les plus importants pour l'optimisation. Il décrit le type de jointure utilisé pour accéder aux lignes de la table. La performance diminue généralement de system à ALL :

system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

• system : La table n'a qu'une seule ligne (table système ou table vide). Très rapide.
• const : La table a une seule ligne correspondante, accédée par une clé primaire ou un index unique. C'est le type le plus rapide pour les lectures de table.

EXPLAIN SELECT * FROM utilisateurs WHERE id_utilisateur = 1;

31. eq_ref : Utilisé pour les jointures où toutes les parties d'une clé primaire ou d'un index unique NOT NULL sont utilisées. Pour chaque ligne de la table précédente, il y a une seule ligne correspondante dans la table actuelle.

EXPLAIN SELECT a.titre, u.nom_utilisateur FROM articles a JOIN utilisateurs u ON a.utilisateur_id = u.id_utilisateur;

33. ref : Utilisé lorsque des index non uniques sont utilisés pour trouver des lignes. Peut renvoyer plusieurs lignes pour chaque valeur de clé.

EXPLAIN SELECT * FROM articles WHERE categorie_id = 5;

35. ref_or_null : Similaire à ref, mais inclut une recherche de lignes contenant des valeurs NULL dans la colonne de l'index.
36. index_merge : MySQL utilise plusieurs index pour satisfaire la requête.

-- Supposons des index sur 'titre' et 'date_publication'
EXPLAIN SELECT * FROM articles WHERE titre LIKE 'SQL%' OR date_publication > '2023-01-01';

38. range : Un index est utilisé pour sélectionner des lignes dans une plage donnée. Courant avec les opérateurs <, >, <=, >=, BETWEEN, IN.

EXPLAIN SELECT * FROM articles WHERE id_article BETWEEN 10 AND 50;

40. index : Parcours complet de l'index pour récupérer les données. Plus rapide qu'un ALL si l'index est une couverture de la requête, car cela évite l'accès aux données de la table.

-- Supposons un index sur 'date_publication'
EXPLAIN SELECT date_publication FROM articles ORDER BY date_publication;

42. ALL : Un scan complet de la table. Le plus lent, car il doit lire chaque ligne de la table pour trouver les correspondances. Souvent le signe d'un manque d'index ou d'une mauvaise utilisation des index existants.

EXPLAIN SELECT * FROM articles WHERE description LIKE '%MySQL%';

6. possible_keys

Indique les index que MySQL pourrait utiliser pour trouver les lignes. Ce n'est qu'une liste de candidats potentiels ; l'optimiseur choisit l'index réel à utiliser, qui peut être absent de cette liste ou ne pas être le premier de la liste.

7. key

L'index réellement utilisé par MySQL. Si aucun index n'est utilisé, la valeur est NULL. Si type est index_merge, plusieurs index peuvent apparaître ici.

8. key_len

Longueur de la clé d'index utilisée, en octets. Une key_len plus courte indique généralement une utilisation plus efficace de l'index. Elle aide à détemriner quelle partie d'un index composé est utilisée. Notez que key_len ne prend en compte que les colonnes utilisées dans la clause WHERE, pas celles utilisées pour le tri ou le regroupement.

9. ref

Montre quelles colonnes ou constantes sont comparées à l'index de la colonne key. Il peut s'agir d'une constante (const), d'une colonne d'une autre table (nom_de_table.nom_de_colonne), ou d'une fonction (func).

10. rows

Estimation du nombre de lignes que MySQL doit examiner pour trouver les lignes pertinentes. C'est un indicateur important de la performance potentielle de la requête ; une valeur plus faible est généralement préférable.

11. filtered

Un pourcentage indiquant la proportion de lignes filtrées après avoir été examinées par le moteur de stockage. Par exemple, si le moteur de stockage renvoie 100 lignes et que filtered est 50 %, cela signifie que 50 lignes répondent aux critères de la requête après le filtrage.

12. Extra

Fournit des informations supplémentaires non représentées dans les autres colonnes. C'est une colonne très importante pour l'optimisation, car elle peut révéler des opérations coûteuses.

• Using index : Indique que la requête est satisfaite uniquement par les données de l'index (requête couvrante), sans avoir à accéder aux lignes complètes de la table. C'est une situation très performante.

-- Supposons un index sur 'titre'
EXPLAIN SELECT titre FROM articles WHERE titre LIKE 'MySQL%';

60. Using where : MySQL doit filtrer des lignes après les avoir récupérées, car il n'a pas pu utiliser un index pour les filtrer directement.

-- Si 'description' n'est pas indexée
EXPLAIN SELECT titre FROM articles WHERE description LIKE '%optimisation%';

62. Using temporary : MySQL doit créer une table temporaire pour traiter la requête. Souvent lié aux clauses GROUP BY ou ORDER BY sur des colonnes non indexées.

EXPLAIN SELECT categorie_id, COUNT(*) FROM articles GROUP BY categorie_id HAVING COUNT(*) > 5;

64. Using filesort : MySQL doit trier les résultats en utilisant un algorithme de tri qui ne peut pas s'appuyer sur l'ordre d'un index. Très coûteux pour les grands ensembles de données.

-- Si 'date_creation' n'est pas indexée
EXPLAIN SELECT * FROM utilisateurs ORDER BY date_creation DESC;

66. Using join buffer (Block Nested Loop) : Utilisé pour les jointures lorsque l'indexation n'estt pas optimale, nécessitant un tampon de jointure pour stocker les résultats intermédiaires.

-- Si 'description' n'est pas indexée dans les deux tables
EXPLAIN SELECT a.titre FROM articles a JOIN commentaires c ON a.description = c.texte_commentaire;

68. Impossible where : La clause WHERE ne peut jamais être satisfaite.

EXPLAIN SELECT * FROM articles WHERE 1=0;

70. No tables used : La requête ne se réfère à aucune table (par exemple, une requête sur une fonction ou une constante).

EXPLAIN SELECT NOW();

L'analyse du plan d'exécution est une compétence essentielle pour tout développeur ou administrateur de base de données. Maîtriser l'interprétation des informations fournies par EXPLAIN est la première étape pour diagnostiquer et résoudre les problèmes de performance des requêtes SQL.

Pour une compréhension plus approfondie, la documentation officielle de MySQL est une ressource inestimable : MySQL EXPLAIN Output.