Очерки сисадмина MySQL и базы данных
innodb_file_per_table: хранение таблиц InnoDB в отдельных файлах
Параметр innodb_file_per_table размещает каждую таблицу InnoDB в собственном файле .ibd. Разбираем, как проверить его состояние, перенести старые таблицы и что делать с разросшимся ibdata1.
В старых версиях MySQL все таблицы InnoDB складывались в одно общее табличное пространство — файл ibdata1. По мере роста данных он раздувался, а вернуть место операционной системе было нельзя. Параметр innodb_file_per_table решает часть проблемы: каждая таблица получает собственный файл имя_таблицы.ibd в каталоге своей базы данных.
Что включено сегодня
Начиная с MySQL 5.6 параметр включён по умолчанию, в MariaDB — тоже. На современных серверах (MySQL 8.x, MariaDB 10.x и 11.x) отдельные файлы создаются без каких-либо настроек. Проверить состояние можно запросом:
SHOW VARIABLES LIKE 'innodb_file_per_table';
Если значение ON, новые таблицы лягут в отдельные файлы. Для старых установок или сознательно изменённых конфигураций параметр задают в секции [mysqld]:
[mysqld]
innodb_file_per_table=1
Параметр динамический, поэтому его можно включить без перезапуска сервера:
SET GLOBAL innodb_file_per_table=1;
Изменение действует на таблицы, созданные или перестроенные после этого момента. Чтобы оно пережило перезапуск, строку всё равно нужно добавить в конфигурационный файл.
Перенос существующих таблиц
Таблицы, созданные до включения параметра, остаются в ibdata1. Чтобы переместить такую таблицу в собственный файл с сохранением данных, достаточно пересоздать её:
ALTER TABLE `table_name` ENGINE=InnoDB;
Для InnoDB эквивалентный результат даёт OPTIMIZE TABLE: сервер выполняет перестроение таблицы и обновляет статистику.
OPTIMIZE TABLE `table_name`;
Во время перестроения таблица копируется целиком, поэтому на диске должно хватать места под её копию, а на больших таблицах операция занимает заметное время. Планируйте её в окно с низкой нагрузкой.
Найти таблицы, которые всё ещё лежат в системном пространстве, можно через information_schema. В MySQL 8.x:
SELECT NAME FROM information_schema.INNODB_TABLES WHERE SPACE = 0;
В MySQL 5.7 аналогичные данные находятся в INNODB_SYS_TABLES. Оценить размер таблиц по базам помогает запрос:
SELECT table_schema,
ROUND(SUM(data_length + index_length) / 1024 / 1024) AS size_mb
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY size_mb DESC;
Почему ibdata1 не уменьшается
После переноса всех таблиц в отдельные файлы ibdata1 не сжимается. Это поведение InnoDB: файл общего пространства только растёт, а освобождённые страницы используются повторно внутри него. Простого аналога VACUUM FULL из PostgreSQL для системного табличного пространства нет, а удалять файл при работающем сервере нельзя.
В нём остаются служебные структуры: словарь данных (до MySQL 8.0), буфер изменений, а в зависимости от версии — журналы отката и буфер двойной записи. Поэтому даже при полном переходе на file_per_table размер ibdata1 бывает значительным, если когда-то он успел вырасти.
Единственный надёжный способ вернуть место — пересоздать каталог данных:
- Сделать логический дамп всех баз:
mysqldump --all-databases --routines --events --triggers > full.sql. - Остановить сервер и убедиться, что дамп цел.
- Сохранить копию каталога данных и удалить
ibdata1, файлыib_logfile*(или каталог redo-журнала в новых версиях) и каталоги баз. - Убедиться, что
innodb_file_per_table=1в конфигурации, и заново инициализировать каталог (mysqld --initializeилиmariadb-install-db). - Запустить сервер и загрузить дамп:
mysql < full.sql.
Эту процедуру нужно отрабатывать на копии сервера: ошибка на шаге 3 при отсутствии резервной копии стоит базы. Если данные критичны, дополнительно проверьте восстановление на тестовой машине.
Общие табличные пространства
Модель «один файл на таблицу» не единственная. Начиная с MySQL 5.7.6 есть общие табличные пространства (general tablespaces): несколько таблиц можно разместить в одном файле, который вы создаёте сами.
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENGINE=InnoDB;
CREATE TABLE t1 (id INT PRIMARY KEY) TABLESPACE ts1;
Такой подход полезен, когда в базе тысячи мелких таблиц и открытых файлов становится слишком много, либо когда нужно вынести часть данных на отдельный диск. Важно, что общее пространство тоже не возвращает место системе автоматически: освобождается оно только при удалении всего пространства.
Что даёт отдельный файл
| Возможность | Системное пространство | Отдельный .ibd-файл |
|---|---|---|
Освобождение места при OPTIMIZE TABLE |
нет | да, файл пересоздаётся |
| Перенос таблицы на другой диск | нет | возможен |
| Резервное копирование одной таблицы | сложно | проще |
| Число открытых файлов | минимальное | растёт с числом таблиц |
Для большинства серверов выгоднее отдельные файлы: проще управлять местом на диске и анализировать, какая таблица сколько занимает. Подробнее о приёмах работы с базами в панели хостинга — в материале про базы данных MySQL в cPanel и phpMyAdmin, а структуру таблицы перед переносом удобно сохранить способами из заметки про SHOW CREATE TABLE. Параметры описаны в документации MySQL.
Частые вопросы
Включён ли innodb_file_per_table по умолчанию?
Да. Начиная с MySQL 5.6 и в современных версиях MariaDB параметр включён по умолчанию, поэтому новые таблицы InnoDB создаются в отдельных файлах tbl_name.ibd.
Почему файл ibdata1 не уменьшается после удаления данных?
Системное табличное пространство InnoDB только растёт: освобождённые страницы остаются внутри файла и используются повторно, но файл на диске не сжимается. Уменьшить его можно только полным дампом и пересозданием каталога данных.
Как перенести существующую таблицу в отдельный файл?
Выполните ALTER TABLE `имя` ENGINE=InnoDB или OPTIMIZE TABLE `имя` при включённом innodb_file_per_table. Таблица будет перестроена и окажется в собственном .ibd-файле.
Можно ли просто удалить ibdata1?
Нет, без остановки сервера и полного пересоздания каталога данных это приведёт к потере базы. Файл содержит служебные структуры InnoDB, поэтому удалять его можно только в рамках процедуры «дамп и восстановление».