Es wird oft empfohlen, Indizes auf einem Fremdschlüssel der Child-Tabelle zu erstellen. Welche Vorteile bringt ein solches Verfahren? Ist es empfehlenswert, Indizes auf allen Fremdschlüsseln zu erstellen? In diesem Artikel möchte ich diese Fragen beantworten.

Ein Fremdschlüssel ist eine Einschränkung, welche die Beziehung zwischen zwei Tabellen definiert und die Integrität der Datenbank sicherstellt. Er wird aus Spalten der untergeordneten Tabelle zusammengesetzt und verweist auf analoge Spalten der Parent-Tabelle. Im Vergleich zu einem Primärschlüssel kann ein Fremdschlüssel Duplikate enthalten, deswegen setzt der SQL Server keinen automatischen Index für Fremdschlüssel – anders als bei Primärschlüsseln.

Der Benutzer muss selbst entscheiden, ob er einen Index für Fremdschlüssel setzen möchte oder nicht. Das Erstellen eines Indexes wird aber empfohlen, da es die folgenden Vorteile mitbringt:

• Ein Index auf einem Fremdschlüssel macht DELETE- und UPDATE-Operationen im Parent deutlich effizienter. Bevor der Parent-Datensatz gelöscht wird, muss der SQL Server die referenzierenden Datensätze in der Child-Tabelle ermitteln. Es gibt zwei Szenarien, wie die referenzierenden Datensätze behandelt werden:
  • 1. Wenn ein Fremdschlüssel mit NO ACTION definiert wird, dann dürfen die Parent-Zeilen nicht gelöscht werden, solange referenzierende Child-Datensätze vorliegen.
  • 2. Bei CASCADE-Option werden die referenzierenden Child-Datensätze entweder gelöscht (DELETE) oder geändert (SET NULL/DEFAULT).
• Wenn kein Index auf dem Fremdschlüssel in der Child-Tabelle angelegt ist, muss in diesen Fällen die gesamte Child-Tabelle gescannt werden, um die untergeordneten Zeilen zu finden.
• Eine SELECT-Abfrage profitiert sehr stark von einem Index auf Fremdschlüsselspalte der Child-Tabelle, wenn die Fremdschlüsselspalten in einer WHERE- oder JOIN-Bedingung verwendet werden. Die Suche über den Index läuft sehr schnell im Vergleich zum Tabellenscan.
• Locking auf der Tabelle wird reduziert, da auf die Tabelle selbst seltener zugegriffen wird. Wenn für einen Fremdschlüssel kein Index angelegt ist, wird die Tabelle komplett gescannt, was dazu führt, dass die Tabelle voraussichtlich häufiger blockiert wird und somit Deadlocks auftreten können.

Am folgenden Beispiel kann man den Vorteil eines  Index auf dem Fremdschlüssel der Child-Tabelle gut nachvollziehen. Als Basis werden die Tabellen SalesOrderHeader und SalesOrderDetail der AdventureWorks2012 OLTP Datenbank genommen. SalesOrderDetail als Child-Tabelle referenziert über die SalesOrderID Spalte die Parent-Tabelle SalesOrderHeader. Wenn kein Index auf der SalesOrderDetail.SalesOrderID Spalte angelegt ist, wird beim Löschen eines Datensatzes in der Parent-Tabelle die Child-Tabelle komplett gescannt. Das sieht man z.B. im folgenden Ausführungsplan:

Nach dem Erstellen eines Index auf der SalesOrderID-Spalte der SalesOrderDetail-Tabelle sieht man, dass die relevanten Datensätze nun deutlich effizienter gefunden werden:

Fazit:

Wie immer muss man abwägen, ob ein zusätzlicher Index den Aufwand bei DML-Operationen und den erhöhten Speicherverbrauch rechtfertigt, aber oft bringt ein Index auf den Fremdschlüsselspalten in der Tat große Vorteile.

Links:

Kimberly Trip. When did SQL Server stop putting indexes on foreign key columns?

Erstellen von Fremdschlüssel-Beziehungen