Comment identifier et optimiser les requêtes lentes dans MySQL? (Journal de requête lente, performance_schema)

Pour optimiser la requête lente MySQL, vous devez utiliser le journal de requête lent et les performances_schema: 1. Activer le journal de requête lente et définir les seuils pour enregistrer la requête lente; 2. Utilisez Performance_schema pour analyser les détails de l'exécution de la requête, découvrir les goulots d'étranglement des performances et optimiser.

introduction

Lorsque vous êtes piégé dans le labyrinthe d'optimisation des performances des bases de données MySQL, l'identification et l'optimisation des requêtes lentes est sans aucun doute la clé pour craquer le labyrinthe. Aujourd'hui, nous explorerons en profondeur la façon d'utiliser le journal de requête lent de MySQL et Performance_schema, deux outils puissants, pour révéler et résoudre les coupables qui ralentissent votre base de données. Que vous soyez un novice de base de données et un expert expérimenté de base de données, cet article peut vous fournir des conseils pratiques et des informations uniques.

Examen des connaissances de base

Avant de commencer à explorer des stratégies d'optimisation spécifiques, vous pourriez aussi bien passer rapidement en revue les outils que nous utiliserons - Log de requête lente et performance_schema.

Le journal de requête lent est une fonctionnalité de MySQL qui enregistre les requêtes dont le temps d'exécution dépasse un certain seuil. Ce seuil peut être défini par la variable long_query_time . En analysant ces journaux, nous pouvons trouver des requêtes lentes qui affectent les performances de la base de données.

Performance_schema est un système de surveillance des performances dans MySQL. Il fournit des données de performances granulaires plus fines, y compris le temps d'exécution de la requête, le temps d'attente de verrouillage, etc. Grâce à Performance_schema, nous pouvons non seulement voir le temps d'exécution global de la requête, mais aussi acquérir une compréhension approfondie des performances de la requête à chaque étape.

Analyse du concept de base ou de la fonction

Définition et fonction du journal de requête lente

Le journal de requête lent est un fichier journal utilisé par MySQL pour enregistrer les requêtes dont le temps d'exécution dépasse le seuil défini. Sa fonction principale est de nous aider à identifier les requêtes qui ont des impacts négatifs sur les performances de la base de données. En analysant le journal de requête lent, nous pouvons découvrir les requêtes qui doivent être optimisées et prendre des mesures correspondantes.

Par exemple, supposons que nous définissons long_query_time à 1 seconde, puis toutes les requêtes qui ont été exécutées depuis plus de 1 seconde seront enregistrées dans le journal de requête lent.

 - Activer le journal de requête lent ensemble global slow_query_log = 'on';
- Définissez le seuil de requête lent sur 1 seconde set global long_query_time = 1;

Comment fonctionne Performance_schema

Le principe de travail de Performance_schema est de surveiller les opérations internes de MySQL à travers une série d'événements. Ces événements incluent l'exécution de la requête, les E / S de fichiers, l'attente de verrouillage, etc.

Par exemple, nous pouvons afficher le temps d'exécution d'une requête via la requête suivante:

 Sélectionnez Event_name, TIMER_WAIT
De performance_schema.events_statements_current
Où thread_id = (sélectionnez thread_id dans performance_schema.threads où processList_id = connection_id ());

Exemple d'utilisation

Utilisation de base: analyser les journaux de requête lents

La façon la plus élémentaire d'analyser les journaux de requête lents consiste à utiliser l'outil mysqldumpslow , qui peut nous aider à trouver rapidement la requête la plus lente. Par exemple:

 mysqldumpslow -st -t 10 /path/to/slow-query.log

Cette commande répertorie les 10 requêtes qui ont le temps d'exécution le plus long dans le journal de requête lent. Ici -st signifie trier par temps, -t 10 signifie afficher les 10 premiers enregistrements.

Utilisation avancée: utilisez des performances_schema pour optimiser la requête

Performance_schema peut fournir des données de performances plus détaillées. Par exemple, nous pouvons analyser le plan d'exécution d'une requête via la requête suivante:

 Sélectionnez * dans Performance_schema.events_statements_history_long
Où sql_text comme '% select%'
Ordre par TIMER_WAIT DESC
Limite 1;

Cette requête renvoie l'instruction SELECT avec le temps d'exécution récent le plus long et affiche son plan d'exécution et d'autres données de performance. Grâce à ces données, nous pouvons avoir une compréhension plus approfondie des goulots d'étranglement des performances de la requête et prendre des mesures d'optimisation correspondantes.

Erreurs courantes et conseils de débogage

Lorsque vous utilisez des journaux de requête lents et des performances_schema, vous pouvez rencontrer des erreurs et des malentendus courants. Par exemple:

• J'ai oublié d'activer le journal de requête lent : si le journal de requête lent n'est pas activé, nous ne pouvons pas enregistrer la requête lente. Il peut être activé par SET GLOBAL slow_query_log = 'ON'; ;.
• Réglez le seuil trop élevé : si long_query_time est réglé trop haut, certaines requêtes qui doivent être optimisées peuvent être manquées. Il est recommandé d'ajuster ce seuil en fonction des conditions réelles.
• Ignorer les frais généraux de Performance_schema : Performance_schema apportera certaines frais généraux de performance, en particulier dans des environnements à haute charge. La surcharge peut être contrôlée via la variable performance_schema_consumer_global_instrumentation .

Optimisation des performances et meilleures pratiques

Dans les applications pratiques, l'optimisation de la requête lente nécessite de combiner plusieurs méthodes et outils. Voici quelques stratégies d'optimisation et meilleures pratiques:

• Optimisation d'index : En analysant les journaux de requête lents et les données Performance_schema, recherchez des requêtes qui n'ont pas utilisé d'index ou sont utilisées de manière inappropriée, et ajoutez ou ajustez l'index.
• Recriture de requête : certaines requêtes peuvent améliorer les performances en réécrivant. Par exemple, réécrivez des sous-requêtes complexes pour rejoindre ou utiliser des tables temporaires pour réduire les calculs en double.
• Optimisation du cache : L'utilisation rationnelle du cache de requête de MySQL et du cache au niveau de l'application peut améliorer considérablement les performances de la requête.
• Mises à niveau matérielle : Dans certains cas, les goulots d'étranglement matériels peuvent être la principale cause de requêtes lentes. Les mises à niveau matérielle appropriées, telles que l'ajout de la mémoire ou l'utilisation de SSD, peuvent considérablement améliorer les performances de la base de données.

Pendant le processus d'optimisation, les points suivants doivent être prêts à l'attention:

• La surveillance et le réglage sont un processus continu : l'optimisation des performances de la base de données n'est pas un travail unique et nécessite une surveillance et un réglage continus.
• Test et vérification : Avant d'appliquer tout schéma d'optimisation dans un environnement de production, il doit être entièrement testé et vérifié dans l'environnement de test pour s'assurer qu'aucun nouveau problème n'est introduit.
• Équilibrer les performances et la consommation de ressources : Bien que la poursuite des performances, la consommation de ressources doit également être prise en compte. Une optimisation excessive peut entraîner un gaspillage de ressources et affecter les performances globales du système.

Grâce aux méthodes et pratiques ci-dessus, nous pouvons identifier et optimiser efficacement les requêtes lentes dans MySQL, améliorant ainsi les performances globales de la base de données. J'espère que cet article pourra vous fournir un fort soutien et une inspiration sur la route de l'optimisation des performances de MySQL.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!