Создание или добавление вторичного индекса

CREATE INDEX name ON table (col_list);

ALTER TABLE tbl_name ADD INDEX name (col_list);

Таблица остается доступной для операций чтения и записи во время создания индекса. Выполнение оператора CREATE INDEX завершается только после завершения всех транзакций, которые обращаются к таблице, чтобы начальное состояние индекса отражало наиболее последние данные таблицы.

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

Новый вторичный индекс содержит только данные, подтвержденные в таблице на момент завершения выполнения оператора CREATE INDEX или ALTER TABLE. Он не содержит не подтвержденных значений, старых версий значений или значений, помеченных на удаление, но еще не удаленных из старого индекса.

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

Удаление индекса

DROP INDEX name ON table;

ALTER TABLE tbl_name DROP INDEX name;

Таблица остается доступной для операций чтения и записи во время удаления индекса. Оператор DROP INDEX завершается только после того, как будут завершены все транзакции, обращающиеся к таблице, чтобы начальное состояние индекса отражало самые последние данные таблицы.

Переименование индекса

ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name, ALGORITHM=INPLACE, LOCK=NONE;

Добавление FULLTEXT индекса

CREATE FULLTEXT INDEX name ON table(column);

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

Добавление SPATIAL индекса

CREATE TABLE geom (g GEOMETRY NOT NULL);
ALTER TABLE geom ADD SPATIAL INDEX(g), ALGORITHM=INPLACE, LOCK=SHARED;

Изменение типа индекса (USING {BTREE | HASH})

ALTER TABLE tbl_name DROP INDEX i1, ADD INDEX i1(key_part,...) USING BTREE, ALGORITHM=INSTANT;

Добавление первичного ключа

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

Перестраивает таблицу на месте. Данные существенно переупорядочиваются, что делает эту операцию дорогостоящей. ALGORITHM=INPLACE не разрешается при определенных условиях, если столбцы необходимо преобразовать в NOT NULL.

Перестройка всегда требует копирования данных таблицы. Поэтому лучше определить первичный ключ при создании таблицы, а не выполнять ALTER TABLE ... ADD PRIMARY KEY позже.

При создании UNIQUE или PRIMARY KEY индекса MySQL выполняет дополнительные действия. Для UNIQUE индексов MySQL проверяет, что таблица не содержит дублирующих значений для ключа. Для PRIMARY KEY индекса MySQL также проверяет, что ни один из столбцов PRIMARY KEY не содержит NULL.

При добавлении первичного ключа с помощью предложения ALGORITHM=COPY, MySQL преобразует NULL значения в связанных столбцах в значения по умолчанию: 0 для чисел, пустую строку для столбцов на основе символов и BLOB, и 0000-00-00 00:00:00 для DATETIME. Это нестандартное поведение, от которого Oracle рекомендует воздержаться.

Добавление первичного ключа с использованием ALGORITHM=INPLACE разрешается только в том случае, если значение параметра SQL_MODE включает флаги strict_trans_tables или strict_all_tables; когда значение SQL_MODE строгое, ALGORITHM=INPLACE разрешается, но операция может быть прервана, если столбцы запрошенного первичного ключа содержат NULL значения. Поведение ALGORITHM=INPLACE более соответствует стандартам.

Если вы создаёте таблицу без первичного ключа, MySQL выбирает его за вас, что может быть первым UNIQUE ключом, определённым в NOT NULL столбцах, или сгенерированным системой ключом. Чтобы избежать неопределённости и потенциальной потребности в дополнительном скрытом столбце, укажите предложение PRIMARY KEY как часть оператора CREATE TABLE.

MySQL создаёт новый кластеризованный индекс, копируя существующие данные из исходной таблицы в временную таблицу с требуемой структурой индекса. После полного копирования данных во временную таблицу исходная таблица переименовывается с другим именем временной таблицы. Временная таблица, содержащая новый кластеризованный индекс, переименовывается в имя исходной таблицы, а исходная таблица удаляется из базы данных.

Онлайн-улучшения производительности, которые применяются к операциям со вторичными индексами, не применяются к индексу первичного ключа. Строки таблицы InnoDB хранятся в структуре, организованной на основе первичного ключа, образуя то, что некоторые системы баз данных называют «таблицей с индексированной организацией». Поскольку структура таблицы тесно связана с первичным ключом, переопределение первичного ключа по-прежнему требует копирования данных.

Когда операция с первичным ключом использует ALGORITHM=INPLACE, даже если данные всё ещё копируются, это более эффективно, чем использование ALGORITHM=COPY, потому что:

Удаление первичного ключа

ALTER TABLE tbl_name DROP PRIMARY KEY, ALGORITHM=COPY;

Только ALGORITHM=COPY поддерживает удаление первичного ключа без добавления нового в том же ALTER TABLE операторе.

Удаление первичного ключа и добавление другого

ALTER TABLE tbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

Данные существенно переупорядочиваются, что делает эту операцию дорогостоящей.

Добавление столбца STORED

ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) STORED), ALGORITHM=COPY;

ADD COLUMN не является операцией на месте для хранимых столбцов (выполняется без временной таблицы), потому что выражение должно быть вычислено сервером.

Изменение порядка столбцов STORED

ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED FIRST, ALGORITHM=COPY;

Перестраивает таблицу на месте.

Удаление столбца STORED

ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;

Перестраивает таблицу на месте.

