Hochleistungs-MySQL, 4. Auflage

Kapitel 1. MySQL-Architektur

Diese Arbeit wurde mithilfe von KI übersetzt. Wir freuen uns über dein Feedback und deine Kommentare: [e-mail]

Die architektonischen Merkmale von MySQL machen es für eine breite Palette von Zwecken nützlich. Obwohl es nicht perfekt ist, ist es flexibel genug, um sowohl in kleinen als auch in großen Umgebungen gut zu funktionieren. Diese reichen von einer persönlichen Website bis hin zu großen Unternehmensanwendungen. Um das Beste aus MySQL herauszuholen, musst du sein Design verstehen, damit du mit ihm und nicht gegen es arbeiten kannst.

Dieses Kapitel gibt einen Überblick über die Architektur des MySQL-Servers, die wichtigsten Unterschiede zwischen den Speicher-Engines und warum diese Unterschiede wichtig sind. Wir haben versucht, MySQL zu erklären, indem wir die Details vereinfacht haben und Beispiele zeigen. Diese Diskussion ist sowohl für diejenigen nützlich, die neu auf dem Gebiet der Datenbankserver sind, als auch für Leser, die sich mit anderen Datenbankservern auskennen.

Die logische Architektur von MySQL

Ein gutes mentales Bild davon, wie die Komponenten von MySQL zusammenarbeiten, hilft dir, den Server zu verstehen. Abbildung 1-1 zeigt einen logischen Überblick über die Architektur von MySQL.

Die oberste Schicht, die Clients, enthält die Dienste, die nicht nur für MySQL gelten. Es sind Dienste, die die meisten netzwerkbasierten Client/Server-Tools oder Server benötigen: Verbindungsmanagement, Authentifizierung, Sicherheit und so weiter.

In der zweiten Schicht wird es dann interessant. Ein Großteil des Gehirns von MySQL befindet sich hier, einschließlich des Codes für das Parsen von Abfragen, die Analyse, die Optimierung und alle eingebauten Funktionen (z. B. Daten, Zeiten, Mathematik und Verschlüsselung). Alle Funktionen, die über Speicher-Engines bereitgestellt werden, befinden sich auf dieser Ebene: Stored Procedures, Triggers und Views zum Beispiel.

Die dritte Schicht enthält die Speicher-Engines. Sie sind für das Speichern und Abrufen aller Daten zuständig, die "in" MySQL gespeichert sind. Wie die verschiedenen Dateisysteme, die für GNU/Linux verfügbar sind, hat auch jede Speicherung ihre eigenen Vor- und Nachteile. Der Server kommuniziert mit ihnen über die API der Speicher-Engine. Diese API verbirgt die Unterschiede zwischen den Speicher-Engines und macht sie auf der Abfrageebene weitgehend transparent. Sie enthält außerdem ein paar Dutzend Low-Level-Funktionen, die Vorgänge wie "eine Transaktion beginnen" oder "die Zeile mit diesem Primärschlüssel abrufen" ausführen. Die Speicher-Engines parsen kein SQL¹ oder kommunizieren miteinander; sie beantworten lediglich Anfragen des Servers.

Abbildung 1-1. Eine logische Ansicht der MySQL-Serverarchitektur

Verbindungsmanagement und Sicherheit

Unter erhält jede Client-Verbindung standardmäßig einen eigenen Thread innerhalb des Server-Prozesses. Die Abfragen der Verbindung werden in diesem einzelnen Thread ausgeführt, der wiederum auf einem Kern oder einer CPU läuft. Der Server verwaltet einen Zwischenspeicher mit einsatzbereiten Threads, damit sie nicht für jede neue Verbindung neu erstellt und gelöscht werden müssen.²

Wenn sich Clients (Anwendungen) mit dem MySQL-Server verbinden, muss der Server sie authentifizieren. Die Authentifizierung basiert auf dem Benutzernamen, dem ursprünglichen Host und dem Passwort. X.509-Zertifikate können auch über eine Transport Layer Security (TLS) Verbindung verwendet werden. Sobald ein Client eine Verbindung hergestellt hat, überprüft der Server, ob der Client für jede Abfrage, die er stellt, über die entsprechenden Berechtigungen verfügt (z. B. ob der Client eine SELECT -Anweisung stellen darf, die auf die Tabelle Country in der Datenbank world zugreift).

Optimierung und Ausführung

MySQL parst Abfragen, um eine interne Struktur (den Parse-Baum) zu erstellen und wendet dann eine Reihe von Optimierungen an. Dazu gehören das Umschreiben der Abfrage, die Festlegung der Reihenfolge, in der die Tabellen gelesen werden, die Auswahl der zu verwendenden Indizes usw. Du kannst dem Optimierer durch spezielle Schlüsselwörter in der Abfrage Hinweise geben, die seinen Entscheidungsprozess beeinflussen. Du kannst den Server auch auffordern, verschiedene Aspekte der Optimierung zu erklären. So erfährst du, welche Entscheidungen der Server trifft, und hast einen Anhaltspunkt für die Überarbeitung von Abfragen, Schemas und Einstellungen, damit alles so effizient wie möglich läuft. Mehr dazu findest du in Kapitel 8.

Dem Optimierer ist es eigentlich egal, welche Speicher-Engine eine bestimmte Tabelle verwendet, aber die Speicher-Engine beeinflusst, wie der Server die Abfrage optimiert. Der Optimierer fragt die Speicher-Engine nach einigen ihrer Fähigkeiten und den Kosten bestimmter Operationen sowie nach Statistiken über die Tabellendaten. Einige Speicher-Engines unterstützen zum Beispiel Index-Typen, die bei bestimmten Abfragen hilfreich sein können. Mehr über Schemaoptimierung und Indexierung erfährst du in den Kapiteln 6 und 7.

In älteren Versionen nutzte MySQL einen internen Abfrage-Cache, um zu sehen, ob es die Ergebnisse von dort liefern konnte. Mit zunehmender Parallelität wurde der Query Cache jedoch zu einem Engpass. Seit MySQL 5.7.20 ist der Abfrage-Cache offiziell veraltet, und in der Version 8.0 wurde der Abfrage-Cache ganz abgeschafft. Auch wenn der Abfrage-Cache kein Kernbestandteil des MySQL-Servers mehr ist, ist das Zwischenspeichern häufig genutzter Ergebnismengen eine gute Praxis. Ein beliebtes Entwurfsmuster ist das Zwischenspeichern von Daten in Memcached oder Redis, auch wenn dies nicht in den Rahmen dieses Buches passt.

Gleichzeitigkeitskontrolle

Jedes Mal, wenn mehr als eine Abfrage gleichzeitig Daten ändern muss, stellt sich das Problem der Gleichzeitigkeitskontrolle. Für unsere Zwecke in diesem Kapitel muss MySQL dies auf zwei Ebenen tun: auf der Serverebene und auf der Ebene der Speicherung. Wir geben dir einen vereinfachten Überblick darüber, wie MySQL mit gleichzeitigen Lesern und Schreibern umgeht, damit du den Kontext hast, den du für den Rest des Kapitels brauchst.

