webentwicklung-frage-antwort-db.com.de

ALTER TABLE ohne den Tisch zu verriegeln?

Wenn Sie eine ALTER TABLE-Anweisung in MySQL ausführen, ist die gesamte Tabelle für die Dauer der Anweisung schreibgeschützt. Wenn es sich um eine große Tabelle handelt, können Insert- oder Update-Anweisungen für eine lange Zeit gesperrt sein. Gibt es eine Möglichkeit, eine "heiße Änderung" vorzunehmen, beispielsweise das Hinzufügen einer Spalte, sodass die Tabelle während des gesamten Vorgangs immer noch aktualisiert werden kann?

Meistens bin ich an einer Lösung für MySQL interessiert, aber ich würde mich für andere RDBMS interessieren, wenn MySQL das nicht kann.

Zur Verdeutlichung möchte ich Ausfallzeiten vermeiden, wenn eine neue Funktion, für die eine zusätzliche Tabellenspalte erforderlich ist, in die Produktion übernommen wird. Jedes Datenbankschema wird sich im Laufe der Zeit ändern, das ist nur eine Tatsache des Lebens. Ich verstehe nicht, warum wir akzeptieren sollten, dass diese Änderungen unweigerlich zu Ausfallzeiten führen müssen. das ist nur schwach.

102
Daniel

Die einzige andere Möglichkeit besteht darin, manuell das zu tun, was viele RDBMS-Systeme sowieso tun ...
- Erstellen Sie eine neue Tabelle

Sie können dann den Inhalt der alten Tabelle jeweils über einen Block kopieren. Achten Sie dabei immer auf INSERT/UPDATE/DELETE in der Quelltabelle. (Könnte durch einen Auslöser verwaltet werden. Dies würde zwar zu einer Verlangsamung führen, ist aber keine Sperre ...)

Wenn Sie fertig sind, ändern Sie den Namen der Quelltabelle und dann den Namen der neuen Tabelle. Am liebsten in einer Transaktion.

Kompilieren Sie anschließend alle gespeicherten Prozeduren usw., die diese Tabelle verwenden. Die Ausführungspläne werden wahrscheinlich nicht mehr gültig sein.

EDIT:

Es wurden einige Kommentare dazu abgegeben, dass diese Einschränkung etwas mangelhaft ist. Also dachte ich, ich würde es aus einer neuen Perspektive betrachten, um zu zeigen, warum es so ist ...

  • Das Hinzufügen eines neuen Felds entspricht dem Ändern eines Felds in jeder Zeile.
  • Feldsperren wären viel schwieriger als Zeilensperren, ganz zu schweigen von Tabellensperren.

  • Sie ändern tatsächlich die physische Struktur auf der Festplatte, jeder Datensatz wird verschoben.
  • Das ist wirklich wie ein UPDATE auf dem ganzen Tisch, aber mit mehr Einfluss ...
58
MatBailie

Percona erstellt ein Tool mit dem Namen pt-online-schema-change , mit dem dies möglich ist.

Es wird im Wesentlichen eine Kopie der Tabelle erstellt und die neue Tabelle geändert. Damit die neue Tabelle mit dem Original synchron bleibt, werden Trigger zum Aktualisieren verwendet. Auf diese Weise kann auf die ursprüngliche Tabelle zugegriffen werden, während die neue Tabelle im Hintergrund vorbereitet wird.

Dies ähnelt der von Dems oben vorgeschlagenen Methode, geschieht jedoch automatisiert.

Einige ihrer Tools haben eine Lernkurve, nämlich das Herstellen einer Verbindung zur Datenbank. Wenn Sie diese jedoch einmal nicht installiert haben, sind sie hervorragende Tools.

Ex:

pt-online-schema-change --alter "ADD COLUMN c1 INT" D=db,t=numbers_are_friends
39
SeanDowney

Diese Frage aus dem Jahr 2009. Jetzt bietet MySQL eine Lösung:

Online-DDL

Eine Funktion, die die Leistung, Parallelität und Verfügbarkeit von InnoDB-Tabellen während DDL-Operationen (hauptsächlich ALTER TABLE) verbessert. Weitere Informationen finden Sie in Abschnitt 14.11, „InnoDB und Online-DDL“.

Die Details variieren je nach Art des Vorgangs. In einigen Fällen kann die Tabelle gleichzeitig geändert werden, während die ALTER TABLE ausgeführt wird. Die Operation kann möglicherweise ohne eine Tabellenkopie oder unter Verwendung eines speziell optimierten Typs einer Tabellenkopie ausgeführt werden. Die Speichernutzung wird durch die Konfigurationsoption innodb_online_alter_log_max_size gesteuert.

Sie können das Gleichgewicht zwischen Leistung und Parallelität während des DDL-Vorgangs anpassen, indem Sie auswählen, ob der Zugriff auf die Tabelle vollständig blockiert werden soll (LOCK = EXCLUSIVE-Klausel), Abfragen ohne DML (LOCK = SHARED-Klausel) oder vollständige Abfragen und DML zugelassen werden sollen Zugriff auf die Tabelle (LOCK = NONE-Klausel). Wenn Sie die LOCK-Klausel weglassen oder LOCK = DEFAULT angeben, lässt MySQL abhängig von der Art der Operation so viel Parallelität wie möglich zu.

Durch das Ausführen von Änderungen an Ort und Stelle, anstatt eine neue Kopie der Tabelle zu erstellen, werden vorübergehende Erhöhungen der Speicherplatznutzung und des E/A-Aufwands vermieden, die mit dem Kopieren der Tabelle und dem Wiederherstellen von Sekundärindizes verbunden sind.

siehe MySQL 5.6 Referenzhandbuch -> InnoDB und Online DDL für weitere Informationen.

Es scheint, dass Online-DDL auch in MariaDB verfügbar ist

Alternativ können Sie ALTER ONLINE TABLE verwenden, um sicherzustellen, dass Ihre ALTER TABLE keine gleichzeitigen Vorgänge blockiert (keine Sperren akzeptiert). Dies entspricht LOCK = NONE.

MariaDB KB über ALTER TABLE

19
Ivanov

Siehe Facebooks Online-Tool zum Ändern von Schemata.

http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932

Nicht für schwache Nerven; aber es wird den Job machen.

16
Steven Soroka

Ich empfehle Postgres, wenn das eine Option ist. Bei Postgres gibt es im Wesentlichen keine Ausfallzeiten mit den folgenden Verfahren:

Ein weiteres großartiges Feature ist, dass die meisten DDL-Anweisungen transaktional sind, sodass Sie eine vollständige Migration innerhalb einer SQL-Transaktion durchführen können. Wenn ein Fehler auftritt, wird das Ganze zurückgesetzt.

Ich schrieb this vor ein bisschen, vielleicht kann es mehr Einblick in die anderen Vorzüge geben.

14
mikelikespie

Da Sie nach anderen Datenbanken gefragt haben, finden Sie hier einige Informationen zu Oracle.

Das Hinzufügen einer NULL-Spalte zu einer Oracle-Tabelle ist ein sehr schneller Vorgang, da nur das Datenwörterbuch aktualisiert wird. Dies hält eine exklusive Sperre für einen sehr kurzen Zeitraum auf dem Tisch. Abhängige gespeicherte Prozeduren, Ansichten, Trigger usw. werden jedoch ungültig. Diese werden automatisch neu kompiliert.

Von dort aus können Sie bei Bedarf mit der ONLINE-Klausel einen Index erstellen. Auch hier werden nur sehr kurze Datenwörterbücher gesperrt. Es liest die gesamte Tabelle und sucht nach zu indizierenden Dingen, blockiert dabei jedoch niemanden.

Wenn Sie einen Fremdschlüssel hinzufügen müssen, können Sie dies tun und Oracle darauf vertrauen lassen, dass die Daten korrekt sind. Andernfalls muss die gesamte Tabelle gelesen und alle Werte überprüft werden, die langsam sein können (erst den Index erstellen).

Wenn Sie in jede Zeile der neuen Spalte einen Standardwert oder einen berechneten Wert einfügen müssen, müssen Sie ein umfangreiches Update oder ein kleines Hilfsprogramm ausführen, das die neuen Daten auffüllt. Dies kann langsam sein, insbesondere wenn die Reihen viel größer werden und nicht mehr in ihre Blöcke passen. Während dieses Vorgangs kann das Sperren verwaltet werden. Da die alte Version Ihrer Anwendung, die noch ausgeführt wird, diese Spalte nicht kennt, benötigen Sie möglicherweise einen hinterhältigen Auslöser oder einen Standardwert.

Von dort aus können Sie auf Ihren Anwendungsservern auf die neue Version des Codes umstellen, und der Code wird weiter ausgeführt. Lass deinen hinterhältigen Abzug fallen.

Alternativ können Sie DBMS_REDEFINITION verwenden, eine Blackbox, die für diese Art von Aufgaben entwickelt wurde.

All dies ist so lästig zu testen, dass wir nur einen frühen Sonntagmorgen Ausfall haben, wenn wir eine Hauptversion veröffentlichen.

7
WW.

Wenn Sie sich bei Anwendungsaktualisierungen keine Ausfallzeit für Ihre Datenbank leisten können, sollten Sie einen Cluster mit zwei Knoten für hohe Verfügbarkeit einrichten. Mit einer einfachen Replikationskonfiguration können Sie Strukturänderungen fast vollständig online durchführen, wie die von Ihnen vorgeschlagene:

  • warten Sie, bis alle Änderungen auf einem passiven Slave repliziert wurden
  • ändern Sie den passiven Slave in den aktiven Master
  • nehmen Sie die strukturellen Änderungen am alten Meister vor
  • änderungen vom neuen Master auf den alten Master zurück replizieren
  • tauschen Sie den Master erneut aus und stellen Sie gleichzeitig die neue App bereit

Es ist nicht immer einfach, aber es funktioniert, normalerweise mit 0 Ausfallzeiten! Der zweite Knoten muss nicht nur passiv sein, sondern kann auch zum Testen, für statistische Zwecke oder als Fallback-Knoten verwendet werden. Wenn Sie keine Infrastrukturreplikation haben, kann diese auf einem einzelnen Computer (mit zwei Instanzen von MySQL) eingerichtet werden.

3
jynus

Nee. Wenn Sie MyISAM-Tabellen verwenden, tun sie meines Wissens nur Tabellensperren - es gibt keine Datensatzsperren, sie versuchen nur, alles durch Einfachheit hyperfast zu halten. (Andere MySQL-Tabellen funktionieren anders.) In jedem Fall können Sie die Tabelle in eine andere Tabelle kopieren, ändern und dann wechseln, um sie auf Unterschiede zu aktualisieren.

Dies ist eine so massive Änderung, dass ich bezweifle, dass ein DBMS dies unterstützen würde. Es wird als Vorteil angesehen, dies in erster Linie mit den Daten in der Tabelle tun zu können.

2
dkretz

Vorübergehende Lösung...

Eine andere Lösung könnte darin bestehen, eine weitere Tabelle mit dem Primärschlüssel der ursprünglichen Tabelle zusammen mit Ihrer neuen Spalte hinzuzufügen.

Füllen Sie Ihren Primärschlüssel in die neue Tabelle und füllen Sie die Werte für die neue Spalte in Ihrer neuen Tabelle auf, und ändern Sie Ihre Abfrage, um diese Tabelle für Auswahloperationen zu verbinden. Außerdem müssen Sie für diesen Spaltenwert eine separate Aktualisierung einfügen.

Wenn Ausfallzeiten auftreten, können Sie die ursprüngliche Tabelle ändern, Ihre DML-Abfragen ändern und die zuvor erstellte neue Tabelle löschen

Andernfalls können Sie sich für das Clustering-Verfahren, die Replikation und das pt-online-Schema-Tool von Percona entscheiden

2
Balasundaram

Mit dem Innodb-Plugin können ALTER TABLE-Anweisungen, die nur Sekundärindizes hinzufügen oder löschen, "schnell" ausgeführt werden, d. H. Ohne die Tabelle neu zu erstellen.

Generell bedeutet jedoch in MySQL jede ALTER TABLE, dass die gesamte Tabelle neu erstellt wird, was sehr lange dauern kann (d. H., Wenn die Tabelle eine nützliche Datenmenge enthält).

Sie müssen Ihre Anwendung wirklich so gestalten, dass ALTER TABLE-Anweisungen nicht regelmäßig ausgeführt werden müssen. Sie möchten auf keinen Fall ALTER TABLE während der normalen Ausführung der Anwendung ausführen, es sei denn, Sie sind bereit zu warten oder Sie ändern winzige Tabellen.

1
MarkR

Der Unterschied zwischen Postgres und MySQL besteht darin, dass in Postgres keine Tabelle neu erstellt wird, sondern das Datenwörterbuch geändert wird, das Oracle ähnelt. Daher ist die Operation schnell, obwohl immer noch für sehr kurze Zeit eine exklusive DDL-Tabellensperre zugewiesen werden muss, wie oben von anderen angegeben.

In MySQL werden durch die Operation Daten in eine neue Tabelle kopiert, während Transaktionen blockiert werden. Dies war vor Version 5.6 für MySQL-Datenbankadministratoren ein großes Problem.

Die gute Nachricht ist, dass die Einschränkung seit der Veröffentlichung von MySQL 5.6 größtenteils aufgehoben ist und Sie nun die wahre Leistung der MYSQL-Datenbank genießen können.

1

Sie sollten auf jeden Fall versuchen pt-online-schema-change. Ich habe dieses Tool verwendet, um Migrationen auf AWS RDS mit mehreren Slaves durchzuführen, und es hat für mich sehr gut funktioniert. Ich habe einen ausführlichen Blog-Beitrag darüber geschrieben, wie man das macht, was für Sie hilfreich sein könnte.

Blog: http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/

1
Rafay

Wenn jemand dies noch liest oder hierher kommt, ist dies der große Vorteil der Verwendung eines NoSQL-Datenbanksystems wie mongodb. Ich hatte das gleiche Problem mit dem Ändern der Tabelle, um entweder Spalten für zusätzliche Features oder Indizes für eine große Tabelle mit Millionen von Zeilen und hohen Schreibzugriffen hinzuzufügen. Es würde sehr lange dauern, bis die LIVE-Datenbank gesperrt ist. Dies würde unsere Benutzer frustrieren. Auf kleinen Tischen kann man damit durchkommen.

Ich hasse die Tatsache, dass wir "unsere Tabellen so gestalten müssen, dass sie nicht verändert werden". Ich glaube einfach nicht, dass das in der heutigen Website-Welt funktioniert. Sie können nicht vorhersagen, wie die Leute Ihre Software verwenden werden. Aus diesem Grund ändern Sie die Dinge schnell basierend auf dem Feedback der Benutzer. Mit Mongodb können Sie "Spalten" nach Belieben ohne Ausfallzeit hinzufügen. Sie fügen sie nicht wirklich hinzu, Sie fügen nur Daten mit neuen Spalten ein und das geschieht automatisch.

Ein Besuch lohnt sich unter: www.mongodb.com

1
Brian Gruber

Im Allgemeinen lautet die Antwort "Nein". Sie ändern die Struktur der Tabelle, die möglicherweise viele Aktualisierungen erfordert ", und dem stimme ich definitiv zu. Wenn Sie dies häufig erwarten, biete ich eine Alternative zu" Dummy "-Spalten an - verwenden Sie VIEWs anstelle von Tabellen für SELECTing-Daten. IIRC ist das Ändern der Definition einer Ansicht relativ einfach, und die Indirektion durch eine Ansicht erfolgt, wenn der Abfrageplan kompiliert wird Fügen Sie die Spalte zu einer neuen Tabelle hinzu und erstellen Sie die Ansicht JOIN in der Spalte.

Dies funktioniert natürlich nur, wenn Sie Fremdschlüssel zum Kaskadieren von Löschungen und so weiter verwenden können. Der andere Vorteil ist, dass Sie eine neue Tabelle mit einer Kombination der Daten erstellen und die Ansicht darauf richten können, ohne die Client-Nutzung zu stören.

Nur ein Gedanke.

1
D.Shawley

Ich würde einen von zwei Ansätzen empfehlen:

  1. Entwerfen Sie Ihre Datenbanktabellen unter Berücksichtigung möglicher Änderungen. Ich habe zum Beispiel mit Content Management Systemen gearbeitet, die Datenfelder in Inhalten regelmäßig ändern. Anstatt die physische Datenbankstruktur so zu erstellen, dass sie den anfänglichen CMS-Feldanforderungen entspricht, ist es viel besser, eine flexible Struktur zu erstellen. In diesem Fall verwenden Sie ein Blob-Textfeld (z. B. varchar (max)), um flexible XML-Daten zu speichern. Dadurch werden strukturelle Veränderungen sehr seltener. Strukturelle Änderungen können kostspielig sein, daher sind auch hier Kostenvorteile zu erwarten.

  2. Haben Sie Systemwartungszeit. Entweder wird das System während Änderungen (monatlich usw.) offline geschaltet, und die Änderungen werden während der am wenigsten frequentierten Tageszeit (z. B. 3 bis 5 Uhr morgens) geplant. Die Änderungen werden vor dem Produktions-Rollout bereitgestellt, sodass Sie eine gute Schätzung der Ausfallzeiten im festgelegten Zeitfenster haben.

2a. Verfügen Sie über redundante Server, damit die gesamte Site nicht ausfällt, wenn das System ausfällt. Auf diese Weise können Sie Ihre Updates gestaffelt "rollen", ohne die gesamte Site zu beschädigen.

Die Optionen 2 und 2a sind möglicherweise nicht durchführbar. Sie sind in der Regel nur für größere Standorte/Operationen. Es handelt sich jedoch um gültige Optionen, und ich habe alle hier vorgestellten Optionen persönlich verwendet.

1
pearcewg

Wie SeanDowney erwähnt hat, pt-online-schema-change ist eines der besten Tools, um das zu tun, was Sie in der Frage hier beschrieben haben. Ich habe kürzlich viele Schemaänderungen an einer Live-DB vorgenommen und es lief ziemlich gut. Sie können mehr darüber in meinem Blog-Beitrag hier lesen: http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/ .

1
Rafay

Dummy-Spalten sind eine gute Idee, wenn Sie ihren Typ vorhersagen können (und sie auf Null setzen können). Überprüfen Sie, wie Ihre Speicher-Engine mit Nullen umgeht.

MyISAM sperrt alles, wenn Sie beim Telefonieren am Flughafen im Vorbeigehen sogar einen Tischnamen erwähnen. Das macht es einfach ...

Davon abgesehen sind Schlösser keine wirklich große Sache; Solange Sie nicht versuchen, jeder Zeile einen Standardwert für die neue Spalte hinzuzufügen, sondern diesen Wert auf Null setzen und Ihre Speicher-Engine klug genug ist, ihn nicht zu schreiben, sollten Sie mit einer Sperre einverstanden sein, die es nur gibt lang genug gehalten, um die Metadaten zu aktualisieren. Wenn Sie versuchen, einen neuen Wert zu schreiben, sind Sie ein Trinkspruch.

0
SquareCog

TokuDB kann Spalten hinzufügen/löschen und Indizes "hot" hinzufügen, die Tabelle ist während des gesamten Prozesses voll verfügbar. Es ist über www.tokutek.com erhältlich

0
tmcallaghan