EXPLAIN tbl_name
Oder:
EXPLAIN [EXTENDED | PARTITIONS] SELECT
select_options
Die EXPLAIN
-Anweisung kann entweder als
Synonym für DESCRIBE
oder als Möglichkeit
verwendet werden, um Informationen bezüglich der Frage zu
ermitteln, wie MySQL eine SELECT
-Anweisung
ausführt:
EXPLAIN
ist synonym zu
tbl_name
DESCRIBE
oder
tbl_name
SHOW COLUMNS FROM
.
tbl_name
Wenn Sie einer SELECT
-Anweisung das
Schlüsselwort EXPLAIN
voranstellen,
zeigt MySQL Informationen des Optimierers zum
Ausführungsplan der Abfrage an. MySQL erläutert also, wie
es die SELECT
-Anweisung verarbeiten
würde, und gibt zudem an, wie und in welcher Reihenfolge
Tabellen miteinander verknüpft werden.
EXPLAIN PARTITIONS
ist seit MySQL 5.1.5
verfügbar. Es ist nur praktisch, wenn Sie Abfragen
untersuchen, die sich auf partitionierte Tabellen beziehen.
Detaillierte Informationen finden Sie in
Abschnitt 17.3.4, „Abruf von Informationen über Partitionen“.
Dieser Abschnitt beschreibt die zweite Verwendungsmöglichkeit
von EXPLAIN
: die Anforderung von
Informationen zum Abfrageausführungsplan. Eine Beschreibung der
Anweisungen DESCRIBE
und SHOW
COLUMNS
finden Sie in Abschnitt 13.3.1, „DESCRIBE
(Informationen über Spalten abrufen)“, und
Abschnitt 13.5.4.3, „SHOW COLUMNS
“.
Mithilfe von EXPLAIN
können Sie ermitteln,
wo Sie Indizes für Tabellen konfigurieren sollten, um
SELECT
-Anweisungen zu beschleunigen, die
Datensätze mithilfe von Indizes finden. Sie können mit
EXPLAIN
auch überprüfen, ob der Optimierer
die Tabellen in optimaler Reihenfolge verknüpft. Um den
Optimierer zur Verwendung einer Verknüpfungsreihenfolge zu
zwingen, die der Reihenfolge entspricht, in der die Tabellen in
der SELECT
-Anweisung aufgeführt sind,
beginnen Sie die Anweisung mit SELECT
STRAIGHT_JOIN
statt mit SELECT
.
Wenn das Problem auftritt, dass Indizes nicht benutzt werden,
obwohl Sie annehmen, dass dies eigentlich der Fall sein sollte,
dann führen Sie ANALYZE TABLE
aus, um die
Tabellenstatistiken wie etwa die Kardinalität der Schlüssel,
die sich auf die durch den Optimierer vorgenommene Auswahl
auswirken kann, zu aktualisieren. Siehe auch
Abschnitt 13.5.2.1, „ANALYZE TABLE
“.
EXPLAIN
gibt je einen Datensatz für jede in
der SELECT
-Anweisung verwendete Tabelle
zurück. Die Tabellen sind in der Ausgabe in der Reihenfolge
aufgeführt, in der MySQL sie bei der Verarbeitung der Abfrage
lesen würde. MySQL löst alle Joins mithilfe einer Methode auf,
die Single-Sweep-Multi-Join heißt.
Hierbei liest MySQL einen Datensatz aus der ersten Tabelle und
sucht dann einen passenden Datensatz in der zweiten Tabelle, der
dritten Tabelle usw. Sind alle Tabellen verarbeitet, dann gibt
MySQL die gewählten Spalten aus und durchsucht die
Tabellenliste in umgekehrter Reihenfolge, bis eine Tabelle
gefunden wird, bei der mehr passende Datensätze vorhanden sind.
Der nächste Datensatz wird aus genau dieser Tabelle gelesen,
und dann wird der Prozess mit der nächsten Tabelle fortgesetzt.
Wenn das Schlüsselwort EXTENDED
verwendet
wird, erzeugt EXPLAIN
zusätzliche
Informationen, die durch Absetzen einer SHOW
WARNINGS
-Anweisung nach der
EXPLAIN
-Anweisung angezeigt werden können.
Diese Informationen geben Hinweise zum Optimierungsprozess,
z. B. wie der Optimierer Tabellen- und Spaltennamen in der
SELECT
-Anweisung qualifiziert oder wie die
SELECT
-Anweisung nach der Anwendung der
Umformulierungs- und Optimierungsregeln aussieht.
Hinweis: Sie können die
Schlüsselwörter EXTENDED
und
PARTITIONS
nicht gemeinsam in derselben
EXPLAIN
-Anweisung verwenden.
Jeder von EXPLAIN
ausgegebene Datensatz
enthält Angaben zu genau einer Tabelle. Dabei enthält jeder
Datensatz die folgenden Spalten:
id
Der SELECT
-Bezeichner. Dies ist die
Sequenznummer der SELECT
-Anweisung
innerhalb der Abfrage.
select_type
Der Typ der SELECT
-Anweisung. Dies kann
jeder der in der folgenden Tabelle aufgeführten Typen sein:
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). |
DEPENDENT
bezeichnet normalerweise die
Verwendung einer korrelierten Unterabfrage. Siehe auch
Abschnitt 13.2.8.7, „Korrelierte Unterabfragen“.
table
Die Tabelle, die der Ausgabedatensatz referenziert.
type
Der Join-Typ. Die verschiedenen Join-Typen sind nachfolgend aufgelistet, sortiert vom besten bis zum schlechtesten:
Diese Tabelle hat nur einen Datensatz (Systemtabelle).
Dies ist ein Sonderfall des Join-Typs
const
.
Die Tabelle hat maximal einen passenden Datensatz, der
beim Start der Abfrage gelesen wird. Da nur ein
Datensatz vorhanden ist, können die Werte aus den
Spalten dieses Datensatzes vom Optimierer im Folgenden
als Konstanten behandelt werden.
const
-Tabellen sind sehr schnell, da
sie nur einmal gelesen werden.
const
wird verwendet, wenn Sie alle
Teile eines Primärschlüssels oder eines eindeutigen
Indexes mit Konstantenwerten vergleichen. In der
folgenden Abfrage kann
tbl_name
als
const
-Tabelle verwendet werden:
SELECT * FROMtbl_name
WHEREprimary_key
=1; SELECT * FROMtbl_name
WHEREprimary_key_part1
=1 ANDprimary_key_part2
=2;
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.
eq_ref
kann für indizierte Spalten
benutzt werden, die mithilfe des Operators
=
verglichen werden. Der
Vergleichswert kann eine Konstante oder ein Ausdruck
sein, der Spalten aus Tabellen verwendet, die vor dieser
Tabelle gelesen wurden. In den folgenden Beispielen kann
MySQL einen eq_ref
-Join zur
Verarbeitung von ref_table
verwenden:
SELECT * FROMref_table
,other_table
WHEREref_table
.key_column
=other_table
.column
; SELECT * FROMref_table
,other_table
WHEREref_table
.key_column_part1
=other_table
.column
ANDref_table
.key_column_part2
=1;
ref
Alle Datensätze mit passenden Indexwerten werden aus
dieser Tabelle für jede Kombination von Datensätzen in
den vorherigen Tabellen gelesen. ref
wird benutzt, wenn der Join nur ein linkes Präfix des
Schlüssels verwendet oder der Schlüssel kein
Primärschlüssel und auch kein eindeutiger Index ist
(d. h., wenn der Join keinen einzelnen Datensatz
basierend auf dem Schlüsselwert auswählen kann). Wenn
der verwendete Schlüssel nur einigen wenigen
Datensätzen entspricht, ist dies ein guter Join-Typ.
ref
kann für indizierte Spalten
benutzt werden, die mithilfe der Operatoren
=
oder <=>
verglichen werden. In den folgenden Beispielen kann
MySQL einen ref
-Join zur Verarbeitung
von ref_table
verwenden:
SELECT * FROMref_table
WHEREkey_column
=expr
; SELECT * FROMref_table
,other_table
WHEREref_table
.key_column
=other_table
.column
; SELECT * FROMref_table
,other_table
WHEREref_table
.key_column_part1
=other_table
.column
ANDref_table
.key_column_part2
=1;
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. Diese
Join-Typ-Optimierung wird meistens bei der Auflösung
von Unterabfragen verwendet. In den folgenden Beispielen
kann MySQL einen ref_or_null
-Join zur
Verarbeitung von ref_table
verwenden:
SELECT * FROMref_table
WHEREkey_column
=expr
ORkey_column
IS NULL;
Siehe auch Abschnitt 7.2.7, „IS NULL
-Optimierung“.
index_merge
Dieser Join-Typ gibt an, dass die
Indexverschmelzungsoptimierung verwendet wird. In diesem
Fall enthält die Spalte key
im
ausgegebenen Datensatz eine Liste der verwendeten
Indizes, und key_len
enthält eine
Liste der längsten Schlüsselteile für die verwendeten
Indizes. Weitere Informationen finden Sie unter
Abschnitt 7.2.6, „Optimierung durch Indexverschmelzung“.
unique_subquery
Dieser Typ ersetzt ref
in einigen
IN
-Unterabfragen der folgenden Form:
value
IN (SELECTprimary_key
FROMsingle_table
WHEREsome_expr
)
unique_subquery
ist eine
Nachschlagefunktion für Indizes, die zur
Effizienzsteigerung die Unterabfrage vollständig
ersetzt.
index_subquery
Dieser Join-Typ ähnelt
unique_subquery
. Er ersetzt
IN
-Unterabfragen, funktioniert aber
bei nichteindeutigen Indizes in Unterabfragen folgender
Form:
value
IN (SELECTkey_column
FROMsingle_table
WHEREsome_expr
)
range
Es werden nur Datensätze abgerufen, die in einem
gegebenen Bereich liegen. Sie werden anhand eines
Indexes ausgewählt. Die Spalte key
im Ausgabedatensatz zeigt an, welcher Index verwendet
wird. key_len
enthält den längsten
verwendeten Schlüsselteil. Die Spalte
ref
ist für diesen Typ
NULL
.
range
kann verwendet werden, wenn
eine Schlüsselspalte unter Verwendung eines der
Operatoren =
,
<>
, >
,
>=
, <
,
<=
, IS NULL
,
<=>
, BETWEEN
oder IN
mit einer Konstante
verglichen wird:
SELECT * FROMtbl_name
WHEREkey_column
= 10; SELECT * FROMtbl_name
WHEREkey_column
BETWEEN 10 and 20; SELECT * FROMtbl_name
WHEREkey_column
IN (10,20,30); SELECT * FROMtbl_name
WHEREkey_part1
= 10 ANDkey_part2
IN (10,20,30);
index
Dieser Join-Typ ist mit ALL
bis auf
die Tatsache identisch, dass nur der Indexbaum gescannt
wird. Insofern ist er in der Regel schneller als
ALL
, weil die Indexdatei gewöhnlich
kleiner ist als die Datendatei.
MySQL kann diesen Join-Typ verwenden, wenn die Abfrage nur Spalten benutzt, die Teil eines einzelnen Indexes sind.
ALL
Ein vollständiger Tabellenscan wird für jede
Kombination von Datensätzen aus den vorherigen Tabellen
durchgeführt. Dies ist normalerweise nicht von Vorteil,
wenn die Tabelle die erste nicht als
const
gekennzeichnete Tabelle ist,
und in allen anderen Fällen sogar ganz
schlimm. Sie können ALL
normalerweise vermeiden, indem Sie Indizes hinzufügen,
die das Abrufen von Datensätzen aus der Tabelle
basierend auf Konstanten- oder Spaltenwerten aus
früheren Tabellen erlauben.
possible_keys
Die Spalte possible_keys
gibt an, unter
welchen Indizes MySQL auswählen kann, um Datensätze in
dieser Tabelle zu suchen. Beachten Sie, dass diese Spalte
vollständig unabhängig von der Reihenfolge der Tabellen
ist, wie sie in der Ausgabe von EXPLAIN
angezeigt wird. Das bedeutet, dass einige der Schlüssel in
possible_keys
mit der erzeugten
Tabellenreihenfolge unter Umständen praktisch nicht
verwendbar sind.
Wenn diese Spalte NULL
ist, gibt es keine
relevanten Indizes. In diesem Fall können Sie die
Leistungsfähigkeit Ihrer Abfrage möglicherweise
verbessern, indem Sie die WHERE
-Klausel
daraufhin untersuchen, ob sie eine oder mehrere Spalten
referenziert, die für die Indexerstellung geeignet wären.
Sollte dies der Fall sein, so erstellen Sie einen passenden
Index und überprüfen die Abfrage dann mit
EXPLAIN
erneut. Siehe auch
Abschnitt 13.1.2, „ALTER TABLE
“.
Um anzuzeigen, welche Indizes eine Tabelle hat, verwenden
Sie SHOW INDEX FROM
.
tbl_name
key
Die Spalte key
gibt den Schlüssel(index)
an, für dessen Verwendung sich MySQL tatsächlich
entschieden hat. Der Schlüssel ist NULL
,
wenn kein Index ausgewählt wurde. Um das Verwenden oder
Ignorieren eines Indexes, der in der Spalte
possible_keys
aufgeführt ist, durch
MySQL zu erzwingen oder zu ignorieren, verwenden Sie
FORCE INDEX
oder USE
INDEX
bzw. IGNORE INDEX
in
Ihrer Abfrage. Siehe auch Abschnitt 13.2.7, „SELECT
“.
Bei MyISAM
- und
BDB
-Tabellen erleichtert die Ausführung
von ANALYZE TABLE
dem Optimierer die
Auswahl geeigneter Indizes. Bei
MyISAM
-Tabellen tut myisamchk
--analyze dasselbe. Siehe auch
Abschnitt 13.5.2.1, „ANALYZE TABLE
“, und
Abschnitt 5.10.4, „Benutzung von myisamchk
für Tabellenwartung und
Absturzreparatur“.
key_len
Die Spalte key_len
gibt die Länge des
Schlüssels an, für dessen Verwendung sich MySQL
entschieden hat. Die Länge ist NULL
,
wenn in der Spalte key
NULL
steht. Beachten Sie, dass der Wert
von key_len
Ihnen die Feststellung
gestattet, wie viele Teile eines mehrteiligen Schlüssels
MySQL tatsächlich verwendet.
ref
Die Spalte ref
zeigt an, welche Spalten
oder Konstanten mit dem in der Spalte key
genannten Index verglichen werden, um Datensätze aus der
Tabelle auszuwählen.
rows
Die Spalte rows
gibt die Anzahl der
Datensätze an, die MySQL glaubt untersuchen zu müssen, um
die Abfrage ausführen zu können.
Extra
Diese Spalte enthält zusätzliche Angaben dazu, wie MySQL die Abfrage auflöst. Es folgt eine Erläuterung der Werte, die in dieser Spalte erscheinen können:
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
MySQL konnte eine LEFT
JOIN
-Optimierung an der Abfrage vornehmen und
untersucht für die vorhergehende Datensatzkombination
keine weiteren Datensätze in dieser Tabelle, sobald ein
Datensatz gefunden wurde, der den LEFT
JOIN
-Kriterien entspricht. Hier ein Beispiel
für einen Abfragetyp, der auf diese Weise optimiert
werden kann:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
Nehmen wir an, t2.id
sei als
NOT NULL
definiert. In diesem Fall
scannt MySQL t1
und sucht die
Datensätze in t2
unter Verwendung
der Werte aus t1.id
heraus. Findet
MySQL einen passenden Datensatz in
t2
, dann weiß es, dass
t2.id
niemals NULL
sein kann, und scannt die weiteren Datensätze in
t2
, die denselben
id
-Wert aufweisen, nicht mehr. Für
jeden Datensatz in t1
muss MySQL also
nur einen einzigen Suchvorgang in t2
durchführen – unabhängig davon, wie viele
entsprechende Datensätze in t2
tatsächlich vorhanden sind.
range checked for each record (index map:
N
)
MySQL hat keinen geeigneten Index gefunden, aber
festgestellt, dass einige der Indizes verwendet werden
könnten, nachdem Spaltenwerte aus vorhergehenden
Tabellen bekannt geworden sind. Für jede
Datensatzkombination in den vorherigen Tabellen
überprüft MySQL, ob es möglich ist, zum Abrufen von
Datensätzen eine der Zugriffsmethoden
range
oder
index_merge
zu verwenden. Dies ist
nicht sehr schnell, aber immer noch schneller als die
Durchführung eines Joins ohne Index. Die
Anwendbarkeitskriterien entsprechen den in
Abschnitt 7.2.5, „Bereichsoptimierung“, und
Abschnitt 7.2.6, „Optimierung durch Indexverschmelzung“,
beschriebenen, nur sind hier alle Spaltenwerte für die
vorhergehende Tabelle bekannt und werden als Konstanten
betrachtet.
Using filesort
MySQL muss einen zusätzlichen Durchlauf vornehmen, um
zu ermitteln, wie die Datensätze in sortierter
Reihenfolge abgerufen werden können. Diese Sortierung
erfolgt, indem alle Datensätze entsprechend dem
Join-Typ überprüft und Sortierschlüssel sowie der
Zeiger auf den Datensatz für alle Datensätze
gespeichert werden, die der
WHERE
-Klausel entsprechen. Die
Schlüssel werden dann sortiert und die Datensätze
entsprechend in sortierter Reihenfolge abgerufen. Siehe
auch
Abschnitt 7.2.12, „ORDER BY
-Optimierung“.
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. Diese Strategie kann verwendet werden, wenn die Abfrage nur Spalten benutzt, die Teil eines einzelnen Indexes sind.
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. Sofern
Sie nicht gezielt alle Datensätze aus der Tabelle
abrufen oder untersuchen wollen, haben Sie in der
Abfrage einen Fehler gemacht, wenn der Wert
Extra
nicht Using
where
ist und ALL
oder
index
als Join-Typ für die Tabelle
angegeben ist.
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(…)
Diese Werte geben an, wie Indexscans für den Join-Typ
index_merge
zusammengefasst werden.
Weitere Informationen finden Sie in
Abschnitt 7.2.6, „Optimierung durch Indexverschmelzung“.
Using index for group-by
Ähnlich wie die Tabellenzugriffsmethode Using
index
gibt Using index for
group-by
an, dass MySQL einen Index gefunden
hat, der zum Abrufen aller Datensätze einer
GROUP BY
- oder
DISTINCT
-Abfrage ohne zusätzlichen
Festplattenzugriff auf die eigentliche Tabelle verwendet
werden kann. Außerdem wird der Index auf die
effizienteste Art und Weise verwendet, sodass für jede
Gruppe nur ein paar wenige Indexeinträge gelesen
werden. Detaillierte Informationen finden Sie in
Abschnitt 7.2.13, „GROUP BY
-Optimierung“.
Using where with pushed condition
Dieses Element ist ausschließlich
für NDB Cluster
-Tabellen verfügbar.
Es bedeutet, dass MySQL Cluster einen
Bedingungs-Pushdown verwendet, um
die Effizienz eines direkten Vergleichs
(=
) zwischen einer nichtindizierten
Spalte und einer Konstanten zu steigern. In solchen
Fällen wird die Bedingung in den Datenknoten des
Clusters „eingekellert“, wo sie dann in
allen Partitionen gleichzeitig ausgewertet wird.
Hierdurch wird der Versand unpassender Datensätze über
das Netzwerk vermieden, und die entsprechenden Abfragen
werden um den Faktor 5 bis 10 im Vergleich zu Fällen
beschleunigt, in denen ein Bedingungs-Pushdown hätte
verwendet werden können, aber nicht verwendet wurde.
Angenommen, eine Cluster-Tabelle sei wie folgt definiert:
CREATE TABLE t1 ( a INT, b INT, KEY(a) ) ENGINE=NDBCLUSTER;
In diesem Fall kann ein Bedingungs-Pushdown bei einer Abfrage wie der folgenden verwendet werden:
SELECT a,b FROM t1 WHERE b = 10;
Die Ausgabe von EXPLAIN SELECT
sieht
dann wie folgt aus:
mysql> EXPLAIN SELECT a,b FROM t1 WHERE b = 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using where with pushed condition
Ein Bedingungs-Pushdown kann nicht mit einer der beiden folgenden Abfragen verwendet werden:
SELECT a,b FROM t1 WHERE a = 10; SELECT a,b FROM t1 WHERE b + 1 = 10;
Bezüglich der ersten dieser zwei Abfragen ist der
Bedingungs-Pushdown nicht anwendbar, weil in der Spalte
a
ein Index vorhanden ist. Im Falle
der zweiten Abfrage kann ein Bedingungs-Pushdown nicht
verwendet werden, weil der Vergleich unter
Berücksichtigung der nichtindizierten Spalte
b
ein indirekter Vergleich ist. (Er
wäre allerdings gültig, wenn Sie in der
WHERE
-Klausel b + 1 =
10
auf b = 9
reduzieren
würden.)
Allerdings kann ein Bedingungs-Pushdown auch verwendet
werden, wenn eine indizierte Spalte unter Verwendung
eines der Operatoren >
oder
<
mit einer Konstanten verglichen
wird:
mysql> EXPLAIN SELECT a,b FROM t1 WHERE a<2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: range
possible_keys: a
key: a
key_len: 5
ref: NULL
rows: 2
Extra: Using where with pushed condition
Merken Sie sich in Bezug auf einen Bedingungs-Pushdown Folgendes:
Der Bedingungs-Pushdown ist nur für MySQL Cluster relevant und tritt nicht auf, wenn Sie Abfragen unter Verwendung anderer Speicher-Engines an Tabellen absetzen.
Bedingungs-Pushdowns werden nicht standardmäßig
eingesetzt. Um sie zu aktivieren, können Sie
mysqld mit der Option
--engine-condition-pushdown
oder
die folgende Anweisung ausführen:
SET engine_condition_pushdown=On;
Sie erhalten einen geeigneten Anhaltspunkt zu der Frage, wie gut
ein Join ist, indem Sie das Produkt der Werte in der Spalte
rows
der Ausgabe von
EXPLAIN
erstellen. Hieraus ergibt sich grob,
wie viele Datensätze MySQL untersuchen muss, um die Abfrage
auszuführen. Wenn Sie die Abfragen mit der Systemvariablen
max_join_size
beschränken, wird dieses
Datensatzprodukt auch benutzt, um zu bestimmen, welche
SELECT
-Anweisungen für mehrere Tabellen
ausgeführt und welche abgebrochen werden. Siehe auch
Abschnitt 7.5.2, „Serverparameter feineinstellen“.
Das folgende Beispiel zeigt, wie ein Join mehrerer Tabellen auf
der Basis der von EXPLAIN
übermittelten
Angaben progressiv optimiert werden kann.
Angenommen, Sie hätten die folgende
SELECT
-Anweisung, die Sie mit
EXPLAIN
untersuchen wollen:
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;
Für dieses Beispiel gelten die folgenden Annahmen:
Die zu vergleichenden Spalten wurden wie folgt deklariert:
Tabelle | Spalte | Datentyp |
tt |
ActualPC |
CHAR(10) |
tt |
AssignedPC |
CHAR(10) |
tt |
ClientID |
CHAR(10) |
et |
EMPLOYID |
CHAR(15) |
do |
CUSTNMBR |
CHAR(15) |
Die Tabellen haben die nachfolgenden Indizes:
Tabelle | Index |
tt |
ActualPC |
tt |
AssignedPC |
tt |
ClientID |
et |
EMPLOYID (Primärschlüssel) |
do |
CUSTNMBR (Primärschlüssel) |
Die tt.ActualPC
-Werte sind nicht
gleichmäßig verteilt.
Anfangs – also vor der Durchführung von Optimierungen –
erzeugt die EXPLAIN
-Anweisung die folgenden
Angaben:
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, NULL NULL NULL 3872 ClientID, ActualPC range checked for each record (key map: 35)
Da type
für jede Tabelle
ALL
ist, gibt diese Ausgabe an, dass MySQL
ein kartesisches Produkt aller Tabellen erzeugt, also jede
mögliche Kombination von Datensätzen. Dies dauert recht lang,
weil das Produkt der Anzahl der Datensätze in jeder Tabelle
untersucht werden muss. Im vorliegenden Fall beträgt dieses
Produkt 74 × 2135 × 74 × 3872 = 45.268.558.720
Datensätze. Wenn die Tabellen noch größer wären, kann man
sich nur vage vorstellen, wie lange dies dauern würde.
Ein Problem besteht hier darin, dass MySQL Indizes für Spalten
effizienter verwenden kann, wenn sie mit demselben Typ und
derselben Größe deklariert wurden. In diesem Kontext werden
VARCHAR
und CHAR
als
identisch betrachtet, wenn sie mit derselben Größe deklariert
werden. tt.ActualPC
wird aber als
CHAR(10)
und et.EMPLOYID
als CHAR(15)
deklariert – die Längen
stimmen also nicht überein.
Um diese Fehlanpassung zwischen den Spaltenlängen aufzuheben,
verlängern Sie ActualPC
mit ALTER
TABLE
von 10 auf 15 Zeichen Länge:
mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
Nun sind tt.ActualPC
und
et.EMPLOYID
beide
VARCHAR(15)
. Die erneute Ausführung der
EXPLAIN
-Anweisung erzeugt folgende Ausgabe:
table type possible_keys key key_len ref rows Extra tt ALL AssignedPC, NULL NULL NULL 3872 Using ClientID, where ActualPC 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
Dies ist noch nicht perfekt, aber es ist schon wesentlich
besser: Das Produkt der rows
-Werte ist
bereits um den Faktor 74 geringer – die Ausführung dieser
Version dauert nur ein paar Sekunden.
Eine weitere Änderung kann vorgenommen werden, um die
Nichtübereinstimmung der Spaltenlängen für die Vergleiche
tt.AssignedPC = et_1.EMPLOYID
und
tt.ClientID = do.CUSTNMBR
zu beseitigen:
mysql>ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
->MODIFY ClientID VARCHAR(15);
Nach dieser Änderung sieht die Ausgabe von
EXPLAIN
so aus:
table type possible_keys key key_len ref rows Extra et ALL PRIMARY NULL NULL NULL 74 tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using ClientID, where ActualPC et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
An dieser Stelle ist die Abfrage schon fast perfekt optimiert.
Als letztes Problem verbleibt die Tatsache, dass MySQL
standardmäßig voraussetzt, dass Werte in der Spalte
tt.ActualPC
gleichmäßig verteilt sind; dies
ist aber bei der Tabelle tt
nicht der Fall.
Glücklicherweise ist es einfach, MySQL zur Analyse der
Schlüsselverteilung zu bewegen:
mysql> ANALYZE TABLE tt;
Mit den zusätzlichen Indexangaben ist der Join perfekt, und
EXPLAIN
erzeugt folgendes Ergebnis:
table type possible_keys key key_len ref rows Extra tt ALL AssignedPC NULL NULL NULL 3872 Using ClientID, where ActualPC 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 Spalte rows
in der
Ausgabe von EXPLAIN
eine begründete Annahme
des MySQL-Join-Optimierers ist. Sie sollten überprüfen, ob die
Zahlen annähernd realistisch sind, indem Sie das
rows
-Produkt mit der tatsächlichen Anzahl
der von der Abfrage zurückgegebenen Datensätze vergleichen.
Unterscheiden sich diese Werte erheblich, dann erhalten Sie
unter Umständen eine bessere Leistung, wenn Sie
STRAIGHT_JOIN
in Ihrer
SELECT
-Abfrage verwenden und versuchen, die
Tabellen in der FROM
-Klausel in einer anderen
Reihenfolge aufzulisten.
Dies ist eine Übersetzung des MySQL-Referenzhandbuchs, das sich auf dev.mysql.com befindet. Das ursprüngliche Referenzhandbuch ist auf Englisch, und diese Übersetzung ist nicht notwendigerweise so aktuell wie die englische Ausgabe. Das vorliegende deutschsprachige Handbuch behandelt MySQL bis zur Version 5.1.