Операции InnoDB онлайн DDL с алгоритмом INPLACE Alter

Операции, поддерживаемые по наследованию

Когда используется фраза ALGORITHM со значением INPLACE, поддерживаемые операции являются супермножеством операций, поддерживаемых при использовании фразы ALGORITHM со значением NOCOPY. Аналогично, когда фраза ALGORITHM имеет значение NOCOPY, поддерживаемые операции являются супермножеством операций, поддерживаемых при использовании фразы ALGORITHM со значением INSTANT.

Поэтому, когда фраза ALGORITHM имеет значение INPLACE, некоторые операции поддерживаются по наследованию. Дополнительную информацию о поддерживаемых операциях можно найти на следующих страницах:

Операции с колонками

`ALTER TABLE ... ADD COLUMN`

InnoDB поддерживает добавление колонок в таблицу с установленным значением ALGORITHM в INPLACE.

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

За исключением добавления колонки с автоинкрементом, эта операция поддерживает стратегию без блокировки. Эту стратегию можно явно выбрать, установив фразу LOCK в значение NONE. При использовании этой стратегии разрешается выполнение всех параллельных операций DML.

Например:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD COLUMN c varchar(50);
Query OK, 0 rows affected (0.006 sec)

Это относится к ALTER TABLE ... ADD COLUMN для таблиц InnoDB.

`ALTER TABLE ... DROP COLUMN`

InnoDB поддерживает удаление колонок из таблицы с установленным значением ALGORITHM в INPLACE.

Эта операция поддерживает стратегию без блокировки. Эту стратегию можно явно выбрать, установив фразу LOCK в значение NONE. При использовании этой стратегии разрешается выполнение всех параллельных операций DML.

Например:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab DROP COLUMN c;
Query OK, 0 rows affected (0.021 sec)

Это относится к ALTER TABLE ... DROP COLUMN для таблиц InnoDB.

`ALTER TABLE ... MODIFY COLUMN`

Это относится к ALTER TABLE ... MODIFY COLUMN для таблиц InnoDB.

Переупорядочение колонок

InnoDB поддерживает переупорядочение колонок в таблице с установленным значением ALGORITHM в INPLACE.

Например:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c varchar(50) AFTER a;
Query OK, 0 rows affected (0.022 sec)

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

InnoDB не поддерживает изменение типа данных колонки с установленным значением ALGORITHM в INPLACE в большинстве случаев. Есть некоторые исключения:

В MariaDB 10.2.2 и более поздних версиях InnoDB поддерживает увеличение длины колонок VARCHAR с установленным значением ALGORITHM в INPLACE, за исключением случаев, когда это потребует изменения количества байт, необходимых для представления длины колонки. Колонка VARCHAR размером от 0 до 255 байт требует 1 байт для представления своей длины, а колонка VARCHAR размером 256 байт или более требует 2 байта для представления своей длины. Это означает, что длина колонки не может быть увеличена с ALGORITHM со значением INPLACE если первоначальная длина была меньше 256 байт, а новая длина составляет 256 байт или более.

В MariaDB 10.4.3 и более поздних версиях InnoDB поддерживает увеличение длины колонок VARCHAR с установленным значением ALGORITHM в INPLACE в тех случаях, когда операция поддерживает установку фразы ALGORITHM со значением INSTANT.

Для получения дополнительной информации см. Операции InnoDB Online DDL с ALGORITHM=INSTANT: Изменение типа данных колонки.

Например, это не выполняется:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c int;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY

Но это выполняется в MariaDB 10.2.2 и более поздних версиях, потому что первоначальная длина колонки меньше 256 байт, а новая длина все еще меньше 256 байт:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
) CHARACTER SET=latin1;

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c varchar(100);
Query OK, 0 rows affected (0.005 sec)

Но это не выполняется в MariaDB 10.2.2 и более поздних версиях, потому что первоначальная длина колонки меньше 256 байт, а новая длина больше 256 байт:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(255)
) CHARACTER SET=latin1;

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c varchar(256);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY

Изменение колонки на NULL

InnoDB поддерживает изменение колонки для разрешения значений NULL с установленным значением ALGORITHM в INPLACE.

Например:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50) NOT NULL
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c varchar(50) NULL;
Query OK, 0 rows affected (0.021 sec)

