Indexerstellung in Datenbanken - Worauf muss ich achten?

Nach unserem Artikel "SQL Server Assessment - Was kann ich erwarten?" möchten wir Ihnen auch in diesem Beitrag das Thema Performance-Optimierung in der MSSQL Instanz näher bringen.
Das A und O bei der Optimierung in Datenbanken ist eine zweckmäßige Indizierung. Hier passt nicht “viel hilft viel”, sondern Indizes müssen gezielt die Abfragen unterstützen und ein Zuviel beeinträchtigt die Leistung, weil sich Datenaktualisierungen und Löschoperationen nicht nur auf die Tabellen, sondern auch auf die Indizes auswirken. Hierbei müssen aber auch gewisse Grundregeln berücksichtigt werden. In unserem Assessment von MSSQL Instanzen werden Index-spezifische Fehleinstellungen in über dreißig verschiedene Gruppen unterteilt, von denen hier einige behandelt werden. Berücksichtigen Sie bitte auch, dass keine der hier vorgestellten Informationen ohne entsprechende Tests in einer Testumgebung in eine produktive Datenbank übernommen werden dürfen.

Einige Basisinformationen

In diesem Kapitel werden einige Basisinformationen zu Indizes wiedergegeben. Der erfahrene DBA kann die folgenden Unterkapitel also überspringen.

Was ist ein Index?

Ein Index ist eine Struktur in der Datenbank, die bei der gezielten Suche nach Informationen unterstützt/unterstützen kann. Vergleichbar ist ein Index mit dem “Inhaltsverzeichnis” des Katalogs eines Versandhauses. Existiert dieses Inhaltsverzeichnis nicht, kann man ein mögliches Produkt nur durch seitenweises Durchblättern des Kataloges finden. Andernfalls erhält man einen gezielten Zugriff auf die gewünschte(n) Seite(n). Der Vorteil eines Index besteht also in einer sehr effizienten Suchmöglichkeit. Der Nachteil wird dadurch verursacht, dass man bei Katalogaktualisierungen auch das Inhaltsverzeichnis anpassen muss. Man erkauft sich den erheblich beschleunigten Zugriff also mit zusätzlichem Verwaltungsaufwand.

Wie ist ein Index aufgebaut?

Ein Index ist eine Datenstruktur die baumartig aufgebaut ist, um ein vorgegebenes Suchmuster möglichst schnell zu finden. SQL Server kennt i.W. zwei Typen solcher Indizes, Clustered und Non-Clustered. Statt nun die x-te Abbildung zur Struktur und zu den Unterschieden dieser beiden Typen ins Netz zu stellen, sei auf die Abbildungen in What do Clustered and Non-Clustered index actually mean? verwiesen.

Was ist ein Index-Scan?

Ein Index-Scan ist eine relativ aufwändige und ineffiziente Methode, auf seine Daten zuzugreifen. Eine Abfrage, die in unserem Versandkatalog alle Produkte auswählt, die den Begriff hose (Klein- oder Großschreibweise) enthalten (Damen-, Herren-, Kinder-, Bade-, Pyjama-, Stoff-, Leder-, Hosenträger, Hosenknopf, auf SQL Ebene also ein “like %hose%”) hieße, dass man das gesamte Inhaltsverzeichnis nach den gewünschten Begriffen durchsuchen (=Full Index Scan) und anschließend mit den gefundenen Seiten den Katalog aufschlagen müsste. Aufwändiger als über den Index-Scan ist in der Regel dann nur noch ein Full Table Scan, bei dem man sich alle Einträge der Tabelle / alle Katalogseiten ansehen muss. Bei einer Aufwandsabschätzung muss jedoch auch berücksichtigt werden, ob ein Clustered oder ein Non-Clustered Index vorliegt. Bei einem Clustered Index erhält man die gewünschten Informationen direkt aus den Blattknoten des Indexbaums, während man bei einem Non-Clustered Index ausgehend vom Blattknoten einen oder mehrere Zugriffe auf die gewünschte Information durchführen muss.

