MySQL Slow Query Troubleshooting: Eine Schritt-für-Schritt-Anleitung

MySQL Slow Query Troubleshooting beginnt mit einer unbequemen Regel: Rate nicht allein anhand des Abfragetextes. Eine Abfrage, die hässlich aussieht, kann harmlos sein, weil sie nur einmal am Tag läuft. Eine Abfrage, die einfach aussieht, kann die Datenbank ruinieren, weil sie tausende Male pro Minute läuft, zu viele Zeilen scannt oder auf Sperren wartet.

Der nützliche Workflow ist auf die beste Weise langweilig. Erfasse echte langsame Abfragen, gruppiere sie nach Kosten, untersuche den Ausführungsplan, ändere eine Sache und messe erneut. Das verhindert, dass du wahllos Indizes hinzufügst, globale Einstellungen blind änderst oder MySQL die Schuld gibst, wenn die Anwendung ein vermeidbares Abfragemuster sendet.

Ich beginne normalerweise mit drei Fragen:

Welche Abfrage schadet den Benutzern, nicht nur, welche sieht verdächtig aus?
Wird die Zeit mit dem Lesen von Zeilen, Sortieren, Warten auf Sperren oder Warten auf die Anwendung verbracht?
Kann ich die Behebung mit EXPLAIN, Zeitmessungen und aktuellen Slow-Log-Daten nachweisen?

Beginne mit dem Slow Query Log

Das MySQL Slow Query Log zeichnet Anweisungen auf, die den konfigurierten Schwellenwert überschreiten. Laut MySQL-Handbuch ist das Log standardmäßig deaktiviert, long_query_time beträgt standardmäßig 10 Sekunden, und eine Anweisung muss normalerweise mindestens so lange laufen und mindestens min_examined_row_limit Zeilen untersuchen, bevor sie protokolliert wird. Wenn log_queries_not_using_indexes aktiviert ist, kann MySQL auch Anweisungen protokollieren, die keine Indizes für Zeilensuchen verwenden. Diese Option ist während der Diagnose nützlich, kann aber auf stark ausgelasteten Systemen viel Rauschen erzeugen.

Eine praktische Startkonfiguration sieht so aus:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_output = FILE

Auf vielen Produktionssystemen ist 1 Sekunde ein vernünftiger erster Durchlauf. Für eine latenzempfindliche API kannst du sie vorübergehend auf 0,5 oder 0,2 senken. Tu das mit einem Plan und einer Überwachung des Speicherplatzes. Eine stark frequentierte Datenbank kann eine überraschende Menge an Slow-Log-Daten schreiben, sobald der Schwellenwert sinkt.

Du kannst die aktiven Einstellungen von einer MySQL-Sitzung aus überprüfen:

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';
SHOW VARIABLES LIKE 'min_examined_row_limit';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

Für eine vorübergehende Untersuchung kannst du das Log aktivieren, ohne die Konfigurationsdatei zu bearbeiten:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Denke daran, dass SET GLOBAL-Änderungen möglicherweise nicht über einen Neustart hinweg erhalten bleiben, es sei denn, du persistierst sie auch über deinen normalen Konfigurationsprozess. Auf MySQL 8 verwenden einige Teams SET PERSIST, aber ich ziehe es immer noch vor, die beabsichtigte Einstellung in der Konfigurationsverwaltung festzuhalten, damit der nächste Operator sie sehen kann.

Wenn du log_queries_not_using_indexes aktivierst, solltest du auch log_throttle_queries_not_using_indexes setzen, damit ein lauter Endpunkt das Log nicht überflutet. MySQL unterstützt diese Drosselung, weil die Protokollierung ohne Index schnell anwachsen kann.

Gruppiere das Log, bevor du einzelne Abfragen liest

Rohe Slow Logs sind repetitiv. Du kannst dieselbe Abfrage hunderte Male mit verschiedenen IDs sehen. Das Lesen der Datei von oben nach unten verschwendet Zeit und lässt seltene, beängstigende Abfragen wichtiger erscheinen als häufige, teure.

Beginne mit mysqldumpslow, das in vielen Umgebungen mit MySQL-Installationen ausgeliefert wird:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

Das fragt nach den Top-Ten-Mustern, sortiert nach Abfragezeit. Die genauen Flags variieren je nach Version und Plattform, also überprüfe mysqldumpslow --help, wenn sich dein Befehl anders verhält. Nützliche Sortierungen umfassen Gesamtzeit, Durchschnittszeit, Sperrzeit und untersuchte Zeilen.