Изменение колонки на NOT NULL

InnoDB поддерживает изменение колонки для запрещения значений NULL с установленным значением ALGORITHM в INPLACE. Требуется включить режим строгий режим в настройке SQL_MODE. Операция завершится неудачей, если колонка содержит какие-либо значения NULL. Изменения, которые могут повлиять на целостность ссылок, также не разрешены.

Например:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c varchar(50) NOT NULL;
Query OK, 0 rows affected (0.021 sec)

Добавление нового значения `ENUM`

InnoDB поддерживает добавление нового значения ENUM в колонку с установленным значением ALGORITHM в INPLACE. Чтобы добавить новое значение ENUM с ALGORITHM со значением INPLACE, должны быть выполнены следующие условия:

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

Эта операция изменяет только метаданные таблицы, поэтому перестроения таблицы не требуется.

Например, это выполняется:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c ENUM('red', 'green')
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c ENUM('red', 'green', 'blue');
Query OK, 0 rows affected (0.004 sec)

Но это не выполняется:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c ENUM('red', 'green')
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c ENUM('red', 'blue', 'green');
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY

Добавление нового значения `SET`

InnoDB поддерживает добавление нового значения SET в колонку с установленным значением ALGORITHM в INPLACE. Чтобы добавить новое значение SET с ALGORITHM со значением INPLACE, должны быть выполнены следующие условия:

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

Эта операция изменяет только метаданные таблицы, поэтому перестроения таблицы не требуется.

Например, это выполняется:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c SET('red', 'green')
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c SET('red', 'green', 'blue');
Query OK, 0 rows affected (0.004 sec)

Но это не выполняется:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c SET('red', 'green')
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c SET('red', 'blue', 'green');
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY

Удаление системного версиирования из колонки

В MariaDB 10.3.8 и более поздних версиях InnoDB поддерживает удаление системного версиирования из колонки с установленным значением ALGORITHM в INPLACE. Для этого необходимо установить системную переменную system_versioning_alter_history в значение KEEP. Дополнительную информацию см. на MDEV-16330.

Например:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50) WITH SYSTEM VERSIONING
);

SET SESSION system_versioning_alter_history='KEEP';
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c varchar(50) WITHOUT SYSTEM VERSIONING;
Query OK, 0 rows affected (0.005 sec)

`ALTER TABLE ... ALTER COLUMN`

Это относится к ALTER TABLE ... ALTER COLUMN для таблиц InnoDB.

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

InnoDB поддерживает изменение значения DEFAULT столбца с ALGORITHM, установленным в INPLACE.

Эта операция изменяет только метаданные таблицы, поэтому перестроение таблицы не требуется.

Эта операция поддерживает стратегию без блокировки. Эту стратегию можно явно выбрать, установив в LOCK NONE. Когда используется эта стратегия, разрешены все одновременные операции DML. Например:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ALTER COLUMN c SET DEFAULT 'No value explicitly provided.';
Query OK, 0 rows affected (0.005 sec)

Удаление значения DEFAULT столбца

InnoDB поддерживает удаление значения DEFAULT столбца с ALGORITHM, установленным в INPLACE.

Эта операция изменяет только метаданные таблицы, поэтому перестроение таблицы не требуется.

Например:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50) DEFAULT 'No value explicitly provided.'
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ALTER COLUMN c DROP DEFAULT;
Query OK, 0 rows affected (0.005 sec)

`ALTER TABLE ... CHANGE COLUMN`

InnoDB поддерживает переименование столбца с ALGORITHM, установленным в INPLACE, если при этом не изменился тип данных или атрибуты столбца помимо имени.

Эта операция изменяет только метаданные таблицы, поэтому перестроение таблицы не требуется.

Например, это успешно выполняется:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab CHANGE COLUMN c str varchar(50);
Query OK, 0 rows affected (0.006 sec)

Но это неудачно:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab CHANGE COLUMN c num int;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY

Это относится к ALTER TABLE ... CHANGE COLUMN для таблиц InnoDB.

Операции с индексами

`ALTER TABLE ... ADD PRIMARY KEY`

InnoDB поддерживает добавление первичного ключа в таблицу с ALGORITHM, установленным в INPLACE.

