15.7.3.4 OPTIMIZE TABLE Statement — MySQL 9.2

15.7.3.4 Выполнение команды OPTIMIZE TABLE

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

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

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

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

Для этой команды требуется SELECT и INSERT права для таблицы.

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

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

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

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

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

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

Вывод команды OPTIMIZE TABLE

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

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

Команда OPTIMIZE TABLE обрабатывает и перехватывает любые ошибки, возникающие при копировании статистики таблицы из старого файла в новый файл. Например, если идентификатор пользователя владельца файла .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). При принятии решения о том, стоит ли запускать оптимизацию, рассмотрите рабочую нагрузку транзакций, которые должен обрабатывать ваш сервер:

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

Подробности MyISAM

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

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

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

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

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