Neues in MariaDB 10.3


Ich möchte mit einer Ankündigung beginnen: MariaDB wird auf modern-sql.com fortan als separate Datenbank behandelt.

Der Grund für die Aufnahme in meinen Club der gängigen SQL-Datenbanken0 ist einfach: Obwohl MariaDB ursprünglich als „Zweig von MySQL, der für Benutzer kompatibel mit der Hauptversion ist,“ beschrieben wurde1, haben sich die beiden Produkte in den letzten Jahren merklich auseinander entwickelt. Anfangs haben die Unterschiede vor allem betriebliche Aspekte (inkl. rechtliche Aspekte) betroffen. In den letzten beiden Jahren kam es aber auch bei den SQL-Dialekten zu nennenswerten Unterschieden. Die getrennte Behandlung von MariaDB ist daher unumgänglich geworden.

Weiters steigt die Popularität von MariaDB kontinuierlich an2 und es scheint, als hätte das MariaDB-Team endlich begonnen den SQL-Standard zur Kenntnis zu nehmen. Eigentlich muss ich sagen, dass sie moderne SQL-Standards zur Kenntnis nehmen – nicht den SQL-92-Standard, der inzwischen sechsfach überholt ist.3

MariaDB 10.3 zeigt das auf beeindruckende Weise. Dieser Artikel erklärt das im Detail.

Inhalt:

  1. Systemversionierte Tabellen
  2. Die Values-Klausel
  3. Sequenzgeneratoren
  4. Percentile_disc und Percentile_cont
  5. Intersect und Except
  6. Zwei-phasen Ausfürung der Update’s Set-Klausel
  7. Selbstreferenzierende Update und Delete-Anweisungen
  8. Am Rande: Limit in Group_Concat
  9. Veranstaltungshinweis: Mein SQL-Training in Wien

Systemversionierte Tabellen

Hattest du jemals die Anforderung, die alten Daten aufzubewahren, wenn du update oder delete verwendest? So ziemlich jede Geschäftsanwendung hat dieses Problem – z. B. bei den Kundenstammdaten. Systemversionierte Tabellen ist eine der Standard-SQL-Funktionen, die das für dich erledigen.4

Der SQL-Standard verwendet rechtsoffene Intervalle5 – gespeichert in „von“ und „bis“ Timestamp-Spalten – um den Gültigkeitszeitraum jeder Zeile darzustellen. Das „System“ in „systemversioniert“ bedeutet, dass diese Zeitstempel von der Datenbank automatisch gesetzt werden, wenn man in eine solche Tabelle schreibt. Die Systemversionierung ist für eine Applikation daher weitgehend transparent.

Die Syntax zum anlegen einer systemversionierten Tabelle ist wie folgt:

CREATE TABLE … (
  [ normale Spalten und Constraints ]
  
, gilt_von TIMESTAMP(6) GENERATED ALWAYS AS ROW START NOT NULL
, gilt_bis TIMESTAMP(6) GENERATED ALWAYS AS ROW END   NOT NULL

, PERIOD FOR SYSTEM_TIME (gilt_von, gilt_bis)
) WITH SYSTEM VERSIONING

Wie du sehen kannst, werden die zwei Spalten für das Intervall explizit angelegt. Diese Spalten sind grundsätzlich normale Spalte und müssen bei select und insert berücksichtigt (ignoriert) werden6. Das ist aber auch schon das einzige, was bei Systemversionierung für die Applikation nicht transparent ist. Als nächstes werden diese beiden Spalten zu einer Periode namens system_time zusammengefasst. Schlussendlich aktiviert die With System Versioning-Klausel den Automatismus.

Hinweis in eigener Sache

Ich lebe von SQL-Schulungen, anderen SQL-Dienstleistungen und dem Verkauf meines Buches. Mehr dazu auf winand.at.

Natürlich kann man Systemversionierung auch für bestehende Tabellen mittels alter aktivieren. Wenn man den Gültigkeitsbereich bereits als rechtsoffenes Intervall gespeichert hat, kann man diese Spalten natürlich verwenden.

Sobald die Systemversionierung aktiviert ist, verwaltet die Datenbank die Spalten der System_time-Periode automatisch. Das bedeutet, dass eine Delete-Anweisung nichts mehr löscht. Sie setzt lediglich das Gültigkeitsende der betroffenen Zeilen auf die Transaktionszeit.7 Update macht das selbe, fügt aber auch eine neue Zeile mit den geänderten Daten und der Transaktionszeit als Gültigkeitsbeginn an. Einmal mehr ist Update nur ein Delete, das von einem Insert gefolgt wird.

