Wie man EXPLAIN in MySQL verwendet, um Abfragen zu verstehen und zu beschleunigen

Startseite » Python » Wie man EXPLAIN in MySQL verwendet, um Abfragen zu verstehen und zu beschleunigen

Wer lange genug mit MySQL arbeitet, wird früher oder später auf eine langsame Abfrage stoßen, die einem den Tag verdirbt. Vielleicht ein Bericht, der nie fertig wird, ein API-Endpunkt, der unter Last eine Zeitüberschreitung verursacht, oder ein Dashboard, das plötzlich 20 Sekunden zum Laden benötigt. In solchen Fällen sollten Sie als erstes Debugging-Tool den … verwenden. EXPLAIN Familie von Befehlen.

Die MySQL-Befehle EXPLAIN und EXPLAIN ANALYZE zeigen Ihnen, wie der Optimierer eine Abfrage plant und ausführt. Richtig eingesetzt, geben sie Aufschluss darüber, welche Tabellen durchsucht werden, welche Indizes verwendet (oder ignoriert) werden, wie Joins durchgeführt werden, wie viele Zeilen untersucht werden und wo die meiste Zeit verbraucht wird. In diesem Leitfaden erklären wir Ihnen in einfachen Worten, wie Sie diese Befehle verwenden, ihre Ausgabe interpretieren und diese Erkenntnisse in konkrete Leistungsverbesserungen umsetzen.

Was EXPLAIN in MySQL bewirkt (und wann man es verwendet)

In MySQL, EXPLAIN Das Schlüsselwort ist ein Diagnosewerkzeug, das die geplante Ausführungsstrategie für eine Anweisung anzeigt, anstatt sie normal auszuführen. Sie platzieren EXPLAIN vor Ihrer Abfrage, und MySQL antwortet mit einer kleinen Tabelle, die beschreibt, wie es die angeforderten Zeilen abrufen will.

Sie können EXPLAIN verwenden mit SELECT, INSERT, UPDATE, DELETE und REPLACE nicht nur SELECT-Anweisungen. Beispielsweise:

Ejemplo:

Abfragebeispiel: EXPLAIN SELECT * FROM employees WHERE last_name = 'Puppo' AND first_name = 'Kendra';

Anstatt die Mitarbeiterdaten zurückzugeben, liefert MySQL eine detaillierte Erklärung, wie die jeweilige Anweisung zeilenweise ausgeführt wird. Sie sehen, welche Tabelle gelesen und welcher Index ausgewählt wird, wie viele Zeilen MySQL zu prüfen erwartet und zusätzliche Hinweise, z. B. ob eine temporäre Tabelle oder eine Dateisortierung erforderlich ist.

Verwenden Sie EXPLAIN, wenn Sie vermuten, dass eine Abfrage langsamer ist als erwartet, oder wenn Sie Indizes entwerfen und überprüfen möchten, ob MySQL diese auch tatsächlich verwendet. EXPLAIN ist außerdem äußerst hilfreich, wenn Sie komplexes Legacy-SQL mit mehreren Unterabfragen und Joins übernehmen und nachvollziehen müssen, was der Optimierer tut.

Varianten von EXPLAIN: EXTENDED, PARTITIONS, ANALYZE und Formate

Die einfache EXPLAIN-Anweisung ist nur der Ausgangspunkt; MySQL bietet verschiedene Erweiterungen und Ausgabeformate, die tiefergehende Einblicke ermöglichen. Das Verständnis dieser Optionen hilft Ihnen, für jede Situation den passenden Detaillierungsgrad zu wählen.

EXPLAIN EXTENDED fügt weitere Optimierungsinformationen hinzu, insbesondere die filtered Spalte und umgeschriebener Abfragetext. Nach dem Rennen EXPLAIN EXTENDED ... Sie können ausführen SHOW WARNINGS; Sie können sehen, wie der Optimierer Ihre Abfrage intern umgeschrieben hat, was sehr hilfreich ist, um Optimierungsentscheidungen zu verstehen.

EXPLAIN PARTITIONS Zeigt an, auf welche Partitionen einer partitionierten Tabelle die Abfrage zugreift. Das partitions Die Spalte listet die relevanten Partitionen auf und hilft Ihnen so zu überprüfen, ob die Partitionsbereinigung tatsächlich stattfindet und Sie nicht versehentlich jede Partition treffen.

