Optimierung von MySQL: Verwendung des Index

Ein Index in MySQL ist eine spezielle Datenstruktur, die das Auffinden von Datensätzen beschleunigt. Ohne Index muss MySQL bei einem WHERE-Statement alle Einträge der Tabelle überprüfen, um die gesuchten Datensätze zu finden. Bei entsprechen großen Tabellen kann solch eine Abfrage dann mehrere Sekunden oder Minuten dauern. Durch einen Index werden alle Einträge einer Spalte indiziert, d.h. es wird neben der Tabelle zusätzlich eine Datenstruktur erstellt, der Index, der für das schnelle Auffinden von Datensätzen verantwortlich ist. Indizes sind wichtig bei großen Tabellen mit mehr als 10.000 Einträge und sofern die Spalte häufig in WHERE oder ORDER BY-Statements verwendet wird. Wird an die Tabelle nun ein Query mit einem WHERE-Statement gesendet und die Spalte ist als Index definiert, so wird zum Auffinden der Einträge auf diese effiziente Datenstruktur zurückgegriffen und das wird Ergebnis wird erheblich schneller zurück geliefert.

MySQL bietet dabei die folgenden Optionen für Indizes an:

• Primary Key: Je Tabelle ist nur ein Primary Key möglich, dies ist typischerweise die ID-Spalte. Jeder Eintrag in dieser Spalte muss eindeutig sein, eine Doppelung ist ausgeschlossen. Der Primary Key unterscheidet sich nicht von Unique, nur dass es maximal einen Primary Key pro Tabelle geben darf.
• Unique: Spalten mit einem Index des Typs Unique können nur eindeutige Werte enthalten. Dass zwei Datensätze den selben Wert in der Spalte enthalten ist ausgeschlossen.
• Index: Spalten die mittels Index gekennzeichnet werden können im Gegensatz zu Unique und Primary Key auch doppelte Werte in der Spalte enthalten.
• Fulltext: Der Typ Fulltext ist geeignet für Spalten die längere Texte enthalten. Jedes Wort wird dabei indiziert und eine Suche nach einzelnen Wörtern oder Teilwörtern ist möglich. Diesen Typ zu wählen macht nur Sinn, wenn mit der Volltextsuche von MySQL gearbeitet wird.

Indizes mittels phpMyAdmin anlegen

Mittels phpMyAdmin können Indizes über zwei Wege angelegt werden. Die erste Variante ist bei dem erstellen neuer Tabellen. Dort könnt in der Spalte Index den Typ des Index wählen.

Die zweite Variante ist einen Index nachträglich zur existenten Tabelle hinzuzufügen. Wechselt bei der Tabelle in die Struktur-Ansicht und unterhalb der Spalten verbirgt sich ein Link Indizes. Wenn ihr diesen Link öffnet, öffnet sich die folgende Ansicht. Diese zeigt euch zum Einen die existenten Indizes an, zum Anderen könnt ihr dort neue Indizes erstellen.

Zuerst werdet ihr gefragt, über wie viele Spalten sich der Index erstrecken soll. Siehe dazu den Abschnitt Indizes über mehrere Spalten.

Danach öffnet sich diese Ansicht:

Dort werdet ihr nach dem Namen des Index gefragt. Dieser ist eigentlich immer irrelevant und ihr könnt ihn leer lassen. Danach wählt ihr den Typ und die Spalte für den Index aus. Bei der Größe könnt ihr angeben, wie viele Zeichen der Spalte indiziert werden sollen. Erstellt ihr ein Index für eine Text-Spalte, so kann es sinnvoll sein nur die ersten paar Zeichen in den Index aufzunehmen. Aber meistens lasse ich diesen Wert leer, dann wird der Index über die gesamte Spalte erstellt.

Grundlagen: Was ist ein Index?

Ein prinzipielle Herausforderungen bei Datenbank ist das schnelle Auffinden von Datensätzen. Angenommen ihr habt eine große User-Tabelle mit 100.000 Einträgen. Sucht ihr in dieser Tabelle nach einer E-Mail-Adresse, so muss die Datenbank im schlimmsten Fall alle 100.000 Einträge durchgehen und die abgespeicherten E-Mail-Adresse mit der gesuchten E-Mail-Adresse vergleichen. Wird oft nach einer E-Mail-Adresse gesucht, z.B. weil sich Benutzer mit ihrer E-Mail-Adresse einloggen, kann dies die Performance des Datenbankserver erheblich beeinträchtigen. Und je größere eure Tabelle sind und je öfter solche WHERE-Statements an die Datenbank gesendet wird, desto stärker geht dies zu lasten der Performance.

