Indizierung in der Praxis

In jedem Lehrbuch zur Datenbankentwicklung erfahren Sie, wie wichtig die Indizierung von Tabellenfeldern ist. Das betrifft jene, die in Vergleichs- und Filterabfragen eingeschlossen werden sollen und außerdem die Felder, welche mehrere Tabellen über Indexfelder über Schlüssel miteinander verknüpfen. Ziel sind möglichst performante Abfragen der Datenbank. Wir untersuchen hier, wie groß der Performancezuwachs in der Praxis tatsächlich ist.

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1702_Indizes.zip.

Wozu Indizierung

In einer relationalen Datenbank gilt die Indizierung von mindestens den Verknüpfungsfeldern der Tabellen als das A und O. Sehen Sie sich dazu etwa das Datenmodell von Kunden und deren Bestellungen in Bild 1 an.

Bild 1: Die vier Tabellen der Kundendatenbank sind alle miteinander über Schlüsselfelder verknüpft

Zu jedem Kunden, identifiziert durch dessen ID, kann es mehrere Bestellungen geben. Das Feld KundeID der Tabelle bezieht sich auf die ID des Kunden. Damit ist die Verbindung hergestellt. Ähnlich verhält es sich mit den Tabellen zu Bestelldetails und Artikeln. Hier stellt jeweils die ID den Primärschlüssel dar, auf den sich die Datensätze einer Detailtabelle über einen Schlüssel xxxID verweisen. Die Bestelldetails machen das gleich zweimal.

Die Indizierung der beteiligten Schlüsselfelder ist immer dann zwingend, wenn Sie eine automatische Lösch- und Aktualisierungsweitergabe erreichen möchten. Access erlaubt die Einstellung dieser Optionen für eine Beziehung nur dann, wenn bereits Indizes auf die Felder gesetzt wurden. Das Löschen eines Kunden zieht dann das Löschen auch aller seiner Bestellungen samt Bestelldetails nach sich.

Doch oft ist Lösch- oder Aktualisierungsweitergabe gar nicht erforderlich oder erwünscht. Auch dann wird aber zu einer Indizierung geraten, weil dies der Performance zugutekommt. Sollen etwa die Bestellungen eines Kunden mit der ID 79 gefunden werden, so muss die Datenbank-Engine alle Datensätze der Tabelle tblBestellungen durchlaufen und die Werte von KundeID mit dieser Zahl vergleichen.

Bei Millionen von Bestelldatensätzen dauert dies recht lange. Ist das Feld KundeID aber indiziert, so zieht Access dessen Index zurate. Dieser erlaubt eine viel schnellere Suche nach dem Wert 79.

Soll gar zu einem Kunden ermittelt werden, welche Artikel er bereits bestellte, so ist die Tabelle tblBestelldetails und deren Feld ArtikelID zu durchsuchen. Da dies nur über den Umweg der Zwischentabelle tblBestellungen geschehen kann, sind nun schon zwei Tabellen verschachtelt komplett zu durchsuchen. Das potenziert die Suchdauer.

Der zweite Anwendungsfall für einen Index ist die unmittelbare Suche nach einem Feldinhalt. Möchten Sie etwa einen Kunden über dessen Namen finden, so muss Access wieder alle Datensätze scannen, um den oder die gewünschten zu erkennen. Sind die Namensfelder mit Indizes versehen, so verwendet die Engine diese zur Suche, was abermals schneller abläuft.

Soweit ist dies alles wahrscheinlich kein Fremdland für Sie!

Was ist ein Index

Nehmen wir an, Sie hätten ein E-Book, wie das vorliegende, in dem Sie ein bestimmtes Thema finden möchten. Sie könnten nun eine Volltextsuche anstoßen, um nach einem Begriff zu suchen, und das dauert einige Zeit. Einfacher ist es da, sich eines Index im hinteren Teil des Buchs zu bedienen. Dort stehen zu jedem Begriff mehrere Seitenzahlen, über die Sie wahrscheinlich schneller zum Thema gelangen.

Ähnlich verhält es sich mit den Indizes einer Datenbank. Mache System weisen tatsächlich auch physisch einen Index auf. Zu einer DBase-Datenbank finden Sie etwa in der Regel neben der eigentlichen Datenbank (dbf) auch eine Datei mit der Endung idx, die lediglich die Indizes zu den Tabellen enthält. Intern speichert auch die Access Database Engine die Indizes in gesonderten Speicherbereichen, nur dass dies in der Oberfläche von Access nicht offenkundig ist. Indizieren Sie ein Tabellenfeld, so zeigt sich dies ja lediglich in der Entwurfsansicht am Eigenschaftenfeld Indiziert oder im Indizes-Dialog (Ribbon Entwurf | Indizes), wie in Bild 2. Intern hat Access aber Indextabellen angelegt.

Bild 2: Der Indizes-Dialog zur Tabelle tblKunden im Entwurf

Um zur Analogie eines Buches zurückzukehren: Suchen Sie etwa nach Löschweitergabe, so suchen Sie erstens im Inhaltsverzeichnis nach der ersten Seite des Index. Dort finden Sie die Begriffe alphanumerisch sortiert vor. Sie blättern zur Seite mit dem L als Überschrift und finden in der Liste den Begriff Löschweitergabe mit drei angefügten Seitenzahlen. Sie inspizieren diese Seiten nacheinander, um die gewünschten Informationen zu erhalten. So ähnlich arbeitet auch ein Index unter Access.

Allerdings sind diese internen Indextabellen wesentlich komplexer und hochoptimiert, damit sich die Zahl der Sprünge zu Einträgen und Untereinträgen so gering hält, wie möglich. Die hier verwendeten Algorithmen sind eine Wissenschaft für sich und tragen Namen, wie B-Tree. Ein mögliches vereinfachtes Modell bei aufsteigender Sortierung für einen Index:

K ->143
L -> 188
M -> 296
188 -> n -> 2354
188 -> o -> 2389
2389 -> d -> 7843
2389 -> e -> 7911

An der Speicherstelle 7911 sind wir immerhin schon bei Loe angekommen, den ersten drei Buchstaben von Löschweitergabe. Das Spiel setzt sich auf ähnliche Weise fort, bis der Begriff vollständig ermittelt ist. Dort stehen dann ein oder mehrere Zeiger auf den Speicherplatz der Datensätze innerhalb der eigentlichen Tabelle.

Für den Begriff braucht es deshalb vielleicht nur 15 Sprünge zwischen Speicherstellen, bis er im Index ermittelt ist. Dem entgegen stünde ein Durchlaufen aller Tausender Datensätze einer Tabelle, wenn auf einen Index für das Feld verzichtet würde.

Es ist also sehr plausibel, dass ein Index die Performance gewaltig erhöht. Zumindest gilt das für das Lesen von Tabellen. Bei Anfügen oder Ändern eines Datensatzes hingegen kommt es zu einem Nachteil: Nicht nur die Daten in der Tabelle selbst müssen geändert werden, sondern auch der Index! Sein ganzer Aufbau muss dem neuen Tabelleninhalt angepasst werden, damit die Suche effizient abläuft.

Imgrunde müssten dazu sämtliche Speicherstellenverweise neu angeordnet werden, damit die Buchstaben oder Zahlen wieder korrekt sortiert in ihm vorliegen. In der Realität passiert das so aber nicht, sondern nur in Teilen, weil es die Performance stark senken würde. Zu einer kompletten Neuordnung der Indizes kommt es erst dann, wenn Sie die Funktion Komprimieren und Reparieren für die Datenbank anstoßen!

Ein Test mit umfangreicher Tabelle

Um zu überprüfen, wie sich die Indizierung in der Praxis auswirkt, verwenden wir erst einmal eine Tabelle mit sehr vielen Datensätzen. Die Wahl fiel auf den OpenThesaurus, eine Tabelle, die synonyme Begriffe enthält. Die Tabelle der Beispieldatenbank kommt aus dem gleichnamigen Open Source-Projekt und enthält etwa 125.000 Datensätze. Bild 3 zeigt einen Ausschnitt der Tabelle.

Bild 3: Die Tabelle OpenThesaurus in der Datenblattansicht

In Bild 4 finden Sie den Entwurf der Tabelle. Die Begriffe stehen in der Spalte Ausdruck. Jene Begriffe, die synonym sein sollen, haben alle die gleiche IDGroup. Man muss also nur nach einem Begriff suchen, die IDGroup desselben auslesen, um über diese und eine Abfrage alle anderen Begriffe zu erhalten. Jeder Datensatz enthält darüber hinaus eine eindeutige ID.

Bild 4: Die Tabelle OpenThesaurus in der Entwurfsansicht

Es ist naheliegend, dass eine Indizierung der Felder Ausdruck (für die Suche nach einem Begriff) und IDGroup (für das Auffinden synonymer Begriffe) hier Pflicht ist. Wir haben dies für die Tabelle so gemacht und jeweils in die Eigenschaft Indiziert im Entwurf die Auswahl Ja (Duplikate möglich) eingestellt.

Um die Performance mit einer Tabelle ohne Indizes zu vergleichen, enthält die Beispieldatenbank zusätzlich die gleiche Tabelle ohne Indizierung, die den Namen OpenThesaurus2 trägt. Und eine weitere nennt sich OpenThesaurus3. Sie leitet sich von der zweiten ab, hat aber für das Feld Ausdruck keine Unicode-Kompression eingestellt. Access stellt diese von Haus aus auf Ja ein. Alle Zeichen von Textfeldern werden unter Access ja als Unicode abgespeichert, um auch Sonderzeichen und ausländische Schriftzeichen abbilden zu können.

So ein Unicode-Zeichen nimmt zwei Bytes ein, was den Speicherbedarf gegenüber ANSI (ein Byte) verdoppelt. Aus diesem Grund wurde die Kompression dieser Doppelzeichen eingeführt, die den physischen Speicherbedarf reduziert. Man sollte nun annehmen, dass die Komprimierung und Dekomprimierung der Texte zusätzliche Rechenzeit benötigt und die Performance senken könnte. Auch das überprüfen wir im Folgenden. Eine Testprozedur im Modul mdlTest hat nun prinzipiell den Code aus Listing 1. Die Zeit wird über die Variable T gemessen. Sie kommt aus der VBA-Funktion Timer, die die aktuelle Zeit in Sekunden als Single-Wert mit drei Nachkommastellen zurückgibt. Das Recordset wird auf die Tabelle OpenThesaurus geöffnet und als Filter bestimmt, dass nur die Datensätze mit dem Ausdruck Akronym berücksichtigt werden sollen. In einer Schleife wird das Ergebnis durchlaufen und jeweils in der Variablen V abgespeichert. Nach dem Schließen des Recordsets gibt die letzte Zeile der Prozedur die Differenz der aktuellen Zeit zu der eingangs in T gespeicherten im VBA-Direktfenster aus.

Sub PerformanceRead()
     Dim T As Single
     InitEngine
     T = VBA.Timer
     Set rs = CurrentDb.OpenRecordset("SELECT * From        OpenThesaurus WHERE Ausdruck='Akronym'", dbOpenDynaset)
     Do While Not rs.EOF
         V = rs!Ausdruck.Value
         rs.MoveNext
     Loop
     rs.Close
     Debug.Print  VBA.Timer - T
End Sub

Listing 1: Performance-Test für den OpenThesaurus

Benutzername oder E-Mail-Adresse: