Comprendre les requêtes MySQL avec Explain

Vous êtes dans votre nouveau travail d’administrateur de base de données ou d’ingénieur de données et vous venez de vous perdre en essayant de comprendre ce que ces requêtes à la recherche folle sont censées signifier et faire. Pourquoi y a-t-il 5 jointures et pourquoi y a-t-il un ORDER BY utilisé dans une sous-requête avant même que l’une des jointures ne se produise ? Rappelez-vous, vous avez été embauché pour une raison – très probablement, cette raison a également à voir avec de nombreuses requêtes alambiquées qui ont été créées et modifiées au cours de la dernière décennie.

Le mot clé EXPLAIN est utilisé dans diverses bases de données SQL et fournit des informations sur la façon dont votre base de données SQL exécute une requête. Dans MySQL, EXPLAIN peut être utilisé devant une requête commençant par SELECTINSERTDELETEREPLACE, et UPDATE. Pour une requête simple, cela ressemblerait à ce qui suit:

EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas';

Au lieu de la sortie de résultat habituelle, MySQL montrerait alors son plan d’exécution d’instruction en expliquant quels processus se déroulent dans quel ordre lors de l’exécution de l’instruction.

Remarque : Si EXPLAIN ne fonctionne pas pour vous, votre utilisateur de base de données peut ne pas avoir le privilège SELECT pour les tables ou les vues que vous utilisez dans votre instruction.

EXPLAIN est un excellent outil pour remédier rapidement aux requêtes lentes. Bien que cela puisse certainement vous aider, cela n’enlèvera pas la nécessité d’une réflexion structurelle et d’un bon aperçu des modèles de données en place. Souvent, le correctif le plus simple et le conseil le plus rapide consiste à ajouter un index aux colonnes d’une table spécifique en question si elles sont utilisées dans de nombreuses requêtes présentant des problèmes de performances. Attention, cependant, n’utilisez pas trop d’indices car cela pourrait être contre-productif. La lecture de l’index et de la table n’a de sens que si la table contient un nombre important de lignes et que vous n’avez besoin que de quelques points de données. Si vous récupérez un énorme ensemble de résultats à partir d’une table et que vous interrogez souvent différentes colonnes, un index sur chaque colonne n’a pas de sens et entrave les performances plus que cela n’aide. Pour en savoir plus sur les calculs réels d’index vs aucun index, lisez Estimation des performances dans la documentation officielle de MySQL.

Les choses que vous voulez éviter dans la mesure du possible et applicables sont le tri et les calculs dans les requêtes. Si vous pensez ne pas pouvoir éviter les calculs dans vos requêtes: oui, vous le pouvez. Écrivez le jeu de résultats ailleurs et calculez votre point de données en dehors de la requête, cela mettra moins de pression sur la base de données et sera donc globalement meilleur pour votre application. Assurez-vous simplement de documenter les raisons pour lesquelles vous calculez dans votre application plutôt que de produire immédiatement un résultat en SQL. Sinon, le prochain administrateur ou développeur de base de données viendra et aura la glorieuse idée d’utiliser un calcul dans la requête du type « oh regardez, mon prédécesseur ne savait même pas que vous pouviez le faire en SQL! »Certaines équipes de développeurs qui n’ont pas encore eu le problème inévitable de mourir de bases de données peuvent utiliser des calculs dans la requête pour les différences de nombre entre les dates ou des points de données similaires.

La règle générale pour les requêtes SQL est la suivante :

Soyez précis et générez uniquement les résultats dont vous avez besoin.

Examinons une requête légèrement plus compliquée…

SELECT site_options.domain, sites_users.user, site_taxes.monthly_statement_fee, site.name, AVG(price) AS average_product_price FROM sites_orders_products, site_taxes, site, sites_users, site_options WHERE site_options.site_id = site.id AND sites_users.id = site.user_id AND site_taxes.site_id = site.id AND sites_orders_products.site_id = site.id GROUP BY site.id ORDER BY site.date_modified desc LIMIT 5;+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| domain | user | monthly_statement_fee | name | average_product_price |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| www.xxxxxxxxxxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxx | 3.254781 || www.xxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | 9.471022 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxx | 8.646297 || | [email protected] | 0.00 | xxxxxxxxxxxxxxx | 9.042460 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxxx | 6.679182 |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+5 rows in set (0.00 sec)

and et sa sortie EXPLAIN.

+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| 1 | SIMPLE | sites | index | PRIMARY,user_id | PRIMARY | 4 | NULL | 858 | Using temporary; Using filesort || 1 | SIMPLE | sites_options | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_taxes | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_users | eq_ref | PRIMARY | PRIMARY | 4 | service.sites.user_id | 1 | || 1 | SIMPLE | sites_orders_products | ref | site_id | site_id | 4 | service.sites.id | 4153 | |//+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+5 rows in set (0.00 sec)

Les colonnes de la sortie EXPLAIN avec celles qui nécessitent une attention particulière pour identifier les problèmes en gras sont:

• id(id de requête)
• select_type(type d’instruction)
• table (table référencée)
• type (type de jointure)
• possible_keys (quelles clés auraient pu être utilisées)
• key (clé utilisée)
• key_len (longueur de la clé utilisée)
• ref (colonnes comparées à l’index)
• lignes (nombre de lignes recherchées)
• Extra (informations supplémentaires)

Plus le nombre de lignes recherchées est élevé, meilleur est le niveau d’optimisation des indices et de la précision des requêtes afin de maximiser les performances. La colonne supplémentaire affiche les actions possibles sur lesquelles vous pouvez vous concentrer pour améliorer votre requête, le cas échéant.

Afficher les avertissements;

