MS SQL-Server > Datenbank-Funktionen > Indizierung

Was ist ein gruppierter (clustered) Index?

Ein gruppierter (clustered) Index ist eine von zwei Arten wie der MS SQL Server (und auch andere Datenbanken) die Daten einer Tabelle physisch auf dem Datenträger speichern kann - entweder

Ungeordnet, als sogenannter Heap (= ohne Definition eines gruppierten (clustered) Index) oder

Geordnet, als gruppierter (clustered) Index

Bei der Verwendung eines gruppierten (clustered) Index wird eine Indexstruktur geordnet nach den Indexschlüsselspalten in Form einer B*-Baum-Struktur (B*-Tree) aufgebaut, bei der sich auf der Blattebene die Daten der Tabelle befinden (im Gegensatz zum nicht-gruppierten (nonclustered) Index, bei dem sich auf Blattebene der Zeilenlokator (row locator) und ggf. die Daten der sogenannten "enthaltenen Spalten" befinden).

Pro Tabelle kann es nur einen gruppierten (clustered) Index geben, weil die Daten selbst nur einmal (angeordnet) gespeichert werden können (Kopien der Daten können in zusätzlichen nicht-gruppierten (nonclustered) Indizes angeordnet gespeichert werden).

Vergleichbare Beispiele der Verwendung eines gruppierten(clustered) Index (allerdings ohne geeignete Suchstruktur ;-)) in der "Realwelt":

Die Daten im Duden sind alphabetisch sortiert abgelegt - beim Erreichen des Eintrages sind alle weiteren Informationen zum gesuchten Wort vorhanden (= Daten auf Blattebene)
Die Daten in einem Tagebuch sind bereits chronologisch sortiert abgelegt - beim Erreichen eines Datums ist man auch bereits beim entsprechenden Eintrag (= Daten auf Blattebene)

Vor- und Nachteile eines gruppierten (clustered) Index

Vorteile:

Die Suche (bzgl. der Indexschlüsselspalten) ist i. d. R. schneller als bei einem nicht-gruppierten (nonclustered) Index da beim Erreichen der Blattebene der Datensatz bereits geladen ist (alle Daten werden beim gruppierten (clustered) Index auf der Blattebene gespeichert - es müssen nicht über einen Zeilenlokator (row locator) zusätzlich weitere Daten geladen werden). Dieser Vorteil hebt sich beim nicht-gruppierten (nonclustered) Index auf, wenn der Index bereits alle benötigten Spalten (entweder in den Schlüsselspalten oder als enthaltene Spalten) einer Abfrage enthält (dann gilt die Abfrage als durch den Index abgedeckt)
Die Daten sind bereits bzgl. der Indexschlüsselspalten sortiert - falls diese in der selben Reihenfolge ausgegeben werden sollen, ist keine zusätzliche Sortieroperation erforderlich
Eine Gruppierungsoperation bzgl. der Indexschlüsselspalten ist mit weniger Aufwand möglich, da die Daten nicht vorsortiert werden müssen
Eine Bereichssuche bzgl. der Indexschlüsselspalten ist mit weniger Aufwand möglich, da die Daten bereits entsprechend sortiert auf dem Datenträger abgelegt sind (und sequentiell geladen werden können)

Nachteile:

Das Einfügen / Löschen / Aktualisieren von Daten dauert i. d. R. länger als bei einem Heap, weil die Indexstruktur evtl. korrigiert werden muss (B*-Baum-Struktur, die evtl. neu ausbalanciert wird) - d. h. es müssen evtl. Daten umgeschrieben werden, um weiterhin effektiv suchen zu können - bei einem Heap werden die Daten einfach ans Ende geschrieben (Achtung: Die Reihenfolge der Daten ist beim Heap prinzipiell nur beim Einfügen fortlaufend - der SQL Server behält sich aber vor, die Daten jederzeit umsortieren zu können, wenn dadurch der Zugriff oder die Datenablage effizienter ist - es gibt also keine Garantie, dass die Daten in derselben Reihenfolge auf dem Datenträger abgelegt sind, wie diese eingefügt wurden und damit stimmt u. U. auch die Ausgabe der Daten nicht mit der Eingabereihenfolge überein und muss deshalb mit ORDER BY bestimmt werden (falls eine sortierte Ausgabe notwendig ist))
Enthält eine Tabelle einen gruppierten (clustered) Index, wird für alle nicht-gruppierten (nonclustered) Indizes als Zeilenlokator die Schlüsselspalten des gruppierten (clustered) Index (anstatt einer RID bestehend aus ID der Datendatei, Blatt-Nummer in der Datei und Zeilen-Nummer (Position) auf dem Blatt) verwendet, was die Anzahl der Lese-Operationen beim Anwenden eines nicht-gruppierten (nonclustered) Index zwangsläufig erhöhen kann (da nach einem gefundenen Eintrag anschließend erst im gruppierten Index gesucht werden muss, um die Daten zu finden)

Als Standard-Einstellung erzeugt der SQL Server immer automatisch einen gruppierten (clustered) Index für den Primär-Schlüssel. Das ist in der Regel auch sinnvoll, insbesondere auch wenn dieser in Fremdschlüssel-Bedingungen eingesetzt wird oder wenn die Tabelle häufig in einer SQL Abfrage verbunden (JOIN) wird (über den Primärschlüssel)

Es kann aber auch Fälle geben, wo es sinnvoller wäre keinen gruppierten (clustered) Index zu haben:

Bei einer reinen Log-Tabelle, die zu einem späteren Zeitpunkt verarbeitet werden soll) - hier geht es evtl. um das (sehr) schnelle Schreiben und eine Suche ist gar nicht notwendig (oder die Daten werden später für die Suche in anderen Tabellen (mit geeigneten Indizes) aufbereitet) - hier ist ein Heap prinzipiell die bessere Wahl

Gruppierten Index erstellen

CREATE [ UNIQUE ] CLUSTERED INDEX <IndexName> ON <TabellenName>

( <SpaltenName>[ ASC | DESC ] [ ,...n ] )

[ WITH (

< PAD_INDEX = { ON | OFF }, >

<SORT_IN_TEMPDB = { ON | OFF }, >
| <IGNORE_DUP_KEY = { ON | OFF }, >
| <STATISTICS_NORECOMPUTE = { ON | OFF },>
| <STATISTICS_INCREMENTAL = { ON | OFF },>
| <DROP_EXISTING = { ON | OFF },>
| <ONLINE = { ON | OFF },>
| <ALLOW_ROW_LOCKS = { ON | OFF },>
| <ALLOW_PAGE_LOCKS = { ON | OFF },>
| <MAXDOP = max_degree_of_parallelism,>
| <DATA_COMPRESSION = { NONE | ROW | PAGE} ,>