MySQL Tabellensperrung: Was passiert, wenn Sie eine Spalte oder einen Index hinzufügen?

Das Modifizieren der Struktur einer MySQL-Tabelle beinhaltet oft das Problem der Sperrung auf Tabellenebene.

Dies ist besonders wichtig für Tabellen mit großen Datenmengen, da es einen spürbaren Einfluss auf die Leistung von Geschäftssystemen haben kann.

Durch die Optimierung von Operationen zur Änderung der Tabellenstruktur können Entwickler die Sperrzeit von Tabellen vermeiden oder minimieren und so sicherstellen, dass das System reibungslos läuft.

Einführung in Sperren auf Tabellenebene

Sperren auf Tabellenebene beziehen sich auf die Sperrung einer gesamten Tabelle während bestimmter Operationen, um die Datenkonsistenz zu gewährleisten.

Speziell:

Bei der Ausführung einer ALTER TABLE-Operation auf einer Tabelle sperrt MySQL die Tabelle standardmäßig und verhindert, dass andere Transaktionen sie lesen oder in sie schreiben, bis die ALTER TABLE-Operation abgeschlossen ist.

Dieses Tabellensperrverhalten hat wenig Auswirkungen auf kleine Tabellen oder Systeme mit geringer Parallelität.

Bei der Arbeit mit großen Datensätzen oder stark frequentierten Geschäftssystemen können Tabellensperren jedoch zu schwerwiegenden Leistungsengpässen führen und sogar zu Systemabstürzen führen.

Spezifische Auswirkungen der Tabellensperrung

Wenn MySQL Operationen wie das Hinzufügen einer Spalte durchführt, kann die Tabellensperrung zu folgenden Problemen führen:

Verzögerungen bei Abfragen: Alle SQL-Abfragen auf dieser Tabelle werden blockiert, bis die Tabellensperre aufgehoben wird.
Blockierte Schreiboperationen: Alle Schreiboperationen (wie INSERT, UPDATE, DELETE) auf der Tabelle werden angehalten, bis die Sperre aufgehoben wird.
Systemverlangsamung: Wenn eine Tabellensperroperation zu lange dauert, sinkt die Gesamtleistung des Geschäftssystems erheblich, was möglicherweise zu einem Systemausfall führt.

In neueren Versionen von MySQL muss beim Hinzufügen einer Spalte zu einer Tabelle mit der InnoDB-Speicher-Engine jedoch nicht unbedingt eine Tabellensperre auftreten.

Die InnoDB-Speicher-Engine bietet einige Mechanismen, um die Tabellensperrung zu reduzieren und die parallele Leistung zu verbessern.

In MySQL hängt es von der verwendeten Speicher-Engine und der Version von MySQL ab, ob das Hinzufügen einer Spalte zu einer Tabelle diese sperrt.

Vor MySQL 5.6

In früheren Versionen von MySQL würde die Verwendung des Befehls ALTER TABLE zum Hinzufügen einer Spalte die Tabelle standardmäßig sperren, wenn sie die InnoDB-Speicher-Engine verwendet.

Dies bedeutet, dass die Tabelle während des Vorgangs gesperrt würde, wodurch andere Lese- und Schreibvorgänge verhindert würden, bis der Vorgang abgeschlossen ist.

Dieses vollständige Tabellensperrverhalten kann zu langen Wartezeiten und Anwendungsunterbrechungen führen, wenn ALTER TABLE-Operationen auf großen Tabellen ausgeführt werden.

Also: Vor MySQL 5.6 würde das direkte Ändern der Tabellenstruktur die Tabelle sperren.

Die spezifischen Schritte sind wie folgt:

Erstellen Sie zunächst eine neue temporäre Tabelle mit der geänderten Struktur mit dem Befehl ALTER TABLE.
Importieren Sie dann die Daten aus der Originaltabelle in die temporäre Tabelle.
Löschen Sie die Originaltabelle.
Benennen Sie schließlich die temporäre Tabelle in den Namen der Originaltabelle um.

