MySQL / 系统课时

选择原生 DDL 或外部工具

选择原生 DDL 或外部工具

这节课给出一套可执行的选型顺序:先判断 MySQL 原生 DDL 能否以可接受的算法和锁级别完成,再结合表规模、外键、复制拓扑、维护窗口与变更类型,在原生 DDL、pt-online-schema-change 和 gh-ost 之间做取舍。

学习目标

  • 用变更算法、锁行为和表重建成本判断是否优先使用原生 DDL
  • 比较 pt-online-schema-change 与 gh-ost 在增量同步、外键和复制环境上的边界
  • 建立一套上线前检查清单,避免只凭“表很大”选择工具

先判断原生 DDL 是否已经足够

选型的第一问不是“表有多大”,而是“这个 ALTER 在当前 MySQL 版本上实际会怎么执行”。如果变更支持 ALGORITHM=INSTANT,通常只改元数据,成本远低于外部工具;如果能以 ALGORITHM=INPLACE, LOCK=NONE 完成,也应先评估原生方案,因为它少一套 ghost 表、增量同步和切表流程。

反过来,如果变更会重建整表、需要 COPY、不允许并发 DML,或者预计持续时间超过业务可接受窗口,才进入外部在线变更工具的比较。

收集决定工具的事实

在做方案评审前,先把目标表规模、外键、触发器和 binlog 条件查出来。下面的查询会把关键事实压成一行,便于进入选型流程。

sql
SET @db = 'shop';
SET @tbl = 'orders';

SELECT
  ROUND((t.DATA_LENGTH + t.INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS table_gb,
  (
    SELECT COUNT(*)
    FROM information_schema.KEY_COLUMN_USAGE k
    WHERE k.REFERENCED_TABLE_SCHEMA = @db
      AND k.REFERENCED_TABLE_NAME = @tbl
  ) AS incoming_fk,
  (
    SELECT COUNT(*)
    FROM information_schema.KEY_COLUMN_USAGE k
    WHERE k.TABLE_SCHEMA = @db
      AND k.TABLE_NAME = @tbl
      AND k.REFERENCED_TABLE_NAME IS NOT NULL
  ) AS outgoing_fk,
  (
    SELECT COUNT(*)
    FROM information_schema.TRIGGERS tr
    WHERE tr.EVENT_OBJECT_SCHEMA = @db
      AND tr.EVENT_OBJECT_TABLE = @tbl
  ) AS trigger_count,
  @@binlog_format AS binlog_format,
  @@binlog_row_image AS binlog_row_image
FROM information_schema.TABLES t
WHERE t.TABLE_SCHEMA = @db
  AND t.TABLE_NAME = @tbl;

预期结果

text
+----------+-------------+-------------+---------------+---------------+------------------+
| table_gb | incoming_fk | outgoing_fk | trigger_count | binlog_format | binlog_row_image |
+----------+-------------+-------------+---------------+---------------+------------------+
| <VALUE>  | <VALUE>     | <VALUE>     | <VALUE>       | ROW           | FULL             |
+----------+-------------+-------------+---------------+---------------+------------------+

<VALUE> 会随环境变化。这个结果本身不会替你做决定,但能暴露最常见的硬约束:外键会显著增加影子表切换复杂度,触发器会影响基于触发器的方案,而 gh-ost 的核心增量链路依赖 row-based binlog。

对比三类方案的控制面

维度 原生 DDL pt-online-schema-change gh-ost
增量同步 MySQL 内部完成 触发器把原表 DML 同步到新表 读取 binlog 并异步应用到 ghost 表
最适合 INSTANT 或可接受的 INPLACE 需要重建且环境适合触发器方案 需要重建并强调暂停、节流、复制延迟控制
外键 由 MySQL 原生语义处理 可处理部分外键场景,但切换与约束重建更复杂 不应把有外键关系的目标表作为默认适用场景
复制依赖 无额外 binlog 消费要求 可监控副本延迟 需要可用的 row-based binlog 来源
最终切换 由服务器执行 ALTER 复制完成后 RENAME/swap 追平 binlog 后执行受控 cut-over
运维复杂度 最低 中 中到高,但控制能力强

这个表格只能做第一轮筛选。真正的决策还要看业务窗口:原生 INPLACE 即使允许 DML,也可能消耗大量 I/O;外部工具虽然把复制拆成小批次,却会把风险周期拉长到数小时,并引入 ghost 表空间、增量追平和最终切换。

按约束顺序做决策

原生 DDL、pt-online-schema-change 与 gh-ost 的选型流程

实际评审可以按下面顺序执行:

  1. 先查当前版本对目标 ALTER 的在线 DDL 能力,并显式写出计划使用的 ALGORITHM 与 LOCK,避免依赖默认降级。
  2. 如果原生方案需要整表重建,估算 I/O、redo、临时空间和持续时间,再与业务窗口比较。
  3. 有外键时先把引用方向和约束数量列清楚。外部工具的“换表”会让外键成为核心风险,而不是附属检查项。
  4. 无外键但需要强节流、暂停和延迟 cut-over,并且复制/binlog 条件满足时,gh-ost 通常更有控制力。
  5. 环境适合触发器式增量同步、且希望使用 pt-online-schema-change 的成熟 chunk 复制与副本延迟保护时,再选择 pt-osc。

用一次小变更验证原生路径

如果目标变更属于当前版本支持的 INSTANT 范围,可以在测试环境显式指定算法,验证“原生优先”的判断,而不是一上来就引入外部工具。以下示例创建一个临时练习表并增加可空列。

sql
CREATE TABLE ddl_choice_demo (
  id BIGINT PRIMARY KEY,
  payload VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

ALTER TABLE ddl_choice_demo
  ADD COLUMN audit_tag VARCHAR(32) NULL,
  ALGORITHM=INSTANT;

SHOW COLUMNS FROM ddl_choice_demo LIKE 'audit_tag';

预期结果

text
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| audit_tag | varchar(32) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

如果显式 ALGORITHM=INSTANT 被服务器拒绝,这个失败本身就是重要信号:不要静默去掉算法让 MySQL 自行选择更重的执行方式,应回到“变更类型与窗口”重新评估 INPLACE、COPY 或外部工具。

最终选择可以归纳成一句话:能用低风险原生 DDL 就优先原生;必须重建时,再按外键和复制拓扑筛掉不兼容方案,最后比较 pt-osc 的触发器路径与 gh-ost 的 binlog 路径谁更符合你的运维控制需求。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

选择原生 DDL 或外部工具

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通