Um zu veranschaulichen, wie MySQL die gleichzeitige Arbeit an ein und demselben Datensatz handhabt, verwenden wir eine herkömmliche Tabellenkalkulationsdatei als Beispiel. Eine Tabellenkalkulation besteht aus Zeilen und Spalten, ähnlich wie eine Datenbanktabelle. Angenommen, die Datei befindet sich auf deinem Laptop und nur du hast Zugriff darauf. Es gibt keine potenziellen Konflikte; nur du kannst Änderungen an der Datei vornehmen. Jetzt stell dir vor, du musst mit einem Kollegen an dieser Tabelle arbeiten. Sie befindet sich jetzt auf einem gemeinsamen Server, auf den ihr beide Zugriff habt. Was passiert, wenn ihr beide zur gleichen Zeit Änderungen an dieser Datei vornehmen müsst? Was ist, wenn ein ganzes Team von Leuten aktiv versucht, Zellen in dieser Tabelle zu bearbeiten, hinzuzufügen oder zu entfernen? Wir können sagen, dass sie sich bei den Änderungen abwechseln sollen, aber das ist nicht effizient. Wir brauchen einen Ansatz, der den gleichzeitigen Zugriff auf ein Tabellenblatt mit hohem Volumen ermöglicht.

Lesen/Schreiben Sperren

Das Lesen von aus der Tabellenkalkulation ist nicht so problematisch. Es ist nicht schlimm, wenn mehrere Clients gleichzeitig dieselbe Datei lesen; da sie keine Änderungen vornehmen, wird wahrscheinlich nichts schiefgehen. Was passiert, wenn jemand versucht, die Zelle Nummer A25 zu löschen, während andere die Tabelle lesen? Das hängt davon ab, aber ein Leser könnte eine beschädigte oder inkonsistente Ansicht der Daten erhalten. Um sicherzugehen, muss man also auch beim Lesen einer Tabelle besonders vorsichtig sein.

Wenn du dir die Tabellenkalkulation als Datenbanktabelle vorstellst, ist es leicht zu erkennen, dass das Problem in diesem Zusammenhang dasselbe ist. In vielerlei Hinsicht ist eine Tabellenkalkulation eigentlich nur eine einfache Datenbanktabelle. Das Ändern von Zeilen in einer Datenbanktabelle ist dem Entfernen oder Ändern des Inhalts von Zellen in einer Tabellenkalkulationsdatei sehr ähnlich.

Die Lösung für dieses klassische Problem der Gleichzeitigkeitskontrolle ist recht einfach. Systeme, die mit gleichzeitigem Lese- und Schreibzugriff arbeiten, implementieren in der Regel ein Sperrsystem, das aus zwei Sperrtypen besteht. Diese Sperren werden in der Regel als Lesesperren und Schreibsperren oder als Shared Locks und Exclusive Locks bezeichnet.

Ohne uns über den eigentlichen Sperrmechanismus Gedanken zu machen, können wir das Konzept wie folgt beschreiben. Lesesperren für eine Ressource werden gemeinsam genutzt, d. h. sie blockieren sich nicht gegenseitig: Viele Clients können gleichzeitig von einer Ressource lesen, ohne sich gegenseitig zu behindern. Schreibsperren auf hingegen sind exklusiv, d. h. sie blockieren sowohl Lesesperren als auch andere Schreibsperren, weil nur ein einziger Client zu einem bestimmten Zeitpunkt auf die Ressource schreiben kann und alle Lesevorgänge verhindert werden, wenn ein Client gerade schreibt.

In der Datenbankwelt kommt das Sperren ständig vor: MySQL muss verhindern, dass ein Client einen Teil der Daten liest, während ein anderer sie ändert. Wenn ein Datenbankserver eine akzeptable Leistung erbringt, ist diese Verwaltung von Sperren schnell genug, um von den Clients nicht bemerkt zu werden. In Kapitel 8 werden wir besprechen, wie du deine Abfragen so abstimmst, dass Leistungsprobleme aufgrund von Sperren vermieden werden.

Granularität sperren

Eine Möglichkeit, die Gleichzeitigkeit einer gemeinsam genutzten Ressource zu verbessern, besteht darin, bei der Sperrung selektiver vorzugehen. Anstatt die gesamte Ressource zu sperren, solltest du nur den Teil sperren, der die Daten enthält, die du ändern willst. Noch besser ist es, wenn du nur genau die Daten sperrst, die du ändern willst. Wenn du die Menge der Daten, die du gleichzeitig sperrst, so gering wie möglich hältst, können Änderungen an einer bestimmten Ressource gleichzeitig vorgenommen werden, solange sie sich nicht gegenseitig behindern.

Leider sind Sperren nicht kostenlos - sie verbrauchen Ressourcen. Jede Sperre - das Anfordern einer Sperre, das Prüfen, ob eine Sperre frei ist, das Freigeben einer Sperre usw. - verursacht einen Overhead. Wenn das System zu viel Zeit damit verbringt, Sperren zu verwalten, anstatt Daten zu speichern und abzurufen, kann die Leistung darunter leiden.

Eine Sperrstrategie ist ein Kompromiss zwischen Sperr-Overhead und Datensicherheit, und dieser Kompromiss wirkt sich auf die Leistung aus. Die meisten kommerziellen Datenbankserver lassen dir keine große Wahl: Du bekommst das sogenannte Row-Level-Locking in deinen Tabellen, mit einer Vielzahl von oft komplexen Möglichkeiten, um mit vielen Sperren eine gute Leistung zu erzielen. Sperren sind die Art und Weise, wie Datenbanken Konsistenzgarantien umsetzen. Ein erfahrener Betreiber einer Datenbank müsste sogar den Quellcode lesen, um die am besten geeigneten Tuning-Konfigurationen zu finden, die diesen Kompromiss zwischen Geschwindigkeit und Datensicherheit optimieren.

MySQL hingegen bietet Wahlmöglichkeiten. Seine Speicher-Engines können ihre eigenen Sperrrichtlinien und Sperrgranularitäten implementieren. Die Verwaltung von Sperren ist eine sehr wichtige Entscheidung bei der Entwicklung von Speicher-Engines; die Festlegung der Granularität auf eine bestimmte Ebene kann die Leistung für bestimmte Zwecke verbessern, macht die Engine aber für andere Zwecke weniger geeignet. Da MySQL mehrere Speicher-Engines anbietet, braucht es nicht nur eine einzige Allzwecklösung. Werfen wir einen Blick auf die beiden wichtigsten Sperrstrategien.

Tischschlösser

