Generated Columns für schnellere Queries auf berechneten Werten

1. Was Generated Columns sind: STORED vs. VIRTUAL

Eine Generated Column ist eine Tabellenspalte, deren Wert nicht direkt geschrieben, sondern automatisch aus einem Ausdruck berechnet wird, der auf andere Spalten derselben Zeile verweist. MySQL bietet dafür zwei Varianten: VIRTUAL und STORED. Eine VIRTUAL Generated Column wird bei jedem Lesezugriff neu berechnet und belegt keinen zusätzlichen Speicherplatz auf der Festplatte, während eine STORED Generated Column ihren berechneten Wert physisch in der Tabelle ablegt und bei jedem Schreibzugriff auf die Basis-Spalten neu berechnet und aktualisiert wird.

Der entscheidende praktische Unterschied liegt im Zusammenspiel mit Indizes. Beide Varianten können indexiert werden, aber bei VIRTUAL Columns wird der Index-Eintrag ebenfalls bei jedem Schreibzugriff aus dem Ausdruck neu berechnet, ohne dass der berechnete Wert selbst gespeichert wird, während bei STORED Columns sowohl der Wert als auch der Index physisch materialisiert vorliegen. Für lesehäufige Anwendungsfälle mit selteneren Schreibzugriffen ist STORED meist die bessere Wahl, weil der Wert nicht bei jedem Lesevorgang neu berechnet werden muss.

Seit MySQL 5.7 sind Generated Columns nativ als Feature im Server verfügbar, ohne dass Trigger oder Anwendungslogik zur Pflege redundanter Spalten nötig sind. Das ist ein erheblicher Vorteil gegenüber der früheren Praxis, berechnete Werte manuell in Anwendungscode oder über BEFORE INSERT/BEFORE UPDATE-Trigger zu pflegen, was fehleranfällig war und leicht aus dem Takt geriet, sobald jemand die Basis-Spalten außerhalb des vorgesehenen Pfads änderte.

-- VIRTUAL: computed on every read, no extra disk storage
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  price DECIMAL(10,2) NOT NULL,
  tax_rate DECIMAL(4,2) NOT NULL DEFAULT 19.00,
  price_with_tax DECIMAL(10,2) AS (price * (1 + tax_rate / 100)) VIRTUAL
);

-- STORED: computed and physically written on every write
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  net_total DECIMAL(10,2) NOT NULL,
  tax_amount DECIMAL(10,2) NOT NULL,
  gross_total DECIMAL(10,2) AS (net_total + tax_amount) STORED
);

2. Syntax und Definition im Detail

Die Syntax einer Generated Column folgt dem Muster spaltenname datentyp GENERATED ALWAYS AS (ausdruck) [VIRTUAL | STORED], wobei die Kurzform AS (ausdruck) ohne das Schlüsselwort GENERATED ALWAYS ebenfalls funktioniert und in der Praxis häufiger verwendet wird. Fehlt die explizite Angabe von VIRTUAL oder STORED, verwendet MySQL standardmäßig VIRTUAL. Der Ausdruck darf auf beliebig viele andere Spalten derselben Zeile zugreifen, deterministische MySQL-Funktionen wie JSON_EXTRACT, SUBSTRING oder arithmetische Operationen verwenden, aber keine Unterabfragen, keine Verweise auf andere Tabellen und keine nicht-deterministischen Funktionen wie NOW() oder RAND() enthalten.

Diese Einschränkung auf deterministische, zeilenlokale Ausdrücke ist kein Zufall, sondern eine bewusste Design-Entscheidung: Eine Generated Column muss für dieselbe Eingabe immer denselben Wert liefern, damit Replikation, Backups und Indexierung konsistent funktionieren. Ein Ausdruck, der von der aktuellen Uhrzeit oder einer anderen Tabelle abhängt, würde diese Garantie verletzen und ist deshalb technisch ausgeschlossen.

-- Adding a generated column to an existing table
ALTER TABLE products
  ADD COLUMN discount_price DECIMAL(10,2)
  AS (ROUND(price * 0.9, 2)) STORED;

