Teil 1: Index-Basics (klassische Indexvarianten)

SQL Server Indizes – der Schlüssel zu optimaler Datenbankperformance
Kommentare

Diese vierteilige Artikelserie bietet eine Einführung in die verschiedenen mit SQL Server verfügbaren Indexformen mit deren individuellen Besonderheiten. Es wird erklärt, wann welche Indexvariante am besten einzusetzen ist, aber auch was bei der Definition und Verwendung zu beachten ist, damit diese möglichst effizient genutzt werden können.

Nach wie vor nutzen die meisten ernsthaften Businessanwendungen relationale Datenbanksysteme wie den SQL Server zur Datenspeicherung. Damit auf die dort gespeicherten Daten auch performant zugegriffen werden kann, sind – angefangen vom Datenbankdesign bis hin zur Abfrage – Indizes ein ganz entscheidender Faktor. Jedoch bietet MS SQL Server mittlerweile eine ganze Reihe von verschiedenen Indexformen: Neben den klassischen gruppierten und nicht gruppierten Indizes gibt es Indizes für spezielle Einsatzgebiete wie Volltextindizes, XML-Indizes und räumliche Indizes. Dazu kamen später die ursprünglich für Data Warehouses entworfenen Columnstore Indizes, die spätestens in der dritten Generation auch gut für andere Bereiche sinnvoll nutzbar werden.

Artikelkasten
Teil 1: Index-Basics (klassische Indexvarianten)
Teil 2: Zusätzliche Optionen und Wartung von Indizes
Teil 3: Indexformen für spezielle Verwendungszwecke
Teil 4: Spaltenbasierte Indizes und Fazit

Allgemeine Funktionsweise von Indizes

Um die grundlegende Funktionsweise von Indizes besser verstehen zu können, ist es hilfreich zu wissen, wie SQL Server die Daten auf dem Datenträger ablegt. Die Daten werden in Dateien mit der Endung .mdf oder .ndf abgelegt. Diese sind in Speicherseiten mit einer Größe von jeweils 8 KB aufgeteilt, was auch die kleinste Einheit ist, in der SQL Server Daten liest oder schreibt. Jeweils acht aufeinanderfolgende Speicherseiten (Pages) bilden ein so genanntes Extent. Während eine Speicherseite aber nur Daten einer Tabelle enthält, gibt es bei den Extents sowohl einheitliche als auch gemischte, die Speicherseiten von verschiedenen Tabellen beinhalten. Wenn eine Abfrage beispielsweise alle Personen mit einem bestimmten Nachnamen aus einer Tabelle liest, für die kein Index existiert, müssen alle Speicherseiten gelesen werden, die Daten zu dieser Tabelle enthalten.

Um diese Abfragen effektiver ausführen zu können, sind entsprechende Strukturen hilfreich, die dafür sorgen, dass die relevanten Speicherseiten schneller gefunden werden können und somit nicht mehr alle Speicherseiten zu lesen sind. Genau diese Aufgabe übernehmen Indizes.

Gruppierte (engl. Clustered) Indizes

Im einfachsten Fall ist die Tabelle selbst nach einer bestimmten Spalte sortiert (im Fall der Personentabelle beispielsweise nach dem Nachnamen). Genau das ist es, was ein gruppierter Index bewirkt. Dies geschieht allerdings nicht in Form einer einfachen Liste, sondern in Form eines binären Baums, bei dem jeder Knoten des Baums eine eigene Speicherseite verwendet, die eine alphabetisch sortierte Liste von Namen enthält, ergänzt um jeweils einen Verweis auf die Speicherseite, in der weiter nach diesem Namen (oder darauf alphabetisch folgenden Namen) zu suchen ist. Dies kann sich – je nach Anzahl der Datensätze in der Tabelle – mehrstufig fortsetzen, bis schließlich auf die Speicherseite verwiesen wird, in der die kompletten Personendaten abgelegt sind. Somit müssen nur wenige Datenseiten gelesen werden, bis schließlich der oder die passenden Tabellenzeile(n) gefunden sind.

Während ein Nachname in einer Personentabelle sicherlich mehrfach vorkommen kann, nutzt man in der Praxis für einen gruppierten Index meist eine eindeutige Spalte, die häufig dem Primärschlüssel der Tabelle entspricht, also dem Schlüssel, über den alle Zeilen einer Tabelle eindeutig identifiziert werden können. Daher wird bei Erstellung eines Primärschlüssels auch implizit ein gruppierter Index dazu erzeugt, sofern nicht explizit das Schlüsselwort NONCLUSTERED angegeben wird (Listing 1).

