La première étape consiste à hiérarchiser les requêtes à optimiser :

1. Identifier les questions sur la base de données qui nécessitent le meilleur délai de réponse
2. Identifier les questions sur les bases de données qui sont fréquemment posées

Ces deux catégories doivent être optimisées lors de la conception du schéma de base de données.

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 pour MySQL, toutes les activités des utilisateurs ont lieu au sein d'une transaction. Le mode autocommit est activé par défaut, ce qui signifie que chaque instruction SQL constitue une transaction unique. Pour effectuer une transaction à plusieurs instructions lorsque le commit automatique est activé, commencez la transaction explicitement par START_TRANSACTION ou START_TRANSACTION, et terminez-la par START_TRANSACTION ou START_TRANSACTION. Si le mode autocommit est désactivé, une transaction est toujours ouverte dans la session jusqu'à ce qu'un COMMIT ou COMMIT la termine et en démarre une nouvelle. 

Il est recommandé d'utiliser des transactions aussi courtes que possible. En effet, les longues transactions présentent plusieurs inconvénients, comme expliqué plus en détail dans cet article.

1. Des conflits de verrouillage prolongés, qui ralentissent les requêtes et peuvent entraîner des échecs de requête

• 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

2. Dégradation des performances du serveur en raison d'une grande quantité de journaux d'annulation

• Grâce au 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 par défaut des lectures reproductibles, les journaux d'annulation ne sont supprimés définitivement que lorsque les transactions qui avaient déjà commencé sont terminées. 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 de la fonction de ligne de commande SHOW ENGINE INNODB STATUS.
• Lorsque la liste de l'historique dépasse les 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.

3. Augmentation de l'utilisation du disque

• Augmentation des journaux d'annulation stockés sur le disque, soit dans l'espace de table système, soit dans l'espace de table d'annulation

4. Délai d'arrêt prolongé

• Lors d'un arrêt normal, les transactions en cours sont annulées. Le délai du rollback est souvent plus long que le temps nécessaire pour arriver à l'essentiel. Par conséquent, l'arrêt du serveur peut prendre longtemps pour le rollback.

5. Temps de récupération prolongé en cas de plantage

• Lors de la récupération après plantage, InnoDB répète les transactions du dernier point de contrôle et débloque les transactions sans engagement. Avec une longue transaction, l'étape correspondante prendrait plus de temps.

• Requêtes SELECT
• Elles ne contiennent 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 de ligne unique
• Diviser et limiter la durée d'exécution des lots à quelques secondes

• Envisager de séparer les requêtes SELECT
• S'il existe une logique d'application entre les requêtes de 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

Les requêtes peuvent être capturées côté application ou côté base de données.

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 le temps de réponse pour certaines fonctionnalités. Cette méthode permet également d'obtenir facilement la durée d'exécution totale des transactions comportant plusieurs instructions.

De plus, le temps de réponse aux requêtes mesuré à partir de la journalisation côté application est une mesure de bout en bout incluant le temps réseau. Elle complète le temps d'exécution des requêtes consigné dans 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.

Insights sur les requêtes MySQL Cloud SQL

L'outil Insights sur les requêtes de Cloud SQL permet de capturer, de surveiller et de diagnostiquer les requêtes.

L'outil Insights sur les requêtes permet de trouver facilement les requêtes les plus fréquentes en fonction de la durée et de la fréquence d'exécution.

L'outil propose des options de filtrage telles que la période, la base de données, le compte utilisateur et l'adresse du client. Il comporte des graphiques illustrant l'utilisation du processeur, ainsi que la répartition des E/S et des temps d'attente de verrouillage. La table "Requêtes et tags les plus fréquents" liste les requêtes les plus fréquentes par temps d'exécution, les requêtes étant normalisées. Outre la durée d'exécution, cette métrique inclut des statistiques sur les "lignes moyennes analysées" et les "lignes moyennes renvoyées" qui fournissent des insights sur l'efficacité des requêtes.

Veuillez consulter la documentation pour découvrir toutes les fonctionnalités proposées et comment les activer.

Utiliser performance_schema

Sur Cloud SQL pour MySQL, la fonctionnalité performance_schema est activée par défaut pour les versions MySQL 8.0.26 et ultérieures avec au moins 15 Go de mémoire. Son activation ou sa désactivation nécessite le redémarrage de l'instance.

Lorsque performance_schema est activé, les instruments d'instruction 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_examined_avg : concerne 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_examined_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_disk_tables : pour savoir si tmp_disk_tables/tmp_disk_tables est suffisant
• Quelles requêtes utilisent le tri de fichiers ?
• rows_sorted/rows_sorted, rows_sorted/rows_sorted : pour identifier les requêtes dont le tri est important et qui peuvent utiliser une valeur rows_sorted plus importante

