MySQL-Abfrageoptimierung: Ursachen und Lösungen für langsame Abfragen

Freunde, die in der Entwicklung tätig sind, insbesondere diejenigen, die mit MySQL zu tun haben, werden manchmal auf MySQL-Abfragen stoßen, die sehr langsam sind. Natürlich meine ich damit große Datenmengen im Millionen- oder Zehnmillionenbereich, nicht nur Dutzende von Einträgen.

Werfen wir einen Blick auf die Lösung für langsame Abfragen

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. Apropos, werfen wir einen Blick auf unsere bisherigen Überwachungspraktiken:

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. Bereitstellung wöchentlicher Leistungsstatistiken, einschließlich Datenzunahme, iostat, vmstat und Datengröße
3. Mysql Slowlog-Sammlung, Liste der Top 10

Früher dachte ich, diese Überwachungen wären perfekt, aber nachdem ich jetzt die MySQL-Knotenprozessüberwachung implementiert habe, habe ich viele Nachteile entdeckt.

• Nachteile 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 zum Überprüfen historischer Daten verwendet.
• Nachteile der zweiten Methode: Da sie nur einmal pro Woche ausgeführt wird, können viele Situationen nicht entdeckt und gemeldet werden.
• Nachteile des dritten Ansatzes: Wenn sich auf einem Knoten viele Slowlogs befinden, wird Top10 bedeutungslos und Sie erhalten häufig Anweisungen für periodische Aufgaben, die ausgeführt werden müssen. . Kaum Referenzwert

Wie also lösen und hinterfragen wir diese Probleme?

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 : 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 aktiviert werden soll | slow_query_log_file | /tmp/slow.log | Hinweis: wo soll es eingestellt werden+---------------------+---------------+
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.
long_query_time=1
slow_query_log_file=/tmp/slow.log

Methode 2 : Befehl mysqldumpslow

/path/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 sortieren nach Anzahl der Datensätze, Zeit, Abfragezeit bzw. Anzahl der zurückgegebenen Datensätze. ac , at , al und ar geben die entsprechende umgekehrte Sortierung an.
-t bedeutet oberste n, also wie viele Datensätze zurückgegeben werden.
-g , kann von einem regulären Ausdruck gefolgt werden, der das Muster der Übereinstimmung enthält, ohne Berücksichtigung der Groß-/Kleinschreibung;
Zum Beispiel
/path/mysqldumpslow -sr -t 10 /tmp/slow-log
Holen Sie sich die 10 wichtigsten Abfragen, die die meisten Datensätze zurückgeben.
/path/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. Für DBAs ist dies ein großartiges Tool zum Auffinden der Ursache.
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 einrichten

Beim Anpassen von tmp_table_size wurden 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 Cache-Beispielanzahl
Qcache_lowmem_prunes Die Anzahl der Abfragen, die aufgrund von Speichermangel aus dem Cache entfernt wurden
Qcache_not_cached Die Anzahl der Abfragen, die nicht zwischengespeichert wurden (entweder weil sie nicht zwischengespeichert werden konnten oder aufgrund von QUERY_CACHE_TYPE)
Qcache_free_memory Abfrage-Cache Gesamter freier Speicher
Qcache_free_blocks Die Anzahl der freien Speicherblöcke im Abfragecache
Qcache_total_blocks fragt die Gesamtzahl der Blöcke im Cache ab
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.

Damit ist dieser Artikel über MySQL-Abfrageoptimierung, die Ursachen für langsame Abfragen und Lösungen abgeschlossen. Weitere relevante Inhalte zur MySQL-Abfrageoptimierung finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den verwandten Artikeln weiter unten. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!