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
darauffolgenden 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 Datenbank 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
vierzigtausend Zeilen aufsummiert, wie in der Rows
-Spalte
des Ausführungsplanes zu sehen ist. Es werden also bis zu vierzigtausend
Tabellenzugriffe vermieden. 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
Ich lebe von SQL-Schulungen, anderen SQL-Dienstleistungen und dem Verkauf meines Buches. Mehr dazu 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 Überraschungen 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 (UNIQUE) | 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“
Performanceverhalten 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ächstbesten
Ausführungsplan. Das heißt, es kann auch ein völlig anderer Index
verwendet werden. Das ist oben auch geschehen. Der neue Ausführungsplan
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-Parameter 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 Tabellenzeilen 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 unterbunden, 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 ohnehin 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 Unique-Index aufnehmen kann. Damit kann man einen Unique-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 alsDYNAMIC
oderCOMPRESSED
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 sogenannte „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 Overheads). 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 eininsert
oderupdate
zu einer Überschreitung führt.- SQL Server
Seit Version 2016 unterstützt SQL Server bis zu 32 Schlüsselspalten mit maximal 1700 Byte (bei Clustered-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?