}
Computer & Internet► Datenbanken► Performance► Performance

Filtered Indizes: SQL-Selects beschleunigen (fast) ohne DML zu bremsen

Ein „Filtered Index“, der auch als „Partial Index“ bezeichnet wird, ist ein Index, der nur jene Zeilen einer Tabelle enthält, welche die für den Index definierte Bedingung erfüllen. Er wird bei Abfragen genutzt, für die nur Zeilen die im filtered Index enthalten sind notwendig sind. Der Einsatz eines Filtered Index hat im Vergleich zu einem normalen Index drei Vorteile:

  • Ist bei geeigneter, stark selektiver Filterbedingung wesentlich kleiner.
  • Muss keine Zeilen aufnehmen, für die der Index auf Grund mangelnder Selektivität höchstens bei Index-Only-Zugriffen genutzt würde.
  • Kein unnötiges ausbremsen von DML-Operationen auf Zeilen, welche die Filterbedingung nicht erfüllen und somit nicht im filtered Index enthalten sind.

Einsatzszenarien für Filtered Indizes

Ein Ideales Einsatzszenario für einen Filtered-Index sind Situationen in denen eine Tabellenspalte fast vollständig die gleiche Ausprägung besitzt, idealerweise sollten > 99% der Zeilen den gleichen Wert haben. Die anderen Ausprägungen sollten in der Spalte nur als Spuren vorkommen, jedoch häufig zur Selektion verwendet werden. In diesem Fall definiert man den Filtered Index so, dass er die Zeilen mit der Hauptausprägung der Spalte nicht aufnimmt. Das hat den Vorteil das für diese Zeilen quasi kein Performance-Nachteil beim Insert, Update oder Delete entsteht, Abfragen nach den Spurenelementen unter den Ausprägungen jedoch vom Index profitieren können. Bei Stark ungleichmäßiger Verteilung der Ausprägungen einer Spalte ist es in der Regel nicht hilfreich die Hauptausprägung in den Index aufzunehmen. Dies gilt zumindest bei Indizes ohne weitere wesentlich selektivere Spalten. Dies liegt daran, dass eine Selektion der Zeilen, die die Hauptausprägung aufweisen nicht von einem Index profitiert, da Indizes nur bei einer Ausreichend hohen Selektivität verwendet werden, da sonst ein Table Scan effizienter ist. Je nach Tabelle, Index und Datenbanksystem schwankt die Grenze ab der ein Table Scan gegenüber einem Indexzugriff bevorzugt wird. Sie liegt jedoch häufig zwischen 0,1% und 10% der Zeilen. Eine Ausnahme stellen hierbei sogenannt Index-Only-Zugriffe dar, durch deren Nutzung ein Zugriff auf die Tabelle vollständig vermieden wird. Dies gilt selbstverständlich auch für Spaltenkombinationen bei denen die meisten Zeilen die gleiche Wertkombination aufweisen und nur wenige eine abweichende Wertekombination für die Spaltenkombination haben. Auch in diesem Fall kann ein Filtered Index den Zugriff auf die Zeilen mit abweichenden Ausprägungen in der Spaltenkombination gut beschleunigen. Trotzdem behält der Filtered Index eine kompakte Größe und belastet die DML-Operationen für den Großteil der Zeilen  nicht durch Indexpflege , da diese nicht im Index enthalten sind. Weitere Einsatzszenarien für filtered Indizes bieten sich immer dann, wenn eine sehr kleine Zahl der Datenzeilen für ein Abfragen relevant ist und durch eine klare Bedingung definiert werden kann. Wobei die Bedingung durchaus mehrere Spalten verwenden darf, sie muss nur deterministisch sein.

Beispiele für Tuning mit Filtered Index

In einer Tabelle werden Vorgänge mit aktuellem Status und dem zugeordneten Bearbeiter sowie weiteren Details gespeichert:

CREATE TABLE Vorgang(
       Vorgangsnummer int NOT NULL PRIMARY KEY,
       Vorgangsstatus char(1) NOT NULL,
       Zugewiesener_Bearbeiter varchar(8) NULL
       ... weitere Spalten mit Details zum Vorgang ...
);

