大表 DDL 的在线变更方案

给一张几百 GB 的表加个字段，开发以为是一行 SQL 的事，DBA 知道这可能是几小时的磁盘 IO 和一次业务抖动。搞清楚 DDL 背后的执行路径，才能判断一次变更到底需要多少资源、会不会阻塞业务。

DDL 为什么会锁表

原因有两类。

第一类：元数据锁（MDL）。任何访问表的语句（包括 SELECT）都会持有该表的 MDL 读锁，DDL 需要 MDL 写锁。两者互斥。这意味着：

一个长时间运行的查询会阻塞后面的 DDL。
一个等待中的 DDL 会阻塞它之后所有要访问这张表的查询——即使 DDL 本身执行很快，排在它后面的请求全部卡住，连接迅速堆积。

这是”加个字段导致整个服务不可用”最常见的成因，与 DDL 本身的耗时无关。

-- 排查 MDL 等待的源头
SELECT object_type, object_name, lock_type, lock_duration, lock_status, owner_thread_id
FROM performance_schema.metadata_locks
WHERE object_name = 'orders';

SELECT * FROM sys.schema_table_lock_waitsG   -- 更直观的等待链

第二类：表重建（rebuild）。修改行格式的操作需要把每一行按照新结构重写一遍：加列（早期版本一定是）、修改列类型、改字符集、删除列、改主键、调整行格式。整个过程中的数据量级是 O(N)，耗时取决于 IO 吞吐，与是否”online”无关，只影响期间允不允许并发写入。

Online DDL 覆盖了什么

InnoDB 引入了三种粒度的 online DDL 行为，通过 ALGORITHM 和 LOCK 子句指定：

ALTER TABLE orders ADD COLUMN ext_code VARCHAR(32) DEFAULT NULL,
  ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM	含义	是否有拷贝
`INSTANT`	只改元数据，不涉及数据页	否，秒级完成
`INPLACE`	原地重建，期间允许并发 DML	是，涉及数据页重写
`COPY`	建临时表，拷数据，期间不允许写	是，且锁表

ALTER TABLE orders ADD COLUMN ext_code VARCHAR(32) DEFAULT NULL, ALGORITHM=INSTANT;
-- Query OK, 0 rows affected (0.04 sec)

INSTANT 的限制比较关键：

加列只能加到表的末尾，不支持指定位置（某些版本支持 Instant Add Column 到任意位置，取决于版本）。
不能同时做多个可能冲突的操作（比如同一语句里加列又删列）。
对行格式有要求（压缩表、含全文索引等可能不支持）。
同一张表做过 INSTANT 加列的次数存在上限，达到后需要重建，之后的加列会退化为 INPLACE。

哪些操作不需要重建，常见的有：

加列、删列（取决于版本，加列通常是 INSTANT，删列往往是 INPLACE 重建）
修改列默认值（仅改元数据）
修改列的 NULL 属性 —— 通常仍需重建
加/删二级索引 —— INPLACE，不重建主键，但要扫描全表构建索引，期间允许 DML
修改表名、修改列注释、修改自增起始值 —— 仅元数据

需要重建的典型操作：改列类型（如 INT 改 BIGINT）、改字符集、删主键、加主键、改行格式（ROW_FORMAT）、OPTIMIZE TABLE。

执行前可以用 --dry-run 语义或者先在一个小副本上验证：

ALTER TABLE orders CHANGE amount amount DECIMAL(18,2), ALGORITHM=INSTANT;
-- ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE.
--                     Try ALGORITHM=COPY/INPLACE.

错误信息直接说明了支持和不支持的原因，这两行比任何文档都准确。

需要注意 INPLACE 的”不阻塞写入”是有代价的：并发期间的 DML 会被记录到 row log（一块有上限的内存区域，由 innodb_online_alter_log_max_size 控制）。 row log 满了之后的追加会失败，整个 ALTER 回滚。在大表上加二级索引时，如果写入并发很高，很容易踩到这个上限。

影子表方案的核心思路

当 Online DDL 无法覆盖（比如要改类型、要跨版本、要避免主从延迟），或者不希望在主库上产生大量 IO，影子表方案提供了另一条路。四个阶段：

1. 建影子表：_orders_new，结构与目标一致
2. 同步增量：把变更期间产生的新增/修改同步到影子表
3. 分批拷贝：把源表的历史数据一块块搬过去（限速）
4. 原子切换：RENAME TABLE orders TO _orders_old, _orders_new TO orders

最后一步的 RENAME 在同一条语句里完成多张表的重命名，整个交换对其他会话而言是原子的：

RENAME TABLE orders TO _orders_old, _orders_new TO orders;

切换瞬间仍然需要持有 MDL 排它锁，因此需要等所有在途的查询结束。这个窗口通常是毫秒到秒级，但如果此时有长事务正在访问这张表，DDL 会等待，而它之后的所有请求会堆积。切换前必须确认没有长事务：

SELECT trx_id, trx_started, trx_rows_locked, LEFT(trx_query, 80)
FROM information_schema.innodb_trx
WHERE trx_started < NOW() - INTERVAL 5 SECOND
ORDER BY trx_started;

分批拷贝是整个过程中最耗时的部分，必须限速，否则会把磁盘 IO 打满：

-- 一次搬一个区间，控制单次影响的行数与 IO
INSERT INTO _orders_new (id, user_id, amount, status, created_at)
SELECT id, user_id, amount, status, created_at
FROM orders
WHERE id BETWEEN ? AND ?            -- 按主键区间切片

切片粒度决定了每次拷贝的负载，通常控制在单次执行几百毫秒到一两秒的量级，通过程序循环执行并在每轮之间 sleep 来摊平 IO。

增量同步：触发器 vs binlog

