Webentwicklung

Generierte Spalten in MariaDB - Abgeleitete Werte an einer Stelle halten

Generierte Spalten in MariaDB - Abgeleitete Werte an einer Stelle halten

Eine Tabelle speichert quantity und unit_price, während die Anwendung zusätzlich line_total speichert. Dieser dritte Wert wirkt praktisch, bis ein Codepfad einen Preis aktualisiert, ohne die Summe neu zu berechnen. Sollte die Anwendung weiterhin alle drei Werte koordinieren, oder sollte die Datenbank den dritten aus den ersten beiden ableiten?

Generierte Spalten in MariaDB bieten eine mögliche Antwort. Sie machen einen Ausdruck innerhalb derselben Zeile zum Bestandteil der Tabellendefinition. Aufrufer können dadurch eine benannte Spalte lesen, ohne ihren Wert unabhängig zu liefern. Das kann eine Art duplizierten Zustands beseitigen, macht aber nicht jede Berechnung zu einer guten Datenbankspalte. Die engere und nützlichere Frage lautet: Hat dieser Wert eine einzige stabile, auf die Zeile begrenzte Definition, die das Schema durchsetzen sollte?

Was eine generierte Spalte verändert

MariaDB definiert eine generierte Spalte als Spalte, deren Wert aus einem Ausdruck stammt. Sie ist kein gewöhnliches, frei beschreibbares Feld. Ein Insert oder Update liefert die Quellspalten, und MariaDB wertet den generierten Ausdruck aus.

Das unterscheidet sich von zwei verwandten Schemafunktionen:

  • Ein DEFAULT liefert einen Wert, wenn eine Spalte beim Insert ausgelassen wird. Danach bleibt diese Spalte unabhängig beschreibbar.
  • Ein CHECK akzeptiert oder verwirft eine Zeile anhand einer Bedingung. Er stellt keinen abgeleiteten Wert für Abfragen bereit.
  • Eine generierte Spalte stellt das Ergebnis eines definierten Ausdrucks bereit und hält es an seine Quellspalten gebunden.

Dieser Unterschied ist wichtig. Eine generierte Summe ist keine zwischengespeicherte Zahl, deren Aktualisierung der Anwendungscode im Blick behalten muss. Sie gehört zur deklarierten Form der Zeile.

Ein kleines Beispiel

Betrachten wir eine bewusst einfache Tabelle für Bestellpositionen:

CREATE TABLE order_lines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  quantity INT UNSIGNED NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  line_total DECIMAL(12,2)
    AS (quantity * unit_price) PERSISTENT,
  PRIMARY KEY (id),
  INDEX idx_line_total (line_total)
);

Die Anwendung schreibt quantity und unit_price, nicht einen unabhängig berechneten Wert für line_total:

INSERT INTO order_lines (quantity, unit_price)
VALUES (2, 19.95), (3, 7.50);

SELECT id, quantity, unit_price, line_total
FROM order_lines;

Unter MariaDB 11.8.6 wurde diese Syntax in einer isolierten temporären Tabelle geprüft. Die beiden generierten Ergebnisse waren 39.90 und 22.50; SHOW CREATE TABLE gab die Definition PERSISTENT als STORED aus. Dies ist eine begrenzte Prüfung von Syntax und Verhalten, kein Beleg dafür, dass der Entwurf für jede Produktionstabelle geeignet oder schnell ist.

Das Beispiel verbirgt zudem reale Geschäftsfragen. Steuern, Rabatte, Währungen und Rundungsregeln können eine kommerzielle Summe wesentlich komplizierter als eine Multiplikation machen. Eine generierte Spalte ist erst dann angemessen, wenn diese Semantik ausdrücklich feststeht. Sie sollte keine ungeklärte Buchungsregel in eine scheinbar verbindliche Zahl verwandeln.

VIRTUAL und PERSISTENT platzieren die Arbeit unterschiedlich

MariaDB unterstützt zwei Formen generierter Spalten:

  • VIRTUAL wird beim Abfragen berechnet und nicht als gewöhnliche Spaltendaten gespeichert. Diese Form ist der Standard, wenn keine der beiden Formen angegeben wird.
  • PERSISTENT, wofür MariaDB auch den Alias STORED akzeptiert, speichert den generierten Wert in der Tabelle.

