Indizes richtig einsetzen

Datenbanken: Schlüssel und Indizes

  1. Ziel und Zweck
  2. Das Wesen von Indizes
  3. Primärschlüssel
  4. Unique-Schlüssel
  5. Index-Schlüssel
  6. Fremdschlüssel
  7. Fulltext-Schlüssel
  8. Welche Indizes sollen nun gesetzt werden
  9. Für Fortgeschrittene: Die Reihenfolge der Spalten im Index

Ziel und Zweck

Schlüssel und Indizes (beide Wörter sind im Falle von Datenbanken gleichbedeutend) sind elementare Bestandteile einer Datenbank. Aus Geschwindigkeitsgründen aber auch aus Gründen der internen Verarbeitung durch das DBMS (Database Management System) ist es wichtig, bestimmte Spalten einer Tabelle mit Schlüsseln zu belegen. Schlüssel können stets auf eine oder mehrere Spalten einer Tabelle gelegt werden.
Als kleine Anmerkung möchte ich darauf hinweisen, dass die Mehrzahl von Index Indizes ist (es gibt keine Indexe).

Das Wesen von Indizes

Indizes werden intern vom DBMS als sortierte Listen gespeichert. Das bedeutet zweierlei: Das Eintragen eines neuen Datensatzes in eine Tabelle, auf der ein oder mehrere Indizes liegen, wird geringfügig mehr Zeit in Anspruch nehmen als ohne Indizes, da ja zu jedem Index der neue Wert in die sortierte Liste an die richtige Position eingetragen werden muss.
Im Umkehrschluss allerdings sind Indizes beim Selektieren von Daten ein enormer Geschwindigkeitsvorteil, denn ein Wert kann viel schneller in einer sortierten Liste gefunden werden. Im Abschnitt Index-Schlüssel steht dazu ein Beispiel, das verdeutlicht, wie die Beschleunigung funktioniert.
Die Anzahl von Indizes pro Tabelle ist unbegrenzt. Lediglich beim Primärschlüssel darf nur einer pro Tabelle vorhanden sein. Man muss sich aber immer überlegen, wann ein Index Sinn macht.

Primärschlüssel

Der Primärschlüssel ist kennzeichnend für einen bestimmten Datensatz. Das bedeutet, dass allein mit dem Primärschlüssel ein Datensatz eindeutig festgelegt ist und somit wiedergefunden werden kann. Pro Tabelle kann nur ein Primärschlüssel vergeben werden. In einer Tabelle Users mit den Spalten Username und E-Mail könnte man als Primärschlüssel den Usernamen wählen, denn ein Username sollte stets nur von einem User belegt sein (ansonsten würde es beim Login Probleme geben). Ebenso könnte man die E-Mail-Adresse als Primärschlüssel wählen, aber das ist unsicher, da durchaus mehrere Personen mit der gleichen E-Mail-Adresse unterwegs sein können (z.B. Familie Müller mit andi_laura_mueller@xyz.de) und da würde es Probleme geben, da ein Datensatz dadurch nicht mehr eindeutig identifizierbar ist (2 Datensätze (zum einen Andi, zum anderen Laura) unter einer E-Mail-Adresse).
Warum sollte man trotzdem eine Extra-Spalte ID hinzufügen und diese als Primärschlüssel wählen? Es gibt zwei Gründe: erstens sollte man in den seltensten Fällen so genannte ‘sprechende Schlüssel’ verwenden. Damit ist gemeint, wenn der Schlüssel bereits ein Nutzdatum darstellt, sollte er nicht als Primärschlüssel dienen. Und der zweite Grund ist die Gewohnheit, denn mit einer automatisch hochzählenden ID als Primärschlüssel kann man nichts falsch machen.
Es ist festzuhalten, dass es oft mehrere Schlüsselkandidaten gibt. Meist sollte man eine ID als Primärschlüssel wählen. Das reduziert die Arbeit für die Datenbank, weil von einem Computer mit einer Zahl grundsätzlich besser umgegangen werden kann als mit einer Zeichenkette. Ausnahmen, wo man überlegen kann, ob man einen sprechenden Primärschlüssel verwendet, sind Daten, die bereits als Zahl vorliegen. Bei der ISBN eines Buchtitels ist dies möglich, noch besser sogar bei der Fahrgestellnummer eines Autos. Aber auch in diesen Fällen schadet es keineswegs die ID als Primärschlüssel zu verwenden.
Grundsätzlich ist nur ein Primärschlüssel pro Entität bzw. Tabelle möglich.

