MySQL InnoDB не освобождает дисковое пространство после удаления строк данных из таблицы

MySQL не уменьшает размер ibdata1. Всегда. Даже если вы используете optimize table, чтобы освободить пространство, используемое для удаленных записей, он будет повторно использовать его позже.

Альтернативой является настройка сервера для использования innodb_file_per_table, но для этого потребуется резервное копирование, удаление базы данных и восстановление. Положительным моментом является то, что файл .ibd для таблицы уменьшается после optimize table.

У меня была такая же проблема.

Что происходит, так это то, что даже если вы отбрасываете базу данных, innodb все равно не освобождает дисковое пространство. Мне пришлось экспортировать, остановить mysql, вручную удалить файлы, запустить mysql, создать базу данных и пользователей, а затем импортировать. Слава богу, у меня было только 200 МБ строк, но это сэкономило 250 ГБ файла innodb.

Неудачный проект.

Если вы не используете innodb_file_per_table, можно освободить дисковое пространство, но довольно утомительный и требует значительного времени простоя.

Как сделать довольно подробно, но я вставил соответствующая часть ниже.

Не забудьте также сохранить копию своей схемы в дампе.

В настоящее время вы не можете удалить файл данных из системного табличного пространства. Чтобы уменьшить размер системного табличного пространства, используйте эту процедуру:

Используйте mysqldump для сброса всех ваших таблиц InnoDB.

Остановите сервер.

Удалите все существующие файлы табличных пространств, включая файлы ibdata и ib_log. Если вы хотите сохранить резервную копию информации, скопируйте все файлы ib * в другое место перед удалением файлов в вашей установке MySQL.

Удалите все файлы .frm для таблиц InnoDB.

Настройте новое табличное пространство.

Перезагрузите сервер.

Импортируйте файлы дампа.

Десять лет спустя и у меня была такая же проблема. Я решил это следующим образом:

• Оптимизировал, все базы остались.
• Я перезапустил свой компьютер и MySQL в службах (Windows + r -> services.msc)

Это все :)

Разобрался с этой проблемой сегодня (через 11 лет после того, как вопрос был первоначально задан), и смог исправить ее, отбросив таблицу и создав ее снова. Мне не нужно было переустанавливать БД или дамп и восстановление, изменять хранилище, изменять таблицы и т. Д. - ничего из этого.

Я использую InnoDB, но не innodb_file_per_table, поэтому даже после того, как я удалил 900K строк из таблицы, размер БД не сдвинулся с места. Поэтому я отбросил таблицу и создал ее снова.

В моем случае моя таблица была очищена до нуля строк, поэтому мне было легко отбросить таблицу, но сохранить структуру, которую я запустил

create table mynewtable as select * from myoldtable where 1=2;

С последующим

drop table myoldtable;

Это уменьшило размер моей БД с 5G до 400MB.

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

Когда вы введете разделение для своей таблицы, потребуются некоторые изменения в схеме таблицы, например: - Уникальные ключи, индексы для включения столбца раздела и т. Д.

Год назад я также столкнулся с той же проблемой на версии mysql5.7, и ibdata1 занимал 150 ГБ. поэтому я добавил табличные пространства отмены

Сделайте резервную копию Mysqldump
Остановите службу mysql.
Удалите все данные из каталога данных.
Добавьте ниже параметр отмены табличного пространства в текущем my.cnf.

 #undo tablespace
  innodb_undo_directory =  /var/lib/mysql/
  innodb_rollback_segments = 128 
  innodb_undo_tablespaces = 3
  innodb_undo_logs = 128  
  innodb_max_undo_log_size=1G
  innodb_undo_log_truncate = ON

Запустить службу mysql и сохранить резервную копию mysqldump

Проблема решена !!

Если ОПТИМИЗАЦИЯ не решает вашу проблему, попробуйте:

ALTER TABLE tbl_name ENGINE=INNODB, ALGORITHM=INPLACE, LOCK=NONE;

or

ALTER TABLE tbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;

Есть несколько способов освободить дисковое пространство после удаления данных из таблицы для MySQL Inodb Engine.

Если вы не используете innodb_file_per_table с самого начала, сброс всех данных, удаление всего файла, воссоздание базы данных и повторный импорт данных - единственный способ (проверьте ответы FlipMcF выше)

Если вы используете innodb_file_per_table, вы можете попробовать

1. Если вы можете удалить все данные, команда truncate удалит данные и освободит дисковое пространство за вас.
2. Команда Alter table удалит и воссоздает таблицу, чтобы освободить место на диске. Поэтому после удаления данных запустите команду alter table, которая ничего не меняет, чтобы освободить жесткий диск (например: таблица TBL_A имеет кодировку uf8, после удаления данных запустите ALTER TABLE TBL_A charset utf8 -> эта команда ничего не меняет в вашей таблице, но она заставляет mysql воссоздать вашу таблицу и восстановить дисковое пространство
3. Создайте TBL_B как TBL_A. Вставьте выбранные данные, которые вы хотите сохранить, из TBL_A в TBL_B. Отбросьте TBL_A и переименуйте TBL_B в TBL_A. Этот способ очень эффективен, если TBL_A и данные, которые необходимо удалить, большие (команда удаления в MySQL innodb очень плохая производительность)