Случайные вставки или удаления из вторичного индекса могут привести к фрагментации индекса. Фрагментация означает, что физический порядок страниц индекса на диске не соответствует порядку записей в индексе на страницах, или что в 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. Дополнительную информацию см. в разделе 14.13 «InnoDB и онлайн DDL».

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

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