Das Ausgabeformat ist ebenfalls flexibel: Sie können tabellarische, tabulatorgetrennte, vertikale oder JSON-Ausgabe in der CLI und visuelle Pläne in Tools wie MySQL Workbench erhalten. Die JSON-Ausgabe ist besonders nützlich für die Automatisierung und tiefergehende Analysen, da sie Kostenschätzungen und die verschachtelte Planstruktur enthält. Beachten Sie jedoch, dass einfache JSON-Ausgabe EXPLAIN ANALYZE MySQL unterstützt derzeit keine Roh-JSON-Ausgabe.

Visuelle Tools wie MySQL Workbench können EXPLAIN als grafischen Baum darstellen, was bei komplexen Joins und Unterabfragen oft leichter verständlich ist. Externe Tools wie Percona Toolkit, EverSQL oder Releem können die EXPLAIN-Ausgabe oder Protokolle langsamer Abfragen auswerten und Ihnen helfen, die zu optimierenden Anweisungen zu priorisieren.

Die EXPLAIN-Spalten verstehen (Tabellenformat)

Wenn Sie eine klassische EXPLAIN-Anweisung ohne JSON-Format ausführen, gibt MySQL für jede Tabelle oder Unterabfrage, die an Ihrer Anweisung beteiligt ist, eine Zeile zurück. Die Reihenfolge dieser Zeilen ist wichtig: Sie zeigt die Reihenfolge an, in der während der Ausführung auf die Tabellen zugegriffen wird.

Dies sind die wichtigsten Spalten, die Sie sehen werden, und was sie Ihnen sagen:

• id: Ein fortlaufender Bezeichner für jeden Teil der Abfrage. Eine einzelne einfache SELECT-Anweisung hat normalerweise id = 1Mehrere IDs weisen auf Unterabfragen, abgeleitete Tabellen oder UNION-Teile hin. Höhere IDs werden im Allgemeinen vor niedrigeren ausgeführt und geben somit einen Hinweis auf die Ausführungsreihenfolge.
• select_type: Beschreibt die Rolle dieser SELECT-Anweisung innerhalb der Gesamtabfrage, mit Werten wie SIMPLE (keine Unterabfragen oder UNIONs), PRIMARY (äußerste SELECT-Anweisung in einer komplexen Abfrage), UNION, UNION RESULT, DERIVED (Unterabfrage in FROM) oder Volltext-bezogene Typen. So erkennen Sie auf einen Blick, ob es sich um verschachtelte Abfragen, Vereinigungen oder abgeleitete Tabellen handelt.
• table: Gibt an, auf welche Tabelle oder welches interne Ergebnis sich diese Zeile bezieht. Es kann sich um einen echten Tabellennamen oder einen Pseudonymnamen wie z. B. handeln. <unionM,N> für ein internes Gewerkschaftsergebnis <derivedN> für eine abgeleitete Tabelle oder ähnliche Kennzeichnungen für materialisierte Unterabfragen.
• partitions: Bei der Verwendung von partitionierten Tabellen zusammen mit EXPLAIN PARTITIONSDiese Liste zeigt die Partitionen an, die Zeilen enthalten, die den Abfragebedingungen entsprechen. Werden viele Partitionen angezeigt, profitieren Sie möglicherweise nicht von der Partitionsbereinigung.
• type: Oft auch Join- oder Zugriffstyp genannt, ist dies einer der wichtigsten Leistungsindikatoren. Er beschreibt, wie MySQL auf Zeilen zugreift: Die Werte reichen von sehr effizient (wie …). const, eq_ref, ref) zu weniger effizient (range) zu arm (index, ALL vollständige Tabellendurchsuchung). Spezielle Typen wie index_merge, unique_subquery und index_subquery spezifische Optimierungen angeben.
• possible_keys: Listet Indizes auf, die MySQL für diesen Teil der Abfrage als verwendbar erachtet. Wenn dies der Fall ist NULLDas bedeutet, dass MySQL keinen brauchbaren Index erkennt, was ein starkes Indiz dafür ist, dass Sie möglicherweise einen Index basierend auf Ihren WHERE- oder JOIN-Bedingungen erstellen müssen.
• key: Zeigt den vom Optimierer für diesen Zugriff tatsächlich gewählten Index an. Wenn es sich um NULL und gleichzeitig die possible_keys Wenn Kandidaten aufgelistet werden, entscheidet der Optimierer, dass die Verwendung eines Index sich nicht lohnt, oft aufgrund geringer Selektivität oder kleiner Tabellengröße.
• key_len: Zeigt die Anzahl der verwendeten Bytes des Index an. Bei zusammengesetzten Indizes gibt dies an, wie viele führende Spalten tatsächlich genutzt werden. Dies ist entscheidend, um zu verstehen, ob Ihr mehrspaltiger Index vollständig oder nur teilweise genutzt wird.
• ref: Gibt an, was mit den in der Liste aufgeführten Indexspalten verglichen wird. key: Es kann auf eine andere Tabellenspalte (für Joins) oder auf eine Konstante (für einfache Filter) verweisen.
• rows: Eine Schätzung, wie viele Zeilen MySQL in diesem Schritt voraussichtlich untersuchen wird. Sie ist auf statistischen Daten basierend und daher nur ein Näherungswert, aber sehr nützlich, um den Aufwand einer Abfrage abzuschätzen und zu beurteilen, ob ein Index den Suchraum effektiv verkleinert.
• filtered: Verfügbar mit EXPLAIN EXTENDEDDieser Prozentsatz gibt an, wie viele der untersuchten Zeilen voraussichtlich die Bedingungen der Tabelle erfüllen. Niedrige Prozentsätze in Kombination mit hohen rows oft schreien sie nach besserer Indexierung oder selektiveren Bedingungen.
• Extra: Ein Freitextfeld für zusätzliche Notizen, die nirgendwo anders Platz finden, wie zum Beispiel Using index, Using where, Using temporary, Using filesort, Volltexthinweise und mehr, die alle wichtige Hinweise zur Leistung enthalten.