Die grundlegendste Sperrstrategie, die in MySQL verfügbar ist, und diejenige mit dem geringsten Overhead, sind Tabellensperren. Eine Tabellensperre ist vergleichbar mit den zuvor beschriebenen Spreadsheet-Sperren: Sie sperrt die gesamte Tabelle. Wenn ein Client in eine Tabelle schreiben will (einfügen, löschen, aktualisieren usw.), erhält er eine Schreibsperre. Dadurch werden alle anderen Lese- und Schreibvorgänge unterbunden. Wenn niemand schreibt, können Leser Lesesperren erhalten, die nicht mit anderen Lesesperren kollidieren.

Es gibt verschiedene Varianten von Tabellensperren, um die Leistung in bestimmten Situationen zu verbessern. Zum Beispiel erlauben READ LOCAL Tabellensperren einige Arten von gleichzeitigen Schreiboperationen. Die Warteschlangen für Schreib- und Lesesperren sind getrennt, wobei die Warteschlange für Schreibvorgänge insgesamt eine höhere Priorität hat als die Warteschlange für Lesevorgänge.³

Reihenschlösser

Der Sperrstil, der die größte Gleichzeitigkeit bietet (und den größten Overhead verursacht), ist die Verwendung von Zeilensperren. Um auf den Vergleich mit der Tabellenkalkulation zurückzukommen: Zeilensperren sind dasselbe wie das Sperren einer Zeile in der Tabellenkalkulation. Diese Strategie ermöglicht es mehreren Personen, gleichzeitig verschiedene Zeilen zu bearbeiten, ohne sich gegenseitig zu blockieren. Das ermöglicht dem Server mehr gleichzeitige Schreibvorgänge, aber der Preis dafür ist ein größerer Aufwand, weil er sich merken muss, wer welche Zeilensperren hat, wie lange sie geöffnet waren und um welche Art von Zeilensperren es sich handelt.

Zeilensperren werden in der Speicher-Engine implementiert, nicht im Server. Der Server weiß meist⁴ Der Server weiß meist nichts von den Sperren, die in den Speicher-Engines implementiert sind, und wie du später in diesem Kapitel und im gesamten Buch sehen wirst, implementieren die Speicher-Engines die Sperren alle auf ihre eigene Weise.

Transaktionen

Du kannst dich nicht lange mit den fortgeschrittenen Funktionen eines Datenbanksystems beschäftigen, bevor Transaktionen ins Spiel kommen. Eine Transaktion ist eine Gruppe von SQL-Anweisungen, die atomar, also als eine einzige Arbeitseinheit, behandelt werden. Wenn die Datenbank-Engine die gesamte Gruppe von Anweisungen auf eine Datenbank anwenden kann, tut sie das auch, aber wenn eine von ihnen wegen eines Absturzes oder aus anderen Gründen nicht ausgeführt werden kann, wird keine von ihnen angewendet. Es geht um alles oder nichts.

Dieser Abschnitt ist nur wenig spezifisch für MySQL. Wenn du dich bereits mit ACID-Transaktionen auskennst, kannst du gerne zu "Transaktionen in MySQL" weiterblättern .

Eine Bankanwendung ist das klassische Beispiel dafür, warum Transaktionen notwendig sind.⁵ Stell dir die Datenbank einer Bank mit zwei Tabellen vor: Giro- und Sparkonto. Um 200 Dollar von Janes Girokonto auf ihr Sparkonto zu überweisen, musst du mindestens drei Schritte ausführen:

1. Vergewissere dich, dass ihr Guthaben auf dem Girokonto größer als 200 $ ist.

2. Ziehe 200 $ von ihrem Girokontostand ab.

3. Füge 200 $ zu ihrem Sparkonto hinzu.

Der gesamte Vorgang sollte in eine Transaktion eingeschlossen werden, so dass, wenn einer der Schritte fehlschlägt, alle abgeschlossenen Schritte rückgängig gemacht werden können.

Du startest eine Transaktion mit der Anweisung START TRANSACTION und machst dann entweder die Änderungen mit COMMIT dauerhaft oder verwirfst sie mit ROLLBACK. Die SQL-Anweisung für unsere Beispieltransaktion könnte also wie folgt aussehen:

1  START  TRANSACTION;
2  SELECT balance FROM checking WHERE customer_id = 10233276;
3  UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
4  UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
5  COMMIT;

Transaktionen allein sind nicht die ganze Geschichte. Was passiert, wenn der Datenbankserver während der Ausführung von Zeile 4 abstürzt? Wer weiß? Der Kunde hat wahrscheinlich gerade 200 Dollar verloren. Was ist, wenn zwischen Zeile 3 und 4 ein anderer Vorgang auftritt und den gesamten Girokontostand entfernt? Die Bank hat dem Kunden 200 Dollar gutgeschrieben, ohne es zu wissen.

Und es gibt noch viel mehr Fehlermöglichkeiten in dieser Abfolge von Vorgängen. Es kann zu Verbindungsabbrüchen, Zeitüberschreitungen oder sogar zu einem Absturz des Datenbankservers kommen, der die Operationen ausführt. Aus diesem Grund gibt es in der Regel hochkomplexe und langsame Zwei-Phasen-Commit-Systeme: um alle möglichen Fehlerszenarien abzufedern.

Transaktionen sind nicht genug, wenn das System den ACID-Test nicht besteht. ACID steht für Atomarität, Konsistenz, Isolation und Haltbarkeit. Dies sind eng miteinander verbundene Kriterien, die ein datensicheres Transaktionsverarbeitungssystem erfüllen muss:

Atomarität

Eine Transaktion muss als eine einzige, unteilbare Arbeitseinheit funktionieren, so dass die gesamte Transaktion entweder ausgeführt oder nie übertragen wird. Wenn Transaktionen atomar sind, gibt es keine teilweise abgeschlossene Transaktion: Es geht um alles oder nichts.

Konsistenz

Die Datenbank sollte immer von einem konsistenten Zustand in den nächsten übergehen. In unserem Beispiel sorgt die Konsistenz dafür, dass ein Absturz zwischen den Zeilen 3 und 4 nicht dazu führt, dass 200 Dollar vom Girokonto verschwinden. Wenn die Transaktion nie bestätigt wird, werden die Änderungen der Transaktion nie in die Datenbank übernommen.

Isolierung

Die Ergebnisse einer Transaktion sind normalerweise für andere Transaktionen unsichtbar, bis die Transaktion abgeschlossen ist. Dadurch wird sichergestellt, dass eine Kontoübersicht, die in unserem Beispiel nach Zeile 3, aber vor Zeile 4 ausgeführt wird, trotzdem die 200 US-Dollar auf dem Girokonto sieht. Wenn wir später in diesem Kapitel die Isolationsebenen besprechen, wirst du verstehen, warum wir "normalerweise unsichtbar" gesagt haben.

Langlebigkeit

Sobald eine Transaktion bestätigt wurde, sind die Änderungen dauerhaft. Das bedeutet, dass die Änderungen so gespeichert werden müssen, dass die Daten bei einem Systemabsturz nicht verloren gehen. Dauerhaftigkeit ist jedoch ein etwas unscharfes Konzept, denn es gibt tatsächlich viele Stufen. Manche Haltbarkeitsstrategien bieten eine stärkere Sicherheitsgarantie als andere, und nichts ist jemals zu 100 % haltbar (wenn die Datenbank selbst wirklich haltbar wäre, wie könnten dann Backups die Haltbarkeit erhöhen?).

