MySQL EXPLAIN erklärt: So lesen Sie Ausführungspläne und optimieren SQL‑Abfragen

1. Was ist MySQL EXPLAIN? Warum sollten Sie es verwenden?

Was ist EXPLAIN? Ein Befehl zur Visualisierung von Ausführungsplänen

In MySQL ist EXPLAIN ein Befehl, der verwendet wird, um zu visualisieren, wie eine SQL‑Abfrage ausgeführt wird. Er ist besonders hilfreich, um zu verstehen, wie Daten in SELECT‑Anweisungen abgerufen werden, und er zeigt den Ausführungsplan der Abfrage an.

Zum Beispiel, wenn Sie eine Abfrage wie SELECT * FROM users WHERE age > 30 ausführen, ermöglicht EXPLAIN Ihnen, interne Details zu sehen, wie welchen Index MySQL verwendet und in welcher Reihenfolge Tabellen gescannt werden.

Die Verwendung ist einfach — fügen Sie einfach EXPLAIN am Anfang Ihrer Abfrage hinzu.

EXPLAIN SELECT * FROM users WHERE age > 30;

Wenn Sie es so schreiben, werden mehrere Spalten, die den Ausführungsplan der Abfrage beschreiben, angezeigt. Jeder Punkt wird in den folgenden Abschnitten ausführlich erklärt.

Warum Sie es verwenden sollten: Langsame Abfrageursachen sichtbar machen

Ein häufiger Fehler, den viele Entwickler machen, ist die Annahme, dass „wenn das SQL funktioniert, es kein Problem gibt.“ Allerdings kann langsame Abfrageausführung die Gesamtleistung einer Anwendung negativ beeinflussen.

In Systemen, die große Datenmengen verarbeiten, kann selbst eine einzige ineffiziente Abfrage zum Engpass werden und erhebliche Last auf den Server legen.

Hier wird EXPLAIN extrem nützlich. Durch die Überprüfung des Ausführungsplans können Sie klar erkennen, ob ein vollständiger Tabellenscan durchgeführt wird oder ob Indizes ordnungsgemäß genutzt werden.

Mit anderen Worten, die Verwendung von EXPLAIN ermöglicht es Ihnen, Leistungsengpässe zu identifizieren und zu bestimmen, wie Sie sie optimieren können. Die Wirksamkeit von Indizes wird insbesondere beim Analysieren der EXPLAIN‑Ausgabe deutlich klarer.

Von EXPLAIN unterstützte SQL‑Anweisungen (SELECT, UPDATE usw.)

EXPLAIN funktioniert nicht nur mit SELECT‑Anweisungen, sondern auch mit den folgenden SQL‑Anweisungen:

• SELECT
• DELETE
• INSERT
• REPLACE
• UPDATE

Zum Beispiel, wenn Sie eine DELETE‑Anweisung auf einem großen Datensatz ausführen und Indizes nicht korrekt verwendet werden, kann MySQL einen vollständigen Tabellenscan durchführen, was die Ausführungszeit erheblich erhöht. Um solche Probleme zu vermeiden, ist es sehr effektiv, den Ausführungsplan mit EXPLAIN zu prüfen, bevor Sie DELETE‑ oder UPDATE‑Anweisungen ausführen.

Abhängig von Ihrer MySQL‑Version können Sie auch EXPLAIN ANALYZE verwenden, das noch detailliertere Ausführungsinformationen liefert. Dies wird später im Artikel behandelt.

2. Verständnis der EXPLAIN‑Ausgabespalten (mit illustriertem Bild)

Auflistung und Erklärung der grundlegenden Ausgabespalten

Die EXPLAIN‑Ausgabe enthält die folgenden Spalten (leicht unterschiedlich je nach MySQL‑Version):

Unter diesen sind die vier wichtigsten Spalten für die Leistungsoptimierung type / key / rows / Extra.

Wie man die vier Schlüsselspalten liest: type / key / rows / Extra

1. type (Zugriffsmethode)

Diese Spalte gibt an, wie MySQL auf die Tabelle zugreift. Sie wirkt sich direkt auf die Leistung aus.

Wenn type = ALL, bedeutet das, dass kein Index verwendet wird und alle Zeilen gescannt werden — die langsamste Zugriffsmethode. Idealerweise sollten Sie Abfragen zu ref oder const optimieren.

2. key (Verwendeter Index)

Diese Spalte zeigt den Namen des tatsächlich verwendeten Index an.
Wenn nichts angezeigt wird, verwendet die Abfrage wahrscheinlich keinen Index.