Was ist ein Index Seek?

Beim Index-Seek erfolgt die Navigation ausgehend von der Wurzel des Baums direkt zu den Blättern in denen die gewünschte Information steht. Um bei dem Vergleich mit dem Versandhauskatalog zu bleiben: Weiß man bereits die komplette Bezeichnung der Produktgruppen, so dass man nicht die like-Abfrage verwenden muss, sondern die exakten Bezeichnungen mit einer oder-Verknüpfung wählen kann, so können die Zielseiten im Inhaltsverzeichnis direkt über den Eintrag für Damenhose, …, Hosenknopf ermittelt werden.

Was ist ein Clustered Index / eine Clustered Table?

Der Clustered Index stellt einen Indexbaum dar, der in den inneren Knoten lediglich Sortierinformationen enthält und in den Blattknoten zusätzlich die Daten einer Tabellenzeile. Damit kann er auch als eine sortierte Tabelle betrachtet werden, wird auch oft als Clustered Table bezeichnet und kann naturgemäß pro Tabelle nur höchstens einmal existieren.

Was ist ein Nonclustered Index?

Der Nonclustered Index stellt einen Indexbaum dar, in dem die Blattknoten auf einen Speicherstruktur außerhalb des Index verweisen. Diese Speicherstruktur kann entweder ein Clustered Index oder die Zeile einer Heap-Tabelle (s.u.) sein.

Heap-Tabelle

Heap-Tabellen sind Tabellen, deren Zeilen unstrukturiert im verfügbaren Speicherplatz der DB-Datei(en) abgelegt wurden. Die Strukturierung dieser Datenzeilen kann über geeignete Nonclustered Indizes erfolgen. Heap-Tabellen werden eingesetzt, wenn es darauf ankommt, Daten besonders schnell in die Tabelle einzufügen. Bei Datenaktualisierungen ist dieser Tabellentyp etwas langsamer als die Clustered Tabelle.

Was ist eine Include-Spalte?

Eine Include-Spalte ist ein Konstrukt das verwendet werden kann, um Abfragen zu beschleunigen, wenn man nur auf einen Teil der Tabellenspalten zugreifen muss. Include-Spalten können nur für Nonclustered Indizes verwendet werden.

Wie bereits oben dargestellt, muss in einem Nonclustered Index nach der Navigation zu den relevanten Blattknoten ein weiterer Zugriff auf die zugehörigen Daten (im Blattknoten des Clustered Index bzw. auf die Speicherseite der Heap-Tabelle in der sich der betreffende Datensatz befindet) erfolgen. Hier sind dann alle Spalteninhalte der jeweiligen Zeile verfügbar. Benötigen Sie in Ihrer Abfrage nur eine relativ kleine Anzahl der Spaltendaten, so können Sie durch Aufnahme dieser wenigen Spalten in den Index den Zugriff auf die eigentliche Tabelle / den Clustered Index einsparen. In diesem Fall liegen die Include-Spalten in den Blattknoten des Index. Storage-technisch bedeutet das dann jedoch, dass die Informationen der betreffenden Spalten sowohl in der Tabelle als auch im Index abgelegt sind.

Zusammenfassung zur Verwendung von Index- und Include-Spalten

Hier als Zusammenfassung zur Verwendung von Index- und Include-Spalten aus nicht-technischer Sicht:

Beim Aufbau eines Index sollte man sich bezüglich der Spaltenauswahl an dem where-Kriterium performance-relevanter Abfragen orientieren. Beziehen sich diese Abfragen nur auf einen Teil der Tabellenspalten, so können / sollten die per select selektierten Spalten als include verwendet werden.

Fremdschlüssel