ACID-Transaktionen und die Garantien, die sie in der InnoDB-Engine bieten, sind eine der stärksten und ausgereiftesten Funktionen von MySQL. Sie bringen zwar gewisse Kompromisse beim Durchsatz mit sich, aber wenn sie richtig eingesetzt werden, ersparen sie dir die Implementierung einer Menge komplexer Logik in der Anwendungsschicht.

Isolationsstufen

Isolation ist komplexer als es aussieht. Der ANSI SQL-Standard definiert vier Isolationsstufen. Wenn du neu in der Welt der Datenbanken bist, empfehlen wir dir dringend, dich mit dem allgemeinen Standard von ANSI SQL⁶ vertraut zu machen, bevor du dich mit der speziellen MySQL-Implementierung beschäftigst. Das Ziel dieses Standards ist es, die Regeln dafür festzulegen, welche Änderungen innerhalb und außerhalb einer Transaktion sichtbar sind und welche nicht. Niedrigere Isolationsstufen ermöglichen in der Regel eine höhere Gleichzeitigkeit und haben einen geringeren Overhead.

Hinweis

Jede Speicher-Engine implementiert die Isolationsebenen etwas anders und sie entsprechen nicht unbedingt dem, was du erwarten würdest, wenn du ein anderes Datenbankprodukt verwendest (daher gehen wir in diesem Abschnitt nicht ausführlich darauf ein). Du solltest die Handbücher der Speicher-Engines lesen, die du verwenden möchtest.

Werfen wir einen kurzen Blick auf die vier Isolationsstufen:

READ UNCOMMITTED

In der Isolationsebene READ UNCOMMITTED können Transaktionen die Ergebnisse von unbestätigten Transaktionen einsehen. Auf dieser Ebene können viele Probleme auftreten, es sei denn, du weißt genau, was du tust, und hast einen guten Grund dafür, es zu tun. Diese Stufe wird in der Praxis nur selten verwendet, weil ihre Leistung nicht viel besser ist als die der anderen Stufen, die viele Vorteile haben. Das Lesen von unbestätigten Daten wird auch als Dirty Read bezeichnet.

READ COMMITTED

Der Standard-Isolation-Level für die meisten Datenbanksysteme (aber nicht für MySQL!) ist READ COMMITTED. Er entspricht der einfachen Definition von Isolation, die weiter oben verwendet wurde: Eine Transaktion sieht weiterhin Änderungen, die von Transaktionen vorgenommen wurden, die nach ihrem Beginn committed wurden, und ihre Änderungen sind für andere nicht sichtbar, bis sie committed wurde. Diese Stufe erlaubt immer noch ein sogenanntes nicht wiederholbares Lesen. Das bedeutet, dass du dieselbe Anweisung zweimal ausführen kannst und unterschiedliche Daten siehst.

REPEATABLE READ

REPEATABLE READ löst die Probleme, die READ UNCOMMITTED zulässt. Es garantiert, dass alle Zeilen, die eine Transaktion liest, in den nachfolgenden Lesevorgängen innerhalb derselben Transaktion "gleich aussehen", aber theoretisch lässt es noch ein anderes kniffliges Problem zu: Phantom Read. Einfach ausgedrückt, kann ein Phantom-Read entstehen, wenn du einen Bereich von Zeilen auswählst, eine andere Transaktion eine neue Zeile in den Bereich einfügt und du dann denselben Bereich erneut auswählst; dann siehst du die neue "Phantom"-Zeile. InnoDB und XtraDB lösen das Problem des Phantomlesens mit der Multiversions-Gleichzeitigkeitskontrolle, die wir später in diesem Kapitel erklären.

REPEATABLE READ ist die Standard-Transaktionsisolationsebene von MySQL.

SERIALIZABLE

Die höchste Isolationsebene, SERIALIZABLE, löst das Phantomleseproblem, indem sie die Reihenfolge der Transaktionen erzwingt, so dass sie nicht in Konflikt geraten können. Kurz gesagt, SERIALIZABLE setzt eine Sperre auf jede Zeile, die gelesen wird. Auf dieser Ebene kann es zu vielen Timeouts und Sperrkonflikten kommen. Wir haben nur selten gesehen, dass diese Isolationsebene verwendet wird, aber die Anforderungen deiner Anwendung könnten dich dazu zwingen, die geringere Gleichzeitigkeit zugunsten der Datensicherheit zu akzeptieren, die sich daraus ergibt.

Tabelle 1-1 fasst die verschiedenen Isolationsstufen und die mit jeder Stufe verbundenen Nachteile zusammen.

Tabelle 1-1. ANSI SQL Isolationsstufen

Isolationsgrad	Dirty Lies möglich	Nicht-wiederholbare Lesungen möglich	Phantomlesungen möglich	Sperren liest
READ UNCOMMITTED	Ja	Ja	Ja	Nein
READ COMMITTED	Nein	Ja	Ja	Nein
REPEATABLE READ	Nein	Nein	Ja	Nein
SERIALIZABLE	Nein	Nein	Nein	Ja

Deadlocks

Ein Deadlock liegt vor, wenn zwei oder mehr Transaktionen gegenseitig Sperren für dieselben Ressourcen halten und anfordern, wodurch ein Kreislauf von Abhängigkeiten entsteht. Deadlocks entstehen, wenn Transaktionen versuchen, Ressourcen in einer anderen Reihenfolge zu sperren. Sie können immer dann auftreten, wenn mehrere Transaktionen dieselben Ressourcen sperren. Betrachten wir zum Beispiel diese beiden Transaktionen, die gegen eine Tabelle StockPrice laufen, deren Primärschlüssel (stock_id, date) lautet:

Transaktion 1

START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = ‘2020-05-01’;
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = ‘2020-05-02’;
COMMIT;

Transaktion 2

START TRANSACTION;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = ‘2020-05-02’;
UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 and date = ‘2020-05-01’;
COMMIT;

Jede Transaktion führt ihre erste Abfrage aus und aktualisiert eine Datenzeile. Dabei wird diese Zeile im Primärschlüsselindex und in jedem weiteren eindeutigen Index, zu dem sie gehört, gesperrt. Jede Transaktion versucht dann, ihre zweite Zeile zu aktualisieren, muss aber feststellen, dass diese bereits gesperrt ist. Die beiden Transaktionen werden ewig warten, bis sie abgeschlossen sind, es sei denn, es wird etwas unternommen, um den Deadlock zu lösen. In Kapitel 7 wird erläutert, wie die Indexierung die Leistung deiner Abfragen beeinflussen kann, wenn sich dein Schema weiterentwickelt.