Indem Sie diese Spalten gemeinsam statt einzeln betrachten, erhalten Sie eine kompakte, aber aussagekräftige Zusammenfassung, wie MySQL Ihre Abfrage ausführen möchte. Mit etwas Übung erkennen Sie schnell Warnsignale wie vollständige Tabellenscans, fehlende Indizes oder unnötige temporäre Tabellen.

Wie EXPLAIN Zugriffstypen definiert (die type Säule)

Das type Die Spalte verdient besondere Aufmerksamkeit, da sie eine der schnellsten Möglichkeiten bietet, den Zustand einer Abfrage zu beurteilen. Obwohl in der Dokumentation von einem Join-Typ die Rede ist, ist es genauer, ihn als Zugriffstyp zu betrachten, der beschreibt, wie Zeilen gefunden werden.

Im Allgemeinen reichen die Zugriffsarten hinsichtlich ihrer Leistung von „hervorragend“ bis „schlecht“. Die genaue Liste ist lang, wichtige Beispiele sind jedoch:

• const / system: MySQL kann eine Tabelle mithilfe eines Primärschlüssels oder eines eindeutigen Index mit einem konstanten Wert auf maximal eine Zeile reduzieren. Dies ist äußerst effizient.
• eq_ref: Für jede Zeile aus der vorherigen Tabelle liest MySQL genau eine übereinstimmende Zeile aus dieser Tabelle, üblicherweise aufgrund eines eindeutigen oder Primärschlüsselverweises in einem Join.
• ref: MySQL greift über einen nicht eindeutigen Index auf Zeilen zu, der einer Konstanten oder Spalte entspricht; mehrere Zeilen können übereinstimmen. Im Allgemeinen ist dies dennoch gut.
• range: MySQL verwendet einen Index, um Zeilen in einem bestimmten Wertebereich abzurufen (z. B. BETWEEN, >=oder eine Präfixübereinstimmung mit LIKE 'abc%'Dies ist oft akzeptabel, insbesondere bei Datums- oder Zahlenbereichen.
• index: MySQL durchsucht den gesamten Index anstatt der gesamten Tabellendaten. Das ist zwar besser als ein vollständiger Tabellenscan, aber bei großen Indizes immer noch oft zu aufwendig.
• ALL: Vollständiger Tabellenscan. MySQL liest jede Zeile, um Übereinstimmungen zu finden. Bei kleinen Tabellen ist das unproblematisch, bei großen Tabellen sollten Sie jedoch in der Regel Indizes verwenden oder Abfragen umschreiben, um dies zu vermeiden.
• index_merge: Zeigt an, dass MySQL Ergebnisse aus mehreren Indizes derselben Tabelle kombiniert. Das klingt zwar gut, ist in der Praxis aber oft schlechter als ein guter einzelner zusammengesetzter Index.
• unique_subquery / index_subquery: Spezielle Zugriffstypen, die zur Optimierung bestimmter IN (SELECT ...) Unterabfragen über eindeutige oder nicht eindeutige Indexabfragen ersetzen weniger effiziente Suchmuster.

Bei der Überprüfung der EXPLAIN-Ausgabe sollten Sie im Allgemeinen darauf achten, dass die Zugriffstypen so weit wie möglich entfernt sind. const / eq_ref / ref so weit wie möglich und vermeiden ALL auf großen Tischen. Wenn Sie sehen, ALL mit einem hohen rows Schätzungsweise ist es fast immer ein Kandidat für eine bessere Indexierung.

Schlüssel, mögliche_Schlüssel und Schlüssellänge: Helfen Ihre Indizes wirklich weiter?

Indizes sind oft die effektivste Methode, um Abfragen zu beschleunigen, und EXPLAIN hilft Ihnen dabei, genau zu verstehen, wie sie verwendet werden. Drei Spalten sind für die Indexdiagnostik besonders wichtig: possible_keys, key und key_len.

possible_keys listet alle Indizes auf, die MySQL basierend auf den WHERE- und JOIN-Bedingungen für diese Tabelle als relevant erachtet. Wenn das ist NULL Bei einer Tabelle, die an einem komplexen Filter oder Join beteiligt ist, ist dies ein starker Hinweis darauf, dass Sie das Hinzufügen eines Indexes, der die relevanten Spalten abdeckt, in Betracht ziehen sollten.

key zeigt Ihnen an, welchen Index MySQL tatsächlich aus den Kandidaten ausgewählt hat. If key is NULL während es Einträge gibt possible_keysDer Optimierer entschied, dass ein vollständiger Scan günstiger ist, was in der Regel auf eine schlechte Indexselektivität, veraltete Statistiken oder ein Abfragemuster hinweist, das nicht vom verfügbaren Indexdesign profitieren kann.

key_len zeigt an, wie viele Bytes des gewählten Index verwendet werden. Bei zusammengesetzten Indizes können Sie so überprüfen, wie viele der indizierten Spalten zur Suche beitragen. Wenn Sie einen Index erstellt haben auf (last_name, first_name) und key_len deckt nur die Länge ab für last_nameSie wissen, dass die Abfragen den zusammengesetzten Index nicht voll ausnutzen.

Das ref Die Spalte ergänzt dies, indem sie angibt, was MySQL mit dem Index vergleicht, ob es sich um eine Konstante handelt (wie z. B. 'Puppo'oder eine Spalte aus einer verknüpften Tabelle. Wenn Sie einen Join debuggen, wird die korrekte Join-Spalte angezeigt in ref zusammen mit einem guten type Wert wie z.B. eq_ref ist ein Zeichen dafür, dass Ihr Join gut indexiert ist.

Zeilen, gefiltert und Extra: Aufspüren versteckter Ineffizienzen

Das rows und filtered Spalten vermitteln einen groben Überblick über das Arbeitsvolumen, Extra hebt spezielle Operationen hervor, die häufig Leistungsprobleme erklären. Diese drei sollten immer gemeinsam überprüft werden.

rows ist die Schätzung von MySQL, wie viele Datensätze in diesem Schritt untersucht werden müssen. Es ist nicht immer exakt, aber gut genug, um offensichtliche Fehler zu erkennen, wie zum Beispiel das Scannen von Hunderttausenden von Zeilen, wenn man nur eine Handvoll erwartet hat.

filtered (verfügbar durch EXPLAIN EXTENDED) gibt den geschätzten Prozentsatz der untersuchten Zeilen an, die die Bedingungen für diese Tabelle erfüllen. Ein hoch rows mit einem sehr niedrigen filtered Prozentangaben deuten in der Regel auf fehlende oder suboptimale Indizes oder schlecht selektive Prädikate hin.

Das Extra Die Spalte fasst zusätzliche Anmerkungen zur Ausführung zusammen, die nirgendwo anders hineinpassen. Zu den wichtigsten Werten, denen Sie begegnen könnten, gehören:

• Using where: Eine WHERE-Bedingung wird angewendet, um die Zeilen dieser Tabelle zu filtern.
• Using index: MySQL kann alle benötigten Spalten allein aus dem Index bedienen, ohne die Tabellendaten zu berühren. Dies wird als Covering Index bezeichnet und ist im Allgemeinen sehr effizient.
• Using temporary: MySQL erstellt eine interne temporäre Tabelle, um Zwischenergebnisse zu speichern, beispielsweise bei komplexen GROUP BY- oder Unterabfrageverarbeitungen. Bei großen Datensätzen kann dies die Performance erheblich beeinträchtigen.
• Using filesort: MySQL führt einen separaten Sortierschritt durch, häufig für ORDER BY oder GROUP BY, der im Arbeitsspeicher oder auf der Festplatte erfolgen kann und in der Regel teurer ist als die Verwendung einer indexbasierten Sortierung.
• Anmerkungen zum Volltext: Bei Verwendung von FULLTEXT-Indizes kann EXPLAIN aufdecken, ob eine Volltextsuche korrekt mit Joins oder anderen Filtern kombiniert wird.

Achten Sie besonders darauf Using temporary und Using filesort in Extra Wenn Abfragen langsam sind, kann dies auf einen hohen Sortier- oder Gruppierungsaufwand hindeuten, der möglicherweise durch bessere Indizes oder umstrukturierte Abfragen ausgelagert werden könnte.

ERKLÄREN ANALYSIEREN: Die tatsächlichen Ausführungskosten erkennen

Ab MySQL 8.0.18 erhalten Sie ein noch leistungsfähigeres Werkzeug: EXPLAIN ANALYZE, wodurch die Abfrage tatsächlich ausgeführt und Laufzeitstatistiken an den Plan angehängt werden. Damit wird die Lücke zwischen theoretischen Kostenschätzungen und der tatsächlichen Leistung geschlossen.

Im Gegensatz zum herkömmlichen EXPLAIN, das lediglich Vorhersagen trifft, führt EXPLAIN ANALYZE die Anweisung aus und misst die Dauer jedes Iterators (Schritts) im Ausführungsplan, die Anzahl der zurückgegebenen Zeilen und die Anzahl der Schleifendurchläufe. Die Syntax ist unkompliziert:

Run: EXPLAIN ANALYZE SELECT * FROM table_name WHERE condition;

Wenn Sie EXPLAIN ANALYZE ausführen, verwendet MySQL FORMAT=tree automatisch und gibt einen baumförmigen Plan aus, der geschätzte und tatsächliche Kennzahlen kombiniert. Für jeden Knoten können Sie Folgendes sehen:

• Geschätzte Ausführungskosten: Das Optimierungsmodell, das angibt, wie aufwändig dieser Schritt sein sollte.
• Geschätzte Zeilenanzahl: Wie viele Zeilen sollten von diesem Iterator zurückgegeben werden?
• Tatsächliche Zeit bis zur ersten Reihe: Wie lange es dauerte, bis die erste Reihe produziert war.
• Tatsächliche Zeit pro Schleife: Durchschnittliche Ausführungszeit des Iterators in Millisekunden, einschließlich seiner Kinder, aber ohne den Elterniterator, über alle Schleifen hinweg.
• Tatsächliche Zeilen und Schleifen: Wie viele Zeilen tatsächlich zurückgegeben wurden und wie oft der Iterator ausgeführt wurde.

EXPLAIN ANALYZE lässt sich mit SELECT-, UPDATE-, DELETE- und TABLE-Anweisungen für mehrere Tabellen verwenden. Besonders hilfreich ist dies, wenn EXPLAIN eine bestimmte Vorgehensweise nahelegt, die Abfrage im Produktivbetrieb jedoch ein anderes Verhalten zeigt, da Sie nun die geschätzten und tatsächlichen Zeilenanzahlen und Kosten vergleichen können.

Große Abweichungen zwischen Schätzungen und Ist-Werten zeigen, wo die Statistiken des Optimierers fehlerhaft sind oder wo komplexe Prädikate (einschließlich Funktionen, benutzerdefinierter Funktionen oder gespeicherter Routinen) die Kostenprognose ungenau machen. Diese Stellen eignen sich besonders für Schemaanpassungen, neue Indizes oder die Überarbeitung von Abfragen.

Klassisches EXPLAIN vs. EXPLAIN ANALYZE: Stärken und Schwächen

Auch wenn EXPLAIN und EXPLAIN ANALYZE unglaublich nützlich sind, ist es wichtig, ihre Schwächen zu kennen, damit man die Ausgabe nicht überinterpretiert.

Die reguläre EXPLAIN-Ausgabe ist im Wesentlichen eine Annäherung an die vom Optimierer erwarteten Ergebnisse. Einige der angezeigten Statistiken sind grobe Schätzungen, und bestimmte interne Optimierungen werden in der Ausgabe gar nicht sichtbar gemacht. Beispielsweise wird nicht angezeigt, wie sich Trigger, gespeicherte Funktionen oder benutzerdefinierte Funktionen auf die CPU-Zeit auswirken. Auch die Vorgänge innerhalb gespeicherter Prozeduren werden nicht dargestellt.

Einige Bezeichnungen in der Ausgabe stellen eine zu starke Vereinfachung dessen dar, was tatsächlich vor sich geht. Zum Beispiel kann die type Die Spalte wird als Join-Typ bezeichnet, obwohl sie eigentlich einen Zugriffstyp darstellt. Extra Wert Using temporary unterscheidet nicht zwischen temporären Tabellen im Arbeitsspeicher und auf der Festplatte, und Using filesort Umfasst sowohl speicher- als auch festplattenbasierte Sortierungen unter der gleichen Bezeichnung.

EXPLAIN ANALYZE behebt einige dieser Probleme durch die Anzeige von Echtzeit-Zeiten und Zeilenanzahlen, hat aber auch Nachteile. Die Abfrage muss ausgeführt werden, was in der Produktionsumgebung aufwändig oder sogar gefährlich sein kann, wenn die Abfrage ressourcenintensiv ist oder Daten verändert. Außerdem ist die Ausgabe von rohem JSON für EXPLAIN ANALYZE derzeit nicht direkt verfügbar, daher arbeiten Sie hauptsächlich mit dem Baumformat in der Befehlszeile oder den Client-Tools.

Der richtige Ansatz besteht darin, EXPLAIN für einen schnellen, unkomplizierten Einblick in den Plan zu verwenden und EXPLAIN ANALYZE für tiefergehende Untersuchungen, bei denen Sie es sich leisten können, die Abfrage tatsächlich auszuführen.

Optimierung realer Abfragen mithilfe von EXPLAIN und EXPLAIN ANALYZE

Das Lesen von EXPLAIN ist nur dann sinnvoll, wenn es zu besseren Abfragen führt; entscheidend ist, die angezeigten Informationen in konkrete Index- und SQL-Änderungen umzusetzen. Sehen wir uns einige gängige Optimierungsszenarien an, die Sie mithilfe von EXPLAIN direkt angehen können.

Stellen Sie sich eine einfache Abfrage auf einem vor employees Tabelle ohne relevante Indizes:

Filterbeispiel: EXPLAIN SELECT * FROM employees WHERE last_name = 'Puppo' AND first_name = 'Kendra';

Ohne Indizes wird EXPLAIN wahrscheinlich Folgendes anzeigen: type = ALL und einen sehr hohen Wert in rows (beispielsweise rund 299,000 Zeilen), was auf einen vollständigen Tabellenscan hinweist. Das ist ein klares Signal dafür, dass Sie einen Index benötigen, der beide Bedingungen unterstützt.

Ein naiver Ansatz besteht darin, zwei separate Indizes zu erstellen, einen für … last_name und einer weiter first_nameDas macht die Kombinationssuche aber immer noch nicht effizient. MySQL kann schnell alle Einträge mit dem Nachnamen „Puppo“ oder alle Einträge mit dem Vornamen „Kendra“ finden, aber um genau den Eintrag „Kendra Puppo“ zu identifizieren, ist die Schnittmenge dieser beiden Mengen erforderlich, was nicht so effizient ist, wie man es sich vielleicht wünschen würde.

Die bessere Lösung ist ein mehrspaltiger Index, der Ihrem selektivsten und am häufigsten verwendeten Suchmuster folgt:

Indexerstellung: CREATE INDEX fullnames ON employees(last_name, first_name);

Wenn Sie EXPLAIN jetzt erneut ausführen, sollten Sie sehen, dass dieser Index verwendet wird. type verbessert sich zu etwa ref oder const abhängig von der Einzigartigkeit und rows sinkt auf 1. Dies bestätigt, dass nur eine Zeile bearbeitet werden muss, was die massive Leistungsverbesserung erklärt.

Ein ähnliches Muster zeigt sich bei der Join-Optimierung. Angenommen, Sie haben zwei Tabellen A und B mit einer gemeinsamen Spalte X, die zum Verknüpfen verwendet wird, und Sie erstellen diese zunächst ohne Indizes:

Schema: CREATE TABLE A ( X VARCHAR(10), Y VARCHAR(10));
CREATE TABLE B ( X VARCHAR(10), Z VARCHAR(10));

Wenn eine Verknüpfung mit einem Filter auf AX ausgeführt wird, werden möglicherweise vollständige Scans sowohl auf A als auch auf B angezeigt, wobei jeweils etwa 10,000 Zeilen untersucht werden, da die Engine keine bessere Option hat, als die Zeilen per Brute-Force-Vergleich zu vergleichen:

Join-Abfrage: EXPLAIN SELECT *
FROM A INNER JOIN B ON A.X = B.X
WHERE A.X = '100';

Sobald Sie die entsprechenden Indizes und Fremdschlüsselbeschränkungen hinzugefügt haben, spiegelt EXPLAIN die verbesserte Strategie wider. Zum Beispiel:

Index & FK: CREATE UNIQUE INDEX a_unique_index ON A(X);
CREATE UNIQUE INDEX b_unique_index ON B(X);
ALTER TABLE B ADD CONSTRAINT fk_b_x FOREIGN KEY (X) REFERENCES A(X);

Nach diesen Änderungen wird EXPLAIN in der Regel Folgendes anzeigen: type Verbesserung (z. B. eq_ref auf der verknüpften Tabelle), sowie rows Der Rückgang von Tausenden auf etwa 1 beweist, dass der Join jetzt effiziente Indexabfragen nutzt, anstatt alles zu durchsuchen.

Mustererkennung, Sortierung und andere häufige Fallstricke

EXPLAIN ist besonders wertvoll bei der Analyse von Mustern, Sortierungen und Gruppierungen, da scheinbar harmlose SQL-Anweisungen Indizes vollständig deaktivieren können. Das frühzeitige Erkennen dieser Muster erspart Ihnen viel Rätselraten.

Ein klassisches Problem ist die Mustererkennung mit führenden Platzhaltern. Zum Beispiel eine Abfrage mit WHERE email LIKE '%yahoo.com' macht es unmöglich, einen Standardindex zu erstellen email Um zu helfen, da MySQL keine Möglichkeit bietet, direkt zu Zeilen mit dieser Endung zu springen, zeigt EXPLAIN in der Regel Folgendes an: type = ALL und eine große rows Anzahl.

Nachlaufende Wildcards, wie LIKE 'john%'Sie sind indexfreundlich, da die Engine den Index verwenden kann, um den Bereich der Zeilen zu finden, die mit 'john' beginnen. EXPLAIN bestätigt dies, indem es einen geeigneten Zugriffstyp und eine kleine Anzeige anzeigt. rows Schätzung anhand der indizierten Spalte.

Sortieren und Gruppieren sind ein weiterer kritischer Punkt bei der Leistungsoptimierung. Wenn EXPLAIN anzeigt Using filesort in Extra zusammen mit einem großen rows Ich schätze, Ihre ORDER BY- oder GROUP BY-Anweisung erzwingt wahrscheinlich einen expliziten Sortierschritt.

Sortierbeispiel: EXPLAIN SELECT sale_id FROM sales ORDER BY sale_date;

Hinzufügen eines Indexes auf sale_date ermöglicht MySQL das direkte Lesen von Zeilen in sortierter Reihenfolge, was EXPLAIN normalerweise durch Entfernen widerspiegelt. Using filesort und gegebenenfalls die Zugriffsart ändern auf index oder besser.

Gleichzeitig sollte man darauf achten, nicht zu überindizieren. EXPLAIN kann Ihnen anzeigen, welche Indizes in possible_keys werden aber nie verwendet als key durch jede wichtige Abfrage. Diese könnten Kandidaten für die Entfernung sein, aber Sie müssen zuerst die Gesamtlast prüfen; nur weil eine Abfrage einen Index ignoriert, bedeutet das nicht, dass andere kritische Abfragen nicht darauf angewiesen sind.

WARNUNGEN und erweiterte Erklärungsmarkierungen anzeigen

Selbst wenn eine EXPLAIN-Abfrage nicht korrekt geparst wird, können Sie dennoch einige Erkenntnisse gewinnen, indem Sie SHOW WARNINGS;. Dieser Befehl zeigt Informationen über die letzte nicht-diagnostische Anweisung an und kann Ihnen Teilhinweise darüber geben, wie MySQL Teile Ihrer Abfrage interpretiert hat.

Wenn Sie beispielsweise EXPLAIN für eine Abfrage ausführen, die auf eine nicht existierende Tabelle verweist oder einen Syntaxfehler enthält, kann EXPLAIN fehlschlagen. SHOW WARNINGS kann jedoch interne Hinweise anzeigen, die angeben, wie Teile der Abfrage behandelt würden. Mögliche Hinweise sind beispielsweise:

• <index_lookup>(query fragment): Dies deutet darauf hin, dass bei einer gültigen Abfrage eine Indexsuche für dieses Fragment durchgeführt würde.
• <primary_index_lookup>(query fragment): weist darauf hin, dass eine Suche auf Basis des Primärschlüssels erfolgen würde.
• <if>(condition, expr1, expr2): weist auf die bedingte Auswertung in diesem Teil der Abfrage hin.
• <temporary table>: weist darauf hin, dass eine interne temporäre Tabelle für Zwischenergebnisse erstellt wird, beispielsweise vor einem Join.

Die Kombination von EXPLAIN EXTENDED mit SHOW WARNINGS ist eine leistungsstarke Methode, um nicht nur den Ausführungsplan, sondern auch die vom Optimierer umgeschriebene Abfrage anzuzeigen, wodurch manchmal Vereinfachungen, Prädikat-Pushdowns oder andere Transformationen sichtbar werden, die man möglicherweise nicht erwartet.

Über EXPLAIN hinaus: Datenmodell, Architektur und Werkzeuge

EXPLAIN ist zwar ein hervorragendes Werkzeug zur Analyse einzelner Abfragen, doch die nachhaltige Performance hängt auch von Ihrem gesamten Datenmodell, der Systemarchitektur und der Hardware ab. Ein grundlegend fehlerhaftes Schema oder eine überlastete Instanz lässt sich nicht allein durch die Anpassung von WHERE-Klauseln beheben.

Ein guter Ausgangspunkt ist ein Datenmodell, das sehr unterschiedliche Arbeitslasten und die JSON-Verarbeitung in SQL trennt . Beispielsweise führt die Vermischung von Zugriffsprotokollen mit Transaktionsdaten in derselben MySQL-Instanz langfristig zu Problemen: Mit dem Wachstum des Unternehmens kann der Protokollierungsverkehr die E/A-Kapazitäten überlasten und kritische Geschäftsabfragen beeinträchtigen. Die Auslagerung der Protokolle in einen dedizierten Speicher oder ein Data Warehouse ist oft skalierbarer.

Die Softwarearchitektur spielt ebenfalls eine wichtige Rolle. Entscheidungen über monolithische Architekturen versus Microservices, Data Warehouses und spezialisierte Datenspeicher (wie Graphdatenbanken für Empfehlungen) beeinflussen, welche Abfragen wo ausgeführt werden. Benötigt ein Dienst nahezu Echtzeit-Analysen auf riesigen Datensätzen, ist eine Standard-OLTP-MySQL-Instanz möglicherweise nicht die beste Lösung.

Auch Hardware und Instanzgröße spielen eine Rolle. Festplatten-E/A, Arbeitsspeicher, CPU und Netzwerkdurchsatz beeinflussen die Abfragelatenz. Durch die Identifizierung von Spitzenlastzeiten und die Planung rechenintensiver analytischer oder administrativer Abfragen außerhalb dieser Zeiträume lässt sich die Benutzerleistung optimieren. Mit Managed DBaaS-Angeboten können Sie Instanzen häufig skalieren oder bei Bedarf auf leistungsfähigere Klassen umsteigen.

Ergänzen Sie EXPLAIN abschließend durch Tools zur kontinuierlichen Überwachung und Profilerstellung. MySQLs eigenes Leistungsschema und Protokoll langsamer Abfragen, MySQL Workbench, Percona Toolkit pt-query-digestWebtools wie EverSQL und Plattformen wie Releem können Ihre größten Datenbankfehler automatisch aufdecken und sogar Optimierungsvorschläge unterbreiten. EXPLAIN ist dann Ihr präzises Werkzeug für diese spezifischen Abfragen.

Mit EXPLAIN und EXPLAIN ANALYZE erweitern Sie Ihre Werkzeugkiste und können langsame Abfragen nicht länger auf Basis konkreter Erkenntnisse analysieren. Durch das Verständnis von IDs, Auswahltypen, Zugriffsmethoden, Indexnutzung, Zeilenschätzungen, Filtern und zusätzlichen Flags lassen sich vollständige Tabellenscans systematisch vermeiden, ressourcenintensive Joins optimieren, intelligentere Indizes entwerfen und unnötige Sortierungen sowie temporäre Tabellen umgehen. Dabei behalten Sie stets das Gesamtbild von Schema und Infrastruktur im Blick, sodass Ihre MySQL-Workloads auch bei wachsendem Datenaufkommen schnell und vorhersehbar bleiben.