Если новый столбец первичного ключа не определён как NOT NULL, то настоятельно рекомендуется включить строгий режим в SQL_MODE. В противном случае значения NULL будут молча преобразованы в значение по умолчанию для данного типа данных, что, вероятно, не является желаемым поведением в этом случае.

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

Например, это успешно выполняется:

CREATE OR REPLACE TABLE tab (
   a int,
   b varchar(50),
   c varchar(50)
);

SET SESSION sql_mode='STRICT_TRANS_TABLES';
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD PRIMARY KEY (a);
Query OK, 0 rows affected (0.021 sec)

Но это неудачно:

CREATE OR REPLACE TABLE tab (
   a int,
   b varchar(50),
   c varchar(50)
);

INSERT INTO tab VALUES (NULL, NULL, NULL);

SET SESSION sql_mode='STRICT_TRANS_TABLES';
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD PRIMARY KEY (a);
ERROR 1265 (01000): Data truncated for column 'a' at row 1

И это неудачно:

CREATE OR REPLACE TABLE tab (
   a int,
   b varchar(50),
   c varchar(50)
);

INSERT INTO tab VALUES (1, NULL, NULL);
INSERT INTO tab VALUES (1, NULL, NULL);

SET SESSION sql_mode='STRICT_TRANS_TABLES';
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD PRIMARY KEY (a);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Это относится к ALTER TABLE ... ADD PRIMARY KEY для таблиц InnoDB.

`ALTER TABLE ... DROP PRIMARY KEY`

InnoDB не поддерживает удаление первичного ключа с ALGORITHM, установленным в INPLACE в большинстве случаев.

Если вы попытаетесь сделать это, увидите ошибку. InnoDB поддерживает эту операцию только с ALGORITHM, установленным в COPY. Одновременные операции DML не разрешены.

Однако есть исключение. Если вы удаляете первичный ключ и одновременно добавляете новый, эта операция может быть выполнена с ALGORITHM, установленным в INPLACE. Эта операция поддерживает стратегию без блокировки. Эту стратегию можно явно выбрать, установив в LOCK NONE. Когда используется эта стратегия, разрешены все одновременные операции DML.

Например, это неудачно:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab DROP PRIMARY KEY;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY

Но это успешно выполняется:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION sql_mode='STRICT_TRANS_TABLES';
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab DROP PRIMARY KEY, ADD PRIMARY KEY (b);
Query OK, 0 rows affected (0.020 sec)

Это относится к ALTER TABLE ... DROP PRIMARY KEY для таблиц InnoDB.

`ALTER TABLE ... ADD INDEX` и `CREATE INDEX`

Это относится к ALTER TABLE ... ADD INDEX и CREATE INDEX для таблиц InnoDB.

Добавление простого индекса

InnoDB поддерживает добавление простого индекса в таблицу с ALGORITHM, установленным в INPLACE. Таблица не перестраивается.

Например, это успешно выполняется:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD INDEX b_index (b);
Query OK, 0 rows affected (0.010 sec)

И это успешно выполняется:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
CREATE INDEX b_index ON tab (b);
Query OK, 0 rows affected (0.011 sec)

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

InnoDB поддерживает добавление индекса FULLTEXT в таблицу с ALGORITHM, установленным в INPLACE. В некоторых случаях таблица не перестраивается.

Однако существуют некоторые ограничения, такие как:

Добавление индекса FULLTEXT в таблицу, не имеющую столбца FTS_DOC_ID, потребует перестроения таблицы один раз. При перестроении таблицы система добавляет скрытый столбец FTS_DOC_ID. С этого момента добавление дополнительных индексов FULLTEXT в ту же таблицу не потребует перестроения таблицы при ALGORITHM, установленном в INPLACE.

Одновременно с ALGORITHM, установленным в INPLACE, может быть добавлен только один индекс FULLTEXT.

Если в таблице более одного индекса FULLTEXT, то она не может быть перестроена никакими операциями ALTER TABLE, когда ALGORITHM установлен в INPLACE.

Если таблица имеет индекс FULLTEXT, то она не может быть перестроена никакими операциями ALTER TABLE, когда в LOCK установлено NONE.