Um dieses Problem zu bekämpfen, setzen Datenbanksysteme verschiedene Formen der Deadlock-Erkennung und Timeouts ein. Hochentwickelte Systeme, wie die InnoDB Speicherung, erkennen zirkuläre Abhängigkeiten und melden sofort einen Fehler. Das kann eine gute Sache sein - andernfalls würden sich Deadlocks in sehr langsamen Abfragen äußern. Andere geben auf, wenn die Abfrage eine Sperrzeit überschreitet, was nicht immer gut ist. Die Art und Weise, wie InnoDB derzeit mit Deadlocks umgeht, besteht darin, dass die Transaktion mit den wenigsten exklusiven Zeilensperren zurückgesetzt wird (ein ungefährer Maßstab dafür, welche Transaktion am einfachsten zurückgesetzt werden kann).

Das Verhalten und die Reihenfolge von Sperren sind spezifisch für die Speicher-Engine, so dass einige Speicher-Engines bei einer bestimmten Anweisungssequenz blockieren können, während andere dies nicht tun. Es gibt zwei Arten von Deadlocks: Einige sind unvermeidbar, weil es echte Datenkonflikte gibt, und andere werden durch die Funktionsweise einer Speicher-Engine verursacht.⁷

Einmal eingetretene Deadlocks können nicht aufgelöst werden, ohne eine der Transaktionen ganz oder teilweise rückgängig zu machen. Sie sind eine Tatsache in transaktionalen Systemen, und deine Anwendungen sollten so konzipiert sein, dass sie damit umgehen können. Viele Anwendungen können ihre Transaktionen einfach von Anfang an wiederholen und sollten erfolgreich sein, solange sie nicht auf einen weiteren Deadlock stoßen.

Transaktionsprotokollierung

Die Transaktionsprotokollierung hilft, Transaktionen effizienter zu gestalten. Anstatt die Tabellen auf der Festplatte bei jeder Änderung zu aktualisieren, kann die Speicherung ihre In-Memory-Kopie der Daten ändern. Das ist sehr schnell. Die Speicherung kann dann einen Eintrag in das Transaktionsprotokoll schreiben, das sich auf der Festplatte befindet und daher dauerhaft ist. Auch dies ist ein relativ schneller Vorgang, da das Anhängen von Log-Ereignissen eine sequentielle E/A in einem kleinen Bereich der Festplatte erfordert und nicht eine zufällige E/A an vielen Stellen. Zu einem späteren Zeitpunkt kann dann ein Prozess die Tabelle auf der Festplatte aktualisieren. Daher schreiben die meisten Speicher-Engines, die diese Technik verwenden (auch bekannt als Write-Ahead-Logging), die Änderungen zweimal auf die Festplatte.

Wenn es zu einem Absturz kommt, nachdem die Aktualisierung in das Transaktionsprotokoll geschrieben wurde, aber bevor die Änderungen an den Daten selbst vorgenommen wurden, kann die Speicher-Engine die Änderungen beim Neustart wiederherstellen. Die Wiederherstellungsmethode variiert zwischen den einzelnen Speicher-Engines.

Transaktionen in MySQL

Speicher-Engines sind die Software, die bestimmt, wie die Daten gespeichert und von der Festplatte abgerufen werden. Während MySQL traditionell eine Reihe von Speicher-Engines anbietet, die Transaktionen unterstützen, ist InnoDB heute der Goldstandard und die empfohlene Engine. Die hier beschriebenen Transaktionsprimitive basieren auf Transaktionen in der InnoDB-Engine.

AUTOCOMMIT verstehen

In der Voreinstellung wird eine einzelne INSERT, UPDATE oder DELETE Anweisung implizit in eine Transaktion eingeschlossen und sofort übertragen. Dies wird als AUTOCOMMIT Modus bezeichnet. Wenn du diesen Modus deaktivierst, kannst du eine Reihe von Anweisungen innerhalb einer Transaktion ausführen und am Ende COMMIT oder ROLLBACK.

Du kannst die Variable AUTOCOMMIT für die aktuelle Verbindung aktivieren oder deaktivieren, indem du den Befehl SET verwendest. Die Werte 1 und ON sind gleichwertig, ebenso wie 0 und OFF. Wenn du mit AUTOCOMMIT=0 startest, befindest du dich immer in einer Transaktion, bis du COMMIT oder ROLLBACK eingibst. MySQL startet dann sofort eine neue Transaktion. Wenn du AUTOCOMMIT aktivierst, kannst du außerdem eine Multistatement-Transaktion mit dem Schlüsselwort BEGIN oder START TRANSACTION starten. Die Änderung des Wertes von AUTOCOMMIT hat keine Auswirkungen auf nicht-transaktionale Tabellen, bei denen es keine Möglichkeit gibt, Änderungen zu bestätigen oder zurückzunehmen.

Bestimmte Befehle, die während einer offenen Transaktion eingegeben werden, führen dazu, dass MySQL die Transaktion festschreibt, bevor sie ausgeführt werden. Dabei handelt es sich in der Regel um DDL-Befehle, die erhebliche Änderungen vornehmen, wie ALTER TABLE , aber auch LOCK TABLES und einige andere Anweisungen haben diesen Effekt. In der Dokumentation deiner Version findest du eine vollständige Liste der Befehle, die eine Transaktion automatisch festschreiben.

In MySQLkannst du den Isolation-Level mit dem Befehl SET TRANSACTION ISOLATION LEVEL einstellen, der beim Start der nächsten Transaktion wirksam wird. Du kannst die Isolationsebene für den gesamten Server in der Konfigurationsdatei oder nur für deine Sitzung festlegen:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Es ist besser, die Isolation, die du am häufigsten verwendest, auf Serverebene festzulegen und nur in bestimmten Fällen zu ändern. MySQL erkennt alle vier ANSI-Standard-Isolationsstufen, und InnoDB unterstützt alle davon.

Vermischung von Speichermedien in Transaktionen

MySQL verwaltet Transaktionen nicht auf der Serverebene. Stattdessen implementieren die zugrunde liegenden Speicher-Engines die Transaktionen selbst. Das bedeutet, dass du nicht zuverlässig verschiedene Engines in einer einzigen Transaktion mischen kannst.

Wenn du transaktionale und nichttransaktionale Tabellen (z. B. InnoDB- und MyISAM-Tabellen) in einer Transaktion mischst, funktioniert die Transaktion ordnungsgemäß, wenn alles gut läuft. Wenn jedoch ein Rollback erforderlich ist, können die Änderungen an der nichttransaktionalen Tabelle nicht rückgängig gemacht werden. Dadurch gerät die Datenbank in einen inkonsistenten Zustand, aus dem sie nur schwer wiederhergestellt werden kann, und der ganze Sinn von Transaktionen wird zunichte gemacht. Deshalb ist es so wichtig, dass du für jede Tabelle die richtige Speicherung auswählst und es auf jeden Fall vermeidest, Speicherungen in deiner Anwendungslogik zu mischen.