Beachte

Die SQL-Standard legt nicht fest, wie die alten Daten physisch gespeichert werden und definiert auch keinen Mechanismus zur Löschung alter Daten.

MariaDB speichert die alten Daten standardmäßig in der selben Tabelle. Die Tabellenpartitionierung wurde jedoch erweitert, sodass man die historische Daten von den aktuellen Daten physisch getrennt speichern kann.

Eine Select-Anweisung auf eine systemversionierte Tabelle liefert standardmäßig nur die aktuellen Daten. Zum Zugriff auf alte Daten wurde die Syntax der From-Klausel erweitert:

FROM <tabelle> FOR SYSTEM_TIME [ AS OF <ts>
                               | BETWEEN <ts> AND <ts>
                               | FROM <ts> TO <ts> ]
          [ [AS] <neuer name> ]

Die neue For System Time-Klausel folgt unmittelbar hinter dem Tabellennamen in der From-Klausel – d. h. ein neuer Name (as <neuer name>) folgt erst danach.

Die As Of-Klausel liefert natürlich die zum angegebenen Zeitpunkt gültigen Daten. Between und From erwarten dagegen jeweils einen Zeitbereich und unterscheiden sich nur darin, ob die Obergrenze inklusive ist (between) oder nicht (from). Die Untergrenze ist in beiden Fällen exklusive.8

Mehr darüber:

Die Values-Klausel

Die Values-Klausel ist vermutlich die grundlegendste SQL-Funktion. Die meisten kennen sie vom Insert-Statement. Die Values-Klausel ist jedoch ein eigenständiges Konzept das auch ohne Insert nützlich ist.

Die Values-Klausel ist die Standard-SQL-Syntax für Select ohne From. Darüber hinaus hat die Values-Klausel den großen Vorteil, dass sie auch mehrere Zeilen auf einmal erzeugen kann:

VALUES (<Spalten der Zeile 1>)
     , (<Spalten der Zeile 2>)
     ,  …

Es ist also nicht nötig, mehrere Select-Abfragen mittels union all zu verbinden.

Im Vergleich zu Select ohne From hat die Values-Klausel aber auch einen Nachteil: man kann den Spalten keinen Namen zuweisen. Dafür muss man entweder auf die From-Klausel oder die With-Klausel zurückgreifen.

SELECT *
  FROM ( VALUES ('a', 'b') ) t(a,b)

Leider unterstützt MariaDB 10.3 die oben gezeigte Benennung in der From-Klausel nicht. Es bleibt also nur die für diesen Fall etwas sperrige With-Klausel.

Ein weiteres Problem ist die mangelnde Unterstützung der Datenbanken. Obwohl MariaDB 10.3 die Values-Klausel nun unterstützt, wird Select ohne From von den getesteten Datenbanken noch immer besser unterstützt.

Mehr darüber:

Sequenzgeneratoren

„Ein Sequenzgenerator ist ein Mechanismus zum generieren fortlaufende, exakter numerischer Werte, einer nach dem anderen“9 Sie ähneln Identitätsspalten, sind jedoch nicht an eine Insert-Anweisung in eine bestimmte Tabelle gebunden so dass sie für beliebige Zwecke verwendet werden können. Der Ausdruck next value for <Sequenzname> liefert den nächsten Wert der Sequenz.

Natürlich kann man diesen Ausdruck in einer selbständigen Values-Anweisung verwenden, um einen neuen Wert zu abzurufen.

VALUES (NEXT VALUE FOR <Sequenzname>)

Mehr darüber:

Percentile_disc und Percentile_cont

Die Funktionen percentile_disc und percentile_cont liefern Perzentilwerte – z. B. den Median – aus einer sortierten Menge von Werten.

Im SQL-Standard erfordern diese Funktionen die Within Group-Klausel und akzeptieren die Over-Klausel optional. In MariaDB 10.3 ist die Over-Klausel jedoch auch zwingend notwendig.

PRECENTILE_DISC(0.5) WITHIN GROUP (ORDER BY x) OVER …

