Rekursive CTEs in MySQL 8: Implementierungsdetails und Fallstricke

1. Wie eine rekursive CTE grundsätzlich aufgebaut ist

Eine rekursive CTE besteht aus zwei durch UNION oder UNION ALL verbundenen Teilabfragen: dem Ankerglied, das die Startzeilen der Rekursion liefert, etwa alle Wurzelkategorien ohne übergeordnete Kategorie, und dem rekursiven Glied, das sich selbst über den Namen der CTE referenziert und in jedem Durchlauf eine weitere Ebene der Hierarchie anfügt. MySQL wertet diese Struktur so aus, dass das rekursive Glied wiederholt gegen das zuletzt erzeugte Zwischenergebnis ausgeführt wird, bis keine neuen Zeilen mehr entstehen.

Wichtig ist dabei die Reihenfolge: Das Ankerglied muss vor dem rekursiven Glied stehen, und beide Teile müssen exakt dieselbe Spaltenanzahl mit kompatiblen Datentypen liefern. Anders als bei einer regulären, nicht rekursiven CTE darf sich der rekursive Teil außerdem ausschließlich auf die zuletzt erzeugte Zwischenmenge beziehen, nicht auf das kumulierte Gesamtergebnis aller bisherigen Durchläufe, ein Detail, das bei komplexeren Aggregationen innerhalb der Rekursion leicht zu Fehlern führt.

2. Wie MySQL die Rekursion intern als UNION-Schleife umsetzt

MySQL implementiert WITH RECURSIVE technisch als wiederholte Ausführung des rekursiven Teils gegen eine temporäre Arbeitstabelle, die nach jedem Durchlauf ausschließlich die neu hinzugekommenen Zeilen enthält, nicht das gesamte bisherige Ergebnis. Diese Arbeitstabelle wird intern ähnlich wie eine reguläre abgeleitete Tabelle behandelt und typischerweise als MEMORY-Tabelle gehalten, solange die Datenmenge klein genug bleibt, bei größeren Zwischenergebnissen erfolgt automatisch ein Umschalten auf eine Disk-basierte Tabelle.

Für die Praxis bedeutet das, dass die Performance einer rekursiven CTE stark von der Größe der Zwischenergebnisse je Rekursionsstufe abhängt, nicht nur von der Gesamtzahl der Zeilen im Endergebnis. Eine Query, die pro Durchlauf sehr viele neue Zeilen erzeugt, etwa bei einer Baumstruktur mit hoher Verzweigung, kann selbst bei überschaubarer Gesamttiefe spürbar mehr Arbeitsspeicher und Zeit benötigen als eine tief verschachtelte, aber schmale Struktur.

3. MySQL-spezifische Besonderheiten gegenüber anderen Datenbanksystemen

Im Vergleich zu PostgreSQL oder Oracle fällt auf, dass MySQL innerhalb des rekursiven Glieds keine Aggregatfunktionen, keine GROUP BY-Klausel, kein DISTINCT und auch keine weitere Subquery erlaubt, die selbst wieder auf die rekursive CTE zugreift. Diese Einschränkungen existieren, weil MySQL die Rekursion strikt zeilenweise über die Arbeitstabelle auflöst und keine Zwischenaggregation über mehrere Rekursionsstufen hinweg unterstützt, was bei der Portierung von Queries aus anderen Systemen häufig zu Anpassungsbedarf führt.

Ebenfalls MySQL-spezifisch ist, dass standardmäßig UNION statt UNION ALL zwischen Anker und rekursivem Glied automatisch eine Duplikaterkennung über die gesamte bisherige Ergebnismenge erzwingt, was bei großen Baumstrukturen zusätzlichen Overhead verursacht. Sofern die zugrunde liegende Struktur ohnehin garantiert zyklenfrei ist, etwa weil eine Fremdschlüsselbeziehung strukturell keine Zyklen zulässt, ist UNION ALL fast immer die schnellere und in der Praxis meist ausreichende Wahl.

4. Endlosschleifen-Schutz über cte_max_recursion_depth