MySQL Versionen 5.6 und 8.0 führten Optimierungen ein, um Tabellensperrprobleme zu beheben.

MySQL 5.6

Ab MySQL 5.6 führte InnoDB Online-DDL-Operationen ein, die es ermöglichen, bestimmte Tabellenänderungen ohne Sperrung der Tabelle durchzuführen.

Das Hinzufügen einer Spalte ist eine Online-Operation und kann ALGORITHM=INPLACE verwenden, um eine vollständige Tabellensperrung zu vermeiden.

ALTER TABLE your_table ADD COLUMN new_column INT ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM=INPLACE gibt an, dass der In-Place-Algorithmus für die Änderung verwendet werden soll, der Teil des Online-DDL-Mechanismus ist.
- Es weist MySQL explizit an, zu versuchen, die Tabellenstruktur direkt zu ändern.
- MySQL wird versuchen, Änderungen nach Möglichkeit anzuwenden, ohne die gesamte Tabelle neu zu erstellen.
LOCK=NONE bedeutet, dass die Tabelle nicht gesperrt werden soll, wodurch die Auswirkungen auf parallele Abfragen minimiert werden.
- Andere Sitzungen dürfen die Tabelle lesen und in sie schreiben.
- Es kann jedoch weiterhin ein gewisses Risiko von Dateninkonsistenzen bestehen.

Dies verbessert die Ausführungseffizienz erheblich und vermeidet Tabellensperren.

Dies ist jedoch in zwei Fälle unterteilt:

Hinzufügen einer Spalte, die keine Nullwerte zulässt:

Es wird eine schnelle Metadaten-Operation durchgeführt, ohne die gesamte Tabelle zu sperren.
Andere Sitzungen können während der Änderung weiterhin aus der Tabelle lesen und in sie schreiben.

Hinzufügen einer Spalte, die Nullwerte zulässt:

Wird auch als schnelle Metadaten-Operation ohne Sperrung der gesamten Tabelle durchgeführt.
Andere Sitzungen können weiterhin Lese- und Schreibvorgänge ausführen, obwohl während der Änderung möglicherweise kurze Sperren auf Zeilenebene auftreten.

Hinweis:

Obwohl die InnoDB-Speicher-Engine reduzierte Sperren bietet, kann die Ausführung von ALTER TABLE dennoch Auswirkungen auf die Leistung haben.

Diese sind auf interne Metadatenoperationen, Datenreorganisation oder Protokollschreibung zurückzuführen.

Daher wird empfohlen, beim Ändern der Struktur großer Tabellen den Vorgang außerhalb der Stoßzeiten durchzuführen, um die Auswirkungen auf Anwendungen zu minimieren.

MySQL 8.0

MySQL 8.0 führt neue Funktionen ein, die es ermöglichen, die meisten ALTER TABLE-Operationen durchzuführen, ohne die Tabelle zu sperren.

Einfach ausgedrückt: Es erweitert die Fähigkeiten von Online-DDL-Operationen.

Standardmäßig sperren in MySQL 8.0 einfache ALTER TABLE-Operationen (wie das Hinzufügen einer Spalte) normalerweise nicht die Tabelle.

Um zu überprüfen, ob eine bestimmte ALTER TABLE-Operation die Tabelle sperrt, können Sie die EXPLAIN-Anweisung vor der Ausführung verwenden:

EXPLAIN ALTER TABLE your_table ADD COLUMN new_column INT;

Dieser Befehl zeigt die Ausführungsplaninformationen an, einschließlich der Frage, ob die Tabelle gesperrt wird.

Hier sind einige spezifische Optimierungen in MySQL 8.0:

Atomares DDL

MySQL 8.0 führt Atomic DDL-Operationen ein, was bedeutet, dass die Ausführung von ALTER TABLE-Anweisungen weniger Blockierungen beinhaltet.
Beim Hinzufügen einer Spalte reduziert der Atomic-DDL-Mechanismus die Tabellensperrzeit und ermöglicht es anderen Sitzungen, weiterhin Daten zu lesen und zu schreiben.

Sofortige Metadatenaktualisierungen

In MySQL 8.0 werden die Metadaten der Tabelle sofort aktualisiert, wenn eine Spalte hinzugefügt wird, ohne auf den Abschluss des gesamten Vorgangs zu warten.
Dies ermöglicht einen schnelleren Abschluss der ALTER TABLE-Operation und reduziert die Sperrzeit.

InnoDB-Engine-Optimierungen

Die InnoDB-Engine in MySQL 8.0 wurde für strukturelle Änderungen an großen Datentabellen optimiert.
Beim Hinzufügen von Spalten, die keine Nullwerte zulassen, muss InnoDB beispielsweise nicht mehr alle Tabellendaten kopieren.
Stattdessen wird eine weniger aufwendige Operation verwendet, um das neue Feld hinzuzufügen, wodurch die Sperrzeit und die Ressourcenauslastung reduziert werden.

Inkrementelle Metadatenaktualisierungen

MySQL 8.0 führt Inkrementelle Metadatenaktualisierungen ein, was bedeutet, dass während der ALTER TABLE-Operation nur die betroffenen Metadaten aktualisiert werden müssen, anstatt der gesamten Tabelle.
Dies reduziert die Sperrzeit und den Overhead.

Online DDL

Online DDL bezieht sich auf die Ausführung von Data Definition Language (DDL)-Operationen während des Betriebs der Datenbank.

Zum Beispiel das Erstellen, Ändern oder Löschen von Tabellenstrukturen, Indizes usw., ohne langfristige Sperren oder die Nichtverfügbarkeit der Datenbank zu verursachen.

Traditionelle DDL-Operationen erfordern typischerweise exklusive Sperren für die betroffenen Tabellen.

Dies kann verhindern, dass andere Sitzungen Lese- oder Schreiboperationen auf der Tabelle durchführen, wodurch die normale Datenbanknutzung beeinträchtigt wird.

Derzeit werden drei Hauptalgorithmen unterstützt:

COPY: Wird in Pre-MySQL 5.6-Versionen verwendet, dies ist ein Nicht-Online-Algorithmus.
INPLACE: Eingeführt in MySQL 5.6.
INSTANT: Eingeführt in MySQL 8.0.12 (beigetragen vom Tencent DBA-Team).

Grundprinzipien

Während der DDL-Ausführung durchläuft sie – unabhängig vom verwendeten Algorithmus – im Allgemeinen drei Phasen:

Vorbereitungsphase
Ausführungsphase (DDL)
Commit-Phase

Der Unterschied liegt in den Optimierungen, die in jeder dieser Phasen je nach Algorithmus angewendet werden.

Einzelheiten zur Implementierung finden Sie in der offiziellen Dokumentation:

Zusammenfassung

Seit MySQL 5.6 erfordert das einfache Hinzufügen einer Spalte, das Ändern von Tabellenstrukturen oder das Hinzufügen von Indizes im Allgemeinen keine Sperrung der gesamten Tabelle.

In einigen Fällen muss MySQL jedoch möglicherweise immer noch die gesamte Tabelle sperren.

Auch bei großen Datenmengen können weiterhin Leistungsprobleme auftreten.

Beachten Sie daher bei tatsächlichen Operationen die Datenmenge in der Tabelle und ihre endgültige Größe (insbesondere Indexdaten).

Wenn Ihre MySQL-Version relativ alt ist oder Online-DDL-Operationen aus bestimmten Gründen nicht unterstützt werden, führen Sie ALTER TABLE-Operationen außerhalb der Stoßzeiten durch, um die Auswirkungen auf Geschäftssysteme zu minimieren.