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

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.

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

PostgreSQL führt das pipelined group by nicht automatisch durch, wenn der Index die Null-Werte als kleinstmöglichen Wert behandelt. Durch hinzufügen einer oder by-Klausel mit der Indexreihenfolge kann man dieses Problem umgehen.

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?

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