3. rows (Geschätzte zu scannende Zeilen)

Dies zeigt, wie viele Zeilen MySQL voraussichtlich scannen wird. Je größer die Zahl, desto länger ist in der Regel die Ausführungszeit. Das Ziel ist, Ihre Abfrage so zu optimieren, dass rows so nahe wie möglich an 1 liegt.

4. Extra (Zusätzliche Informationen)

Die Extra‑Spalte enthält zusätzliche Details wie Sortieroperationen oder die Verwendung temporärer Tabellen.

Wenn Sie Using temporary oder Using filesort sehen, sollten Sie Ihre SQL‑Anweisung oder das Indexdesign überprüfen.

[Illustration] Sample EXPLAIN Output

EXPLAIN SELECT * FROM users WHERE age > 30;

In diesem Beispiel existiert zwar der Index (age_index), er wird jedoch nicht tatsächlich verwendet, was zu ALL (vollständiger Tabellenscan) führt. Das weist auf Optimierungspotenzial hin.

3. Lernen durch Beispiele: Wie man EXPLAIN verwendet und Ergebnisse interpretiert

Beispiel 1: EXPLAIN-Ausgabe für eine einfache SELECT-Abfrage (mit Erklärung)

Beginnen wir mit einer einfachen SELECT-Abfrage auf einer einzelnen Tabelle.

EXPLAIN SELECT * FROM users WHERE age > 30;

Angenommen, die EXPLAIN-Ausgabe sieht folgendermaßen aus:

Erklärung:

• type: ALL → Vollständiger Tabellenscan. Kein Index wird verwendet.
• key: NULL → Es wird tatsächlich kein Index verwendet.
• rows: 5000 → MySQL schätzt, dass etwa 5.000 Zeilen gescannt werden.

Wie man verbessert:

Durch das Hinzufügen eines Indexes zur Spalte age können Sie die Abfrageleistung erheblich verbessern.

CREATE INDEX idx_age ON users(age);

Wenn Sie EXPLAIN erneut ausführen, sollten Sie sehen, dass sich type zu range oder ref ändert, was bestätigt, dass der Index jetzt verwendet wird.

Beispiel 2: Analyse der EXPLAIN-Ausgabe für eine Abfrage mit JOIN

Als Nächstes schauen wir uns ein Beispiel an, das mehrere Tabellen JOINt.

EXPLAIN
SELECT orders.id, users.name
FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.age > 30;

Beispielausgabe:

Erklärung:

• Die Tabelle users führt einen Vollscan (ALL) durch, daher ist dies der zu verbessernde Teil.
• Währenddessen verwendet die Tabelle orders einen Index mit ref, was effizient ist.

Optimierungspunkte:

• Das Hinzufügen eines Indexes auf users.age kann das Scannen der Tabelle users beschleunigen.
• Der Schlüssel ist, Indexe so zu entwerfen, dass die WHERE‑Klausel Zeilen vor dem JOIN filtern kann.

Wenn Indexe nicht verwendet werden (Schlechtes Beispiel → Gutes Beispiel)

Schlechtes Beispiel: WHERE‑Klausel verwendet eine Funktion

SELECT * FROM users WHERE DATE(created_at) = '2024-01-01';

Bei einer solchen Abfrage wird der Index unbrauchbar, weil die DATE()‑Funktion den Spaltenwert transformiert und MySQL daran hindert, den Index effizient zu nutzen.

Verbessertes Beispiel: Bereich angeben, ohne eine Funktion zu verwenden

SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

Dies ermöglicht den Index auf der Spalte created_at und erlaubt MySQL, Daten effizient abzurufen.

Fazit: Verwenden Sie reale EXPLAIN-Beispiele, um die Leistung zu diagnostizieren

Durch die Analyse von EXPLAIN-Ausgaben in realen Abfragen können Sie klar erkennen, wo die Engpässe liegen und wie man sie optimiert.

• ALL → Vollständiger Scan. Erwägen Sie das Hinzufügen oder Anpassen von Indexen.
• key = NULL → Index nicht verwendet. Erfordert Untersuchung.
• Extra enthält Using temporary → Leistungswarnung.
• Die Verwendung von Funktionen oder Berechnungen in Bedingungen kann die Indexnutzung deaktivieren.

Wenn Sie diese Punkte berücksichtigen, können Sie die Abfrageleistung mit EXPLAIN kontinuierlich verbessern.

4. Praktische Abfrageoptimierungstechniken basierend auf EXPLAIN-Ergebnissen

