Tipp für Relationale Datenbanken: Indizes für Fremdschlüssel

Direkt zu Beginn in Kurzform das Wesentliche: In relationalen Datenbanken kann es sinnvoll sein, aus Performancegründen Indizes auf Fremdschlüssel-Spalten zu erstellen! Für wen dies bereits logisch und bekannt ist, bietet dieser Beitrag nicht Neues.

Dass Indizes Sortierung und Filterung auf in ihnen enthaltenen Spalten beschleunigen, sollte allen, die mit Datenbanken arbeiten, vertraut sein. Für mich war es jedoch eine neue – rückwirkend betrachtet eigentlich offensichtliche – Erkenntnis, dass Löschoperationen durch Indizes in anderen Tabellen beschleunigt werden können. Allerdings bin ich kein Datenbankadministrator und der Entwurf und die Optimierung von Datenbankschemata sind nur gelegentlich Teil meiner Aufgaben. In Bezug auf relationale Datenbanken wurde mir vor allem vermittelt, wie wichtig eine gute Modellierung zur Gewährleistung von Datenkorrektheit ist. Know-how zu Performance wurde weitestgehend ausgespart, weil sich das bei sauberen Schemata bereits automatisch ergeben würde. In der Praxis habe ich dennoch teilweise nicht akzeptable Verarbeitungsgeschwindigkeiten feststellen müssen und mögliche Lösungswege kennengelernt. Weil vermutlich noch weitere Personen in einer vergleichbaren Situation sind, möchte ich diesen Tipp teilen, um ihnen eventuell etwas Zeit bei einer zukünftigen Problemanalyse zu ersparen.

Problemfall

Bei meinem aktuellen Kundenprojekt war ein Schritt von vielen, während einer fast dreitägigen Produktivsystemabschaltung, eine Datenbankbereinigung. Vor allem in einer Tabelle mussten dabei mehrere Millionen Datensätze gelöscht werden – allerdings nicht alle, sodass kein Weg um die Löschung von Einzeleinträgen herumführte (kein TRUNCATE möglich). Bei Tests vor der produktiven Ausführung stellte sich heraus, dass nur 4-5 Elemente pro Sekunde gelöscht werden konnten!

Wie bei den meisten Problemen kann es hier ebenfalls viele mögliche Ursachen geben. Beispielsweise eine anderweitig bereits ausgelastete Datenbank, eine aufwändige Ermittlung der zu löschenden Datensätze oder nicht zusammengefasste Löschanweisungen, die einzeln nacheinander von einer Anwendung mit einer gewissen Verbindungslatenz zur Datenbank abgesetzt werden. In diesem Fall konnten die genannten Gründe ausgeschlossen werden: Die Datensätze wurden bei der Löschung direkt per Primärschlüssel referenziert und immer eine gewisse Menge in einer Abfrage gebündelt verarbeitet.

Analyse

Generell empfiehlt sich bei der Auswertung von Performance-Problemen im Datenbankumfeld eine Prüfung des Ausführungsplans („query plan“) für langsame Operationen. Auf diese Weise kann ein Einblick in die vom Datenbanksystem unter der Haube ausgeführten Teilschritte zur Bearbeitung von SQL-Anweisungen erlangt werden.

Leider sind Ausführungspläne nicht leicht verständlich: Bereits beim Identifizieren von langsamen Teilschritten kann man daneben liegen (lange Ausführungszeit eines Teilschritts bedeutet wegen Daten-Streaming nicht, dass es der Flaschenhals war) und eventuell automatisch generierte Empfehlungen zur Performance-Steigerung müssen genau hinterfragt werden. Darüber hinaus hängen die Details vom verwendeten Datenbanksystem ab, sodass dieser Blogbeitrag darauf nicht weiter eingeht. Für den Microsoft SQL Server bietet das SQL Server Management Studio grafische Werkzeuge , die auf SET SHOWPLAN_XML (Dokumentation ) und SET STATISTICS XML (Dokumentation ) basieren. Bei PostgreSQL kann EXPLAIN (Dokumentation ) verwendet werden. Andere Systeme bieten ähnliche Werkzeuge.

