iT-Ti.ruочерки сисадмина

Очерки сисадмина MySQL и базы данных

innodb_file_per_table: хранение таблиц InnoDB в отдельных файлах

Опубликовано: Обновлено: 4 мин чтенияАвтор: Редакция iT-Ti.ru

Параметр 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 бывает значительным, если когда-то он успел вырасти.

Единственный надёжный способ вернуть место — пересоздать каталог данных:

  1. Сделать логический дамп всех баз: mysqldump --all-databases --routines --events --triggers > full.sql.
  2. Остановить сервер и убедиться, что дамп цел.
  3. Сохранить копию каталога данных и удалить ibdata1, файлы ib_logfile* (или каталог redo-журнала в новых версиях) и каталоги баз.
  4. Убедиться, что innodb_file_per_table=1 в конфигурации, и заново инициализировать каталог (mysqld --initialize или mariadb-install-db).
  5. Запустить сервер и загрузить дамп: 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, поэтому удалять его можно только в рамках процедуры «дамп и восстановление».