Enthält die zugrunde liegende Datenstruktur versehentlich einen Zyklus, etwa eine Kategorie, die fälschlicherweise sich selbst oder eines ihrer eigenen Kindelemente als übergeordnete Kategorie referenziert, würde eine rekursive CTE ohne Schutzmechanismus theoretisch endlos weiterlaufen. MySQL begrenzt die maximale Rekursionstiefe deshalb über die Systemvariable cte_max_recursion_depth, deren Standardwert bei 1000 liegt und die sowohl global als auch pro Session angepasst werden kann.

Wird dieser Grenzwert überschritten, bricht MySQL die Abfrage mit einer expliziten Fehlermeldung ab, statt sie unbegrenzt weiterlaufen zu lassen und dabei Arbeitsspeicher oder Festplattenplatz zu erschöpfen. Für die meisten Baumstrukturen im E-Commerce-Umfeld, deren tatsächliche Tiefe selten mehr als eine Handvoll Ebenen erreicht, ist der Standardwert bereits ein komfortabler Sicherheitsabstand, der reale Anfragen nicht einschränkt, aber echte Zyklen zuverlässig abfängt.

-- Aktuellen Grenzwert pruefen
SHOW VARIABLES LIKE 'cte_max_recursion_depth';

-- Für eine einzelne Session bewusst heraufsetzen,
-- wenn eine tatsaechlich sehr tiefe Struktur erwartet wird
SET SESSION cte_max_recursion_depth = 5000;

5. Praxisbeispiel: Magento-Kategoriebaum rekursiv auflösen

Der Kategoriebaum eines Magento-Shops liegt in catalog_category_entity als flache Tabelle mit einem parent_id-Fremdschlüssel vor, klassisch als Adjacency-List-Modell. Für eine vollständige Pfadauflösung, etwa um für jede Kategorie ihren gesamten Pfad von der Wurzel bis zur aktuellen Ebene als lesbare Zeichenkette zu erzeugen, eignet sich eine rekursive CTE deutlich besser als eine feste Kette von Self-Joins, die bei variabler Baumtiefe ohnehin nicht zuverlässig funktioniert.

Das Ankerglied selektiert dabei die Wurzelkategorien, das rekursive Glied verknüpft jede Ebene über parent_id mit der vorherigen und baut dabei sowohl die Tiefe als auch den vollständigen Namenspfad schrittweise auf. Für aktive Anwendungsfälle wie eine dynamische Breadcrumb-Navigation oder eine Kategoriebaum-Exportfunktion lässt sich dieselbe Query nahezu unverändert wiederverwenden.

WITH RECURSIVE category_path AS (
    SELECT entity_id, parent_id, name, 0 AS depth,
           CAST(name AS CHAR(500)) AS full_path
    FROM catalog_category_entity
    WHERE parent_id = 1

    UNION ALL

    SELECT c.entity_id, c.parent_id, c.name, cp.depth + 1,
           CONCAT(cp.full_path, ' / ', c.name)
    FROM catalog_category_entity c
    JOIN category_path cp ON c.parent_id = cp.entity_id
)
SELECT entity_id, depth, full_path
FROM category_path
ORDER BY full_path;

6. Performance-Tuning für tiefe oder breite Baumstrukturen

Der entscheidende Index für eine performante rekursive Kategorieabfrage liegt auf der Spalte parent_id, da jede Rekursionsstufe intern im Wesentlichen eine gefilterte Suche nach allen Kindern der zuvor gefundenen Zeilen darstellt. Ohne diesen Index degeneriert jede Rekursionsstufe zu einem vollständigen Tabellen-Scan, was bei tausenden Kategorien schnell spürbar wird, selbst wenn die tatsächliche Baumtiefe gering bleibt.

Zusätzlich lohnt es sich, das Ankerglied so präzise wie möglich zu formulieren und nicht mehr Startzeilen zu selektieren als tatsächlich benötigt werden, etwa durch eine zusätzliche Filterbedingung auf is_active, wenn deaktivierte Kategorien für den jeweiligen Anwendungsfall ohnehin irrelevant sind. Ein kleineres Ankerglied reduziert direkt die Größe jeder folgenden Zwischenergebnismenge und damit den gesamten Rekursionsaufwand.