MySQL gibt normalerweise keine Warnung oder Fehlermeldung aus, wenn du Transaktionsoperationen an einer nicht-transaktionalen Tabelle durchführst. Manchmal wird bei der Rückgängigmachung einer Transaktion die Warnung "Einige nicht-transaktionale geänderte Tabellen konnten nicht rückgängig gemacht werden" ausgegeben, aber in den meisten Fällen gibt es keinen Hinweis darauf, dass du mit nicht-transaktionalen Tabellen arbeitest.

Warnung

Es ist die bewährte Methode, in deiner Anwendung keine Speichermedien zu mischen. Fehlgeschlagene Transaktionen können zu inkonsistenten Ergebnissen führen, da einige Teile ein Rollback durchführen können und andere nicht.

Implizites und explizites Sperren

InnoDB verwendet ein zweiphasiges Sperrprotokoll. Es kann während einer Transaktion jederzeit Sperren erwerben, gibt sie aber erst nach einer COMMIT oder ROLLBACK wieder frei. Es gibt alle Sperren gleichzeitig frei. Die zuvor beschriebenen Sperrmechanismen sind alle implizit. InnoDB verwaltet Sperren automatisch, je nach Isolationsebene.

Allerdings unterstützt InnoDB auch explizites Sperren, was im SQL-Standard überhaupt nicht erwähnt wird:^{8, 9}

SELECT ... FOR SHARE
SELECT ... FOR UPDATE

MySQL unterstützt auch die Befehle LOCK TABLES und UNLOCK TABLES, die im Server und nicht in den Speicher-Engines implementiert sind. Wenn du Transaktionen benötigst, verwende eine transaktionale Speicherung. LOCK TABLES ist unnötig, da InnoDB Row-Level Locking unterstützt.

Tipp

Die Interaktion zwischen LOCK TABLES und Transaktionen ist komplex, und in einigen Serverversionen gibt es unerwartete Verhaltensweisen. Deshalb empfehlen wir dir, LOCK TABLES nur dann zu verwenden, wenn du dich in einer Transaktion befindest und AUTOCOMMIT deaktiviert ist, unabhängig davon, welche Speicher-Engine du verwendest.

Multiversions-Gleichzeitigkeitskontrolle

Die meisten transaktionalen Speicher-Engines von MySQL verwenden keinen einfachen Row-Locking-Mechanismus. Stattdessen verwenden sie Sperren auf Zeilenebene in Verbindung mit einer Technik zur Erhöhung der Gleichzeitigkeit, die als Multiversions-Gleichzeitigkeitskontrolle (MVCC) bekannt ist. MVCC gibt es nicht nur bei MySQL: Oracle, PostgreSQL und einige andere Datenbanksysteme verwenden es ebenfalls, obwohl es erhebliche Unterschiede gibt, weil es keinen Standard dafür gibt, wie MVCC funktionieren sollte.

Du kannst dir MVCC als eine Abwandlung des Row-Level-Lockings vorstellen. In vielen Fällen ist ein Locking gar nicht nötig und der Overhead ist viel geringer. Je nachdem, wie es implementiert ist, kann es nicht sperrende Lesevorgänge zulassen, während es bei Schreibvorgängen nur die notwendigen Zeilen sperrt.

MVCC arbeitet mit Schnappschüssen der Daten, wie sie zu einem bestimmten Zeitpunkt vorhanden waren. Das bedeutet, dass die Transaktionen eine konsistente Sicht auf die Daten haben, egal wie lange sie laufen. Das bedeutet auch, dass verschiedene Transaktionen zur gleichen Zeit unterschiedliche Daten in denselben Tabellen sehen können! Wenn du das noch nie erlebt hast, mag es verwirrend sein, aber mit der Zeit wird es einfacher zu verstehen.

Jede Speicher-Engine implementiert MVCC anders. Einige der Variationen umfassen optimistische und pessimistische Gleichzeitigkeitssteuerung. Wir veranschaulichen eine Funktionsweise von MVCC, indem wir das Verhalten von InnoDB¹⁰ in Form eines Sequenzdiagramms in Abbildung 1-2.

InnoDB implementiert MVCC, indem es für jede Transaktion, die gestartet wird, eine Transaktions-ID vergibt. Diese ID wird zugewiesen, wenn die Transaktion zum ersten Mal Daten liest. Wenn ein Datensatz innerhalb der Transaktion geändert wird, wird ein Undo-Datensatz in das Undo-Protokoll geschrieben, der erklärt, wie die Änderung rückgängig gemacht werden kann, und der Rollback-Zeiger der Transaktion zeigt auf diesen Undo-Datensatz. So kann die Transaktion bei Bedarf den Weg zum Rollback finden.

Abbildung 1-2. Ein Sequenzdiagramm zur Behandlung mehrerer Versionen einer Zeile über verschiedene Transaktionen hinweg

Wenn eine andere Sitzung einen Cluster-Key-Indexsatz liest, vergleicht InnoDB die Transaktions-ID des Satzes mit der Leseansicht dieser Sitzung. Wenn der Datensatz in seinem aktuellen Zustand nicht sichtbar sein sollte (die Transaktion, die ihn verändert hat, wurde noch nicht übertragen), wird der Undo-Log-Datensatz verfolgt und angewendet, bis die Sitzung eine Transaktions-ID erreicht, die sichtbar sein kann. Dieser Prozess kann in einer Schleife bis zu einem Rückgängigmachungs-Datensatz führen, der diese Zeile vollständig löscht und der Leseansicht signalisiert, dass diese Zeile nicht mehr existiert.

Datensätze in einer Transaktion werden gelöscht, indem ein "deleted"-Bit in den "info flags" des Datensatzes gesetzt wird. Dies wird auch im Rückgängigmachungsprotokoll als "Löschmarkierung entfernen" festgehalten.

Es ist auch erwähnenswert, dass alle Undo-Log-Schreibvorgänge auch als Redo-Log protokolliert werden, da die Undo-Log-Schreibvorgänge Teil des Wiederherstellungsprozesses bei einem Serverabsturz sind und zu den Transaktionen gehören.¹¹ Die Größe dieser Redo- und Undo-Logs spielt ebenfalls eine große Rolle für die Leistung von Transaktionen mit hoher Parallelität. Die Konfiguration dieser Logs wird in Kapitel 5 ausführlicher behandelt.

Das Ergebnis dieser zusätzlichen Datenspeicherung ist, dass die meisten Leseabfragen keine Sperren erhalten. Sie lesen die Daten einfach so schnell wie möglich und stellen sicher, dass nur Zeilen ausgewählt werden, die die Kriterien erfüllen. Die Nachteile sind, dass die Speicherung mehr Daten mit jeder Zeile speichern muss, mehr Arbeit bei der Prüfung der Zeilen anfällt und einige zusätzliche Verwaltungsaufgaben erledigt werden müssen.

MVCC funktioniert nur mit den Isolationsebenen REPEATABLE READ und READ COMMITTED. READ UNCOMMITTED ist nicht MVCC-kompatibel¹² weil Abfragen nicht die Zeilenversion lesen, die zu ihrer Transaktionsversion passt, sondern immer die neueste Version. SERIALIZABLE ist nicht MVCC-kompatibel, weil Lesevorgänge jede Zeile sperren, die sie zurückgeben.

