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

CREATE INDEX name ON table (col_list);

ALTER TABLE tbl_name ADD INDEX name (col_list);

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

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

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

Если сервер завершится во время создания вторичного индекса, при восстановлении MySQL удалит любые частично созданные индексы. Необходимо повторно выполнить оператор ALTER TABLE или CREATE INDEX.

Некоторые факторы влияют на производительность, использование памяти и семантику этой операции. Подробности см. в Разделе 14.13.6, «Ограничения онлайн-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=INPLACE;

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

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;

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

Добавление колонки

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INPLACE, LOCK=NONE;

Одновременная DML не разрешена при добавлении колонки. Данные существенно реорганизуются, что делает эту операцию дорогостоящей. Как минимум, необходимо ALGORITHM=INPLACE, LOCK=SHARED.

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

ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INPLACE, LOCK=NONE;

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

Переименование колонки

ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INPLACE, LOCK=NONE;

Для разрешения одновременной DML сохраните тот же тип данных и измените только имя колонки.

Если вы сохраняете тот же тип данных и [NOT] NULL атрибут, изменяя только имя колонки, операция всегда может выполняться онлайн.

Вы также можете переименовать колонку, входящую в ограничение внешнего ключа. Определение внешнего ключа автоматически обновляется для использования нового имени колонки. Переименование колонки, участвующей во внешнем ключе, работает только с ALGORITHM=INPLACE. Если вы используете ALGORITHM=COPY клаузу или какое-то другое условие приводит к тому, что операция использует ALGORITHM=COPY, операция ALTER TABLE терпит неудачу.

ALGORITHM=INPLACE не поддерживается для переименования колонки.

Перестановка колонок

Для перестановки колонок используйте FIRST или AFTER в операциях CHANGE или MODIFY.

ALTER TABLE tbl_name MODIFY COLUMN col_name column_definition FIRST, ALGORITHM=INPLACE, LOCK=NONE;

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

Изменение типа данных колонки

ALTER TABLE tbl_name CHANGE c1 c1 BIGINT, ALGORITHM=COPY;

Изменение типа данных колонки поддерживается только с ALGORITHM=COPY.

Расширение размера колонки VARCHAR

ALTER TABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;

Количество байтов длины, необходимых для колонки VARCHAR, должно оставаться неизменным. Для колонок VARCHAR размером от 0 до 255 байт требуется один байт длины для кодирования значения. Для колонок VARCHAR размером 256 байт или более требуется два байта длины. В результате, локальная операция ALTER TABLE поддерживает только увеличение размера колонки VARCHAR с 0 до 255 байт или с 256 байт до большего размера. Локальная операция ALTER TABLE не поддерживает увеличение размера колонки VARCHAR с менее чем 256 байт до размера, равного или большего 256 байт. В этом случае меняется количество необходимых байтов длины с 1 на 2, что поддерживается только копией таблицы (ALGORITHM=COPY). Например, попытка изменить размер колонки VARCHAR с использованием локальной операции ALTER TABLE возвращает следующую ошибку:

ALTER TABLE tbl_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change
column type INPLACE. Try ALGORITHM=COPY.

Уменьшение размера колонки VARCHAR с помощью локальной операции ALTER TABLE не поддерживается. Для уменьшения размера колонки VARCHAR требуется копия таблицы (ALGORITHM=COPY).

Установка значения по умолчанию для колонки

ALTER TABLE tbl_name ALTER COLUMN col SET DEFAULT literal, ALGORITHM=INPLACE, LOCK=NONE;

Изменяет только метаданные таблицы. Значения по умолчанию колонок хранятся в для таблицы, а не в InnoDB.

Удаление значения по умолчанию для колонки

ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT, ALGORITHM=INPLACE, LOCK=NONE;

Изменение значения автоинкремента

ALTER TABLE table AUTO_INCREMENT=next_value, ALGORITHM=INPLACE, LOCK=NONE;

Изменяет значение, хранящееся в памяти, а не в файле данных.

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

Определение колонки как NULL

ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NULL, ALGORITHM=INPLACE, LOCK=NONE;

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

Определение колонки как NOT NULL

ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;

Перестраивает таблицу на месте. STRICT_ALL_TABLES или STRICT_TRANS_TABLES SQL_MODE необходимы для успешного выполнения операции. Операция завершается неудачей, если колонка содержит значения NULL. Сервер запрещает изменения колонок внешнего ключа, которые могут привести к потере целостности ссылок. См. Раздел 13.1.8, «Операция ALTER TABLE». Данные существенно реорганизуются, что делает эту операцию дорогостоящей.

Изменение определения колонки ENUM или SET

CREATE TABLE t1 (c1 ENUM('a', 'b', 'c'));
ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd'), ALGORITHM=INPLACE, LOCK=NONE;

Изменение определения колонки ENUM или SET путем добавления новых элементов перечисления или множества в конец списка допустимых значений может выполняться на месте, если размер хранилища типа данных не меняется. Например, добавление элемента в колонку SET с 8 элементами меняет требуемый размер хранилища на значение с 1 байта на 2 байта; это требует копирования таблицы. Добавление элементов в середине списка приводит к переиндексации существующих элементов, что требует копирования таблицы.

Добавление 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=INPLACE, LOCK=NONE;

Добавление виртуального столбца — это операция на месте для таблиц без разбиения. Однако добавление виртуального столбца не может быть объединено с другими действиями ALTER TABLE.

Добавление 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=INPLACE, LOCK=NONE;

Удаление VIRTUAL столбца — это операция на месте для таблиц без разбиения. Однако удаление виртуального столбца не может быть объединено с другими действиями ALTER TABLE.

Удаление 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

Либо запросите таблицу Information Schema 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. Дополнительную информацию см. в Разделе 14.8.11.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=INPLACE, LOCK=NONE;

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