Чому після DELETE розмір файлу таблиці InnoDB не зменшується?

Question

Accepted Answer

За замовчуванням (innodb_file_per_table = ON) кожна таблиця InnoDB живе у власному файлі .ibd. Коли рядки видаляють, InnoDB позначає місце вільним усередині файлу й використовує його для нових рядків цієї ж таблиці. Операційній системі місце не повертається.
-- таблиця логів займає 40 ГБ
DELETE FROM activity_log WHERE created_at < NOW() - INTERVAL 90 DAY;
-- файл activity_log.ibd усе ще 40 ГБ, але всередині багато вільних сторінок

Як оцінити вільне місце:
SELECT table_name,
       ROUND(data_length / 1024 / 1024) AS data_mb,
       ROUND(index_length / 1024 / 1024) AS index_mb,
       ROUND(data_free / 1024 / 1024) AS free_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_free DESC;

Цифри приблизні (оцінка за статистикою), але порядок величин видно.
Як повернути місце ОС:

OPTIMIZE TABLE - для InnoDB це перебудова таблиці (ALTER TABLE ... FORCE): створюється нова компактна копія, стара видаляється. Виконується онлайн (паралельні зміни дозволені), але потребує вільного диска на розмір таблиці й створює навантаження;
TRUNCATE TABLE - якщо треба очистити все: файл перестворюється порожнім, миттєво;
секціонування за датою й ALTER TABLE ... DROP PARTITION - найкращий варіант для логів: стара частина видаляється разом із файлом, без DELETE мільйонів рядків.

Чи потрібно повертати місце взагалі? Часто ні. Якщо таблиця знову виросте до того ж розміру, вільне місце просто використається. Перебудова має сенс, коли таблиця назавжди зменшилася в рази або диск закінчується.
Системний табличний простір ibdata1 не зменшується ніколи. Якщо таблиці колись створили з innodb_file_per_table = OFF, їхні дані лежать там, і звільнити місце можна лише перенесенням даних у новий інстанс. Тому вимикати file-per-table немає причин.
Масове видалення варто робити частинами (DELETE ... LIMIT 10000 у циклі): один гігантський DELETE - це довга транзакція, величезний undo-журнал і затримка реплік.
Докладніше в документації: Табличні простори file-per-table

Чому після DELETE розмір файлу таблиці InnoDB не зменшується?

Схожі питання