Replikation

MySQL ist so konzipiert, dass immer nur auf einem Knoten geschrieben werden kann. Das hat Vorteile bei der Verwaltung der Konsistenz, führt aber zu Kompromissen, wenn die Daten auf mehreren Servern oder an mehreren Orten geschrieben werden müssen. MySQL bietet eine native Möglichkeit, Schreibvorgänge, die ein Knoten vornimmt, auf weitere Knoten zu verteilen. Dies wird als Replikation bezeichnet. In MySQL hat der Quellknoten einen Thread pro Replikat, der als Replikationsclient angemeldet ist und bei einem Schreibvorgang aufwacht und neue Daten sendet. In Abbildung 1-3 zeigen wir ein einfaches Beispiel für diesen Aufbau, der normalerweise als Topologiebaum mehrerer MySQL-Server in einem Quell- und Replikataufbau bezeichnet wird.

Abbildung 1-3. Eine vereinfachte Ansicht einer MySQL-Server-Replikationstopologie

Für alle Daten, die du in der Produktion verwendest, solltest du die Replikation nutzen und mindestens drei weitere Replikate haben, die idealerweise auf verschiedene Standorte verteilt sind (in Cloud-Umgebungen werden sie als Regionen bezeichnet), um eine Notfallwiederherstellung zu planen.

Im Laufe der Jahre wurde die Replikation in MySQL immer ausgefeilter. Globale Transaktionsbezeichner, Multisource-Replikation, parallele Replikation auf Replikaten und semisynchrone Replikation sind einige der wichtigsten Neuerungen. Wir behandeln die Replikation sehr ausführlich in Kapitel 9.

Struktur der Datendateien

In Version 8.0 hat MySQL die Metadaten einer Tabelle in ein Datenwörterbuch umgewandelt, das in der .ibd-Datei der Tabelle enthalten ist. Dadurch werden Informationen über die Tabellenstruktur für Transaktionen und atomare Änderungen der Datendefinition unterstützt. Anstatt sich nur auf information_schema zu verlassen, um die Tabellendefinition und die Metadaten während der Operationen abzurufen, wird der Dictionary Object Cache eingeführt, ein LRU-basierter Cache für Partitionsdefinitionen, Tabellendefinitionen, gespeicherte Programmdefinitionen, Zeichensätze und Sortierinformationen im Speicher. Diese wichtige Änderung in der Art und Weise, wie der Server auf Metadaten über Tabellen zugreift, reduziert den E/A-Aufwand und ist effizient, vor allem, wenn eine Untergruppe von Tabellen am häufigsten genutzt wird und sich daher am häufigsten im Cache befindet. Die Dateien .ibd und .frm werden durch serialisierte Wörterbuchinformationen (.sdi) pro Tabelle ersetzt.

Die InnoDB-Engine

InnoDB ist die Standard-Engine für die transaktionale Speicherung in MySQL und die wichtigste und nützlichste Engine überhaupt. Sie wurde für die Verarbeitung vieler kurzlebiger Transaktionen entwickelt, die in der Regel abgeschlossen und nicht zurückgesetzt werden. Aufgrund ihrer Leistung und der automatischen Wiederherstellung nach einem Absturz ist sie auch für nicht-transaktionale Speicherungen beliebt. Wenn du dich mit Speicher-Engines beschäftigen willst, lohnt es sich, InnoDB eingehend zu studieren, um so viel wie möglich darüber zu erfahren, anstatt alle Speicher-Engines gleichermaßen zu studieren.

Hinweis

Es ist die bewährte Methode, die InnoDB Speicher-Engine als Standard-Engine für jede Anwendung zu verwenden. MySQL hat das einfach gemacht, indem es InnoDB vor ein paar Hauptversionen zur Standard-Engine gemacht hat.

InnoDB ist die Standard-Engine für die universelle Speicherung von MySQL. Standardmäßig speichert InnoDB seine Daten in einer Reihe von Datendateien, die zusammen als Tablespace bezeichnet werden. Ein Tablespace ist im Grunde genommen eine Blackbox, die InnoDB ganz allein verwaltet.

InnoDB verwendet MVCC, um eine hohe Gleichzeitigkeit zu erreichen, und implementiert alle vier SQL-Standardisolationsebenen. Standardmäßig ist die Isolationsebene REPEATABLE READ eingestellt und verfügt über eine Next-Key-Locking-Strategie, die Phantom-Lesungen in dieser Isolationsebene verhindert: Anstatt nur die Zeilen zu sperren, die du in einer Abfrage berührt hast, sperrt InnoDB auch Lücken in der Indexstruktur und verhindert so, dass Phantome eingefügt werden.

InnoDB-Tabellen basieren auf einem geclusterten Index, auf den wir in Kapitel 8 bei der Diskussion des Schemadesigns näher eingehen werden. Die Indexstrukturen von InnoDB unterscheiden sich stark von denen der meisten anderen MySQL-Speichersysteme. Daher bietet sie sehr schnelle Primärschlüssel-Lookups. Allerdings enthalten sekundäre Indizes (Indizes, die nicht der Primärschlüssel sind) die Primärschlüsselspalten. Wenn dein Primärschlüssel also groß ist, werden auch die anderen Indizes groß sein. Du solltest einen kleinen Primärschlüssel anstreben, wenn du viele Indizes in einer Tabelle hast.

InnoDB verfügt über eine Reihe von internen Optimierungen. Dazu gehören Predictive Read-Ahead für das Vorabholen von Daten von der Festplatte, ein adaptiver Hash-Index, der automatisch Hash-Indizes im Speicher aufbaut, um sehr schnelle Lookups zu ermöglichen, und ein Insert-Puffer, um Einfügungen zu beschleunigen. Wir behandeln diese Funktionen in Kapitel 4 dieses Buches.

Das Verhalten von InnoDB ist sehr kompliziert, und wir empfehlen dir, den Abschnitt "InnoDB Locking and Transaction Model" im MySQL-Handbuch zu lesen, wenn du InnoDB verwendest. Aufgrund der MVCC-Architektur gibt es viele Feinheiten, die du kennen solltest, bevor du eine Anwendung mit InnoDB entwickelst. Die Arbeit mit einer Speicher-Engine, die konsistente Ansichten der Daten für alle Benutzer aufrechterhält, auch wenn einige Benutzer Daten ändern, kann komplex sein.

Als transaktionale Speicher-Engine unterstützt InnoDB "heiße" Online-Sicherungen über eine Reihe von Mechanismen, darunter Oracles proprietäres MySQL Enterprise Backup und das Open-Source-Programm Percona XtraBackup. Auf die Sicherung und Wiederherstellung gehen wir in Kapitel 10 näher ein.