-- explizit
CREATE CLUSTERED INDEX PK_Person_BusinessEntityID
ON Person.Person (BusinessEntityID ASC)

-- implizit durch Erstellung eines Primärschlüssels
ALTER TABLE Person.Person 
ADD CONSTRAINT PK_Person_BusinessEntityID 
PRIMARY KEY CLUSTERED (BusinessEntityID ASC)

Die gängigste Variante für einen gruppierten Index ist eine Id-Spalte vom Typ int (oder bigint bei großen Tabellen) mit einer Identitätsspezifikation, die dafür sorgt, dass die Spalte automatisch mit eindeutigen Nummern versehen wird. Als Alternative werden oft auch Spalten vom Typ uniqueidentifier verwendet, die über die Funktion NewId() als Default Constraint ebenfalls mit eindeutigen – aber nicht fortlaufenden – Werten versorgt werden. Dies ergibt vor allem in verteilten Umgebungen Sinn, wo neue Zeilen in verschiedenen Datenbankkopien erstellt werden, die dann später zusammengefügt werden, ohne dass bei den Primärschlüsseln Überschneidungen auftreten.
Ein großer Nachteil der gruppierten Indizes liegt darin, dass die Sortierung nur in einer Reihenfolge vorliegen kann. Eine Abfrage, die anhand einer anderen Spalte (z. B. dem Vornamen) sucht, wird von einem Index auf der Spalte Nachname nicht profitieren. Um dies zu lösen, werden n nicht gruppierte Indizes verwendet.

Anzeige von Ausführungsplänen

Um die Verwendung von Indizes in Abfragen zu überprüfen, bietet das SQL Server Management Studio die Möglichkeit, sowohl die voraussichtlichen als auch die tatsächlichen Ausführungspläne von Abfragen anzeigen zu lassen. Beide Optionen lassen sich am einfachsten über die entsprechenden Optionen im Abfrage-Menü oder alternativ über die Symbolleiste aktivieren. Dabei ist zu beachten, dass die Anzeige des voraussichtlichen Ausführungsplans die Abfrage selbst nicht ausführt, während der tatsächliche Ausführungsplan nur nach Ausführung der Abfrage angezeigt werden kann. In den grafisch dargestellten Ausführungsplänen, die von rechts oben zu lesen sind, ist schnell zu erkennen, ob (und wie) der Zugriff auf einen Index oder die Tabelle selbst erfolgt. Bei dem „wie“ ist auf die Schlüsselworte „Scan“ und „Seek“ zu achten. Ein Index-Seek bedeutet, dass die relevanten Zeilen durch binäre Suche im Indexbaum gefunden werden, während ein Scan darauf hinweist, dass die Tabelle oder der Index komplett gelesen werden, was in den meisten Fällen deutlich aufwendiger ist. Ausführlichere Informationen zum Lesen von Ausführungsplänen sind in den Quellenangaben zu diesem Artikel zu finden [1], [2], [3].

Nicht gruppierte (engl. Non-clustered) Indizes

Nicht gruppierte Indizes stellen eine zusätzliche Datenstruktur dar, die ebenfalls in einer Baumstruktur verwaltet wird, deren Blätter aber nicht die eigentlichen Zeilendaten enthalten, sondern einen Verweis auf die Adresse der Daten. Vereinfacht dargestellt verhält sich also ein nicht gruppierter Index wie ein Stichwortverzeichnis am Ende eines Buchs: Dies ist zwar selbst sortiert, damit man schnell nach einem bestimmten Begriff suchen kann. Ist der Begriff gefunden, steht dahinter aber lediglich die Seitenzahl, wo der Begriff zu finden ist. Es ist also ein weiterer Wechsel auf die entsprechende Buchseite erforderlich, um die gewünschten Informationen zu finden. Beim Datenbankindex ist der Verweis auf die echten Daten natürlich etwas komplexer als eine einfache Seitenzahl. Dieser als Row-Look-up bezeichnete Prozess nutzt einen Row Identifier (kurz: RID), der aus drei Teilen besteht:

  1. Die Nummer der Datenbankdatei (denn eine Tabelle kann auf mehrere Dateien verteilt sein)
  2. Die Nummer der Speicherseite
  3. Die Nummer des Datensatzes auf dieser Speicherseite

