InnoDB przechowuje wszystkie tabele w jednym dużym pliku ibdata1.

Po upuszczeniu dużego stołu plik zachowuje swój rozmiar bez względu na to, jak duży był stół. Jak mogę zmniejszyć ten plik bez konieczności zrzucania i ponownego importowania całej bazy danych (która ma łącznie kilkaset GB)?

Myślę, że powodem jest to, że nadal możesz cofnąć upuszczanie. W moim przypadku nie muszę.

Jest to jeden z najbardziej kontrowersyjnych tematów, z którymi miałem do czynienia przez lata jako MySQL DBA oraz w DBA StackExchange.

Mówiąc łagodnie, po prostu nie ma innego sposobu na zmniejszenie ibdata1 . Przy wyłączonej innodb_file_per_table , za każdym razem, gdy uruchomisz OPTIMIZE TABLEtabelę InnoDB, ibdata1 szybko rośnie. Dane, które są upuszczane przy użyciu DROP TABLEi DROP DATABASEnie można ich przywrócić, ponieważ są to DDL, a nie DML. Wierzę, że Oracle i MSSQL mogą wycofać DDL. MySQL nie może tego zrobić.

Istnieje kilka klas informacji, które znajdują się w ibdata1

• Dane tabeli
• Indeksy tabel
• Tabela Metadane
• Dane kontrolne MVCC
• Podwójny bufor zapisu (zapis w tle, aby uniknąć polegania na buforowaniu systemu operacyjnego)
• Wstaw bufor (zarządzanie zmianami w nieunikalnych indeksach wtórnych)

Użycie innodb_file_per_table=1pozwoli ci stworzyć nowe tabele z danymi tabeli i indeksami tabel tworzonymi poza ibdata1. Możesz wyodrębnić dowolne tabele nadal wewnątrz ibdata1 przy użyciu ALTER TABLE ... ENGINE=InnoDB;lub, OPTIMIZE TABLEale pozostawi to duże puste miejsce w ibdata1.

Niezależnie od tego musisz wyczyścić infrastrukturę InnoDB. Napisałem już posty StackExchange o tym, jak i dlaczego to zrobić:

• May 21, 2012: Jak duża będzie baza danych MySQL w stosunku do pliku zrzutu?
• Apr 01, 2012: Czy wskazany jest plik innodb_file_per_table?
• Mar 25, 2012: Dlaczego InnoDB przechowuje wszystkie bazy danych w jednym pliku?
• Feb 04, 2011: MySQL InnoDB - wady innodb_file_per_table?
• Oct 29, 2010: Howto: Wyczyść mysql silnik pamięci InnoDB?

Dobre wieści

Wystarczy zrzucić dane, załadować jeszcze raz i nigdy więcej nie powracać do tego problemu . Uruchamianie OPTIMIZE TABLEpotem rzeczywiście będzie kurczyć .ibdpliku tabel dla każdej tabeli InnoDB.

InnoDB przechowuje wszystkie tabele InnoDB w ibdata1 tylko wtedy, gdy nie używasz następującego ustawienia w pliku domyślnym my.cnf:

innodb_file_per_table = 1

TABELI DROP (i DROP DATABASE) nie można przywrócić.

To nie jest powód, dla którego nie można zmniejszyć ibdata1.

Jest to skrócone wyjaśnienie, ale ibdata1 zawiera dane wewnętrzne InnoDB oprócz danych tabeli. Z mojego zrozumienia, jego zmniejszenie wymagałoby defragmentacji, co nie jest obsługiwaną operacją.