Comment analyser les raisons pour lesquelles les requêtes MySQL sont lentes

La requête MySQL est lente, ce qui est un problème rencontré par de nombreux utilisateurs MySQL. Une requête lente affecte non seulement les performances de la base de données, mais affecte également l'expérience utilisateur. Dans cet article, nous allons apprendre à analyser les raisons de la lenteur des requêtes MySQL et à proposer des solutions.

1. Surveiller le journal des requêtes lentes

Tout d'abord, nous devons activer la fonction de journal des requêtes lentes de MySQL afin d'analyser la cause d'une requête lente. Le journal des requêtes lentes enregistre le temps passé sur la requête, les instructions SQL, les adresses des clients et d'autres informations connexes, ce qui nous est d'une grande aide dans l'analyse des requêtes lentes.

Pour activer le journal des requêtes lentes, nous devons modifier le fichier de configuration MySQL "my.cnf" ou "my.ini" et ajouter le contenu suivant sous le nœud "[mysqld]" :

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2

Parmi eux, "slow_query_log " signifie activer le journal des requêtes lentes, une valeur de 1 signifie qu'il est activé. "slow_query_log_file" spécifie le chemin de sauvegarde du journal des requêtes lentes, qui peut être modifié en fonction de la situation réelle. "long_query_time" indique combien de secondes dure la requête dépasse avant qu'elle ne soit considérée comme une requête lente. La valeur par défaut est de 10 secondes, qui peut être modifiée en fonction de la situation réelle. Après avoir modifié le fichier de configuration, redémarrez le service MySQL pour que le fichier de configuration prenne effet.

2. Utilisez des outils d'analyse du journal des requêtes lentes

Après avoir obtenu le journal des requêtes lentes, nous devons utiliser certains outils pour analyser le journal et découvrir la raison de la requête lente. Ce qui suit présente deux outils d'analyse des journaux de requêtes lentes couramment utilisés :

2.1 mysqldumpslow

mysqldumpslow est le propre outil d'analyse des journaux de requêtes lentes de MySQL. Il peut être basé sur différentes dimensions, telles que le temps, l'instruction de requête, l'adresse du client, etc. le journal des requêtes lentes et donne les résultats statistiques correspondants.

Avant d'utiliser mysqldumpslow pour l'analyse, nous devons comprendre certains paramètres de mysqldumpslow :

• -a : afficher les résultats dans l'ordre inverse du temps de requête
• -s : spécifier la dimension de tri les paramètres couramment utilisés sont : t (sort. par heure) ), al (trié par instruction de requête), ar (trié par adresse client), etc.
• -t : le nombre de résultats de sortie

L'utilisation de la commande mysqldumpslow nécessite le journal de requête lent en entrée, généralement le le chemin par défaut du journal des requêtes lentes est "/var/log/mysql/slow-query.log". Voici un exemple :

mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

La commande ci-dessus triera par heure et affichera les 10 premiers éléments de données.

2.2 pt-query-digest

pt-query-digest est un outil d'analyse des journaux de requêtes lentes de la suite d'outils Percona. Il peut non seulement analyser les journaux de requêtes lentes de MySQL, mais également les journaux de requêtes lentes d'autres bases de données. Par rapport à mysqldumpslow, pt-query-digest prend en charge plus de dimensions et des résultats d'analyse plus précis.

Avant d'utiliser pt-query-digest, vous devez installer la suite d'outils Percona, puis exécuter la commande suivante pour effectuer l'analyse :

pt-query-digest /var/log/mysql/slow-query.log

Une fois l'opération terminée, pt-query-digest donnera les résultats statistiques correspondants, et peut être basé sur différentes dimensions à trier.

3. Optimiser les instructions de requête

L'analyse des journaux de requêtes lentes peut découvrir les raisons des requêtes lentes, mais pour vraiment résoudre le problème, vous devez optimiser les instructions de requête. Voici quelques façons d’optimiser les instructions de requête.

3.1 Déterminer les index appropriés

Les index sont la clé pour accélérer les requêtes. Les index permettent à MySQL de localiser plus rapidement les lignes de données. Lors de la conception de la table, définissez les index appropriés en fonction des exigences de la requête. Généralement, nous devons ajouter des index pour les colonnes qui apparaissent fréquemment dans les clauses WHERE, tout en évitant trop d'index car les index occupent de l'espace disque et affectent les performances lors de l'écriture des données.

Si nous ne pouvons pas déterminer quelles colonnes doivent être indexées, nous pouvons utiliser la commande EXPLAIN pour afficher le plan d'exécution de la requête et découvrir où une optimisation est nécessaire. Par exemple, l'instruction de requête SQL suivante :

SELECT * FROM table WHERE name = 'Tom' and age > 18

Exécutez la commande EXPLAIN :

EXPLAIN SELECT * FROM table WHERE name = 'Tom' and age > 18

et obtenez les résultats suivants :

id  select_type table  type  possible_keys  key  key_len  ref  rows  Extra
1   SIMPLE      table  ref   idx_name_age   idx_name_age 123 const      10    Using where

Parmi eux, "type" représente le type de requête. Les types courants sont : ALL (analyse complète de la table. ), index (index) Scan), etc. ; "possible_keys" indique les index qui peuvent être utilisés ; "key" indique l'index réellement utilisé ; "Extra" indique d'autres informations, comme si une table temporaire est utilisée, etc. Si la requête utilise une analyse complète de la table, cela signifie que l'index approprié n'est pas utilisé.

3.2 Évitez d'utiliser des sous-requêtes inutiles

Une sous-requête est une instruction de requête imbriquée qui sélectionne des données et elle peut être imbriquée dans d'autres instructions de requête. Bien que les sous-requêtes puissent facilement interroger des données complexes, dans certains cas, elles ont de faibles performances et peuvent facilement causer des problèmes. Afin d'éviter les problèmes de performances des sous-requêtes, nous pouvons utiliser des requêtes associées ou des tables temporaires pour remplacer les sous-requêtes ou optimiser les sous-requêtes.

3.3 Récupération de données à la demande

Lorsque nous exécutons l'instruction de requête SELECT, nous n'avons parfois pas besoin d'interroger toutes les colonnes et lignes, mais seulement certaines colonnes et lignes. À ce stade, nous devrions essayer de récupérer des données à la demande et d'obtenir le nombre de lignes requis via la clause LIMIT pour réduire la charge de travail de la base de données et la quantité de transmission de données. Par exemple, l'instruction de requête SQL suivante :

SELECT * FROM table WHERE id > 100 ORDER BY id DESC

doit uniquement interroger les enregistrements dont l'ID est supérieur à 100 et les trier par ordre décroissant par ID. S'il y a beaucoup d'enregistrements dans la table, nous pouvons utiliser la clause LIMIT pour limiter le jeu de résultats de la requête :

SELECT * FROM table WHERE id > 100 ORDER BY id DESC LIMIT 50

以上就是几种优化查询语句的方法，在实际的应用中，我们需要根据具体的情况选择合适的方法。

总结

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!