Das ist keine einfache Wahl zwischen langsam und schnell. Eine virtuelle Spalte verschiebt die Auswertung des Ausdrucks in Richtung der Lesevorgänge. Eine persistente Spalte berechnet und speichert den Wert, wenn die Quelldaten geschrieben werden. Speicherbedarf, Schreib- und Lesehäufigkeit, Kosten des Ausdrucks sowie Indizierung beeinflussen gemeinsam das Ergebnis. Ohne eine repräsentative Arbeitslast wäre eine allgemeine Leistungsempfehlung bloße Vermutung.

Vor der Leistungsfrage steht außerdem eine semantische Frage. Wird das Ergebnis selten ausgewählt und ist seine Berechnung günstig, kann eine virtuelle Spalte unnötig gespeicherte Daten vermeiden. Wird der Wert häufig gelesen oder ist der Ausdruck teuer, kann sich eine Prüfung persistenter Speicherung lohnen. In beiden Fällen sollten das tatsächliche Schema und die reale Arbeitslast gemessen werden, statt das Schlüsselwort als Optimierungsschalter zu behandeln.

Ein Index ist möglich, doch der Plan muss geprüft werden

Die aktuelle MariaDB-Dokumentation besagt, dass Indizes sowohl auf VIRTUAL als auch auf PERSISTENT generierten Spalten definiert werden können. Der Optimierer kann einen solchen Index ebenso berücksichtigen wie einen Index auf einer gewöhnlichen Spalte. „Kann berücksichtigen“ ist entscheidend: Ein verfügbarer Index ist keine Zusage, dass jede Abfrage ihn nutzt.

Der Plan sollte für die relevante Abfrageform untersucht werden:

EXPLAIN
SELECT id, quantity, unit_price, line_total
FROM order_lines
WHERE line_total = 39.90;

Die EXPLAIN-Referenz beschreibt Felder wie possible_keys, key, den Zugriffstyp type und die geschätzten rows. Bei der kleinen lokalen Prüfung meldete MariaDB den Zugriffstyp ref mit idx_line_total. Bei lediglich zwei Zeilen zeigt dies nur, dass der Index für diese Abfrage infrage kommt; über Laufzeit oder Nutzen in der Produktion sagt es nichts Brauchbares aus.

EXPLAIN zeigt den vorgesehenen Plan des Optimierers und ist kein Benchmark der Arbeitslast. MariaDBs Anweisung ANALYZE geht weiter, führt die Anweisung aus und ergänzt beobachtete Ausführungsinformationen. Bei realen Daten gehören Planprüfung und Messung zusammen.

Versionsdetails können die Zuordnung eines Ausdrucks verändern

Die Verwendung des Namens der generierten Spalte ist innerhalb verschiedener MariaDB-Versionen die klarste und portabelste Abfrage:

SELECT * FROM order_lines WHERE line_total >= 100.00;

Seit MariaDB 11.8 kann der Optimierer außerdem den exakten Ausdruck hinter einer indizierten virtuellen Spalte in einer WHERE-Klausel erkennen und durch die generierte Spalte ersetzen. Laut Dokumentation des Optimierers muss der Ausdruck exakt der Spaltendefinition entsprechen. Vor Version 11.8 steht diese Ausdruckssubstitution nicht zur Verfügung.

Diese Versionsgrenze ist ein Grund, Annahmen nicht in Anwendungsabfragen zu verstecken. Der Name der generierten Spalte kommuniziert die beabsichtigte Schema-Schnittstelle, funktioniert auch mit älteren Versionen, die die Spalte selbst unterstützen, und vermeidet die Abhängigkeit von der Ausdruckserkennung. Nutzt eine Anwendung diese Substitution bewusst, sollten ihre MariaDB-Mindestversion und die Abfragepläne Teil der Deployment-Prüfungen sein.

Der Ausdruck hat echte Grenzen

Eine generierte Spalte ist für einen Wert gedacht, der aus seiner eigenen Zeile ableitbar ist. MariaDB unterstützt keine Unterabfragen in generierten Ausdrücken, und Daten außerhalb der Zeile sind keine geeignete Abhängigkeit. Gespeicherte Funktionen werden dort ebenfalls nicht unterstützt. Diese Grenzen verhindern, dass eine generierte Spalte zu einer verborgenen Engine für tabellenübergreifende Berechnungen wird.

