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

Wenn Dir dieser Artikel gefällt, könnte mein Buch SQL Performance Explained oder mein Training auch etwas für Dich sein.

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.

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

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). Ein B-Tree-Index kann maximal 32 Spalten beinhalten.

Bei Verwendung der Standardeinstellungen mit Release 11g (8k-Blöcke) ist die maximale Schlüssellänge 6398 Byte. Bei Überschreitung erhält man die Fehlermeldung „ORA-01450: maximum key length (6398) exceeded“.

PostgreSQL

PostgreSQL unterstützt Index-Only-Scans ab PostgreSQL 9.2.

B-Tree-Indizes sind auf 2713 Bytes beschränkt (hardcoded, circa BLCKSZ/3). Die Fehlermeldung „index row size ... exceeds btree max­imum, 2713“ erscheint erst, wenn ein insert oder update zu einer Überschreitung führt. Ein B-Tree-Index kann bis zu 32 Spalten haben.

SQL Server

Seit Version 2016 unterstützt SQL Server 2016 bis zu 32 Schlüsselspalten. Insgesamt dürfen sie 1700 Byte nicht überschreiten (bei Clustered-Indizes: 900 Byte).0SQL Server hat jedoch eine Erweiterung, die es erlaubt, beliebige Daten­menge für einen Index-Only-Scan in den Index aufzunehmen. Dazu wird zwischen Schlüsselspalten und Nicht-Schlüsselspalten unterschieden.

Schlüsselspalten sind normale Indexspalten, wie wir sie bisher kennengelernt haben. Nicht-Schlüsselspalten sind zusätzliche Spalten, die nur in die Blattknoten des Index aufgenommen werden. Nicht-Schlüsselspalten können beliebig lang sein, aber nicht für Zugriffs­prädikate verwendet werden.

In der create index-Anweisung werden Nicht-Schlüsselspalten mit der include-Klausel angegeben:

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

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 lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

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.

Holen Sie sich Markus

…für ein Training ins Büro.

Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

Erfahren Sie mehr»

Fußnoten

0

Vor SQL Server 2016: 16 Spalten und generall 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 | CC-BY-NC-ND 3.0 Lizenz