MySQL-Referenzhandbuch :: 6.2.1 EXPLAIN-Syntax (Informationen über ein SELECT erhalten)

MySQL-Referenzhandbuch

6.2 SELECTs und andere Anfragen optimieren
6.2.1 EXPLAIN-Syntax (Informationen über ein SELECT erhalten)
6.2.2 Anfragen-Performance abschätzen
6.2.3 Geschwindigkeit von SELECT-Anfragen
6.2.4 Wie MySQL WHERE-Klauseln optimiert
6.2.5 Wie MySQL DISTINCT optimiert
6.2.6 Wie MySQL LEFT JOIN optimiert
6.2.7 Wie MySQL LIMIT optimiert
6.2.8 Geschwindigkeit von INSERT-Anfragen
6.2.9 Geschwindigkeit von UPDATE-Anfragen
6.2.10 Geschwindigkeit von DELETE-Anfragen
6.2.11 Weitere Optimierungstipps

Additional languages

Previous / Next / Up / Table of Contents

6.2.1. `EXPLAIN`-Syntax (Informationen über ein `SELECT` erhalten)

     EXPLAIN tabelle
oder EXPLAIN SELECT select_optionen

EXPLAIN tabelle ist ein Synonym für DESCRIBE tabelle oder SHOW COLUMNS FROM tabelle.

Wenn Sie einem SELECT-Statement das Schlüsselwort EXPLAIN voran stellen, erklärt MySQL explains, wie er das SELECT ausführen würde, indem Informationen darüber gemacht werden, wie Tabellen verknüpft (Join) werden und in welcher Reihenfolge.

Mit der Hilfe von EXPLAIN können Sie erkennen, wo Sie Tabellen Indexe hinzufügen müssen, um ein schnelleres SELECT zu erhalten, das Indexe benutzt, um die Datensätze zu finden. Ausserdem sehen Sie, ob der Optimierer die Tabellen in optimaler Reihenfolge verknüpft. Um den Optimierer zu zwingen, eine spezielle Verknüpfungsreihenfolge bei einem SELECT-Statement einzuhalten, fügen Sie eine STRAIGHT_JOIN-Klausel hinzu.

Bei nicht einfachen Verknüpfungen (Joins) gibt EXPLAIN für jede Tabelle, die im SELECT-Statement benutzt wurde, eine Informationszeile zurück. Die Tabellen sind in der Reihenfolge aufgelistet, in der sie gelesen werden würden. MySQL löst alle Joins mit einer Single-Sweep-Multi-Join-Methode auf. Das bedeutet, dass MySQL eine Zeile aus der ersten Tabelle liest, dann die passende Zeile in der zweiten Tabelle sucht, dann in der dritten Tabelle usw. Wenn alle Tabellen verarbeitet wurden, gibt er die ausgewählten Spalten aus und geht rückwärts durch die Tabellenliste durch, bis eine Tabelle gefunden wird, bei der es weitere passende Zeilen gibt. Die nächste Zeile wird aus dieser Tabelle gelesen, und der Prozess fährt mit der nächsten Tabelle fort.

Die Ausgabe von EXPLAIN enthält folgende Spalten:

table

Die Tabelle, auf die sich die Ausgabezeile bezieht.
type

Der Join-Typ. Informationen über die verschiedenen Typen finden Sie weiter unten.
possible_keys

Die possible_keys-Spalte gibt an, welche Indexe MySQL verwenden konnte, um Zeilen in dieser Tabelle zu finden. Beachten Sie, dass diese Spalte völlig unabhängig von der Reihenfolge der Tabellen ist. Das heißt, dass einige der Schlüssel in possible_keys möglicherweise bei der tatsächlich verwendeten Tabellenreihenfolge nicht verwendbar sind.

Wenn diese Spalte leer ist, gibt es keine relevanten Indexe. In diesem Fall können Sie die Performance Ihrer Anfrage womöglich verbessern, indem Sie die WHERE-Klausel untersuchen, um festzustellen, ob diese auf eine oder mehrere Spalten verweist, die zweckmäßigerweise indiziert werden sollten. Wenn das der Fall ist, erzeugen Sie einen entsprechenden Index und prüfen Sie die Anfrage noch einmal mit EXPLAIN. See Abschnitt 7.5.4, „ALTER TABLE-Syntax“.

Um zu sehen, welche Indexe eine Tabelle hat, benutzen Sie SHOW INDEX FROM tabelle.
key

Die key-Spalte gibt den Schlüssel an, den MySQL tatsächlich benutzen wird. Der Schlüssel ist NULL, wenn kein Index gewählt wurde. Wenn MySQL den falschen Index wählt, können Sie ihn wahrscheinlich zwingen, einen anderen Index zu nehmen, indem Sie myisamchk --analyze oder Abschnitt 5.4.6.1, „Aufrufsyntax von myisamchk“ ausführen oder USE INDEX/IGNORE INDEX benutzen. See Abschnitt 7.4.1.1, „JOIN-Syntax“.
key_len