Für Produktionsuntersuchungen ist pt-query-digest aus dem Percona Toolkit oft besser, weil es eine reichhaltigere Gruppierung und Perzentil-Details bietet. Das Tool ist keine Magie; es erspart dir lediglich, von Hand zu rechnen. Wichtig ist, nach Auswirkungen zu ordnen. Eine Abfrage, die einmal pro Nacht acht Sekunden dauert, kann weniger dringend sein als eine Abfrage, die 120 Millisekunden dauert, aber 600 Mal pro Sekunde läuft.

Beim Lesen der gruppierten Ausgabe solltest du nach Mustern suchen:

Hohe Gesamtzeit: wahrscheinlich benutzersichtbar oder ressourcenintensiv.
Hohe Anzahl: oft eine Anwendungsschleife oder fehlender Cache.
Hohe untersuchte Zeilen bei niedrigen gesendeten Zeilen: normalerweise ein Indexierungs- oder Filterproblem.
Hohe Sperrzeit: möglicherweise eine Transaktion, Schreibkonkurrenz, Metadatensperre oder ein DDL-Problem.

Gehe nicht davon aus, dass hohe Rows_examined immer schlecht ist. Reporting-Abfragen und Batch-Jobs scannen manchmal absichtlich. Die Frage ist, ob der Scan zum Job passt und ob er zur richtigen Zeit stattfindet.

Reproduziere eine Abfrage sicher

Wähle ein Abfragemuster aus und besorge eine echte Stichprobe mit Parametern. Wenn das Slow Log Literale normalisiert hat, finde die ursprüngliche Abfrage in Anwendungslogs, APM-Traces oder dem rohen Slow-Log-Eintrag.

Bevor du sie manuell ausführst, überprüfe den Schadensradius. Ein langsames SELECT auf einem Replikat ist normalerweise sicher. Ein langsames UPDATE in der Produktion ist nichts, was man beiläufig wiederholen sollte. Für Schreibabfragen überprüfe zuerst den Plan und das Transaktionsmuster oder teste gegen eine Staging-Kopie mit realistischen Daten.

Eine nützliche Notiz für jede Abfrage sieht so aus:

Endpunkt: GET /customers/123/orders
Abfragemuster: Bestellungen nach Kunde und Status, neueste zuerst
Beobachtet: 1,8s Durchschnitt, 420k Zeilen untersucht, 20 Zeilen gesendet
Tabellengröße: 12M Zeilen
Erwartete Ergebnisgröße: eine Seite Bestellungen
Verdacht: fehlender zusammengesetzter Index für customer_id, status, created_at

Diese Notiz hält die Arbeit an einen echten Benutzerpfad gebunden, nicht an ein zufälliges SQL-Snippet.

Verwende `EXPLAIN`, lies es dann wie ein Operator

Führe EXPLAIN für die langsame Abfrage aus:

EXPLAIN
SELECT id, customer_id, status, created_at, total
FROM orders
WHERE customer_id = 123
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Für MySQL 8 kann EXPLAIN ANALYZE die Abfrage ausführen und tatsächliche Timing-Informationen anzeigen. Verwende es vorsichtig bei teuren Abfragen, da es die Anweisung wirklich ausführt. Für einfache SELECT-Abfragen in einer kontrollierten Umgebung kann es sehr hilfreich sein.

Die Spalten, die ich zuerst überprüfe, sind type, possible_keys, key, rows, filtered und Extra.

type sagt dir das Zugriffsmuster. const, eq_ref, ref und range sind normalerweise gute Zeichen. index bedeutet, dass MySQL einen Index scannt, was immer noch zu viel Arbeit sein kann. ALL bedeutet einen vollständigen Tabellenscan. Ein vollständiger Scan ist bei einer winzigen Tabelle nicht automatisch falsch, aber bei einer heißen Tabelle mit Millionen von Zeilen ist er verdächtig.

key zeigt den Index, den MySQL ausgewählt hat. Wenn possible_keys einen vielversprechenden Index auflistet, key aber anders ist, hält der Optimator den anderen Index möglicherweise für billiger. Das kann aufgrund schlechter Selektivität, veralteter Statistiken oder eines Index passieren, der nicht zu Filter und Sortierung zusammen passt.

