ALTER TABLE ohne den Tisch zu sperren?
Wenn Sie eine ALTER TABLE-Anweisung in MySQL die gesamte Tabelle gelesen wird-gesperrt für die Dauer der Anweisung. Wenn es einen großen Tisch, das bedeutet, dass insert-oder update-Anweisungen gesperrt werden könnte für einen laaaangen Zeit. Gibt es einen Weg, um eine "hot verändern", wie das hinzufügen einer Spalte in einer Weise, dass die Tabelle ist immer noch aktualisierbar ist während des gesamten Prozesses?
Vor allem bin ich daran interessiert, eine Lösung für MySQL, aber mich würde interessieren, in anderen RDBMS, wenn MySQL nicht kann.
Klären, mein Ziel ist einfach, um Ausfallzeiten zu vermeiden, wenn ein neues feature, das erfordert eine extra Tabelle Spalte geschoben Produktion. Jede Datenbank-schema wird im Laufe der Zeit ändern, das ist einfach eine Tatsache des Lebens. Ich sehe nicht ein, warum wir akzeptieren sollten, dass diese Veränderungen müssen zwangsläufig auch zu Ausfallzeiten führen; das ist einfach nur schwach.
InformationsquelleAutor der Frage Daniel | 2009-01-21
Du musst angemeldet sein, um einen Kommentar abzugeben.
Die einzige andere Möglichkeit ist, manuell zu tun, was viele RDBMS-Systeme ohnehin tun...
- Erstellen Sie eine neue Tabelle
Können Sie dann kopieren Sie den Inhalt der alten Tabelle über ein Stück zu einer Zeit. Während immer vorsichtig bei jedem INSERT/UPDATE/DELETE auf der Quell-Tabelle. (Könnte sein, verwaltet von einem trigger ausgelöst. Obwohl, das wäre der Grund für die langsame nach unten, es ist nicht ein Schloss...)
Sobald Sie fertig sind, ändern Sie den Namen der Quelltabelle, dann ändern Sie den Namen der neuen Tabelle ein. Vorzugsweise in einer Transaktion.
Sobald Sie fertig sind, kompilieren Sie alle gespeicherten Prozeduren, etc, verwenden Sie die Tabelle. Die Ausführungspläne werden, wahrscheinlich nicht mehr gültig.
EDIT:
Einige Anmerkungen, die gemacht wurden, über diese Beschränkung ein wenig dürftig. Also ich dachte, ich würde setzen eine neue Perspektive auf Sie zu zeigen, warum es ist wie es ist...
InformationsquelleAutor der Antwort MatBailie
Percona macht ein tool namens pt-online-schema-changedass dies getan werden.
Es stellt im wesentlichen eine Kopie der Tabelle und ändert die neue Tabelle. Halten Sie die neue Tabelle in sync mit dem original verwendet Trigger zu aktualisieren. Dies ermöglicht es der original-Tabelle, auf die zugegriffen werden, während die neue Tabelle bereit ist, in den hintergrund.
Dies ist ähnlich Dems vorgeschlagene Methode oben, aber das ist so in einer automatisierten Weise.
Einige Ihrer Werkzeuge haben eine Lernkurve, nämlich mit der Datenbank verbinden, aber sobald Sie haben, Sie sind großartige Werkzeuge zu haben.
Ex:
InformationsquelleAutor der Antwort SeanDowney
Siehe Facebook, die online-schema-change tool.
http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932
Nicht für das schwache des Herzens; aber es wird den job zu erledigen.
InformationsquelleAutor der Antwort Steven Soroka
Empfehle ich, Postgres, wenn das ist eine option. Mit postgres gibt es im Grunde keine Ausfallzeiten durch die folgenden Verfahren:
Andere große feature ist, dass die meisten DDL-Anweisungen sind transaktional, so dass Sie könnte eine gesamte migration innerhalb einer SQL-Transaktion, und wenn etwas schief geht, die ganze Sache wird ein Rollback ausgeführt.
Schrieb ich diese ein bisschen her ist, vielleicht kann es ein bisschen mehr Einblick auf die anderen Vorzüge.
InformationsquelleAutor der Antwort mikelikespie
Diese Frage aus dem Jahr 2009. Jetzt bietet MySQL eine Lösung:
online-DDL -
Es ermöglicht Ihnen die Anpassung der balance zwischen Leistung und Parallelität während der DDL-Vorgang, indem Sie entscheiden, ob Sie blockieren Sie den Zugriff auf die Tabelle vollständig (LOCK=EXKLUSIV-Klausel) können Sie Abfragen, aber keine DML (LOCK=SHARED-Klausel), oder erlauben die volle Abfrage-und DML-Zugriffe auf die Tabelle (LOCK=KEINE Ziffer). Wenn Sie weglassen die LOCK-Klausel oder spezifizieren Sie LOCK=DEFAULT, MySQL erlaubt, so viel Parallelität wie möglich, je nach der Art der operation.
Durchführung von änderungen in-place-wo es möglich ist, anstatt eine neue Kopie der Tabelle, vermeidet eine vorübergehende Erhöhungen der Verwendung von Speicherplatz und I/O-overhead im Zusammenhang mit dem kopieren der Tabelle und die Rekonstruktion von sekundären Indizes.
sehen MySQL 5.6 Reference Manual -> InnoDB und Online DDL für mehr info.
Scheint es, dass online-DDL-auch in MariaDB
MariaDB KB über ALTER TABLE
InformationsquelleAutor der Antwort Fyodor Glebov
Da fragte man zu anderen Datenbanken, hier einige Informationen über Oracle.
Hinzufügen einer NULL-Spalte in einer Oracle-Tabelle ist eine sehr schnelle operation, da Sie nur updates, die das data dictionary. Dieser hält einen exklusiven lock auf die Tabelle für eine sehr kurze Zeit. Es wird jedoch die Gültigkeit abhängiger von gespeicherten Prozeduren, sichten, Trigger, etc. Diese erhalten automatisch neu kompiliert.
Von dort, wenn nötig, können Sie die create index mit der ONLINE-Klausel. Wieder nur sehr kurz-data dictionary locks. Es werden Lesen Sie die gesamte Tabelle, nach Sachen zu suchen, zu indizieren, aber nicht block irgendjemand während dies zu tun.
Wenn Sie brauchen, um hinzuzufügen, ein fremder Schlüssel, die Sie tun können, dies und Oracle, Ihnen zu Vertrauen, dass die Daten korrekt sind. Ansonsten muss Lesen Sie die gesamte Tabelle, und überprüfen Sie alle Werte, die können langsam sein (der index erstellt werden zuerst).
Wenn Sie brauchen, um eine Standard-oder berechnete Wert in jeder Zeile der neuen Spalte, die Sie ausführen müssen, ein massives update, oder vielleicht ein kleines Hilfsprogramm, das füllt die neuen Daten. Dies kann langsam sein, vor allem, wenn die Zeilen zu bekommen viel größer und passen nicht mehr in Ihre Blöcke. Sperren verwaltet werden können, während dieses Prozesses. Da der alte versino Ihrer Anwendung, die noch nicht wissen, über diese Spalte müssen Sie möglicherweise einen hinterhältigen trigger oder geben Sie einen Standard.
Von dort aus können Sie eine switcharoo auf Ihrem Anwendung-Server auf die neue version des Codes, und es wird laufen. Lassen Sie Ihre hinterhältigen auslösen.
Alternativ können Sie DBMS_REDEFINITION ist eine black-box, entworfen, um zu tun diese Art der Sache.
Das ist alles so viel Aufwand, um zu testen, etc, wir haben nur eine am frühen Sonntag morgen Ausfall, wenn wir release einer major-version.
InformationsquelleAutor der Antwort WW.
Wenn Sie sich nicht leisten können Ausfallzeiten für Ihre Datenbank, wenn dabei die Anwendung von updates sollten Sie überlegen, die Aufrechterhaltung einer zwei-Knoten-cluster für hohe Verfügbarkeit. Mit einem einfachen Einrichtung der Replikation, die Sie tun könnten, nahezu vollständig online-strukturelle Veränderungen, wie die, die Sie vorschlagen:
Es ist nicht immer einfach, aber es funktioniert, in der Regel mit 0 downtime! Der zweite Knoten nicht nur passiv, es kann für Testzwecke verwendet werden, dabei Statistiken oder als fallback-Knoten.
Wenn Sie nicht über die Infrastruktur kann die Replikation innerhalb einer einzigen Maschine (mit zwei Instanzen von MySQL).
InformationsquelleAutor der Antwort jynus
NÖ. Wenn Sie InnoDB-Tabellen zu meinem besten wissen, dass Sie nur die Tabelle sperrt - es gibt keine Aufzeichnungen sperren, die Sie einfach versuchen Sie, zu halten alles, was hyperfast durch Einfachheit. (Andere MySQL-Tabellen, die anders funktionieren.) In jedem Fall können Sie kopieren Sie die Tabelle zu einer anderen Tabelle ändern, und dann schalten Sie die Aktualisierung für die Unterschiede.
Dies ist, wie eine massive Veränderung, ich bezweifle, dass alle DBMS unterstützen würde. Es ist als ein Vorteil zu sein in der Lage, es zu tun mit den Daten in der Tabelle auf dem ersten Platz.
InformationsquelleAutor der Antwort dkretz
Temporäre Lösung...
Andere Lösung könnte sein, fügen Sie eine weitere Tabelle mit Primärschlüssel der ursprünglichen Tabelle, zusammen mit Ihrer neuen Spalte ein.
Füllen Sie Ihre Primärschlüssel in der neuen Tabelle, und füllen Sie die Werte für die neue Spalte in der neuen Tabelle, und ändern Sie Ihre Abfrage, an diesen Tisch für select-Operationen, und Sie müssen auch insert -, update-separat für diese Spalte Wert.
Wenn Sie in der Lage zu erhalten, Ausfallzeiten, können Sie verändern die ursprüngliche Tabelle, ändern Sie Ihre DML-Abfragen-und-drop Ihre neue Tabelle erstellt frühere
Anderen, können Sie gehen für die clustering-Methode, Replikation, pt-online-schema-tool von percona
InformationsquelleAutor der Antwort Balasundaram
Verwendung der Innodb-plugin, ALTER TABLE-Anweisungen, die nur hinzufügen oder löschen eines sekundären Indizes getan werden kann, "schnell", d.h., ohne die Neuerstellung der Tabelle.
Generell jedoch in den MySQL-ALTER-TABELLE beinhaltet den Wiederaufbau der gesamten Tabelle, die eine sehr lange Zeit (D. H., wenn die Tabelle eine nützliche Menge der Daten, die in it).
Sie wirklich brauchen, um Ihre Anwendung so entwerfen, dass ALTER TABLE-Anweisungen müssen nicht regelmäßig durchgeführt werden; Sie wollen sicher nicht, dass ALTER-TABELLE erfolgt während der normalen Ausführung der Anwendung, es sei denn, Sie sind bereit zu warten oder Sie zu verändern winzige Tische.
InformationsquelleAutor der Antwort MarkR
Ich würde empfehlen, eine von zwei Methoden:
Gestalten Sie Ihre Datenbank-Tabellen mit den möglichen änderungen in den Sinn. Zum Beispiel, ich habe mit Content-Management-Systeme, die Daten ändern Felder in content regelmäßig. Anstelle des Aufbaus der physikalischen Struktur der Datenbank übereinstimmen, die erste CMS-Bereich Anforderungen, es ist viel besser zu bauen, die in einer flexiblen Struktur. In diesem Fall ist die Verwendung einer blob-Textfeld (varchar(max) zum Beispiel) zu halten, flexible XML-Daten. Dies macht strukturelle Veränderungen sehr viel weniger Häufig. Strukturelle änderungen können teuer werden, so gibt es eine Kosten-nutzen-hier auch.
System-Pflege. Entweder das system offline geht, während änderungen (monatlich, etc), und die Veränderungen, die geplant sind, während die zuletzt stark beanspruchte Zeit von der Tag (3-5 Uhr morgens zum Beispiel). Die änderungen sind inszeniert vor der Produktivsetzung, so haben Sie einen guten festen Fenster Schätzung der Ausfallzeit.
2a. Redundante Server, so dass, wenn die system-Ausfallzeiten, die ganze Website nicht nach unten gehen. Dieses würde Ihnen erlauben, zu "Rollen" Ihre updates in eine gestaffelte Mode, ohne dabei die ganze Seite nach unten.
Optionen 2 und 2a darf nicht möglich sein; Sie tendieren dazu, sich nur für größere sites/Operationen. Sie gültig sind Optionen, aber, und ich habe persönlich alle Möglichkeiten, die hier vorgestellt.
InformationsquelleAutor der Antwort pearcewg
Im Allgemeinen, die Antwort ist "Nein". Sie ändern die Struktur der Tabelle, die möglicherweise erfordern eine Menge von updates" und ich Stimme definitiv mit, dass. Wenn Sie erwarten, tun dies oft, dann werde ich eine alternative zu "dummy" - Spalten - verwenden Sie
VIEWs statt Tabellen fürSELECTing die Daten. Wenn ich mich Recht erinnere, die änderung der definition einer Sicht ist relativ leicht und der Umweg über eine view ist fertig, wenn der Abfrageplan kompiliert wird. Die Kosten müssten Sie hinzufügen, um die Spalte in eine neue Tabelle und stellen Sie die AnsichtJOINin der Spalte.Natürlich funktioniert dies nur, wenn Sie über Fremdschlüssel zu führen Kaskadierung löscht und so weiter. Der andere bonus ist, dass Sie können erstellen Sie eine neue Tabelle mit einer Kombination der Daten und zeigen Sie die Ansicht, um es ohne störende client-Nutzung.
Nur ein Gedanke.
InformationsquelleAutor der Antwort D.Shawley
Als SeanDowney erwähnt hat,
pt-online-schema-changeist eines der besten Werkzeuge, um zu tun, was Sie beschrieben haben, in der Frage hier. Ich habe vor kurzem eine Menge von schema-änderungen auf einem live-DB und es ging ziemlich gut. Lesen Sie mehr darüber auf meinem blog-post hier: http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/.InformationsquelleAutor der Antwort Rafay
Sollten Sie auf jeden Fall versuchen
pt-online-schema-change. Ich habe mit diesem tool zu tun Migrationen auf AWS RDS mit mehreren slaves und es hat sehr gut funktioniert für mich. Ich schrieb einen aufwendigen blog-post auf, wie das zu tun, was für Sie nützlich sein könnte.Blog: http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/
InformationsquelleAutor der Antwort Rafay
Dummy-Spalten sind eine gute Idee, wenn Sie voraussehen können, Ihre Art (und machen Sie null-Werte zulässt). Prüfen Sie, wie Ihre storage-engine behandelt null-Werte.
InnoDB sperrt sich alles, wenn du noch erwähnen, dass ein Tabellenname übergeben, am Telefon, am Flughafen. Es funktioniert einfach,...
Dass gesagt wird, die sperren sind nicht wirklich schlimm, solange Sie nicht versuchen, fügen Sie eine default-Wert für die neue Spalte, in jeder Zeile, aber lassen Sie es sich als null ist, und Ihre Speicher-engine ist klug genug, nicht zu gehen, Sie zu schreiben, Sie sollten ok sein, mit einer Sperre, die nur lang genug gehalten, um aktualisieren Sie die Metadaten. Wenn Sie versuchen, schreiben Sie einen neuen Wert, gut, Sie sind toast.
InformationsquelleAutor der Antwort SquareCog
Falls es jemand noch Lesen, dieser oder kommen passiert hier, das ist der große Vorteil der Verwendung einer NoSQL-Datenbank-system wie mongodb. Ich hatte das gleiche Problem, der Umgang mit Veränderung die Tabelle entweder Spalten hinzufügen, um zusätzliche Funktionen oder Indizes für eine große Tabelle mit Millionen von Zeilen und hoch schreibt. Es würde am Ende der sperren für eine sehr lange Zeit, damit diese auf der LIVE-Datenbank würde frustrieren unsere Nutzer. Auf kleinen Tischen, die Sie bekommen können Weg mit es.
Ich hasse die Tatsache, dass wir "gestalten unsere Tabellen zu vermeiden, verändern Sie". Ich glaube einfach nicht, das funktioniert in der heutigen website Welt. Sie können nicht Vorhersagen, wie die Leute Ihre software verwenden, die ist, warum Sie sich rasch die Dinge ändern, basierend auf Benutzer-feedback. Mit mongodb, können Sie "Spalten" wird ohne Ausfallzeiten. Sie nicht wirklich noch hinzufügen, fügen Sie einfach Daten mit neuen Spalten, und es tut es automatisch.
Lohnt sich: http://www.mongodb.com
InformationsquelleAutor der Antwort Brian Gruber
TokuDB kann hinzufügen/löschen von Spalten und Indizes hinzufügen "heiß", wird die Tabelle vollständig verfügbar, während des gesamten Prozesses. Es ist erhältlich über http://www.tokutek.com
InformationsquelleAutor der Antwort tmcallaghan
Den Unterschied zwischen Postgres und MySQL in diesem Zusammenhang ist, dass in Postgres es nicht wieder eine Tabelle erstellt, aber die Daten ändert Wörterbuch, die ähnlich zu Oracle. Die Bedienung ist schnell, solange es noch benötigt, um zu reservieren eine exklusive DDL-Tabelle sperren für sehr kurze Zeit, wie oben angegeben, von anderen.
In MySQL die operation kopieren von Daten in eine neue Tabelle, während die Blockierung von Transaktionen, die wurde, die wichtigsten Schmerz für MySQL DBAs vor der v. 5.6.
Die gute Nachricht ist, dass seit MySQL 5.6 release die Beschränkung wurde meist gehoben und Sie können jetzt genießen Sie die wahre macht der MYSQL-DB.
InformationsquelleAutor der Antwort Dmitriy Royzenberg
Nicht wirklich.
Sie ändern die zugrunde liegende Struktur der Tabelle, nachdem alle, und das ist ein bit der Informationen, das ist ganz wichtig, um das zugrunde liegende system. Sie sind auch (wahrscheinlich) bewegt sich sehr viel von den Daten auf der Festplatte.
Wenn Sie planen, dies zu tun eine Menge, sind Sie besser dran, einfach die Polsterung der Tabelle mit "dummy" - Spalten, die verfügbar sind für die zukünftige Verwendung.
InformationsquelleAutor der Antwort Will Hartung