von Markus Winand.

Index-organisierte Tabellen und Clustered Indizes


Ein Index-Only-Scan führt eine SQL-Anweisung ausschließlich unter Nut­zung der redundanten Daten aus dem Index aus. Die Originaldaten in der Heap-Tabelle werden nicht benötigt. Wenn man das Konzept weiterführt und alle Spalten in den Index aufnimmt, stellt sich die Frage, wozu man die Heap-Tabelle überhaupt braucht?

Bei manchen Datenbanken kann man den Index tatsächlich als primären Tabellenspeicher benutzen. Bei der Oracle Datenbank nennt man dieses Konzept Index-organisierte Tabellen (IOT), andere Datenbanken verwenden den Begriff Clustered-Index. Im Folgenden werden beide Begriffe gemischt verwendet, je nachdem, ob der Index- oder Tabellencharakter im Vorder­grund steht.

Eine Index-organisierte Tabelle ist also ein B-Tree-Index ohne Heap-Tabelle. Daraus ergeben sich zwei Vorteile: (1) der Speicherplatz für die Heap-Struktur wird gespart; (2) jeder Zugriff auf den Clustered-Index ist automa­tisch ein Index-Only-Scan. Beide Vorteile klingen vielversprechend, können in der Praxis aber nur selten genutzt werden.

Sobald man weitere Indizes auf einer Index-organisierten Tabelle anlegt, kommen die Nachteile zum Vorschein. Analog zu einem normalen Index enthält ein solcher sekundärer Index ebenfalls Verweise auf die Tabellen­daten – die allerdings im Clustered-Index gespeichert sind. Dort sind die Tabellendaten aber nicht feststehend, wie in einer Heap-Tabelle, sondern können jederzeit verschoben werden, damit die Indexreihenfolge gewahrt bleibt. Daher kann man die physischen Adressen der Zeilen im Clustered-Index nicht im sekundären Index speichern. Stattdessen muss ein logischer Schlüssel verwendet werden.

Hinweis in eigener Sache

Ich biete SQL Schulungen, Optimierung und Beratung an. Auch der Kauf meines Buches „SQL Performance Explained“ (ab €9,95) unterstützt meine Arbeit an dieser Webseite.

Die folgenden Abbildungen zeigen, wie eine Suche nach allen Verkäufen vom 23. Mai 2012 über einen sekundären Index abläuft. Zum Vergleich betrachten wir in Abbildung 5.2 zunächst die Situation mit einer Heap-Tabelle. Die Ausführung erfolgt in zwei Schritten: (1) die Indexsuche (INDEX RANGE SCAN); (2) der Tabellenzugriff (TABLE ACCESS BY INDEX ROWID).

Abbildung 5.2 Indexgestützter Zugriff auf eine Heap-Tabelle

Der Tabellenzugriff kann zwar durchaus der limitierende Faktor sein, führt aber maximal einen Lesezugriff pro Zeile durch. Diese Begrenzung ergibt sich daraus, dass der Index die ROWID der Zeile speichert. Die exakte Spei­cherstelle in der Heap-Tabelle ist also bekannt, sodass die Zeile direkt geladen werden kann. Bei einem sekundären Index sieht das jedoch anders aus. Er speichert nicht die physische Position (ROWID) der Zeile, sondern den Schlüssel für den Clustered-Index – den sogenannten Clustering-Schlüssel. Das ist meist der Primärschlüssel der Index-organisierten Tabelle.

Der Zugriff auf einen Sekundär-Index liefert also keine ROWID, sondern den Schlüssel für den Zugriff auf den Clustered-Index. Das heißt, dass ein Tabellenzugriff über einen sekundären Index zwei Indizes durchsucht. Einmal den sekundären Index (INDEX RANGE SCAN) und dann den Clustered-Index für jede Zeile (INDEX UNIQUE SCAN).

Abbildung 5.3 Sekundärer Indexzugriff auf eine IOT

In Abbildung 5.3 wird deutlich, dass der Indexbaum des Clustered-Indexes zwischen dem Sekundär-Index und den Tabellendaten steht.

Der Zugriff auf eine Index-organisierte Tabelle über einen sekundären Index ist also sehr ineffizient. Man kann ihn jedoch genauso vermeiden, wie man den Tabellenzugriff bei einer Heap-Tabelle vermeiden kann: mit ei­nem Index-Only-Scan – in diesem Fall vielleicht besser als „Only-Secondary-Index-Scan“ umschrieben. Diese Methode ist bei einem sekundären Index sogar noch gewinnbringender, da nicht nur ein Zugriff pro Zeile, sondern ein ganzer INDEX UNIQUE SCAN eingespart wird.

Wichtig

Ein Tabellenzugriff über einen sekundären Index ist sehr ineffizient.

Anhand dieser Idee kann man wieder einmal zeigen, dass Datenbanken sämtliche Redundanzen ausnutzen. Konkret ist es der Clustering-Schlüssel, der in jeden sekundären Index automatisch aufgenommen wird. Daraus folgt, dass man den Clustering-Schlüssel direkt aus dem sekundären Index beziehen kann:

SELECT sale_id
  FROM sales_iot
 WHERE sale_date = ?
-------------------------------------------------
| Id | Operation        | Name           | Cost |
-------------------------------------------------
|  0 | SELECT STATEMENT |                |    4 |
|* 1 |  INDEX RANGE SCAN| SALES_IOT_DATE |    4 |
-------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("SALE_DATE"=:DT)