Fremdschlüssel sollten grundsätzlich indiziert werden. Fremdschlüssel sind das Mittel, über das Detail-Datensätze mit den zugehörigen Masterdatensätze verknüpft sind. Werden Fremdschlüssel nicht indiziert, so bedeutet dies, dass beim kaskadierenden Löschen von Master-Datensätzen weitreichende Sperren in der Detail-Tabelle erforderlich sind und ein Scan durch die gesamte Detail-Tabelle benötigt wird. Hierdurch kann es insbesondere auch zum Auftreten von Deadlocks in Ihrer Datenbank kommen.

Verwendung zu vieler Indizes

Mit jedem zusätzlichen Index, erhöht sich der Aufwand beim Einfügen, Aktualisieren oder Löschen von Daten. Diese Aktualisierungen gehen mit Sperroperationen einher. Besitzt eine Tabelle zu viele Indizes, können die o.a. Datenänderungen zu erhöhten Sperren in der Datenbank führen. Was im Endeffekt noch gerade angemessen und was eher zu viel ist, hängt von der Art und Weise ab, wie man die Datenbank verwendet, überwiegend zum Lesen oder überwiegend zum Schreiben. Unser Assessment macht darauf aufmerksam, wenn durch den Einsatz zu vieler Indizes übermäßig viele Sperren zur Aktualisierung benötigt werden.

Verwendung zu weniger Indizes

Werden Abfragen nicht durch passende Indizes unterstützt, so erhält man das Verhalten wie bei unserem Versandhauskatalog, dem das Inhaltsverzeichnis fehlt: Man muss sich seitenweise durch den Katalog durcharbeiten bzw. einen “Full Table Scan” durchführen, um seine Ergebnisse zu erhalten. Der Katalog hat vielleicht “nur” ca. 1000 Seiten, Ihre Datenbank aber möglicherweise einige TB.

Möglichkeit einen Index zu filtern

Es gibt Datenbanksituationen, in denen bestimmte Spaltenwerte für benutzerspezifische Abfragen irrelevant sind. Eine solche Situation kann man sich beispielsweise für Spalten vorstellen, deren Inhalt in den überwiegenden Fällen NULL sind oder in einem konkreten Fall für Rechnungen die bereits bezahlt wurden, mit einem gesetzten bezahlt-Flag versehen wurden und deshalb für das Mahnwesen irrelevant sind. Sind gewisse Kriterien für Abfragen nicht relevant, so können Indexwerte die diese Kriterien erfüllen aus dem Index herausgelassen werden. Das kann je nach Datenbestand den Umfang des Index-Baums und damit den Aufwand für eine Suche in diesem Index erheblich reduzieren.

Unser Assessment erkennt solche Kandidaten und macht darauf aufmerksam. Es ist jedoch die Aufgabe des Entwicklers und/oder DBA, diese Hinweise zu bewerten und ggf. darauf zu reagieren.

Indizierung von Textspalten

Textspalten sind i.d.R. sehr lang und werden in Abfragen häufig nicht so verwendet, wie es durch einen Index unterstützt wird. Eine effiziente Index-Unterstützung kann nur stattfinden, wenn sich die Suche auf den gesamten Text oder den Beginn eines Textes beziehen.

Beispiel:

nicht aber

weil hier die Information über den Beginn der zu suchenden Zeichenkette fehlt und der Index eine alphabetisch sortierte Liste der Produktnamen darstellt. Im Zusammenhang mit Textsuchen sollte die MSSQL-Komponente Volltext-Suche installiert und verwendet werden. Diese ist der “normalen” Textsuche bzgl. Suchmöglichkeiten und Performance bei weitem überlegen, erfordert jedoch eine andere Syntax bei der Abfrage.

Verwendung zu vieler Spalten

Jede zusätzliche Spalte, die in einen Index aufgenommen wird, vergrößert diesen und erhöht den Aufwand bei der Pflege. Wir haben schon oft Indizes gesehen, die über mehr als zehn Spalten einer Tabelle gebildet wurden. Dies können wir i.a. nur als verzweifelte Tat sehen, durch die vielen Spalten mangelnde Performance doch noch in den Begriff zu bekommen. Es gibt aber auch Sonderfälle, in denen ein solcher Index sinnvoll sein könnte, nämlich in einem Datawarehouse in einer Tabelle die sehr groß ist, nur sehr selten aktualisiert wird und für die es eine Abfrage mit entsprechend vielen Spalten gibt. Als groß würden wir in diesem Zusammenhang Tabellen mit einigen zig-Millionen Zeilen betrachten.

