Online DDL: ALGORITHM=INSTANT / INPLACE, LOCK=NONE и отслеживание хода выполнения

Команда

-- Always state ALGORITHM/LOCK: unsupported operations fail immediately instead of silently falling back to COPY
ALTER TABLE shop.orders ADD COLUMN status TINYINT NOT NULL DEFAULT 0, ALGORITHM=INSTANT;
ALTER TABLE shop.orders RENAME COLUMN note TO remark, ALGORITHM=INSTANT;
ALTER TABLE shop.orders ADD INDEX ix_status (status), ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE shop.orders MODIFY remark VARCHAR(200), ALGORITHM=INPLACE, LOCK=NONE;

-- INSTANT row version counter (INSTANT is refused at 64, a rebuild resets it)
SELECT NAME, TOTAL_ROW_VERSIONS FROM information_schema.INNODB_TABLES WHERE NAME = 'shop/orders';

-- Progress of a long ALTER (enable the instruments once)
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE 'stage/innodb/alter%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_stages_%';
SELECT t.PROCESSLIST_ID, s.EVENT_NAME, s.WORK_COMPLETED, s.WORK_ESTIMATED,
       ROUND(100 * s.WORK_COMPLETED / NULLIF(s.WORK_ESTIMATED, 0), 1) AS pct_done
FROM performance_schema.events_stages_current s
JOIN performance_schema.threads t ON t.THREAD_ID = s.THREAD_ID
WHERE s.EVENT_NAME LIKE 'stage/innodb/alter%';

Что делает

INSTANT меняет только словарь данных и завершается мгновенно независимо от размера таблицы. INPLACE + LOCK=NONE обрабатывает таблицу на месте, допуская параллельные чтение и запись. Последний запрос показывает, на каком этапе находится выполняющийся ALTER, и процент выполнения.

Когда использовать

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

Внимание

INSTANT и INPLACE всё равно берут кратковременную эксклюзивную блокировку метаданных в начале и в конце; если долгая транзакция удерживает таблицу, ALTER ждёт, и каждый запрос за ним встаёт в очередь (см. диагностику metadata lock). Снизьте lock_wait_timeout в сессии (SET SESSION lock_wait_timeout = 5). DML, поступающий во время INPLACE, журналируется в пределах innodb_online_alter_log_max_size; при превышении ALTER в конце откатывается. Изменение типа столбца, преобразование charset и изменение PK требуют COPY и блокируют запись; на больших таблицах используйте gh-ost / pt-online-schema-change. На реплике ALTER выполняется в один поток столько же времени; большой INPLACE ALTER вызывает отставание реплики, INSTANT — нет.