Mit MySQL 5.6 führte InnoDB die Online-DDL ein, die zunächst nur begrenzte Anwendungsfälle hatte, die in den Versionen 5.7 und 8.0 erweitert wurden. Schemaänderungen an Ort und Stelle ermöglichen spezifische Tabellenänderungen ohne eine vollständige Tabellensperre und ohne Verwendung externer Werkzeuge, was die Funktionsfähigkeit von MySQL InnoDB-Tabellen erheblich verbessert. Wir werden die Optionen für Online-Schemaänderungen, sowohl native als auch externe Werkzeuge, in Kapitel 6 behandeln.

Unterstützung von JSON-Dokumenten

Der JSON-Typ wurde erstmals mit der Version 5.7 von InnoDB eingeführt und bietet eine automatische Validierung von JSON-Dokumenten sowie eine optimierte Speicherung, die einen schnellen Lesezugriff ermöglicht - eine deutliche Verbesserung gegenüber den Kompromissen, die Ingenieure bei der BLOB-Speicherung (Binary Large Object) für JSON-Dokumente in Kauf nehmen mussten. Zusammen mit der neuen Datentypunterstützung hat InnoDB auch SQL-Funktionen eingeführt, die umfangreiche Operationen mit JSON-Dokumenten unterstützen. Eine weitere Verbesserung in MySQL 8.0.7 ist die Möglichkeit, mehrwertige Indizes für JSON-Arrays zu definieren. Mit dieser Funktion können Lesezugriffsabfragen auf JSON-Typen noch weiter beschleunigt werden, indem die gängigen Zugriffsmuster mit Funktionen abgeglichen werden, die die JSON-Dokumentwerte abbilden können. Auf die Verwendung und die Auswirkungen des JSON-Datentyps auf die Leistung gehen wir in Kapitel 6 unter "JSON-Daten" ein.

Änderungen im Data Dictionary

Eine weitere wichtige Änderung von in MySQL 8.0 ist die Abschaffung der dateibasierten Speicherung von Tabellenmetadaten und die Umstellung auf ein Data Dictionary mit InnoDB-Tabellenspeicherung. Diese Änderung bringt alle Vorteile von InnoDBs Crash-Recovery-Transaktionen für Operationen wie Änderungen an Tabellen mit sich. Diese Umstellung verbessert zwar die Verwaltung von Datendefinitionen in MySQL, erfordert aber auch größere Änderungen beim Betrieb eines MySQL-Servers. Vor allem Backup-Prozesse, die bisher auf die Metadaten der Tabellen angewiesen waren, müssen nun das neue Data Dictionary abfragen, um Tabellendefinitionen zu extrahieren.

Atomare DDL

Schließlich wurden mit MySQL 8.0 atomare Datendefinitionsänderungen eingeführt. Das bedeutet, dass Datendefinitionsanweisungen jetzt entweder vollständig erfolgreich abgeschlossen oder vollständig rückgängig gemacht werden können. Dies wird durch die Erstellung eines DDL-spezifischen Undo- und Redo-Logs möglich, auf das sich InnoDB verlässt, um die Änderung zu verfolgen - ein weiterer Punkt, an dem das bewährte Design von InnoDB auf die Operationen des MySQL-Servers erweitert wurde.

Zusammenfassung

MySQL hat eine mehrschichtige Architektur, mit serverweiten Diensten und Abfrageausführung oben und Speicher-Engines darunter. Obwohl es viele verschiedene Plug-in-APIs gibt, ist die API der Speicher-Engine die wichtigste. Wenn du verstehst, dass MySQL Abfragen ausführt, indem es Zeilen über die API der Speicher-Engine hin und her schickt, hast du die Grundlagen der Architektur des Servers verstanden.

In den letzten Hauptversionen hat sich MySQL auf InnoDB als Hauptentwicklungsschwerpunkt festgelegt und hat sogar seine interne Buchhaltung rund um Tabellenmetadaten, Authentifizierung und Autorisierung nach Jahren in MyISAM verlagert. Diese verstärkte Investition von Oracle in die InnoDB-Engine hat zu wichtigen Verbesserungen wie atomaren DDLs, robusteren Online-DDLs, besserer Widerstandsfähigkeit gegen Abstürze und besserer Bedienbarkeit für sicherheitsbewusste Implementierungen geführt.

InnoDB ist die Standard-Engine für die Speicherung und diejenige, die fast alle Anwendungsfälle abdecken sollte. Daher konzentrieren wir uns in den folgenden Kapiteln stark auf die InnoDB Speicher-Engine, wenn wir über Funktionen, Leistung und Einschränkungen sprechen, und gehen nur selten auf andere Speicher-Engines ein.

¹ Eine Ausnahme ist InnoDB, das Fremdschlüsseldefinitionen parst, weil der MySQL-Server sie noch nicht selbst implementiert.

² MySQL 5.5 und neuere Versionen unterstützen eine API, die Thread-Pooling-Plug-ins akzeptieren kann, auch wenn diese nicht häufig verwendet werden. Üblicherweise wird das Thread-Pooling auf der Zugriffsebene durchgeführt, was wir in Kapitel 5 besprechen.

³ Wir empfehlen dir auf jeden Fall, die Dokumentation zu exklusiven und gemeinsamen Sperren, Intention Locking und Record Locks zu lesen.

⁴ Es gibt Metadatensperren, die bei der Änderung von Tabellennamen oder Schemata verwendet werden, und in 8.0 werden "Sperrfunktionen auf Anwendungsebene" eingeführt. Bei alltäglichen Datenänderungen wird das interne Sperren der InnoDB-Engine überlassen.

⁵ Obwohl dies eine übliche akademische Übung ist, verlassen sich die meisten Banken tatsächlich auf die tägliche Abstimmung und nicht auf strenge Transaktionsvorgänge während des Tages.

⁶ Weitere Informationen findest du in einer Zusammenfassung von ANSI SQL von Adrian Coyler und in einer Erklärung der Konsistenzmodelle von Kyle Kingsbury.

⁷ Wie du später in diesem Kapitel sehen wirst, sperren einige Speicher-Engines ganze Tabellen, während andere komplexere zeilenbasierte Sperren implementieren. All diese Logik befindet sich größtenteils in der Ebene der Speicherung.

⁸ Diese Sperrhinweise werden häufig missbraucht und sollten in der Regel vermieden werden.

⁹ SELECT...FOR SHARE ist eine Funktion von MySQL 8.0, die SELECT...LOCK IN SHARE MODE der vorherigen Versionen ersetzt.

¹⁰ Wir empfehlen, diesen Blogbeitrag von Jeremy Cole zu lesen, um ein tieferes Verständnis der Datensatzstruktur in InnoDB zu erhalten.

¹¹ Wie InnoDB mit mehreren Versionen seiner Datensätze umgeht, erfährst du in diesem Blogbeitrag von Jeremy Cole.

¹² Es gibt keinen formalen Standard, der MVCC definiert, so dass verschiedene Engines und Datenbanken es sehr unterschiedlich implementieren, und niemand kann sagen, dass eine von ihnen falsch ist.