Utiliser EXPLAIN pour écrire de meilleures requêtes SQL

Cet article a été publié il y a 8 ans 18 jours, il est donc possible qu’il ne soit plus à jour. Les informations proposées sont donc peut-être expirées.

Le problème de l’auto-formation, c’est qu’on oublie forcément des choses quand on se documente sur un sujet, en partie parce qu’on cherche en priorité les informations dont on a besoin. Fort heureusement, je baigne actuellement dans un monde où les collègues ne sont pas avares en conseils et astuces, ce qui me permet de monter en niveau très rapidement. Et j’ai découvert récemment le profilage de requêtes SQL, certes par le biais de PostgreSQL (dont je parlerai vite fait à la fin de l’article), et après n’avoir pas trouvé aussi détaillé qu’un article en anglais, j’ai décidé de vous le traduire.

Le billet d’aujourd’hui est donc une traduction d’un article de Shameer C. sur la mine de ressources qu’est Sitepoint.com. Si l’on se penche sur la « signature », elle dit ceci : « Shameer est un programmeur passionné et un enthousiaste originaire de Kerala, en Inde. Il a de l’expérience dans le développement web en Scala, PHP, Ruby, MySQL, et JavaScript. Quand il ne travaille pas, Shameer consacre son temps à développer des projets personnels, apprendre, visionner des screencasts (ndt : cherchez une meilleure trad :P) bloguer, etc. Ses centres d’intérêt spécifiques sont le cloud computing, et l’administration système et base de données« .

Quand vous envoyez une requête, l’optimiseur de requêtes de MySQL va tenter de dessiner un plan optimal pour l’exécution de celle-ci. Vous pouvez voir l’information à propos de ce plan en préfixant votre requête avec la commande EXPLAIN. EXPLAIN est un des plus puissants outils à votre disposition pour comprendre et optimiser des requêtes MySQL problématiques, mais la triste réalité est que les développeurs ne l’utilisent pratiquement jamais. Dans cet article, vous allez apprendre à quoi ressemble le résultat d’EXPLAIN et comment l’utiliser pour optimiser votre schéma et vos requêtes.

Comprendre la sortie d’EXPLAIN

Utiliser EXPLAIN est aussi simple que de l’ajouter au début d’une requête SELECT. Analysons un peu le résultat d’une requête simple pour se familiariser avec les colonnes retournées par la commande :

(NdT: La sortie peut être sous forme de tableau, en tout cas, c’est le cas sur MariaDB chez moi, mais les informations sont identiques). Ça ne paraît pas comme ça, mails il y a énormément d’informations contenues dans ces dix colonnes ! Détaillons-les :

– id – un identifiant séquentiel pour chaque SELECT de la requête (quand vous avez des sous-requêtes imbriquées)

– select_type – le type de requête SELECT. Les choix possibles sont :

• SIMPLE – un seul SELECT, sans UNIONs, sans sous-requêtes
• PRIMARY – le SELECT « le plus éloigné » dans un JOIN (NdT : le dernier ?)
• DERIVED – le SELECT fait partie d’une sous-requête dans une condition FROM
• SUBQUERY – le premier SELECT d’une sous-requête
• DEPENDANT SUBQUERY – une sous-requête elle-même dépendante d’une autre sous-requête
• UNCACHEABLE SUBQUERY – Une sous-requête qui ne peut pas être mise en cache (il y a certaines conditions pour ça)
• UNION – le SELECT est le second ou plus d’une UNION
• DEPENDANT  UNION – idem au dessus, mais dépendant d’une sous-requête « plus loin »
• UNION RESULT – le SELECT est le résultat d’une UNION

– table – la table référencée dans la ligne

– type – comment MySQL a relié les tables utilisées. C’est de loin le plus perspicace des champs retournés parce qu’il peut indiquer des indexes manquants ou comment il faudrait reconsidérer la façon dont la requête est écrite. Les valeurs possibles sont :