Dies trifft allerdings nur zu, wenn die Tabelle keinen gruppierten Index hat und damit als ungeordneter „Haufen“ (engl. Heap) gespeichert ist. Existiert dagegen ein gruppierter Index, so ist als Verweis auf Blattebene des nicht gruppierten Indexes der Schlüsselwert des gruppierten Indexes angegeben. Es muss also noch ein weiterer Indexbaum durchlaufen werden, bevor die eigentlichen Daten gefunden sind. (Der Zusatzaufwand für diesen so genannten Key-Look-up ist allerdings meist eher gering.)

Abb. 1: Indexbaum zu einer Tabelle ohne gruppierten Index

Abb. 1: Indexbaum zu einer Tabelle ohne gruppierten Index

Ein großer Vorteil der nicht gruppierten Indizes liegt nun darin, dass es zu einer Tabelle mehrere davon geben kann, die nach unterschiedlichen Spalten sortiert sind. Somit kann anschließend performant nach verschiedenen Kriterien gesucht werden (Abb. 1).

Da es die nicht gruppierten Indizes aber zusätzliche Datenstrukturen sind, muss man sich auch stets der Tatsache bewusst sein, dass diese einerseits zusätzlichen Speicher benötigen und andererseits bei jeder Änderung der Daten selbst auch alle Indizes aktualisiert werden müssen, in denen die geänderten Spalten enthalten sind. Man sollte also möglichst nicht für jede Spalte einer Tabelle einen separaten Index erzeugen, sondern muss stets abwägen, für welche Spalten ein Index sinnvoll ist und wo darauf verzichtet werden kann. Als Grundregel kann man sich merken, dass zusätzliche Indizes die Lesezugriffe zwar beschleunigen können, die Schreibzugriffe aber verlangsamen. Die Entscheidung, für welche Spalten ein Index sinnvoll ist, lässt sich anhand folgender Kriterien treffen:

  • Wird nach der Spalte oft gesucht oder gefiltert, spricht dies für einen Index.
  • Dasselbe gilt für Spalten, die als Fremdschlüsselspalten verwendet werden, da auf diese bei einem JOIN gefiltert wird.
  • Beinhaltet die Spalte nur wenige verschiedene Werte (man redet dann auch von geringer Selektivität), spricht dies eher gegen einen Index darauf. Dies gilt insbesondere für Spalten, die den Datentyp Bit verwenden.
  • Wird die Tabelle meist gelesen und nur selten geändert, sind mehr Indizes vertretbar.
  • Wird die Tabelle primär geschrieben (z. B. eine Protokolltabelle) und nur selten gelesen, sollte man die Anzahl der Indizes möglichst gering halten.

Kombinierte Indizes

Ein Weg, die Anzahl der zu verwaltenden Indizes gering zu halten, aber auch um die Indizes so effektiv wie möglich nutzbar zu machen, ist die Verwendung von kombinierten Indizes, die über mehrere Spalten definiert sind. Wenn in einer Personentabelle beispielsweise ein separater Index für die Spalten Vorname und Nachname existiert, wird die folgende Abfrage kaum beide Indizes nutzen können:

SELECT * FROM Person.Person

WHERE FirstName = ‚John‘ AND LastName = ‚Wood‘

Stattdessen wird der SQL Server automatisch den Index nutzen, von dem die geringere zu lesende Seitenzahl erwartet wird (den Index auf der Spalte mit der höheren Selektivität). Da es aber sowohl zum gesuchten Vornamen als auch zum Nachnamen mehrere Einträge geben kann, wäre es noch effektiver, einen Index zu haben, der beide Spalten beinhaltet, also einen kombinierten Index, der wie folgt erstellt werden kann:

CREATE NONCLUSTERED INDEX IX_Person_LastName_FirstName

ON Person.Person (LastName, FirstName)

Während es sich bei der Bezeichnung des Indexes (IX_Person_LastName_FirstName) nur um eine gängige Namenskonvention handelt, ist die Reihenfolge der Spalten in der Indexdefinition von entscheidender Bedeutung. Dadurch, dass der Nachname an erster Stelle steht, kann der Index auch dann effektiv genutzt werden, wenn eine Abfrage lediglich nach dem Nachnamen sucht (da dieser das primäre Sortierkriterium für den Index bildet). Wird stattdessen lediglich nach einem Vornamen gesucht, kann der Index zwar auch genutzt werden, allerdings nicht über den effektiven Zugriff über den binären Indexbaum (Index Seek). Stattdessen muss der gesamte Index gelesen werden (Index Scan), um alle möglichen Kombinationen zu finden, die den gesuchten Vornamen enthalten (was in den meisten Fällen immer noch effektiver ist, als die gesamte Tabelle ohne Index zu lesen).

