Indizes werden verwendet, um schnell Datensätze mit bestimmten Spaltenwerten zu finden. Ohne Index müsste MySQL mit der Suche beim ersten Datensatz beginnen und dann die gesamte Tabelle nach passenden Datensätzen durchforsten. Je größer die Tabelle, desto höher sind die Kosten dafür. Wenn die Tabelle einen Index für die fraglichen Spalten aufweist, kann MySQL die Position in der Mitte der Datendatei, an der die Suche beginnen soll, schnell bestimmen, ohne alle Daten in der Datei überprüfen zu müssen. Wenn eine Tabelle 1.000 Datensätze hat, ist dies mindestens hundertmal schneller als ein sequenzielles Lesen. Wenn Sie hingegen auf die meisten Datensätze zugreifen müssen, ist das sequenzielle Lesen schneller, weil die Anzahl der Festplattenzugriffe geringer ist.
Die meisten MySQL-Indizes (PRIMARY KEY
,
UNIQUE
, INDEX
und
FULLTEXT
) sind in B-Trees gespeichert. Zu
erwähnende Ausnahmen sind, dass Indizes für raumbezogene
Datentypen R-Typen verwenden und
MEMORY
-Tabellen auch Hash-Indizes
unterstützen.
Strings werden automatisch präfixkomprimiert; Gleiches gilt
für Leerzeichen am Ende des Strings. Siehe auch
Abschnitt 13.1.4, „CREATE INDEX
“.
Generell werden Indizes wie nachfolgend beschrieben verwendet.
Die spezifischen Eigenschaften von Hash-Indizes, die in
Verbindung mit MEMORY
-Tabellen verwendet
werden, werden am Ende dieses Abschnitts erläutert.
MySQL verwendet Indizes für die folgenden Operationen:
Zum schnellen Auffinden von Datensätzen, die der
WHERE
-Klausel entsprechen.
Zum Ignorieren bestimmter Datensätze. Wenn mehrere Indizes vorhanden sind, verwendet MySQL normalerweise denjenigen, der die kleinste Anzahl von Datensätzen findet.
Zum Abrufen von Datensätzen aus anderen Tabellen bei der Durchführung von Joins.
Zum Auffinden des MIN()
- oder
MAX()
-Werts für eine bestimmte
indizierte Spalte key_col
. Dies
wird mit einem Vorprozessor optimiert, der überprüft, ob
Sie WHERE
für alle
Schlüsselteile verwenden, die im Index vor
key_part_N
=
constant
key_col
erscheinen. In diesem
Fall führt MySQL eine einfache Schlüsselsuche nach jedem
MIN()
- oder
MAX()
-Ausdruck durch und ersetzt ihn
durch eine Konstante. Wenn alle Ausdrücke durch Konstanten
ersetzt werden konnten, wird die Abfrage umgehend
abgeschlossen. Zum Beispiel:
SELECT MIN(key_part2
),MAX(key_part2
) FROMtbl_name
WHEREkey_part1
=10;
Zum Sortieren oder Gruppieren einer Tabelle, sofern das
Sortieren bzw. Gruppieren auf der Basis des linken Präfixes
eines verwendbaren Schlüssels erfolgt (z. B.
ORDER BY
). Wenn auf
alle Schlüsselteile key_part1
,
key_part2
DESC
folgt, wird der
Schlüssel in umgekehrter Reihenfolge gelesen. Siehe auch
Abschnitt 7.2.12, „ORDER BY
-Optimierung“.
In manchen Fällen kann eine Abfrage so optimiert werden, dass sie Werte abruft, ohne die eigentlichen Datensätze zu untersuchen. Wenn eine Abfrage nur Spalten einer Tabelle verwendet, die numerisch sind und bei einem Schlüssel ein linkes Präfix bilden, dann können die ausgewählten Werte auch aus dem Indexbaum abgerufen werden, was deutlich schneller ist:
SELECTkey_part3
FROMtbl_name
WHEREkey_part1
=1
Nehmen wir an, dass Sie folgende
SELECT
-Anweisung absetzen:
mysql> SELECT * FROM tbl_name
WHERE col1=val1
AND col2=val2
;
Wenn für col1
und col2
ein mehrspaltiger Index vorhanden ist, können die
entsprechenden Datensätze direkt abgerufen werden. Sind
separate einspaltige Indizes für col1
und
col2
vorhanden, dann versucht der Optimierer,
den restriktivsten Index zu ermitteln, indem er überprüft,
welcher Index weniger Spalten findet; dieser Index wird dann zum
Abrufen von Datensätzen benutzt.
Wenn die Tabelle einen mehrspaltigen Index aufweist, kann ein
beliebiges linkes Präfix des Indexes vom Optimierer zum Suchen
von Datensätzen verwendet werden. Haben Sie beispielsweise
einen dreispaltigen Index für (col1, col2,
col3)
, dann bietet dieser Suchfunktionalität für
(col1)
, (col1, col2)
und
(col1, col2, col3)
.
MySQL kann keinen Teilindex verwenden, wenn die Spalten kein
linkes Präfix für den Index bilden. Betrachten Sie einmal die
folgenden SELECT
-Anweisungen:
SELECT * FROMtbl_name
WHERE col1=val1
; SELECT * FROMtbl_name
WHERE col1=val1
AND col2=val2
; SELECT * FROMtbl_name
WHERE col2=val2
; SELECT * FROMtbl_name
WHERE col2=val2
AND col3=val3
;
Wenn ein Index für (col1, col2, col3)
vorhanden ist, verwenden diese nur die ersten beiden Abfragen.
Die dritte und die vierte Abfrage beziehen sich zwar auf
indizierte Spalten, aber (col2)
und
(col2, col3)
sind keine linken Präfixe von
(col1, col2, col3)
.
Ein B-Tree-Index kann für Spaltenvergleiche in Ausdrücken
benutzt werden, die die Operatoren =
,
>
, >=
,
<
, <=
oder
BETWEEN
verwenden. Der Index kann auch für
LIKE
-Vergleiche benutzt werden, wenn das
Argument zu LIKE
ein Konstanten-String ist,
der nicht mit einem Jokerzeichen beginnt. Die folgenden
SELECT
-Anweisungen verwenden beispielsweise
Indizes:
SELECT * FROMtbl_name
WHEREkey_col
LIKE 'Patrick%'; SELECT * FROMtbl_name
WHEREkey_col
LIKE 'Pat%_ck%';
In der ersten Anweisung werden nur Datensätze mit
'Patrick' <=
berücksichtigt. In der zweiten Anweisung
werden nur Datensätze mit key_col
<
'Patricl''Pat' <=
berücksichtigt.
key_col
< 'Pau'
Die folgenden SELECT
-Anweisungen verwenden
keine Indizes:
SELECT * FROMtbl_name
WHEREkey_col
LIKE '%Patrick%'; SELECT * FROMtbl_name
WHEREkey_col
LIKEother_col
;
In der ersten Anweisung beginnt der Wert LIKE
mit einem Jokerzeichen. In der zweiten Anweisung ist der
LIKE
-Wert keine Konstante.
Wenn Sie … LIKE
'%
verwenden und
string
%'string
mehr als drei Zeichen umfasst,
verwendet MySQL den
Turbo-Boyer-Moore-Algorithmus zur
Initialisierung des Musters für den String; mit diesem Muster
wird die Suche dann schneller durchgeführt.
Eine Suche unter Verwendung von
setzt Indizes ein, wenn col_name
IS NULLcol_name
indiziert ist.
Ein Index, der nicht alle AND
-Ebenen in der
WHERE
-Klausel einbezieht, wird nicht zur
Optimierung der Abfrage benutzt. Um also einen Index verwenden
zu können, muss in jeder AND
-Gruppe ein
Präfix des Indexes benutzt werden.
Die folgenden WHERE
-Klauseln verwenden
Indizes:
... WHEREindex_part1
=1 ANDindex_part2
=2 ANDother_column
=3 /*index
= 1 ORindex
= 2 */ ... WHEREindex
=1 OR A=10 ANDindex
=2 /* optimized like "index_part1
='hello'" */ ... WHEREindex_part1
='hello' ANDindex_part3
=5 /* Can use index onindex1
but not onindex2
orindex3
*/ ... WHEREindex1
=1 ANDindex2
=2 ORindex1
=3 ANDindex3
=3;
Die folgenden WHERE
-Klauseln verwenden
Indizes nicht:
/*index_part1
is not used */ ... WHEREindex_part2
=1 ANDindex_part3
=2 /* Index is not used in both parts of the WHERE clause */ ... WHEREindex
=1 OR A=10 /* No index spans all rows */ ... WHEREindex_part1
=1 ORindex_part2
=10
Manchmal verwendet MySQL auch dann keinen Index, wenn ein
solcher vorhanden ist. Ein Umstand, unter dem dies geschieht,
liegt vor, wenn der Optimierer der Ansicht ist, dass MySQL bei
Verwendung eines Indexes auf einen sehr großen Anteil der
Datensätze in der Tabelle zugreifen muss. (In diesem Fall ist
ein Tabellenscan mit hoher Wahrscheinlichkeit schneller, weil
weniger Suchvorgänge erforderlich sind.) Wenn eine solche
Abfrage jedoch dank einer LIMIT
-Klausel nur
ein paar Datensätze abruft, verwendet MySQL trotzdem einen
Index, da sich die Datensätze, die zum Ergebnis gehören, auf
diese Weise schneller finden lassen.
Hash-Indizes weisen etwas andere Eigenschaften auf als die soeben beschriebenen:
Sie werden nur zu Vergleichen verwendet, die die Operatoren
=
oder <=>
benutzen (aber sie sind extrem
schnell). Nicht verwendet werden sie für Vergleiche mit
<
, bei denen ein Wertebereich gefunden
wird.
Der Optimierer kann einen Hash-Index nicht zur
Beschleunigung von ORDER BY
-Operationen
verwenden. (Dieser Indextyp kann nicht zur Suche nach dem
nächsten Eintrag in der Reihenfolge benutzt werden.)
MySQL kann nicht näherungsweise bestimmen, wie viele
Datensätze zwischen zwei Werten vorhanden sind (diese
Angabe verwendet der Bereichsoptimierer für die
Entscheidung, welchen Index er verwenden soll). Dies kann
sich auf einige Abfragen auswirken, wenn Sie eine
MyISAM
-Tabelle in eine
MEMORY
-Tabelle mit einem Hash-Index
konvertieren.
Nur vollständige Schlüssel können zur Suche nach einem Datensatz benutzt werden. (Bei einem B-Tree-Index kann dagegen ein beliebiges linkes Präfix des Schlüssels zum Suchen von Datensätzen eingesetzt werden.)
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.