Ein Primärschlüssel kann auch aus mehreren Spalten zusammengesetzt sein. Hat man eine Tabelle Automodell wäre der Modellname allein eventuell ein Schlüsselkandidat. Wir gehen dabei davon aus, dass kein Hersteller B den Modellnamen von Hersteller A ‘klaut’ (wenn das nicht sogar markenrechtlich verboten ist). Wenn man aber einen Autohändler fragen würde, käme der sofort mit einem Beispiel, wo der Modellname als Primärschlüssel nicht funktioniert: Manche Hersteller entdecken nämlich alte Modellnamen wieder. Mir ist das beispielsweise bei Skoda bekannt. In den 1950er Jahren gab es bereits einen Skoda Octavia. Und dieses Modell gibt es auch heute noch oder besser wieder, allerdings mit ganz anderen inneren und äußeren Werten. Stellt man beide Autos nebeneinander, kann man schnell feststellen, dass es sich nicht um das gleiche Modell handelt. Die Datenbank aber, die nur den Modellnamen kennt, würde beide Modelle für gleich halten.
Wenn man nun als Primärschlüssel den Modellnamen und das Baujahr verknüpft, funktioniert das wieder. Dann erkennt auch die Datenbank, dass es zwei verschiedene Modelle sind. Sicherer (und in diesem Fall auch wieder performanter) ist es aber, eine automatisch hochzählende ID als Primärschlüssel zu vergeben.

Unique-Schlüssel

Unique-Schlüssel verhalten sich ähnlich wie Primärschlüssel. Sie dienen dazu, dass der Wert eines Datensatzes in der Spalte, die mit dem Unique-Schlüssel belegt ist, bei keinem anderen Datensatz vorkommen darf. In meinem Beispiel mit der Tabelle ‘Users’ hätte ich beispielsweise auf den Usernamen einen Unique-Index gesetzt (wenn man eine ID als Primärschlüssel wählt), weil ein Username nur ein mal vorkommen darf (Stichwort Login).
Ansonsten kann auch ein Unique-Index mehrere Spalten vereinen. Das ist sogar manchmal sehr sinnvoll. Als Beispiel sei nur der Umstand aufgeführt, wenn es in einer Tabelle ‘Users’, die neben den Spalten Username und E-Mail noch das Geburtsdatum enthält, ein Unique-Index auf E-Mail und Geburtsdatum gesetzt wird. Wenn wir davon ausgehen, dass User immer ihr wirkliches Geburtsdatum angeben, ist es somit gestattet, dass sich Personen, die eine gemeinsame E-Mail-Adresse haben, auch beide anmelden können (es sei denn sie haben am gleichen Tag Geburtstag).
Die Datenbank verhält sich so, dass sie den Versuch einen Wert in ein Feld einzutragen, der in der Tabelle schon ein Mal vorhanden ist und auf dessen Spalte ein Unique-Index liegt, einfach abwehrt. Wenn das Eintragen in PHP mittels mysql_query() versucht wird sollte man den Rückgabewert dieser Funktion auswerten (1 bzw. true bei Erfolg, 0 bzw. false bei Misserfolg) und den User darüber informieren, weshalb das Eintragen keinen Erfolg hatte, sonst weiß der nämlich nicht Bescheid und wird entnervt aufgeben.

Index-Schlüssel

Index-Schlüssel existieren alleinig zur Beschleunigung von Datenbankabfragen. Wenn sich jemand über eine langsame Datenbank ärgert, ist meistens kein Index gesetzt, der die Spalten in der WHERE-Bedingung abdeckt. Wie weiter oben erklärt speichert ein Index die Spalten-Inhalte, die zum Index gehören, mit einer Vorsortierung. Dadurch können Bedingungen und Sortierungen leichter ausgeführt werden. Am Beispiel lässt sich das leicht verdeutlichen: Angenommen die Namen im Telefonbuch wären nicht nach Städten und alphabetisch geordnet, sondern man bekäme ein Telefonbuch von ganz Deutschland und alle Namen stehen zufällig an irgendeiner Position. Wie schnell schafft man es den eigenen Namen zu finden? Und diese Zeit vergleichen wir, wenn wir ein nach Orten und in den Orten noch nach Namen sortiertes Telefonbuch bekommen. Zweitere Methode geht wohl wesentlich schneller. Und wer denkt die Zeitdifferenzen seien in diesem Beispiel extrem, der irrt. Man muss nur mal versuchen aus einer Artikeldatenbank mit 500000 Artikeln sich alle auflisten lassen mit dem Anfangsbuchstaben A. Und danach legt man einen Index auf den Artikelnamen und wiederholt das Experiment. Eine Größenordnung von 50-200-facher Beschleunigung ist keine Seltenheit.