-- Allowed: deterministic expressions referencing only this row's columns
-- Not allowed: subqueries, other tables, NOW(), RAND(), UUID()
ALTER TABLE products
  ADD COLUMN name_slug VARCHAR(255)
  AS (LOWER(REPLACE(name, ' ', '-'))) STORED;

3. Indexierung von Generated Columns

Der eigentliche Performance-Gewinn von Generated Columns entsteht durch ihre Indexierbarkeit. Ohne Generated Column lässt sich ein Ausdruck wie WHERE JSON_EXTRACT(data, '$.status') = 'active' nicht direkt indexieren, MySQL müsste bei jeder Anfrage die JSON-Struktur jeder Zeile zur Laufzeit parsen und auswerten, was bei großen Tabellen einen vollständigen Tabellenscan erzwingt. Legt man denselben Ausdruck als Generated Column an und indexiert diese, kann der Optimizer den Index direkt für Bereichs- und Gleichheitsabfragen nutzen, exakt wie bei einer regulären Spalte.

Wichtig dabei: MySQL erkennt automatisch, wenn eine Anfrage einen Ausdruck verwendet, der exakt der Definition einer indexierten Generated Column entspricht, auch wenn die Anfrage selbst die Spalte nicht namentlich referenziert. Eine Anfrage mit WHERE JSON_EXTRACT(data, '$.status') = 'active' nutzt also automatisch den Index auf der entsprechenden Generated Column, sofern der Ausdruck exakt übereinstimmt, inklusive Groß-/Kleinschreibung und Klammerung. Diese Übereinstimmung sollte nach dem Anlegen des Index immer mit EXPLAIN verifiziert werden.

-- Without a generated column: JSON_EXTRACT cannot be indexed directly,
-- MySQL must parse the JSON document on every row for every query
SELECT id FROM events WHERE JSON_EXTRACT(payload, '$.status') = 'active';
-- EXPLAIN shows: type ALL, full table scan

-- With an indexed generated column, the same expression uses the index
ALTER TABLE events
  ADD COLUMN status VARCHAR(32)
  AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.status'))) STORED,
  ADD INDEX idx_status (status);

-- MySQL recognizes the matching expression automatically
SELECT id FROM events WHERE JSON_EXTRACT(payload, '$.status') = 'active';
-- EXPLAIN now shows: type ref, key idx_status

4. Anwendungsfall: JSON-Werte extrahieren und indexieren

Der häufigste Anwendungsfall für Generated Columns in modernen MySQL-Anwendungen ist die Indexierung von Werten innerhalb einer JSON-Spalte. Anwendungen, die flexible, schwach strukturierte Daten in einer JSON-Spalte ablegen, etwa Event-Payloads, Konfigurationsobjekte oder Produktattribute mit variabler Struktur, verlieren dabei normalerweise die Möglichkeit, effizient nach einzelnen Feldern innerhalb des JSON-Dokuments zu filtern. Generated Columns lösen genau dieses Dilemma, indem sie einzelne, häufig abgefragte JSON-Felder als reguläre, indexierbare Spalten extrahieren, während die volle Flexibilität der JSON-Spalte für seltener abgefragte Felder erhalten bleibt.

In der Praxis wählt man typischerweise die zwei bis drei am häufigsten in WHERE-Klauseln verwendeten JSON-Felder für die Extraktion als Generated Column aus, während der Rest des JSON-Dokuments unverändert bleibt. Das ist ein pragmatischer Mittelweg zwischen der vollen Flexibilität eines reinen Dokumentenspeichers und der Performance eines starr normalisierten relationalen Schemas, ohne die Nachteile eines vollständigen Schema-Redesigns in Kauf nehmen zu müssen.

-- Products table with flexible JSON attributes
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  attributes JSON NOT NULL,
  -- Extract the two most frequently filtered attributes as indexed columns
  brand VARCHAR(100)
    AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.brand'))) STORED,
  in_stock TINYINT(1)
    AS (JSON_EXTRACT(attributes, '$.stock') > 0) STORED,
  INDEX idx_brand (brand),
  INDEX idx_in_stock (in_stock)
);

-- Efficient filtering without ever touching the raw JSON document
SELECT id, name FROM products
WHERE brand = 'Acme' AND in_stock = 1;

