Случайные вставки или удаления из вторичного индекса могут привести к фрагментации индекса. Фрагментация означает, что физический порядок страниц индекса на диске не соответствует порядку записей в индексе на страницах или что в 64-страничных блоках, выделенных для индекса, много неиспользуемых страниц.

Одним из симптомов фрагментации является то, что таблица занимает больше места, чем “должна”. Точное значение трудно определить. Все InnoDB данные и индексы хранятся в, и их занимаемое пространство может варьироваться от 50% до 100%. Другим симптомом фрагментации является то, что сканирование таблицы, например, занимает больше времени, чем “должно”:

SELECT COUNT(*) FROM t WHERE non_indexed_column <> 12345;

Предыдущий запрос требует от MySQL выполнения полного сканирования таблицы, самого медленного типа запроса для большой таблицы.

Для ускорения сканирования индексов можно периодически выполнять операцию “null” ALTER TABLE, которая заставляет MySQL перестроить таблицу:

ALTER TABLE tbl_name ENGINE=INNODB

Также можно использовать ALTER TABLE tbl_name FORCE для выполнения операции “null” изменения, которая перестраивает таблицу.

И ALTER TABLE tbl_name ENGINE=INNODB, и ALTER TABLE tbl_name FORCE используют онлайн DDL. Для получения дополнительной информации см. Раздел 17.12, «InnoDB и онлайн DDL».

Другой способ выполнения операции дефрагментации — использование mysqldump для экспорта таблицы в текстовый файл, удаления таблицы и загрузки её из файла дампа.

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