Эта операция поддерживает стратегию блокировки только для чтения. Эту стратегию можно явно выбрать, установив в LOCK SHARED. При использовании этой стратегии разрешены одновременные операции DML только для чтения.

Например, это успешно выполняется, но требует перестроения таблицы, чтобы можно было добавить скрытый столбец FTS_DOC_ID.

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD FULLTEXT INDEX b_index (b);
Query OK, 0 rows affected (0.055 sec)

И это успешно выполняется аналогичным образом:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
CREATE FULLTEXT INDEX b_index ON tab (b);
Query OK, 0 rows affected (0.041 sec)

И это успешно выполняется, и второе команд не требует перестроения таблицы:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD FULLTEXT INDEX b_index (b);
Query OK, 0 rows affected (0.043 sec)

ALTER TABLE tab ADD FULLTEXT INDEX c_index (c);
Query OK, 0 rows affected (0.017 sec)

Но эта вторая команда не выполняется, так как одновременно может быть добавлен только один индекс FULLTEXT:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50),
   d varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD FULLTEXT INDEX b_index (b);
Query OK, 0 rows affected (0.041 sec)

ALTER TABLE tab ADD FULLTEXT INDEX c_index (c), ADD FULLTEXT INDEX d_index (d);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY

И эта третья команда не выполняется, потому что таблица не может быть перестроена, если в ней более одного индекса FULLTEXT:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD FULLTEXT INDEX b_index (b);
Query OK, 0 rows affected (0.040 sec)

ALTER TABLE tab ADD FULLTEXT INDEX c_index (c);
Query OK, 0 rows affected (0.015 sec)

ALTER TABLE tab FORCE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY

Добавление пространственного индекса

InnoDB поддерживает добавление пространственного индекса SPATIAL в таблицу с ALGORITHM, установленным в INPLACE.

Однако есть некоторые ограничения, такие как:

Если таблица имеет индекс SPATIAL, то она не может быть перестроена никакими операциями ALTER TABLE, когда в LOCK установлено NONE.

Например, это успешно выполняется:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c GEOMETRY NOT NULL
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD SPATIAL INDEX c_index (c);
Query OK, 0 rows affected (0.006 sec)

И это успешно выполняется аналогичным образом:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c GEOMETRY NOT NULL
);

SET SESSION alter_algorithm='INPLACE';
CREATE SPATIAL INDEX c_index ON tab (c);
Query OK, 0 rows affected (0.006 sec)

`ALTER TABLE ... DROP INDEX` и `DROP INDEX`

InnoDB поддерживает удаление индексов из таблицы с ALGORITHM, установленным в INPLACE.

Эта операция изменяет только метаданные таблицы, поэтому перестроение таблицы не требуется.

Например, это успешно выполняется:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50),
   INDEX b_index (b)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab DROP INDEX b_index;

И это успешно выполняется:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50),
   INDEX b_index (b)
);

SET SESSION alter_algorithm='INPLACE';
DROP INDEX b_index ON tab;

Это относится к ALTER TABLE ... DROP INDEX и DROP INDEX для таблиц InnoDB.

`ALTER TABLE ... ADD FOREIGN KEY`

InnoDB поддерживает добавление ограничений внешнего ключа в таблицу с ALGORITHM, установленным в INPLACE. Для добавления нового ограничения внешнего ключа в таблицу с ALGORITHM, установленным в INPLACE, переменная системы foreign_key_checks должна быть установлена в OFF. Если она установлена в ON, то требуется ALGORITHM=COPY.

Эта операция изменяет только метаданные таблицы, поэтому перестроение таблицы не требуется.

Например, это неудачно:

CREATE OR REPLACE TABLE tab1 (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50),
   d int
);

CREATE OR REPLACE TABLE tab2 (
   a int PRIMARY KEY,
   b varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab1 ADD FOREIGN KEY tab2_fk (d) REFERENCES tab2 (a);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY

Но это успешно выполняется:

CREATE OR REPLACE TABLE tab1 (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50),
   d int
);

CREATE OR REPLACE TABLE tab2 (
   a int PRIMARY KEY,
   b varchar(50)
);

SET SESSION foreign_key_checks=OFF;
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab1 ADD FOREIGN KEY tab2_fk (d) REFERENCES tab2 (a);
Query OK, 0 rows affected (0.011 sec)

