Neue Indexoption "included columns"

Indexe können ein effizientes Mittel zur Steigerung der Performance von Datenbank-Abfragensein. Sie können die Anzahl von logischem und physikalischem I/O unter Umständen dramatisch reduzieren und so zu der gewünschten Performancesteigerung beitragen. Dieser Artikel beleuchtet die neue Indexoption "included columns" (eingeschlossene Spalten). Anhand eines Beispiels wird der logische I/O der neuen Option mit den beiden bisherigen Varianten verglichen.

Als anschauliches Beispiel wird eine Tabelle zur Verwaltung von Adressen sowie die Abfrage aller Adressen mit dem Wohnort "Berlin" verwendet. In der Tabelle befinden sich 10.000 Adressen, 5 Prozent davon aus Berlin. Den SQL-Befehl zum Erstellen der Tabelle finden Sie in Abbildung 1, den SQL-Befehl der Abfrage in Abbildung 2.

CREATE TABLE Adressen(
  Name CHAR(100), 
  Vorname CHAR(100), 
  Strasse CHAR(100), 
  Wohnort CHAR(100), 
  Telefon CHAR(100), 
  Hinweise CHAR(3000) 
);

Um die Abfrage der Adressdaten zu beschleunigen, werden folgende drei Indexe angelegt:

• Ein einfacher Index auf der Spalte "Wohnort"
• Ein abdeckender Index auf alle in der Abfrage verwendeten Spalten("coveringindex")
• Ein Index mit der neuen Option der eingeschlossenen Spalten ("included columns")

Für jeden Index wird dessen Größe ermittelt, sowie die Anzahl der Seiten, die für die Beispielabfrage gelesen werden müssen. Dabei wird teilweise mit Näherungswerten gerechnet und es werden nicht alle Verwaltungsinformationen oder -seiten mit einbezogen, da es hier nicht auf die genauen Werte, sondern vielmehr auf die Größenverhältnisse ankommt.

SELECT Name, Vorname, Strasse, Wohnort
	FROM Adressen
	WHERE Wohnort = 'Berlin'; 

Zunächst wird jedoch die Größe der Tabelle berechnet und der Aufwand für den Fall abgeschätzt, dass die Abfrage ohne Index durchgeführt wird.

Jeder Datensatz belegt in diesem Beispiel 3.500 Bytes. Da in einer 8 KB großen Datenseite des SQL Servers somit 2 Datensätze gespeichert werden können, würden 10.000 Datensätze hier 5.000 Datenseiten belegen.

Wird kein Index verwendet, muss die komplette Tabelle gelesen werden und jeder Datensatz auf das Kriterium der WHERE-Klausel hin überprüft werden, also in unserem Fall 5.000 Datenseiten.

Ein einfacher Index auf der Spalte "Wohnort"

Um die Anzahl der zu durchsuchenden Seiten zu reduzieren, wird in einem ersten Schritt ein Index auf die Spalte "Wohnort" gelegt (siehe Abbildung 3). Jetzt können über den Index die Datenseiten ermittelt werden, die die benötigten Datensätze enthalten. Diese Datenseiten müssen dann in einem separaten Schritt gelesen werden. Man spricht hierbei von Lookup-Zugriffen.

CREATE INDEX idx_Adressen_Wohnort ON Adressen (Wohnort); 

Bei der Berechnung der Größe des Indexes wird immer mit der untersten Ebene (Ebene 0, diese wird auch Blattebene genannt) begonnen. Dort besteht jeder Index-Datensatz aus dem Index-Feld (100 Bytes) und dem Verweis auf die Datenseite des zugehörigen Datensatzes (6 Bytes). In eine 8 KB große Indexseite passen demnach 76 Index-Datensätze. Bei 10.000 Datensätzen werden also 132 Seiten benötigt.

Zusätzlich wird eine weitere Ebene (Ebene 1) mit zwei Indexseiten benötigt, in der die Verweise auf die Indexseiten der Blattebene gespeichert werden. Eine weitere Ebene (Ebene 2) mit einer Seite bildet die Wurzelseite (root_page) als Ausgangspunkt für alle Abfragen. Siehe hierzu Abbildung 4.

Abb. 4: Baumdarstellung des ersten Indexes.

Jetzt müssen circa fünf Prozent der Indexseiten auf Blattebene gelesen werden (7 Seiten), um die Adressen derjenigen Datenseiten zu ermitteln, die Adressen aus Berlin enthalten. Im besten Fall stehen je zwei Berliner Adressen zusammen auf einer Datenseite. Im schlechtesten Fall steht jede Berliner Adresse zusammen mit einer Nicht-Berliner Adresse auf einer Seite. Um die 500 Berliner Adressen zu ermitteln, müssen also zwischen 250 und 500 Datenseiten gelesen werden.

Zusammen mit den oberen Ebenen (Ebenen 1 und 2) des Index (3 Seiten) sind also insgesamt zwischen 260 und 510 Seiten zu lesen.

Ein abdeckender Index auf allen Spalten der Abfrage

Um die Anzahl der zu lesenden Datenseiten weiter zu reduzieren, gab es schon in den vergangenen Versionen des SQL Servers die Möglichkeit, zusammengesetzte Indexe zu erstellen. Werden neben den Spalten der WHERE-Klausel auch alle Spalten der SELECT-Klausel in den Index aufgenommen, so spricht man von einem abdeckenden Index ("covering index"), siehe Abbildung 5.

