13.7.2.4 OPTIMIZE TABLE Statement — MySQL 5.7

13.7.2.4 Оптимизация таблицы (OPTIMIZE TABLE)

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...

OPTIMIZE TABLE переупорядочивает физическое хранение данных таблицы и связанных индексов, чтобы уменьшить занимаемое место и улучшить производительность ввода-вывода при обращении к таблице. Точные изменения, вносимые в каждую таблицу, зависят от используемого для неё движка.

Используйте OPTIMIZE TABLE в следующих случаях, в зависимости от типа таблицы:

После выполнения значительного количества операций вставки, обновления или удаления данных в таблице InnoDB, у которой есть собственный файл данных, потому что она была создана с включенным параметром innodb_file_per_table. Таблица и индексы переупорядочиваются, и дисковое пространство может быть освобождено для использования операционной системой.
После выполнения значительного количества операций вставки, обновления или удаления данных в столбцах, являющихся частью полнотекстового индекса в таблице InnoDB. Сначала необходимо установить параметр конфигурации innodb_optimize_fulltext_only=1. Для поддержания разумного времени обслуживания индекса установите параметр innodb_ft_num_word_optimize для указания количества слов, которые необходимо обновить в поисковом индексе, и выполните ряд OPTIMIZE TABLE запросов до тех пор, пока поисковый индекс не будет полностью обновлен.
После удаления большой части таблицы MyISAM или InnoDB, или внесения множества изменений в таблицу с переменной длиной строк (таблицы, имеющие столбцы типа VARCHAR, VARBINARY, BLOB или TEXT). Удалённые строки хранятся в связанном списке, и последующие операции вставки повторно используют старые позиции строк. Вы можете использовать OPTIMIZE TABLE для освобождения неиспользуемого места и дефрагментации файла данных. После значительных изменений в таблице этот запрос также может улучшить производительность запросов, использующих эту таблицу, иногда значительно.

Для выполнения этого запроса требуются права SELECT и INSERT для таблицы.

OPTIMIZE TABLE работает с таблицами типа InnoDB, MyISAM и ARCHIVE. OPTIMIZE TABLE также поддерживается для динамических столбцов в памяти таблиц NDB. Он не работает со столбцами фиксированной ширины в таблицах памяти, а также с таблицами с данными на диске. Производительность запроса OPTIMIZE на таблицах NDB Cluster может быть натренирована с помощью --ndb-optimization-delay, который управляет длительностью ожидания между обработкой партий строк запросом OPTIMIZE TABLE. Дополнительную информацию см. в ...

Для таблиц NDB Cluster запрос OPTIMIZE TABLE может быть прерван (например, при завершении потока SQL, выполняющего операцию OPTIMIZE).

По умолчанию, запрос OPTIMIZE TABLE не работает для таблиц, созданных с использованием других движков хранения, и возвращает результат, указывающий на отсутствие поддержки. Вы можете заставить запрос OPTIMIZE TABLE работать для других движков хранения, запустив mysqld с параметром --skip-new. В этом случае OPTIMIZE TABLE просто сопоставляется с ALTER TABLE.

Этот запрос не работает с представлениями.

OPTIMIZE TABLE поддерживается для разнесенных по группам таблиц. Дополнительную информацию о применении этого запроса к разнесенным по группам таблицам и фрагментам таблиц см. в Разделе 22.3.4, «Поддержание фрагментов».

По умолчанию, сервер записывает запросы OPTIMIZE TABLE в двоичный журнал, чтобы они реплицировались на репликах. Чтобы запретить логирование, укажите необязательное ключевое слово NO_WRITE_TO_BINLOG или его псевдоним LOCAL.

Вывод запроса OPTIMIZE TABLE

OPTIMIZE TABLE возвращает набор результатов со столбцами, показанными в следующей таблице.

Столбец	Значение
`Table`	Имя таблицы
`Op`	Всегда `optimize`
`Msg_type`	`status`, `error`, `info`, `note` или `warning`
`Msg_text`	Информационное сообщение

Запрос OPTIMIZE TABLE перехватывает и обрабатывает любые ошибки, возникающие во время копирования статистики таблицы из старого файла в новый файл. Например, если идентификатор пользователя владельца файла .frm, .MYD или .MYI отличается от идентификатора пользователя процесса mysqld, запрос OPTIMIZE TABLE генерирует ошибку "невозможно изменить владельца файла", если mysqld не запущен пользователем root.

Детали InnoDB

Для таблиц InnoDB, OPTIMIZE TABLE отображается как ALTER TABLE ... FORCE, которая перестраивает таблицу для обновления статистики индексов и освобождения неиспользуемого пространства в кластерном индексе. Это отображается в выводе OPTIMIZE TABLE, когда вы запускаете её на таблице InnoDB, как показано здесь:

mysql> OPTIMIZE TABLE foo;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

OPTIMIZE TABLE использует онлайн-DDL для обычных и разбиениых таблиц InnoDB, что сокращает время простоя для одновременных операций DML. Перестройка таблицы, вызываемая OPTIMIZE TABLE, завершается на месте. Эксклюзивный замок на таблице берется только на короткое время во время фазы подготовки и фазы подтверждения операции. Во время фазы подготовки метаданные обновляются и создается промежуточная таблица. Во время фазы подтверждения изменения метаданных таблицы подтверждаются.

OPTIMIZE TABLE перестраивает таблицу с помощью метода копирования таблицы в следующих условиях:

Когда переменная системы old_alter_table включена.
Когда сервер запущен с опцией --skip-new.

OPTIMIZE TABLE с использованием онлайн-DDL не поддерживается для таблиц InnoDB, которые содержат индексы FULLTEXT. Вместо этого используется метод копирования таблицы.

InnoDB хранит данные с использованием метода выделения страниц и не страдает фрагментацией так же, как устаревшие движки хранения (такие как MyISAM). При рассмотрении необходимости запуска OPTIMIZE TABLE, учтите рабочую нагрузку транзакций, которые ожидается обработать вашему серверу:

Определённый уровень фрагментации ожидается. InnoDB заполняет только на 93%, чтобы оставить место для обновлений без необходимости разделения страниц.
Операции удаления могут оставлять пробелы, делающие страницы менее заполненными, чем желательно, что может сделать целесообразным оптимизацию таблицы.
Обновления строк обычно перезаписывают данные в той же странице, в зависимости от типа данных и формата строк, когда достаточно места. См. Раздел 14.9.1.5, «Как работает сжатие для таблиц InnoDB» и Раздел 14.11, «Форматы строк InnoDB».
Рабочие нагрузки с высокой конкуренцией могут со временем оставлять пробелы в индексах, так как InnoDB сохраняет несколько версий одних и тех же данных из-за своего механизма. См. Раздел 14.3, «Многоверсионность InnoDB».

Детали MyISAM

Для таблиц MyISAM, OPTIMIZE TABLE работает следующим образом:

Если в таблице есть удалённые или разделённые строки, исправьте таблицу.
Если страницы индекса не отсортированы, отсортируйте их.
Если статистика таблицы не обновлена (и исправление не может быть выполнено путём сортировки индекса), обновите её.

Другие Соображения

OPTIMIZE TABLE выполняется онлайн для обычных и разбиениых таблиц InnoDB. В противном случае MySQL работает в течение времени, пока OPTIMIZE TABLE выполняется.

OPTIMIZE TABLE не сортирует индексы R-дерева, такие как пространственные индексы на столбцах POINT. (Ошибка #23578)