von Markus Winand.

GROUP BY indizieren


SQL-Datenbanken verwenden zwei unterschiedliche group by-Algorith­men. Der erste, der Hash-Algorithmus, baut das aggregierte Ergebnis in einer Hash-Tabelle auf. Sobald die Quelldaten vollständig verarbeitet wurden, wird die Hash-​Tabelle als Ergebnis ausgegeben. Der zweite Al­go­rithmus, der Sort/Group-Algorithmus, sortiert die Quelldaten zuerst nach dem Gruppierungs­schlüssel. Dadurch folgen die Zeilen jeder Gruppe unmittelbar hintereinander, sodass sie nur noch zusammengefasst werden müssen. Grundsätzlich müssen beide Algorithmen ein Zwischenergebnis materialisieren und werden daher nicht „am Fließband“ (pipelined) ausge­führt. Es sei denn, der Sort/Group-Algorithmus kann die Sortierung durch Indizierung ersetzen.

Beachte

Die MySQL Datenbank 5.6 verwendet den Hash-Algorithmus nicht. Die unten beschriebene Optimierung für den Sort/Group-Al­go­rith­mus funktioniert aber.

Als Beispiel betrachten wir die folgende Abfrage. Sie zeigt die Umsätze von gestern nach PRODUCT_ID gruppiert an:

SELECT product_id, sum(eur_value)
  FROM sales
 WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
 GROUP BY product_id

Unter Berücksichtigung des Indexes auf SALE_DATE und PRODUCT_ID aus dem vorherigen Abschnitt ist der Sort/Group-Algorithmus in diesem Fall vorteilhaft, da der INDEX RANGE SCAN das Ergebnis bereits in der benötigten Reihenfolge liefert. Das bedeutet, dass die Datenbank die Materialisierung vermeiden kann, weil nicht extra sortiert werden muss. Das group by kann „am Fließband“ (pipelined) ausgeführt werden.

Die Oracle Datenbank kennzeichnet die Ausführung „am Fließband“ (pipe­lined) durch den Zusatz NOSORT. Bei anderen Datenbanken scheint die Sortierung erst gar nicht im Ausführungsplan auf.

Für ein pipelined group by gelten dieselben Voraussetzungen wie für ein piplined order by, nur dass es keine ASC/DESC-Spezifikationen gibt. Das bedeutet, dass die Indizierung mit ASC/DESC-Spezifikationen keinen Einfluss auf ein pipelined group by haben sollte. Dasselbe gilt auch für NULLS FIRST/LAST. Dennoch gibt es Datenbanken, bei denen ASC/DESC-Indizierung ein pipelined group by beeinflusst.

Achtung

Bei PostgreSQL muss man eine order by-Klausel angeben, damit ein Index mit NULLS LAST-Sortierung für ein pipelined group by ver­wendet werden kann.

Die Oracle Datenbank kann einen Index nicht rückwärts lesen, um ein pipelined group by durchzuführen, das von einem order by gefolgt wird.

Mehr Information finden Sie im Anhang für PostgreSQL und Oracle Datenbank.

Wir erweitern nun die Abfrage, um alle Verkäufe seit gestern zu be­rück­sichti­gen, wie wir es zuvor auch bei dem Beispiel für das pipelined order by getan haben. Diese Änderung unterbindet ein pipelined group by aus demselben Grund, aus dem auch das pipelined order by unterbunden wurde: Der INDEX RANGE SCAN liefert die Daten nicht mehr ausschließlich nach dem Gruppierungs­schlüssel sortiert (vergleiche Abbildung 6.1).

SELECT product_id, sum(eur_value)
  FROM sales
 WHERE sale_date >= TRUNC(sysdate) - INTERVAL '1' DAY
 GROUP BY product_id

Daher bevorzugt die Datenbank den Hash-Algorithmus. Dessen Vorteil liegt darin, dass er nur das aggregierte Ergebnis materialisiert. Der Sort/Group-Algorithmus muss jedoch die gesamten Quelldaten zwischenspeichern. Kurz gesagt: Der Hash-​Algorithmus benötigt weniger Speicher.

Wie auch beim pipelined order by, geht es beim piplined group by nicht unbedingt um eine schnellere Ausführung. Wichtig ist, dass die Datenbank die Aggregierung „am Fließband“ (pipelined) ausführt – das also das erste Ergebnis kommt, bevor Quelldaten vollständig verarbeitet wurden. Das ist die Voraussetzung für die Optimierungsmethoden des nächsten Kapitels.

Tipp

Gibt es neben Sortieren und Gruppieren noch andere Datenbank-Operationen, die einen Index nutzen könnten, um eine Sortierung zu vermeiden?

Wenn dir gefällt, wie ich die Dinge erkläre, wirst du meine Kurse lieben.

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

Kaufen Sie sein Buch bei Amazon

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»

„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