CREATE INDEX idx_Adressen_ganze_Adresse
	ON Adressen (Wohnort, Name, Vorname, Strasse); 

Jetzt ist für die Durchführung der Abfrage ein Zugriff auf die Datenseiten nicht mehr notwendig, da alle benötigten Informationen bereits in den Indexseiten enthalten sind.

Die Anzahl der Indexseiten auf der Blattebene steigt hierbei jedoch auf 527, da jeder Index-Datensatz 406 Bytes groß ist und nur noch 19 Index-Datensätze in eine Indexseite passen. Auf der nächsthöheren Ebene werden nun 28 Indexseiten benötigt, um auf die 527 Seiten der Blattebene zu verweisen. Deshalb wird unterhalb der Wurzelseite eine weitere Ebene (Ebene 2) mit 2 Seiten benötigt, um auf die 28 Seiten der Ebene 1 zu verweisen. Siehe hierzu Abbildung 6.

Abb. 6: Baumdarstellung des zweiten Indexes.

Die Anzahl der zu lesenden Indexseiten steigt zwar auf circa 30 (5 Prozent von 527, zusätzlich einige Seiten der höheren Ebenen 1 bis 3), allerdings müssen keine Datenseiten mehr gelesen werden.

Diese Lösung sieht auf den ersten Blick sehr gut aus, doch hat sie einen entscheidenden Nachteil: Bei jeder Änderung an einer der Index-Spalten muss nicht nur die entsprechende Datenseite, sondern auch der Index aktualisiert werden. In der vorhergehenden Lösung (siehe Abbildung 4) mit nur einem Index auf "Wohnort" musste der Index nur dann aktualisiert werden, wenn sich der Wohnort geändert hat.

Ein Index mit eingeschlossenen Spalten

Mit der Version 2005 hat der Microsoft SQL Server eine neue Indexoption bekommen: Die eingeschlossenen Spalten ("included columns"). Die Werte dieser Spalten werden nur in der Blattebene des Indexes gespeichert, nicht in den übergeordneten Ebenen (Ebene 1 und höher). Der Index ist auch nicht nach diesen Spalten sortiert, womit bei einer Datenänderung innerhalb dieser Spalten der Index nicht umsortiert werden muss.

Um diese Funktion optimal zu nutzen, werden die Spalten der WHERE-Klausel als Index-Spalten und die zusätzlichen Spalten der SELECT-Klausel als eingeschlossene Spalten in den Index aufgenommen (siehe Abbildung 7).

CREATE INDEX idx_Adressen_ganze_Adresse_Inc
	ON Adressen (Wohnort) INCLUDE (Name, Vorname, Strasse);

Hierdurch ist die Blattebene dieses Indexes genauso groß wie die Blattebene des zuvor besprochenen abdeckenden Indexes (siehe Abbildung 6). Es werden auch hier 527 Indexseiten für die Blattebene benötigt.

Die nächsthöhere Ebene (Ebene 1) enthält jedoch nur die Spalte "Wohnort" sowie den Verweis auf die zugehörige Seite der Blattebene. So passen, wie im ersten Beispiel (siehe Abbildung 4), 76 Index-Datensätze in eine Seite. Es werden auf dieser Ebene also 8 Seiten benötigt. Diese 8 Seiten können alle von der Wurzelseite (Ebene 2) aus verkettet werden, so dass dieser Index gegenüber dem vorherigen Index um eine Ebene kleiner ist. Siehe hierzu Abbildung 8.

Abb. 8: Baumdarstellung des dritten Indexes.

Im Vergleich zum einfachen Index aus Abbildung 4 können die Werte der Spalten der SELECT-Klausel jetzt ohne Zugriff auf die Tabellen-Seiten direkt aus den Indexseiten ermittelt werden. Nachteile dabei sind aber ein etwas größerer Index und die doppelte Datenhaltung. Da die Daten sowohl in den Tabellen-Seiten als auch in den Indexseiten gespeichert sind, vergrößert sich der Aufwand bei Datenänderungen.

Im Vergleich zum abdeckenden Index aus Abbildung 6 belegt der Index mit eingeschlossenen Spalten aus Abbildung 8 weniger Platz, besteht vielfach aus weniger Ebenen und muss nur reorganisiert werden, wenn die Werte der Index-Spalte ("Wohnort") geändert werden.

Fazit

Das hier vorgestellte Beispiel ist so gewählt, dass der Effekt der eingeschlossenen Spalten deutlich wird. Ob ein Index mit eingeschlossenen Spalten tatsächlich zu einer Steigerung der Performance führt, hängt aber von vielen Faktoren ab: z. B. vom Verhältnis zwischen lesenden und schreibenden Zugriffen sowie von der Größe der Tabelle und der betroffenen Spalten. Gerne unterstützen wir Sie bei der Analyse und Optimierung Ihrer Datenbankanwendung, nicht nur hinsichtlich der Einführung dieser neuen Indexoption.

Andreas Jordan (info@ordix.de).

Seitenanfang

Seite drucken

Seite weiterempfehlen