von Markus Winand.

Index-Only-Scan


Bei einem Index-Only-Scan wird nicht nur der Tabellenzugriff für die where-Klausel vermieden. Der Tabellenzugriff kann vollständig vermieden werden, wenn die Abfrage die gesuchten Spalten direkt aus dem Index entnehmen kann.

Für einen Index-Only-Scan müssen daher alle Spalten der Abfrage im Index enthalten sein – insbesondere auch die Spalten der select-Klausel, wie im folgenden Beispiel:

CREATE INDEX sales_sub_eur
    ON sales
     ( subsidiary_id, eur_value )
SELECT SUM(eur_value)
  FROM sales
 WHERE subsidiary_id = ?

Dabei hat die Indizierung der where-Klausel natürlich Vorrang gegenüber anderen Klauseln. Die Spalte SUBSIDIARY_ID muss also an erster Stelle stehen, damit sie für das Zugriffsprädikat genutzt werden kann.

Der Ausführungsplan zeigt den Indexzugriff, allerdings ohne darauffol­genden Tabellenzugriff (TABLE ACCESS BY INDEX ROWID).

Der Index deckt also die gesamte Abfrage ab. In diesem Fall kann man einen Index auch als „Covering Index“ bezeichnen.

Beachte

Wenn ein Index einen Tabellenzugriff vermeidet, bezeichnet man ihn auch als „Covering Index“.

Der Begriff ist jedoch irreführend, da er als Eigenschaft des Indexes missverstanden werden kann. Der Bezeichnung Index-Only-Scan legt hingegen nahe, dass es um einen Schritt im Ausführungsplan geht.

Da der Index eine Kopie der Spalte EUR_VALUE beinhaltet, kann die Daten­bank direkt mit dem Wert aus dem Index arbeiten. Der Tabellenzugriff ist also nicht mehr nötig, da alle Informationen zum Beantworten der Abfrage im Index gespeichert sind.

Ein Index-Only-Scan kann einen enormen Geschwindigkeitsvorteil bringen. Dazu muss man sich nur vor Augen führen, dass die Abfrage circa vierzig­tausend Zeilen aufsummiert, wie in der Rows-Spalte des Ausführungsplanes zu sehen ist. Es werden also bis zu vierzigtausend Tabellenzugriffe ver­mieden. Allerdings nur, wenn jede Zeile in einem anderen Tabellenblock gespeichert ist. Wenn der Clustering-Faktor des Indexes aber gut ist, wenn die entsprechenden Zeilen also auch in der Tabelle nahe beieinander liegen, kann der Vorteil deutlich geringer ausfallen.

Hinweis in eigener Sache: Schulung in Wien

SQL Performance und modernes SQL – das sind die Themen meiner 5-tägigen SQL-Schulung im Mai. Mehr darüber und andere Schulungsformate auf winand.at.

Neben dem Clustering-Faktor wird der Performancevorteil eines Index-Only-Scans auch durch die Zahl der betroffenen Zeilen beschränkt. Wenn zum Beispiel nur eine einzelne Zeile gelesen wird, kann auch nur ein Tabellenzugriff eingespart werden. Das Durchwandern des Indexbaumes alleine benötigt aber schon einige Lesezugriffe. Einen Tabellenzugriff zu sparen bringt also verhältnismäßig wenig.

Wichtig

Der Performancevorteil eines Index-Only-Scans hängt vom Index-Clustering-Faktor und von der Zeilenzahl ab.

Mit einem Index-Only-Scan kann man aber auch unangenehme Überra­schungen erleben. Wenn man die Abfrage zum Beispiel auf aktuelle Verkäufe einschränkt:

SELECT SUM(eur_value)
  FROM sales
 WHERE subsidiary_id = ?
   AND sale_date > ?

Ohne den Ausführungsplan zu betrachten, könnte man erwarten, dass die Abfrage schneller wird. Schließlich werden weniger Zeilen abfragt. Da die Abfrage aber auf eine Spalte zugreift, die nicht im Index ist, muss nun ein Tabellenzugriff durchgeführt werden:

Durch den Tabellenzugriff wird die Abfrage langsamer – obwohl weniger Zeilen benötigt werden. Der relevante Faktor ist nicht, wie viele Zeilen benötigt werden, sondern wie viele die Datenbank laden muss, um die gesuchten zu finden.

Warnung

Eine Erweiterung der where-Klausel kann zu „unlogischem“ Per­formance­verhalten führen. Prüfe den Ausführungsplan vor dem Erweitern einer Abfrage.