• system – la table a zéro ou une ligne.
• const – la table a seulement une ligne d’indexée qui correspond. C’est le plus rapide type de JOIN parce qu’on peut lire la table seulement une fois et le résultat est traité comme une constante quand on joint d’autres tables (NdT : pas besoin de relire la table en question).
• eq_ref – toutes les parties d’un index sont utilisées par la jointure et l’index est la clé primaire (PRIMARY KEY) ou unique et non nul (UNIQUE NOT NULL). C’est le deuxième type possible le plus rapide.
• ref – toutes les lignes correspondantes d’une colonne d’indexée sont lues pour chaque combinaison de lignes de la table précédente. Ce type de jointure apparait quand on fait des comparaisons sur des colonnes indexées avec les opérateurs = ou <=>.
• fulltext – la jointure utilise l’index FULLTEXT de la table.
• ref_or_null – identique à ref mais contient également les lignes avec une valeur « null » dans la colonne.
• index_merge – la jointure utilise une liste des indexes pour produire le résultat. La colonne « key » de la sortie d’EXPLAIN contiendra la liste des clés utilisées.
• unique_subquery – une sous-requête IN renvoie un seul résultat de la table et utilise une clé primaire.
• index_subquery – identique à unique_subquery mais retourne plus d’un résultat.
• range – un index est utilisé pour trouver des lignes correspondantes dans une plage spécifique, typiquement quand la colonne clé est comparée à une constante en utilisant des opérateurs comme BETWEEN, IN, >, >=, etc.
• index – l’arbre entier des indexes est utilisé pour trouver des colonnes correspondantes.
• all – toute la table est scannée pour trouver des correspondances dans la jointure. C’est le pire type de jointure et typiquement indique le manque d’indexes appropriés dans une table.

– possible_keys – montre les clés qui pourraient être utilisées par MySQL pour trouver des lignes dans la table, même si elles ne seraient pas utilisées en pratique. En fait, cette colonne peut souvent aider à optimiser des requêtes car si la colonne indique NULL, cela signifie qu’il n’a pas trouvé d’index pertinent.

– keys – indique l’index réellement utilisé par MySQL. Cette colonne peut contenir un index qui n’est pas dans la liste des possible_keys. L’optimiseur de MySQL cherche toujours une clé optimale qui peut être utilisée pour la requête. Quand il joint plusieurs tables, il peut identifier quelques clés qui ne sont pas dans la liste des clés possibles mais qui sont plus optimales.

– key_len – indique la longueur de l’index que l’optimiseur MySQL a choisi d’utiliser. Par exemple, une valeur de 4 signifie qu’il a besoin de mémoire pour stocker quatre caractères. Consultez la page Data type storage requirements (NdT: en anglais, merci Oracle) de la documentation MySQL pour en savoir plus sur le sujet.

– ref – affiche les colonnes et les constantes comparés à l’index nommé dans la colonne keys. MySQL va prendre soit la valeur d’une constante pour la comparer soit une colonne directement en se basant sur le plan d’exécution de la requête. On en verra plus dans l’exemple juste après.

– rows – Le nombre d’enregistrement examinés pour produire le résultat de la requête. C’est une colonne importante à surveiller lors de l’optimisation de requêtes, spécialement pour celles qui utilisent JOIN et autres sous-requêtes.

– Extra – contient des informations additionnelles en rapport avec le plan d’exécution. Des valeurs comme « Using temporary », « Using filesort », etc dans cette colonne peut indique une requête problématique. Pour une liste complète, référez-vous à la documentation de MySQL.

Vous pouvez également ajouter EXTENDED juste après EXPLAIN dans votre requête et MySQL vous montrera des informations supplémentaires sur la façon dont il exécute la requête. Pour voir le détail de l’information, vous pouvez faire suivre votre requête EXPLAIN par SHOW WARNINGS. C’est principalement utile pour voir la requête réellement exécutée après les transformations de l’optimiseur :

Analyser les performances avec EXPLAIN

Maintenant voyons comment on peut améliorer une mauvaise requête en analysant la sortie d’EXPLAIN. Quand on a affaire à une application du monde réel il y aura à n’en pas douter un certain nombre de tables avec plusieurs relations entre elles, mais parfois il est bien difficile d’anticiper la façon la plus optimale d’écrire une requête dessus.

Ici j’ai créé un exemple de base de données pour une application d’e-commerce qui n’a aucun index ni clé primaire, et on va démontrer l’impact d’un si mauvais design en écrivant une requête absolument horrible. Vous pouvez télécharger le schéma d’exemple sur GitHub :

