ALTER TABLE bez blokowania stołu?


107

Podczas wykonywania instrukcji ALTER TABLE w MySQL cała tabela jest blokowana do odczytu (zezwala na równoczesne odczyty, ale zabrania współbieżnych zapisów) na czas trwania instrukcji. Jeśli jest to duża tabela, instrukcje INSERT lub UPDATE mogą zostać zablokowane na dłuuuugi czas. Czy istnieje sposób na wykonanie „gorącej zmiany”, na przykład dodanie kolumny w taki sposób, aby tabela była nadal aktualizowana w trakcie całego procesu?

Przede wszystkim jestem zainteresowany rozwiązaniem dla MySQL, ale byłbym zainteresowany innymi RDBMS, jeśli MySQL nie może tego zrobić.

Aby wyjaśnić, moim celem jest po prostu uniknięcie przestojów, gdy nowa funkcja, która wymaga dodatkowej kolumny tabeli, jest wypychana do produkcji. Każdy schemat bazy danych będzie się z czasem zmieniać, to po prostu fakt. Nie widzę powodu, dla którego mielibyśmy zaakceptować fakt, że te zmiany muszą nieuchronnie skutkować przestojami; to jest po prostu słabe.


2
Zastanawiasz się, ile razy będziesz zmieniać tabelę?
Allain Lalonde

1
IMHO, zmiany schematu bazy danych są powiązane z zupełnie nowymi wersjami - nie są wprowadzane sporadycznie, jak robią to inne zmiany. To nieuchronnie wielka sprawa.
dkretz

9
@AllainLalonde - więcej niż 0 razy to pytanie jest uzasadnione, zwłaszcza jeśli przestój w systemie kosztowałby życie lub dużo pieniędzy. W każdym razie czasami pojawiają się nowe wymagania dotyczące oprogramowania.
Nathan Long

Odpowiedzi:


60

Jedyną inną opcją jest zrobienie ręcznie tego, co i tak robi wiele systemów RDBMS ...
- Utwórz nową tabelę

Następnie możesz skopiować zawartość starej tabeli na porcję naraz. Zawsze zachowując ostrożność w przypadku jakichkolwiek INSERT / UPDATE / DELETE w tabeli źródłowej. (Może być zarządzany przez wyzwalacz. Chociaż spowodowałoby to spowolnienie, nie jest to blokada ...)

Po zakończeniu zmień nazwę tabeli źródłowej, a następnie zmień nazwę nowej tabeli. Najlepiej w transakcji.

Po zakończeniu ponownie skompiluj wszystkie procedury składowane itp., Które używają tej tabeli. Plany wykonania prawdopodobnie stracą ważność.

EDYTOWAĆ:

Pojawiły się pewne komentarze, że to ograniczenie jest nieco słabe. Pomyślałem więc, że spojrzę na to z nowej perspektywy, aby pokazać, dlaczego tak jest ...

  • Dodanie nowego pola przypomina zmianę jednego pola w każdym wierszu.
  • Blokady polowe byłyby znacznie trudniejsze niż zamki rzędowe, nie wspominając o blokadach stołu.

  • W rzeczywistości zmieniasz fizyczną strukturę dysku, każdy rekord jest przenoszony.
  • To naprawdę jest jak AKTUALIZACJA całej tabeli, ale ma większy wpływ ...

2
I przygotuj dokładny plan testów przed zamianą. Jeśli to się nie powiedzie, zacznij od nowa.
dkretz

2
Zarządzanie synchronizacją za pomocą wyzwalaczy było fajnym pomysłem. Używam MySQL od tak dawna, że ​​zapominam, że mają teraz wyzwalacze. Użyłem tej techniki i teraz mam funkcjonalny skrypt hot-alter. Z paskiem postępu. Działa z MyISAM. Życie jest dobre.
Daniel

2
+1 To jest dosłownie to, co SQL Enterprise Manager robi za kulisami, gdy dokonujesz pewnych rodzajów zmian tabel w interfejsie użytkownika. W SQL 2008 faktycznie dodali ostrzeżenie, aby użytkownik WIE, że wykonuje tę drastyczną akcję.
BradC

2
Nie wspomniałeś nic o kluczach obcych odnoszących się do zmienianych tabel. Czy to nie byłby problem?
Rafay,

2
@MohammadRafayAleem - i pola AUTOINCREMENT, widoki, wyzwalacze itp., Itd. Ale mimo to podejście jest nadal wykonalne.
MatBailie,

42

Percona tworzy narzędzie zwane pt-online-schema-change, które to umożliwia.

Zasadniczo tworzy kopię tabeli i modyfikuje nową tabelę. Aby zachować synchronizację nowej tabeli z oryginalną, do aktualizacji używa wyzwalaczy. Umożliwia to dostęp do oryginalnej tabeli, podczas gdy nowa tabela jest przygotowywana w tle.

Jest to podobne do sugerowanej powyżej metody Demsa, ale odbywa się to w sposób zautomatyzowany.

Niektóre z ich narzędzi mają krzywą uczenia się, a mianowicie łączenie się z bazą danych, ale kiedy już to zrobisz, są świetnymi narzędziami.

Dawny:

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

Wygląda na to, że link jest uszkodzony. Okazało się, że ten link działa.
Noam Ben Ari

25

To pytanie z 2009 roku. Teraz MySQL oferuje rozwiązanie:

Online DDL (język definicji danych)

Funkcja poprawiająca wydajność, współbieżność i dostępność tabel InnoDB podczas operacji DDL (głównie ALTER TABLE). Szczegółowe informacje można znaleźć w sekcji 14.11, „InnoDB i Online DDL”.

Szczegóły różnią się w zależności od rodzaju operacji. W niektórych przypadkach tabela może być modyfikowana współbieżnie, podczas gdy ALTER TABLE jest w toku. Operację można wykonać bez kopiowania tabeli lub przy użyciu specjalnie zoptymalizowanego typu kopiowania tabeli. Wykorzystanie przestrzeni jest kontrolowane przez opcję konfiguracyjną innodb_online_alter_log_max_size.

Pozwala dostosować równowagę między wydajnością a współbieżnością podczas operacji DDL, wybierając, czy całkowicie blokować dostęp do tabeli (klauzula LOCK = EXCLUSIVE), zezwalać na zapytania, ale nie na DML (LOCK = klauzula SHARED) lub zezwalać na pełne zapytania i DML dostęp do tabeli (klauzula LOCK = NONE). W przypadku pominięcia klauzuli LOCK lub określenia LOCK = DEFAULT MySQL zezwala na możliwie największą współbieżność w zależności od typu operacji.

Wykonywanie zmian na miejscu, jeśli to możliwe, zamiast tworzenia nowej kopii tabeli, pozwala uniknąć tymczasowego wzrostu wykorzystania miejsca na dysku i obciążenia we / wy związanego z kopiowaniem tabeli i rekonstrukcją indeksów pomocniczych.

zobacz Podręcznik MySQL 5.6 -> InnoDB i Online DDL, aby uzyskać więcej informacji.

Wygląda na to, że DDL online jest również dostępny w MariaDB

Alternatywnie możesz użyć ALTER ONLINE TABLE, aby upewnić się, że ALTER TABLE nie blokuje współbieżnych operacji (nie ma blokad). Jest równoważne LOCK = NONE.

MariaDB KB o ALTER TABLE


3
Szkoda, że ​​nie ma innego sposobu niż głosy, aby przenieść to na szczyt, biorąc pod uwagę, że przeważnie neguje wszystkie inne odpowiedzi wyłącznie dlatego, że nie odwołują się już do aktualnej wersji MySQL.
Burhan Ali


14

Polecam Postgres, jeśli jest taka opcja. W przypadku postgres zasadniczo nie ma przestojów przy następujących procedurach:

Inną wspaniałą cechą jest to, że większość instrukcji DDL to transakcje transakcyjne, więc możesz wykonać całą migrację w ramach transakcji SQL, a jeśli coś pójdzie nie tak, wszystko zostanie wycofane.

Napisałem to trochę temu, być może może to rzucić nieco więcej wglądu w inne zalety.


6
Postgres nadal tworzy wyłączną blokadę alter, uniemożliwiając innym czytanie z tej tabeli.
clofresh,

5
Nie zgadzam się z fragmentem „zasadniczo nie ma przestojów”. Jak powiedział clofresh, ALTER TABLE przejmuje wyłączną blokadę tabeli, blokując wszystkie jednoczesne odczyty i zapisy. Z mojego doświadczenia wynika, że ​​dla aktywnych stołów przez większość czasu nawet nie dostaniesz blokady (ALTER TABLE będzie głodować). A w przypadku transakcji możesz łatwo skończyć z zakleszczeniem, jeśli nie jesteś zbyt ostrożny. Z tego powodu zawsze ustawiam przestoje podczas zmiany istniejących tabel w Postgres.
Pankrat

1
Bardziej szczegółowe wyjaśnienie: dba.stackexchange.com/questions/27153/ ... wspomina o konsekwencjach blokady na wyłączność i sposobach jej obejścia
John Douthat

4
Tak, zmiana tabeli w postgres powoduje nałożenie blokady na wyłączność, ale ponieważ sama operacja kończy się w milisekundach, w większości przypadków jest to praktycznie nieistotne. Osobiście dodałem kolumny do stu milionów wierszy tabel w środku dnia roboczego bez przestojów.
Noah Yetter

2
@cobbzilla Tak, DROP COLUMN działa równie szybko. Pod maską zasadniczo oznacza to kolumnę jako ukrytą. Wartości, które istniały w tej kolumnie przed usunięciem, nadal znajdują się w plikach danych (i są widoczne dla innych transakcji) i pozostaną takie, chyba że i dopóki nie wykonasz VACUUM FULL.
Noah Yetter

7

Ponieważ pytałeś o inne bazy danych, oto kilka informacji o Oracle.

Dodanie kolumny NULL do tabeli Oracle jest bardzo szybką operacją, ponieważ aktualizuje tylko słownik danych. To utrzymuje wyłączną blokadę na stole przez bardzo krótki czas. Spowoduje to jednak unieważnienie wszelkich zależnych procedur składowanych, widoków, wyzwalaczy itp. Zostaną one ponownie skompilowane automatycznie.

Stamtąd, jeśli to konieczne, możesz utworzyć indeks za pomocą klauzuli ONLINE. Ponownie, tylko bardzo krótkie blokady słownika danych. Odczyta całą tabelę, szukając rzeczy do zindeksowania, ale nikogo nie blokuje podczas wykonywania tej czynności.

Jeśli potrzebujesz dodać klucz obcy, możesz to zrobić i sprawić, by Oracle zaufało Ci, że dane są poprawne. W przeciwnym razie musi odczytać całą tabelę i zweryfikować wszystkie wartości, które mogą być powolne (najpierw utwórz indeks).

Jeśli chcesz umieścić wartość domyślną lub obliczoną w każdym wierszu nowej kolumny, musisz uruchomić masową aktualizację lub może mały program narzędziowy, który zapełni nowe dane. Może to być powolne, zwłaszcza jeśli rzędy stają się znacznie większe i nie mieszczą się już w swoich blokach. Podczas tego procesu można zarządzać blokowaniem. Ponieważ stara wersja Twojej aplikacji, która nadal działa, nie zawiera informacji o tej kolumnie, możesz potrzebować podstępnego wyzwalacza lub określić domyślną.

Stamtąd możesz wykonać przełączenie na serwerach aplikacji do nowej wersji kodu i będzie on nadal działać. Opuść podstępny spust.

Alternatywnie możesz użyć DBMS_REDEFINITION, która jest czarną skrzynką zaprojektowaną do tego typu rzeczy.

Wszystko to jest tak trudne do przetestowania, itp., Że mamy po prostu wczesną, niedzielną przerwę w pracy, ilekroć wydajemy wersję główną.


3

Jeśli nie możesz pozwolić sobie na przestoje dla bazy danych podczas wykonywania aktualizacji aplikacji, powinieneś rozważyć utrzymanie klastra z dwoma węzłami w celu zapewnienia wysokiej dostępności. Dzięki prostej konfiguracji replikacji można by dokonać prawie w całości zmian strukturalnych online, takich jak ta, którą sugerujesz:

  • poczekaj, aż wszystkie zmiany zostaną zreplikowane na pasywnym urządzeniu podrzędnym
  • zmienić pasywnego slave'a na aktywnego mastera
  • dokonaj zmian strukturalnych starego mistrza
  • replikować zmiany z nowego mastera do starego mastera
  • ponownie wykonaj główną zamianę i jednocześnie nowe wdrożenie aplikacji

Nie zawsze jest to łatwe, ale działa, zazwyczaj bez przestojów! Drugi węzeł nie musi być tylko pasywny, może służyć do testowania, tworzenia statystyk lub jako węzeł rezerwowy. Jeśli nie masz infrastruktury, replikację można skonfigurować w ramach jednego komputera (z dwoma wystąpieniami MySQL).


1
Stary wzorzec jest poza klastrem lub w klastrze?
John Chornelius,

2

Nie. Jeśli korzystasz z tabel MyISAM, zgodnie z moim najlepszym zrozumieniem, robią one tylko blokady tabel - nie ma blokad rekordów, po prostu starają się zachować wszystko błyskawicznie dzięki prostocie. (Inne tabele MySQL działają inaczej). W każdym przypadku możesz skopiować tabelę do innej tabeli, zmienić ją, a następnie przełączyć, aktualizując ze względu na różnice.

Jest to tak ogromna zmiana, że ​​wątpię, by jakikolwiek DBMS ją wspierał. Możliwość zrobienia tego z danymi w tabeli jest uważana za korzyść w pierwszej kolejności.



Tak, MySQL to aberracja. Dlatego byłem konkretny w kwestii „standardowych” tabel.
dkretz

Napisałeś - standardowe tabele MySQL tylko blokują tabele - co jest niepoprawne.
Eran Galperin

Jak to interpretujesz o tabelach MyISAM (tj. Standardu MySQL) z cytowanej strony? „MySQL używa blokowania na poziomie tabeli dla tabel MyISAM i MEMORY, blokowania na poziomie strony dla tabel BDB oraz blokowania na poziomie wiersza dla tabel InnoDB”.
dkretz

niektóre silniki magazynu używają blokowania na poziomie wiersza, a inne blokowania na poziomie tabeli. Nie ma standardowego mechanizmu przechowywania danych (może miałeś na myśli domyślny w phpMyAdmin ...)
Eran Galperin

2

Rozwiązanie tymczasowe ...

Innym rozwiązaniem może być dodanie kolejnej tabeli z kluczem podstawowym oryginalnej tabeli wraz z nową kolumną.

Wypełnij swój klucz podstawowy w nowej tabeli i wypełnij wartości dla nowej kolumny w nowej tabeli i zmodyfikuj zapytanie, aby dołączyć do tej tabeli dla operacji wybierania, a także musisz wstawić, zaktualizować oddzielnie dla tej wartości kolumny.

Gdy możesz uzyskać przestój, możesz zmienić oryginalną tabelę, zmodyfikować zapytania DML i porzucić nową, utworzoną wcześniej tabelę

W przeciwnym razie możesz skorzystać z metody klastrowania, replikacji, narzędzia pt-online-schema firmy Percona


1

Korzystając z wtyczki Innodb, instrukcje ALTER TABLE, które tylko dodają lub usuwają indeksy pomocnicze, mogą być wykonywane „szybko”, tj. Bez przebudowywania tabeli.

Jednak ogólnie rzecz biorąc, w MySQL każda ALTER TABLE wymaga przebudowania całej tabeli, co może zająć bardzo dużo czasu (tj. Jeśli tabela zawiera użyteczną ilość danych).

Naprawdę musisz zaprojektować swoją aplikację tak, aby instrukcje ALTER TABLE nie musiały być wykonywane regularnie; na pewno nie chcesz, aby ALTER TABLE była wykonywana podczas normalnego działania aplikacji, chyba że jesteś przygotowany na czekanie lub zmieniasz małe tabele.


1

Poleciłbym jedno z dwóch podejść:

  1. Projektuj tabele bazy danych z uwzględnieniem potencjalnych zmian. Na przykład pracowałem z systemami zarządzania treścią, które regularnie zmieniają pola danych w treści. Zamiast budować fizyczną strukturę bazy danych w celu dopasowania do początkowych wymagań pola CMS, znacznie lepiej jest zbudować elastyczną strukturę. W tym przypadku użycie pola tekstowego typu blob (na przykład varchar (max)) do przechowywania elastycznych danych XML. To sprawia, że ​​zmiany strukturalne są bardzo rzadsze. Zmiany strukturalne mogą być kosztowne, więc koszty również są korzystne.

  2. Miej czas na konserwację systemu. System przechodzi w tryb offline podczas zmian (co miesiąc itp.), A zmiany są planowane w czasie o najmniej intensywnym ruchu w ciągu dnia (na przykład 3-5 rano). Zmiany są wprowadzane etapowo przed wdrożeniem produkcyjnym, więc będziesz mieć dobre oszacowanie czasu przestoju w oknie.

2a. Posiadaj redundantne serwery, dzięki czemu podczas przestoju systemu cała witryna nie ulegnie awarii. Umożliwiłoby to „udostępnianie” aktualizacji w sposób rozłożony w czasie, bez wyłączania całej witryny.

Warianty 2 i 2a mogą być niewykonalne; zwykle dotyczą tylko większych witryn / operacji. Są to jednak ważne opcje i osobiście korzystałem ze wszystkich przedstawionych tutaj opcji.


1

Jeśli ktoś nadal to czyta lub przychodzi tutaj, jest to duża zaleta korzystania z systemu baz danych NoSQL, takiego jak mongodb. Miałem ten sam problem, jeśli chodzi o zmianę tabeli, aby dodać kolumny dla dodatkowych funkcji lub indeksy w dużej tabeli z milionami wierszy i wysokimi zapisami. W rezultacie blokowałby się na bardzo długi czas, więc zrobienie tego w bazie danych LIVE frustrowałoby naszych użytkowników. Na małych stolikach możesz uciec.

Nienawidzę faktu, że musimy „projektować nasze tabele, aby ich nie zmieniać”. Po prostu nie sądzę, żeby to działało w dzisiejszym świecie witryn internetowych. Nie możesz przewidzieć, jak ludzie będą używać twojego oprogramowania, dlatego szybko zmieniasz rzeczy na podstawie opinii użytkowników. Dzięki mongodb możesz dowolnie dodawać „kolumny” bez przestojów. Tak naprawdę nawet ich nie dodajesz, po prostu wstawiasz dane z nowymi kolumnami i robi to automatycznie.

Warto sprawdzić: www.mongodb.com


2
MySQL jest nadal używany w wielu systemach, więc tak naprawdę pytanie dotyczy tego, jak osiągnąć zmianę schematu w SQL RDBMS, nawet jeśli jestem zagorzałym zwolennikiem NoSQL.
Alexy

1

Ogólnie rzecz biorąc, odpowiedź brzmi „nie”. Zmieniasz strukturę tabeli, która potencjalnie będzie wymagała wielu aktualizacji. ”I zdecydowanie się z tym zgadzam. Jeśli spodziewasz się, że będziesz to robić często, zaoferuję alternatywę dla„ pustych ”kolumn - VIEWzamiast tego użyj s tabel dla SELECTdanych. IIRC, zmiana definicji widoku jest stosunkowo niewielka, a pośrednia zmiana widoku jest wykonywana podczas kompilacji planu kwerend. Koszt jest taki, że trzeba by dodać kolumnę do nowej tabeli i zobacz JOINw kolumnie.

Oczywiście działa to tylko wtedy, gdy można używać kluczy obcych do wykonywania kaskadowego usuwania i tak dalej. Drugą zaletą jest to, że możesz utworzyć nową tabelę zawierającą kombinację danych i skierować do niej widok bez zakłócania użytkowania klienta.

Tylko myśl.


1

Różnica między Postgresem a MySQL w tym zakresie polega na tym, że w Postgres nie odtwarza tabeli, ale modyfikuje słownik danych, który jest podobny do Oracle. Dlatego operacja jest szybka, podczas gdy nadal wymaga przydzielenia wyłącznej blokady tabeli DDL na bardzo krótki czas, jak stwierdzono powyżej przez innych.

W MySQL operacja skopiuje dane do nowej tabeli podczas blokowania transakcji, co było głównym problemem dla baz danych MySQL przed wersją 5.6.

Dobra wiadomość jest taka, że ​​od czasu wydania MySQL 5.6 ograniczenie zostało w większości zniesione i teraz możesz cieszyć się prawdziwą mocą bazy danych MYSQL.


3
Wygląda na to, że próbujesz utworzyć link do odniesienia dotyczącego zmiany w MySql 5.6, ale to nie zadziałało. Proszę spróbuj ponownie.
dg99



0

Kolumny atrapy są dobrym pomysłem, jeśli potrafisz przewidzieć ich typ (i uczynić je zerowymi). Sprawdź, jak Twój aparat pamięci obsługuje wartości zerowe.

MyISAM zablokuje wszystko, jeśli choćby wymienisz nazwę stolika podczas rozmowy telefonicznej na lotnisku. Po prostu to robi ...

Biorąc to pod uwagę, zamki nie są naprawdę takie duże; tak długo, jak nie próbujesz dodać domyślnej wartości dla nowej kolumny do każdego wiersza, ale pozostawisz ją jako zerową, a twój silnik magazynu jest wystarczająco inteligentny, aby go nie pisać, powinieneś być w porządku z blokadą, która jest tylko trzymany wystarczająco długo, aby zaktualizować metadane. Jeśli spróbujesz wpisać nową wartość, cóż, jesteś uniesiony.


1
Próbowałem dodać kolumnę NULL do tabeli InnoDB i musiałem odbudować całą tabelę; nie jest to prosta operacja „zaktualizuj metadane”.
Daniel

Myślę, że pomysł polegał na uwzględnieniu dodatkowych kolumn dopuszczających wartość null w bazie danych, gdy jest ona zaprojektowana, tak aby jeśli wymagana jest nowa funkcja, można było „dodać” nową kolumnę, po prostu zaczynając jej używać. Nie będzie miał ładnej nazwy, ale jeśli typ danych został poprawnie wybrany / przewidywany, powinien działać.
supercat

0

TokuDB może dodawać / usuwać kolumny i dodawać indeksy „na gorąco”, tabela jest w pełni dostępna przez cały proces. Jest dostępny na www.tokutek.com


-6

Nie całkiem.

W końcu ZMIENISZ podstawową strukturę tabeli, a to jest trochę informacji, które są dość ważne dla podstawowego systemu. Przenosisz również (prawdopodobnie) większość danych na dysku.

Jeśli planujesz robić to często, lepiej po prostu uzupełnij tabelę „fikcyjnymi” kolumnami, które będą dostępne do wykorzystania w przyszłości.


3
Wypełnienie tabeli fikcyjnymi kolumnami wydaje się być naprawdę złym pomysłem.
Jost
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.