Wenn ein Index durch die Änderung einer Abfrage nicht mehr für einen Index-Only-Scan verwendet werden kann, wählt der Optimizer den nächst­besten Ausführungsplan. Das heißt, es kann auch ein völlig anderer Index verwendet werden. Das ist oben auch geschehen. Der neue Ausführungs­plan verwendet einen Index auf SALE_DATE – ein Relikt aus dem Join-Kapitel.

Gegenüber dem vorherigen Index hat dieser, aus Sicht des Optimizers, zwei Vorteile. Der Optimizer glaubt, dass die Bedingung auf der Spalte SALE_DATE selektiver ist als jene auf der SUBSIDIARY_ID-Spalte. Das erkennt man am jeweiligen Rows-Wert für den INDEX RANGE SCAN (circa 10.000 versus 40.000). Diese Schätzungen sind jedoch völlig willkürlich, da Bind-Para­meter verwendet werden. Der Filter auf SALE_DATE könnte, zum Beispiel, alle Zeilen liefern, wenn ein entsprechend frühes Datum angegeben wird.

Der zweite Vorteil des SALES_DATE-Indexes ist, dass er einen besseren Clustering-Faktor hat. Dieser Vorteil ist stichhaltig, weil die Tabelle SALES nur chronologisch wächst. Da keine Zeilen gelöscht werden, werden neue Einträge immer an das hintere Ende der Tabelle geschrieben. Sie ist also grob chronologisch sortiert – ebenso wie der Index. Die Indexreihenfolge stimmt gut mit der Tabellenreihenfolge überein – der Index hat einen guten Clustering-Faktor.

Bei einem Indexzugriff mit gutem Clustering-Faktor liegen die Tabellen­zeilen nahe beieinander, sodass nur wenige Tabellenblöcke gelesen werden müssen, um alle Zeilen zu laden. Die Abfrage könnte mit diesem Index also auch ohne Index-Only-Scan schnell genug sein. Dann sollte man die nicht mehr benötigten Spalten aus dem alten Index löschen.

Beachte

Manche Indizes haben von Natur aus einen guten Clustering-Faktor. Der Performancevorteil eines Index-Only-Scans kann dann klein sein.

Bei diesem Beispiel ist es zu einem glücklichen Zufall gekommen. Die Erweiterung der where-Klausel hat nicht nur den Index-Only-Scan unter­bunden, sondern gleichzeitig einen neuen Zugriffsweg geöffnet. Dadurch konnte der Optimizer die Auswirkung begrenzen. Ein Index-Only-Scan kann aber auch durch neue Spalten in anderen Klauseln unterbunden werden. Es genügt, eine weitere Spalte in die select-Klausel aufzunehmen. Dadurch kann man aber keinen neuen Zugriffsweg öffnen, der die Auswirkung begrenzen könnte.

Tipp

Warte deine Index-Only-Scans.

Kommentiere den Source-Code, damit man den Index-Only-Scan nicht vergessen kann. Füge einen Verweis auf diese Seite hinzu, damit jeder nachlesen kann.

Funktions-basierende Indizes können im Zusammenhang mit Index-Only-Scans ebenfalls zu Überraschungen führen. Wenn man die Spalte LAST_NAME selektiert, hilft es nicht, den Ausdruck UPPER(last_name) zu indizieren. Daher hätte man im vorherigen Abschnitt die Spalte LAST_NAME selbst indizieren sollen, damit sie im Rahmen eines Index-Only-Scans auch für die select-Klausel benutzt werden kann.

Tipp

Versuche immer die Original-Daten zu indizieren. Das ist meist die nützlichste Information, die man in den Index aufnehmen kann.

Vermeide Funktions-basierte Indizierung, wenn der Ausdruck ohne­hin nicht als Zugriffsprädikat verwendet werden kann.

Aggregierende Abfragen wie oben sind gute Kandidaten für einen Index-Only-Scan. Solche Abfragen greifen meist auf viele Zeilen, aber nur wenige Spalten zu. Dadurch genügt ein verhältnismäßig schmaler Index für einen Index-Only-Scan. Je mehr Spalten abgefragt werden, desto mehr Spalten muss man auch in den Index aufnehmen, damit ein Index-Only-Scan durchgeführt werden kann. Als Entwickler sollte man daher nur auf die unbedingt notwendigen Spalten zugreifen.

Tipp

Vermeide select * und lade nur die tatsächlich benötigten Spalten.

INCLUDE: Nicht-Schlüsselspalten

SQL Server und PostgreSQL 11+ unterstützen neben den bisher behan­delten Schlüsselspalten auch Nicht-Schlüssel­spal­ten. Diese werden nur in die Blattknoten aufgenommen und können daher nicht für Zugriffsprädikate verwendet werden.

Nicht-Schlüsselspalten werden in der include-Klausel angegeben:

 CREATE INDEX empsubupnam
     ON employees
       (subsidiary_id, last_name)
INCLUDE(phone_number, first_name)

DB2

DB2 LUW ist auf 64 Spalten mit einer maximalen Schlüssellänge von 25% der Blockgröße beschränkt.

DB2 unterstützt eine INCLUDE-Klausel, mit der man Nicht-Schlüsselspalten in einen Unqiue-Index aufnehmen kann. Damit kann man einen Unqiue-Index um weitere Spalten erweitern um einen Index-Only-Scan zu ermöglichen, ohne die Semantik des Unique-Keys zu ändern.

MySQL

MySQL und InnoDB beschränken die Gesamtschlüssellänge (alle Spalten) auf 3072 Byte. Weiters ist die Länge jeder Spalte mit 767 Byte begrenzt, wenn innodb_large_prefix nicht aktiviert ist oder andere Zeilenformate als DYNAMIC oder COMPRESSED verwendet wird. Das war die Standard-Einstellung bis inklusive MySQL 5.6. MyISAM-Indizes sind auf 16 Spalten und 1000 Byte Gesamtschlüssellänge beschränkt.

In diesem Zusammenhang hat MySQL eine Besonderheit: die soge­nannte „Präfix-Indizierung“, manchmal auch „partielle Indizierung“ genannt. Dabei wird nur der Anfang einer Spalte in den Index aufgenommen. Es hat also nichts mit partieller Indizierung zu tun, wie sie in Kapitel 2 vorgestellt wurde. Falls die zulässige Länge einer Index-Spalte überschritten wird, kann sie von MySQL – abhängig vom SQL mode und dem Zeilenformat – automatisch gekürzt werden (767, 1000 oder 3072 Byte, siehe oben). In diesem Fall erhält man bei der create index-Anweisung die Warnung „Specified key was too long; max key length is … bytes“. Das bedeutet, dass die Spalte nicht mehr vollständig im Index gespeichert ist – selektiert man eine solche Spalte verhindert man damit einen Index-Only-Scan (ähnlich einem Funktions-basierenden Index).

Man kann die Präfix-Indizierung in MySQL auch explizit verwenden, um die Gesamtschlüssellänge zu begrenzen, wenn der Fehler: „Specified key was too long; max key length is … bytes“ auftritt. Beim folgenden Beispiel werden nur die ersten zehn Zeichen der Spalte LAST_NAME in den Index übernommen.

CREATE INDEX .. ON employees (last_name(10))
Oracle

Ein B-Tree-Index kann maximal 32 Spalten beinhalten. Die maximale Schlüssellänge hängt von der Blockgröße und den Storage-Parametern ab (75% der Blockgröße, abzüglich eines Over­heads). Bei Release 11g ist die maximale Schlüssellänge bei den Standardeinstellungen (8k-Blöcke) 6398 Byte (ORA-01450: maximum key length (6398) exceeded).

PostgreSQL

PostgreSQL unterstützt Index-Only-Scans ab PostgreSQL 9.2. B-Tree-Indizes sind auf 32 Spalten und 2713 Bytes beschränkt (hardcoded, circa BLCKSZ/3). Die Fehlermeldung „index row size ... exceeds btree maximum, 2713“ erscheint erst, wenn ein insert oder update zu einer Überschreitung führt.

SQL Server

Seit Version 2016 unterstützt SQL Server bis zu 32 Schlüssel­spal­ten mit maximal 1700 Byte (bei Clus­tered-Indizes: 900 Byte).0 Include-Spalten unterliegen dieser Limitierung nicht.

Tipp

Abfragen, die keine Tabellenspalten in der select-Klausel haben, werden oft als Index-Only-Scan ausgeführt.

Fällt dir ein sinnvolles Beispiel ein?

Über den Autor

Foto von Markus Winand

Markus Winand ist der SQL Renaissance Botschafter auf der Mission, Entwickler auf die Evolution von SQL im 21. Jahrhundert aufmerksam zu machen. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Sein Buch bei Amazon kaufen

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Bei Amazon kaufen
(Taschenbuch)

Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

Sein Training

Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how

Erfahren Sie mehr»

Fußnoten

0

Vor SQL Server 2016: 16 Spalten und generell 900 Byte.

„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz und DSGVO | CC-BY-NC-ND 3.0 Lizenz