Aktualizacja [2017]: MySQL 5.6 obsługuje aktualizacje indeksów online
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-index-syntax-notes
W MySQL 5.6 i nowszych, tabela pozostaje dostępna dla operacji odczytu i zapisu podczas tworzenia lub usuwania indeksu. Instrukcja CREATE INDEX lub DROP INDEX kończy się dopiero po zakończeniu wszystkich transakcji, które uzyskują dostęp do tabeli, tak aby początkowy stan indeksu odzwierciedlał najnowszą zawartość tabeli. Wcześniej modyfikowanie tabeli podczas tworzenia lub usuwania indeksu powodowało zwykle zakleszczenie, które powodowało anulowanie instrukcji INSERT, UPDATE lub DELETE w tabeli.
[2015] Aktualizacja tabeli wskazuje na bloki zapisów w MySQL 5.5
Z powyższej odpowiedzi:
„Jeśli używasz wersji wyższej niż 5.1, indeksy są tworzone, gdy baza danych jest online. Nie martw się, nie przerywasz korzystania z systemu produkcyjnego”.
To jest **** FALSE **** (przynajmniej dla tabel MyISAM / InnoDB, których używa 99,999% ludzi. Wersja Clustered jest inna).
Wykonywanie operacji UPDATE na tabeli spowoduje ZABLOKOWANIE podczas tworzenia indeksu. MySQL jest naprawdę głupi w tym (i kilku innych rzeczach).
Skrypt testowy:
(
for n in {1..50}; do
#(time mysql -uroot -e 'select * from website_development.users where id = 41225\G'>/dev/null) 2>&1 | grep real;
(time mysql -uroot -e 'update website_development.users set bio="" where id = 41225\G'>/dev/null) 2>&1 | grep real;
done
) | cat -n &
PID=$!
sleep 0.05
echo "Index Update - START"
mysql -uroot website_development -e 'alter table users add index ddopsonfu (last_name, email, first_name, confirmation_token, current_sign_in_ip);'
echo "Index Update - FINISH"
sleep 0.05
kill $PID
time mysql -uroot website_development -e 'drop index ddopsonfu on users;'
Mój serwer (InnoDB):
Server version: 5.5.25a Source distribution
Dane wyjściowe (zwróć uwagę, jak szóste bloki operacji przez ~ 400 ms potrzebne do zakończenia aktualizacji indeksu):
1 real 0m0.009s
2 real 0m0.009s
3 real 0m0.009s
4 real 0m0.012s
5 real 0m0.009s
Index Update - START
Index Update - FINISH
6 real 0m0.388s
7 real 0m0.009s
8 real 0m0.009s
9 real 0m0.009s
10 real 0m0.009s
11 real 0m0.009s
W porównaniu z operacjami odczytu, które nie blokują (zamień komentarz linii w skrypcie):
1 real 0m0.010s
2 real 0m0.009s
3 real 0m0.009s
4 real 0m0.010s
5 real 0m0.009s
Index Update - START
6 real 0m0.010s
7 real 0m0.010s
8 real 0m0.011s
9 real 0m0.010s
...
41 real 0m0.009s
42 real 0m0.010s
43 real 0m0.009s
Index Update - FINISH
44 real 0m0.012s
45 real 0m0.009s
46 real 0m0.009s
47 real 0m0.010s
48 real 0m0.009s
Aktualizacja schematu MySQL bez przestojów
Tak więc, jest tylko jedna metoda, którą znam, aby zaktualizować schemat MySql i uniknąć przerwy w dostępności. Mistrzowie cyrkularni:
- Master A ma uruchomioną bazę danych MySQL
- Doprowadzić Master B do użytku i powtórzyć zapisy z Mastera A (B jest niewolnikiem A)
- Wykonaj aktualizację schematu w Master B. Zostanie ona opóźniona podczas aktualizacji
- Niech mistrz B dogoni. Niezmienny: zmiana schematu MUSI umożliwiać przetwarzanie poleceń replikowanych ze schematu obniżonej wersji. Zmiany indeksowania kwalifikują się. Zwykle kwalifikują się proste dodatki do kolumn. Usunąć kolumnę? prawdopodobnie nie.
- ATOMICZNIE zamień wszystkich klientów z Master A na Master B. Jeśli chcesz być bezpieczny (zaufaj mi, masz), powinieneś upewnić się, że ostatni zapis do A zostanie zreplikowany do B PRZEDB dokonuje pierwszego zapisu. Jeśli zezwolisz na równoczesne zapisy do 2+ masterów, ... lepiej zrozumiesz replikację MySQL na poziomie DEEP lub udajesz się w świat bólu. Ekstremalny ból. Na przykład, czy masz kolumnę o nazwie AUTOINCREMENT ??? masz przerąbane (chyba że używasz parzystych liczb na jednym mistrzu, a kursów na drugim). NIE ufaj replikacji MySQL, że „postępuje właściwie”. To NIE jest mądre i nie uratuje cię. Jest to tylko trochę mniej bezpieczne niż kopiowanie binarnych dzienników transakcji z wiersza poleceń i ponowne ich ręczne odtwarzanie. Mimo to odłączenie wszystkich klientów od starego serwera głównego i przełączenie ich na nowego głównego można wykonać w ciągu kilku sekund, znacznie szybciej niż czekanie na wielogodzinną aktualizację schematu.
- Teraz Mistrz B jest twoim nowym mistrzem. Masz nowy schemat. Życie jest dobre. Mieć piwo; najgorsze minęło.
- Powtórz ten proces z mistrzem A, aktualizując jego schemat, aby stał się twoim nowym drugim mistrzem, gotowym do przejęcia władzy w przypadku, gdy twój główny mistrz (teraz mistrz B) utraci moc lub po prostu podniesie się i umrze wraz z tobą.
Prosty sposób na aktualizację schematu to nie jest. Wykonalne w poważnym środowisku produkcyjnym; Tak to jest. Proszę, proszę, proszę, jeśli istnieje łatwiejszy sposób na dodanie indeksu do tabeli MySQL bez blokowania zapisów, daj mi znać.
Googlowanie doprowadziło mnie do tego artykułu, w którym opisano podobną technikę. Co więcej, radzą pić w tym samym momencie procedury (zwróć uwagę, że napisałem swoją odpowiedź przed przeczytaniem artykułu)!
Percona's pt-online-schema-change
Artykuł I połączone powyżej opowiada o narzędzia, pt-online-schemat-change , że działa w następujący sposób:
- Utwórz nową tabelę z taką samą strukturą jak oryginał.
- Zaktualizuj schemat w nowej tabeli.
- Dodaj wyzwalacz do oryginalnej tabeli, aby zmiany były zsynchronizowane z kopią
- Kopiuj wiersze partiami z oryginalnej tabeli.
- Usuń oryginalny stół z drogi i zastąp go nowym.
- Upuść stary stół.
Sam nigdy nie próbowałem tego narzędzia. YMMV
RDS
Obecnie używam MySQL za pośrednictwem RDS firmy Amazon . To naprawdę sprytna usługa, która zarządza MySQL i zarządza nią, umożliwiając dodawanie nowych replik do odczytu za pomocą jednego przycisku i przejrzystą aktualizację bazy danych w różnych jednostkach SKU sprzętu. To naprawdę wygodne. Nie uzyskujesz SUPER dostępu do bazy danych, więc nie możesz wkręcać w replikację bezpośrednio (czy to błogosławieństwo czy przekleństwo?). Możesz jednak skorzystać z promocji Read Replica, aby wprowadzić zmiany w schemacie na urządzeniu podrzędnym tylko do odczytu, a następnie wypromować go na nowego mistrza. Dokładnie ta sama sztuczka, którą opisałem powyżej, tylko znacznie łatwiejsza do wykonania. Nadal nie robią zbyt wiele, aby pomóc ci w cięciu. Musisz ponownie skonfigurować i ponownie uruchomić aplikację.