rows ist eine Schätzung, kein Versprechen. Wenn die Schätzung stark abweicht, führe ANALYZE TABLE während eines geeigneten Wartungsfensters aus oder überprüfe, ob die Datenverteilung verzerrt ist.

Extra erzählt oft die Geschichte. Using filesort bedeutet, dass MySQL einen separaten Sortierschritt benötigt; es bedeutet nicht unbedingt Sortierung auf der Festplatte, aber es ist eine Überprüfung wert, wenn die Ergebnismenge groß ist. Using temporary tritt oft bei Gruppierungen, DISTINCT-Abfragen oder komplexen Sortierungen auf. Using index kann gut sein, weil die Abfrage aus dem Index erfüllt wird, ohne Tabellenzeilen zu lesen.

Behebe Indizes mit der vollständigen Abfrageform im Hinterkopf

Die häufigste Slow-Query-Behebung ist nicht "Füge einen Index zur Spalte in der WHERE-Klausel hinzu". Die bessere Regel ist: Erstelle einen Index, der der Art und Weise entspricht, wie die Abfrage filtert, verknüpft, sortiert und Zeilen begrenzt.

Für die obige Bestellabfrage könnte ein einzelner Index auf customer_id helfen, aber MySQL könnte immer noch viele Zeilen für diesen Kunden sortieren müssen. Ein zusammengesetzter Index ist oft nützlicher:

CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at);

Wenn die Abfrage nach den neuesten zuerst sortiert, kann MySQL den Index oft in umgekehrter Reihenfolge scannen. In MySQL 8 kannst du auch absteigende Indizes definieren, wenn das zu einem größeren Muster passt:

CREATE INDEX idx_orders_customer_status_created_desc
ON orders (customer_id, status, created_at DESC);

Die Spaltenreihenfolge ist wichtig. Setze Gleichheitsfilter zuerst, dann Bereichs- oder Sortierspalten, wenn das zur Abfrage passt. Zum Beispiel ist bei WHERE customer_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20 customer_id, status, created_at normalerweise nützlicher als created_at, customer_id, status.

Füge nicht jeden Index hinzu, der hilfreich erscheint. Indizes beschleunigen Lesevorgänge, verlangsamen aber Schreibvorgänge und verbrauchen Speicher. Wenn die Tabelle viele Einfügungen oder Aktualisierungen erhält, hat ein neuer zusammengesetzter Index echte Kosten. Überprüfe zuerst vorhandene Indizes:

SHOW INDEX FROM orders;

Manchmal ist die richtige Antwort, zwei schwache Indizes durch einen besseren zusammengesetzten Index zu ersetzen, nicht alle drei zu behalten.

Schreibe Abfragen um, die die Indexnutzung blockieren

Einige langsame Abfragen sind langsam, weil sie indizierte Werte hinter Funktionen oder Mustern verstecken, die MySQL nicht effizient nutzen kann.

Diese Version ist üblich und schmerzhaft:

SELECT *
FROM orders
WHERE YEAR(created_at) = 2026;

Wenn created_at indiziert ist, kann das Verpacken in YEAR() eine normale Bereichssuche verhindern. Schreibe das Prädikat stattdessen als Bereich:

SELECT id, customer_id, status, created_at, total
FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01';

Die gleiche Idee gilt für führende Wildcard-Suchen:

WHERE email LIKE '%@example.com'

Ein normaler B-Baum-Index kann nicht in die Mitte einer Zeichenkette springen. Wenn Suffix-Suchen wichtig sind, benötigst du möglicherweise eine generierte Spalte, ein separates normalisiertes Feld oder ein Suchsystem, das für diesen Anwendungsfall entwickelt wurde.

Achte auch auf SELECT *. Es sieht während der Entwicklung harmlos aus, kann aber MySQL zwingen, Tabellenzeilen zu lesen, wenn eine kleinere Projektion einen abdeckenden Index verwenden könnte. Es sendet auch unnötige Daten über das Netzwerk.

Überprüfe Sperren, wenn der Abfrageplan in Ordnung aussieht

Eine Abfrage kann einen anständigen Plan haben und trotzdem langsam sein, weil sie wartet. Die Lock_time im Slow Log kann dich in diese Richtung weisen, erklärt aber nicht jede Art von Wartezeit. Wenn Benutzer zufällige Pausen melden, überprüfe aktive Sitzungen:

SHOW PROCESSLIST;

