
Utiliser EXPLAIN pour écrire de meilleures requêtes SQL

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 :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
EXPLAIN SELECT * FROM categoriesG; ********************** 1. row ********************** id: 1 select_type: SIMPLE table: categories type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 Extra: 1 row in set (0.00 sec) |
(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 :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
EXPLAIN EXTENDED SELECT City.Name FROM City JOIN Country ON (City.CountryCode = Country.Code) WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia' ********************** 1. row ********************** id: 1 select_type: SIMPLE table: Country type: const possible_keys: PRIMARY key: PRIMARY key_len: 3 ref: const rows: 1 filtered: 100.00 Extra: ********************** 2. row ********************** id: 1 select_type: SIMPLE table: City type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4079 filtered: 100.00 Extra: Using where 2 rows in set, 1 warning (0.00 sec) |
1 2 3 4 5 6 7 |
SHOW WARNINGS ********************** 1. row ********************** Level: Note Code: 1003 Message: select `World`.`City`.`Name` AS `Name` from `World`.`City` join `World`.`Country` where ((`World`.`City`.`CountryCode` = 'IND')) 1 row in set (0.00 sec) |
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 :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
EXPLAIN SELECT * FROM orderdetails d INNER JOIN orders o ON d.orderNumber = o.orderNumber INNER JOIN products p ON p.productCode = d.productCode INNER JOIN productlines l ON p.productLine = l.productLine INNER JOIN customers c on c.customerNumber = o.customerNumber WHERE o.orderNumber = 10101 ********************** 1. row ********************** id: 1 select_type: SIMPLE table: l type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 7 Extra: ********************** 2. row ********************** id: 1 select_type: SIMPLE table: p type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 110 Extra: Using where; Using join buffer ********************** 3. row ********************** id: 1 select_type: SIMPLE table: c type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 122 Extra: Using join buffer ********************** 4. row ********************** id: 1 select_type: SIMPLE table: o type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 326 Extra: Using where; Using join buffer ********************** 5. row ********************** id: 1 select_type: SIMPLE table: d type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2996 Extra: Using where; Using join buffer 5 rows in set (0.00 sec) |
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 :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
ALTER TABLE customers ADD PRIMARY KEY (customerNumber); ALTER TABLE employees ADD PRIMARY KEY (employeeNumber); ALTER TABLE offices ADD PRIMARY KEY (officeCode); ALTER TABLE orderdetails ADD PRIMARY KEY (orderNumber, productCode); ALTER TABLE orders ADD PRIMARY KEY (orderNumber), ADD KEY (customerNumber); ALTER TABLE payments ADD PRIMARY KEY (customerNumber, checkNumber); ALTER TABLE productlines ADD PRIMARY KEY (productLine); ALTER TABLE products ADD PRIMARY KEY (productCode), ADD KEY (buyPrice), ADD KEY (productLine); ALTER TABLE productvariants ADD PRIMARY KEY (variantId), ADD KEY (buyPrice), ADD KEY (productCode); |
Bien, relançons donc maintenant notre requête après avoir ajouté nos indexes et le résultat devrait ressembler à ça :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
********************** 1. row ********************** id: 1 select_type: SIMPLE table: o type: const possible_keys: PRIMARY,customerNumber key: PRIMARY key_len: 4 ref: const rows: 1 Extra: ********************** 2. row ********************** id: 1 select_type: SIMPLE table: c type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: ********************** 3. row ********************** id: 1 select_type: SIMPLE table: d type: ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 4 Extra: ********************** 4. row ********************** id: 1 select_type: SIMPLE table: p type: eq_ref possible_keys: PRIMARY,productLine key: PRIMARY key_len: 17 ref: classicmodels.d.productCode rows: 1 Extra: ********************** 5. row ********************** id: 1 select_type: SIMPLE table: l type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 52 ref: classicmodels.p.productLine rows: 1 Extra: 5 rows in set (0.00 sec) |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
EXPLAIN SELECT * FROM ( SELECT p.productName, p.productCode, p.buyPrice, l.productLine, p.status, l.status AS lineStatus FROM products p INNER JOIN productlines l ON p.productLine = l.productLine UNION SELECT v.variantName AS productName, v.productCode, p.buyPrice, l.productLine, p.status, l.status AS lineStatus FROM productvariants v INNER JOIN products p ON p.productCode = v.productCode INNER JOIN productlines l ON p.productLine = l.productLine ) products WHERE status = 'Active' AND lineStatus = 'Active' AND buyPrice BETWEEN 30 AND 50 ********************** 1. row ********************** id: 1 select_type: PRIMARY table: <derived2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 219 Extra: Using where ********************** 2. row ********************** id: 2 select_type: DERIVED table: p type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 110 Extra: ********************** 3. row ********************** id: 2 select_type: DERIVED table: l type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 52 ref: classicmodels.p.productLine rows: 1 Extra: ********************** 4. row ********************** id: 3 select_type: UNION table: v type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 109 Extra: ********************** 5. row ********************** id: 3 select_type: UNION table: p type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 17 ref: classicmodels.v.productCode rows: 1 Extra: ********************** 6. row ********************** id: 3 select_type: UNION table: l type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 52 ref: classicmodels.p.productLine rows: 1 Extra: ********************** 7. row ********************** id: NULL select_type: UNION RESULT table: <union2,3> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: 7 rows in set (0.01 sec) |
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 :
1 2 3 4 |
CREATE INDEX idx_buyPrice ON products(buyPrice); CREATE INDEX idx_buyPrice ON productvariants(buyPrice); CREATE INDEX idx_productCode ON productvariants(productCode); CREATE INDEX idx_productLine ON products(productLine); |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
EXPLAIN SELECT * FROM ( SELECT p.productName, p.productCode, p.buyPrice, l.productLine, p.status, l.status as lineStatus FROM products p INNER JOIN productlines AS l ON (p.productLine = l.productLine AND p.status = 'Active' AND l.status = 'Active') WHERE buyPrice BETWEEN 30 AND 50 UNION SELECT v.variantName AS productName, v.productCode, p.buyPrice, l.productLine, p.status, l.status FROM productvariants v INNER JOIN products p ON (p.productCode = v.productCode AND p.status = 'Active') INNER JOIN productlines l ON (p.productLine = l.productLine AND l.status = 'Active') WHERE v.buyPrice BETWEEN 30 AND 50 ) product ********************** 1. row ********************** id: 1 select_type: PRIMARY table: <derived2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 12 Extra: ********************** 2. row ********************** id: 2 select_type: DERIVED table: p type: range possible_keys: idx_buyPrice,idx_productLine key: idx_buyPrice key_len: 8 ref: NULL rows: 23 Extra: Using where ********************** 3. row ********************** id: 2 select_type: DERIVED table: l type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 52 ref: classicmodels.p.productLine rows: 1 Extra: Using where ********************** 4. row ********************** id: 3 select_type: UNION table: v type: range possible_keys: idx_buyPrice,idx_productCode key: idx_buyPrice key_len: 9 ref: NULL rows: 1 Extra: Using where ********************** 5. row ********************** id: 3 select_type: UNION table: p type: eq_ref possible_keys: PRIMARY,idx_productLine key: PRIMARY key_len: 17 ref: classicmodels.v.productCode rows: 1 Extra: Using where ********************** 6. row ********************** id: 3 select_type: UNION table: l type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 52 ref: classicmodels.p.productLine rows: 1 Extra: Using where ********************** 7. row ********************** id: NULL select_type: UNION RESULT table: <union2,3> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: 7 rows in set (0.01 sec) |
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.