Index-Only-Scan: Den Tabellenzugriff vermeiden


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).

DB2
Explain Plan
---------------------------------------------------------------
ID | Operation              |                       Rows | Cost
 1 | RETURN                 |                            |   21
 2 |  GRPBY (COMPLETE)      |       1 of 34804 (   .00%) |   21
 3 |   IXSCAN SALES_SUB_EUR | 34804 of 1009326 (  3.45%) |   19

Predicate Information
 3 - START (Q1.SUBSIDIARY_ID = ?)
      STOP (Q1.SUBSIDIARY_ID = ?)
Oracle
----------------------------------------------------------
| Id  | Operation         | Name          |  Rows | Cost |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |  104 |
|   1 |  SORT AGGREGATE   |               |     1 |      |
|*  2 |   INDEX RANGE SCAN| SALES_SUB_EUR | 40388 |  104 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SUBSIDIARY_ID"=TO_NUMBER(:A))

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.

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

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.

Der Index-Only-Scan ist eine aggressive Indizierungsstrategie. Man sollte einen Index nicht auf Verdacht für einen Index-Only-Scan auslegen, da er dadurch unnötig Speicher belegt und der Wartungsaufwand für update-Anweisungen steigen kann. Siehe Kapitel 8, „Schreiboperationen. In der Praxis indiziert man zuerst ohne Berücksichtigung der select-Klausel und bessert bei Bedarf nach.

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:

DB2
Explain Plan
-------------------------------------------------------------------
ID | Operation                 |                       Rows |  Cost
 1 | RETURN                    |                            | 13547
 2 |  GRPBY (COMPLETE)         |        1 of 1223 (   .08%) | 13547
 3 |   FETCH SALES             |    1223 of 34804 (  3.51%) | 13547
 4 |    RIDSCN                 |   34804 of 34804 (100.00%) |    32
 5 |     SORT (UNQIUE)         |   34804 of 34804 (100.00%) |    32
 6 |      IXSCAN SALES_SUB_EUR | 34804 of 1009326 (  3.45%) |    19

Predicate Information
 3 - SARG (? < Q1.SALE_DATE)
     SARG (Q1.SUBSIDIARY_ID = ?)
 6 - START (Q1.SUBSIDIARY_ID = ?)
      STOP (Q1.SUBSIDIARY_ID = ?)
Oracle
--------------------------------------------------------------
|Id | Operation                    | Name      | Rows  |Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT             |           |     1 | 371 |
| 1 |  SORT AGGREGATE              |           |     1 |     |
|*2 |   TABLE ACCESS BY INDEX ROWID| SALES     |  2019 | 371 |
|*3 |    INDEX RANGE SCAN          | SALES_DATE| 10541 |  30 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("SUBSIDIARY_ID"=TO_NUMBER(:A))
   3 - access("SALE_DATE">:B)

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.

Tweet this tip

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.

Tweet this tip

Tipp

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

Unabhängig davon, dass ein Index mit vielen Spalten auch viel Speicher­platz belegt, kann man damit auch an die Grenzen der Datenbank stoßen. Die maximale Spaltenzahl und Schlüssellänge sind bei den meisten Datenbanken verhältnismäßig eng begrenzt. Das heißt, dass man nicht beliebig viele und auch nicht beliebig lange Spalten indizieren kann. Die folgende Übersicht zeigt die Beschränkungen verschiedener Datenbanken. Unabhängig davon gibt es Indizes, die eine ganze Tabelle abdecken, wie wir im nächsten Abschnitt sehen werden.

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

SQL Server beschränkt Indexschlüssel auf 900 Byte und 16 Spalten. SQL 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)

Denksport

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

Fällt dir ein sinnvolles Beispiel ein?

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.