Fremd-Schlüssel

Obwohl Sie standardmäßig von MySQL, dem führenden Open Source-DBMS, nicht unterstützt werden (nur mit dem Tabellentyp InnoDB), sollen Fremdschlüssel kurz erklärt werden.
Wenn zwei Tabellen über eine Relation verknüpft werden, geschieht dies über das Eintragen des Primärschlüssels der nicht eindeutigen Tabelle in die Partner-Tabelle (1:n, bei 1:1 genauso, da ist es egal, auf welche Seite der Primärschlüssel geschrieben wird) oder durch das Erstellen einer Zwischentabelle bei einer n:m-Beziehung.
Diese Spalten, die allein zum Zuordnen geeignet sind und nichts mit den eigentlichen Daten in der Tabelle zu tun haben, werden in einem Fremdschlüssel gespeichert. Hat man beispielsweise eine Artikeldatenbank (ID, Name, Preis) und dazugehörige Kategorien (ID, Name), fügt man der Artikel-Tabelle eine Spalte Kategorien_ID hinzu und belegt diese Spalte mit einem Fremdschlüssel, der anzeigt, auf welche Tabelle bzw. genauer auf welchen Datensatz sich die ‘Relationsspalte’ Kategorien_ID bezieht.
Soll nun eine Kategorie gelöscht werden, der 100 Artikel zugeordnet sind, bemerkt das DBMS diese Verknüpfung. Man hat dann die Wahl zuerst alle 100 Artikel einer anderen Kategorie zuzuordnen, die Kategorie doch beizubehalten oder die Kategorie trotzdem zu löschen (dann verweist die Kategorien_ID dieser 100 Artikel allerdings ins Nichts). Fremdschlüssel sind eine schöne Funktion, um Abhängigkeiten zweier Tabellen untereinander umzusetzen.
Wie gesagt, gibt es in MySQL keine Fremd-Schlüssel. Als Empfehlung würde ich aber zumindest einen Index-Schlüssel auf die Spalte legen, die aus der Beziehung entsteht, denn oft werden viele Abfragen über das Fremdschlüssel-Attribut bedingt.

Fulltext-Schlüssel

Fulltext-Schlüssel bilden eine Ausnahme vom besagten Wesen der Schlüssel. Diese Schlüssel können nur auf Felder mit den Datentypen TEXT oder BLOB angewendet werden.
Einen Fulltext-Index benötigt man nur, wenn man mittels MATCH eine ‘natürliche Ähnlichkeit’ zweier Beiträge feststellen möchte. Wie das funktioniert soll hier vorerst nicht näher beleuchtet werden, aber so werden die häufigen ‘Ähnlichen Artikel’ in Online-Shops und Ähnlichem dynamisch gefunden. Man übergibt der Match-Funktion einen String und sie findet in den angegebenen Spalten ähnlich lautende Artikel. Und damit das funktioniert, muss auf allen im Match-Befehl angegebenen Spalten ein FULLTEXT-Index liegen (pro MATCH-Befehl ein FULLTEXT-Index, das heißt, wenn in dem MATCH in 2 Spalten gesucht werden soll, muss 1 Fulltext-Index mit 2 Spalten angelegt werden).
Als SQL-Code sieht ein MATCH-Befehl dann so aus:

SELECT ID FROM artikel WHERE MATCH (titel, beschreibung) AGAINST ('ein grüner Wollpullover, der so kuschelig weich und warm ist')

Und ein solcher Befehl würde – vorausgesetzt man hat solche Artikel in der Artikeltabelle – dann auch ID von Artikeln mit den Titeln ‘Blauer Wollpullover’, ‘Plüschtier-Teddy’ (wenn in der Spalte beschreibung irgendwie etwas mit “kuschelig” steht) usw.

