Explain : optimisation de requêtes MySQL

Il existe un outil intégré à MySQL qui permet d’obtenir des informations très intéressantes, et qui vous donnera de très bonnes pistes d’optimisation lors de la construction de vos requêtes et de vos index. Il s’agit d’EXPLAIN.

Utilisation de EXPLAIN

Son utilisation est on ne peut plus simple. Il suffit de l’ajouter devant une requête MySQL.
Par exemple :


EXPLAIN SELECT nom, prenom FROM utilisateurs WHERE nom = 'Dupont';

Vous pouvez également avoir plus informations en utilisant le paramètre EXTENDED (en lançant alors la commande EXPLAIN EXTENDED).

Vous aurez alors un résultat comme celui-ci, qui vous indiquera le plan d’exécution de la requête :

Interprétation des résultats

Étudions plus en détail le résultat de EXPLAIN.

id : cette colonne donne un id différent à chaque SELECT (dans le cas de sous-requêtes). Dans l’exemple ci-dessous, toutes les lignes ont 1 en id car elles sont partie prenantes d’une seule clause SELECT.
select_type : le select_type dépend de votre requête et des éventuelles imbrications de requêtes et de sous-requêtes, ou d’union entre plusieurs SELECT.. Vous pourrez avoir comme valeur par exemple SIMPLE pour un simple SELECT, PRIMARY pour le premier SELECT d’une imbrication, DERIVED pour les SELECT dérivés, UNION…
table : la table ou l’alias concerné par la ligne.
type : comme type de joiture, une des valeurs les plus importantes renvoyée par EXPLAIN. Elle peut prendre les valeurs suivantes, de la meilleure valeur possible à la pire :
- system : la table n’a qu’une ligne
- const : la table ne renvoie au maximum qu’une ligne qui est indexée. La table n’est alors lue qu’une fois, et la valeur est considérée comme une constante.
- eq_ref : l’index de type PRIMARY ou UNIQUE NOT NULL est utilisé complètement par la jointure. Pour chaque combinaison des tables précédentes, une seule ligne est lue dans cette table.
- ref : toutes les lignes de la table avec des valeurs d’index correspondants sont lus pour chaque combinaison des tables précédentes. La différence avec eq_ref est donc que la jointure ne peut sélectionner une seule ligne.
- fulltext : la jointure se fait sur la base d’un index fulltext.
- ref_or_null : identique à ref avec en plus une recherche de valeurs nulles.
- index_merge : la jointure utilise l’optimisation « Index Merge », c’est à dire que pour répondre à la requête, il va fusionner plusieurs index existants sur la table. A ce moment là, une question pourrait se poser de savoir si un index multi-colonne ne pourrait pas être posé. Vous pouvez aller voir cet article sur le site de Percona qui traite du sujet, ainsi que la doc officielle sur la fusion des index.
- unique_subquery : équivalent à eq_ref pour certaines sous-requêtes sur des PRIMARY KEY ou des index uniques utilisée dans un IN.
- index_subquery : équivalent à unique_subquery mais des sur index non uniques.
- range : récupération de lignes correspondant à une plage de données, notamment avec des opérateurs comme BETWEEN, >, <, >=, <= …
- index : équivalent à ALL (voir ci-dessous), mais à la différence que l’arbre d’index est scanné complètement au lieu de la table (ce qui est généralement plus rapide, l’index étant la plupart du temps plus petit que la table).
- all : toute la table est scannée, cela indique généralement qu’il faut revoir les index ou la requête.
possible_keys : indique les index que MySQL estime comme pouvant être pertinents pour répondre à la requête. Si vous avez « NULL » dans cette colonne, vous pouvez regarder de plus près si la création d’un index ne s’imposerait pas.
key : clé que MySQL à décidé d’utiliser, qui peut d’ailleurs être un autre index que celui ou ceux listés dans les possibles. Cette valeur de retour, avec la précédente, est très importante pour déterminer si vos index sont bons et pertinents, et si une création d’index pourrait être intéressante.
key_len : longueur de l’index retenu.
ref : indique le champ qui est comparé à « key ».
rows : estimation du nombre de ligne que MySQL pense devoir examiner (cela peut être différent du nombre réel).
filtered (uniquement avec EXTENDED) : donne une estimation en pourcentage des lignes qui seront filtrées par le ou les conditions.
extra : informations supplémentaires que renvoie EXPLAIN. Vous avez la liste exhaustive des valeurs possibles dans la documentation officielle. A noter quelques valeurs intéressantes tout de même, comme « using temporary » qui indique que la requête va devoir créer une table temporaire, et « using filesort » qui indique qu’une passe supplémentaire sera nécessaire pour trier les résultats. Si vous avez un « using index« , c’est plutôt une bonne nouvelle, cela veut dire que vous avez un index couvrant et que MySQL va pouvoir répondre sans accès à la table.

Cas d’usage et index non utilisé

Par rapport à l’analyse des résultats de EXPLAIN, vous avez vu que cela va vous permettre de voir si vos index sont bien utilisés, et donc s’ils sont pertinents. Cela vous permettra aussi d’optimiser plus facilement vos requêtes.
Par exemple, j’ai pu ainsi me rendre compte sur une base dont j’avais récupéré la maintenance de certains soucis. Une des requêtes mettait un temps fou à s’exécuter, alors que les index semblaient pertinents. En utilisant EXPLAIN, j’ai pu voir que le principal d’index d’une table n’était pas utilisé, alors qu’il était utilisé en tant que jointure entre les deux plus grosses tables de la requête. En creusant la question, j’ai pu voir que les champs n’étaient pas équivalents entre les deux tables (les deux varchar n’avait pas la même taille, et l’un d’entre eux bizarrement à un encodage différent). Après modification de la taille et de l’encodage, l’index est utilisé et le temps d’exécution est divisé par 10.

Une bonne idée est de coupler cette analyse aux requêtes que vous renvoient les Slow Queries Log de MySQL.