MySQL startet langsames SQL und analysiert die Ursachen

Schritt 1. Aktivieren Sie die langsame MySQL-Abfrage

Methode 1: Ändern der Konfigurationsdatei

Windows: Die Windows-Konfigurationsdatei ist my.ini und befindet sich normalerweise im MySQL-Installationsverzeichnis oder unter c:\Windows.

Linux: Die Linux-Konfigurationsdatei ist my.cnf, normalerweise in /etc

Fügen Sie my.ini einige Zeilen hinzu:

[mysqlld] 
lange_Abfragezeit=2 
#5.0, 5.1 und andere Versionen haben die folgenden Konfigurationsoptionen log-slow-queries="mysql_slow_query.log" 
#5.5 und höhere Versionen konfigurieren die folgende Option slow-query-log=On 
slow_query_log_file="mysql_slow_query.log" 
Log-Abfrage verwendet keine Indizes

Der erste Satz wird verwendet, um zu definieren, wie viele Sekunden es dauert, bis eine Abfrage als langsam gilt. Ich definiere es hier als 2 Sekunden.

Der zweite Satz wird verwendet, um den Pfad des langsamen Abfrageprotokolls zu definieren (da es sich um Windows handelt, gibt es kein Berechtigungsproblem).

Der dritte Satz dient zum Aufzeichnen der Abfrage, die den Index nicht verwendet

Schritt 2: Überprüfen des Status langsamer Abfragen

Methode 2: Aktivieren Sie langsame Abfragen über die MySQL-Datenbank

Damit die obige Konfiguration wirksam wird, muss der MySQL-Serverprozess mysqld neu gestartet werden. Aber häufig, insbesondere in Produktbetriebsumgebungen, möchten Sie den MySQL-Server nicht bei jeder Änderung neu starten und Sie möchten die Änderungen auch zu bestimmten festgelegten Zeitpunkten aufzeichnen. MySQL 5.1 bietet uns eine flexiblere Laufzeitsteuerung, sodass Sie den MySQL-Server nicht neu starten müssen und bestimmte langsame Abfragen selektiv aufzeichnen können oder nicht.

In MySQL 5.1 werden die globalen Variablen slow_query_log und slow_query_log_file bereitgestellt, um das Aktivieren/Deaktivieren langsamer Abfragen flexibel zu steuern. Sie können die Zeit auch über long_query_time einstellen

#//Protokollierung langsamer Abfragen aktivieren#Hinweis: Wenn die globale Variable slow_query_log gesetzt ist, ändert sich auch log_slow_queries implizitmysql>set global slow_query_log=ON

Leider bietet MySQL 5.0 keine ähnlichen globalen Variablen zur flexiblen Steuerung, aber wir können die Aufzeichnung bestimmter Abfrageanweisungen vermeiden, indem wir long_query_time auf einen ausreichend großen Wert setzen. Zum Beispiel

mysql>globale Long_Query_Time = 3600 festlegen;

mysql>globale log_querise_not_using_indexes = EIN festlegen;

Wenn Sie in MySQL 5.0 keine Protokolle aufzeichnen möchten, wenn der Dienst nicht heruntergefahren ist, können Sie die Protokolldatei zu einem symbolischen Link auf /dev/null machen. Hinweis: Sie müssen FLUSH LOGS erst nach der Änderung ausführen, um sicherzustellen, dass MySQL den aktuellen Protokolldateideskriptor freigibt und erneut in /dev/null protokolliert.

Im Gegensatz zu MySQL 5.0 kann MySQL 5.1 das Journaling-Verhalten zur Laufzeit ändern und Protokolle in Datenbanktabellen aufzeichnen. Setzen Sie einfach die globale MySQL-Variable log_output auf TABLE. MySQL zeichnet Protokolle in zwei Tabellen auf: mysql.gengera_log und mysql.slow_log. Wir empfehlen jedoch die Protokollierung in einer Journaldatei.

mysql> Variablen wie 'log_output' anzeigen\G
Variablenname: log_output
Wert: FILE
mysql>globale log_output='Tabelle' festlegen;

Mängel und Audits

Das Aufzeichnen langsamer Abfragen kann Ihnen jedoch dabei helfen, Ihr Produkt zu optimieren. Die aktuelle Version von MySQL weist jedoch noch einige Mängel auf.