Это относится к ALTER TABLE ... ADD FOREIGN KEY для таблиц InnoDB.

`ALTER TABLE ... DROP FOREIGN KEY`

InnoDB поддерживает удаление ограничений внешнего ключа из таблицы с ALGORITHM, установленным в INPLACE.

Данная операция изменяет только метаданные таблицы, поэтому перестроение таблицы не требуется.

Данная операция поддерживает стратегию без блокировки. Эту стратегию можно явно выбрать, установив в LOCK предложение NONE. При использовании этой стратегии разрешены все одновременные DML-операции.

Например:

CREATE OR REPLACE TABLE tab2 (
   a int PRIMARY KEY,
   b varchar(50)
);

CREATE OR REPLACE TABLE tab1 (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50),
   d int,
   FOREIGN KEY tab2_fk (d) REFERENCES tab2 (a)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab1 DROP FOREIGN KEY tab2_fk;
Query OK, 0 rows affected (0.005 sec)

Это относится к ALTER TABLE ... DROP FOREIGN KEY для таблиц InnoDB.

Операции с таблицами

`ALTER TABLE ... AUTO_INCREMENT=...`

InnoDB поддерживает изменение значения AUTO_INCREMENT таблицы с ALGORITHM, установленным в INPLACE. Данная операция должна завершиться мгновенно. Перестроение таблицы не происходит.

Например:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.004 sec)

Это относится к ALTER TABLE ... AUTO_INCREMENT=... для таблиц InnoDB.

`ALTER TABLE ... ROW_FORMAT=...`

InnoDB поддерживает изменение формата строк таблицы с ALGORITHM, установленным в INPLACE.

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

Например:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
) ROW_FORMAT=DYNAMIC;

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ROW_FORMAT=COMPRESSED;
Query OK, 0 rows affected (0.025 sec)

Это относится к ALTER TABLE ... ROW_FORMAT=... для таблиц InnoDB.

`ALTER TABLE ... KEY_BLOCK_SIZE=...`

InnoDB поддерживает изменение значения KEY_BLOCK_SIZE таблицы с ALGORITHM, установленным в INPLACE.

Например:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
) ROW_FORMAT=COMPRESSED
  KEY_BLOCK_SIZE=4;

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab KEY_BLOCK_SIZE=2;
Query OK, 0 rows affected (0.021 sec)

Это относится к KEY_BLOCK_SIZE=... для таблиц InnoDB.

`ALTER TABLE ... PAGE_COMPRESSED=...` и `ALTER TABLE ... PAGE_COMPRESSION_LEVEL=...`

В MariaDB 10.3.10 и более поздних версиях InnoDB поддерживает установку значения PAGE_COMPRESSED в 1 с ALGORITHM, установленным в INPLACE. InnoDB также поддерживает изменение значения PAGE_COMPRESSED от 1 до 0 с ALGORITHM, установленным в INPLACE.

В этих версиях InnoDB также поддерживает изменение значения PAGE_COMPRESSION_LEVEL с ALGORITHM, установленным в INPLACE.

См. MDEV-16328 для получения дополнительной информации.

Например, это успешно выполняется:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab PAGE_COMPRESSED=1;
Query OK, 0 rows affected (0.006 sec)

И это успешно выполняется:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
) PAGE_COMPRESSED=1;

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab PAGE_COMPRESSED=0;
Query OK, 0 rows affected (0.020 sec)

И это успешно выполняется:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
) PAGE_COMPRESSED=1
  PAGE_COMPRESSION_LEVEL=5;

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab PAGE_COMPRESSION_LEVEL=4;
Query OK, 0 rows affected (0.006 sec)

Это относится к PAGE_COMPRESSED=... и PAGE_COMPRESSION_LEVEL=... для таблиц InnoDB.

`ALTER TABLE ... DROP SYSTEM VERSIONING`

InnoDB поддерживает удаление системной версии из таблицы с ALGORITHM, установленным в INPLACE.

Данная операция поддерживает стратегию блокировки только для чтения. Эту стратегию можно явно выбрать, установив в LOCK предложение SHARED. При использовании этой стратегии разрешены только одновременные DML-операции для чтения.

