MySQL Queries mit EXPLAIN auswerten und das Ergebnis richtig deuten

Datenbanken, MySQL, , ,

Mit dem Befehl EXPLAIN vor einem SELECT kann man sehen wie die Query optimiert und verarbeitet. Man kann sehen ob und welche Indexe benutzt werden und wie hoch die Kosten für die Ausführung sind. Anhand des Ergebnisses kann man z.B. auch sehen wo evtl. noch ein Index fehlt oder wie man die Query noch weiter optimieren kann.

Wenn man z.B. nur EXPLAIN TABLENAME ausführt,  kann man sehen welche Spalten einer Tabelle bereits mit einem Index versehen sind und welche nicht.

EXPLAIN bietet noch eine erweiterte Auswertung an, die noch weitere Werte zu Tage fördert.

Hilfreiche Queries bei der Verwendung von EXPLAIN

Wenn man mit EXPLAIN arbeitet, sind die folgenden Queries sehr hilfreich. Sie haben zwar nichts direkt mit EXPLAIN zu tun, können aber das Ergebnis der EXPLAIN Auswertung deutlich verändern.

Z.B. kann man die Tabellenreihenfolge bei JOINS mal ändern, um  zu sehen wie die dann ist. Man kann dabei u.a. auch sehen ob der Query Optimizer die Reihenfolge der Tabellen im JOIN optimiert oder nicht.

Nach dem setzen eines Indexes oder wenn man die Vermutung hat ein Index wird nicht benutzt, sollte man die Tabellen Statistik mal aktualisieren.

Auswertung des Ergebnisses von EXPLAIN

Die Spalten im Ergebnis bedeuten dabei folgendes:

  • select_type –
  • table – Name der abgefragten Tabelle
  • type – Art des Lesezugriffs
  • possible_keys – gibt an, unter welchen Indizes auswählen kann
  • key – gibt den Schlüssel(index) an, für dessen Verwendung sich tatsächlich entschieden hat.
  • key_len – gibt die Länge des Schlüssels an, für dessen Verwendung sich entschieden hat.
  • ref – zeigt an, welche Spalten oder Konstanten mit dem in der Spalte key genannten Index verglichen werden,
  • rows – gibt die Anzahl der Datensätze an, die glaubt untersuchen zu müssen, um die Abfrage ausführen zu können.
  • extra – zusätzliche Angaben dazu, wie MySQL die Abfrage auflöst
  • filtered – (Nur bei EXTENDED)

Hier die Bedeutung der Werte von einigen Spalten

select_type

SIMPLE einfache SELECT-Anweisung (ohne UNION oder Unterabfragen).
PRIMARY äußerste SELECT-Anweisung.
UNION zweite oder spätere SELECT-Anweisung in einer UNION.
DEPENDENT UNION zweite oder spätere SELECT-Anweisung in einer UNION, abhängig von der äußeren Abfrage.
UNION RESULT Ergebnis einer UNION.
SUBQUERY erste SELECT-Anweisung in einer Unterabfrage.
DEPENDENT SUBQUERY erste SELECT-Anweisung in einer Unterabfrage, abhängig von der äußeren Abfrage.
DERIVED abgeleitete Tabellen-SELECT – Anweisung (Unterabfrage in FROM-Klausel).

type

system Diese Tabelle hat nur einen Datensatz (Systemtabelle). Dies ist ein Sonderfall des Join-Typs const.
const Die Tabelle hat maximal einen passenden Datensatz, der beim Start der Abfrage gelesen wird.
eq_ref Für jede Datensatzkombination aus den vorherigen Tabellen wird genau ein Datensatz aus dieser Tabelle gelesen. Anders als die Typen system und const ist dies der beste Join-Typ. Er wird verwendet, wenn alle Teile eines Indexes vom Join verwendet werden und der Index ein Primärschlüssel oder ein eindeutiger Index ist.
ref Alle Datensätze mit passenden Indexwerten werden aus dieser Tabelle für jede Kombination von Datensätzen in den vorherigen Tabellen gelesen.
ref_or_null Dieser Join-Typ ähnelt ref weitgehend, allerdings führt MySQL hierbei eine zusätzliche Suche nach Datensätzen durch, die NULL-Werte enthalten.
index_merge Dieser Join-Typ gibt an, dass die Indexverschmelzungsoptimierung verwendet wird. mehr infos dazu
unique_subquery Dieser Typ ersetzt ref in einigen IN-Unterabfragen
index_subquery Dieser Join-Typ ähnelt unique_subquery. Er ersetzt IN-Unterabfragen
range Es werden nur Datensätze abgerufen, die in einem gegebenen Bereich liegen. Sie werden anhand eines Indexes ausgewählt.
index Dieser Join-Typ ist mit ALL bis auf die Tatsache identisch, dass nur der Indexbaum gescannt wird.
ALL Ein vollständiger Tabellenscan wird für jede Kombination von Datensätzen aus den vorherigen Tabellen durchgeführt.

extra

Distinct MySQL sucht nach unterschiedlichen Werten, d. h., die Suche nach weiteren Datensätzen zur aktuellen Datensatzkombination wird beendet, sobald der erste Datensatz gefunden wurde.
Not exists vvv
range checked for each record (index map: N) vvv
Using filesort MySQL muss einen zusätzlichen Durchlauf vornehmen, um zu ermitteln, wie die Datensätze in sortierter Reihenfolge abgerufen werden können.
Using index Die Spaltendaten werden ausschließlich unter Verwendung von Angaben im Indexbaum aus der Tabelle abgerufen – es erfolgt kein zusätzlicher Suchvorgang, um jeweils den eigentlichen Datensatz auszulesen.
Using temporary Um die Abfrage aufzulösen, muss MySQL eine Temporärtabelle zur Aufnahme des Ergebnisses erstellen. Dies geschieht typischerweise, wenn die Abfrage GROUP BY- und ORDER BY-Klauseln enthält, die Spalten unterschiedlich auflisten.
Using where Mit einer WHERE-Klausel wird festgelegt, welche Datensätze mit der nächsten Tabelle verglichen oder an den Client gesendet werden. Wollen Sie Ihre Abfragen so schnell wie möglich machen, dann sollten Sie nach den Extra-Werten Using filesort und Using temporary suchen.
Using sort_union(…), Using union(…), Using intersect(…) to do…
Using index for group-by to do…
Using where with pushed condition to do…

Weblinks

Folgende Artikel könnten auch interessieren