Grundlagen des Indexdesigns, um „type: ALL“ zu vermeiden

Wenn EXPLAIN type: ALL anzeigt, bedeutet das, dass MySQL einen vollständigen Tabellenscan durchführt. Dies ist ein sehr teurer Vorgang und wird zu einem Hauptengpass für Tabellen mit Tausenden bis Millionen von Zeilen.

Wie man das vermeidet:

• Fügen Sie Indexe zu Spalten hinzu, die in der WHERE‑Klausel verwendet werden

  CREATE INDEX idx_age ON users(age);
  

• Wenn Sie mehrere Bedingungen haben, erwägen Sie einen zusammengesetzten Index

  CREATE INDEX idx_status_created ON orders(status, created_at);
  

• Vermeiden Sie LIKE‑Muster, die nicht mit einem Präfix beginnen

  -- Bad example (index won’t work)
  WHERE name LIKE '%tanaka%'
  
  -- Good example (index may work)
  WHERE name LIKE 'tanaka%'
  

Was „Extra: Using temporary“ bedeutet und wie man es behebt

Wenn die Spalte Extra „Using temporary“ anzeigt, bedeutet das, dass MySQL intern eine temporäre Tabelle erstellt, um die Abfrage zu verarbeiten. Dies tritt häufig auf, wenn Operationen wie GROUP BY oder ORDER BY nicht allein durch Indexe abgewickelt werden können, sodass MySQL temporären Speicher verwenden muss, um die Daten manuell zu organisieren.

Wie man es behebt:

• Wenden Sie Indexe auf Spalten an, die in GROUP BY und ORDER BY verwendet werden

  CREATE INDEX idx_group_col ON sales(department_id);
  

• Entfernen Sie unnötiges Sortieren oder GROUP BY aus Ihrem SQL
• Verwenden Sie LIMIT oder Unterabfragen, um die Ziel‑Datenmenge zu reduzieren

Verstehen Sie, was „rows“ und „key“ Ihnen sagen, um die Leistung zu verbessern

Die rows‑Spalte gibt an, wie viele Zeilen MySQL voraussichtlich aus der Tabelle lesen muss. Zum Beispiel kann eine Abfrage, die rows = 100000 zeigt, die Leistung erheblich beeinträchtigen.

Wenn dieser Wert groß ist, müssen Sie wahrscheinlich Indexes anwenden, die die Anzahl der gescannten Zeilen reduzieren oder Ihre Bedingungen umschreiben.

Andererseits zeigt die key‑Spalte den tatsächlich verwendeten Index. Wenn sie NULL ist, ist das eine Warnung, dass kein Index verwendet wird.

Optimierungs‑Checkliste:

• Wenn rows groß ist → Sind Ihre Filter effektiv? Werden Indizes ordnungsgemäß verwendet?
• Wenn key = NULL → Verwenden Sie Muster in WHERE/JOIN, die die Indexnutzung verhindern?

Machen Sie EXPLAIN und Optimierung zu einer Gewohnheit

Um Abfragen effektiv zu optimieren, besteht der grundlegende Ansatz darin, diesen Zyklus zu wiederholen: schreiben → mit EXPLAIN prüfen → verbessern → erneut prüfen.

Behalten Sie diesen Arbeitsablauf im Hinterkopf:

1. Schreiben Sie die Abfrage normal
2. Prüfen Sie den Ausführungsplan mit EXPLAIN
3. Überprüfen Sie type, key, rows und Extra
4. Wenn es einen Engpass gibt, überarbeiten Sie Indizes oder schreiben Sie die Abfrage neu
5. Führen Sie EXPLAIN erneut aus, um die Verbesserungen zu bestätigen

Die Abfrageleistung wird nicht nur von Indizes, sondern auch davon beeinflusst, wie die Abfrage selbst geschrieben ist. Einfache Vergleiche (statt Funktionen) und klare Bedingungen können überraschend effektiv sein.

5. Visuelle Analyse mit MySQL Workbench Visual EXPLAIN

Ausführungspläne visuell mit einem GUI‑Tool prüfen

MySQL Workbench ist ein GUI‑Tool, das auf MySQL‑Administration und -Entwicklung spezialisiert ist. Einer seiner größten Vorteile ist, dass es Ausführungspläne visuell darstellen kann, die oft schwer im Terminal‑Ausgabe zu lesen sind.

Mit Visual EXPLAIN können Sie die folgenden Informationen in einer Baumstruktur überprüfen:

• Zugriffsreihenfolge jeder Tabelle
• Verwendeter JOIN‑Typ
• Status der Indexnutzung
• Ob ein Full‑Table‑Scan stattfindet
• Datenfilterungs‑ und Sortieroperationen

Da der Plan grafisch angezeigt wird, können selbst Anfänger leichter erkennen, wo Leistungsengpässe bestehen.

[With Images] Wie man Visual EXPLAIN verwendet und liest (Schritt für Schritt)

Befolgen Sie diese Schritte, um Visual EXPLAIN zu verwenden:

1. Starten Sie MySQL Workbench und öffnen Sie Ihre Datenbankverbindung → Stellen Sie sicher, dass die Verbindung im Voraus konfiguriert ist.
2. Geben Sie Ihre Zielabfrage im SQL‑Editor ein

   SELECT * FROM users WHERE age > 30;
   

3. Klicken Sie auf das Symbol „EXPLAIN VISUAL“ neben dem EXPLAIN‑Button → Oder klicken Sie mit der rechten Maustaste und wählen Sie „Visual Explain“ im Menü.
4. Der Ausführungsplan wird visuell angezeigt Wenn Sie auf jeden Knoten (Tabelle) klicken, erscheinen detaillierte Informationen wie die folgenden:

• Zugriffsverfahren (ALL, ref, range usw.)
• Verwendeter Index
• Geschätzte Zeilen (rows)
• Filterbedingungen und JOIN‑Methode

Hinweis:
In Visual EXPLAIN helfen Knotenfarben und Symbole, schwere Operationen oder ineffiziente Teile hervorzuheben.
Achten Sie besonders auf Knoten, die rot markiert sind, da sie typischerweise Leistungsprobleme anzeigen.

Auch Anfänger können Engpässe leicht finden

Textbasierte EXPLAIN‑Ausgaben können zunächst überwältigend wirken, aber Visual EXPLAIN lässt Problemzonen visuell hervorstechen.

Zum Beispiel wird es einfacher, zu identifizieren:

• Tabellen, die type: ALL verwenden
• Abfrageblöcke, die Using temporary zeigen
• Muster mit unnötigen JOINs
• Tabellen, bei denen Indizes nicht verwendet werden

Mit seiner GUI‑Oberfläche können Sie schnell Optimierungshypothesen bilden, und es ist auch nützlich, um SQL‑Leistung im Team zu teilen und zu überprüfen.

Visual EXPLAIN ist besonders wertvoll für Anfänger bis Fortgeschrittene im SQL.
Wenn Sie unsicher sind, wie Sie EXPLAIN‑Ergebnisse interpretieren, probieren Sie diese Funktion aus.

6. Häufig gestellte Fragen (FAQ)

Q1. Wann sollte ich EXPLAIN verwenden?

A. Sie sollten EXPLAIN verwenden, wann immer Sie sich über die Ausführungsgeschwindigkeit einer Abfrage unsicher sind – besonders wenn eine Abfrage „langsam erscheint“. Es ist auch nützlich, wenn Sie überprüfen möchten, ob eine neu erstellte Abfrage Indexes korrekt verwendet.

Durch das Prüfen des Ausführungsplans vor dem Deployment können Sie Performance‑Risiken frühzeitig identifizieren.

Q2. Die Ausgabe zeigt type = ALL. Was soll ich tun?

A. type: ALL bedeutet, dass MySQL einen vollständigen Tabellenscan durchführt. Dies ist ein kostenintensiver Vorgang und kann die Leistung erheblich beeinträchtigen, insbesondere bei großen Tabellen.

Betrachten Sie die folgenden Maßnahmen:

• Indizes zu Spalten hinzufügen, die in der WHERE‑Klausel verwendet werden
• Funktionen oder Operationen vermeiden, die die Indexnutzung deaktivieren
• SELECT * vermeiden und nur notwendige Spalten abrufen

Q3. Ist „Using temporary“ in der Extra‑Spalte ein Problem?

A. Using temporary zeigt an, dass MySQL intern eine temporäre Tabelle erstellt, um die Abfrage zu verarbeiten. Dies tritt häufig bei GROUP BY oder ORDER BY auf und kann Speicher- und Festplatten‑I/O‑Kosten erhöhen.

Mögliche Lösungen umfassen:

• Indizes zu Spalten hinzufügen, die in GROUP BY / ORDER BY verwendet werden
• Unnötiges Sortieren oder Aggregieren reduzieren
• LIMIT oder Unterabfragen verwenden, um den Datensatz zu verkleinern

