Analyse des performances et optimisation des instructions MySQL

1. Utilisez expliquer pour afficher le plan de requête

2. Utilisez show processlist pour afficher le processus de requête (dans quel état). ), complete La commande est la suivante mysql -uroot -p -e 'show processlist G' |grep state : |sort|uniq -c|sort -rn Cette méthode est similaire à la méthode 3. Il faut dire que la méthode 3 est plus utile.

3. Utilisez le profil d'affichage. Il est désactivé par défaut et doit être activé avec set profiling = 1. Après avoir exécuté certaines requêtes, tapez show profiles pour voir que l'heure de la requête des instructions précédemment exécutées est affichée avec une grande précision. Utilisez ensuite show profile for query n pour voir chaque étape de l'exécution de la requête correspondant à l'instruction de requête et le temps qu'elle prend.

4. Utilisez des journaux lents et utilisez l'outil tiers pt-query-digest pour générer des rapports d'analyse. Lorsque vous utilisez cette méthode d'analyse, il est très probable que vous deviez modifier le fichier de configuration, qui peut être défini sous la forme suivante : log_slow_queries = /var/log/mysql/mysql-slow.log#Le répertoire de stockage des journaux long_query_time = 0 //Capturez tous les journaux de requêtes -queries-not-using-indexes//Peut être enregistré même si les index ne sont pas utilisés

Dans le projet, il a été constaté que presque tout le temps d'exécution du programme est consommé dans les opérations de base de données . Utilisez pt-query-digest pour créer un rapport d'analyse sur le journal des requêtes lentes (en production réelle, il n'est pas facile d'ouvrir et de fermer le journal des requêtes lentes. Dans ce cas, vous pouvez le simuler en surveillant le trafic TCP ou en utilisant tcpdump) On constate que les opérations de mise à jour et d'insertion représentent 95 % du temps.

Ensuite, l'instruction exécutée est analysée plus en détail.

La consommation de temps de chaque partie de cette déclaration de mise à jour est la suivante :

On peut voir que le le temps est principalement consacré au statut de fin de la requête.

Allez sur Google pour obtenir la réponse. Ajoutez innodb_flush_log_at_trx_commit = 0 au fichier de configuration mysql my.conf. Après vérification, le problème a été résolu avec succès et l'amélioration de la vitesse était très évidente (les changements ci-dessus ont également eu un effet sur l'opération d'insertion). Dans le même temps, des questions subsistent : quel est l'état de fin de la requête, pourquoi cela prend-il autant de temps et pourquoi les performances sont-elles tellement améliorées après l'ajout de innodb_flush_log_at_trx_commit = 0 ?

Quel est l’état de fin de requête ? La documentation officielle de MySQL explique : Cet état se produit après le traitement d'une requête mais avant l'état de libération des éléments. Je crois comprendre que l'instruction est exécutée, mais qu'il reste encore du travail de suivi qui n'est pas terminé.

Alors, quel est le statut de la libération des objets ? Le thread a exécuté une commande. Certaines libérations d'éléments effectuées pendant cet état impliquent le cache de requêtes. Cet état est généralement suivi d'un nettoyage de l'espace dans le cache de requêtes (car il s'agit d'une opération de mise à jour, donc correspondante). les enregistrements dans le cache sont invalides, cette étape est donc nécessaire).

La valeur par défaut de innodb_flush_log_at_trx_commit est 1. Le comportement à ce moment est le suivant : le tampon de journal est écrit dans le fichier journal à chaque validation de transaction et l'opération de vidage sur le disque est effectuée sur le fichier journal. La fonction du tampon de journal : permet à la transaction d'écrire le journal (la transaction doit conserver un journal) sur le disque uniquement une fois l'exécution terminée. Le temps doit-il être principalement consacré aux E/S du disque ?

Après avoir modifié la valeur de innodb_flush_log_at_trx_commit sur 0, le comportement est le suivant : Si la valeur de innodb_flush_log_at_trx_commit est 0, le tampon du journal est écrit dans le fichier journal une fois par seconde et l'opération de vidage sur le disque est effectuée sur le journal. fichier, mais rien n'est fait lors d'une validation de transaction. On constate qu'après le passage à 0, l'opération qui devait être effectuée à chaque fois n'est désormais effectuée qu'une fois par seconde, ce qui permet de gagner beaucoup de temps.

Fixer la valeur de innodb_flush_log_at_trx_commit à 0 a un effet secondaire : le crash de tout programme mysql côté serveur entraînera la perte de la dernière seconde des transactions (avant qu'elles n'aient eu le temps d'être incluses dans le fichier journal ). Mais étant donné que l'application n'a pas d'exigences aussi strictes en matière de transactions, cela est acceptable.

Ce qui précède est l'analyse des performances et l'optimisation des instructions mysql. Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (www.php.cn) !