Indizes die nie verwendet werden

In einigen Fällen kann eine Datenbank Indizes enthalten, die keine der üblichen Abfragen unterstützen. Dies kann historisch bedingt sein, aber auch beispielsweise dadurch, dass die Datentypen der Abfrage nicht mit den Datentypen der Indizes übereinstimmen, so dass der Optimizer sie nicht verwenden / erkennen kann. In einer solchen Situation muss der Index bei jeder Datenänderung gepflegt werden, bringt aber keinen Vorteil und kann damit gelöscht werden. Denkbar wäre aber auch, dass dieser Index nur bei bestimmten Sonderläufen, z.B. Reporting zum Ende eines Quartals / Jahres verwendet wird. In diesem Fall sollte der Index dann nur für diesen Sonderlauf angelegt und anschließend wieder gelöscht werden.

Nonunique Clustered Index

Ein Clustered Index erfordert, dass die Werte in der/den Indexspalte(n) eindeutig ist/sind. Ist dies nicht der Fall, so muss SQL Server hier eine zusätzliche (unsichtbare) Spalte verwenden, um Eindeutigkeit zu erreichen. Hierdurch wird die Tabelle implizit etwas vergrößert. Ein entsprechender Performanceverlust und höhere Storage-Verwendung werden sich zwar vermutlich nur in Extremfällen bemerkbar machen. Als allgemeine Designempfehlung können wir Ihnen mitgeben, Tabellen immer mit einem künstlichen Primärschlüssel mit IDENTITY-Eigenschaft anzulegen und diesen Primärschlüssel als Clustered Index für die Tabelle zu verwenden.

Sortierung und Gruppierung

Sortierung ist die wohl aufwändigste Operation in der Datenbank. Ein paar tausend Tabellenzeilen sind sehr schnell sortiert und Sie werden vermutlich keinen Unterschied merken, ob die Daten sortiert werden oder nicht. Kleinere Datenmengen können auch problemlos im Hauptspeicher sortiert werden. Bei größeren Datenmengen muss dagegen die tempdb hinzugezogen werden, das heißt die Daten werden zunächst vom Storage in den Hauptspeicher geladen und anschließend in die tempdb (Storage) wo die Sortierung stattfindet. Aber auch das ist noch nicht das Problem, sondern die exponentiell wachsende Komplexität der Sortierung: Was im vergangenen Jahr noch in wenigen Minuten erledigt werden konnte, macht möglicherweise in diesem Jahr, nach einigem Datenwachstum, Probleme. Hier hilft die Struktur eines Index, in dem die Indexspalte(n) ja bereits in sortierter Reihenfolge in den Blättern des Indexbaums abgelegt ist/sind. Das heißt also, dass bei geschickter Wahl eines Index die Sortieroperation entfallen kann.

Gruppierung setzt sortierte Daten voraus, so dass hier das gleiche gilt wie für die Sortierung.

Fazit

In diesem Artikel wurden einige grundlegende Betrachtungen zur Verwendung von Indizes in Datenbanken angestellt. Er betrachtet einige der über dreißig Kriterien die von unserem Assessment untersucht werden. Das Design von Indizes erfordert nicht nur Kenntnis über die Struktur der Daten, sondern auch zu deren Umfang und insbesondere über die typischen Abfragen. Hier bietet unser Assessment ein wertvolles Hilfsmittel zur Analyse von Abfragen, Indizes und zur Aufdeckung von Fehlern bei der Indizierung.

Gerne stehen unsere Expert:innen bei weiteren Fragen zur Verfügung. Kontaktieren Sie uns dafür gerne unverbindlich über unser Kontaktformular. Wir freuen uns von Ihnen zu hören.