Zusammengesetzte Schlüssel

Obwohl die Datenbank den Primärschlüssel automatisch indiziert, kann man den Index dennoch verfeinern, wenn der Primärschlüssel aus mehreren Spalten besteht. Dafür wird ein zusammengesetzter Index verwendet. Das ist ein Index, der alle Spalten des Primärschlüssels enthält. Die Reihenfolge der Spalten im Index hat jedoch einen großen Einfluss darauf, welche Abfragen ihn nutzen können. Die Spalten-Reihenfolge muss also sehr sorgfältig gewählt werden.

Zur Demonstration stellen wir uns eine Betriebsübernahme vor. Dabei werden die Mitarbeiter anderer Unternehmen in die EMPLOYEES-Tabelle übernommen, sodass diese insgesamt auf das Zehnfache anwächst. Das einzige Problem ist, dass die EMPLOYEE_ID nicht mehr eindeutig ist. Daher erweitern wir den Primärschlüssel um eine weitere Spalte: die Zweigstellen-Nummer. Der neue Primärschlüssel besteht also aus zwei Spalten: der EMPLOYEE_ID wie gehabt, und der SUBSIDIARY_ID, um die Eindeutigkeit wiederherzustellen.

Der Index für den Primärschlüssel ist also wie folgt definiert:

CREATE UNIQUE INDEX employees_pk
    ON employees (employee_id, subsidiary_id)

Eine Abfrage nach einem bestimmten Mitarbeiter muss den vollständigen Primärschlüssel verwenden, also auch die SUBSIDIARY_ID:

SELECT first_name, last_name
  FROM employees
 WHERE employee_id   = 123
   AND subsidiary_id = 30

Wenn der Zugriff mit dem vollständigen Primärschlüssel erfolgt, kann die Datenbank einen INDEX UNIQUE SCAN durchführen – egal wie viele Spalten der Index hat. Aber was geschieht, wenn die Abfrage nur eine der beiden Spalten verwendet? Wenn zum Beispiel alle Mitarbeiter einer Zweigstelle gesucht werden:

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 20

Der Ausführungsplan zeigt, dass der Index nicht benutzt wird. Stattdessen wird eine TABLE ACCESS FULL-Operation durchgeführt. Dabei liest die Daten­bank die ganze Tabelle und vergleicht jede Zeile mit der where-Klausel. Die Ausführungszeit steigt proportional mit der Tabellengröße. Wenn die Tabelle auf das Zehnfache wächst, dauert der TABLE ACCESS FULL auch zehn Mal so lange. Das Tückische an dieser Operation ist, dass sie in kleinen Entwicklungsumgebungen oft schnell genug ist, in Produktion aber zu Problemen führt.

Die Datenbank verwendet den Index nicht, da man die einzelnen Spalten eines zusammengesetzten Indexes nicht willkürlich verwenden kann. Zum Verständnis betrachten wir den Aufbau eines zusammengesetzten Indexes genauer.

Auch ein zusammengesetzter Index ist ein B-Tree-Index, der die indizierten Daten als sortierte Liste verwaltet. Bei der Sortierung werden die Spalten entsprechend ihrer Position im Index berücksichtigt. Die erste Spalte wird also als vorrangiges Sortierkriterium herangezogen. Die zweite Spalte bestimmt die Reihenfolge nur dann, wenn zwei Einträge denselben Wert in der ersten Spalte haben. Und so fort.

Die Sortierung eines zweispaltigen Indexes erfolgt also wie bei einem Telefonbuch, das zuerst nach Nachname, dann nach Vorname sortiert ist. Daraus folgt, dass ein zweispaltiger Index nicht zur Suche auf der zweiten Spalte alleine verwendet werden kann. Das wäre, als würde man in einem Telefonbuch mit einem Vornamen suchen.

Abbildung 2.1 Zusammengesetzter Index