Die key_len-Spalte gibt die Länge des Schlüssels an, den MySQL benutzen wird. Die Länge ist NULL, wenn key NULL ist. Beachten Sie, dass Ihnen das angibt, wie viele Teile eines mehrteiligen Schlüssels MySQL tatsächlich benutzen wird.
ref

Die ref-Spalte zeigt an, welche Spalten oder Konstanten beim key benutzt werden, um Zeilen aus der Tabelle auszuwählen.
rows

die rows-Spalte gibt die Anzahl von Zeilen an, von denen MySQL annimmt, dass es sie untersuchen muss, um die Anfrage auszuführen.
Extra

Diese Spalte enthält zusätzliche Informationen darüber, wie MySQL die Anfrage auflösen wird. Folgende unterschiedliche Text-Zeichenketten können in dieser Spalte stehen:
- Distinct
  
  MySQL wird die Suche nach weiteren Zeilen für die aktuelle Zeilenkombination nicht fortsetzen, nachdem er die erste passende Zeile gefunden hat.
- Not exists
  
  MySQL war in der Lage, eine LEFT JOIN-Optimierung der Anfrage durchzuführen, und wird keine weiteren Spalten in dieser Tabelle für die vorherige Zeilenkombination mehr untersuchen, nachdem er eine Zeile gefunden hat, die den LEFT JOIN-Kriterien entspricht.
  
  Hier ist ein Beispiel dafür:
```
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
```
  Angenommen, t2.id ist mit NOT NULL definiert. In diesem Fall scannt MySQL t1 und schlägt die Zeilen in t2 über t1.id nach. Wenn MySQL eine übereinstimmende Zeile in t2 findet, weiß er, dass t2.id nie NULL sein kann und scannt nicht durch den Rest der Zeilen in t2, die dieselbe id haben. Mit anderen Worten, für jede Zeile in t1 muss MySQL nur ein einziges Mal in t2 nachschlagen, unabhängig davon, wie viel übereinstimmende Zeilen es in t2 gibt.
- range checked for each record (index map: #)
  
  MySQL hat keinen gut geeigneten Index zum Benutzen gefunden. Statt dessen wird er für jede Zeilenkombination in der vorherigen Tabelle eine Prüfung vornehmen, welchen Index er benutzen soll (falls überhaupt) und diesen Index benutzen, um Zeilen aus der Tabelle abzurufen. Das ist nicht sehr schnell, aber immer noch schneller, als einen Join ohne Index durchzuführen.
- Using filesort
  
  MySQL braucht einen zusätzlichen Durchgang, um herauszufinden, wie die Zeilen in sortierter Reihenfolge abgerufen werden sollen. Die Sortierung wird durchgeführt, indem in Abhängigkeit vom join type durch alle Zeilen durchgegangen wird und der Sortierschlüssel und Zeiger auf die Zeilen für alle Zeilen gespeichert wird, die dem WHERE entsprechen. Danach werden die Schlüssel sortiert. Schließlich werden die Zeilen in sortierter Reihenfolge abgerufen.
- Using index
  
  Die Spalteninformation wird aus der Tabelle abgerufen, indem nur Informationen aus dem Index-Baum benutzt werden, ohne dass zum Suchen zusätzlich in den tatsächlichen Zeilen gelesen werden muss. Das kann gemacht werden, wenn alle benutzten Spalten der Tabelle Teil desselben Indexes sind.
- Using temporary
  
  Um die Anfrage aufzulösen muss MySQL eine temporäre Tabelle erzeugen, die das Ergebnis enthält. Das passiert typischerweise, wenn Sie ein ORDER BY auf eine andere Spalte setzen als auf die, die Sie im GROUP BY angegeben haben.
- Where used
  
  Eine WHERE-Klausel wird benutzt, um zu begrenzen, bei welchen Zeilen auf Übereinstimmung in der nächsten Tabelle gesucht wird oder welche Zeilen an den Client geschickt werden. Wenn Sie diese Information nicht haben und die Tabelle vom Typ ALL oder index ast, ist vielleicht in Ihrer Anfrage etwas falsch (falls Sie nicht vorhaben, alle Zeilen aus der Tabelle zu holen / zu untersuchen).
Wenn Sie wollen, dass Ihre Anfragen so schnell wie möglich laufen, sollten Sie auf Using filesort und Using temporary achten.

Die verschiedenen Join-Typen sind unten aufgeführt, sortiert vom besten zum schlechtesten Typ:

system

Die Tabelle hat nur eine Zeile (= Systemtabelle). Das ist ein spezieller Fall des const-Join-Typs.
const

Die Tabelle hat höchsten eine übereinstimmende Zeile, die am Anfang der Anfrage gelesen werden wird. Weil es nur eine Zeile gibt, können Spaltenwerte in dieser Zeile vom Optimierer als Konstanten betrachtet werden. const-Tabellen sind sehr schnell, weil sie nur einmal gelesen werden!
eq_ref

Aus dieser Tabelle wird für jede Zeilenkombination der vorherigen Tabellen eine Zeile gelesen. Das ist der bestmögliche Join-Typ, ausgenommen die const-Typen. Er wird benutzt, wenn alle Teile eines Indexes vom Join benutzt werden und der Index UNIQUE oder ein PRIMARY KEY ist.
ref

Alle Zeilen mit übereinstimmenden Index-Werten werden für jede Zeilenkombination der vorherigen Tabellen gelesen. ref wird benutzt, wenn der Join nur das am weitesten links stehende Präfix des Schlüssels benutzt, oder wenn der Schlüssel nicht UNIQUE oder ein PRIMARY KEY ist (mit anderen Worten, wenn der Join auf der Grundlage des Schlüsselwerts keine einzelne Zeile auswählen kann). Wenn der Schlüssel, der benutzt wird, nur mit einigen wenigen Zeilen übereinstimmt, ist dieser Join-Typ gut.
range

Nur Zeilen, die innerhalb eines angegebenen Bereichs sind, werden abrufen, wobei ein Index benutzt wird, um die Zeilen auszuwählen. Die key-Spalte gibt an, welcher Index benutzt wird. key_len enthält den längsten Schlüsselteil, der benutzt wurde. Die ref-Spalte ist für diesen Typ NULL.
index

Das ist dasselbe wie ALL, ausser dass nur der Index-Baum gescannt wird. Das ist üblicherweise schneller als ALL, weil die Index-Datei üblicherweise kleiner ist als die Daten-Datei.
ALL

Für jede Zeilenkombination der vorherigen Tabellen wird ein kompletter Tabellenscan durchgeführt. Das ist normalerweise nicht gut, wenn die Tabelle die erste Tabelle ist, die nicht als const gekennzeichnet ist, und üblicherweise sehr schlecht in allen anderen Fällen. Sie können ALL normalerweise vermeiden, indem Sie mehr Indexe hinzufügen, so dass die Zeile auf der Grundlage der Konstanten-Werte oder Spaltenwerte von früheren Tabellen abgerufen werden kann.

Sie erhalten einen guten Anhaltspunkt, wie gut ein Join ist, wenn Sie alle Werte in der rows-Spalte der EXPLAIN-Ausgabe multiplizieren. Das sollte grob aussagen, wie vielen Zeilen MySQL untersuchen muss, um die Anfrage auszuführen. Diese Anzahl wird auch benutzt, wenn Sie Anfragen mit der max_join_size-Variablen begrenzen. See Abschnitt 6.5.2, „Serverparameter tunen“.

Das folgende Beispiel zeigt, wie ein JOIN progressiv optimiert werden kann, indem die Informationen genutzt werden, die EXPLAIN bereit stellt.

Angenommen, Sie haben unten stehendes SELECT-Statement, das Sie mit EXPLAIN untersuchen:

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
            tt.ProjectReference, tt.EstimatedShipDate,
            tt.ActualShipDate, tt.ClientID,
            tt.ServiceCodes, tt.RepetitiveID,
            tt.CurrentProcess, tt.CurrentDPPerson,
            tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
            et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
            AND tt.ActualPC = et.EMPLOYID
            AND tt.AssignedPC = et_1.EMPLOYID
            AND tt.ClientID = do.CUSTNMBR;

Nehmen wir bei diesem Beispiel folgendes an:

Die Spalten, die verglichen werden, wurden wie folgt deklariert:

Tabelle	Spalte	Spaltentyp
`tt`	`ActualPC`	`CHAR(10)`
`tt`	`AssignedPC`	`CHAR(10)`
`tt`	`ClientID`	`CHAR(10)`
`et`	`EMPLOYID`	`CHAR(15)`
`do`	`CUSTNMBR`	`CHAR(15)`

Die Tabellen haben die unten stehenden Indexe:

Tabelle Index

tt ActualPC

tt AssignedPC

tt ClientID

et EMPLOYID (primary key)

do CUSTNMBR (primary key)
Die tt.ActualPC-Werte sind nicht gleichmäßig verteilt.

Anfangs, bevor die Optimierung durchgeführt wurde, ergab das EXPLAIN-Statement folgende Informationen:

table type possible_keys                key  key_len ref  rows  Extra
et    ALL  PRIMARY                      NULL NULL    NULL 74
do    ALL  PRIMARY                      NULL NULL    NULL 2135
et_1  ALL  PRIMARY                      NULL NULL    NULL 74
tt    ALL  AssignedPC,ClientID,ActualPC NULL NULL    NULL 3872
      range checked for each record (key map: 35)

Weil type bei jeder Tabelle ALL ist, zeigt die Ausgabe, dass MySQL eine komplette Verknüpfung (Full Join) aller Tabellen durchführt! Das dauert recht lange, weil das Produkt der Zeilenanzahl in jeder Tabelle untersucht werden muss! In diesem Fall ist das 74 * 2.135 * 74 * 3.872 = 45.268.558.720 Zeilen. Wenn die Tabellen größer wären, können Sie sich vorstellen, wie lange das dauern würde.

Ein Problem liegt hier darin, dass MySQL (noch) keine Indexe auf Spalten effizient benutzen kann, wenn sie unterschiedlich deklariert sind. In diesem Zusammenhang sind VARCHAR und CHAR dasselbe, es sei denn, sie sind mit unterschiedlichen Längen deklariert. Weil tt.ActualPC als CHAR(10) und et.EMPLOYID als CHAR(15) deklariert ist, gibt eine Unstimmigkeit der Längen.

Um diese Ungleichheit der Spaltenlängen zu beheben, benutzen Sie ALTER TABLE, um ActualPC von 10 auf 15 Zeichen zu verlängern:

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

Jetzt sind tt.ActualPC und et.EMPLOYID beide VARCHAR(15). Eine erneute Ausführung des EXPLAIN-Statements ergibt dieses Ergebnis:

table type   possible_keys   key     key_len rew         rows    Extra
tt    ALL    AssignedPC,ClientID,ActualPC NULL NULL NULL 3872    where used
do    ALL    PRIMARY         NULL    NULL    NULL        2135
      range checked for each record (key map: 1)
et_1  ALL    PRIMARY         NULL    NULL    NULL        74
      range checked for each record (key map: 1)
et    eq_ref PRIMARY         PRIMARY 15      tt.ActualPC 1

Das ist nicht perfekt, aber viel besser (das Produkt der rows-Werte ist jetzt um einen Faktor 74 niedriger). Diese Version wird innerhalb von ein paar Sekunden ausgeführt.

Eine zweite Änderung kann durchgeführt werden, um die Unstimmigkeit der Spaltenlängen für die tt.AssignedPC = et_1.EMPLOYID- und tt.ClientID = do.CUSTNMBR-Vergleiche zu beheben:

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
                      MODIFY ClientID   VARCHAR(15);

Jetzt ergibt EXPLAIN folgende Ausgabe:

table type   possible_keys   key     key_len rew            rows     Extra
et    ALL    PRIMARY         NULL    NULL    NULL           74
tt    rew    AssignedPC,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where used
et_1  eq_ref PRIMARY         PRIMARY 15      tt.AssignedPC  1
do    eq_ref PRIMARY         PRIMARY 15      tt.ClientID    1

Das ist fast so gut, wie es überhaupt geht.

Das verbleibende Problem besteht darin, dass MySQL vorgabemäßig annimmt, dass die Werte in der tt.ActualPC-Spalte gleichmäßig verteilt sind, was in der tt-Tabelle nicht der Fall ist. Glücklicherweise ist es einfach, MySQL darüber zu informieren:

shell> myisamchk --analyze PFAD_ZU_MYSQL_DATENBANK/tt
shell> mysqladmin refresh

Jetzt ist der Join perfekt und EXPLAIN ergibt dieses Ergebnis:

table type   possible_keys   key     key_len ref            rows    Extra
tt    ALL    AssignedPC,ClientID,ActualPC NULL NULL NULL    3872    where used
et    eq_ref PRIMARY         PRIMARY 15      tt.ActualPC    1
et_1  eq_ref PRIMARY         PRIMARY 15      tt.AssignedPC  1
do    eq_ref PRIMARY         PRIMARY 15      tt.ClientID    1

Beachten Sie, dass die rows-Spalte in der Ausgabe von EXPLAIN eine gehobene Form von Vermutung des MySQL-Join-Optimierers ist. Um eine Anfrage zu optimieren, sollten Sie überprüfen, ob diese Zahlen der Wahrheit nahe kommen. Wenn nicht, erhalten Sie eventuell bessere Performance, wenn Sie STRAIGHT_JOIN in Ihrem SELECT-Statement benutzen und versuchen, die Tabellen in der FROM-Klausel in anderer Reihenfolge anzugeben.

Top / Previous / Next / Up / Table of Contents

MySQL-Referenzhandbuch :: 6.2.1 EXPLAIN-Syntax (Informationen über ein SELECT erhalten)

Additional languages

6.2.1. EXPLAIN-Syntax (Informationen über ein SELECT erhalten)

6.2.1. `EXPLAIN`-Syntax (Informationen über ein `SELECT` erhalten)