Дефрагментация таблиц InnoDB

Как я уже упоминал в данной статье, при использовании движка базы данных InnoDB для освобождения места на жестком диске недостаточно просто удалить записи из таблиц — нужно обязательно выполнить дефрагментацию таблиц InnoDB.

Логически табличное пространство в таблицах InnoDB может расширяться и уменьшаться, но физически оно изменяется только в сторону увеличения. Что делать? Давайте разберемся!

Примечание. Табличное пространство — логическое пространство, которое используется для хранения объектов базы данных, таких как индексы, таблицы и т. п. Физически табличные пространства обычно хранятся в виде файлов на жестком диске

Чаще всего для дефрагментации таблиц InnoDB предлагают сделать следующие шаги:

• сделать резервную копию БД с помощью утилиты mysqldump;
• удалить существующую БД;
• создать пустую БД с таким же именем;
• восстановить таблицы в пустую БД из резервной копии.

Если база данных маленькая, то никаких проблем с предложенным вариантом возникнуть не должно. Но что делать, если в вашем проекте база данных занимает сотни гигабайт? И как, например, сделать дефрагментацию одной таблицы InnoDB?

Мы уже знаем как включить опцию innodb_file_per_table, следовательно можем использовать OPTIMIZE TABLE.

Примечание. OPTIMIZE TABLE tbl_name реорганизует физическое пространство данных таблицы и связанных индексов. Используется для уменьшения объема физического пространства и повышения эффективности операций ввода/вывода при доступе к таблице.

Разработчики советуют использовать OPTIMIZE TABLE tbl_name после добавления, удаления и обновления большого количества записей в таблице. Следует помнить, что OPTIMIZE TABLE tbl_name работает только с MyISAM, InnoDB и ARCHIVE таблицами.

Для движка базы данных InnoDB операция OPTIMIZE TABLE tbl_name — то же самое, что и ALTER TABLE tbl_name ENGINE=InnoDB. Следовательно, выполнение OPTIMIZE TABLE tbl_name будет блокировать таблицу.

В своих проектах для дефрагментации таблиц InnoDB я использую следующий скрипт:

#!/usr/bin/php
<?php
$host='<имя_хоста>';
$database='<имя_базы_данных>';
$user='<имя_пользователя_бд>';
$pwd='<очень_сложный_пароль>';
$tables=array(
    "<имя_таблицы_1>",
    "имя_таблицы_2",
    "имя_таблицы_3");
 
$c_mysql = mysql_connect($host, $user, $pwd);
if(!$c_mysql)
{
    echo('Cannot connect to mysql server');
}
 
$db = mysql_select_db($database, $c_mysql);
if (!$db)
{
    echo('Cannot connect to database');
}
 
$rec = mysql_query('SHOW TABLE STATUS');
while ($data = mysql_fetch_object($rec))
    if ($data->Engine == 'InnoDB')
  if (in_array($data->Name, $tables))
  {
      echo 'Processing ', $data->Name, ' ... '; 
      flush();
      mysql_query("ALTER TABLE {$data->Name} ENGINE=InnoDB");
      echo "done.\n";
  }
mysql_free_result($rec);
mysql_close();