MySQL - najszybszy sposób na ALTER TABLE dla InnoDB

Mam tabelę InnoDB, którą chcę zmienić. Tabela ma ~ 80 milionów wierszy i zamknęła kilka indeksów.

Chcę zmienić nazwę jednej z kolumn i dodać jeszcze kilka indeksów.

• Jaki jest najszybszy sposób na zrobienie tego (zakładając, że mogę cierpieć nawet z powodu przestojów - serwer jest nieużywanym urządzeniem podrzędnym)?
• Czy „proste” alter table, najszybsze rozwiązanie?

W tej chwili liczy się tylko szybkość :)

Pewnym sposobem na przyspieszenie ALTER TABLE jest usunięcie niepotrzebnych indeksów

Oto pierwsze kroki, aby załadować nową wersję tabeli

CREATE TABLE s_relations_new LIKE s_relations;
#
# Drop Duplicate Indexes
#
ALTER TABLE s_relations_new
    DROP INDEX source_persona_index,
    DROP INDEX target_persona_index,
    DROP INDEX target_persona_relation_type_index
;

Uwaga:

• Upuściłem source_persona_index, ponieważ jest to pierwsza kolumna w 4 innych indeksach

  • Unique_target_persona
  • unikatowy cel
  • source_and_target_object_index
  • source_target_persona_index

• Upuściłem cel_persona_index, ponieważ jest to pierwsza kolumna w 2 innych indeksach

  • target_persona_relation_type_index
  • target_persona_relation_type_message_id_ind_index

• Upuściłem cel_persona_relation_type_index, ponieważ pierwsze 2 kolumny są również w target_persona_relation_type_message_id_index

OK To zajmuje się niepotrzebnymi indeksami. Czy są jakieś indeksy o niskiej liczności? Oto sposób, aby ustalić, że:

Uruchom następujące zapytania:

SELECT COUNT(DISTINCT sent_at)               FROM s_relations;
SELECT COUNT(DISTINCT message_id)            FROM s_relations;
SELECT COUNT(DISTINCT target_object_id)      FROM s_relations;

Zgodnie z Twoim pytaniem istnieje około 80 000 000 wierszy. Zasadniczo optymalizator zapytań MySQL nie będzie korzystał z indeksu, jeśli liczność wybranych kolumn jest większa niż 5% liczby wierszy tabeli. W tym przypadku byłoby to 4 000 000.

• Jeśli COUNT(DISTINCT sent_at)> 4 000 000
  • następnie ALTER TABLE s_relations_new DROP INDEX sent_at_index;
• Jeśli COUNT(DISTINCT message_id)> 4 000 000
  • następnie ALTER TABLE s_relations_new DROP INDEX message_id_index;
• Jeśli COUNT(DISTINCT target_object_id)> 4 000 000
  • następnie ALTER TABLE s_relations_new DROP INDEX target_object_index;

Po określeniu przydatności lub bezużyteczności tych indeksów można ponownie załadować dane

#
# Change the Column Name
# Load the Table
#
ALTER TABLE s_relations_new CHANGE sent_at sent_at_new int(11) DEFAULT NULL;
INSERT INTO s_relations_new SELECT * FROM s_relations;

To jest to, prawda? NIE !!!

Jeśli twoja strona działała cały czas, być może INSERT działa przeciwko s_relations podczas ładowania s_relations_new. Jak odzyskać brakujące wiersze?

Znajdź maksymalny identyfikator w s_relations_new i dołącz wszystko po tym identyfikatorze z s_relations. Aby upewnić się, że tabela jest zamrożona i używana tylko do tej aktualizacji, musisz mieć trochę przestoju w celu uzyskania tych ostatnich wierszy, które zostały wstawione do s_relation_new. Oto co robisz:

W systemie operacyjnym uruchom ponownie mysql, aby nikt inny nie mógł się zalogować, ale root @ localhost (wyłącza TCP / IP):

$ service mysql restart --skip-networking

Następnie zaloguj się do mysql i załaduj te ostatnie wiersze:

mysql> SELECT MAX(id) INTO @maxidnew FROM s_relations_new;
mysql> INSERT INTO s_relations_new SELECT * FROM s_relations WHERE id > @maxidnew;
mysql> ALTER TABLE s_relations RENAME s_relations_old;
mysql> ALTER TABLE s_relations_new RENAME s_relations;

Następnie uruchom ponownie mysql normalnie

$ service mysql restart

Teraz, jeśli nie możesz zdjąć mysql, będziesz musiał zrobić przynętę i przełączyć na s_relations. Zaloguj się do mysql i wykonaj następujące czynności:

mysql> ALTER TABLE s_relations RENAME s_relations_old;
mysql> SELECT MAX(id) INTO @maxidnew FROM s_relations_new;
mysql> INSERT INTO s_relations_new SELECT * FROM s_relations_old WHERE id > @maxidnew;
mysql> ALTER TABLE s_relations_new RENAME s_relations;