• 1. In MySQL 5.0 ist die Zeitgranularität von long_query_time nicht fein genug und der Mindestwert beträgt 1 Sekunde. Bei Webskripten mit hoher Parallelitätsleistung ist das Erscheinen innerhalb von 1 Sekunde von geringer Bedeutung. Das heißt, es gibt relativ wenige Abfragen, die 1 Sekunde dauern. Erst in MySQL 5.1.21 wurde eine feinkörnigere long_query_time-Einstellung bereitgestellt.
• 2. Nicht alle vom Server ausgeführten Abfragen können im Slow-Log aufgezeichnet werden. Obwohl das normale MySQL-Protokoll alle Abfragen aufzeichnet, werden sie vor der Analyse der Abfrage aufgezeichnet. Dies bedeutet, dass normale Protokolle keine Informationen wie Ausführungszeit, Tabellensperrzeit, Anzahl der geprüften Zeilen usw. enthalten können.
• 3. Wenn die Option log_queries_not_using_indexes aktiviert ist, wird das langsame Abfrageprotokoll mit zu vielen Junk-Logdatensätzen gefüllt. Diese schnellen und effizienten vollständigen Tabellenscanabfragen (kleine Tabellen) löschen die wirklich nützlichen langsamen Abfragedatensätze aus. Auch Abfragen wie „select * from category“ werden aufgezeichnet.

Mit dem Microslow-Patch-Patch können Sie eine feinere Zeitgranularität verwenden und alle ausgeführten SQL-Anweisungen aufzeichnen. Um diesen Patch zu verwenden, müssen Sie MySQL jedoch selbst kompilieren. Aus Stabilitätsgründen empfehlen wir, diesen Patch in einer Entwicklungs- und Testumgebung anzuwenden, um den damit verbundenen Komfort zu nutzen. Versuchen Sie, dies nicht in einer Betriebsumgebung zu tun …

Schritt 2. Überprüfen Sie, ob die langsame Abfrage aktiviert ist

Führen Sie die folgende SQL-Anweisung aus, um den Status der langsamen MySQL-Abfrage anzuzeigen

Das Ausführungsergebnis gibt auf dem Bildschirm Informationen aus, z. B. ob die langsame Abfrage aktiviert ist, die Anzahl der Sekunden für die langsame Abfrage, das Protokoll der langsamen Abfrage usw.

/* Langsame Abfragezeit prüfen */ 
Variablen wie „long_query_time“ anzeigen; Standard 10 s 
/* Überprüfen Sie die Konfiguration für langsame Abfragen */ 
Status wie „%slow_queries%“ anzeigen; 
/*Protokollpfad für langsame Abfragen anzeigen*/ 
 Variablen wie „%slow%“ anzeigen;

Schritt 3: Ausführen einer langsamen Abfrage

Tatsächlich ist es schwierig, eine sinnvolle langsame Abfrage auszuführen, denn als ich es selbst getestet habe, dauerte die Abfrage einer riesigen Tabelle mit 200.000 Datensätzen nur wenige Sekunden. Wir können es durch die folgende Anweisung ersetzen:

Wählen Sie Sleep (10);

Schritt 4: Überprüfen Sie die Anzahl langsamer Abfragen

Verwenden Sie die folgende SQL-Anweisung, um anzuzeigen, wie viele langsame Abfragen ausgeführt wurden:

globalen Status wie „%slow%“ anzeigen;

Konfiguration des MySQL-Protokolls:

Hinweis: Diese täglichen Dateien werden nur generiert, wenn MySQL neu gestartet wird. #Alle SQL-Anweisungen aufzeichnen log=E:/mysqllog/mysql.log 
# Notieren Sie Informationen zum Starten und Herunterfahren der Datenbank sowie während des Vorgangs generierte Fehlerinformationen log-error=E:/mysqllog/myerror.log 
# Zeichnen Sie alle SQL-Anweisungen außer Select-Anweisungen im Protokoll auf, das zum Wiederherstellen der Datendatei log-bin=E:/mysqllog/bin verwendet werden kann 
#Langsame Abfrage-SQL-Anweisungen aufzeichnen log-slow-queries=E:/mysqllog/slow.log  
#Langsame Abfragezeit long_query_time=2

Schritt 4: Analysieren des langsamen Abfrageprotokolls

Methode 1: Analyse mithilfe von Tools

