Was ist ein Index?

 

Ganz vereinfacht ausgedrückt, handelt es sich bei einem Index um eine Sortierung der Daten, also um eine Information, an welcher Position sich bestimmte Einträge befinden. Es gibt prinzipiell zwei Möglichkeiten, eine solche Information zu erzeugen:

 

 

Die erste Art Index ist besonders effizient, kann aber logischerweise nur für ein Kriterium verwendet werden.  Diese Art der Indizierung wird als Clustered Index bezeichnet. (In Wahrheit liegen die Dinge etwas komplizierter, es kommt beim Clustering auf die Zuordnung der Daten zu sogenannten Speicherseiten an, auf denen sie Gruppen (Cluster) bilden, aber die tatsächliche physische Reihenfolge der Daten spielt hier eine entscheidende Rolle, um zu verhindern, daß logisch benachbarte Daten auf viele Speicherseiten verteilt sind, die dann vom System alle angefordert werden müßten.)
Der zweite Indextyp, der Non-Clustered Index, kann beliebig oft verwendet werden, hierbei ist aber nicht sichergestellt – und bei mehreren Kriterien auch logisch ausgeschlossen –, daß benachbarte Daten sicher auf gleichen oder benachbarten Speicherseiten stehen.

 

 

Ein Code-Beispiel zur Erläuterung mit VBA-Arrays:

 

 

Wir haben hier natürlich nur an der Oberfläche gekratzt. Die tatsächlich eingesetzten Indextypen weisen in der Regel eine effizientere Baumstruktur auf:

 

 

Da man als Datenbankentwickler hierauf aber keinen Einfluß hat, soll das Thema nicht weiter vertieft werden.

 

Was nützt ein Index?

 

Alle lesenden Zugriffe oder Berechnungen, die eine definierte Reihenfolge von Daten ausnutzen können, profitieren von einem Index, also Sortierungen, Filterungen, Minimums- und Maximums-Bestimmung u. ä.

 

Was schadet ein Index?

 

Schreibende Zugriffe werden durch Indizes verlangsamt, da die Indizes bei Eingabe neuer und Änderung vorhandener Daten mit dem neuen Bestand aktualisiert werden müssen.
Es ist also keine gute Idee, möglichst viele Felder zu indizieren. Im Hinblick darauf, daß bei den meisten Datenbanken die Lesezugriffe einen weitaus größeren Anteil am Datenbankgeschehen haben als die Schreibzugriffe, sollten aber die sinnvollen Indizierungen auf jeden Fall umgesetzt werden.

 

Welche Felder indizieren?

 

Welche Felder soll man nun indizieren? Grundsätzlich profitieren alle Vorgänge, bei denen es irgendwie auf die Reihenfolge der Daten ankommt, von einem Index. Es sind also alle

 

 

zu indizieren. Auch Felder, die in einem SELECT DISTINCT vorkommen, profitieren von einem Index.


Ein Index ist um so effizienter, je weniger Wertwiederholungen im indizierten Feld vorkommen. Ein Index auf Firmenname bringt mehr als einer auf Anrede, da Firmennamen sich kaum wiederholen, also auf ganz wenige Datensätze eingeschränkt wird, während die Information „Herr“ von einer Million Datensätzen immer noch circa 500.000 übrigläßt. Das heißt nicht, daß ein Index auf solchen Feldern nichts bringt – auf einem stärker distinktiven Feld bringt er aber unter Umständen erheblich mehr.

 

Mehrfelderindizes richtig planen

 

Ein Index über mehrere Felder unterstützt nicht nur eine Sortierung nach der Kombination aller beteiligten Felder, sondern auch von Teilmengen.


Angenommen, ein Index geht in dieser Reihenfolge über 4 Felder {A, B, C, D}. Von diesem Index profitieren auch Sortierungen nach den Teilmengen {A, B, C}, {A, B}, {A}. Wenn in diesen Reihenfolgen sortiert werden soll, ist ein zusätzlicher Index unnötig. Sortierungen nach {B, C, D}, {C, D}, {B, C}, {B}, {C}, {D} oder {B, A} würden hingegen nicht profitieren und bräuchten gegebenenfalls einen eigenen Index.
Die Regel dürfte klar sein: Feldkombinationen, die einen Mehrfelderindex ausnutzen können, sind alle, die ausgehend vom Indexkopf Folgefelder ohne Unterbrechung beinhalten.

 