5. Anwendungsfall: berechnete Summen und Kennzahlen

Neben JSON-Extraktion sind vorberechnete Aggregate und Kennzahlen ein zweites, sehr verbreitetes Einsatzgebiet für Generated Columns. Ein Bruttobetrag aus Netto und Steuer, ein Volltextsuche-Score aus mehreren gewichteten Feldern, oder ein normalisierter Sortierschlüssel, der Groß-/Kleinschreibung und Sonderzeichen aus einem Produktnamen entfernt, lassen sich als Generated Column abbilden, statt bei jeder Leseoperation neu berechnet oder in der Anwendungsschicht redundant gepflegt zu werden.

Ein praktisches Beispiel aus dem E-Commerce-Umfeld ist ein normalisierter Suchschlüssel, der Umlaute, Sonderzeichen und Groß-/Kleinschreibung aus einem Produktnamen entfernt, um konsistente, case-insensitive Sortierungen und Präfix-Suchen zu ermöglichen, ohne bei jeder Anfrage eine Funktion auf die Originalspalte anwenden zu müssen. Da diese Transformation deterministisch ist, eignet sie sich ideal für eine STORED Generated Column mit eigenem Index.

-- Normalized, indexable sort/search key derived from a display name
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  display_name VARCHAR(255) NOT NULL,
  search_key VARCHAR(255)
    AS (LOWER(REPLACE(REPLACE(REPLACE(display_name,
        'ä','ae'), 'ö','oe'), 'ü','ue'))) STORED,
  INDEX idx_search_key (search_key)
);

-- Case-insensitive prefix search using the index, not a runtime function
SELECT id, display_name FROM products
WHERE search_key LIKE 'stuehle%';

6. Performance-Vergleich: Generated Column vs. Alternativen

Vergleicht man Generated Columns gegen die früher übliche Alternative aus redundanter Spalte plus manuellem Trigger, zeigt sich der Vorteil vor allem in der Konsistenzgarantie. Ein BEFORE UPDATE-Trigger, der eine redundante Spalte pflegt, wird bei jedem regulären UPDATE-Statement zuverlässig ausgeführt, kann aber bei direkten Bulk-Operationen über LOAD DATA INFILE oder bestimmten replikationsbedingten Sonderfällen übersprungen werden, was zu inkonsistenten Daten führt. Eine Generated Column hingegen wird vom Server selbst auf einer tieferen Ebene berechnet und kann prinzipbedingt nicht aus dem Takt geraten, weil der Wert gar nicht direkt geschrieben werden kann.

Beim reinen Laufzeitvergleich gegen einen funktionalen Index, den MySQL 8.0.13 zusätzlich als direktes Feature eingeführt hat, sind beide Ansätze für einfache Fälle nahezu gleichwertig, ein funktionaler Index legt intern ebenfalls eine unsichtbare Generated Column an. Der praktische Unterschied ist, dass eine explizite Generated Column als benannte Spalte auch direkt in SELECT-Listen, Sortierungen und Gruppierungen verwendbar ist, während ein rein funktionaler Index nur für die Indexierung selbst existiert und im Ergebnis nicht direkt sichtbar ist.

7. Einschränkungen und Fallstricke

Generated Columns haben klare technische Grenzen. Der definierende Ausdruck darf keine Unterabfragen enthalten, keine Nutzervariablen referenzieren und keine nicht-deterministischen Funktionen wie NOW(), RAND() oder CONNECTION_ID() verwenden, weil diese die Konsistenzgarantie zwischen Wert und Index verletzen würden. Auch Fremdschlüssel auf VIRTUAL Generated Columns waren vor MySQL 5.7.6 nicht möglich, seitdem aber grundsätzlich erlaubt, sofern die referenzierende Engine dies unterstützt.

Ein weniger offensichtlicher Fallstrick betrifft STORED Generated Columns bei sehr breiten Tabellen mit hoher Schreiblast, denn jede Änderung an einer Basis-Spalte löst eine Neuberechnung und einen zusätzlichen physischen Schreibvorgang für die abgeleitete Spalte aus, was bei vielen abhängigen Generated Columns pro Zeile den Schreibdurchsatz spürbar reduzieren kann. Für Tabellen mit extrem hoher Schreibfrequenz und seltenen Lesezugriffen auf den berechneten Wert kann daher eine VIRTUAL Column oder ein rein funktionaler Index die bessere Wahl sein.