Si la requête que vous avez utilisée avec EXPLAIN ne s’analyse pas correctement, vous pouvez taper SHOW WARNINGS; dans votre éditeur de requêtes MySQL pour afficher des informations sur la dernière instruction exécutée et non diagnostique, c’est-à-dire qu’elle n’affichera pas d’informations pour des instructions comme SHOW FULL PROCESSLIST;. Bien qu’il ne puisse pas donner un plan d’exécution de requête approprié comme le fait EXPLAIN, il peut vous donner des conseils sur les fragments de requête qu’il pourrait traiter. Disons que nous utilisons la requête EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas'; sur une base de données donnée qui n’a pas réellement de table foo. La sortie MySQL serait:

ERROR 1146 (42S02): Table 'db.foo' doesn't exist

Si nous tapons SHOW WARNINGS; la sortie est la suivante:

+-------+------+-------------------------------------+| Level | Code | Message |+-------+------+-------------------------------------+| Error | 1146 | Table 'db.foo' doesn't exist |+-------+------+-------------------------------------+1 row in set (0.00 sec)

Essayons cela avec une erreur de syntaxe délibérée.

EXPLAIN SELECT * FROM foo WHERE name = ///;

Cela génère les avertissements suivants:

> SHOW WARNINGS;+-------+------+---------------------------------------------------------------------+| Level | Code | Message |+-------+------+---------------------------------------------------------------------+| Error | 1064 | You have an error in your SQL syntax; (...) near '///' at line 1 |+-------+------+---------------------------------------------------------------------+

Cette sortie d’avertissement est assez simple et affichée par MySQL comme sortie de résultat immédiatement, mais pour les requêtes plus compliquées qui ne sont pas analysées, il est toujours possible de regarder ce qui se passe dans les fragments de requête qui peuvent être analysés. SHOW WARNINGS; inclut des marqueurs spéciaux qui peuvent fournir des informations utiles, telles que :

• <index_lookup>(query fragment) : une recherche d’index se produirait si la requête avait été correctement analysée
• <if>(condition, expr1, expr2): une condition if se produit dans cette partie spécifique de la requête
• <primary_index_lookup>(query fragment): une recherche d’index se produirait via la clé primaire
• <temporary table>: une table interne serait créée ici pour enregistrer des résultats temporaires, par exemple dans les sous-requêtes avant les jointures

Pour en savoir plus sur ces marqueurs spéciaux, lisez Extended Expliquez le format de sortie dans la documentation officielle de MySQL.

Le correctif à long terme

Il existe plusieurs façons de corriger la cause première des mauvaises performances de la base de données. Le premier point à examiner est le modèle de données, c’est-à-dire comment les données sont-elles structurées et utilisez-vous la bonne base de données ? Pour de nombreux produits, une base de données SQL est très bien. Une chose importante à retenir est de toujours séparer les journaux d’accès de la base de données de production régulière, ce qui n’arrive malheureusement pas dans de nombreuses entreprises. La plupart du temps, dans ces cas, une entreprise a commencé petite, s’est agrandie et utilise essentiellement la même base de données, ce qui signifie qu’elle accède à la même base de données pour la fonctionnalité de journalisation ainsi que pour d’autres transactions. Cela réduit considérablement les performances globales, d’autant plus que l’entreprise grandit. Par conséquent, il est très important de créer un modèle de données qui convient et qui soit durable.

Modèle de données

Le choix d’un modèle de données révélera très probablement également la bonne forme de base de données. À moins que votre produit ne soit très basique, vous aurez probablement plusieurs bases de données pour plusieurs cas d’utilisation – si vous devez afficher des numéros en temps quasi réel pour les journaux d’accès, vous voudrez probablement un entrepôt de données très performant alors que des transactions régulières peuvent se produire via une base de données SQL, et vous pourriez avoir une base de données graphique qui accumule les points de données pertinents des deux bases de données dans un moteur de recommandation.

L’architecture logicielle de l’ensemble du produit est tout aussi importante que la base de données elle-même car une mauvaise conception entraînera des goulots d’étranglement qui iront vers la base de données et ralentiront tout à la fois du côté logiciel et de ce que la base de données peut produire. Vous devrez choisir si les conteneurs conviennent à votre produit, si un monolithe est la meilleure façon de gérer les choses, si vous souhaitez avoir un monolithe central avec plusieurs microservices ciblant d’autres fonctionnalités réparties ailleurs et comment vous accédez, collectez, traitez et stockez les données.

Matériel

Tout aussi important que votre structure générale, votre matériel est un élément clé des performances de votre base de données. Exoscale vous propose diverses options d’instance que vous pouvez utiliser en fonction de votre volume de transaction et de stockage ainsi que du temps de réponse souhaité.

Il est crucial de déterminer les périodes de pointe de votre application et donc de savoir quand omettre les requêtes administratives plus lentes si possible. Les statistiques d’E/S de disque et de réseau doivent également être prises en compte lorsque vous concevez le calendrier des transactions et des analyses de votre base de données.

Résumé

En conclusion, voici les principaux points de performance à long terme résumés:

• créez un modèle de données durable qui répond aux besoins de votre entreprise
• choisissez la bonne forme de base de données
• utilisez une architecture logicielle qui correspond à votre produit
• passez par des itérations régulières de la structure de vos requêtes et utilisez EXPLAINsur les plus complexes, optimisez l’utilisation de la ou des bases de données que vous avez choisies, également en ce qui concerne les mises à jour de la base de données et la façon dont elles pourraient vous affecter

>

• choisissez les instances qui répondent le mieux aux besoins de votre application et de votre base de données en fonction des performances et de la bande passante