Leistungsanalyse und Optimierung von MySQL-Anweisungen

1. Verwenden Sie „explain“, um den Abfrageplan anzuzeigen.

2 ), vollständig Der Befehl lautet wie folgt: mysql -uroot -p -e 'show Processlist G' |grep state: |sort|uniq -c|sort -rn Diese Methode ähnelt Methode 3. Es sollte gesagt werden, dass Methode 3 ähnlich ist nützlicher.

3. Showprofil verwenden. Es ist standardmäßig deaktiviert und muss mit set profiling = 1 aktiviert werden. Geben Sie nach der Ausführung einiger Abfragen showprofiles ein, um sicherzustellen, dass die Abfragezeit der zuvor ausgeführten Anweisungen mit hoher Genauigkeit angezeigt wird. Verwenden Sie dann „Profil anzeigen“ für Abfrage n, um jeden Schritt der Abfrageausführung entsprechend der Abfrageanweisung und die dafür benötigte Zeit anzuzeigen.

4. Verwenden Sie langsame Protokolle und verwenden Sie das Drittanbieter-Tool pt-query-digest, um Analyseberichte zu erstellen. Wenn Sie diese Analysemethode verwenden, ist es sehr wahrscheinlich, dass Sie die Konfigurationsdatei ändern müssen, die auf das folgende Formular eingestellt werden kann: log_slow_queries = /var/log/mysql/mysql-slow.log#Das Protokollspeicherverzeichnis long_query_time = 0 //Alle Abfrageprotokolle erfassen -queries-not-using-indexes//Kann auch dann aufgezeichnet werden, wenn keine Indizes verwendet werden

Im Projekt wurde festgestellt, dass fast die gesamte Programmausführungszeit für Datenbankoperationen verbraucht wird . Verwenden Sie pt-query-digest, um einen Analysebericht zum langsamen Abfrageprotokoll zu erstellen (in der tatsächlichen Produktion ist es nicht einfach, das langsame Abfrageprotokoll zu öffnen und zu schließen. In diesem Fall können Sie es simulieren, indem Sie den TCP-Verkehr überwachen oder tcpdump verwenden). Es wurde festgestellt, dass Aktualisierungs- und Einfügevorgänge 95 % der Zeit ausmachen.

Anschließend wird die ausgeführte Anweisung weiter analysiert.

Der Zeitverbrauch jedes Teils dieser Aktualisierungserklärung ist wie folgt:

Es ist ersichtlich, dass die Die Zeit wird hauptsächlich für den Endstatus der Abfrage aufgewendet.

Gehen Sie zu Google, um die Antwort zu erhalten. Fügen Sie innodb_flush_log_at_trx_commit = 0 zur MySQL-Konfigurationsdatei my.conf hinzu. Nach der Überprüfung wurde das Problem erfolgreich gelöst und die Geschwindigkeitsverbesserung war sehr offensichtlich (die oben genannten Änderungen wirkten sich auch auf den Einfügevorgang aus). Gleichzeitig bleiben Fragen offen: Wie ist der Status des Abfrageendes, warum dauert es so lange und warum hat sich die Leistung nach dem Hinzufügen von innodb_flush_log_at_trx_commit = 0 so stark verbessert?

Wie ist der Status des Abfrageendes? In der offiziellen Dokumentation von MySQL wird erklärt: Dieser Status tritt nach der Verarbeitung einer Abfrage ein, aber vor dem Freigabestatus für Elemente. Meines Wissens nach wird die Anweisung ausgeführt, aber es gibt noch einige Folgearbeiten, die noch nicht abgeschlossen sind.

Wie ist also der Status der Freigabe von Artikeln? Der Thread hat einen Befehl ausgeführt, der während dieses Zustands durchgeführt wird. Auf diesen Zustand folgt normalerweise das Bereinigen des Speicherplatzes im Abfrage-Cache (da es sich um einen Aktualisierungsvorgang handelt). Datensätze im Cache sind ungültig, daher ist dieser Schritt erforderlich.

Der Standardwert von innodb_flush_log_at_trx_commit ist 1. Das Verhalten zu diesem Zeitpunkt ist: Der Protokollpuffer wird bei jedem Transaktions-Commit in die Protokolldatei geschrieben und der Flush-to-Disk-Vorgang wird für die Protokolldatei ausgeführt. Die Funktion des Protokollpuffers: Ermöglicht der Transaktion, das Protokoll (die Transaktion muss ein Protokoll verwalten) erst nach Abschluss der Ausführung auf die Festplatte zu schreiben. Sollte die Zeit hauptsächlich für Festplatten-E/A aufgewendet werden?

Nachdem der Wert von innodb_flush_log_at_trx_commit auf 0 geändert wurde, ist das Verhalten wie folgt: Wenn der Wert von innodb_flush_log_at_trx_commit 0 ist, wird der Protokollpuffer einmal pro Sekunde in die Protokolldatei geschrieben und der Flush-to-Disk-Vorgang wird für das Protokoll ausgeführt Datei, aber bei einem Transaktions-Commit wird nichts getan. Es ist ersichtlich, dass nach dem Wechsel auf 0 die Operation, die jedes Mal ausgeführt werden sollte, nur noch einmal pro Sekunde ausgeführt wird, was viel Zeit spart.

Das Setzen des Werts von innodb_flush_log_at_trx_commit auf 0 hat einen Nebeneffekt: Der Absturz eines serverseitigen MySQL-Programms führt dazu, dass die letzte Sekunde der Transaktionen verloren geht (bevor sie Zeit hatten, in die Protokolldatei aufgenommen zu werden). ). Angesichts der Tatsache, dass diese Anwendung keine so strengen Anforderungen an Transaktionen stellen muss, ist dies jedoch akzeptabel.

Das Obige ist die Leistungsanalyse und Optimierung von MySQL-Anweisungen. Weitere verwandte Inhalte finden Sie auf der chinesischen PHP-Website (m.sbmmt.com)!