Например:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
) WITH SYSTEM VERSIONING;

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab DROP SYSTEM VERSIONING;

Это относится к ALTER TABLE ... DROP SYSTEM VERSIONING для таблиц InnoDB.

`ALTER TABLE ... DROP CONSTRAINT`

В MariaDB 10.3.6 и более поздних версиях InnoDB поддерживает удаление CHECK ограничения из таблицы с ALGORITHM, установленным в INPLACE. См. MDEV-16331 для получения дополнительной информации.

Например:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50),
   CONSTRAINT b_not_empty CHECK (b != '')
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab DROP CONSTRAINT b_not_empty;
Query OK, 0 rows affected (0.004 sec)

Это относится к ALTER TABLE ... DROP CONSTRAINT для таблиц InnoDB.

`ALTER TABLE ... FORCE`

InnoDB поддерживает принудительное перестроение таблицы с ALGORITHM, установленным в INPLACE.

Например:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab FORCE;
Query OK, 0 rows affected (0.022 sec)

Это относится к ALTER TABLE ... FORCE для таблиц InnoDB.

`ALTER TABLE ... ENGINE=InnoDB`

InnoDB поддерживает принудительное перестроение таблицы с ALGORITHM, установленным в INPLACE.

Например:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ENGINE=InnoDB;
Query OK, 0 rows affected (0.022 sec)

Это относится к ALTER TABLE ... ENGINE=InnoDB для таблиц InnoDB.

`OPTIMIZE TABLE ...`

InnoDB поддерживает оптимизацию таблицы с ALGORITHM, установленным в INPLACE.

Если системная переменная innodb_defragment установлена в OFF, и системная переменная innodb_optimize_fulltext_only также установлена в OFF, то OPTIMIZE TABLE будет эквивалентно ALTER TABLE … FORCE.

Если хотя бы одна из упомянутых системных переменных установлена в ON, то OPTIMIZE TABLE оптимизирует некоторые данные без перестроения таблицы. Однако размер файла не уменьшится.

Например, это успешно выполняется:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SHOW GLOBAL VARIABLES WHERE Variable_name IN('innodb_defragment', 'innodb_optimize_fulltext_only');
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_defragment             | OFF   |
| innodb_optimize_fulltext_only | OFF   |
+-------------------------------+-------+

SET SESSION alter_algorithm='INPLACE';
OPTIMIZE TABLE tab;
+---------+----------+----------+-------------------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                          |
+---------+----------+----------+-------------------------------------------------------------------+
| db1.tab | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| db1.tab | optimize | status   | OK                                                                |
+---------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.026 sec)

И это успешно выполняется, но таблица не перестраивается:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET GLOBAL innodb_defragment=ON;
SHOW GLOBAL VARIABLES WHERE Variable_name IN('innodb_defragment', 'innodb_optimize_fulltext_only');
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_defragment             | ON    |
| innodb_optimize_fulltext_only | OFF   |
+-------------------------------+-------+

SET SESSION alter_algorithm='INPLACE';
OPTIMIZE TABLE tab;
+---------+----------+----------+----------+
| Table   | Op       | Msg_type | Msg_text |
+---------+----------+----------+----------+
| db1.tab | optimize | status   | OK       |
+---------+----------+----------+----------+
1 row in set (0.004 sec)

Это относится к OPTIMIZE TABLE для таблиц InnoDB.

`ALTER TABLE ... RENAME TO` и `RENAME TABLE ...`

InnoDB поддерживает переименование таблицы с ALGORITHM, установленным в INPLACE.

Данная операция изменяет только метаданные таблицы, поэтому перестроение таблицы не требуется.

Данная операция поддерживает стратегию эксклюзивной блокировки. Эту стратегию можно явно выбрать, установив в LOCK предложение EXCLUSIVE. При использовании этой стратегии одновременные DML-операции не разрешены.

Например, это успешно выполняется:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab RENAME TO old_tab;
Query OK, 0 rows affected (0.011 sec)

И это успешно выполняется:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
RENAME TABLE tab TO old_tab;

Это относится к ALTER TABLE ... RENAME TO и RENAME TABLE для таблиц InnoDB.

Ограничения