In diesem konkreten Fall war im Ausführungsplan auffällig, dass für die DELETE-Anweisung sehr viele „clustered index scans“ generiert wurden: Je einer für fast jeden Fremdschlüssel, der auf die Tabelle verwies. Das bedeutet, dass das Datenbanksystem den kompletten Inhalt – nicht bloß die Fremdschlüsselspalte – von mehr als 30 Tabellen* laden und auf Verwendungen der zu löschenden Datensätze prüfen musste! Ursache ist, dass ein Datenbanksystem die referentielle Integrität gewährleisten muss: Entweder das Löschen abbrechen oder die für die Fremdschlüssel definierten Löschaktionen ausführen, wenn ein Verweis auf einen zu löschenden Datensatz gefunden wird.

* Die Löschoperation musste in einer Tabelle angewendet werden, die im Datenbankschema sehr zentral war und entsprechend oft referenziert wurde.

Problembehebung

Wenn die Ursache mal gefunden ist, ist eine Lösung oft der leichtere Teil. In diesem Fall mussten in den anderen Tabellen, die eine Fremdschlüsselbeziehung zur Tabelle mit den zu löschenden Einträgen haben, Indizes auf die Spalten mit den Fremdschlüsseln angelegt werden.

Zur Veranschaulichung ein sehr simples Beispiel, bei denen ein Customer über mehrere Address-Einträge verfügen kann:

CREATE TABLE [Customer] (
    [Id]   [int] IDENTITY(1, 1) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,

    CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([Id])
);

CREATE TABLE [Address] (
    [Id]         [int] IDENTITY(1, 1) NOT NULL,
    [CustomerId] [int] NOT NULL,
    [Formatted]  [nvarchar](300) NOT NULL,

    CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED ([Id]),
    CONSTRAINT [FK_Address_CustomerId_Customer_Id] FOREIGN KEY ([CustomerId]) REFERENCES [Customer] ([Id])
);

Um (falls notwendig) Löschoperationen in der Customer-Tabelle zu beschleunigen, muss ein Index erstellt werden, der ein effizientes Prüfen auf Verwendungen durch die Address-Tabelle ermöglicht:

CREATE NONCLUSTERED INDEX [IX_Address_CustomerId] ON [Address] ([CustomerId]);

Man könnte intuitiv davon ausgehen, dass ein Datenbanksystem solche Indizes automatisch im Hintergrund erstellt. Dies passiert jedoch im Regelfall nicht und würde eventuell gar nicht benötigt: Wenn in der primären Tabelle nie gelöscht wird, besteht kein Bedarf für einen solchen Index. Weil Indizes immer aktuell gehalten werden müssen und damit zur Laufzeit etwas die Verarbeitungsgeschwindigkeit reduzieren, ist das Einsparen nicht notwendiger Indizes empfehlenswert. Es ist demnach eine Abwägungssache, die vom jeweiligen Anwendungsfall abhängt.

Ergebnis

Für das Anlegen der notwendigen Indizes (siehe vorherigen Abschnitt) benötigte die Datenbank etwa eine halbe Minute. Anschließend wurde dieselbe DELETE-Anweisung für mehr als 30.000 Datensätze pro Sekunde ausgeführt!

Von einer ursprünglichen Ausführungszeit von hochgerechnet mehr als sechs Tagen sind somit nur ein paar Minuten übriggeblieben. Mit den erstellten Indizes konnte das Datenbanksystem von den „clustered index scans“ zu den wesentlich schnelleren „index seeks“ wechseln. Der Geschwindigkeitsgewinn entsteht, weil Indizes eine eigene, für das schnelle Auffinden von Werten optimierte Datenstruktur bereitstellen. Dadurch muss nur ein sehr kleiner Bruchteil der Tabellendaten verarbeitet werden. Ohne diese Optimierung wäre der Zeitplan für die Produktivsystemumstellung, bei der noch viele weitere Aktionen stattfinden mussten, nicht möglich gewesen.