Der Unterschied dieser beiden Funktionen ist der Umgang mit Perzentilwerten die zwischen zwei Zeilen fallen. Zum Beispiel, wenn die mittlere Zeile – mit dem Medianwert – aus vier Zeilen benötigt wird.

00.250.50.7511234PERCENTILE_CONT(0.5)PERCENTILE_DISC(0.5)

Percentile_disc liefert immer einen diskreten Wert aus den Eingangsdaten – selbst wenn das geforderte Perzentil zwischen zwei Zeilen fällt. In diesem Fall wir der Wert der ersten Zeile entsprechend der Order By-Klausel geliefert. Im Gegensatz dazu liefert percentile_cont einen gewichteten Mittelwert der aus den Werten der beiden angrenzenden Zeilen berechnet wird.

MariaDB 10.3 hat auch die proprietäre Funktion median(x) als kürzere Syntax für percentile_disc(0.5) within group (order by x) eingeführt.

Mehr darüber:

Intersect und Except

Intersect und except sind Tabellenoperationen wie union. Anstatt zwei Tabellen aneinander zu hängen, liefern sie die gemeinsamen Zeilen (intersect) respektive die Zeilen der ersten Tabelle reduziert um die der Zweiten (except).

Mehr darüber:

Zwei-phasen Ausfürung der Update’s Set-Klausel

Das lässt sich am besten anhand eines Beispiels erklären. Sieh dir die folgende Abfrage genau an.

UPDATE …
   SET c1 = c2
     , c2 = c1

Beachte, dass beide Spalten auf beiden Seiten des Zuweisungsoperators (=) aufscheinen – beide Spalten werden in der selben Anweisung also gelesen und geändert.

Für diesen Fall definiert der SQL-Standard, dass aus logischer Sicht alle Leseoperationen ausgeführt werden, bevor die Änderungen geschrieben werden. Man kann sich das also wie eine Ausführung in zwei Phasen vorstellen: zuerst eine Phase, in der nur gelesen wird, um die Werte auf der rechten Seite der Zuweisungen zu ermitteln, gefolgt von einer Phase in der diese Werte gespeichert werden. Mit dieser Regel tauscht die Anweisung von oben die Werte der Spalten c1 und c2 aus.

Das verhalten von MySQL und MariaDB (inkl. 10.3) ist in der Standardkonfiguration aber anders. Diese Datenbanken führen die Set-Klausel in der Reihenfolge aus, in der sie in der Anweisung aufscheinen. Daher ist das Ergebnis bei diesen Datenbanken, dass beide Spalten den Wert enthalten, der zuvor in der Spalte c2 gespeichert war.

Der neue SQL-Modus SIMULTANEOUS_ASSIGNMENT von MariaDB 10.3 aktiviert standardkonformes Verhalten für diesen Fall.

SET sql_mode = (SELECT CONCAT( @@sql_mode
                             , ',SIMULTANEOUS_ASSIGNMENT'
                             )
               )

Mehr darüber:

Selbstreferenzierende Update und Delete-Anweisungen

Dieses Thema ist dem vorherigen sehr ähnlich. Der Unterschied ist, dass das es beim vorherigen Thema um zyklische Referenzen zwischen den Spalten einer Zeile ging, wobei es bei diesem Thema um zyklische Referenzen zwischen verschiedenen Zeilen derselben Tabelle geht.

Dieses Problem tritt auf, wenn eine Anweisung, die Daten ändert, auch eine Abfrage enthält, die auf andere Zeilen derselben Tabelle zugreift. Dabei könnten wiederum zyklische Referenz auftreten – diesmal zwischen Zeilen.

Das folgende Beispiel zeigt den einfachsten Fall:

INSERT INTO t1
SELECT *
  FROM t1

Bei dieser Anweisung stellt sich die grundsätzliche Frage, ob der Select-Zweig die Zeilen, die bereits eingefügt wurden, schon sehen kann. Die Antwort lautet natürlich „Nein“ – ansonsten entstünde eine Endlosschleife.

Dasselbe Problem kann auch mit den anderen Anweisungen auftreten, die Daten ändern: update, delete und merge. Auch diese Anweisungen müssen dafür eine Abfrage enthalten, die nochmals auf die bearbeitete Tabelle zugreift – z. B. in exists oder in – ansonsten kann kein Zugriff auf andere Zeilen der selben Tabelle erfolgen und somit auch keine zyklische Referenz entstehen.