Die Lösung für dieses Problem sind sogenannte Indizes. Habt ihr eine Spalte als Index definiert, z.B. als Unique oder Index, so speichert MySQL die Werte dieser Spalte in einer zusätzlichen Datenstruktur ab. Diese Datenstruktur erlaubt es, effizient nach dem gewünschten Wert zu suchen. Ein Index auf die E-Mail-Adresse eurer User-Tabelle würde also alle 100.000 E-Mail-Adresse in einer zusätzlichen Datenstruktur abspeichern. In dieser Datenstruktur ist dann ebenfalls vermerkt, wo auf der Festplatte der Datensatz des Benutzers gespeichert ist.

Taucht nun die E-Mail-Adresse in einem WHERE-Statement auf, so werden nicht alle 100.000 Einträge durchsucht, sondern im Index wird die E-Mail-Adresse nachgeschlagen und der gewünschte Benutzer wird zurückgegeben. Der Aufwand zum Nachschlagen im Index ist dabei O(log n), d.h. statt 100.000 Einträge zum Finden der E-Mail-Adresse braucht MySQL nun nur noch ca. log(100.000) = 17 Vergleiche.

Der Nachteil von Indizes ist, dass eine zusätzliche Datenstruktur in der Datenbank existiert und gepflegt werden muss. Dies kosten zum einen zusätzlichen Speicherplatz, zusätzlich werden INSERT, UPDATE und DELETE-Statements dadurch verlangsamt, da diese ggf. eine Aktualisierung des Index erfordern.

Wann sollte ich ein Index verwenden und für welche Spalten?

Der Vorteil von Indizes ist das schnelle Auffinden von Datensätzen sofern nach der Spalte gesucht wird. Der Nachteil ist, dass zusätzliche Speicherplatz benötigt wird und INSERT, UPDATE und DELETE-Statements ggf. länger benötigen.

Sofern eure Tabelle eine ausreichend Größe besitzt, also mehr als 1.000 oder 10.000 Datensätze, so ist es sinnvoll einen Index zu definieren für alle Spalten, die regelmäßig in WHERE oder ORDER BY-Statements verwendet werden. Der zusätzliche benötigte Speicherplatz ist tendenziell zu vernachlässigen. Besitzt eure Anwendung eine typische User-Tabelle, so wird wahrscheinlich die ID als Primary Key definiert sein. Für die E-Mail-Adresse bietet sich ein Unique-Index an, um doppelte E-Mail-Adressen auszuschließen und das schnelle Auffinden von Benutzer mittels ihrer E-Mail-Adresse zu ermöglichen. Ebenfalls kann es sinnvoll sein, für den Vor- und Nachnamen einen Index vom Typ Index anzulegen, sofern nach dem Vor- oder Nachnamen im System gesucht werden kann.

Welchen Index-Typ sollte ich nutzen?

Der Primary Key wird für die primäre Indexierung einer Tabelle genutzt und typischerweise definiert man die ID als den Primary Key. Es empfiehlt sich stets für jede Tabelle eine ID anzulegen.

Unique sollte verwendet werden, sofern keine doppelten Einträge erlaubt sein dürfen. Dies kann nützlich sein, wenn z.B. eine E-Mail-Adresse nur einem Benutzer zugeordnet werden kann.

Index solltet ihr verwenden, wenn doppelte Einträge möglich sind.

Fulltext ist der aufwendigste Index, da hier jedes Wort eures Textes indiziert wird.  Diesen Index-Typ solltet ihr nur nutzen, wenn ihr die MySQL Volltextsuche verwendet. Siehe dazu MySQL Fulltext Search.

Indizes über mehrere Spalten

Mittels phpMyAdmin könnt ihr Indizes auch über mehrere Spalten anlegen. Die Thematik ob man lieber einen Index über mehrere Spalten oder mehrere Indizes über eine Spalte anlegt ist leider recht komplex und als Daumenregel würde bevorzuge ich eher mehrere Indizes über je eine Spalte zu erstellen.

Bei einem mehrspaltigen Index verbindet MySQL die Spalten und speichert diese in einem Index. Dies kann Vorteilhaft sein, wenn man stets nach allen Spalten sucht, so wird die Performance der Abfrage beschleunigt. Habt ihr beispielsweise einen Vor- und Nachnamen in der Benutzertabelle, so wäre ein gemeinsamer Index für diese beiden Spalten vorteilhaft, wenn stets nach Vor- und Nachname gesucht wird. Wird nur nach einem der beiden Spalten gesucht, so wäre ein gemeinsamer Index nicht nützlich. In diesem Fall ist je ein Index auf den Vornamen und den Nachnamen besser.

Mehr Informationen zu diesem komplexen Thema gibt es im MySQL Handbuch Multiple-Column Indices.