|
Wie finde ich langsame SQL-Anweisungen in MySQL? Dies kann ein Problem sein, das viele Leute verwirrt. MySQL findet SQL-Anweisungen mit geringer Ausführungseffizienz durch langsame Abfrageprotokolle. Wenn mysqld mit der Option --log-slow-queries[=file_name] gestartet wird, schreibt es eine Protokolldatei mit allen SQL-Anweisungen, deren Ausführungszeit long_query_time Sekunden überschreitet. Durch Anzeigen dieser Protokolldatei können Sie die SQL-Anweisungen mit geringer Effizienz finden. So fragen Sie langsame SQL-Anweisungen in MySQL ab 1. Die MySQL-Datenbank verfügt über mehrere Konfigurationsoptionen, die uns helfen können, ineffiziente SQL-Anweisungen rechtzeitig zu erfassen 1. langsames_Abfrageprotokoll Wenn dieser Parameter auf ON gesetzt ist, können SQL-Anweisungen erfasst werden, deren Ausführungszeit einen bestimmten Wert überschreitet. 2. lange_Abfragezeit Wenn die Ausführungszeit der SQL-Anweisung diesen Wert überschreitet, wird dies im Protokoll aufgezeichnet. Es wird empfohlen, den Wert auf 1 oder weniger festzulegen. 3. slow_query_log_file Der Dateiname der Protokolldatei. 4. log_queries_not_using_indexes Wenn dieser Parameter auf „ON“ gesetzt ist, können alle SQL-Anweisungen, die keine Indizes verwenden, erfasst werden, obwohl die SQL-Anweisung möglicherweise sehr schnell ausgeführt wird. 2. Methoden zum Erkennen der Effizienz von SQL-Anweisungen in MySQL 1. Durch Abfragen des Protokolls (1) Aktivieren Sie die langsame MySQL-Abfrage unter Windows Die Konfigurationsdatei von MySQL im Windows-System ist im Allgemeinen my.ini. Finden Sie sie unter [mysqld] und fügen Sie hinzu Der Code lautet wie folgt log-slow-queries = F:/MySQL/log/mysqlslowquery. Protokoll
lange_Abfragezeit = 2 (2) Aktivieren Sie die langsame MySQL-Abfrage unter Linux Die Konfigurationsdatei von MySQL im Windows-System ist im Allgemeinen my.cnf. Finden Sie es unter [mysqld] und fügen Sie hinzu Der Code lautet wie folgt log-slow-queries=/data/mysqldata/slowquery. Protokoll
lange_Abfragezeit=2 veranschaulichen log-slow-queries = F:/MySQL/log/mysqlslowquery. Dies ist der Speicherort, an dem das langsame Abfrageprotokoll gespeichert wird. Im Allgemeinen muss dieses Verzeichnis über Schreibberechtigungen für das MySQL-Ausführungskonto verfügen. Im Allgemeinen wird dieses Verzeichnis als MySQL-Datenspeicherverzeichnis festgelegt.
Die 2 in long_query_time=2 bedeutet, dass die Abfrage nur aufgezeichnet wird, wenn sie länger als zwei Sekunden dauert. 2.Befehl „show processlist“ WSHOW PROCESSLIST zeigt an, welche Threads ausgeführt werden. Sie können diese Informationen auch mit der mysqladmin-Prozessliste-Anweisung abrufen. Bedeutung und Zweck der einzelnen Spalten: ID-Spalte Ein Flag, das nützlich ist, wenn Sie eine Anweisung beenden möchten. Beenden Sie die Abfrage mit dem Befehl /*/mysqladmin kill process number. Spalte „Benutzer“ Zeigt den aktuellen Benutzer an. Wenn Sie nicht der Root-Benutzer sind, zeigt dieser Befehl nur die SQL-Anweisungen innerhalb Ihrer Berechtigung an. Spalte „Host“ Zeigt, von welcher IP und welchem Port diese Anweisung gesendet wurde. Nützlich, um den Benutzer aufzuspüren, der die problematische Anweisung erstellt hat. db-Spalte Zeigt an, mit welcher Datenbank dieser Prozess aktuell verbunden ist. Befehlszeile Zeigt die von der aktuellen Verbindung ausgeführten Befehle an, im Allgemeinen „Sleep“, „Query“ oder „Connect“. Zeitspalte Die Dauer dieses Zustands in Sekunden. Spalte „Status“ Zeigt den Status der SQL-Anweisung unter Verwendung der aktuellen Verbindung an. Dies ist eine sehr wichtige Spalte. Alle Status werden später beschrieben. Bitte beachten Sie, dass der Status nur ein bestimmter Zustand bei der Ausführung einer Anweisung ist. Beispielsweise muss eine SQL-Anweisung, wie z. B. eine Abfrage, möglicherweise die Zustände „In temporäre Tabelle kopieren“, „Ergebnis sortieren“ und „Daten senden“ durchlaufen, bevor sie abgeschlossen werden kann. Infospalte Dieses SQL-Statement wird angezeigt. Aufgrund der begrenzten Länge werden lange SQL-Statements nicht vollständig angezeigt, es ist jedoch eine wichtige Grundlage zur Beurteilung von Problemstellungen. Der kritischste Teil dieses Befehls ist die Spalte „state“. Die von MySQL aufgelisteten Zustände sind hauptsächlich die folgenden: Prüftabelle
Überprüfen der Datentabellen (dies erfolgt automatisch).
Tabellen schließen
Die geänderten Daten in der Tabelle werden auf die Festplatte geschrieben und die verwendete Tabelle wird geschlossen. Dieser Vorgang geht sehr schnell, ist dies jedoch nicht der Fall, sollten Sie prüfen, ob die Festplatte voll ist oder ob sie stark ausgelastet ist.
Aus verbinden
Der Replikations-Slave-Server stellt eine Verbindung zum Master-Server her.
Kopieren in die temporäre Tabelle auf der Festplatte
Da der temporäre Ergebnisset größer als tmp_table_size ist, wird die temporäre Tabelle aus Speichergründen vom Arbeitsspeicher in den Festplattenspeicher konvertiert.
Tmp-Tabelle erstellen
Erstellen einer temporären Tabelle zum Speichern teilweiser Abfrageergebnisse.
Löschen aus der Haupttabelle
Der Server führt den ersten Teil einer Löschung mehrerer Tabellen aus und hat gerade die erste Tabelle gelöscht.
Löschen aus Referenztabellen
Der Server führt den zweiten Teil einer Mehrfachtabellenlöschung aus und löscht Datensätze aus anderen Tabellen.
Spültische
FLUSH TABLES wird ausgeführt und wartet darauf, dass andere Threads die Datentabelle schließen.
Getötet
Wenn eine Kill-Anforderung an einen Thread gesendet wird, überprüft der Thread das Kill-Flag und bricht die nächste Kill-Anforderung ab. MySQL überprüft das Kill-Flag in jeder Hauptschleife, aber in einigen Fällen kann es eine Weile dauern, bis der Thread beendet wird. Wenn der Thread durch andere Threads gesperrt ist, wird die Kill-Anforderung sofort wirksam, wenn die Sperre aufgehoben wird.
Gesperrt
Durch eine andere Abfrage gesperrt.
Senden von Daten
Die Datensätze der SELECT-Abfrage werden verarbeitet und die Ergebnisse werden an den Client gesendet.
Sortierung nach Gruppe
Sortieren nach GROUP BY.
Sortieren nach Bestellung
Sortierung für ORDER BY.
Tische öffnen
Dieser Vorgang sollte schnell ablaufen, sofern er nicht durch andere Faktoren gestört wird. Beispielsweise kann die Tabelle nicht von anderen Threads geöffnet werden, bis die Anweisung ALTER TABLE oder LOCK TABLE abgeschlossen ist. Ich versuche, einen Tisch zu öffnen.
Duplikate entfernen
Es wird eine SELECT DISTINCT-Abfrage ausgeführt, aber MySQL konnte im vorherigen Schritt doppelte Datensätze nicht durch Optimierung beseitigen. Daher muss MySQL doppelte Datensätze erneut entfernen und dann die Ergebnisse an den Client senden.
Tabelle erneut öffnen
Es wird eine Sperre für eine Tabelle eingerichtet, dies kann jedoch erst nach einer Änderung der Tabellenstruktur erfolgen. Die Sperre wurde aufgehoben, die Tabelle wurde geschlossen und es wird versucht, die Tabelle erneut zu öffnen.
Reparatur durch Sortieren
Korrigieren Sie die Anweisungen zum Sortieren, um einen Index zu erstellen.
Reparatur mit Keycache
Die Reparaturanweisung besteht darin, unter Verwendung des Index-Cache nacheinander neue Indizes zu erstellen. Dies ist langsamer als die Reparatur durch Sortieren.
Durchsuche Zeilen für Updates
Zur Aktualisierung werden die Datensätze gesucht, die die Bedingungen erfüllen. Dies muss erfolgen, bevor das UPDATE zugehörige Datensätze ändern kann.
Schlafen
Warte auf eine neue Clientanfrage.
Systemsperre
Wartet auf den Erwerb einer externen Systemsperre. Wenn Sie nicht mehrere MySQLD-Server ausführen, die gleichzeitig dieselbe Tabelle anfordern, können Sie externe Systemsperren deaktivieren, indem Sie den Parameter --skip-external-locking hinzufügen.
Schloss aufrüsten
INSERT DELAYED versucht, eine Tabellensperre zu erwerben, um einen neuen Datensatz einzufügen.
Aktualisierung
Nach passenden Datensätzen suchen und diese ändern. Benutzersperre
Wartet auf GET_LOCK().
Warten auf Tische
Der Thread wird benachrichtigt, dass die Datentabellenstruktur geändert wurde und erneut geöffnet werden muss, um die neue Struktur zu erhalten. Um die Datentabelle dann erneut zu öffnen, müssen Sie warten, bis alle anderen Threads die Tabelle schließen. Diese Benachrichtigung wird in den folgenden Situationen generiert: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE oder OPTIMIZE TABLE.
Warten auf Handler-Einfügen
INSERT DELAYED hat alle ausstehenden Einfügevorgänge verarbeitet und wartet auf neue Anforderungen.
Die meisten Zustände entsprechen sehr schnellen Vorgängen. Wenn ein Thread mehrere Sekunden lang im gleichen Zustand bleibt, liegt möglicherweise ein Problem vor und muss überprüft werden.
Es gibt noch weitere Status, die oben nicht aufgeführt sind, die meisten davon sind jedoch nur nützlich, um festzustellen, ob Fehler beim Server vorliegen. Beispielsweise wie in der Abbildung dargestellt: 
3. Erklären Sie, wie Sie den Status der SQL-Ausführung verstehen EXPLAIN zeigt, wie MySQL Indizes verwendet, um Auswahlanweisungen zu verarbeiten und Tabellen zu verknüpfen. Es kann dabei helfen, bessere Indizes auszuwählen und optimiertere Abfrageanweisungen zu schreiben. Um es zu verwenden, fügen Sie einfach „explain“ vor der Select-Anweisung hinzu: Zum Beispiel:
Erklären Sie, dass Sie Nachname und Vorname in der Form a,b auswählen, wobei a.id=b.id Die Ergebnisse sind in der Abbildung dargestellt 
Erklärung der EXPLAIN-Spalten Tisch Zeigt an, um welche Tabelle es sich bei dieser Datenzeile handelt Typ Dies ist die wichtige Spalte, die zeigt, welche Art von Verbindung verwendet wird. Die Join-Typen vom besten zum schlechtesten sind const, eq_reg, ref, range, indexhe und ALL mögliche Schlüssel Zeigt mögliche Indizes, die auf diese Tabelle angewendet werden können. Wenn leer, ist kein Index möglich. Sie können für das jeweilige Feld eine entsprechende Anweisung aus der WHERE-Klausel auswählen. Schlüssel Der tatsächlich verwendete Index. Wenn NULL, wird kein Index verwendet. In seltenen Fällen wählt MySQL einen nicht ausreichend optimierten Index. In diesem Fall können Sie USE INDEX (Indexname) in der SELECT-Anweisung verwenden, um die Verwendung eines Indexes zu erzwingen, oder IGNORE INDEX (Indexname) verwenden, um MySQL zu zwingen, den Index zu ignorieren. Schlüssellänge Die Länge des zu verwendenden Indexes. Je kürzer die Länge, desto besser, ohne an Genauigkeit zu verlieren. Referenz Zeigt an, welche Spalte des Index verwendet wird, oder wenn möglich eine Konstante. Reihen Die Anzahl der Zeilen, die MySQL als notwendig erachtet, um die angeforderten Daten zurückzugeben Extra Zusätzliche Informationen darüber, wie MySQL die Abfrage analysiert. Dies wird in Tabelle 4.3 erläutert. Die hier zu sehenden schlechten Beispiele sind jedoch die Verwendung der temporären Methode und die Verwendung der Dateisortierung. Dies bedeutet, dass MySQL den Index überhaupt nicht verwenden kann, was zu langsamen Abrufen führt. Die Bedeutung der Beschreibung, die in der zusätzlichen Spalte zurückgegeben wird Unterscheidbar Sobald MySQL eine Zeile findet, die mit der Zeilenvereinigung übereinstimmt, wird die Suche beendet. Existiert nicht MySQL optimiert LEFT JOIN. Sobald es eine Zeile findet, die den LEFT JOIN-Kriterien entspricht, wird nicht erneut gesucht. Für jeden Datensatz wird der Bereich geprüft (Indexkarte: #) Da kein idealer Index gefunden wurde, prüft MySQL für jede Zeilenkombination aus den vorherigen Tabellen, welcher Index verwendet werden soll, und gibt damit die Zeilen aus der Tabelle zurück. Dies ist eine der langsamsten Verbindungen über den Index Verwenden von Filesort Wenn Sie dies sehen, muss die Abfrage optimiert werden. MySQL muss einen zusätzlichen Schritt ausführen, um herauszufinden, wie die zurückgegebenen Zeilen sortiert werden. Es sortiert alle Zeilen basierend auf dem Verknüpfungstyp und speichert den Sortierschlüsselwert und die Zeilenzeiger für alle Zeilen, die der Bedingung entsprechen. Verwenden des Index Spaltendaten werden aus der Tabelle zurückgegeben, indem nur die Informationen im Index verwendet werden, ohne dass diese tatsächlich gelesen werden. Dies geschieht, wenn alle angeforderten Spalten für die Tabelle Teil desselben Index sind. Mit temporären Wenn Sie dies sehen, muss die Abfrage optimiert werden. Hier muss MySQL eine temporäre Tabelle zum Speichern der Ergebnisse erstellen. Dies geschieht normalerweise, wenn ORDER BY statt GROUP BY auf verschiedene Spaltensätze angewendet wird. Wo verwendet Mit der WHERE-Klausel wird eingeschränkt, welche Zeilen der nächsten Tabelle zugeordnet oder an den Benutzer zurückgegeben werden. Dies kann passieren, wenn Sie nicht alle Zeilen in der Tabelle zurückgeben möchten und der Verknüpfungstyp ALL oder Index ist oder wenn ein Problem mit der Abfrage vorliegt. Erklärung der verschiedenen Verknüpfungstypen (sortiert nach Effizienz) Konstante Der Maximalwert eines Datensatzes in der Tabelle, der dieser Abfrage entsprechen kann (der Index kann ein Primärschlüssel oder ein eindeutiger Index sein). Da nur eine Zeile vorhanden ist, ist dieser Wert tatsächlich eine Konstante, da MySQL den Wert zuerst liest und ihn dann als Konstante behandelt. eq_ref In der Verbindung liest MySQL bei der Abfrage für jede Datensatzvereinigung einen Datensatz aus der vorherigen Tabelle. Dies wird verwendet, wenn die Abfrage den Index als Primärschlüssel oder eindeutigen Schlüssel verwendet. Referenz Dieser Verknüpfungstyp tritt nur auf, wenn die Abfrage einen Schlüssel verwendet, der kein eindeutiger oder Primärschlüssel ist oder Teil eines dieser Typen ist (z. B. bei Verwendung eines ganz linken Präfixes). Bei jedem Zeilenjoin mit den vorherigen Tabellen werden alle Datensätze aus der Tabelle gelesen. Dieser Typ hängt stark davon ab, wie viele Datensätze mit dem Index abgeglichen werden – je weniger, desto besser. Reichweite Dieser Verknüpfungstyp verwendet einen Index, um einen Zeilenbereich zurückzugeben, ähnlich dem, was passiert, wenn Sie > oder < verwenden, um etwas zu finden. Index Dieser Verknüpfungstyp führt einen vollständigen Scan aller Datensätze in der vorherigen Tabelle durch (besser als ALLE, da der Index im Allgemeinen kleiner als die Tabellendaten ist). ALLE Dieser Verknüpfungstyp führt einen vollständigen Scan jedes vorherigen Datensatzes durch, was im Allgemeinen schlecht ist und vermieden werden sollte. MySQL - Langsames SQL anzeigen
Überprüfen Sie, ob MySQL die langsame SQL-Protokolldatei aktiviert hat (1) Überprüfen Sie, ob das langsame SQL-Protokoll aktiviert ist mysql> Variablen wie „log_slow_queries“ anzeigen;
+------------------+--------+
| Variablenname | Wert |
+------------------+--------+
| log_slow_queries | EIN |
+------------------+--------+
1 Zeile im Satz (0,00 Sek.) (2) Überprüfen Sie, wie viele Sekunden SQL-Anweisungen, die langsamer als die angegebene Anzahl von Sekunden sind, in der Protokolldatei aufgezeichnet werden
mysql> Variablen wie „long_query_time“ anzeigen;
+-----------------+--------+
| Variablenname | Wert |
+-----------------+--------+
| lange Abfragezeit | 1 |
+-----------------+--------+
1 Zeile im Satz (0,00 Sek.) Hier bedeutet Wert=1 1 Sekunde 2. Konfigurieren Sie die Datei my.ini (der Dateiname unter Linux lautet my.cnf), suchen Sie den Abschnitt [mysqld] und fügen Sie die Protokollkonfiguration hinzu, wie im folgenden Beispiel gezeigt:
[mysqld]
log="C:/temp/mysql.log"
log_slow_queries="C:/temp/mysql_slow.log"
lange_Abfragezeit=1
„log“ gibt das Verzeichnis an, in dem die Protokolldateien gespeichert sind.
log_slow_queries gibt das SQL-Protokollverzeichnis an, das die lange Ausführungszeit aufzeichnet;
long_query_time gibt in Sekunden an, wie lange die Ausführungszeit als lang angesehen wird.
Diese Konfigurationselemente sollten in Linux bereits vorhanden sein, sind aber auskommentiert. Sie können die Kommentare entfernen. Es ist aber auch in Ordnung, Konfigurationselemente direkt hinzuzufügen.
Nachdem Sie die ineffiziente SQL-Anweisung gefunden haben, können Sie mit dem Befehl EXPLAIN oder DESC Informationen darüber erhalten, wie MySQL die SELECT-Anweisung ausführt, einschließlich der Art und Weise, wie die Tabellen verbunden sind, und der Reihenfolge, in der sie während der Ausführung der SELECT-Anweisung verbunden sind. Wenn wir beispielsweise den Umsatz aller Unternehmen im Jahr 2006 berechnen möchten, müssen wir die Umsatztabelle und die Unternehmenstabelle verknüpfen und eine Summenoperation auf dem Gewinnfeld ausführen. Der entsprechende SQL-Ausführungsplan lautet wie folgt:
mysql> erklären Sie „select sum(profit) from sales a,company b where a.company_id = b.id und a.year = 2006\G“;
*************************** 1. Reihe ***************************
ID: 1
select_type: EINFACH
Tabelle: eine
Typ: ALLE
mögliche Schlüssel: NULL
Schlüssel: NULL
key_len: NULL
Ref: NULL
Reihen: 12
Extra: Verwenden von „where“
*************************** 2. Reihe ***************************
ID: 1
select_type: EINFACH
Tabelle: b
Typ: ALLE
mögliche Schlüssel: NULL
Schlüssel: NULL
key_len: NULL
Ref: NULL
Reihen: 12
Extra: Verwenden von „where“
2 Zeilen im Satz (0,00 Sek.)
Die Erklärung der einzelnen Spalten lautet wie folgt:
•select_type: Gibt den Typ von SELECT an. Gängige Werte sind SIMPLE (einfache Tabelle, d. h. es wird kein Tabellenjoin oder keine Unterabfrage verwendet), PRIMARY (primäre Abfrage, d. h. äußere Abfrage), UNION (die zweite oder nachfolgende Abfrageanweisung in UNION), SUBQUERY (das erste SELECT in einer Unterabfrage) usw.
•Tabelle: Die Tabelle zur Ausgabe des Ergebnissatzes.
• Typ: Zeigt den Verbindungstyp der Tabelle an. oder eindeutiger Index, gewöhnlicher Index wird verwendet), ref_or_null (ähnlich wie bei Ref, ist der Unterschied, dass die Bedingung eine Abfrage für NULL enthält), Index_merge (Index-Merge-Optimierung), Unique_SubQuery (in wird von einem Unterabschnitt, der das primäre Schlüsselfeld anliegt, in der Teile, die in der Region eingeteilt ist ), Index (für jede vorherige Zeile werden Daten erhalten, indem der Index abfragt) alle (für jede vorherige Zeile werden Daten erhalten, indem die gesamte Tabelle gescannt wird).
•possible_keys: gibt die Indizes an, die bei der Abfrage verwendet werden können.
•Schlüssel: gibt den tatsächlich verwendeten Index an.
•key_len: Die Länge des Indexfelds.
•Zeilen: Die Anzahl der zu scannenden Zeilen.
•Extra: Hinweise und Beschreibung der Implementierung.
Im obigen Beispiel kann bestätigt werden, dass der vollständige Tabellenscan der Tabelle a zu schlechter Effizienz führt. Erstellen Sie dann wie folgt einen Index für das Jahresfeld der Tabelle a:
mysql> erstelle Index idx_sales_year für Verkäufe(Jahr);
Abfrage OK, 12 Zeilen betroffen (0,01 Sek.)
Datensätze: 12 Duplikate: 0 Warnungen: 0
Nach dem Erstellen des Indexes sieht der Ausführungsplan für diese Anweisung wie folgt aus:
mysql> erklären Sie „select sum(profit) from sales a,company b where a.company_id = b.id und a.year = 2006\G“;
*************************** 1. Reihe ***************************
ID: 1
select_type: EINFACH
Tabelle: eine
Typ: ref
mögliche Schlüssel: idx_sales_year
Schlüssel: idx_sales_year
Schlüssellänge: 4
Verweis: const
Reihen: 3
Extra:
*************************** 2. Reihe ***************************
ID: 1
select_type: EINFACH
Tabelle: b
Typ: ALLE
mögliche Schlüssel: NULL
Schlüssel: NULL
key_len: NULL
Ref: NULL
Reihen: 12
Extra: Verwenden von „where“
2 Zeilen im Satz (0,00 Sek.) Es lässt sich feststellen, dass nach der Indexerstellung die Anzahl der Zeilen, die für Tabelle a gescannt werden müssen, erheblich reduziert ist (von einem vollständigen Tabellenscan auf 3 Zeilen). Es lässt sich erkennen, dass die Verwendung von Indizes die Zugriffsgeschwindigkeit der Datenbank erheblich verbessern kann, insbesondere wenn die Tabelle sehr groß ist. Dieser Vorteil ist offensichtlicher. Die Verwendung von Indizes zur Optimierung von SQL ist eine gängige grundlegende Methode zur Optimierung problematischen SQL. In den folgenden Kapiteln werden wir speziell die Verwendung von Indizes zur Optimierung von SQL vorstellen.
In diesem Artikel wird hauptsächlich die Methode zur Analyse langsamer MySQL-Abfragen vorgestellt. Vor einiger Zeit habe ich einen Datensatz eingerichtet, um SQL-Anweisungen in der MySQL-Datenbank langsamer als 1 Sekunde abzufragen. Mir fiel ein, dass es einige sehr spezielle Einstellungsmethoden gab, und ich konnte mich an die Namen einiger Parameter nicht erinnern. Deshalb habe ich sie neu geordnet und mir einige Notizen gemacht.
Wenn es um die Fehlersuche und das Auffinden von Leistungsengpässen geht, lassen sich langsame MySQL-Abfragen und Abfragen ohne Indizes am einfachsten finden und lösen. OK, beginnen wir mit der Suche nach SQL-Anweisungen, deren Ausführung in MySQL nicht „angenehm“ ist.
Methode 1 zur Analyse langsamer MySQL-Abfragen:
Ich verwende diese Methode, haha, ich bevorzuge die Unmittelbarkeit dabei.
MySQL-Versionen ab 5.0 unterstützen die Aufzeichnung von SQL-Anweisungen, die langsam ausgeführt werden.
MySQL> Variablen wie „long%“ anzeigen;
Hinweis: Diese long_query_time wird verwendet, um zu definieren, wie viele Sekunden langsamer eine Abfrage als "langsame Abfrage" gilt.
+-----------------+------------+
| Variablenname | Wert |
+-----------------+------------+
| lange Abfragezeit | 10.000000 |
+-----------------+------------+
1 Zeile im Satz (0,00 Sek.)
MySQL> setze long_query_time=1;
Hinweis: Ich habe es auf 1 gesetzt, was bedeutet, dass jede Abfrage, deren Ausführung länger als 1 Sekunde dauert, als langsame Abfrage betrachtet wird.
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
MySQL> Variablen wie „slow%“ anzeigen;
+---------------------+---------------+
| Variablenname | Wert |
+---------------------+---------------+
| langsame Startzeit | 2 |
| slow_query_log | EIN |
Hinweis: Ist die Protokollierung aktiviert? | slow_query_log_file | /tmp/slow.log |
Hinweis: Auf welche Position soll +---------------------+---------------+ eingestellt werden?
3 Zeilen im Satz (0,00 Sek.)
MySQL> global slow_query_log='ON' festlegen
Hinweis: Protokollierung aktivieren Sobald die Variable slow_query_log auf ON gesetzt ist, beginnt MySQL sofort mit der Protokollierung.
Die Anfangswerte der oben genannten globalen MySQL-Variablen können in /etc/my.cnf festgelegt werden.
long_query_time=1 slow_query_log_file=/tmp/slow.log
Methode 2 zur Analyse langsamer MySQL-Abfragen:
MySQLdumpslow-Befehl /Pfad/MySQLdumpslow -sc -t 10 /tmp/slow-log
Dadurch werden die 10 wichtigsten SQL-Anweisungen mit den meisten Datensätzen ausgegeben. Dabei gilt:
-s gibt die Sortiermethode an. c, t, l und r werden jeweils nach der Anzahl der Datensätze, der Zeit, der Abfragezeit und der Anzahl der zurückgegebenen Datensätze sortiert. ac, at, al und ar geben die entsprechende umgekehrte Sortierung an.
-t, was „oberste n“ bedeutet, gibt an, wie viele Datensätze zurückgegeben werden.
-g, gefolgt von einem passenden Muster für einen regulären Ausdruck, ohne Berücksichtigung der Groß-/Kleinschreibung;
Beispiel: /Pfad/MySQLdumpslow -sr -t 10 /tmp/slow-log
Holen Sie sich die 10 wichtigsten Abfragen, die die meisten Datensätze zurückgeben.
/Pfad/MySQLdumpslow -st -t 10 -g "left join" /tmp/slow-log
Holen Sie sich die ersten 10 Abfrageanweisungen mit Left Joins, sortiert nach Zeit.
Ein einfacherer Weg:
Öffnen Sie my.ini, suchen Sie [mysqld] und fügen Sie darunter long_query_time = 2 hinzu log-slow-queries = D:/mysql/logs/slow.log #Legen Sie fest, wohin die Protokolle geschrieben werden sollen. Es kann leer sein und das System gibt eine Standarddatei an #log-slow-queries = /var/youpath/slow.log host_name-slow.log unter Linux log-queries-not-using-indexes long_query_time bezieht sich darauf, wie lange (in Sekunden) es dauert, bis SQL ausgeführt wird, bevor es aufgezeichnet wird. Die Einstellung hier beträgt 2 Sekunden.
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. www.jb51.net -g, gefolgt von einem Übereinstimmungsmuster für reguläre Ausdrücke, bei dem die Groß-/Kleinschreibung nicht beachtet wird. 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. Listet die 10 SQL-Anweisungen mit den meisten Datensätzen auf mysqldumpslow -sc -t 10 host-slow.log Listet die 10 SQL-Anweisungen mit den meisten zurückgegebenen Datensätzen auf mysqldumpslow -sr -t 10 host-slow.log Gibt die 10 wichtigsten SQL-Anweisungen mit linken Verknüpfungen nach Zeit zurück mysqldumpslow -st -t 10 -g "left join" host-slow.log Mit dem Befehl mysqldumpslow können wir die verschiedenen Abfrageanweisungen, die wir benötigen, klar abrufen, was für die Überwachung, Analyse und Optimierung von MySQL-Abfrageanweisungen sehr hilfreich ist.
Bei der täglichen Entwicklung stoßen wir häufig auf Situationen, in denen die Seite extrem langsam geöffnet wird. Nach der Beseitigung stellen wir fest, dass dies auf die Datenbank zurückzuführen ist. Um das spezifische SQL schnell zu finden, können wir die MySQL-Protokollierungsmethode verwenden.
-- SQL-Ausführungsprotokollierung aktivieren
setze global log_output='TABLE'; -- Ausgabe in Tabelle
set global log=ON; – Aktivieren Sie general_log für alle Befehlsausführungen, alle Anweisungen: erfolgreich und nicht erfolgreich.
set global log_slow_queries=ON; -- Aktivieren Sie die SQL-Protokollierung für langsame Abfragen slow_log, erfolgreiche Ausführung: langsame Abfrageanweisungen und Anweisungen, die keine Indizes verwenden
setze global long_query_time=0.1; -- Zeitlimit für langsame Abfragen (Sekunden)
set global log_queries_not_using_indexes=ON; -- SQL-Anweisungen protokollieren, die keine Indizes verwenden
-- SQL-Ausführungsdatensätze abfragen
select * from mysql.slow_log order by 1; -- Erfolgreich ausgeführt: langsame Abfrageanweisungen und Anweisungen, die keine Indizes verwenden
select * from mysql.general_log order by 1; – Alle Anweisungen: erfolgreich und nicht erfolgreich.
-- SQL-Ausführungsprotokoll schließen
globales Protokoll auf AUS setzen;
setze global log_slow_queries=OFF;
-- Beschreibung des Parameters „long_query_time“
- v4.0, 4.1, 5.0, v5.1 bis 5.1.20 (einschließlich): unterstützt keine langsame Abfrageanalyse im Millisekundenbereich (unterstützt Genauigkeit von 1–10 Sekunden);
– Version 5.1.21 und höher: unterstützt langsame Abfrageanalysen im Millisekundenbereich, z. B. 0,1;
- 6.0 bis 6.0.3: Unterstützt keine langsame Abfrageanalyse im Millisekundenbereich (unterstützt eine Genauigkeit von 1–10 Sekunden);
- 6.0.4 und höher: unterstützt langsame Abfrageanalyse auf Millisekundenebene;
Durch das im Protokoll aufgezeichnete SQL können Sie die spezifische Datei schnell lokalisieren und das SQL optimieren, um zu sehen, ob sich die Geschwindigkeit verbessert.
Dieser Artikel analysiert das Problem, dass MySQL-Datenbankserverabfragen immer langsamer werden, und schlägt entsprechende Lösungen vor. Die spezifische Analyse und Lösung sind wie folgt: Entwickler suchen häufig nach Anweisungen ohne Index oder Anweisungen ohne Begrenzung n. Diese Anweisungen haben große Auswirkungen auf die Datenbank ...
Dieser Artikel analysiert das Problem, dass MySQL-Datenbankserverabfragen immer langsamer werden, und schlägt entsprechende Lösungen vor. Die spezifische Analyse und Lösung sind wie folgt:
Entwickler müssen häufig nach Anweisungen ohne Index oder n-Grenzwert suchen. Diese Anweisungen können erhebliche Auswirkungen auf die Datenbank haben. Beispielsweise muss eine große Tabelle mit mehreren zehn Millionen Datensätzen vollständig gescannt werden, oder es wird kontinuierlich eine Dateisortierung durchgeführt, was sich auf die E/A der Datenbank und des Servers auswirkt. Dies ist die Situation in der Spiegelbibliothek.
Bei Online-Datenbanken gibt es neben Anweisungen ohne Index und Anweisungen ohne Begrenzung ein weiteres Problem: zu viele MySQL-Verbindungen. Lassen Sie uns zunächst einen Blick auf unsere bisherigen Überwachungspraktiken werfen.
1. Setzen Sie Open-Source-Systeme zur verteilten Überwachung ein, wie z. B. Zabbix, um täglich Daten zur Datenbank-E/A, CPU und Anzahl der Verbindungen zu erhalten.
2. Stellen Sie wöchentlich Leistungsstatistiken bereit, einschließlich Datenzunahme, iostat, vmstat und Datengröße
3. Mysql Slowlog-Sammlung, Liste der Top 10
Früher dachte ich, dass die Überwachung perfekt sei, aber nachdem ich die Überwachung der MySQL-Knotenprozesse implementiert hatte, stellte ich viele Nachteile fest. Der Nachteil des ersten Ansatzes: Zabbix ist zu groß und die Überwachung erfolgt nicht innerhalb von MySQL. Viele Daten sind nicht sehr gut vorbereitet und werden im Allgemeinen verwendet, um historische Daten zu überprüfen. Der Nachteil des zweiten Ansatzes: Da er nur einmal pro Woche ausgeführt wird, können viele Situationen nicht erkannt und keine Warnung ausgegeben werden. Der Nachteil des dritten Ansatzes: Wenn der Knoten viele Slowlogs hat, werden die Top10 bedeutungslos und Sie erhalten häufig regelmäßige Task-Anweisungen, die ausgeführt werden müssen. . Der Referenzwert ist nicht großartig. Wie können wir also diese Probleme lösen und abfragen? Zur Fehlerbehebung und zum Auffinden von Leistungsengpässen sind langsame Abfragen und Abfragen von MySQL ohne Indizes die am einfachsten zu findenden und zu lösenden Probleme.
OK, beginnen wir mit der Suche nach SQL-Anweisungen, deren Ausführung in MySQL nicht „angenehm“ ist. Methode 1: Ich verwende derzeit diese Methode. Haha, ich bevorzuge die Unmittelbarkeit dieser Methode. MySQL-Versionen 5.0 und höher unterstützen die Aufzeichnung von SQL-Anweisungen, die langsam ausgeführt werden. mysql> Variablen wie „long%“ anzeigen; Hinweis: Diese long_query_time wird verwendet, um zu definieren, wie viele Sekunden eine langsamere Abfrage als „langsame Abfrage“ gilt.
+-----------------+------------+
| Variablenname | Wert |
+-----------------+------------+
| lange Abfragezeit | 10.000000 |
+-----------------+------------+
1 Zeile im Satz (0,00 Sek.)
mysql> set long_query_time=1; Hinweis: Ich habe es auf 1 gesetzt, was bedeutet, dass jede Abfrage, deren Ausführung länger als 1 Sekunde dauert, als langsame Abfrage betrachtet wird.
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
mysql> Variablen wie „slow%“ anzeigen;
+---------------------+---------------+
| Variablenname | Wert |
+---------------------+---------------+
| langsame Startzeit | 2 |
| slow_query_log | ON | Hinweis: Ob die Protokollierung geöffnet werden soll
| slow_query_log_file | /tmp/slow.log | Hinweis: Wo wird es eingestellt?
+---------------------+---------------+
3 Zeilen im Satz (0,00 Sek.)
mysql> set global slow_query_log='ON' Hinweis: Aktivieren Sie die Protokollierung
Sobald die Variable slow_query_log auf ON gesetzt ist, beginnt MySQL sofort mit der Protokollierung.
Die Anfangswerte der oben genannten globalen MySQL-Variablen können in /etc/my.cnf festgelegt werden.
lange_Abfragezeit=1
slow_query_log_file=/tmp/slow.log Methode 2: Befehl mysqldumpslow /Pfad/mysqldumpslow -sc -t 10 /tmp/slow-log
Dadurch werden die 10 wichtigsten SQL-Anweisungen mit den meisten Datensätzen ausgegeben. Dabei gilt:
-s gibt die Sortiermethode an. c, t, l und r werden jeweils nach der Anzahl der Datensätze, der Zeit, der Abfragezeit und der Anzahl der zurückgegebenen Datensätze sortiert. ac, at, al und ar geben die entsprechende umgekehrte Sortierung an.
-t, was „oberste n“ bedeutet, gibt an, wie viele Datensätze zurückgegeben werden.
-g, gefolgt von einem passenden Muster für einen regulären Ausdruck, ohne Berücksichtigung der Groß-/Kleinschreibung;
Zum Beispiel
/Pfad/mysqldumpslow -sr -t 10 /tmp/slow-log
Holen Sie sich die 10 wichtigsten Abfragen, die die meisten Datensätze zurückgeben.
/Pfad/mysqldumpslow -st -t 10 -g "left join" /tmp/slow-log
Holen Sie sich die ersten 10 Abfrageanweisungen mit Left Joins, sortiert nach Zeit.
Abschließend fassen wir die Vorteile der Knotenüberwachung zusammen
1. Leichte Überwachung in Echtzeit, die an die tatsächlichen Bedingungen angepasst und geändert werden kann
2. Zum Filtern der Anweisungen, die ausgeführt werden müssen, wird ein Filter eingerichtet.
3. Entdecken Sie diese ungenutzten Indizes oder illegalen Abfragen rechtzeitig. Obwohl die Verarbeitung dieser langsamen Anweisungen einige Zeit in Anspruch nimmt, lohnt es sich, um Datenbankabstürze zu vermeiden.
4. Wenn zu viele Verbindungen zur Datenbank bestehen, speichert das Programm automatisch die Prozessliste der aktuellen Datenbank. Dies ist für DBAs ein großartiges Tool, um die Ursache zu finden.
5. Wenn Sie mysqlbinlog zur Analyse verwenden, können Sie einen klaren Zeitraum erhalten, in dem der Datenbankstatus abnormal ist. Einige Leute schlagen vor, dass wir die MySQL-Konfigurationsdatei festlegen. Beim Anpassen von tmp_table_size habe ich einige andere Parameter gefunden
Qcache_queries_in_cache Die Anzahl der im Cache registrierten Abfragen
Qcache_inserts Die Anzahl der Abfragen, die dem Cache hinzugefügt wurden
Qcache_hits Die Anzahl der Cache-Samples
Qcache_lowmem_prunes Die Anzahl der Abfragen, die aufgrund von Speichermangel aus dem Cache entfernt wurden.
Qcache_not_cached Anzahl der Abfragen, die nicht zwischengespeichert wurden (entweder nicht zwischenspeicherbar oder aufgrund von QUERY_CACHE_TYPE)
Qcache_free_memory Die Gesamtmenge an freiem Speicher im Abfragecache
Qcache_free_blocks Die Anzahl der freien Speicherblöcke im Abfragecache
Qcache_total_blocks Die Gesamtzahl der Blöcke im Abfragecache
Qcache_free_memory kann einige häufig verwendete Abfragen zwischenspeichern. Wenn es sich um häufig verwendetes SQL handelt, wird es in den Speicher geladen. Dadurch wird die Geschwindigkeit des Datenbankzugriffs erhöht |