Si vous regardez le résultat ci-dessus, vous pouvez voir tous les symptômes d’une mauvaise requête. Mais même si j’avais écrit une meilleure requête, le résultat n’aurait pas changé puisqu’il n’y a aucun index. Le « join_type » affiché est « ALL » (qui est le pire), ce qui signifie que MySQL n’a pu identifier aucune clé utilisable et c’est pourquoi les colonnes possible_keys et keys sont « NULL ». Plus important, la colonne rows montre que MySQL scanne toutes les lignes de chaque table de la requête. Donc pour exécuter cette dernière, il va scanner 7 × 110 × 122 × 326 × 2996 = 91,750,822,240 enregistrements pour trouver quatre pauvres résultats. C’est vraiment horrible, et ça ne fera qu’empirer à mesure que la base grossira.

Maintenant, ajoutons quelques indexes évidents, comme des clés primaires pour chaque table, et relançons notre requête. En règle générale, vous pouvez regarder les colonnes dans les clauses JOIN de la requête comme des bons candidats pour les clés parce que MySQL va toujours regarder dans ces colonnes pour trouver des résultats :

Bien, relançons donc maintenant notre requête après avoir ajouté nos indexes et le résultat devrait ressembler à ça :

Après avoir ajouté les indexes, le nombre de lignes scannés a été ramené à 1 × 1 × 4 × 1 × 1= 4. Ce qui veut dire que pour chaque enregistrement avec orderNumber à 10101 dans la table orderdetails, MySQL a été capable de trouver directement les résultats dans les autres tables grâce aux indexes sans avoir à scanner les tables complètes.

Dans la sortie de la première ligne vous pouvez voir que le type de jointure utilisée est « const », qui est le plus rapide des types pour une table avec plus d’un enregistrement. MySQL a pu utiliser la PRIMARY key comme index. La colonne ref affiche « const », qui n’est rien d’autre que la valeur 10101 utilisée dans la clause WHERE.

Jetons un œil à un autre exemple de requête. Ici on fait l’union de deux tables, products et productvariants, chacune jointe avec productline. La table productvariants contient les variantes d’un produit avec productCode comme clé de référence ainsi que les prix.

On peut voir un certain nombre de problèmes dans cette requête. Elle scanne tous les enregistrements des tables products et productvariants. Comme il n’y a pas d’index pour ces tables pour les colonnes productLine et buyPrice, les colonnes possible_keys et keys affichent « NULL ». L’état de products et de productlines est vérifié après l’UNION, donc les déplacer dans l’UNION va réduire le nombre d’enregistrements. Ajoutons donc quelques indexes et réécrivons la requête :

Comme on peut le voir dans le résultat, maintenant le nombre approximatif de lignes scannées a été réduit de 2,625,810 (219 × 110 × 109) à 276 (12 × 23), ce qui est un énorme gain en performances. Si vous essayez la même requête, sans la réarranger et juste en ajoutant les indexes, ça n’aurait fait aucune différence. MySQL n’est pas capable d’exploiter les indexes à cause de la clause WHERE dans le résultat dérivé. Après avoir déplacé ces conditions à l’intérieur de l’UNION, il peut utiliser les indexes. Cela signifie qu’ajouter des indexes n’est parfois pas suffisant; MySQL ne saura pas les utiliser à moins d’écrire les requêtes en conséquence.

Conclusion

Dans cet article, j’ai présenté EXPLAIN, ce que signifie sa sortie, et comment vous pouvez l’utiliser pour construire de meilleures requêtes. Dans le monde réel, ça peut être plus pratique que les scenarii présentés ici. Plus souvent, vous allez joindre un nombre certain de tables ensemble en utilisant des clauses WHERE complexes. Simplement ajouter des indexes sur quelques colonnes ne suffit pas toujours, et il faut alors se pencher sur les requêtes elles-mêmes.

La magie d’Internet aidant, et parce que je n’aime pas facilement m’avouer vaincu, j’ai fini par trouver (après avoir fini d’écrire la traduction, avant la relecture/mise en page) cet article sur l’utilisation de MySQL avec PHP et la façon dont on peut utiliser EXPLAIN pour optimiser ses requêtes.

Et comme je le disais en introduction, j’ai découvert EXPLAIN par le biais de PostgreSQL. Je n’ai pas besoin de détailler les raisons pour laquelle cette version-là est meilleure (apparemment PostgreSQL a beaucoup d’avantages sur MySQL à ce niveau), Stéphane Bortzmeyer l’a très bien décrit dans l’un de ses articles, et vous pourrez comparer le niveau de détail de PostgreSQL par rapport à MariaDB. Édifiant.