Si vous utilisez MySQL Workbench, les rapports sur le schéma des performances sont basés sur la vue système. Le rapport comporte une section "Instructions SQL à coût élevé" qui fournit des informations sur les performances des requêtes.

Utiliser un journal lent et des outils

Le journal lent capture toutes les requêtes qui s'exécutent au-delà de la valeur long_query_time. Il consigne également le temps d'exécution des requêtes, le temps de verrouillage, ainsi que les lignes de données examinées et envoyées. Les statistiques d'exécution supplémentaires en font un candidat idéal pour analyser les requêtes de base de données plutôt que d'utiliser le journal général.

Il est recommandé d'activer les journaux lents. Normalement, la valeur long_query_time doit rester à un seuil raisonnable pour capturer les requêtes que vous avez l'intention d'examiner et d'optimiser.

log_output=FILE

slow_query_log=ON

long_query_time=2

De temps à autre, il peut être judicieux de définir la valeur long_query_time sur 0 afin de capturer toutes les requêtes pendant une courte période et d'obtenir un aperçu du volume de requêtes et des performances.

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 de SolarWinds (anciennement VividCortex), etc.

Après avoir capturé les requêtes dans les transactions, l'étape suivante consiste à les optimiser.

La commande EXPLAIN fournit le plan d'exécution de la requête et, à partir de la version 8.0.18, la commande EXPLAIN ANALYZE exécuterait une instruction et générerait une sortie EXPLAIN ainsi que la durée 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" indique le nombre de lignes à lire
• Les ES sont la partie la plus chronophage. Si une requête doit lire une grande quantité de données, elle risque d'être lente. Pour vous faire une idée générale, multipliez les "lignes" entre les tables jointes. Dans l'exemple ci-dessus, cela correspond à 858 * 23523. Lire 23 523 lignes de t2 pour chacune des 858 lignes de t1 ne semble pas optimal. L'optimisation consisterait donc à réduire l'accès aux données de t2 pour 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 du tableau, le champ "Extra" indique alors "Utilise l'index".
• Le type "plage" signifie qu'un index est utilisé, mais aussi qu'une condition de plage est 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 de l'index est de 1:m au lieu de 1:1. Plusieurs lignes seront lues 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 réel utilisé.
• La sélection de l'index à utiliser est basée sur 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 la clé en octets.
• Avec un indice multicolonne, key_len suggère la partie d'index utilisée. Par exemple, si un index contient (col1, col2, col3) et que la condition de la requête est "col1 = n et col2 comme '%string%'", seul col1 sera utilisé pour le filtrage des index. Si la requête peut être remplacée par "col1 = n et col2 comme 'string%'", alors les deux (col1, col2) seront utilisés pour le filtrage des index. Cette modification mineure pourrait faire une différence spectaculaire au niveau des 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, et potentiellement des fichiers de disque 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

Les variables d'état de session peuvent être utilisées pour obtenir des détails sur l'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 format d'accès aux données et le nombre de lignes. Created_* indiquerait si une table temporaire et/ou une table temporaire sur le disque sont créées. Sort_* afficherait 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 la durée d'exécution de la requête par étape d'exécution, ce qui peut également fournir des informations utiles.

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. 

• Ajoutez ou mettez à jour la définition d'index
• Pour un meilleur filtrage, réduisez l'accès aux données
• Pour le tri, évitez le tri des fichiers
• Mettez à jour les statistiques d'index si cette option est désactivée
• ANALYZE TABLE <tbl>;
• Revérifier ensuite le résultat du plan EXPLAIN
• Utilisez Index Hint
• Pour suggérer ou forcer l'utilisation d'un certain index pour filtrer, joindre ou trier par/regrouper par
• Utilisez STRAIGHT_JOIN pour définir l'ordre de jointure des tables
• Utiliser les suggestions de l'optimiseur

Pour optimiser la configuration du serveur pour certaines requêtes, il est fortement recommandé d'utiliser les variables au niveau de la session au lieu de modifier la valeur globale qui affecte toutes les sessions.

Les valeurs de session fréquemment utilisées sont les suivantes :

En résumé, nous avons abordé trois aspects concernant l'optimisation des requêtes :

• Prenez des décisions éclairées lors de la rédaction des requêtes. Ces décisions sont le facteur moteur des performances des requêtes, du débit global du serveur et des performances du serveur.
• Suivez les données d'exécution de la requête 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 intérêts de l’entreprise et refléter les activités commerciales.
• Enfin, plusieurs outils vous aident à comprendre le plan d'exécution des requêtes, les coûts associés aux différentes étapes et les moyens d'optimiser les requêtes.