Welche Indizes sollen nun gesetzt werden

Die Pflicht

Der Primärschlüssel sollte immer als erstes besetzt werden. Regeln dazu findet man im Kapitel über Primärschlüssel.
Unique-Schlüssel ergeben sich ebenfalls aus den Anforderungen. Wenn eine bestimmte E-Mail-Adresse beispielsweise in einer Newsletterdatenbank nur ein Mal enthalten sein soll, wird ein Unique-Index auf diese Tabelle gelegt.
Wenn man ein DBMS verwendet, das Fremdschlüssel unterstützt, können diese bereits anhand des ERM abgelesen werden.
Für eine ‘Ähnliche Artikel’-Funktion wird immer ein Fulltext-Index benötigt, sonst gibt das DBMS eine Fehlermeldung aus. Wenn eine solche Funktion also geplant ist bzw. implementiert werden soll, ist dieser Index durch die beteiligten Spalten der MATCH-Funktion ebenfalls bereits vorgegeben.

Die Kür

Bei den Index-Schlüsseln muss man etwas nachdenken, denn es bringt wenig, auf jede Spalte einfach einen Index zu legen, egal ob er gebraucht wird oder nicht. Ich habe die Erfahrung gemacht, dass es für ein Webprojekt durchaus Sinn macht, für jede Funktion, die häufig ausgeführt wird, einen Index anzulegen. Ein Beispiel: Auf einer Communityseite loggen sich täglich viele User ein. Wenn wir als Abfrage im Script folgendes haben:

SELECT ID FROM users WHERE username='".$user."' AND passwort='".$pass."'

würde ich einen Index auf username und passwort legen (einen Index für beide Spalten zusammen). In einer Tabelle Personen einen Index auf die Hausnummer zu legen, macht dagegen keinen Sinn und verlangsamt das Eintragen neuer und das Updaten vorhandener Datensätze nur unnötig.
Wichtig dabei: Pro SELECT-Anweisung kann das DBMS nur einen Index auswählen. Deshalb ist ein verbundener Index im eben genannten Login-Beispiel performanter als jeweils einen Index auf username und passwort zu legen.
Für eine weitere Optimierung lege ich noch den Abschnitt zur Bedeutung der Reihenfolge der Spalten in einem Index ans Herz.

Für Fortgeschrittene: Die Reihenfolge der Spalten im Index

Wer das letzte Quäntchen aus seiner MySQL-Datenbank holen möchte, sollte sich die Reihenfolge der Spalten in den einzelnen Indizes ansehen. Die Indizes erzeugen vorsortierte Listen, das wurde bereits gesagt. Das DBMS kann den Index dann allerdings auch nur in dieser Reihenfolge abarbeiten. Am Beispiel aller Telefonnummern Deutschlands erzeugt die Sortierung nach Städten und anschließend (bei gleicher Stadt) nach Namen eine andere Liste als die Sortierung nach Namen und (bei gleichem Namen) nach der Stadt.
Daraus und aus dem Umstand, dass nur ein Index pro Abfrage genutzt werden kann, folgt, dass die Reihenfolge der Spalten im Index der der Abfrage entsprechen sollte.
Als Beispiel: Unsere Datensätze:

Name Stadt Telefonnummer
Müller Ulm 12345
Meier München 54321
Müller München 56789
Schmidt Leipzig 98765

Wir legen einen Index Name_Stadt (geben zuerst Name, dann Stadt an) an, der folgende Liste erzeugt:
(Meier,München); (Müller,München); (Müller,Ulm); (Schmidt,Leipzig)
Zum Vergleich legen wir noch einen Index Stadt_Name an. Dieser erzeugt folgende Liste: (Leipzig,Schmidt); (München,Meier); (München,Müller); (Ulm,Müller)
Im Script steht nun folgende Abfrage:

SELECT Name,Stadt FROM personen WHERE Name='Müller' AND Stadt='München'

Bei dieser Minidatenbank mag das Ergebnis nicht wirklich unterschiedlich sein, aber bei größeren Datenmengen ist der Unterschied eindeutig vorhanden. Der Index Name_Stadt würde demzufolge schnellere Ergebnisse erzielen, da zuerst eine Untermenge aller Datensätze gesucht wird, in denen der Name='Müller' ist. Der Index Stadt_Name kann hierfür nur bei der Stadt genutzt werden (das Finden aller Datensätze mit dem Namen Müller muss ohne Index erfolgen). Die Performance mit dem Index Stadt_Name ist dadurch etwas besser als ganz ohne Index, aber es ginge schneller.
Mit dem Index Name_Stadt kann dann auch sehr flott der Eintrag der Müllers aus München gefunden werden. Die Reihenfolge spielt also keine unbedeutende Rolle!
Andererseits können somit auch Indizes eingespart werden. Wenn man beispielsweise einfach alle Personen selektieren möchte, die den Namen Müller tragen:

SELECT Stadt FROM Personen WHERE Name='Müller'

In diesem Fall kann der Index Name_Stadt ebenfalls genutzt werden. Es muss also kein Extra-Index auf die Spalte Name gelegt werden.

Und natürlich wird der Index nicht nur bei der WHERE-Bedingung eingesetzt sondern auch beim Sortieren. Da das WHERE immer vor dem ORDER BY ausgeführt werden muss, würde also auch folgende Abfrage beschleunigt:

SELECT Telefonnummer FROM Personen WHERE Name='Müller' ORDER BY Stadt

Dagegen wäre der Index Name_Stadt für folgende Abfrage recht langsam sein:

SELECT Telefonnummer FROM Personen WHERE Stadt='München' ORDER BY Name

Er käme lediglich beim Sortieren zum Einsatz. Das Filtern würde recht lange dauern, falls kein anderer Index festgelegt wurde.

Jan hat 152 Beiträge geschrieben

13 Kommentare zu “Indizes richtig einsetzen

  1. Ingo sagt:

    Klasse Artikel. Habe schon viel darüber gelesen, aber das ist jetzt der erste bei dem ich meine den Sinn von Indizes über mehrere Spalten verstanden zu haben.
    Danke 🙂

  2. index und schlüssel sind *nicht* dasselbe und sollten nicht synonym verwendet werden.

    ein index dient zur beschleunigung der suche, er muss nicht eindeutig sein. (hier index-schlüssel genannt)

    ein schlüssel dient dazu einen datensatz eindeutig zu identifizieren, er muss also eindeutig sein. (hier unique-schlüssel)

    der primärschlüssel ist in sql beides, man kann aber zusätzlich noch indices vergeben oder unique-spalten definieren

  3. Michael sagt:

    Guter Artikel, danke.

    Bist Du Dir sicher, dass die Aussage
    “Pro SELECT-Anweisung kann das DBMS nur einen Index auswählen”
    korrekt ist? Es gibt Datenbanken, die das können. Ist das bei MySQL so? Wo hast Du denn die Information gefunden?

    Gruß,
    Michael

  4. Michael sagt:

    Ich habe noch ein wenig recherchiert und bin selbst auf die Antwort gestoßen.:

    Ein Auszug aus dem englischsprachigen Manual von MySQL 5.1 zum Thema ‘Index Merge Optimization’, d.h. des Schnitts der Ergebnisse von zwei Indizes:

    ————–
    Note:
    If you have upgraded from a previous version of MySQL, you should be aware that this type of join optimization [d.h. die Verwendung mehrerer Indizes, Anm. d. Verfassers] is first introduced in MySQL 5.0, and represents a significant change in behavior with regard to indexes. (Formerly, MySQL was able to use at most only one index for each referenced table.)
    —————
    D.h. es kann sein, dass mehr als ein Index pro Abfrage benutzt wird. Wohl gemerkt steht auch dran, dass vor 5.0 nur ein Index pro Tabelle verwendet werden konnte – nicht nur ein Index pro Abfrage.

    Das wollte ich nur richtigstellen. Ansonsten enthält Dein Artikel viele richtige und nützliche Informationen. Danke nochmal!

  5. Christian sagt:

    Hey Jan,

    so weit, so gut! Habe es glaube ich endlich mal prinzipiell verstanden! Aber warum jetzt der Index Name_Stadt schneller sein soll als der Stadt_Name erschließt sich mir nicht ganz?!

    Grüße
    Christian

Eine Antwort schreiben

Ihre E-Mail-Adresse wird nicht veröffentlicht. Benötigte Felder sind markiert mit *

You may use these HTML tags and attributes: <a href=""> <blockquote cite=""> <pre lang=""> <b> <strong> <i> <em>