Die Tabelle SALES_IOT ist eine Index-organisierte Tabelle mit der Spalte SALE_ID als Clustering-Schlüssel. Obwohl der Index SALES_IOT_DATE an sich nur die Spalte SALE_DATE beinhaltet, kann der Clustering-Schlüssel – die Spalte SALE_ID – direkt aus dem sekundären Index geliefert werden.

Wenn andere Spalten selektiert werden, muss zusätzlich ein INDEX UNIQUE SCAN auf dem Clustered-Index durchgeführt werden:

SELECT eur_value
  FROM sales_iot
 WHERE sale_date = ?
---------------------------------------------------
| Id  | Operation         | Name           | Cost |
---------------------------------------------------
|   0 | SELECT STATEMENT  |                |   13 |
|*  1 |  INDEX UNIQUE SCAN| SALES_IOT_PK   |   13 |
|*  2 |   INDEX RANGE SCAN| SALES_IOT_DATE |    4 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("SALE_DATE"=:DT)
   2 - access("SALE_DATE"=:DT)

Zusammengefasst sind Index-organisierte Tabellen oder Clustered-Indizes nicht so nützlich, wie es auf den ersten Blick scheint. Der Performancegewinn auf dem Clustered-Index geht bei Zugriffen über sekundäre Indizes schnell verloren. Der Clustering-Schlüssel ist häufig länger als eine ROWID, sodass die sekundären Indizes oft größer sind als ein entsprechender Index auf einer Heap-Tabelle. Dadurch wird die Platzersparnis ebenfalls zunichtegemacht. Die Stärke einer Index-organisierten Tabelle oder eines Clustered-Indexes liegt vor allem bei Tabellen, die keinen sekundären Index benötigen. Heap-Tabellen haben hingegen den Vorteil, dass sie ein stationäres Original zur Verfügung stellen, auf das mehrere Indizes einfach verweisen können.

Wichtig

Tabellen, die nur einen Index haben, werden am besten als Clustered-Index oder Index-organisierte Tabelle umgesetzt.

Tabellen mit mehreren Indizes können von der Heap-Struktur profitieren. Dennoch kann man die Indizes jeweils für einen Index-Only-Scan auslegen, sodass der Zugriff auf die Heap-Struktur entfällt. Damit erhält man die select-Performance eines Clustered-Indexes, ohne die Zugriffe über andere Indizes zu verlangsamen.

Die Unterstützung von Index-organisierten Tabellen und Clustered-Indizes ist von Datenbank zu Datenbank sehr unterschiedlich. Die folgende Übersicht erklärt die wichtigsten Besonderheiten.

DB2

DB2 kennt keine Index-organisierte Tabellen, verwendet den Begriff „clustered index“ jedoch für eine andere Funktion. Dabei wird weiterhin eine Heap-Tabelle verwendet, die Datenbank versucht jedoch neue Einträge in denselben Tabellenblock zu legen, wie die Einträge die im Index nahe sind.

MySQL

MySQL verwendet bei der MyISAM-Engine nur Heap-Tabellen, mit InnoDB aber nur Clustered-Indizes. Das bedeutet, dass man keine direkte Wahlmöglichkeit hat.

Oracle

Die Oracle Datenbank verwendet per Default Heap-Tabellen. Einzelne Tabellen können durch den Zusatz ORGANIZATION INDEX auch als Index-organisierte Tabelle angelegt werden:

CREATE TABLE (
   id    NUMBER NOT NULL PRIMARY KEY,
   [...]
) ORGANIZATION INDEX

Dabei wird immer der Primärschlüssel als Clustering-Schlüssel ver­wendet.

PostgreSQL

PostgreSQL verwendet nur Heap-Tabellen.

Es gibt jedoch die Möglichkeit, den Tabelleninhalt nach einem Index auszurichten (CLUSTER-Klausel).

SQL Server

SQL Server verwendet per Default Clustered-Indizes (Index-organisierte Tabellen) mit dem Primärschlüssel als Clustering-Schlüssel. Man kann aber einen beliebigen Clustering-Schlüssel verwenden – sogar non-unique Spaltenkombinationen.

Zum Anlegen einer Heap-Tabelle muss man die NONCLUSTERED-Klausel bei der Definition des Primärschlüssels angeben:

CREATE TABLE (
   id    NUMBER NOT NULL,
   [...]
   CONSTRAINT pk PRIMARY KEY NONCLUSTERED (id)
)

Löscht man einen Clustered-Index, wird die Tabelle in eine Heap-Ta­belle umgebaut.

Das Standardverhalten von SQL Server führt häufig zu Performance­problemen bei Zugriffen über einen Sekundär-Index.

Vorherige SeiteNächste Seite

Du kannst nicht alles an einem Tag lernen. Abonniere den Newsletter via E-Mail, Twitter oder RSS um sukzessive aufzuholen. Und sieh dir auch modern-sql.com an.

Über den Autor

Foto von Markus Winand

Markus Winand gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Sein Buch kaufen

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

Die Essenz: SQL-Tuning auf 200 Seiten

Jetzt Kaufen
(Taschenbuch und/oder PDF)

Sein Training

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

Erfahren Sie mehr»

Mit Markus Winand verbinden

Markus Winand auf LinkedInMarkus Winand auf XINGMarkus Winand auf Twitter
„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