Dodawanie indeksu do dużych tabel mysql

Mam stolik

| base_schedule_line_items | UTWÓRZ TABELĘ base_schedule_line_items(
idint (10) unsigned NOT NULL AUTO_INCREMENT,
installmentint (10) unsigned NOT NULL,
on_datedata NOT NULL,
actual_datedata DEFAULT NULL,
payment_typeint (11) NOT NULL,
scheduled_principal_outstandingdziesiętny (65,0) NOT NULL,
scheduled_principal_duedziesiętny (65,0) NOT NULL,
scheduled_interest_outstandingdziesiętny (65,0) NOT NULL,
scheduled_interest_duedziesiętny (65,0) NOT NULL,
currencyint (11) NOT NULL,
updated_atdata / godzina NOT NULL DEFAULT '2013-01-06 14:29:16', data / godzina
created_atNOT NULL DEFAULT ' 2013-01-06 14:29:16 ',
loan_base_schedule_idint (10) unsigned NOT NULL,
lending_idint (10) unsigned NOT NULL,
rescheduletinyint (1) DEFAULT' 0 ',
PRIMARY KEY ( id),
KEY index_base_schedule_line_items_loan_base_schedule ( loan_base_schedule_id), KEY index_bslt_spd( scheduled_principal_due),
KLUCZ index_bslt_lending(lending_id)
KLUCZ index_bslt_actualdate( actual_date)
KLUCZ index_bslt_spsila ( loan_base_schedule_id, scheduled_principal_due, scheduled_interest_due, actual_date)
KLUCZ index_bslt_ondate( on_date)
KLUCZ index_bslt_oa( on_date, actual_date)
KLUCZ index_bslt_ol( on_date, loan_base_schedule_id)
KLUCZ index_bslt_oli( on_date, lending_id)
) Silnik = InnoDB AUTO_INCREMENT = 30410126 Domyślnym zestawem = utf8 |

Teraz ta tabela zawiera 30 milionów rekordów, muszę dodać do tego jeszcze dwa indeksy i dodawanie go zajmuje lata.

zmień tabelę base_schedule_line_items dodaj indeks index_bslt_sla (zaplanowana_principal_due, faktyczna_data, lending_id);
zmień tabelę base_schedule_line_items dodaj indeks index_bslt_ssla (zaplanowana_principal_due, zaplanowana_interes_due, lending_id, faktyczna_data);

Użyłem poniższego zapytania, aby znaleźć rozmiar tabeli

WYBIERZ nazwę_tabeli AS „Tabele”, okrągłe (((długość_danych + długość_indeksu) / 1024/1024), 2) „Rozmiar w MB” OD schematu_informacyjnego.TABLES GDZIE tabela_schema = „moja_nazwa_bazy_danych”;

Wynik wyszedł jako

base_schedule_line_items | 20111,00

Użyłem tego tylko do obliczenia długości danych i pominąłem długość indeksu

WYBIERZ nazwę_tabeli AS „Tabele”, okrągłe (((długość_danych) / 1024/1024), 2) „Rozmiar w MB” OD schematu_informacyjnego.TABLES GDZIE schemat_tabeli = „moja_nazwa_bazy_danych”;

i wynik był

base_schedule_line_items | 9497,00

Indeksy

KLUCZ index_bslt_actualdate (actual_date)
index_bslt_spsila KLUCZ (loan_base_schedule_id, scheduled_principal_due, scheduled_interest_due, actual_date)
KLUCZ index_bslt_ondate (on_date)
KLUCZ index_bslt_oa (on_date, actual_date)
KLUCZ index_bslt_ol (on_date, loan_base_schedule_id)
KLUCZ index_bslt_oli (on_date, lending_id)

zostały dodane przeze mnie, ale nie jestem pewien, do jakiego celu zostały dodane inne wskaźniki. To dość duża aplikacja. Teraz muszę dodać te dwa wyżej wymienione wskaźniki, ponieważ pomagają mi one wyodrębnić raport przy użyciu instrukcji select i bardzo trudno jest je dodać. Każda pomoc byłaby bardzo mile widziana

Zmiana dużych tabel odbywa się etapami:

1. Utwórz nową tabelę z wymaganymi polami i indeksami w testowej bazie danych (tylko struktura)
2. Zrzuć dane z istniejącej tabeli i załaduj to samo do nowo utworzonej tabeli w testowej bazie danych
3. Teraz ogłosić swój przestój :)
4. Zamień tabele, zmieniając nazwę - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name;Jest to operacja atomowa trwająca ułamek sekundy.
5. Załaduj dodatkowe rekordy do nowo utworzonej tabeli (rekordy, które pojawiły się po zrzuceniu, a następnie załadowaniu). Ten krok można wykonać przed krokiem 3 także raz, aby skrócić czas przestoju.
6. Twój system powrócił

Kilka notatek:

1. Nie musisz uderzać bezpośrednio w schemat informacyjny, spróbuj użyć SHOW TABLE STATUS from db like 'table_name'
2. Prędkość tabeli zmian jest mniej więcej związana z prędkością we / wy. Ostatnim razem, gdy uruchomiliśmy tabelę bezpośrednich zmian (bez powyższych kroków), mieliśmy rozmiar stołu ponad 40 GB i zajęło to około 4 godzin. Jeśli twoje 20 GB danych zajmuje lata, pracujesz na jakimś nieaktualnym komputerze.
3. Również upuszczenie niechcianych indeksów, jak index_bslt_ondate, index_base_schedule_line_items_loan_base_schedulew przypadku niektórych innych indeksów, ma kolumnę najbardziej na lewo jako kolumnę indeksowaną

Daj mi znać, jeśli potrzebujesz wyjaśnień na temat któregokolwiek z tych kroków.

Edycja: prosty skrypt Pythona do automatyzacji procesu https://github.com/georgecj11/hotswap_mysql_table.git

Możesz użyć narzędzia, takiego jak pt-online-schema-change, aby dodać indeks bez blokowania aplikacji. Podstawowa logika jest następująca:

• utwórz pustą kopię tabeli z nowym indeksem
• dodaj wyzwalacze do istniejącej tabeli, aby wszelkie zmiany, które uderzą w tabelę, zostaną zastosowane do kopii tabeli *
• zacznij kopiować wiersze ze starej tabeli do nowej
• po zakończeniu kopiowania zamień tabele
• upuść stary stół

* Narzędzie nie działa, jeśli w tabeli istnieją wyzwalacze

Sposób zamiany i upuszczenia można dostosować w zależności od opcji przekazanych do narzędzia.

Występują również problemy z kluczami obcymi, dlatego należy przeczytać dokumentację narzędzia, aby w pełni zrozumieć jego działanie.

I oczywiście dobrze jest upewnić się, że masz kopię zapasową tabeli, aby móc ją przywrócić w razie potrzeby.