Моментальное добавление столбца для InnoDB

Обычно добавление столбца в таблицу требует полного перестроения таблицы. Сложность операции пропорциональна размеру таблицы или O(n·m), где n — количество строк в таблице, а m — количество индексов.

В MariaDB 10.0 и более поздних версиях оператор ALTER TABLE поддерживает онлайн DDL для движков хранилища, которые реализовали соответствующие алгоритмы и стратегии блокировки онлайн DDL.

Двигатель хранилища InnoDB реализовал онлайн DDL для многих операций. Эти оптимизации онлайн DDL позволяют в многих случаях выполнять одновременные операции DML с таблицей, даже если таблица нуждается в перестроении.

См. Обзор онлайн DDL для InnoDB для получения дополнительной информации об онлайн DDL с InnoDB.

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

В отличие от этого, с новым моментальным ALTER TABLE ... ADD COLUMN требуется только операция O(log n) для вставки специальной скрытой записи в таблицу и обновления словаря данных. Для большой таблицы вместо нескольких часов операция завершится в мгновение ока. Операция ALTER TABLE ... ADD COLUMN лишь немного дороже, чем обычная операция INSERT из-за ограничений блокировки.

В прошлом некоторые разработчики реализовывали своего рода «моментальное добавление столбца» в приложении, кодируя несколько столбцов в один столбец типа TEXT или BLOB. MariaDB Динамические столбцы являлись ранним примером этого. Более свежим примером являются функции JSON и связанные с ними функции манипулирования строками.

Добавление реальных столбцов имеет следующие преимущества по сравнению с кодированием столбцов в один «расширяемый» столбец:

• Эффективное хранение в собственном двоичном формате
• Безопасность типов данных
• Индексы могут быть построены непосредственно
• Доступны ограничения: UNIQUE, CHECK, FOREIGN KEY
• Можно указать значения по умолчанию
• Триггеры можно писать легче

С моментальным ALTER TABLE ... ADD COLUMN вы можете получить все преимущества структурированного хранения без недостатка необходимости перестраивать таблицу.

Моментальное ALTER TABLE ... ADD COLUMN доступно как для старых, так и для новых таблиц InnoDB. В основном вы можете просто обновиться с MySQL 5.x или MariaDB и начать моментально добавлять столбцы.

Столбцы, моментально добавленные в таблицу, существуют в отдельной структуре данных от основного определения таблицы, аналогично тому, как InnoDB разделяет BLOB столбцы. Если таблица когда-либо станет пустой (например, из-за операторов TRUNCATE или DELETE), InnoDB интегрирует добавленные моментально столбцы в основное определение таблицы. См. InnoDB Online DDL Operations with ALGORITHM=INSTANT: Non-canonical Storage Format Caused by Some Operations для получения дополнительной информации.

Операция также безопасна при сбоях. Если сервер будет остановлен во время выполнения моментального ALTER TABLE ... ADD COLUMN, при восстановлении InnoDB интегрирует новый столбец, выравнивая определение таблицы.

Ограничения

• В MariaDB 10.3 моментальное ALTER TABLE ... ADD COLUMN применяется только в том случае, если добавленные столбцы появляются последними в таблице. Спецификатор места LAST является значением по умолчанию. Если AFTER столбец указан, тогда столбец должен быть последним, в противном случае операция потребует перестроения таблицы. В MariaDB 10.4 это ограничение было снято.

• Если таблица содержит скрытый FTS_DOC_ID столбец из-за FULLTEXT INDEX, то моментальное ALTER TABLE ... ADD COLUMN будет невозможно.

• Файлы данных InnoDB после моментального ALTER TABLE ... ADD COLUMN нельзя импортировать в более старые версии MariaDB или MySQL без предварительного перестроения.

• После использования Моментального ALTER TABLE ... ADD COLUMN, любая операция перестроения таблицы, такая как ALTER TABLE … FORCE, включит моментально добавленные столбцы в основное тело таблицы.

• Моментальное ALTER TABLE ... ADD COLUMN недоступно для ROW_FORMAT=COMPRESSED.

• В MariaDB 10.3 операция ALTER TABLE … DROP COLUMN требует перестроения таблицы. В MariaDB 10.4 это ограничение было снято.

Пример

CREATE TABLE t(id INT PRIMARY KEY, u INT UNSIGNED NOT NULL UNIQUE)
ENGINE=InnoDB;

INSERT INTO t(id,u) VALUES(1,1),(2,2),(3,3);

ALTER TABLE t ADD COLUMN
(d DATETIME DEFAULT current_timestamp(),
 p POINT NOT NULL DEFAULT ST_GeomFromText('POINT(0 0)'),
 t TEXT CHARSET utf8 DEFAULT 'The quick brown fox jumps over the lazy dog');

UPDATE t SET t=NULL WHERE id=3;

SELECT id,u,d,ST_AsText(p),t FROM t;

SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_instant_alter_column';

Приведённый выше пример демонстрирует, что когда добавленные столбцы объявлены NOT NULL, значение по умолчанию должно быть доступно, либо подразумевается типом данных, либо явно задано пользователем. Выражение не обязательно должно быть константой, но оно не должно ссылаться на столбцы таблицы, например DEFAULT u+1 (расширение MariaDB). Значение по умолчанию current_timestamp() будет вычислено в момент выполнения ALTER TABLE и применено к каждой строке, как это происходит при не-моментальном ALTER TABLE. Если последующий ALTER TABLE изменит значение по умолчанию для последующего INSERT, значения столбцов в существующих записях естественным образом не изменятся.

Дизайн был разработан в апреле инженерами из MariaDB Corporation, Alibaba и Tencent. Прототип был разработан Вином Чэном (陈福荣) из команды DBA Tencent Game.

См. также

• Другие операции INSTANT в InnoDB