SQL EXPLAIN

Dans le langage SQL, l’instruction EXPLAIN est à utiliser juste avant un SELECT et permet d’afficher le plan d’exécution d’une requête SQL. Cela permet de savoir de quelle manière le Système de Gestion de Base de Données (SGBD) va exécuter la requête et s’il va utiliser des index et lesquels.

En utilisant cette commande la requête ne renverra pas les résultats du SELECT mais plutôt une analyse de cette requête.

A noter : le résultat de cette instruction est différent selon les SGBD, tel que MySQL ou PostgreSQL. Par ailleurs, le nom de cette instruction diffère pour certains SGBD :

  • MySQL : EXPLAIN
  • PostgreSQL : EXPLAIN
  • Oracle : EXPLAIN PLAN
  • SQLite : EXPLAIN QUERY PLAN
  • SQL Server :
    • SET SHOWPLAN_ALL : informations estimées d’une requête SQL, affiché au format textuel détaillé
    • SET SHOWPLAN_TEXT : informations estimées d’une requête SQL, affiché au format textuel simple
    • SET SHOWPLAN_XML : informations estimées d’une requête SQL, affiché au format XML
    • SET STATISTICS PROFILE : statistiques sur l’exécution d’une requête SQL, affiché au format textuel
    • SET STATISTICS XML : statistiques sur l’exécution d’une requête SQL, affiché au format XML
  • Firebird : SET PLANONLY ON; puis l’exécution de la requête SQL à analyser

Syntaxe

La syntaxe ci-dessous représente une requête SQL utilisant la commande EXPLAIN pour MySQL ou PostgreSQL :

EXPLAIN SELECT *
FROM `user`
ORDER BY `id` DESC

Rappel : dans cet exemple, la requête retournera des informations sur le plan d’exécution, mais n’affichera pas les “vrai” résultats de la requête.

Exemple

Pour expliquer concrètement le fonctionnement de l’instruction EXPLAIN nous allons prendre une table des fuseaux horaires en PHP. Cette table peut être créé à partir de la requête SQL suivante :

CREATE TABLE IF NOT EXISTS `timezones` (
 `timezone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `timezone_groupe_fr` varchar(50) DEFAULT NULL,
 `timezone_groupe_en` varchar(50) DEFAULT NULL,
 `timezone_detail` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`timezone_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=698;

La requête ci-dessous permet de mieux comprendre la structure et les index de cette table.

Imaginons que l’ont souhaite compter le nombre de fuseaux horaires par groupe, pour cela il est possible d’utiliser la requête SQL suivante :

SELECT timezone_groupe_fr, COUNT(timezone_detail) AS total_timezone
FROM `timezones` 
GROUP BY timezone_groupe_fr
ORDER BY timezone_groupe_fr ASC

Analyse de la requête SQL

Nous allons voir dans notre exemple comment MySQL va exécuter cette requête. Pour cela, il faut utiliser l’instruction EXPLAIN:

EXPLAIN SELECT timezone_groupe_fr, COUNT(timezone_detail) AS total_timezone
FROM `timezones` 
GROUP BY timezone_groupe_fr
ORDER BY timezone_groupe_fr ASC

Le retour de cette requête SQL est le suivant :

Requête SQL avec EXPLAIN sans index

Requête SQL avec EXPLAIN sans index

Dans cet exemple on constate les champs suivants :

  • id : identifiant de SELECT
  • select_type : type de cause SELECT (exemple : SIMPLE, PRIMARY, UNION, DEPENDENT UNION, SUBQUERY, DEPENDENT SUBSELECT ou DERIVED)
  • table : table à laquelle la ligne fait référence
  • type : le type de jointure utilisé (exemple : system, const, eq_ref, ref, ref_or_null, index_merge, unique_subquery, index_subquery, range, index ou ALL)
  • possible_keys : liste des index que MySQL pourrait utiliser pour accélérer l’exécution de la requête. Dans notre exemple, aucun index n’est disponible pour accélérer l’exécution de la requête SQL
  • key : cette colonne présente les index que MySQL a décidé d’utiliser pour l’exécution de la requête
  • key_len : indique la taille de la clé qui sera utilisée. S’il n’y a pas de clé, cette colonne renvois NULL
  • ref : indique quel colonne (ou constante) sont utilisés avec les lignes de la table
  • rows : estimation du nombre de ligne que MySQL va devoir analyser examiner pour exécuter la requête
  • Extra : information additionnelle sur la façon dont MySQL va résoudre la requête. Si cette colonne retourne des résultats, c’est qu’il y a potentiellement des index à utiliser pour optimiser les performances de la requête SQL. Le message “using temporary” permet de savoir que MySQL va devoir créer une table temporaire pour exécuter la requête. Le message “using filesort” indique quant à lui que MySQL va devoir faire un autre passage pour retourner les lignes dans le bon ordre

Ajout d’un index

Il est possible d’ajouter un index sur la colonne “timezone_groupe_fr” à la table qui n’en avait pas.

ALTER TABLE `timezones` ADD INDEX ( `timezone_groupe_fr` );

L’ajout de cet index va changer la façon dont MySQL peut exécuter une requête SQL. En effectuant la même requête que tout à l’heure, les résultats seront différent.

Requête SQL avec EXPLAIN avec index

Requête SQL avec EXPLAIN avec index

Dans ce résultat il est possible de constater que MySQL va utiliser un l’index “index_timezone_groupe_fr” et qu’il n’y a plus aucune information complémentaire d’indiquée dans la colonne “Extra”.

Partager