Optimiser les performances MySQL : optimisation des requêtes
Les bases de données jouent un rôle essentiel dans les performances de toute application. La base de données MySQL ne fait pas exception. Il est donc important de bien comprendre les nombreuses façons dont l'optimisation, la conception et la configuration de la base de données peuvent vous aider à améliorer les performances de votre application. Vous trouverez ci-dessous quelques façons d'optimiser MySQL pour obtenir des performances optimales.
Articles associés
Présentation
Optimiser les requêtes
L'optimisation des performances de la base de données débute à partir de l'application. La manière dont l'application traduit les exigences métier en requêtes de base de données fait toute la différence en termes de complexité et d'efficacité de l'application. L'évaluation réelle des performances indique dans quelle mesure chaque instance de base de données contribue aux besoins de l'entreprise.
Conception de schémas
La définition des entités et des relations dans une base de données relationnelle détermine le degré de simplicité ou de complexité d'une réponse à une requête de base de données. De plus, la définition de la clé primaire et des index secondaires joue un rôle important.
Configuration du serveur
La configuration du serveur permet d'optimiser et de maximiser l'utilisation des ressources système. Les ressources système correspondent aux cœurs de processeurs (CPU), à la mémoire sur la machine physique ou la machine virtuelle (VM), au système de stockage sous-jacent et au réseau.
Réglages dynamiques du serveur
Surveillance, optimisation et réglage continus des performances pour adapter les charges de travail des bases de données à la dynamique mondiale réelle.
Cet article porte sur l'optimisation des requêtes. Les autres sujets seront abordés dans les articles suivants.
Nous commençons souvent par optimiser les requêtes en examinant la requête de base de données. Il est préférable de commencer par évaluer l'efficacité avec laquelle les exigences métier ont été traduites en requête de base de données. L'interprétation et le traitement d'un besoin de l'entreprise en requête déterminent le niveau de coût faible ou élevé.
Concevoir des requêtes
La première étape consiste à hiérarchiser les requêtes à optimiser :
- Identifier les questions sur la base de données qui nécessitent le meilleur délai de réponse
- Identifier les questions fréquentes sur la base de données
Ces deux catégories doivent être optimisées lors de la conception du schéma de base de données.
Effectuer des transactions courtes
Une transaction est une unité logique dans laquelle toutes les instructions contenues peuvent être entièrement validées ou annulées. La transaction est une fonctionnalité qui assure l'atomicité, la cohérence, l'isolement et la durabilité (ACID) pour MySQL.
Dans InnoDB, le moteur de stockage de MySQL, toutes les activités de l'utilisateur se produisent au sein d'une transaction. Par défaut, le mode autocommit est activé, ce qui signifie que chaque instruction SQL forme une seule transaction à part entière. Pour effectuer une transaction à plusieurs instructions lorsque le mode autocommit est activé, démarrez la transaction explicitement avec START_TRANSACTION ou BEGIN, et terminez-la avec COMMIT ou ROLLBACK. Si le mode autocommit est désactivé, une transaction est toujours ouverte dans la session jusqu'à ce qu'une commande COMMIT ou ROLLBACK y mette fin et en démarre une nouvelle.
Nous vous recommandons de choisir des transactions aussi courtes que possible. En effet, les transactions longues présentent plusieurs inconvénients, comme indiqué plus loin dans cet article.
Inconvénients des transactions longues
- Les conflits liés aux verrous prolongés entraînent des requêtes plus lentes et des échecs de requête potentiels.
- Le verrouillage au niveau des lignes InnoDB est conservé pendant toute la durée de la transaction
- Cela peut potentiellement augmenter les temps de verrouillage, les délais avant expiration des verrous et les blocages qui ralentissent les requêtes ou les font complètement échouer
- Dégradation des performances du serveur en raison d'une grande quantité de journaux d'annulation
- En raison du contrôle de simultanéité multiversion InnoDB (MVCC), les anciennes versions des lignes modifiées sont stockées dans des journaux d'annulation pour une lecture et un rollback cohérents. Avec le niveau d'isolation de lecture reproductible par défaut, les journaux d'annulation ne sont pas supprimés définitivement tant que les transactions n'ont pas commencé avant sa fin. Par conséquent, une transaction de longue durée accumule des journaux d'annulation. Cela peut être observé et surveillé via la liste d'historique dans la fonction de ligne de commande SHOW ENGINE INNODB STATUS.
- Lorsque la liste de l'historique dépasse des millions, cela peut avoir un impact négatif sur les performances du serveur en raison de la contention du mutex sur les segments de rollback, de l'augmentation du volume pour la lecture des journaux d'annulation et de l'augmentation du temps de balayage à travers la liste associée de journaux d'annulation. Cela implique également davantage de travail pour la suppression définitive des threads
- Augmentation de l'utilisation du disque
- Augmentation du nombre de journaux d'annulation stockés sur disque, soit dans l'espace de table système, soit dans l'espace de table d'annulation
- Temps d'arrêt prolongé
- Lors d'un arrêt normal, les transactions en cours sont annulées. Le temps de rollback est souvent plus long que le temps nécessaire pour atteindre le point. L'arrêt du serveur peut donc prendre un certain temps.
- Temps de reprise après plantage prolongé
- Lors de la reprise après plantage, InnoDB répète les transactions à partir du dernier point de contrôle et annule le déploiement des transactions non validées. Une transaction longue rallonge l'étape correspondante.
Remarque sur les transactions à requête unique
- Requêtes SELECT
- Elles ne permettent pas de verrous de ligne
- Elles peuvent entraîner l'accumulation de journaux d'annulation
- Voir la section ci-dessous sur l'optimisation des requêtes
- Requêtes UPDATE/INSERT/DELETE
- Les requêtes par lot sont plus performantes que de nombreuses modifications apportées sur une seule ligne
- Diviser et limiter le temps d'exécution par lot à quelques secondes
Remarque sur les transactions comportant plusieurs instructions
- Envisagez de séparer les requêtes SELECT.
- S'il existe une logique d'application entre les requêtes de la base de données, envisagez de scinder la transaction
- Estimez le nombre de verrous de ligne pouvant être maintenus pour chaque instruction
- Évaluez l'ordre d'exécution pour minimiser le verrouillage des lignes
- Recherchez des opportunités permettant de réduire la taille des transactions
Capturer les requêtes
Les requêtes peuvent être capturées côté application ou côté base de données.
Côté application
Il est recommandé de consigner les requêtes de base de données et leur temps d'exécution. La journalisation côté application permet d'évaluer facilement l'efficacité des requêtes dans leur contexte commercial. Par exemple, les utilisateurs peuvent consigner le temps de réponse de chaque requête ou de certaines fonctionnalités. C'est également un moyen simple d'obtenir le temps total d'exécution des transactions comportant plusieurs instructions.
De plus, le délai de réponse de la requête mesuré à partir de la journalisation côté application est une mesure de bout en bout, incluant le délai du réseau. Il complète le temps d'exécution des requêtes enregistré à partir de la base de données et permet d'identifier facilement si le problème est lié au réseau ou à la base de données.
Côté base de données
Insights sur les requêtes MySQL Cloud SQL
L'outil d'insights sur les requêtes Cloud SQL permet d'enregistrer, de surveiller et de diagnostiquer des requêtes.
Les insights sur les requêtes permettent de trouver facilement les requêtes les plus fréquentes en fonction du temps et de la fréquence d'exécution.
L'outil dispose d'options de filtrage telles que la période, la base de données, le compte utilisateur et l'adresse client. Il comporte des graphiques illustrant l'utilisation du processeur, et une répartition par E/S et temps de verrouillage. Le tableau "Requêtes et tags les plus fréquents" liste les requêtes les plus fréquentes par temps d'exécution en normalisant les requêtes. En plus du temps d'exécution, il inclut des statistiques sur la "moyenne des lignes analysées" et la "moyenne des lignes renvoyées", ce qui donne des insights sur l'efficacité des requêtes.
Veuillez consulter la documentation pour découvrir toutes ses fonctionnalités et savoir comment l'activer.
Utiliser performance_schema
Sur Cloud SQL pour MySQL, la fonctionnalité performance_schema est activée par défaut pour MySQL 8.0.26 et les versions ultérieures avec plus de 15 Go de mémoire. Pour l'activer ou la désactiver, vous devez redémarrer l'instance.
Lorsque la fonction performance_schema est activée, les instruments d'instructions de requête sont activés par défaut. La table sys.statement_analysis fournit des statistiques agrégées pour les requêtes normalisées. Elle répond à des questions telles que:
- Quelles requêtes effectuent une analyse complète de la table ?
- full_scan/exec_count : pour savoir si les requêtes effectuent fréquemment une analyse complète de la table, ce qui s'avère souvent inefficace
- Quelles requêtes sont lentes ?
- avg_latency : temps d'exécution moyen des requêtes
- Quelles requêtes sont inefficaces ?
- rows_examined_avg/rows_sent_avg : pour les requêtes de lecture. Le ratio idéal est 1. Plus le ratio est élevé, plus la requête est inefficace.
- rows_examined_avg/rows_affected_avg : pour les requêtes d'écriture Le ratio idéal est 1. Plus le ratio est élevé, plus la requête est inefficace.
- Quelles requêtes utilisent des tables temporaires et doivent être converties en tables temporaires sur disque ?
- tmp_disk_tables/tmp_tables : pour savoir si tmp_table_size/max_heap_table_size est suffisant
- Quelles requêtes utilisent le tri de fichiers ?
- rows_sorted/exec_count, sort_merge_passes/exec_count : pour identifier les requêtes dont le tri est important et qui peuvent utiliser une valeur sort_buffer_size plus importante.
Si vous utilisez MySQL Workbench, vous disposez de rapports sur les schémas de performances basés sur la vue sys. Le rapport comporte une section sur les instructions SQL coûteuses qui fournit des insights sur les performances des requêtes.
Utiliser un journal lent et des outils
Le journal lent capture toutes les requêtes s'exécutant au-delà de long_query_time. Il consigne également le temps d'exécution des requêtes, le temps de verrouillage, les lignes de données analysées et les lignes de données envoyées. Avec les statistiques d'exécution supplémentaires, il est préférable d'analyser les requêtes de base de données plutôt que d'utiliser le journal général.
Nous vous recommandons d'activer le journal lent. Normalement, la valeur de long_query_time doit rester à un seuil raisonnable pour capturer les requêtes que vous souhaitez examiner et optimiser.
log_output=FICHIER
slow_query_log=ACTIVÉ
long_query_time=2
De temps à autre, il est recommandé de définir long_query_time=0 pour capturer toutes les requêtes pendant une courte période, et obtenir un aperçu du volume et des performances des requêtes.
Des outils, tels que mysqldumpslow et pt-query-digest, extraient les signatures de requête et génèrent un rapport pour afficher les statistiques des requêtes.
D'autres outils de surveillance tiers génèrent des rapports sur les statistiques de requête, par exemple : Surveillance et gestion de Percona, Contrôle des performances de base de données SolarWinds (anciennement VividCortex), etc.
Optimiser les requêtes
Après avoir capturé les requêtes dans les transactions, l'étape suivante consiste à les optimiser.
EXPLAIN – Éléments à rechercher
La commande EXPLAIN fournit le plan d'exécution de la requête. À partir de la version 8.0.18, la commande EXPLAIN ANALYZE exécute une instruction et génère une sortie EXPLAIN ainsi que le calendrier de l'exécution.
L'insight sur les requêtes MySQL permet d'accéder facilement au plan EXPLAIN.
Que recherchons-nous dans le résultat ?
- Le champ "rows" (lignes) indique le nombre de lignes à lire
- L'E/S est la partie la plus chronophage. Si une requête doit lire une grande quantité de données, elle sera probablement lente. Pour vous faire une idée, multipliez les "lignes" entre les tables jointes. Dans l'exemple ci-dessus, vous obtenez 858 * 23523. La lecture de 23 523 lignes de t2 pour chacune des 858 lignes de t1 ne semble pas optimale Par conséquent, l'optimisation consisterait à réduire la quantité d'accès aux données à partir de t2 à chaque itération.
- Le champ "type" décrit le type de jointure des tables
- Le type "index" signifie que l'index est analysé. Si l'index satisfait toutes les données requises de la table, le champ "Extra" indique "Utilise l'index".
- Le type "plage" signifie qu'un index est utilisé, mais qu'une condition de plage est également fournie pour limiter l'analyse des données.
- Pour les tables suivantes dans l'ordre de jointure, le type "eq_ref" signifie qu'une ligne est lue à partir de cette table pour chaque combinaison de lignes des tables précédentes, ce qui est le plus efficace.
- Le type "ref" signifie que la correspondance d'index est de 1:m au lieu de 1:1. Plus d'une ligne sera lue dans cette table pour chaque combinaison de lignes des tables précédentes.
- Le type à éviter est "TOUT". Cela signifie qu'une analyse complète de la table est effectuée pour chaque combinaison de lignes des tables précédentes.
- Le champ "key" indique l'index utilisé.
- Le choix de l'index à utiliser dépend de la cardinalité de l'index, qui peut être obsolète. Il est donc important de vérifier que l'index le plus sélectif est utilisé.
- Le champ "key_len" indique la longueur de clé en octets.
- Avec un index à plusieurs colonnes, key_len suggère la partie de l'index utilisée. Par exemple, si un index possède (col1, col2, col3) et que la condition de la requête est "col1 = n et col2 comme '%string%'", alors seul col1 sera utilisé pour le filtrage des index. Si la requête peut être remplacée par "col1 = n et col2 comme 'chaîne%'", alors les deux (col1, col2) seront utilisées pour le filtrage d'index. Cette légère modification peut faire une énorme différence dans les performances des requêtes.
- Le champ "Extra" contient des informations supplémentaires sur le plan de requête
- L'option "Utiliser temporairement" signifie qu'une table temporaire interne est créée et peut générer une table temporaire sur disque.
- Le champ "Utiliser un tri de fichiers" signifie que le tri n'a pu exploiter aucun index et nécessite un tampon de tri, ainsi que des fichiers de disque potentiellement temporaires.
- "Utilise l'index" signifie que toutes les données requises de cette table sont contenues dans l'index ; pas besoin de lire des lignes de données
Profilage de requête
Les variables d'état de session peuvent être utilisées pour obtenir les détails d'exécution de la requête.
Commencez par effacer les variables de session, puis exécutez la requête et examinez les compteurs. Par exemple, l'état "Handler_*" indique le schéma d'accès aux données et le nombre de lignes. La valeur "Created_*" s'affiche si une table temporaire et/ou une table temporaire sur disque sont créées. L'élément "Sort_*" affiche le nombre de tentatives de fusion de tri et le nombre de lignes triées. Vous trouverez plus d'informations sur les variables de session dans la documentation.
L'instruction SHOW PROFILE indique le temps d'exécution de la requête par étape d'exécution, ce qui peut également être utile.
Optimiser le plan d'exécution des requêtes
Une fois que vous avez compris le plan d'exécution des requêtes, vous pouvez le modifier et l'optimiser de plusieurs manières.
- Ajouter ou mettre à jour une définition d'index
- Améliorer l'efficacité du filtrage en limitant l'accès aux données
- Pour le tri, évitez le tri de fichiers
- Mettre à jour les statistiques d'index si cette option est désactivée
- ANALYZE TABLE <tbl>;
- Revérifier ensuite le résultat du plan EXPLAIN
- Utiliser l'indice d'index
- Pour suggérer ou forcer l'utilisation d'un index spécifique pour le filtrage, joindre ou trier par / regrouper par
- Utiliser STRAIGHT_JOIN pour définir l'ordre de jointure des tables
- Utiliser les suggestions de l'optimiseur
Optimiser l'exécution pour la session
Pour optimiser la configuration du serveur pour certaines requêtes, nous vous recommandons vivement d'utiliser les variables au niveau de la session plutôt que de modifier la valeur globale qui affecte toutes les sessions.
Les valeurs de session fréquemment utilisées sont les suivantes :
Résumé
En résumé, nous avons abordé trois aspects concernant l'optimisation des requêtes :
- Prenez des décisions éclairées lorsque vous composez les requêtes. Ces décisions constituent le facteur déterminant pour les performances des requêtes, le débit global du serveur et les performances du serveur.
- Suivez les données d'exécution des requêtes côté application et côté base de données. La journalisation côté application est importante. Elle peut être configurée en fonction des centres d'intérêt de l'entreprise et refléter les activités commerciales.
- Enfin, plusieurs outils peuvent vous aider à comprendre le plan d'exécution des requêtes, les coûts associés aux différentes étapes et les façons d'optimiser les requêtes.
Produits et services associés
Google Cloud propose une base de données MySQL gérée conçue pour répondre aux besoins de votre entreprise, de la suppression de votre centre de données sur site à l'exécution d'applications SaaS, en passant par la migration de systèmes d'entreprise principaux.