Добавление столбца VIRTUAL

ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL), ALGORITHM=INSTANT;

Добавление виртуального столбца может выполняться мгновенно или на месте для таблиц без разделения.

Добавление VIRTUAL не является операцией на месте для разделяемых таблиц.

Изменение порядка столбцов VIRTUAL

ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL FIRST, ALGORITHM=COPY;

Удаление столбца VIRTUAL

ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;

Удаление столбца VIRTUAL может выполняться мгновенно или на месте для таблиц без разделения.

Добавление ограничения внешнего ключа

Алгоритм INPLACE поддерживается, когда foreign_key_checks отключен. В противном случае поддерживается только алгоритм COPY.

ALTER TABLE tbl1 ADD CONSTRAINT fk_name FOREIGN KEY index (col1)
  REFERENCES tbl2(col2) referential_actions;

Удаление ограничения внешнего ключа

ALTER TABLE tbl DROP FOREIGN KEY fk_name;

Удаление внешнего ключа может выполняться онлайн с параметром foreign_key_checks включённым или выключенным.

Если вам не известны имена ограничений внешних ключей для определённой таблицы, выполните следующую команду и найдите имя ограничения в фрагменте CONSTRAINT для каждого внешнего ключа:

SHOW CREATE TABLE table\G

Или запросите таблицу схемы информации TABLE_CONSTRAINTS и используйте столбцы CONSTRAINT_NAME и CONSTRAINT_TYPE для идентификации имён внешних ключей.

Также можно удалить внешний ключ и связанный с ним индекс в одной команде:

ALTER TABLE table DROP FOREIGN KEY constraint, DROP INDEX index;

Изменение ROW_FORMAT

ALTER TABLE tbl_name ROW_FORMAT = row_format, ALGORITHM=INPLACE, LOCK=NONE;

Данные существенно переупорядочиваются, что делает операцию дорогостоящей.

Дополнительную информацию об опции ROW_FORMAT см. в разделе Параметры таблицы.

Изменение KEY_BLOCK_SIZE

ALTER TABLE tbl_name KEY_BLOCK_SIZE = value, ALGORITHM=INPLACE, LOCK=NONE;

Данные существенно переупорядочиваются, что делает операцию дорогостоящей.

Дополнительную информацию об опции KEY_BLOCK_SIZE см. в разделе Параметры таблицы.

Установка параметров постоянных статистических данных таблицы

ALTER TABLE tbl_name STATS_PERSISTENT=0, STATS_SAMPLE_PAGES=20, STATS_AUTO_RECALC=1, ALGORITHM=INPLACE, LOCK=NONE;

Изменяет только метаданные таблицы.

Постоянные статистики включают STATS_PERSISTENT, STATS_AUTO_RECALC и STATS_SAMPLE_PAGES. Для получения дополнительной информации см. Раздел 17.8.10.1, «Настройка параметров постоянных статистических данных оптимизатора».

Указание кодовой страницы

ALTER TABLE tbl_name CHARACTER SET = charset_name, ALGORITHM=INPLACE, LOCK=NONE;

Перестраивает таблицу, если новое кодирование отличается.

Преобразование кодовой страницы

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name, ALGORITHM=COPY;

Перестраивает таблицу, если новое кодирование отличается.

Оптимизация таблицы

OPTIMIZE TABLE tbl_name;

Операция на месте не поддерживается для таблиц с индексами FULLTEXT. Операция использует алгоритм INPLACE, но синтаксис ALGORITHM и LOCK не допускается.

Перестройка таблицы с опцией FORCE

ALTER TABLE tbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;

Использует ALGORITHM=INPLACE начиная с MySQL 5.6.17. ALGORITHM=INPLACE не поддерживается для таблиц с индексами FULLTEXT.

Выполнение «нулевой» перестройки

ALTER TABLE tbl_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

Использует ALGORITHM=INPLACE начиная с MySQL 5.6.17. ALGORITHM=INPLACE не поддерживается для таблиц с индексами FULLTEXT.

Переименование таблицы

ALTER TABLE old_tbl_name RENAME TO new_tbl_name, ALGORITHM=INSTANT;

Переименование таблицы может выполняться мгновенно или на месте. MySQL переименовывает файлы, соответствующие таблице tbl_name, без создания копии. (Вы также можете использовать оператор RENAME TABLE для переименования таблиц. См. Раздел 15.1.36, «Оператор RENAME TABLE».) Предоставленные конкретно для переименованной таблицы привилегии не мигрируют в новое имя. Их необходимо изменить вручную.

Переименование общего табличного пространства

ALTER TABLESPACE tablespace_name RENAME TO new_tablespace_name;

ALTER TABLESPACE ... RENAME TO использует алгоритм INPLACE, но не поддерживает предложение ALGORITHM.

Включение или отключение шифрования общего табличного пространства

ALTER TABLESPACE tablespace_name ENCRYPTION='Y';

ALTER TABLESPACE ... ENCRYPTION использует алгоритм INPLACE, но не поддерживает предложение ALGORITHM.

Для получения дополнительной информации см. Раздел 17.13, «Шифрование данных InnoDB».

Включение или отключение шифрования табличного пространства на основе файла на таблицу

ALTER TABLE tbl_name ENCRYPTION='Y', ALGORITHM=COPY;

Для получения дополнительной информации см. Раздел 17.13, «Шифрование данных InnoDB».