Der Indexausschnitt in Abbildung 2.1 zeigt, dass die Einträge mit SUBSIDIARY_ID = 20 nicht an einer zentralen Stelle liegen. Es ist auch zu sehen, dass SUBSIDIARY_ID = 20 nicht im Indexbaum aufscheint, obwohl entsprechende Einträge in den Blattknoten vorhanden sind.. Der Indexbaum ist für diese Abfrage völlig nutzlos.

SELECT <INDEX SPALTENLISTE> 
  FROM <TABELLE>  
 ORDER BY <INDEX SPALTENLISTE>
 FETCH FIRST 100 ROWS ONLY;

Um die Abfrage dennoch zu beschleunigen, kann man natürlich einen zweiten Index auf SUBSIDIARY_ID anlegen. Es gibt jedoch eine bessere Alternative – zumindest wenn man davon ausgeht, dass die Suche nach einer EMPLOYEE_ID alleine keinen Sinn ergibt.

Dafür macht man sich zunutze, dass die erste Spalte eines Indexes immer für Suchen herangezogen werden kann. In einem Telefonbuch kann man ja auch ohne einen Vornamen zu kennen, nach einem Nachnamen suchen. Der Trick besteht also darin, die Spaltenreihenfolge des Indexes umzudrehen, sodass SUBSIDIARY_ID an erster Position steht:

CREATE UNIQUE INDEX EMPLOYEES_PK 
    ON EMPLOYEES (SUBSIDIARY_ID, EMPLOYEE_ID)

Beide Spalten zusammen sind nach wie vor eindeutig, sodass der Zugriff über den vollständigen Primärschlüssel weiterhin als INDEX UNIQUE SCAN erfolgt. Die Index-Sortierung hat sich jedoch grundlegend geändert. Der neue Index ist vorrangig nach der SUBSIDIARY_ID sortiert. Das heißt, dass alle Mitarbeiter einer Zweigstelle unmittelbar hintereinander im Index stehen. Dadurch kann man den Indexbaum auch nutzen, um sie zu finden.

Der Ausführungsplan zeigt, dass der „umgedrehte“ Index benutzt wird. Da die Spalte SUBSIDIARY_ID alleine nicht eindeutig ist, muss die Datenbank die Blattknoten verfolgen, um alle Einträge zu finden. Es findet also ein INDEX RANGE SCAN statt:

Ein zusammengesetzter Index kann benutzt werden, wenn mit den führenden Spalten gesucht wird. Das heißt, ein Index mit drei Spalten kann für Suchen mit der ersten Spalte alleine, mit den ersten beiden oder mit allen drei Spalten gemeinsam verwendet werden.

Obwohl die Lösung mit einem zweiten Index auch sehr gute select-Performance liefert, ist die Variante mit einem Index vorzuziehen. Dadurch wird nicht nur Speicherplatz gespart, sondern auch der Wartungs-Aufwand, den jeder Index nach sich zieht. Je weniger Indizes eine Tabelle hat, desto besser ist die insert-, delete- und update-Performance.

Zur optimalen Indexdefinition muss man also nicht nur wissen, wie ein Index funktioniert, man muss auch wissen, wie die Anwendung auf die Daten zugreift. Konkret heißt das, dass man wissen muss, welche Spaltenkombinationen in der where-Klausel vorkommen.

Für externe Berater kann es daher sehr schwierig sein, einen optimalen Index anzulegen, da die Übersicht über die Zugriffspfade fehlt. Daher wird oft nur eine einzelne SQL-Abfrage berücksichtigt. Den Mehrwert, den ein besser definierter Index für andere Abfragen bringen könnte, wird nicht ausgeschöpft. Datenbankadministratoren sind in einer ähnlichen Situation. Sie kennen die Daten zwar besser, einen umfassenden Überblick über die Zugriffspfade der Applikation haben sie aber auch nicht.

Die einzige Stelle, an der sowohl das Applikationswissen als auch Datenbankwissen vorhanden ist, ist die Entwicklung. Entwickler haben ein Gefühl für die Daten und kennen die Zugriffspfade. Damit können sie ohne großen Aufwand richtig indizieren, um einen optimalen Nutzen zu erzielen.