Freitag, 1. Juli 2011

Richtig indizieren - ein paar Grundregeln

Indexe sind wichtig um Datenbankabfragen zu beschleunigen - so weit, so klar. Mit den richtigen Indexen werden Benutzerabfragen blitzschnell beantwortet. Datenbankentwickler und Datenbank-Administrator können sich dann so lässig zurücklehnen wie diese Dame.
Feger (c) F. Vahlbruch
Welches sind die richtigen Indexe für meine Datenbank?

Nun liefert Microsoft mit dem SQL Server keine harten, vorgegebenen Regeln für die Indexerstellung. Das ist alleine deswegen schon nicht möglich, weil es von den Benutzerabfragen abhängt, was gerade der "richtige" Index ist. Aber es gibt einige Grundregeln, deren Verständnis Ihnen das Arbeiten mit Indexen erleichtern wird.
Bevor ich diese Regeln aufliste, möchte ich noch kurz in Erinnerung rufen, dass SQL Server zwei grundsätzlich unterschiedliche Indextypen kennt:
  • Gruppierter Index (clustered index) - definiert die Reihenfolge der Datensätze in der Tabelle. Daher kann es immer auch nur einen gruppierten Index pro Tabelle geben.
  • Nichtgruppierter Index (nonclustered index) - es kann beliebig viele nichtgruppierte Indexe zu einer Tabelle geben.

Regeln für gruppierte Indexe
  1. Jede Tabelle sollte einen gruppierten Index (clustered index) haben. Der gruppierte Index gibt die Reihenfolge vor, mit der die Datensätze in der Tabelle gespeichert werden. Die Spalte, auf die der gruppierte Index erstellt wird, sollte folgende Eigenschaften aufweisen:
    1. Sie sollte häufig in Abfragen verwendet werden.
    2. Sie sollte eine hohe Selektivität aufweisen, d. h. in dieser Spalte sollten möglichst wenige doppelte Werte vorkommen. Zum Beispiel ist der Nachname eines Kunden nicht so selektiv wie die Kundennummer.
    3. Der Wert in dieser Spalte sollte sich möglichst nicht ändern. Jede Änderung eines Wertes in einer Spalte, die in einem gruppierten Index enthalten ist, zieht eine Aktualisierung aller nonclustered-Index-Einträge des betreffenden Datensatzes nach sich.
  2. SQL Server erstellt standardmäßig einen gruppierten Index auf die PRIMARY KEY Spalte(n). Das ist in den meisten Fällen auch sinnvoll, denn auf den Primärschlüssel treffen alle vorstehenden Eigenschaften zu. Sie sollten sich aber darüber klar sein, dass Sie bei Bedarf auch einen gruppierten Index auf andere Spalten erstellen können (DROP INDEX und CREATE INDEX).
Allgemeine Regeln für Indexe
  1. Ein Index sollte nicht mehr Spalten enthalten als nötig. Je "breiter" ein Index ist, desto mehr Arbeitsspeicher ist erforderlich um die Indexinformationen zu verarbeiten.
  2. Erstellen Sie nicht mehr Indexe als nötig. Jeder INSERT, UPDATE und DELETE Befehl erfordert eine Aktualisierung aller Indexe der Tabelle. Meist wiegt der Geschwindigkeitsvorteil beim SELECT den Aufwand für die Aktualisierung mehr als auf - aber geschenkt bekommen wir diesen Vorteil nicht.
  3. Aus demselben Grund ist es nicht sinnvoll mehrere Indexe zu haben, welche dieselben Spalten beinhalten. Sie kosten nur Speicherplatz und Performance bei Datenänderungen.
  4. Ebenfalls sollten Sie die Indexe entfernen, die nicht mehr benutzt werden. Hierfür gibt es einen Datenbank-Bericht (Rechtsklick auf die Datenbank, Menüpunkt "Berichte") mit dem Namen "Statistik zur Indexverwendung". Dort können Sie die Indexe identifizieren, die sehr lange nicht mehr verwendet wurden. Aber Vorsicht: sicherheitshalber sollten Sie vor das CREATE INDEX Skript vorher erstellen und an einem Ort speichern, wo Sie es wiederfinden - man weiß ja nie...
Werkzeuge zur Indexerstellung

Weil das Auffinden der "richtigen" Indexe nicht trivial ist und von den Abfragen abhängt, die an die Datenbank gesendet werden, enthält SQL Server ein hervorragendes Werkzeug: Den Datenbankoptimierungsratgeber (Database Engine Tuning Advisor). Sie können damit sowohl für einzelne SQL Statements als auch für eine aufgezeichnete Arbeitslast (workloads) Indexempfehlungen generieren lassen. Machen Sie sich mit diesem Werkzeug vertraut - es lohnt sich. Oder besuchen Sie meinen Workshop zur Optimierung von SQL Server :-)
Aber bei allem Lob für das Werkzeug ist Vorsicht angebracht, denn welche der Vorschläge für neue Indexe Sie umsetzen und welche nicht, das müssen Sie entscheiden. Ich hoffe, die vorstehenden Punkte helfen Ihnen bei dieser Entscheidung.