Der Großteil der Vorgänge ist abgeschlossen, da jeder Vorgang im Status „abgeschlossen“ endet und aus Dokumentationsgründen keine Vorgänge gelöscht werden. Hierdurch wird der Anteil der Vorgänge die abgeschlossen sind kontinuierlich weiter anwachsen. Neue Vorgänge werden ohne zugeordneten Bearbeiter angelegt, da die Zuordnung erst dann erfolgt, wenn ein Bearbeiter frei wird und dieser einen neuen Vorgang zugewiesen bekommt. Zur Zuweisung eines Vorgangs an einen Bearbeiter wird jeweils aus allen neuen, noch nicht zugewiesenen Vorgängen (Vorgangsstatus = ‘N’, Zugewiesener_Bearbeiter = NULL) der am längsten wartende, also de mit der kleinsten Vorgangsnummer, ausgewählt und dem gerade freien Bearbeiter zugewiesen:

SELECT min(Vorgangsnummer)
FROM Vorgang
WHERE Vorgangsstatus = 'N';

Oder als direktes Update:

UPDATE Vorgang
SET Zugewiesener_Bearbeiter = 'Bearb003', Vorgangsstatus = 'B'
WHERE Vorgangsnummer = (
       SELECT min(Vorgangsnummer)
       FROM Vorgang
       WHERE Vorgangsstatus = 'N');

Ein Filtered Index der nur die neuen Vorgänge aufnimmt kann die Ermittlung des nächsten Vorgangs für einen Sachbearbeiter beschleunigen ohne, dass Änderungen durch die Sachbearbeiter an den bereits zugewiesenen Vorgängen ausgebremst werden. Dazu werden nur die neuen Vorgänge (Vorgangsstatus = ‘N’) in den Index aufgenommen:

CREATE INDEX IX_Vorgangsnummer_nur_Status_N
       ON Vorgang (Vorgangsnummer)
       WHERE Vorgangsstatus = 'N';

In diesem Fall reicht es die Vorgangsnummer in den Index aufzunehmen, da das DBMS durch die Filterbedingung des Index weiß, dass nur Vorgänge im Status Neu (Vorgangsstatus = ‘N’) im Index enthalten sind. Wenn das obige Update ausgeführt wird, wird dem Vorgang nicht nur ein Bearbeiter zugewiesen und der Vorgangstatus auf ‘B’ (Bearbeitung) geändert, der Vorgang wird auch aus dem Index entfernt, da er die Filterbedingung des Index nicht mehr erfüllt. Der Filtered Index hat im Vergleich zu einem normalen nicht gefilterten Index den Vorteil, dass er wesentlich kleiner als ein Index ohne Filter ist. Weil der Großteil (>99%) der Vorgänge nicht mehr neu ist und deshalb nicht im Index aufgenommen werden muss ist dieser sehr klein. Außerdem müsste ein Index der nicht nur Vorgangsstatus = ‘N’ enthält auch die Spalte Vorgangsstatus mit indizieren um die obige Abfrage optimal zu unterstützten, da er Vorgänge mit verschiedenen Stati enthält und nur wenn der Vorgangsstatus im Index enthalten ist kann bereits auf dem Index erkannt werden welche Vorgänge neu sind. Der Index hat jedoch den Nachteil, dass er Abfragen die Vorgänge mit einem anderem Status als Neu benötigen nicht unterstützen kann. Dafür beeinträchtigt er die Geschwindigkeit von Änderungen an Sätzen mit anderem Status nicht durch Indexpflegeaufwand. Durch diese Einschränkung des Pflegeaufwands auf wenige indizierte Sätze können mehr individuelle Filtered Indizes angelegt werden die verschiedene Abfragen unterstützen ohne die Änderungsperformance zu stark zu verschlechtern, solange die Filterbedingungen auf unterschiedliche Datensätze zutreffen.

Einsatzbedingungen und Alternativen

Ob der Filtered Index bzw. Partial Index ein sinnvolles Mittel ist um die Performance zu steigern hängt stark von den Daten und darauf ausgeführten Abfragen ab. Er bietet sich an wenn mit Abfragen - die nicht von bereits vorhandenen Indizes unterstützt werden - immer wieder Daten aus dem gleichen, wohldefinierten, kleinen Teil der Tabelle selektiert werden. Nicht die Richtige Wahl ist der Filtered Index, wenn die Daten in wechselnden Bereichen der Tabelle liegen, der Bereich nicht durch eine definierte, deterministische Filter Bedingung abgegrenzt werden kann oder die für die Abfragen benötigte Datenmenge nicht selektiv genug ist um einen Indexzugriff zu rechtfertigen. Alternativen die in solchen Situationen bessere Ergebnisse liefern können sind z.B. Clustered Indizes, Tabellen-Partitionierung und manchmal auch Materialized Views.