Auf MySQL 8 können die Performance Schema- und sys-Schema-Ansichten bessere Details liefern, je nachdem, wie der Server konfiguriert ist. Für einen schnellen Blick überprüfe ich oft lang laufende Transaktionen und blockierte Anweisungen, bevor ich Indizes ändere.

Ein reales Beispiel: Ein UPDATE orders SET status = ? WHERE id = ? sollte schnell sein. Wenn es im Slow Log mit einer Primärschlüsselsuche erscheint, könnte das Problem eine Transaktion sein, die die Zeile gesperrt hat, während sie nicht zusammenhängende Arbeit erledigt. Die Behebung ist kein weiterer Index. Die Behebung ist die Verkürzung der Transaktion und das Verschieben langsamer externer Aufrufe außerhalb davon.

Metadatensperren können eine ähnliche Falle darstellen. Eine Migration, die ALTER TABLE ausführt, kann auf eine alte Transaktion warten, während sich neue Abfragen hinter dem ausstehenden DDL stauen. Das Slow Query Log zeigt Symptome, aber die Ursache ist das Deployment-Verhalten.

Optimiere Servereinstellungen erst nach der Abfragearbeit

Konfiguration ist wichtig, aber es ist leicht, sie als erste Antwort zu überbeanspruchen. Wenn eine Abfrage fünf Millionen Zeilen scannt, um zehn zurückzugeben, kann eine Erhöhung des Arbeitsspeichers nur dazu führen, dass der schlechte Plan weniger wehtut.

Für InnoDB-lastige Systeme ist innodb_buffer_pool_size die erste Einstellung, die überprüft werden sollte. Auf einem dedizierten MySQL-Server wird sie oft auf einen großen Teil des Arbeitsspeichers gesetzt, aber der richtige Wert hängt davon ab, was sonst noch auf dem Host läuft, der Datensatzgröße und der Arbeitslast. Kopiere keinen Prozentsatz blind aus einem Blogbeitrag.

Überprüfe auch, ob die Datenbank auf die Festplatte wartet. Wenn der Arbeitssatz nicht in den Speicher passt oder der Speicher gesättigt ist, können selbst gut indizierte Abfragen ins Stocken geraten. Kombiniere die Abfrageüberprüfung mit Host-Metriken: CPU, Festplattenlatenz, IOPS, Speicherdruck und Verbindungsanzahl.

Verbindungspools können langsame Abfragen schlimmer erscheinen lassen. Wenn ein Endpunkt zu viele langsame Anweisungen auslöst, füllt sich der Pool, nicht zusammenhängende Anfragen warten auf Verbindungen, und die gesamte App fühlt sich kaputt an. In diesem Fall ist die Behebung der Abfrage immer noch die Hauptaufgabe, aber Pool-Grenzen und Timeouts bestimmen, wie anmutig das System ausfällt.

Beweise die Behebung

Nachdem du einen Index hinzugefügt oder eine Abfrage umgeschrieben hast, führe EXPLAIN erneut aus. Du möchtest weniger geschätzte Zeilen, einen besser ausgewählten Schlüssel und weniger teure zusätzliche Schritte sehen. Teste dann die tatsächliche Abfrage mit realistischen Parametern.

Höre nicht bei einem schnellen Durchlauf auf. Ein warmer Cache kann Probleme verbergen. Probiere häufige, große und schwierige Fälle aus:

Ein Kunde mit vielen Bestellungen.
Ein Kunde ohne passende Bestellungen.
Ein Datumsbereich, der eine geschäftige Periode abdeckt.
Ein Statuswert, der zu den meisten Zeilen passt.

Beobachte dann das Slow Log nach dem Deployment. Das beste Ergebnis ist nicht "die Abfrage sah im Staging besser aus". Das beste Ergebnis ist, dass das Abfragemuster aus den Top-Verursachern verschwindet, der CPU- oder I/O-Druck sinkt und der Benutzerpfad schneller ist.

MySQL Slow Query Troubleshooting ist hauptsächlich disziplinierte Beweissammlung. Aktiviere das Log mit vernünftigen Schwellenwerten, gruppiere die teuren Muster, untersuche den Plan, behebe die Abfrageform und validiere mit frischen Daten. Diese Gewohnheit verhindert sowohl Unter- als auch Überbehebung, was genau das ist, was du willst, wenn die Datenbank bereits unter Druck steht.