MySQL verfügt über das Tool mysqldumpslow zur Analyse langsamer Abfrageprotokolle. Darüber hinaus gibt es einige nützliche Open-Source-Tools. Zum Beispiel MyProfi (Download-Adresse: http://sourceforge.net/projects/myprofi/), mysql-log-filter und natürlich mysqlsla

Nachfolgend finden Sie eine Beschreibung der allgemeinen Parameter von mysqldumpslow. Detaillierte Informationen erhalten Sie, indem Sie mysqldumpslow -help zur Abfrage verwenden.

• -s gibt die Sortiermethode an. c, t, l und r werden nach Anzahl der Datensätze, Zeit, Abfragezeit und Anzahl der zurückgegebenen Datensätze (von groß nach klein) sortiert. ac, at, al und ar werden in umgekehrter Reihenfolge sortiert.
• -t bedeutet oberste n, also wie viele Datensätze zurückgegeben werden.
• -g, gefolgt von einem Übereinstimmungsmuster für einen regulären Ausdruck, unterscheidet nicht zwischen Groß- und Kleinschreibung.

Der nächste Schritt besteht darin, mysqldumpslow zu verwenden, ein langsames Abfragetool, das mit MySQL geliefert wird, um es zu analysieren (im Bin-Verzeichnis von MySQL). Der Name meiner Protokolldatei lautet host-slow.log.

Liste der 10 wichtigsten SQL-Anweisungen mit den meisten Datensätzen

mysqldumpslow -sc -t 10 host-slow.log

Listen Sie die 10 wichtigsten SQL-Anweisungen auf, die die meisten Datensätze zurückgeben

mysqldumpslow -sr -t 10 host-slow.log

Gibt die ersten 10 SQL-Anweisungen zurück, die Left Joins enthalten, und zwar nach Zeit.

mysqldumpslow -st -t 10 -g "linker Join" host-slow.log

Mit dem Befehl mysqldumpslow können wir die verschiedenen benötigten Abfrageanweisungen klar abrufen, was bei der Überwachung, Analyse und Optimierung von MySQL-Abfrageanweisungen eine große Hilfe ist.

Methode 2: Direkte Analyse der langsamen MySQL-Abfrageprotokolle

Ein Teil des Protokolls lautet wie folgt:

# Zeit: 121017 17:38:54 
# Benutzer@Host: root[root] @ localhost [127.0.0.1] 
# Abfragezeit: 3,794217 Sperrzeit: 0,000000 Gesendete Zeilen: 1 Untersuchte Zeilen: 4194304 
SET-Zeitstempel=1350466734; 
Wählen Sie * aus Wei, wobei Text = „orange“ ist. 
# Zeit: 121017 17:46:22 
# Benutzer@Host: root[root] @ localhost [127.0.0.1] 
# Abfragezeit: 3,819219 Sperrzeit: 0,000000 Gesendete Zeilen: 0 Untersuchte Zeilen: 4194304 
SET-Zeitstempel=1350467182; 
Wählen Sie * aus Wei, wobei Text = "xishizhaohua" ist.

Tatsächlich reicht es aus, die langsame Abfrageanweisung zu lokalisieren. Führen Sie den Befehl „explain“ oder „desc“ aus, um die langsame Abfrageanweisung anzuzeigen, wie unten gezeigt:

Das Problem liegt auf der Hand, und auch die Lösung liegt auf der Hand: Erstellen Sie einen Index.

mysql> erstelle Index text_index auf wei(text);  
Abfrage OK, 4194304 Zeilen betroffen (1 Min. 58,07 Sek.)  
Datensätze: 4194304 Duplikate: 0 Warnungen: 0

Die Ausführung der Abfrageoperation nimmt dann deutlich weniger Zeit in Anspruch.

mysql> wähle * von wei, wobei Text = "orange" ist; 
+---------+--------+ 
| Ich würde | Text | 
+---------+--------+ 
| 4103519 | orange | 
+---------+--------+ 
1 Zeile im Satz (0,33 Sek.)

Das Slow Query-Protokoll hilft Ihnen, die ausgeführten SQL-Anweisungen aufzuzeichnen. Aber es ist kein Allheilmittel und bedeutet möglicherweise nicht so viel, wie Sie denken. Es sagt Ihnen nur, welche Anweisungen langsam sind, aber warum sind sie langsam? Sie müssen die spezifischen Gründe immer noch selbst analysieren und kontinuierlich debuggen. Vielleicht müssen Sie nur eine effizientere SQL-Anweisung ändern, vielleicht müssen Sie einfach nur einen Index hinzufügen, aber es ist auch möglich, dass Sie das Design Ihrer Anwendung anpassen müssen. Beispielsweise ist die obige Anweisung sehr offensichtlich, sie überprüft mehr als 6 Millionen Datenzeilen. Leider ist nicht jede Aussage so offensichtlich. Es kann andere Gründe geben, wie zum Beispiel:

• *Die Tabelle ist gesperrt, wodurch sich die Abfrage in einem isochronen Zustand befindet. lock_time zeigt die Zeit an, die die Abfrage auf die Übersetzung der Sperre gewartet hat
• *Daten oder Indizes werden nicht zwischengespeichert. Dies kommt häufig vor, wenn der Server zum ersten Mal gestartet wird oder nicht optimiert ist.
• *Backup-Datenbank, I/O verlangsamt sich
• * Möglicherweise werden gleichzeitig andere Abfragen ausgeführt, wodurch die aktuelle Abfrage reduziert wird

Seien Sie daher nicht zu beunruhigt über einen bestimmten Eintrag in der Protokolldatei, sondern sehen Sie sich diese mit Bedacht an, um den wahren Grund herauszufinden. Treten langsame Abfragen häufig auf, ist besondere Aufmerksamkeit geboten. Wenn es einzeln auftritt, führen Sie einfach einige Routinekontrollen durch. Wir empfehlen, Statistiken und Benchmarkberichte zum Vergleichen und Ausschließen anzulegen, da dies sinnvoller ist, als blinde Versuche zu unternehmen. Ich hoffe, Sie verschwenden nicht zu viel Zeit und Energie auf diesen Teil.

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM. Wenn Sie mehr darüber erfahren möchten, schauen Sie sich bitte die folgenden Links an