Как работает online DDL в MySQL 8 и когда операция всё равно блокирует?

Как работает Online DDL

MySQL 8 поддерживает три алгоритма для ALTER TABLE:

• INSTANT — изменяет только метаданные в data dictionary, без касания данных. Самый быстрый (миллисекунды).
• INPLACE — реорганизует данные на месте, не создавая полную копию. DML (INSERT/UPDATE/DELETE) продолжает работать во время операции.
• COPY — создаёт полную копию таблицы. Блокирует DML операции.

ALGORITHM=INSTANT (MySQL 8.0+)

-- Добавление столбца в конец таблицы — INSTANT:
ALTER TABLE orders ADD COLUMN notes TEXT, ALGORITHM=INSTANT;

-- Изменение DEFAULT значения:
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending', ALGORITHM=INSTANT;

-- Добавление/удаление виртуального столбца:
ALTER TABLE orders ADD COLUMN total_with_tax DECIMAL(10,2)
  AS (amount * 1.2) VIRTUAL, ALGORITHM=INSTANT;

-- Проверить, что поддерживается INSTANT:
ALTER TABLE orders ADD COLUMN notes TEXT, ALGORITHM=INSTANT, LOCK=NONE;
-- Если INSTANT невозможен — вернёт ошибку, можно перейти на INPLACE

ALGORITHM=INPLACE (Online DDL)

-- Добавление индекса — INPLACE, concurrent DML разрешён:
ALTER TABLE orders
  ADD INDEX idx_status_date (status, created_at),
  ALGORITHM=INPLACE,
  LOCK=NONE;

-- Добавление столбца в середину таблицы (MySQL 8.0 < 8.0.29 — INPLACE):
ALTER TABLE orders
  ADD COLUMN priority INT NOT NULL DEFAULT 0 AFTER status,
  ALGORITHM=INPLACE,
  LOCK=NONE;

-- Удаление индекса:
ALTER TABLE orders DROP INDEX idx_old, ALGORITHM=INPLACE, LOCK=NONE;

При INPLACE MySQL буферизует входящий DML в online DDL log (временный файл) и применяет его после завершения реорганизации.

Когда блокирует (ALGORITHM=COPY)

-- Изменение типа столбца с конвертацией данных:
ALTER TABLE orders MODIFY COLUMN amount BIGINT;  -- COPY, блокирует!

-- Изменение charset/collation столбца с данными:
ALTER TABLE users MODIFY COLUMN name VARCHAR(100)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;  -- обычно COPY

-- Добавление/удаление первичного ключа:
ALTER TABLE logs DROP PRIMARY KEY;  -- COPY

-- Узнать заранее:
ALTER TABLE orders MODIFY COLUMN amount BIGINT, ALGORITHM=INPLACE;
-- ERROR 1846: ALGORITHM=INPLACE is not supported — нужен COPY

Metadata lock (MDL)

Любой online DDL берёт metadata lock в начале и конце операции. Если в момент старта или завершения DDL есть длинная открытая транзакция — DDL будет ждать MDL release:

-- Найти блокирующие транзакции:
SELECT * FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'PENDING';

SELECT * FROM information_schema.processlist
WHERE command != 'Sleep' ORDER BY time DESC;

pt-online-schema-change и gh-ost для нулевого даунтайма

Для операций, требующих COPY, или для гарантии нулевого влияния на production:

# gh-ost: без триггеров, читает binlog
gh-ost \
  --user=repl --password=secret \
  --host=replica.db \
  --database=mydb --table=orders \
  --alter="MODIFY COLUMN amount BIGINT" \
  --allow-on-master \
  --execute

# pt-osc: через триггеры
pt-online-schema-change \
  --alter="MODIFY COLUMN amount BIGINT" \
  D=mydb,t=orders \
  --execute

Подводные камни

• INPLACE не блокирует DML, но блокирует DDL queue — все последующие ALTER TABLE ждут завершения.
• Online DDL log (буфер DML изменений) ограничен параметром innodb_online_alter_log_max_size (default 128 MB) — при overflow операция откатывается с ошибкой.
• INSTANT доступен только для добавления столбцов в конец (в MySQL 8.0 до 8.0.29) или в любую позицию (MySQL 8.0.29+) — в более ранних версиях срединная вставка требует INPLACE.
• Репликация: DDL реплицируется через binlog и выполняется на replica синхронно — долгий DDL на primary вызывает replication lag.
• FOREIGN KEY операции почти всегда требуют COPY.
• MDL ожидание не отображается в SHOW PROCESSLIST как "Waiting for metadata lock" до тех пор, пока DDL не заблокируется сам — это создаёт невидимую очередь блокировок.
• gh-ost не работает с таблицами без PRIMARY KEY или без уникального индекса.

• Отвечать определением без production-сценария.
• Не называть runtime boundary, security boundary или failure mode.
• Игнорировать версию API, observability и тестовую проверку.

• Объясняет механизм своими словами и без выдуманных API.
• Называет реальные риски, диагностику и критерий корректности.
• Связывает ответ с текущей документацией и миграционными ограничениями.