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 处于哪个阶段及其百分比。

适用场景

在对生产表进行结构变更之前；用于了解哪些操作可以无停机运行，以及估算长时间 ALTER 何时完成。

注意

INSTANT 和 INPLACE 在开始和结束时仍会短暂获取排他元数据锁；如果有长事务持有该表，ALTER 会等待，其后的每个查询都会排队（参见元数据锁诊断）。在会话中调低 lock_wait_timeout（SET SESSION lock_wait_timeout = 5）。INPLACE 期间到达的 DML 会记录到 innodb_online_alter_log_max_size 为止；超过时 ALTER 会在结束时回滚。列类型变更、字符集转换和 PK 变更需要 COPY 并会阻塞写入；在大表上请使用 gh-ost / pt-online-schema-change。在副本上 ALTER 以单线程运行相同的时长；大型 INPLACE ALTER 会造成副本延迟，INSTANT 不会。