Überlappende Indizes

 

Es kann sinnvoll sein, in solchen Fällen überlappende Mehrfelderindizes anzulegen. Um die sechs Sortierungen {A, B, C, D}, {A, B, C}, {A, B}, {A}, {C,B}, {C} zu unterstützen, wären zwei Indizes erforderlich: {A, B, C, D} und {C,B}.
Achtung! Man verwechsle die Feldreihenfolge in einem Mehrfelderindex nicht mit der Sortierreihenfolge (aufsteigend/absteigend) in einem Feld.

 

Clustered Index in Access

 

Im Gegensatz zum SQL-Server läßt sich in einer Access-Tabelle der Clustered Index, also die physische Sortierung nach Speicherseiten („gruppenbildender Index“, grob vereinfacht: die tatsächliche Datensatzreihenfolge), nicht einfach festlegen. Access benutzt vielmehr grundsätzlich den Primärschlüssel als Clustered Index. Die Clustered-Eigenschaft des Index-Objektes hilft uns hier leider nicht weiter.


Im Hinblick auf die allseits beliebten AutoWert-Felder liegt hier einiges Potential in der Regel brach. Die dadurch optimal unterstützte Datensatzreihenfolge ist die Eingabereihenfolge, die fast immer uninteressant ist.


Da andererseits AutoWerte als unsemantische (referentielle) Selektoren sehr fehlerrobust sind und als Long-Integer-Zahlen sehr schnell verarbeitet werden, möchte man aber ungern darauf verzichten, Beziehungen über sie herzustellen.


Freundlicherweise erlaubt sowohl die relationale Theorie im allgemeinen wie auch Access im besonderen, Beziehungen über jeden Kandidatschlüssel herzustellen. Die folgenden Beispiele zeigen darauf basierend, wie man in Access sowohl zu einem sinnvollen Clustered Index als auch zu einer Beziehung über AutoWerte kommt.

 

Beispiele zur Indizierung

 

Beispiel Stammdaten
Hier z. B. Personentabelle

 

 

 

Indizierung 1

 

 

Ideal für Sortierung nach {Nachname}, {Nachname, Vorname}
Sortierung nach Vorname alleine profitiert kaum
Beziehungen werden über den Parallelschlüssel ReferenceKey/idPrs hergestellt.

 

Indizierung 2

 

 

Ideal für Sortierung nach (Nachname, Vorname), aber auch nur oder zuerst Vorname
Sortierung nach Vorname profitiert vom tabellierten (non-clustered) Index OrderKey1

 

Beispiel inhaltliche/benutzerdefinierte Sortierung
Hier z. B. Katalog-/Nachschlagetabelle, Kategorien o. ä.

 

 

Indizierung

 

 

Ideal für Sortierung nach (irKat), also benutzerdefinierte/inhaltliche Reihenfolge
Warum zusätzlich Referenzschlüssel idKat?
Bei Reihenfolgenänderungen/Einfügen müssen keine Fremdschlüssel angepaßt werden – der große Vorteil des unsemantischen Schlüssels.

 

Beispiel Bewegungsdaten
Hier: Bestellungen

 

 

Verwendung


idBest: Primärschlüssel, Referenzfeld,
dtBestDatum: Nachsortierfeld, Kriterienfeld


Indizierung


PrimaryKey & ReferenceKey (Unique, 1 Feld)
idBest ASC


Kein Index
dtBestDatum, wenn informell *


OrderKey1(Ambique, ASC(!))
dtBestDatum, wenn relevant *


Auch wenn man absteigend wird sortieren wollen, ist trotzdem ein aufsteigender Index anzulegen. Absteigende Indizes werden von Access nicht richtig genutzt.


* Daten sind informell, wenn sie nur der Information des Benutzers dienen; sie sind relevant, wenn sie vom Db-System verarbeitet werden, also zum Sortieren, als Kriterien, zum Verknüpfen etc. benutzt werden.

 

 

Indexbeschränkungen

 

 