触发器方案（pt-online-schema-change 采用）

在源表上建三个触发器，INSERT / UPDATE / DELETE 分别同步到影子表：

CREATE TRIGGER pt_orders_ins AFTER INSERT ON orders
FOR EACH ROW REPLACE INTO _orders_new (id, ...) VALUES (NEW.id, ...);

CREATE TRIGGER pt_orders_upd AFTER UPDATE ON orders
FOR EACH ROW REPLACE INTO _orders_new (id, ...) VALUES (NEW.id, ...);

CREATE TRIGGER pt_orders_del AFTER DELETE ON orders
FOR EACH ROW DELETE FROM _orders_new WHERE id = OLD.id;

REPLACE INTO 的用法解决了”拷贝进行中某行被更新”的顺序问题：后到的 UPDATE 覆盖先前拷过去的旧版本。

代价是：触发器在源表的写路径上同步执行，每条 DML 都要额外做一次影子表的写，写延迟增加，TPS 可能明显下降；源表不能有已存在的触发器；触发器与外键约束在某些场景冲突。

binlog 方案（gh-ost 采用）

伪装成一个从库，订阅主库的 binlog 流，把 Row Event 应用到影子表：

gh-ost ──(订阅)──> master binlog
       ──(应用)──> _orders_new
       ──(拷贝)──> SELECT 源表 + INSERT INTO _orders_new

它的写负载作用在影子表上，源表侧的额外成本几乎只有 binlog 读取。因此可以动态限速：根据主库的复制延迟、Threads_running、IO 利用率实时调整拷贝速率：

gh-ost 
  --host=127.0.0.1 --database=app --table=orders 
  --alter="ADD COLUMN ext_code VARCHAR(32) DEFAULT NULL" 
  --chunk-size=1000 --max-load=Threads_running=40 
  --critical-load=Threads_running=100 
  --max-lag-millis=1500 --throttle-flag-file=/tmp/ghost.pause 
  --switch-to-rbr --allow-on-master --execute

--throttle-flag-file 是一个很实用的机制：创建该文件即可让工具暂停，删除则继续,给了外部自动化一个干预入口。

代价是：需要伪装成从库读取 binlog，要求 binlog 格式为 ROW（工具可用 --switch-to-rbr 自动切换，但这会影响其他从库）；要求被改表有主键或唯一键来支撑切片与 UPDATE 定位；不能处理外键；部署复杂度高于触发器方案。

两者的取舍没有对错：触发器方案部署简单、对 binlog 格式无要求，代价是源表写放大；binlog 方案对源表影响小且可限速，代价是依赖 ROW 格式且有更多前置要求。

切换前后的校验

拷贝完成不等于数据一致。可做的校验：

-- 行数比对（注意：拷贝期间源表仍在变化，两行数不可能完全一致，要看差值稳定性）
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM _orders_new;

-- 抽样 checksum 比对
SELECT BIT_XOR(CRC32(CONCAT_WS('|', id, amount, status))) 
FROM orders WHERE id BETWEEN 1000000 AND 1001000;
SELECT BIT_XOR(CRC32(CONCAT_WS('|', id, amount, status))) 
FROM _orders_new WHERE id BETWEEN 1000000 AND 1001000;

切换完成后不能直接删掉旧表。应该保留一段时间（几天到一周），作为回滚路径：

RENAME TABLE orders TO _orders_broken, _orders_old TO orders;   -- 回滚

变更前的容量评估

一次大表 DDL 之前要算清楚的是：

磁盘空间。影子表方案需要额外一整份数据。假设表 500GB，加上索引可能接近 1TB。这个空间必须预留到位，而不是看当前剩余空间”差不多够”。考虑到 InnoDB 的表空间释放机制（DROP TABLE 会释放，但单个 ibd 文件不会自动收缩），长期看要规划一次表空间回收。

IO 容量。拷贝速率上限取决于磁盘。限速阈值应基于线下压测得出的”不影响业务的最大 IO”，而不是拍一个百分比。可以用 --max-load 让工具自适应限速，也可以用 cgroup 限制进程资源。

时间窗口。估算公式：

总时长 ≈ 数据量 / 有效拷贝速率 + 追赶增量的时长

有效拷贝速率要在同规格机器上先试跑小片得到。如果算下来超过可接受窗口，应该考虑：能否用 INSTANT / INPLACE 替代；能否先归档冷数据减小单表体积；能否拆成分阶段变更（先加列，再回填数据，最后加索引）。

主从延迟。INPLACE 方案下，主库执行完之后这条语句要到从库重放，从库执行期间同样产生延迟。如果采用在从库上先执行、再主从切换的方式（”先切再改”），可以避开主库的 IO 冲击，但需要保证切换前从库健康且延迟为零。

回滚方案。每一步都要能退回：接管后 INSTANT 加列不可回滚（某些版本支持 INSTANT 回滚但有限制）；影子表方案靠保留旧表回滚；INPLACE DDL 中断时会回滚并清理临时文件，回滚本身的耗时可能很长。

一份变更清单

确认 MDL 等待风险：变更窗口内是否有长事务、是否有长时间持有该表的定时任务或慢查询。
确认没有触发器、外键冲突。
确认 binlog 格式是否满足所选方案的要求。
确认磁盘剩余空间 ≥ 表大小 × 安全系数。
在低峰期执行，且先在一张小的同结构表上完整跑一遍流程。
准备限速与暂停手段，并把回滚命令提前写好。
切换后观察：错误日志、慢日志、主从延迟、应用连接池的 pending 数量。

大表 DDL 不是一条 SQL 的事情，是一次有预案的变更操作。它的工作量不在那条 ALTER 上，而在前面的评估和后面的观察。