8. Migration bestehender Tabellen

Das nachträgliche Hinzufügen einer Generated Column zu einer bereits produktiven, großen Tabelle erfordert Vorsicht bezüglich der Sperrverhalten. In MySQL 8.0 wird das Hinzufügen einer VIRTUAL Generated Column als reine Metadatenänderung ausgeführt und blockiert daher weder Lese- noch Schreibzugriffe nennenswert, weil kein bestehender Datensatz physisch angefasst werden muss. Das Hinzufügen einer STORED Generated Column hingegen erfordert eine vollständige Neuberechnung und Neuschreibung der Tabelle, was bei mehreren Millionen Zeilen erhebliche Zeit in Anspruch nehmen und je nach ALGORITHM-Wahl blockierend wirken kann.

Für produktive Migrationen an großen Tabellen empfiehlt sich daher entweder das Ausweichen auf ein Online-Schema-Change-Tool wie pt-online-schema-change oder gh-ost, oder ein zweistufiges Vorgehen: zunächst die Spalte als VIRTUAL anlegen, den Effekt in der Produktion validieren, und erst danach bei Bedarf auf STORED umstellen, wenn sich der Lesezugriff als der dominierende Faktor herausstellt.

-- Cheap, metadata-only change: adding a VIRTUAL column
ALTER TABLE large_table
  ADD COLUMN full_name VARCHAR(255)
  AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
  ALGORITHM=INSTANT;

-- Expensive: converting to STORED requires rewriting every row
ALTER TABLE large_table
  MODIFY COLUMN full_name VARCHAR(255)
  AS (CONCAT(first_name, ' ', last_name)) STORED,
  ALGORITHM=INPLACE, LOCK=NONE;

9. Generated Columns mit Triggern und Constraints kombinieren

Generated Columns lassen sich sinnvoll mit CHECK-Constraints kombinieren, die seit MySQL 8.0.16 tatsächlich durchgesetzt werden. Ein typisches Muster ist eine Generated Column, die eine Validierungsregel abbildet, kombiniert mit einem CHECK-Constraint, der auf dem berechneten Wert aufsetzt, etwa um sicherzustellen, dass ein aus mehreren Feldern abgeleiteter Status nur innerhalb einer definierten Menge gültiger Werte liegt.

Bei der Kombination mit klassischen Triggern ist Vorsicht geboten: Ein BEFORE INSERT-Trigger kann grundsätzlich nicht auf eine Generated Column schreibend zugreifen, da deren Wert erst nach der Trigger-Ausführung vom Server berechnet wird. Wer versucht, in einem Trigger direkt einen Wert in eine Generated Column zu schreiben, erhält einen klaren Fehler. Diese strikte Trennung ist beabsichtigt und verhindert widersprüchliche Schreibversuche zwischen Trigger-Logik und der serverseitigen Berechnung.

10. Zusammenfassung

Generated Columns lösen ein klassisches Dilemma zwischen flexibler Datenmodellierung und effizienter Indexierung, indem sie berechnete Werte, sei es aus JSON-Dokumenten extrahiert oder aus mehreren Spalten aggregiert, als reguläre, indexierbare Spalten verfügbar machen. Die Wahl zwischen VIRTUAL und STORED hängt vom Verhältnis aus Lese- und Schreiblast ab: VIRTUAL spart Speicherplatz und Schreib-Overhead, STORED liefert schnellere Lesezugriffe, weil der Wert bereits materialisiert vorliegt.

Verglichen mit der historischen Alternative aus redundanten Spalten plus manuell gepflegten Triggern bieten Generated Columns eine serverseitig garantierte Konsistenz, die auch bei Bulk-Operationen nicht aus dem Takt geraten kann. Für Migrationen an großen, produktiven Tabellen empfiehlt sich der vorsichtige Weg über VIRTUAL mit ALGORITHM=INSTANT, bevor bei Bedarf auf STORED umgestellt wird.

11. FAQ: Generated Columns für schnellere Queries