MySQL: InnoDB и дефрагментация табличных пространств

Хочу сделать несколько заметок по MySQL, чтобы сохранить для себя информацию, а заодно ей поделиться со всеми. Информации в интернете по этим темам, конечно, полно, пусть будет ещё.

Я надеюсь, что все кто использует MySQL как реляционную СУБД для больших и нагруженных баз данных использует движок InnoDB. MyISAM я бы не стал для этого использовать, всё-таки скорость обновлений (из-за блокировки таблицы вместо строки) и отсутствие транзакций не подразумевает использование этого движка для чего-то кроме мелких проектов. Единственный плюс, из-за которого для системы электронного документооборота «Визирь» я использовал базу данных на движке MyISAM — это полнотекстовый поиск, в InnoDB он штатно не был предусмотрен.

Для перевода таблиц на движок InnoDB можно выполнить команду (но перед этим лучше прочитать статью целиком):

ALTER TABLE tablename​ ENGINE = InnoDB;

Движок InnoDB в MySQL имеет две реализации хранения данных — в одном файле (ibdata1) и каждая таблица в отдельном файле (табличном пространстве). До версии MySQL 5.6.6 по умолчанию СУБД было настроено на хранение в одном файле (в одном табличном пространстве) всех баз данных со всеми таблицами. На мой взгляд, по опыту данного использования — решение так себе. Мало того, что это пространство имеет тенденцию к «распуханию», которое так просто не решить (иногда вплоть до полного его удаления и восстановления данных из дампа), так еще и шансы выше получить проблемы в случае файловых сбоев. Бэкап, конечно, наше всё, но восстанавливать на удаленном сервере упавшую БД с многогигабайтным файлом дампа — это отдельный вид удовольствия, которого хотелось бы по возможности избегать. Хорошо, что есть такой параметр у СУБД MySQL как innodb_file_per_table и его можно выставить в 1 и получить хранение данных в разных файлах, с разделением их по каталогам БД. С версии MySQL 5.6.6 данный параметр по умолчанию так и настроен, что хорошо.

Но проблема с «распуханием» (увеличением места занимаемым данными) в отдельных файлах табличных пространств (с расширением .ibd) никуда не делось. Это так называемый процесс фрагментации и чем больше в БД идет добавлений, обновлений и удалений (любимая всеми тройка операторов реляционных СУБД: INSERT, UPDATE, DELETE) тем быстрее табличное пространство с одной стороны отнимает у нас драгоценное место, а с другой — происходит еще и падение скорости обращений к данным из-за этой фрагментации. Например, у меня БД занимающая 10 гигабайт дискового памяти легко распухает до 12-13 гигабайт за неделю работы, при этом и скорость падает на десяток-другой процентов. Понятно, что с этим надо бороться.

На помощь к нам приходит прекрасная команда:

 OPTIMIZE TABLE tablename;

Данная команда на самом деле выполняет пересоздание таблицы: на диске появляется новый временный файл размером с подвергающегося оптимизации табличного пространства и в него перекачиваются данные. Потом данный файл заменяет старый и мы, как правило, имеем «похудевшее» табличное пространство, чего мы и добивались. Понятно, что на диске должно быть нужное количество свободного пространства, иначе получим ошибку, и хорошо еще, если СУБД сама сможет почистить временные файлы, а не приляжет из-за внезапно окончившегося свободного места — у MySQL такое бывает.

Поскольку процесс фрагментации идет постоянно по мере обновления данных в БД, то и дефрагментировать тоже приходится постоянно. Поэтому в Task Sheduler в Windows я поставил запуск bat-файла с командой (при необходимости добавить пути к файлам):

@mysql.exe --host=localhost --port=3306 --user=%1 --password=%2 --database=%3 < table_optimize.sql > table_optimize.log

где %1 — имя пользователя, %2 — пароль, %3 — имя БД.