Comment activer le journal des requêtes lentes dans MySQL

Dans l'utilisation quotidienne de la base de données MySQL, afin d'améliorer la vitesse et l'efficacité des requêtes, nous utilisons généralement des index et des optimiseurs. Mais parfois, nous avons également besoin de connaître l'exécution spécifique de l'instruction de requête afin de mieux trouver le problème et d'optimiser la base de données avec plus de précision. Pour le moment, vous devez utiliser la fonction Slow Query Log de MySQL.

Le journal des requêtes lentes peut enregistrer toutes les instructions SQL dont le temps d'exécution dépasse un certain seuil afin que nous puissions dépanner et optimiser. Cet article explique comment activer le journal des requêtes lentes dans la base de données MySQL et explique en détail les paramètres pertinents et les méthodes d'optimisation du journal des requêtes lentes.

1. Activez le journal des requêtes lentes

1. Modifiez le fichier de configuration

Ajoutez la configuration suivante dans le fichier de configuration MySQL my.cnf ou my.ini :

slow_query_log = 1 #开启慢查询日志
slow_query_log_file = /var/log/mysql/mysql-slow.log #日志文件路径
long_query_time = 3 #超过3s即为慢查询
log_queries_not_using_indexes = 1 #记录未使用索引的查询

Slow_query_log = 1 signifie activer le journal des requêtes lentes , et slow_query_log_file est spécifié. Le chemin et le nom du fichier journal des requêtes lentes. long_query_time indique le nombre de secondes que dépasse le temps d'exécution de la requête avant qu'elle ne soit considérée comme une requête lente.

2. Utilisez la commande SET pour modifier dynamiquement la configuration

En plus de modifier le fichier de configuration, nous pouvons également modifier dynamiquement les paramètres via la commande SET. Par exemple, nous pouvons activer le journal des requêtes lentes dans la ligne de commande MySQL via l'instruction suivante :

mysql> SET global slow_query_log = 1;
mysql> SET global slow_query_log_file = '/var/log/mysql/mysql-slow.log';
mysql> SET global long_query_time = 3;
mysql> SET global log_queries_not_using_indexes = 1;

Dans le code ci-dessus, SET global signifie qu'il prendra effet globalement, c'est-à-dire que toutes les connexions utiliseront ces paramètres.

Bien sûr, si vous souhaitez uniquement définir les paramètres d'une connexion locale, vous pouvez utiliser la commande SET SESSION. Par exemple :

mysql> SET SESSION slow_query_log = 1;
mysql> SET SESSION slow_query_log_file = '/var/log/mysql/mysql-slow.log';
mysql> SET SESSION long_query_time = 3;
mysql> SET SESSION log_queries_not_using_indexes = 1;

2. Optimisez les paramètres de configuration du journal des requêtes lentes

1. slow_query_log_file

Avant d'activer le journal des requêtes lentes, nous devons nous assurer que le répertoire dans lequel se trouve le fichier journal spécifié existe déjà et dispose d'une autorisation en écriture. . Si le répertoire n'existe pas, vous devez d'abord le créer.

Nous pouvons utiliser la commande suivante pour afficher le répertoire de travail actuel du processus MySQL :

mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

Comme vous pouvez le voir dans l'exemple ci-dessus, le répertoire de travail du processus MySQL est /var/lib/mysql/, et il y a un répertoire de journaux dans ce répertoire slow.log. Par conséquent, si nous voulons écrire le journal des requêtes lentes dans ce fichier, il nous suffit d'ajouter la configuration suivante à my.cnf :

slow_query_log_file = /var/lib/mysql/logs/slow.log

2. long_query_time

La valeur par défaut de long_query_time est de 10 secondes, c'est-à-dire uniquement le temps d'exécution Les instructions de requête qui prennent plus de 10 secondes seront enregistrées dans le journal des requêtes lentes. Dans des circonstances normales, ce délai peut être un peu long. Nous pouvons le définir sur une durée plus courte en fonction des conditions réelles pour découvrir plus rapidement les requêtes lentes.

La raison de la longue exécution des instructions de requête est souvent que l'instruction elle-même est inefficace et doit généralement être optimisée. En utilisation réelle, nous pouvons progressivement réduire le temps long_query_time pour découvrir les instructions moins efficaces, les optimiser et améliorer l'efficacité des requêtes.

3. log_queries_not_using_indexes

Si le paramètre log_queries_not_using_indexes est défini sur 1, alors toutes les requêtes exécutées sans utiliser d'index seront enregistrées. Cela peut nous aider à identifier les problèmes potentiels et à les optimiser en temps opportun. Cependant, cela peut laisser des faux positifs puisque les requêtes sans index sont normales dans certains cas.

Vous devez être prudent lorsque vous utilisez le paramètre log_queries_not_using_indexes. Il est recommandé de ne pas le définir comme variable globale. Vous pouvez choisir de le définir dynamiquement si nécessaire. Car si ce paramètre est toujours activé, cela peut avoir un impact négatif sur les performances de MySQL, entraînant une diminution de l'efficacité globale de fonctionnement de la base de données.

3. Afficher et analyser le journal des requêtes lentes

Lorsque nous activons le journal des requêtes lentes, MySQL commence à enregistrer toutes les instructions de requête dont le temps d'exécution dépasse le seuil. Nous pouvons utiliser la commande suivante pour afficher le journal des requêtes lentes :

mysql> SHOW VARIABLES LIKE 'slow_query_log_file';
+---------------------+------------------------+
| Variable_name       | Value                  |
+---------------------+------------------------+
| slow_query_log_file | /var/lib/mysql/slow.log |
+---------------------+------------------------+
1 row in set (0.00 sec)

Comme le montre la sortie ci-dessus, le chemin actuel du fichier journal des requêtes lentes est /var/lib/mysql/slow.log.

Si vous souhaitez afficher le contenu spécifique du journal des requêtes lentes, vous pouvez utiliser la commande suivante :

mysql> mysqldumpslow -s t /var/lib/mysql/slow.log

Parmi elles, -s signifie trier par heure et -t signifie afficher uniquement les 10 premiers enregistrements. Si vous souhaitez afficher tous les enregistrements de requête, vous pouvez supprimer le paramètre -t.

4. Optimiser les journaux de requêtes lentes

L'activation des journaux de requêtes lentes est un très bon moyen de nous aider à découvrir et à optimiser les problèmes de performances. Cependant, vous devez également faire attention aux points suivants en utilisation réelle :

1. La taille du fichier journal des requêtes lentes continuera d'augmenter, il doit donc être nettoyé régulièrement. Il est recommandé de définir max_slowlog_size sur une valeur appropriée dans my.cnf pour éviter de manquer d'espace disque.
2. La journalisation des requêtes lentes aura un impact négatif sur les performances de MySQL, il est donc recommandé de n'activer la journalisation nécessaire que dans un environnement de production. Normalement, définissez le niveau de journalisation et enregistrez uniquement les informations clés.
3. Vous pouvez utiliser des outils pour analyser les journaux de requêtes lentes, tels que pt-query-digest, etc. Ces outils peuvent nous aider à mieux découvrir les causes des requêtes lentes et à optimiser les plans d'exécution.

En bref, c'est une bonne habitude d'activer le journal des requêtes lentes MySQL. Dans un environnement de production, nous devons configurer soigneusement les paramètres et utiliser les outils associés pour garantir les performances et la stabilité de la base de données.

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!