Der SQL-Standard löst dieses Problem nach dem selben Muster wie bei der Set-Klausel oben besprochen. Aus logischer Sicht muss die Ausführung in eine Nur-Lese- und eine Nur-Schreib-Phase aufgeteilt werden. Diesmal definiert der Standard dieses Verhalten aber als optionale Erweiterung (F781, “Self-referencing operations”). Datenbanken, die diese Erweiterung nicht unterstützen, müssen bei solchen Anweisungen einen Fehler ausgeben.

Und tatsächlich produzieren MySQL und MariaDB (vor 10.3) eine Fehlermeldung:

You can't specify target table 't1' for update in FROM clause

Diese Meldung lautet übrigens immer auf “update”, auch wenn sie bei einer Delete-Anweisung auftritt. Bei MariaDB 10.0 wurde die Fehlermeldung dann geändert:

Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data

MariaDB 10.3 kann selbstreferenzierende Update- und Delete-Anweisungen nun ausführen.10

Am Rande: Limit in Group_Concat

Group_concat ist keine Standard-SQL-Funktion. Die Standard-Funktion dafür ist nämlich listagg. Eine Neuerung bei group_concat möchte ich dennoch kurz erwähnen, da listagg eine andere Lösung für dasselbe Problem hat: wie Verhindert man das Überschreiten des Längenlimits des Ergebnisses?

Listagg bietet dafür die On Overflow-Klausel, die beim erreichen des Längenlimits keine weiteren Werte mehr anhängt. Bei MariaDB’s group_concat kann man nun in der optionalen Limit-Klausel eine Obergrenze für die Anzahl der zu aggregierenden Werte festlegen.

Veranstaltungshinweis: Mein SQL-Training in Wien

Wenn du diesen Artikel bis hier her gelesen hast, willst du bestimmt noch mehr lernen. Wenn das so ist, wirf bitte einen Blick auf meine nächste Schulung in Wien (17.-21. September).

Die Schulung dauert eine volle Woche und behandelt alle Themen die ich in meiner Schulungs- und Beratungslaufbahn immer wieder klarstellen musste. Das beinhaltet die richtige Indizierung, SQL-Grundlagen, die sich kaum jemand zu fragen traut und moderne SQL-Funktionen wie Rekursionen und Window-Funktionen im Detail.

Sieh dir die Schulung jetzt an ;)

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

Sein Buch bei Amazon kaufen

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.

Sein Training

Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how

Erfahren Sie mehr»

Fußnoten

0

Ich behaupte regelmäßig, dass mein Material für „alle gängigen SQL-Datenbanken“ gilt – z. B. auch am Cover meines Buches SQL Performance Explained.

1

Original “branch of MySQL that is, on the user level, compatible with the main version”. Ursprünglich auf askmonty.org (jetzt auf der Wayback Machine).

2

Im letzten Jahr stieg MariaDB bei db-engines.com in der Kategorie RDBMS vom Platz 11 auf Platz 9 auf.

3

SQL-92 wurde durch die folgenden Standards abgelöst: SQL:1999, SQL:2003, SQL:2006, SQL:2008, SQL:2011 und dem – zum Zeitpunkt des Schreibens – aktuellen Standard SQL:2016.

4

Die andere Funktion ist “Application-time period tables”.

5

Die Untergrenze (gilt von) ist inklusive, die Obergrenze (gilt bis) exklusive. Mathematisch geschrieben: [von, bis).

6

Select * liefert diese Spalten. Insert muss diese Spalten auslassen, oder default verwenden.

Um dieser Widrigkeit zu begegnen, bieten manche Herstelle eine proprietäre Möglichkeit zum verstecken von Spalten an. In MariaDB sind das „unsichtbare Spalten“.

7

Die Transaktionszeit ist innerhalb einer Transaktion unveränderlich, damit alle Änderungen einer Transaktion auch im Nachhinein als gleichzeitig durchgeführt erscheinen.

8

Im Gegensatz zum Between-Prädikat, bei dem beide Grenzen inklusive sind.

9

Der original Wortlaut in SQL:2016, Part 2, §4.27.1: “A sequence generator is a mechanism for generating successive exact numeric values, one at a time.”

10

Es scheint, als könnten bestimmte Insert-Anweisungen (z. B. mit Aggregatfunktionen auf der Zieltabelle) diesen Fehler noch immer auslösen.

„modern SQL“ 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