Da eine Suche nur nach dem Vornamen in der Praxis sicherlich deutlich seltener vorkommen wird als eine über den Nachnamen, bietet sich die oben gewählte Spaltenreihenfolge an und hat den Nebeneffekt, dass man damit auf die separaten Indizes auf die Spalten Vorname und Nachname verzichten kann.

Abdeckende Indizes und Include-Spalten

Bei der Erläuterung der nicht gruppierten Indizes wurde darauf hingewiesen, dass auf der Blattebene des binären Indexbaums Verweise auf die eigentlichen Datensätze stehen (entweder in Form des gruppierten Indexschlüssels oder als mehrteilige Adresse aus Dateinummer, Speicherseite und Zeilennummer). Im Idealfall sind jedoch alle abgefragten Spalten im Index selbst enthalten, sodass es gar nicht notwendig wird, diesem Verweis noch zu folgen. Wenn bei existierendem kombinierten Index auf die Spalten LastName und FirstName die folgende Abfrage ausgeführt wird:

 
SELECT FirstName FROM Person.Person

WHERE LastName = 'Wood'

… dann kann der Index anhand der LastName-Spalte durchsucht werden, beinhaltet aber auch schon den abgefragten LastName, sodass der normalerweise notwendige Look-up auf die komplette Datenzeile entfallen kann. Man spricht dann von einem abdeckenden Index (in Bezug auf die Abfrage), da dieser alle Spalten für die Abfrage abdeckt.

Um möglichst oft den Vorteil eines abdeckenden Indexes zu nutzen, ohne den Index selbst zu häufig umsortieren zu müssen, kann man Spalten als so genannte Include-Spalten in den Index integrieren. Das bedeutet, dass die Werte dieser Spalten auf der Blattebene des Indexbaums enthalten sind, diese aber nicht für die Sortierung des Indexes berücksichtigt werden (daher reicht es auch aus, wenn die Spalten nur auf der Blattebene und nicht auf den Knoten darüber existieren). Für die zuletzt dargestellte Abfrage würde also auch folgender Index ausreichen:

 
CREATE NONCLUSTERED INDEX IX_Person_LastName

ON Person.Person (LastName) INCLUDE (FirstName)

Dabei lassen sich auch mehrere Include-Spalten definieren, deren Reihenfolge dann keine Rolle spielt, da diese für die Sortierung ja nicht relevant sind. Würde man alle Spalten in den Index mit aufnehmen (entweder für die Sortierung oder als Include-Spalten), so hätte man dadurch einen gruppierten Index simuliert, allerdings mit dem Nachteil, dass dann noch der Platz für den Verweis auf die Datenzeile selbst belegt wird, was in dieser Variante unnötig ist, da diese ja auch komplett im Index enthalten ist. Dies ist also definitiv nicht zu empfehlen, zumal Indizes effektiver genutzt werden können, je kleiner sie sind.

Gefilterte Indizes

Seit SQL Server 2008 existiert mit den gefilterten Indizes eine weitere Möglichkeit, den Index möglichst kompakt zu halten und damit die Anzahl der zu lesenden Speicherseiten zu minimieren. Dabei wird ein Index um eine WHERE-Klausel ergänzt, sodass der Index nur für die Zeilen erstellt werden muss, die der angegebenen Bedingung entsprechen. Daraus ergibt sich konsequenterweise die Einschränkung, dass Filterbedingungen nur in Kombination mit nicht gruppierten Indizes verwendet werden können.

Ein typischer Anwendungsfall für gefilterte Indizes sind Tabellen mit Spalten, die bei einem großen Teil der Zeilen nicht gefüllt sind:

 
CREATE NONCLUSTERED INDEX IX_Person_MiddleName

ON Person.Person (MiddleName)

WHERE MiddleName IS NOT NULL

Indizierte Sichten

Bereits seit der Version 2005 bietet SQL Server auch die Möglichkeit, Indizes auf Basis von Sichten zu erstellen. Dadurch werden die von der Sicht abgefragten Daten als Kopie redundant gespeichert, allerdings sortiert nach den Indexkriterien. Dieses Konzept wird daher oft auch als materialisierte Sicht bezeichnet (insbesondere im Oracle-Umfeld ist diese Bezeichnung verbreitet). Der wesentliche Vorteil einer solchen indizierten Sicht liegt darin, dass alle abzufragenden Daten bereits fertig verknüpft und in der richtigen Sortierreihenfolge vorliegen. Somit muss die Abfrage lediglich auf die indizierte Sicht zugreifen, ohne die Daten aus mehreren Tabellen erst über JOIN-Bedingungen zusammenzuführen. Auch zusätzliche Row- bzw. Key-Look-ups entfallen, da – sofern die Sicht entsprechend angelegt ist – alle abzufragenden Spalten in der Sicht und damit auch in dem Index darauf enthalten sind.