Если таблица содержит более одного FULLTEXT индекса, то она не может быть перестроена никакими операциями ALTER TABLE, когда ALGORITHM установлен в INPLACE.

Если таблица содержит FULLTEXT индекс, то она не может быть перестроена никакими операциями ALTER TABLE, когда предложение LOCK установлено в NONE.

Если таблица имеет индекс SPATIAL, то она не может быть перестроена с помощью каких-либо операций ALTER TABLE, когда условие LOCK установлено в NONE.

Генерируемые колонки в настоящее время не поддерживают онлайн DDL для всех тех же операций, которые поддерживаются для "реальных" колонок.

См. Генерируемые (виртуальные и постоянные/хранимые) колонки: Поддержка операторов для получения дополнительной информации об ограничениях.

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

© 2023 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/innodb-online-ddl-operations-with-the-inplace-alter-algorithm/

Операции InnoDB онлайн DDL с алгоритмом INPLACE Alter

Операции, поддерживаемые по наследованию

Операции с колонками

ALTER TABLE ... ADD COLUMN

ALTER TABLE ... DROP COLUMN

ALTER TABLE ... MODIFY COLUMN

Переупорядочение колонок

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

Изменение колонки на NULL

Изменение колонки на NOT NULL

Добавление нового значения ENUM

Добавление нового значения SET

Удаление системного версиирования из колонки

ALTER TABLE ... ALTER COLUMN

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

Удаление значения DEFAULT столбца

ALTER TABLE ... CHANGE COLUMN

Операции с индексами

ALTER TABLE ... ADD PRIMARY KEY

ALTER TABLE ... DROP PRIMARY KEY

ALTER TABLE ... ADD INDEX и CREATE INDEX

Добавление простого индекса

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

Добавление пространственного индекса

ALTER TABLE ... DROP INDEX и DROP INDEX

ALTER TABLE ... ADD FOREIGN KEY

ALTER TABLE ... DROP FOREIGN KEY

Операции с таблицами

ALTER TABLE ... AUTO_INCREMENT=...

ALTER TABLE ... ROW_FORMAT=...

ALTER TABLE ... KEY_BLOCK_SIZE=...

ALTER TABLE ... PAGE_COMPRESSED=... и ALTER TABLE ... PAGE_COMPRESSION_LEVEL=...

ALTER TABLE ... DROP SYSTEM VERSIONING

ALTER TABLE ... DROP CONSTRAINT

ALTER TABLE ... FORCE

ALTER TABLE ... ENGINE=InnoDB

OPTIMIZE TABLE ...

ALTER TABLE ... RENAME TO и RENAME TABLE ...

Ограничения

Ограничения, связанные с полнотекстовыми индексами

Ограничения, связанные с пространственными индексами

Ограничения, связанные с генерируемыми (виртуальными и постоянными/хранимыми) колонками

`ALTER TABLE ... ADD COLUMN`

`ALTER TABLE ... DROP COLUMN`

`ALTER TABLE ... MODIFY COLUMN`

Добавление нового значения `ENUM`

Добавление нового значения `SET`

`ALTER TABLE ... ALTER COLUMN`

`ALTER TABLE ... CHANGE COLUMN`

`ALTER TABLE ... ADD PRIMARY KEY`

`ALTER TABLE ... DROP PRIMARY KEY`

`ALTER TABLE ... ADD INDEX` и `CREATE INDEX`

`ALTER TABLE ... DROP INDEX` и `DROP INDEX`

`ALTER TABLE ... ADD FOREIGN KEY`

`ALTER TABLE ... DROP FOREIGN KEY`

`ALTER TABLE ... AUTO_INCREMENT=...`

`ALTER TABLE ... ROW_FORMAT=...`

`ALTER TABLE ... KEY_BLOCK_SIZE=...`

`ALTER TABLE ... PAGE_COMPRESSED=...` и `ALTER TABLE ... PAGE_COMPRESSION_LEVEL=...`

`ALTER TABLE ... DROP SYSTEM VERSIONING`

`ALTER TABLE ... DROP CONSTRAINT`

`ALTER TABLE ... FORCE`

`ALTER TABLE ... ENGINE=InnoDB`

`OPTIMIZE TABLE ...`

`ALTER TABLE ... RENAME TO` и `RENAME TABLE ...`