Q4. Wie verwende ich Visual EXPLAIN?

A. Sie können das offizielle MySQL‑Tool „MySQL Workbench“ verwenden, um EXPLAIN‑Ergebnisse einfach in einer GUI zu visualisieren. Geben Sie einfach Ihre Abfrage ein und klicken Sie auf die Schaltfläche „Visual Explain“.

Dies wird besonders empfohlen für:

• Benutzer, die die textbasierte EXPLAIN‑Ausgabe schwer lesbar finden
• Personen, die komplexe JOINs visuell verstehen möchten
• Teams, die gemeinsam die SQL‑Performance überprüfen

Q5. Warum wird mein Index nicht verwendet, obwohl er existiert?

A. Auch wenn ein Index existiert, verwendet MySQL ihn nicht immer. Indizes können in folgenden Fällen ignoriert werden:

• Verwendung von Funktionen oder Ausdrücken in der WHERE‑Klausel (z. B. WHERE YEAR(created_at) = 2024 )
• Geringe Kardinalität (wenige unterschiedliche Werte), bei der ein Vollscan als schneller gilt
• Die Spaltenreihenfolge stimmt nicht mit der Definition eines zusammengesetzten Index überein

Um zu bestätigen, ob ein Index korrekt verwendet wird, prüfen Sie stets die Spalte key in EXPLAIN.

7. Zusammenfassung: Verwenden Sie EXPLAIN, um SQL‑Optimierungsmöglichkeiten zu entdecken

Performance‑Tuning in MySQL dreht sich nicht nur um das Hinzufügen von Indizes.
Das wesentliche Werkzeug, um welche Abfragen Engpässe darstellen, warum sie langsam sind und wie man sie behebt zu identifizieren, ist EXPLAIN.

In diesem Artikel haben wir die folgenden Schlüsselpunkte behandelt:

✅ Die Rolle und grundlegende Verwendung von EXPLAIN

• Einfach EXPLAIN vor einer Abfrage hinzufügen, um ihren Ausführungsplan zu prüfen
• Probleme wie Vollscans (ALL) und Using temporary werden sichtbar

✅ Wie man Ausgabespalten liest und die Performance bewertet

• Die vier wichtigsten Spalten sind type, key, rows und Extra
• Vollständige Tabellenscans vermeiden und eine korrekte Indexnutzung anstreben
• Vorsicht, wenn Sie Using temporary oder Using filesort sehen

✅ Praktische Diagnose und Optimierung anhand realer Beispiele

• Nicht nur das Hinzufügen von Indizes, sondern auch die Verbesserung der SQL‑Syntax ist wichtig
• Selbst komplexe Abfragen mit JOINs oder Unterabfragen können mit EXPLAIN analysiert werden
• Durch kontinuierliches Verfeinern von Abfragen basierend auf Ausführungsplänen verbessert man die Performance am schnellsten

✅ GUI‑Tools für visuelle Bestätigung verwenden

• Verwenden Sie „Visual EXPLAIN“ in MySQL Workbench, um Ausführungspläne grafisch anzuzeigen
• Für Anfänger einfacher, Engpässe visuell zu erkennen
• Hilfreich für Team‑Diskussionen und SQL‑Performance‑Reviews

✅ FAQ‑Abdeckung für reale Szenarien

• Ursachen und Lösungen für Probleme wie type=ALL und key=NULL erklärt
• Beispiele dafür geliefert, warum Indizes möglicherweise nicht verwendet werden

✍️ Machen Sie EXPLAIN zu einer Gewohnheit, um Ihre SQL‑Fähigkeiten zu verbessern

Wenn Sie sich zur Gewohnheit machen, Abfragen jedes Mal mit EXPLAIN zu prüfen, wenn Sie SQL schreiben, werden Sie von selbst schnellere und effizientere Abfragen schreiben.

Das ist nicht nur ein technischer Trick – es ist Teil der Entwicklung professioneller SQL‑Kompetenz.

• Führen Sie EXPLAIN sofort nach dem Schreiben einer Abfrage aus
• Beheben Sie verdächtige Ausführungspläne sofort
• Entwerfen Sie effiziente Indizes durchdacht

Durch das Beherrschen dieses Zyklus werden Ihre MySQL‑Fähigkeiten stetig verbessert.

Wir hoffen, dass dieser Artikel Ihr erster Schritt zu einer besseren Abfrageoptimierung wird.

Wenn Sie Fragen haben oder weitere Themen behandelt sehen möchten, hinterlassen Sie gerne einen Kommentar!