Бывают ситуации, когда файлы базы данных занимают практически все место на сервере и при попытке удалить не нужные данные из таблицы, место не освобождается. Это обусловлено тем, что логически размер БД может как уменьшаться так и увеличиваться, при этом физический файл данных на диске только растет.

Объекты базы данных, такие как таблицы и индексы, хранятся в табличных пространствах, так называемых логических структурах, а физически это файл в ОС на диске.

Дефрагментацией базы данных MySQL называют процесс, который позволяет уменьшить размер физического файла на диске, за счет освобождения не используемого логического пространства.

Есть несколько вариантов дефрагментации баз данных в том числе и таблиц INNODB в MySQL:

Первый вариант так называемых бэкап-восстановление:

  • выполняется резервное копирование базы данных с помощью mysqldump
  • удаление БД, drop database
  • создание новой БД с таким же именем
  • восстановление из бэкапа данные

Данный вариант не плох, когда у вас не большая база, что же делать если размер БД, огромный? Использовать второй метод.

Выполнить дефрагментацию базы данных MySQL.

По умолчанию MyIsam применима процедура дефрагментации, а вот для InnoDB должна быть включена опция  innodb_file_per_table, после чего мы с легкостью можем использовать данную команду

Данная команда позволяет реорганизовать не только логическое, но и физичекое место на диске. Кроме уменьшения размера, занимаемого файла на диске, данная операция позволяет улучшить скорость ввода/вывода, что позволяет также оптимизировать работу базы данных MySQL.

OPTIMIZE TABLE, рекомендовано использовать при удалении, обновлении и добавлении большого количества записей в таблицы баз.

Во время выполнения процедуры оптимизации, таблица будет заблокирована.

Третий вариант использовать команду mysqlcheck, можно использовать с такими параметрами

Данная команда позволит дефрагментировать все таблицы в БД. Например, выполнив данную команду после крупного удаления данных из таблицы, получили следующие результаты:

Размер одной таблицы в базе ДО — 212,6 Гб. ПОСЛЕ — 113,8 Гб.

Нужна помощь в поддержке баз данных и оптимизации производительности сервера, обращайтесь [email protected]