Memo-Felder:

 

In Memo-Feldern sind nur die ersten 255 Zeichen indizierbar. Da Memo-Felder auch sonst einige Tücken haben und ihre Verwendung in 90% aller Fälle, spätestens aber wenn sie mit dem Wunsch nach einer Textsuche im Memo gepaart wird, ein Indikator für Entwurfs­fehler im DB-Design ist, sollte man sich bei vermeintlicher Not­wendigkeit für Memo-Felder dreimal fragen, ob das sein muß. Nur wenn die Antwort dreimal Ja lautet, sollte man es einsetzen.

 

Anzahl der Indizes:

 

In einer Tabelle können maximal 32 Indizes verwaltet werden. Die unsichtbaren automatischen Fremdschlüsselindizes (siehe übernächster Punkt) zählen hierbei mit.
Bis zum Fanatismus überzeugten Normalisierungsanhängern geht hier das Herz auf: Wer seine Datenbanken aus Hunderten Tabellen mit je nur einer Handvoll Feldern strickt, wird mit dieser Beschränkung keine Konflikte bekommen.

 

Anzahl der Felder

 

Ein Index kann über maximal 10 Felder gehen. Im Gegensatz zur 32-Indizes-Grenze ist das recht großzügig bemessen. Für einen notwendigen Mehrfelderindex über mehr als 10 Felder werden sich kaum sinnvolle Beispiele konstruieren lassen.

 

Automatischer Fremdschlüsselindex

 

Das Fremdschlüsselfeld in einer Beziehung (n-Seite) muß unbedingt indiziert sein. Diese Indizierung ist so wichtig, daß wir sie nicht setzen. Klingt verrückt? Nun, aufgrund der Bedeutung dieses Index setzt Access ihn beim Anlegen einer Beziehung selbst – ohne Aufhebens und unsichtbar. Ein bereits bestehender Index auf diesem Feld wird hierbei nicht genutzt. Der Index wird auch in der Indextabelle (Tabellenentwurf, Index) nicht angezeigt, er existiert aber. Mit der Indexes-Auflistung von DAO.TableDef läßt er sich anzeigen, das ist aber das einzige Indiz für seine Existenz.

 

 

Der sonst unsichtbare Fremdschlüsselindex ist daran erkennbar, daß sein Name aus den Namen der beteiligten Tabellen besteht (Tabelle1Tabelle2) und seine Fields-Eigenschaft den Namen des Fremdschlüsselfeldes enthält.
Wenn wir das Fremdschlüsselfeld selbst indizieren, liegen auf diesem Feld überflüssigerweise zwei gleichwertige Indizes. Das bringt keinen Gewinn, aber Verluste durch den entstehenden doppelten Verwaltungsaufwand und reduziert unsinnigerweise die mit 32 ohnehin nicht sehr üppig bemessene Indexobergrenze.

 

Sonstige Join-Felder

 

Wenn man plant, Joins zu verwenden, die nicht über Fremdschlüsselfelder, also Felder, die in Beziehungen vorkommen, gehen – das ist zwar eher ungewöhnlich, aber keineswegs ausgeschlossen –, dann muß man ein solches Join-Feld natürlich selbst indizieren.

 

Aufsteigend indizieren

 

Theoretisch ist ein absteigender Index ebenso gut wie ein aufsteigender. Leider hat Access die Unart, absteigende Indizes häufig nicht zu benutzen, zum Beispiel in Kriterien, was sich mit dem JetShowPlan nachweisen läßt.
Absteigende Indizes werden allerdings bei einer absteigenden Sortierung benutzt. Aber: Mit einem aufsteigenden Index wird eine absteigende Sortierung genauso schnell vorgenommen!
Deswegen soll man alle Indizes aufsteigend anlegen, auch wenn man später absteigend nach diesem Feld sortieren will.

 

Zusammenfassung

 

Eine vernünftige Indexplanung und Durchführung ist Voraussetzung für alle folgenden Tips zur Abfrageoptimierung. Wer nicht alle erforderlichen Felder sachgerecht indiziert hat, braucht sich über Performance keine weiteren Gedanken zu machen, da dann sowieso Hopfen und Malz verloren ist.