Was ist eigentlich... ein Clustered Index?

LeonardH

Ihr beschäftigt euch mit SQL und wollt wissen, was ein Index ist und wofür wir das brauchen? Dann seid ihr hier richtig. In diesem Artikel schauen wir uns an, was ein clustered index (gruppierter Index) ist, wie wir einen erstellen und wie er funktioniert.

Dieses Beispiel ist für alle geeignet, die alt genug sind, um noch zu wissen, wie ein gutes altes Telefonbuch verwendet wurde (alle anderen: stellt euch ein Lexikon vor… oder irgendein Buch… eines mit richtigen Seiten aus Papier 😉 ).

Angenommen, wir suchen nach der Telefonnummer von Bruce Wayne im Telefonbuch von Gotham City. Angenommen, dieses Telefonbuch hat 1000 Seiten. Kein Problem für uns, denn wir wissen ja, ein Telefonbuch ist alphabetisch erst nach Nachnamen, dann nach Vornamen geordnet. Wir blättern also zu „W“, streifen kurz mal über „Wayne, Alex“ und „Wayne, Amanda“ und schon haben wir „Wayne, Bruce“ gefunden.

Jetzt stellt euch aber mal vor, die Einträge im Telefonbuch wären nicht alphabetisch geordnet, sondern in der Reihenfolge, in der die Leute dort zugezogen sind. Bei einer so alteingesessenen Familie wäre „Wayne, Bruce“ wohl relativ weit am Anfang zu finden, aber wo genau auf diesen 1000 Seiten? Entsprechend länger dauert es, bis wir etwas gefunden haben.

Aber genau so speichert die Datenbank die Datensätze ab; die Reihenfolge ist mehr oder weniger zufällig, gespeichert wird dort, wo gerade Platz ist.

Dabei sprechen wir von einem „heap“ – die Einträge werden sozusagen alle auf einem „Haufen“ gespeichert.

Die Datenbank müsste also alle Einträge nach „Wayne, Bruce“ absuchen, denn selbst, wenn sie den gewünschten Eintrag zufällig gleich als zweiten findet, kann sie ja nicht sicher sein, dass es nicht irgendwo noch einen anderen Einwohner mit dem gleichen Namen gibt.

Erstellen wir dazu einmal eine Beispieltabelle:

In diese Tabelle fügen wir die oben genannten Personen mit absolut authentischen Telefonnummern ein. Mit einem SELECT-Statement geben wir jetzt den Inhalt der Tabelle aus und lassen uns zugleich den Actual Execution Plan anzeigen:

Die eigentliche Ausgabe ist erwartungsgemäß unspektakulär:

Viel spannender ist für uns ein Blick in den Execution Plan:

Hier sehen wir, dass, obwohl wir nur eine einzige Person abgefragt haben, ein sogenannter Table Scan stattgefunden hat – das bedeutet, dass die gesamte Tabelle gescannt, also abgesucht wurde!

Bei unseren 12 Einträgen hat das natürlich keine erkennbaren Auswirkungen auf die Performance; bei ein paar Millionen Einträgen sieht das aber gleich ganz anders aus.

Beim Telefonbuch können wir uns darauf verlassen, dass, wenn wir „Wayne, Bruce“ unter „W“ gefunden haben, nicht noch andere Personen dieses Namens irgendwo anders versteckt sind. Wir müssen also wesentlich weniger absuchen (nur die Einträge unter „W“) und kommen somit schneller zu einem Ergebnis.

Diese Funktion erfüllt bei der Datenbank der Clustered Index. Er sorgt dafür, dass die Einträge auch in einer (vom Index bestimmten) Reihenfolge abgespeichert werden. Wir suchen also sozusagen nur unter „W“.

Datenspeicherung in der Datenbank

Die Daten werden auf sogenannten Seiten (pages) gespeichert. Auf einer Seite werden rund 8KB (8060 bytes) gespeichert. Ein Index sorgt dafür, dass die Daten in einer bestimmten Reihenfolge abgespeichert werden, und nicht als heap. Wir bekommen also einen Verweis auf die Seiten, auf denen unsere gewünschten Daten liegen, und müssen nicht mehr alle absuchen.

Navigation mit Index

Wären jetzt unsere Daten alphabetisch sortiert (was ein Index für uns übernehmen kann), könnte die Datenbank also nach „Wayne, Bruce“ suchen, wie wir im Telefonbuch: vorblättern bis „W“, A-V müssen gar nicht erst durchsucht werden.

Das kann man sich etwa so vorstellen:

Somit können wir zu der Seite mit den gesuchten Informationen navigieren, ohne alle Seiten lesen zu müssen. Ausgehend vom root node gehen wir über die intermediate nodes zu den eingentlichen data pages:

Die SQL-Anweisung, um einen Clustered Index zu erstellen, ist keine Hexerei:

Wenn wir jetzt noch einmal mittels SELECT nach Bruce Wayne suchen, sieht unser Execution Plan gleich anders aus:

Jetzt wurde kein Table Scan mehr ausgeführt, sondern ein Clustered Index SEEK, eine indizierte Suche.

Im Relationalen Datenbankmodell haben Tabellen normalerweise einen Primary Key (Hauptschlüssel). Dieser erstellt eigentlich klammheimlich im Hintergrund einen solchen Clustered Index für uns – auf der Spalte des Hauptschlüssels. In vielen Fällen ist das auch sinnvoll; will man die Datenbank optimieren, muss man allerdings von Fall zu Fall abwägen.

Eine Tabelle kann mehrere Indizes haben, davon aber nur einen Clustered Index (mit Nonclustered Indexes, also nichtgruppierten Indizes setzen wir uns in einem anderen Artikel auseinander).

Daher sollte schon bei der Planung überlegt werden, ob es im jeweiligen Fall sinnvoll ist, dass der Clustered Index über dem Hauptschlüssel liegt, oder ob man ihn lieber über eine andere Spalte setzen möchte.

Viel Spaß beim Ausprobieren, und vielleicht sehen wir uns ja in einem unserer Kurse zum Thema SQL!