Um indizierte Sichten verwenden zu können, sind allerdings auch ein paar Einschränkungen zu beachten:

  • Die Sicht selbst muss mit der Option WITH SCHEMABINDING erstellt sein, die verhindert, dass die Datenstruktur der in der Sicht verwendeten Spalten geändert wird.
  • Der Index auf die Sicht muss als UNIQUE CLUSTERED INDEX angelegt werden.
  • Der Index darf nicht gefiltert sein, was aber keine wirkliche Einschränkung bedeutet, da man ein Filterkriterium problemlos in der vom Index verwendeten Sicht unterbringen kann.
 
Listing 2: Erstellung einer indizierten Sicht

CREATE VIEW Person.vwPersonPhone

WITH SCHEMABINDING

AS SELECT pers.FirstName, pers.LastName, phone.PhoneNumber,

ptype.Name AS PhoneType

FROM Person.Person AS pers

INNER JOIN Person.PersonPhone AS phone

ON pers.BusinessEntityID = phone.BusinessEntityID

INNER JOIN Person.PhoneNumberType AS ptype

ON phone.PhoneNumberTypeID = ptype.PhoneNumberTypeID

 

CREATE UNIQUE CLUSTERED INDEX IX_PersonPhone

ON Person.vwPersonPhone (PhoneType, LastName, FirstName, PhoneNumber)

Listing 2 zeigt, wie eine indizierte Sicht in zwei Schritten erstellt werden kann. Nach der Erstellung der Sicht mit Zusatz SCHEMABINDING erfolgt die Erzeugung des UNIQUE CLUSTERED INDEX auf diese Sicht. Wenn man nun eine einfache Abfrage auf der Sicht durchführt, kann man über den Ausführungsplan feststellen, dass lediglich der Index selbst gelesen wurde und nicht die dahinterliegenden Tabellen:

 

SELECT * FROM Person.vwPersonPhone

WHERE PhoneType = 'Work'

Ein interessanter Nebeneffekt von indizierten Sichten ist, dass diese selbst dann verwendet werden können, wenn nicht explizit die Sicht, sondern die dahinterliegenden Tabellen angesprochen werden. Dies lässt sich leicht überprüfen, wenn man den Ausführungsplan der folgenden Abfrage betrachtet:

 

SELECT pers.FirstName, pers.LastName, phone.PhoneNumber, ptype.Name AS PhoneType

FROM Person.Person AS pers

INNER JOIN Person.PersonPhone AS phone

ON pers.BusinessEntityID = phone.BusinessEntityID

INNER JOIN Person.PhoneNumberType AS ptype

ON phone.PhoneNumberTypeID = ptype.PhoneNumberTypeID

Hier erkennt SQL Server bei der Erstellung des Ausführungsplans automatisch, dass eine passende indizierte Sicht existiert, über die der Zugriff schneller erfolgt als über die verwendeten Tabellen und deren Indizes.

Fazit

In der ersten Folge dieser Artikelserie wurden die Grundlagen geschaffen und ein Überblick über die bereits seit längerer Zeit verfügbaren „klassischen“ Indexvarianten gegeben. In der nächsten Folge dieser Serie wird auf die Wartung von Indizes sowie ein paar erweiterte Optionen (z. B. Komprimierung von Indizes) eingegangen.

Links & Literatur

[1] Panther, Robert: „SQL-Abfragen optimieren“, entwickler.press, Juni 2014, ISBN: 978-3868021233

[2] Panther, Robert: „SQL Server Performance Ratgeber“, entwickler.press, Februar 2010, ISBN: 978-3868020304

[3] Fritchey, Grant: „SQL Server Execution Plans“, Simple Talk Publishing, Oktober 2012, ISBN: 978-smartblock id=1906434939

Windows Developer

Windows DeveloperDieser Artikel ist im Windows Developer erschienen. Windows Developer informiert umfassend und herstellerneutral über neue Trends und Möglichkeiten der Software- und Systementwicklung rund um Microsoft-Technologien.

Natürlich können Sie den Windows Developer über den entwickler.kiosk auch digital im Browser oder auf Ihren Android- und iOS-Devices lesen. In unserem Shop ist der Windows Developer ferner im Abonnement oder als Einzelheft erhältlich.

Unsere Redaktion empfiehlt:

Relevante Beiträge

Meinungen zu diesem Beitrag

X
- Gib Deinen Standort ein -
- or -