Partielle Indizes: Ausgewählte Zeilen indizieren


Gilt für
DB2Nein
MySQLNein
OracleNein
PostgreSQLJa
SQL ServerJa

Bisher haben wir uns nur damit beschäftigt, welche Spalten in einen Index aufgenommen werden. Mit partiellen (PostgreSQL) oder gefilterten (SQL Server) Indizes kann man auch steuern, welche Zeilen in einen Index aufgenommen werden.

Achtung

Die Oracle Datenbank hat einen besonderen Zugang zu partiellen Indizes. Der nächste Abschnitt geht darauf ein, baut aber auf diesen Abschnitt auf.

DB2 unterstützt keine partiellen Indizes. Durch EXCLUDE NULL KEY kann man ähnlich wie bei Oracle emulieren.

Partielle Indizes sind für häufig verwendete where-Bedingungen mit konstanten Werten nützlich. Im folgenden Beispiel ist das der Status-Code:

SELECT message
  FROM messages
 WHERE processed = 'N'
   AND receiver  = ?

Abfragen dieser Form kommen bei Warteschlangen häufig vor. Es werden alle Nachrichten für einen bestimmten Empfänger gesucht, die noch nicht verarbeitet wurden. Nachrichten, die bereits verarbeitet wurden, werden nur sehr selten gesucht. Und falls doch, mit einem konkreten Suchkriterium wie dem Primärschlüssel.

Mit einem zweispaltigen Index kann die Suche ideal unterstützt werden. Wenn man nur diese Abfrage betrachtet, ist die Spaltenreihenfolge sogar egal, weil keine Bereichsbedingung vorkommt.

CREATE INDEX messages_todo
          ON messages (receiver, processed)

Der Index erfüllt zwar seinen Zweck, beinhaltet aber sehr viele Einträge, die niemals gesucht werden – alle Nachrichten, die bereits verarbeitet wurden. Durch die logarithmische Skalierung des Indexes ist die Abfrage trotzdem schnell, der Speicherplatz ist aber verschwendet.

Mit partieller Indizierung kann man den Index auf die noch nicht ver­ar­bei­teten Nachrichten einschränken. Die Syntax dafür ist erstaunlich einfach: eine where-Klausel.

CREATE INDEX messages_todo
          ON messages (receiver)
       WHERE processed = 'N'

Der Index beinhaltet also nur die Zeilen, die die where-Klausel erfüllen. Im konkreten Fall kann man die PROCESSED-Spalte sogar aus dem Index entfernen, da sie ohnehin immer 'N' ist. Dadurch schrumpft der Index in zwei Richtungen: vertikal, weil weniger Zeilen indiziert werden, horizontal, weil eine Spalte entfällt.

Die passende Tasse zu dieser Website findest du in unserem Shop.
Sieht gut aus und unterstützt meine Arbeit hier.

Der Index ist also sehr klein. Im Falle einer Warteschlange kann das sogar so weit gehen, dass die Indexgröße konstant bleibt, obwohl die Tabelle wächst. Schließlich ist nicht die ganze Tabelle indiziert, sondern nur die Nachrichten, die noch nicht verarbeitet wurden.

Die where-Klausel eines partiellen Indexes kann grundsätzlich beliebig komplex sein. Nur bei Funktionen gibt es eine Einschränkung: Man kann nur deterministische Funktionen verwenden – wie in der restlichen Indexdefinition auch. SQL Server hat jedoch sehr restriktive Ein­schränk­ungen und erlaubt weder Funktionen noch den OR-Operator in Index-Prädikaten.

Die Datenbank kann einen partiellen Index natürlich nur verwenden, wenn die where-Klausel der Indexdefinition unverändert in der Abfrage verwendet wird.

Denksport

Welche Besonderheit hat der kleinstmögliche Index für die folgende Abfrage:

SELECT message
  FROM messages
 WHERE processed = 'N'

Wenn dir gefällt, wie ich die Dinge erkläre, wirst du meine Kurse lieben.

Über den Autor

Photo of Markus Winand
Markus Winand stimmt Entwickler auf SQL-Performance ein. Er hat das Buch SQL Performance Explained veröffentlicht und bietet inhouse Schulungen sowie Tuning-Leistungen auf http://winand.at/ an.