Die meisten zulässigen deterministischen Ausdrücke und viele eingebaute Funktionen werden unterstützt. Persistente oder indizierte Ergebnisse benötigen jedoch stärkere Konsistenz. MariaDB warnt, dass ein Ausdruck, dessen Ergebnis sich mit relevanten sql_mode-Einstellungen ändert, gespeicherte oder indizierte Werte von einer späteren Auswertung abweichen lassen kann. Die Dokumentation nennt Zeichenauffüllung und vorzeichenlose Subtraktion als konkrete Fälle und empfiehlt bei Bedarf ausdrückliche Operationen wie RTRIM() oder CAST().

Auch strukturelle Einschränkungen bestehen. Eine generierte Spalte kann kein MariaDB-Primärschlüssel sein, obwohl beide Formen gewöhnliche Indizes haben können. Einige Fremdschlüsselkombinationen und manche ALTER TABLE-Operationen unterliegen zusätzlichen Einschränkungen. Deshalb sollte vor einer Migration die genaue Referenz für die eingesetzte MariaDB-Version geprüft werden, nicht erst nachdem ein Entwicklungsschema funktioniert hat.

Wann ein gewöhnlicher Ausdruck besser sein kann

Generierte Spalten reduzieren eine Art von Duplizierung, koppeln die Logik aber auch an das Schema. Ein gewöhnlicher Ausdruck in einer Abfrage oder Anwendung kann klarer sein, wenn:

  • der Wert nur in einem Bericht oder Endpoint vorkommt;
  • sich die Regel häufig ändert und Anwendungs-Releases statt Datenbankmigrationen folgt;
  • die Berechnung von anderen Zeilen, Tabellen, externen Diensten oder dem Request-Kontext abhängt;
  • das Ergebnis die zum Zeitpunkt eines Ereignisses geltende historische Regel bewahren muss;
  • Datenbankportabilität wichtiger ist als eine benannte MariaDB-Schemafunktion.

Der historische Fall verdient besondere Aufmerksamkeit. Angenommen, eine spätere Preisänderung darf die Summe einer alten Rechnung nicht verändern. Ein aus den aktuellen Preisspalten generierter Wert berechnet den aktuellen Ausdruck korrekt neu, kann damit aber genau die falsche historische Bedeutung liefern. Unveränderliche Transaktionsfelder oder ein separates Ledger-Design können ehrlicher sein.

Eine vorsichtige Entscheidungsfolge

  1. Die Regel in Alltagssprache formulieren und entscheiden, ob sie ausschließlich von der aktuellen Zeile abhängt.
  2. Datentypen, Nullverhalten, Rundung und Überlaufverhalten bewusst wählen.
  3. Prüfen, ob der Wert einen stabilen Spaltennamen für mehrere Aufrufer benötigt.
  4. VIRTUAL oder PERSISTENT danach wählen, wo Berechnung und Speicherung stattfinden sollen, nicht nach einem Slogan.
  5. Einen Index nur für ein reales Zugriffsmuster hinzufügen, anschließend EXPLAIN untersuchen und mit repräsentativen Daten messen.
  6. Die Migration mit genau der MariaDB-Version und dem sql_mode des Deployments testen.
  7. Dokumentieren, ob der Wert eine aktuelle Ableitung oder eine historische Tatsache darstellt.

Fazit

Eine generierte MariaDB-Spalte kann einer zeilenbezogenen Regel einen einzigen Ort auf Schemaebene geben. Sie kann verhindern, dass Anwendungspfade widersprüchliche Quell- und abgeleitete Werte schreiben, und einen indizierbaren Namen für einen Ausdruck bereitstellen. Das sind nützliche Eigenschaften, aber kein automatischer Beweis für einen einfacheren oder schnelleren Entwurf.

Die sicherere Entscheidung beginnt bei der Bedeutung: ein stabiler Ausdruck auf Grundlage der aktuellen Zeile, dessen Ergebnis mehreren Abfragen oder Constraints nützt. Erst danach folgen Speicherung, Indizierung, Versionskompatibilität und Messung. Ist die Regel kontextabhängig, historisch oder tabellenübergreifend, kann es der genauere Entwurf sein, sie außerhalb einer generierten Spalte zu belassen.

References