7. Häufige Implementierungsfehler in der Praxis

Ein verbreiteter Fehler ist der Versuch, innerhalb des rekursiven Glieds eine Aggregatfunktion wie COUNT oder SUM einzusetzen, um beispielsweise laufend die Anzahl der Kindkategorien mitzuzählen, was MySQL syntaktisch schlicht ablehnt. Solche Aggregationen müssen stattdessen in einer separaten, der rekursiven CTE nachgelagerten Abfrage erfolgen, die auf dem bereits vollständig aufgelösten Gesamtergebnis arbeitet.

Ebenso häufig ist ein fehlender oder falsch gesetzter Datentyp bei textbasierten Pfadspalten wie im obigen full_path-Beispiel: Wird CAST(name AS CHAR(500)) im Ankerglied vergessen, bestimmt MySQL die Spaltenbreite automatisch aus der kürzeren ursprünglichen Spalte, wodurch der Pfad bei tieferen Rekursionsstufen stillschweigend abgeschnitten wird, ohne dass ein Fehler oder eine Warnung erscheint.

8. Zyklen aktiv erkennen statt nur über die Tiefenbegrenzung abzufangen

Der cte_max_recursion_depth-Schutz verhindert zwar einen unkontrollierten Absturz, liefert aber keine unmittelbare Diagnose, welche konkrete Zeile den Zyklus verursacht. Für datenkritische Anwendungsfälle empfiehlt sich deshalb eine zusätzliche, explizite Zyklenerkennung innerhalb der CTE selbst, etwa durch Mitführen eines Pfads bereits besuchter IDs und einer Abbruchbedingung, sobald die aktuelle ID bereits in diesem Pfad vorkommt.

Diese Technik erkauft sich zwar etwas zusätzliche Komplexität in der Query, liefert dafür aber eine präzise, sofort auswertbare Information darüber, welche Zeile fehlerhaft referenziert ist, was insbesondere bei der Fehlersuche in importierten oder manuell gepflegten Kategoriedaten erheblich Zeit spart gegenüber dem bloßen Abfangen über die Tiefenbegrenzung.

WITH RECURSIVE category_path AS (
    SELECT entity_id, parent_id,
           CAST(entity_id AS CHAR(2000)) AS visited
    FROM catalog_category_entity
    WHERE parent_id = 1

    UNION ALL

    SELECT c.entity_id, c.parent_id,
           CONCAT(cp.visited, ',', c.entity_id)
    FROM catalog_category_entity c
    JOIN category_path cp ON c.parent_id = cp.entity_id
    WHERE FIND_IN_SET(c.entity_id, cp.visited) = 0
)
SELECT * FROM category_path;

9. Wann sich eine rekursive CTE lohnt und wann Alternativen besser sind

Für gelegentliche Auswertungen, Reports und Admin-Werkzeuge ist eine rekursive CTE fast immer die klarste und wartbarste Lösung, weil die gesamte Baumlogik an einer Stelle in lesbarem SQL steht, statt über mehrere Anwendungsschichten verteilt zu sein. Für sehr häufig aufgerufene, lastkritische Pfade im Frontend, etwa die Navigation auf jeder einzelnen Seitenanfrage, ist dagegen häufig eine denormalisierte, materialisierte Pfadspalte oder ein Nested-Set-Modell mit vorab berechneten linken und rechten Grenzwerten performanter.

Die pragmatische Faustregel lautet: Wird die Baumstruktur deutlich häufiger gelesen als verändert, lohnt sich eine einmalige Materialisierung über eine rekursive CTE in eine denormalisierte Hilfstabelle, die dann bei jeder Leseanfrage ohne erneute Rekursion direkt abgefragt wird. Nur bei tatsächlich seltenen, ad hoc ausgeführten Abfragen ist die direkte rekursive CTE bei jedem Aufruf die einfachere und ausreichend performante Lösung.

10. Zusammenfassung

11. FAQ: Rekursive CTEs: Das Wichtigste auf einen Blick