15.7.3.4 OPTIMIZE TABLE Statement — MySQL 8.4

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, разрешенные в кластере NDB 8.4».

Для таблиц 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)