ZMIEŃ TABELĘ na dużej tabeli z indeksowaną kolumną

Mam dużą tabelę z kolumną VARCHAR (20) i muszę ją zmodyfikować, aby stała się kolumną VARCHAR (50). Zazwyczaj wykonanie ALTER TABLE (dodanie TINYINT) na tym konkretnym stole zajmuje około 90-120 minut, więc naprawdę mogę to zrobić tylko w sobotę lub niedzielę wieczorem, aby uniknąć wpływu na użytkowników bazy danych. Jeśli to możliwe, chciałbym wcześniej dokonać tej modyfikacji.

Kolumna jest również indeksowana, co, jak zakładam, spowoduje spowolnienie ALTER TABLE, ponieważ musi ona odbudować indeks po zmodyfikowaniu długości kolumny.

Aplikacja internetowa jest skonfigurowana w środowisku replikacji MySQL (26 urządzeń slave i jeden master). Pamiętam, jak kiedyś czytałem, że jedną z metod jest najpierw wykonanie ALTER TABLE na każdym slave (minimalizowanie wpływu na użytkowników), a następnie zrobienie tego na Master, ale czy to nie będzie wtedy próba replikacji polecenia ALTER TABLE na slave?

Więc moje pytanie brzmi: jaki jest najlepszy sposób na zmodyfikowanie tej tabeli przy minimalnych zakłóceniach dla moich użytkowników?

Edycja: tabela to InnoDB.

Jeśli jesteś trochę ryzykowny, możesz wziąć sprawy w swoje ręce, wykonując ALTER TABLE w etapach, które możesz zobaczyć. Załóżmy, że tabela, którą chcesz zmienić, nazywa się WorkingTable. Możesz wykonać zmiany etapami w następujący sposób:

#
#  Script 1
#  Alter table structure of a single column of a large table
#
CREATE TABLE WorkingTableNew LIKE WorkingTable;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT * FROM WorkingTable;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

Możesz to zrobić na wszystkich niewolnikach. Co z mistrzem ??? Jak temu zapobiec replikacji do niewolników. Proste: nie wysyłaj kodu SQL do dzienników binarnych wzorca. Po prostu wyłącz logowanie binarne w sesji przed wykonaniem czynności ALTER TABLE:

#
#  Script 2
#  Alter table structure of a single column of a large table
#  while preventing it from replicating to slaves
#
SET SQL_LOG_BIN = 0;
CREATE TABLE WorkingTableNew LIKE WorkingTable;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTable;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

Ale poczekaj !!! A co z nowymi danymi przychodzącymi podczas przetwarzania tych poleceń? Zmiana nazwy tabeli na początku operacji powinna załatwić sprawę. Zmieńmy nieco ten kod, aby zapobiec wprowadzaniu nowych danych w tym zakresie:

#
#  Script 3
#  Alter table structure of a single column of a large table
#  while preventing it from replicating to slaves
#  and preventing new data from entering into the old table
#
SET SQL_LOG_BIN = 0;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
CREATE TABLE WorkingTableNew LIKE WorkingTableOld;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

• Skrypt 1 można wykonać na dowolnym urządzeniu podrzędnym, które nie ma włączonych dzienników binarnych
• Skrypt 2 można wykonać na dowolnym urządzeniu slave, które ma włączone dzienniki binarne
• Skrypt 3 można wykonać na systemie głównym lub w dowolnym innym miejscu

Spróbuj !!!

Domyślam się z dokumentacji , że zwykłe zwiększenie ograniczenia długości na varcharnie spowodowałoby takich samych problemów jak dodanie kolumny:

W przypadku niektórych operacji możliwy jest lokalny ALTER TABLE, który nie wymaga tabeli tymczasowej:

Ale wydaje się, że jest to sprzeczne z komentarzami do tego pisemnego zgłoszenia zastrzeżeń pytania .

EDYTOWAĆ

Przynajmniej w wersji 5.0, myślę, że mogę potwierdzić, że zwiększenie długości rzeczywiście wymaga tymczasowego stołu (lub innej równie kosztownej operacji):

testbed:

create table my_table (id int auto_increment primary key, varchar_val varchar(10));
insert into my_table (varchar_val)
select 'HELLO'
from (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) s1,
     (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) s2,
     (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) s3,
     (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) s4,
     (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) s5,
     (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) s6;

wynik:

alter table my_table modify varchar_val varchar(20);
Query OK, 1000000 rows affected (2.91 sec)

alter table my_table add int_val int;
Query OK, 1000000 rows affected (2.86 sec)

Myślałem, że wspomnę o tym, ponieważ ENGINE=INNODB

Jeśli masz ograniczenia na klucze obce, nie możesz zmieniać i zmieniać nazwy bez ograniczeń, wskazując na starą tabelę (teraz przemianowaną). Będziesz musiał później zmienić lub usunąć ograniczenia na czas trwania.