MySQL Optimierung, Part II: Mit Indizes richtig umgehen… Und immer noch beschäftigt mich das Thema, wie weit man seine MySQL Datenbank optimieren kann. Mittlerweile bin ich auf den Trichter gekommen, das sinnvolle Indizes was bringen könnten.
Wie funktioniert ein Index? Da könnte man natürlich bücherweise drüber referieren - ich beschränke mich einfach auf ein banales Beispiel. Ein Index ist vergleichbar mit einer Inhaltseingabe (grob granulierter Index / Kapitel) und einem Stichwortverzeichnis am Ende eines Buches (fein granulierter Index).
- Wenn man grob weiß, was man sucht sollte man zunächst ganz hinten im Stichwortverzeichnis suchen, das ist alphabetisch und man findet schnell, was man sucht.
- Wenn noch die Kapitelnummer weiß, is das Inhaltsverzeichnis eine gute Adresse.
Genauso geht MySQL bei einem SELECT vor. Natürlich ist das wilde Suchen in einem Taschenbuch effizienter als bei einem dicken Schmöker. Mit Datenbanken ist das nicht anders - für kleine Datenbanken sind Indizes überdimensioniert (Wer benötigt schon ein Stichwortverzeichnis für einen 20 Seiten Band?). Bei großen Datenbeständen lohnt es sich durchaus, sinnvolle Indizes zu definieren.
Hier mal ein paar Beispiele aus der Praxis:
- SELECT über die Datensatz ID. Das ist quasi die Mutter aller Indizes und wird deshalb von MySQL standardmäßig eingerichtet: Der Primary Key. Der PK ist nichts anderes als ein UNIQUE Index mit der Regel, dass er nicht NULL sein darf.
- SELECT über einen Namen. Denkbar z.B. bei Benutzerverwaltungen. Bei einem Login wird der Benutzer via Name gesucht. Also am Besten einen Index über den Namen anlegen und schon flutscht die Anmeldung! (Wenn die Applikation mit Sessions arbeitet, handelt es sich vielleicht nur um einen kleinen Performance Gewinn, ganz anders sieht es aber bei einer Anmeldung mittels HTTP Auth aus (wird u.a. mittels .htaccess ausgeführt).
- SELECT über eine Link- oder Zuordnungs-Tabelle. Was ist das für eine Tabelle? Gesetzt der Fall, es gibt eine Tabelle für Benutzer, eine Tabelle für Benutzergruppen und der Benutzer kann mehreren Gruppen zugeordnet sein. Dann benötigt man eine Tabelle die folgende Felder hat: “link_id”, “group_id” und ”user_id”. Es gibt Arten auf die hinterlegten Informationen zuzugreifen - manche mögen vielleicht den PK link_id weglassen und stattdessen einen zusammengesetzten PL verwenden, der aus “group_id” und “user_id” besteht. Schneller gehts oft, wenn man auf beides nicht verzichtet: Wie wärs denn mit zwei Indizes? Standard-PK mit Auto-ID “link_id” und zusammengesetzter Index aus Gruppen- und User-ID. Für UPDATE und DELETE Statements bietet sich die eindeutige Auto-ID an. Für SELECTs zum auslesen der Gruppeninformationen bietet sich der kombinierte Index an.
Mit sinnvollen Indizes gibts bestimmt einen großen Speedup für jede größere Applikation. Und so setzt man einen Index in MySQL auf eine Spalte (Der Username muss eindeutig sein):
ALTER TABLE `users` ADD UNIQUE `LoginOptimierung` ( `user_name` )
Die erweiterte Fassung über mehrere Spalten, bei eindeutigen Zuordnungen (Ein Benutzer kann nur einmal einer Gruppe angehören):
ALTER TABLE `user_group_links` ADD UNIQUE `GruppenZuordnung` ( `user_id` , `group_id` )
Die Fassung über mehrere Spalten, bei beliebig vielen gleichbedeutenden Zuordnungen (vielleicht bei einem Rezeptbuch):
ALTER TABLE `ingredient_recipe_links` ADD INDEX `ZutatenZuordnung` ( `recipe_id` , `ingredient_id` )
Ähnliche Artikel:
