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 Datenbank 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.
Hinweis in eigener Sache
Ich biete SQL Schulungen, Optimierung und Beratung an. Auch der Kauf meines Buches „SQL Performance Explained“ (ab €9,95) unterstützt meine Arbeit an dieser Webseite.
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.
Wichtig
Ein zusammengesetzter Index ist ein Index über mehrere Spalten.
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.
Tipp
Die Visualisierung eines Indexes hilft zu verstehen, welche Abfragen unterstützt werden. Dazu kann man sich die Indexreihenfolge mit einer Abfrage ansehen (SQL:2008 Syntax, siehe hier für proprietäre Lösungen mit LIMIT
, TOP
oder ROWNUM
):
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.
Wichtig
Die wichtigste Überlegung beim Erstellen eines zusammengesetzten Indexes ist, wie man die Spaltenreihenfolge wählt, damit er möglichst oft benutzt werden kann.
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:
- DB2
Explain Plan ------------------------------------------------------------- ID | Operation | Rows | Cost 1 | RETURN | | 128 2 | FETCH EMPLOYEES | 1195 of 1195 (100.00%) | 128 3 | RIDSCN | 1195 of 1195 (100.00%) | 43 4 | SORT (UNIQUE) | 1195 of 1195 (100.00%) | 43 5 | IXSCAN EMPLOYEES_PK | 1195 of 10000 ( 11.95%) | 43 Predicate Information 2 - SARG (Q1.SUBSIDIARY_ID = +00002.) 5 - START (Q1.SUBSIDIARY_ID = +00002.) STOP (Q1.SUBSIDIARY_ID = +00002.)
Dieser Ausführungsplan sieht etwas komplexer aus, als bei der Nutzung des Indexes vorhin. Die Schlüssel-Operationen sind jedoch noch immer hier: die
IXSCAN
-Operation für den Indexzugriff und dieFETCH
-Operation für den Tabellenzugriff. Dazwischen tauchen jedoch die unerwarteten OperationenSORT
undRIDSCAN
auf: dieSORT
-Operation sortiert die Indexeinträge entsprechend der physischen Speicharaddresse der zugehörigen Zeile in der Heap-Tabelle – sinngemäß entsprechend der ROWID, die bei DB2 RID genannt wird. DieRIDSCN
-Operation lädt dann alle betroffenen Datenseiten aus der Heap-Tabelle, wobei aufeinanderfolgende Blöcke in eine Leseoperation zusammengefasst werden.- MySQL
+----+-----------+------+---------+---------+------+-------+ | id | table | type | key | key_len | rows | Extra | +----+-----------+------+---------+---------+------+-------+ | 1 | employees | ref | PRIMARY | 5 | 123 | | +----+-----------+------+---------+---------+------+-------+
Der MySQL-Zugriffstyp
ref
enspricht einemINDEX RANGE SCAN
der Oracle-Datenbank.- Oracle
--------------------------------------------------------------- |Id |Operation | Name | Rows | Cost | --------------------------------------------------------------- | 0 |SELECT STATEMENT | | 106 | 75 | | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 106 | 75 | |*2 | INDEX RANGE SCAN | EMPLOYEES_PK | 106 | 2 | --------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("SUBSIDIARY_ID"=20)
- PostgreSQL
QUERY PLAN ---------------------------------------------- Bitmap Heap Scan on employees (cost=24.63..1529.17 rows=1080 width=13) Recheck Cond: (subsidiary_id = 2::numeric) -> Bitmap Index Scan on employees_pk (cost=0.00..24.36 rows=1080 width=0) Index Cond: (subsidiary_id = 2::numeric)
PostgreSQL führt hier zwei Operationen durch: einen
Bitmap Index Scan
gefolgt von einemBitmap Heap Scan
. Diese Operationen entsprechen ungefähr demINDEX RANGE SCAN
und demTABLE ACCESS BY INDEX ROWID
. Der Unterschied ist, dass das derBitmap Index Scan
zuerst alle passenden Zeilen aus dem Index holt, diese dann entsprechend ihrer physischen Speicherposition in der Tabelle sortiert und dann erst den Zugriff auf die Tabelle durchführt (Bitmap Heap Scan
).- SQL Server
|--Nested Loops(Inner Join) |--Index Seek(OBJECT:employees_pk, | SEEK:subsidiary_id=20 | ORDERED FORWARD) |--RID Lookup(OBJECT:employees, SEEK:Bmk1000=Bmk1000 LOOKUP ORDERED FORWARD)
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.
Hinweis in eigener Sache
Ich biete SQL Schulungen, Optimierung und Beratung an. Auch der Kauf meines Buches „SQL Performance Explained“ (ab €9,95) unterstützt meine Arbeit an dieser Webseite.
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.