Spróbuj !!!

CAVEAT: Gdy będziesz zadowolony z tej operacji, możesz upuścić stary stół w najbliższym dogodnym momencie:

mysql> DROP TABLE s_relations_old;

Prawidłowa odpowiedź zależy od używanej wersji silnika MySQL.

W przypadku korzystania z wersji 5.6+ zmiana nazw oraz dodawanie / usuwanie indeksów odbywa się online , tzn. Bez kopiowania wszystkich danych tabeli.

Po prostu użyj, ALTER TABLEjak zwykle, będzie to w większości przypadków zmiana nazw i spadków indeksów oraz dość szybkie dodawanie indeksów (tak szybko, jak jednorazowe odczytanie całej tabeli).

Jeśli używasz 5.1+, a wtyczka InnoDB jest włączona, dodawanie / usuwanie indeksów będzie również dostępne online. Nie jestem pewien co do zmian nazw.

Jeśli używasz starszej wersji, ALTER TABLEnadal jest najszybsza - ale prawdopodobnie będzie strasznie powolna, ponieważ wszystkie twoje dane zostaną ponownie wstawione do tymczasowej tabeli pod maską.

Wreszcie czas na obalenie mitu. Niestety nie mam tutaj dość karmy, aby komentować odpowiedzi, ale uważam, że ważne jest, aby poprawić najczęściej głosowaną odpowiedź. To jest złe :

Zasadniczo optymalizator zapytań MySQL nie będzie korzystał z indeksu, jeśli liczność wybranych kolumn jest większa niż 5% liczby wierszy tabeli

W rzeczywistości jest na odwrót .

Wskaźniki są przydatne do wyboru kilku wierszy, dlatego ważne jest, aby miały wysoką liczność, co oznacza wiele odrębnych wartości i statystycznie kilka wierszy o tej samej wartości.

Miałem ten sam problem z Maria DB 10.1.12, a następnie po przeczytaniu dokumentacji stwierdziłem, że istnieje opcja wykonania operacji „w miejscu”, która eliminuje kopię tabeli. Dzięki tej opcji tabela zmian jest bardzo szybka. W moim przypadku było to:

alter table user add column (resettoken varchar(256),
  resettoken_date date, resettoken_count int), algorithm=inplace;

to jest bardzo szybkie. Bez opcji algorytmu nigdy by się nie skończyło.

https://mariadb.com/kb/en/mariadb/alter-table/

W przypadku zmiany nazwy kolumny

ALTER TABLE tablename CHANGE columnname newcolumnname datatype;

powinno być w porządku i nie powodować przestojów.

W przypadku indeksów instrukcja CREATE INDEX zablokuje tabelę. Jeśli jest to nieużywany niewolnik, jak wspomniałeś, to nie jest problem.

Inną opcją byłoby utworzenie zupełnie nowej tabeli, która ma odpowiednie nazwy kolumn i indeksy. Następnie możesz skopiować do niego wszystkie dane, a następnie wykonać serię

BEGIN TRAN;
ALTER TABLE RENAME tablename tablenameold;
ALTER TABLE RENAME newtablename tablename;
DROP TABLE tablenameold;
COMMIT TRAN;

Pozwoliłoby to zminimalizować czas przestoju kosztem tymczasowego zużycia dwukrotnie większej przestrzeni.

Mam również ten problem i użyłem tego SQL:

/*on créé la table COPY SANS les nouveaux champs et SANS les FKs */
CREATE TABLE IF NOT EXISTS prestations_copy LIKE prestations;

/* on supprime les FKs de la table actuelle */
ALTER TABLE `prestations`
DROP FOREIGN KEY `fk_prestations_pres_promos`,
DROP FOREIGN KEY `fk_prestations_activites`;

/* on remet les FKs sur la table copy */
ALTER TABLE prestations_copy 
    ADD CONSTRAINT `fk_prestations_activites` FOREIGN KEY (`act_id`) REFERENCES `activites` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    ADD CONSTRAINT `fk_prestations_pres_promos` FOREIGN KEY (`presp_id`) REFERENCES `pres_promos` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION;

/* On fait le transfert des données de la table actuelle vers la copy, ATTENTION: il faut le même nombre de colonnes */
INSERT INTO prestations_copy
SELECT * FROM prestations;

/* On modifie notre table copy de la façon que l'on souhaite */
ALTER TABLE `prestations_copy`
    ADD COLUMN `seo_mot_clef` VARCHAR(50) NULL;

/* on supprime la table actuelle et renome la copy avec le bon nom de table */
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE prestations;
RENAME TABLE prestations_copy TO prestations;
SET FOREIGN_KEY_CHECKS=1;   

Mam nadzieję, że to może komuś pomóc

Pozdrowienia,

Wola