面试知识库
中 困难

大表DDL操作实战#

一句话答案#

千万级大表做 COPY 类 DDL（改列类型等）或对复制延迟敏感时不宜直接 ALTER，用 pt-online-schema-change（触发器复制）或 gh-ost（binlog 复制）实现不锁表的在线 DDL；MySQL 8.0 起加列/删列多数可走 INSTANT（秒级），加二级索引走 INPLACE 且允许并发读写，先确认原生 Online DDL 能否满足再上工具。

核心要点

直接 ALTER 的问题#

ALTER TABLE orders ADD COLUMN remark VARCHAR(255);  -- 5.7 及以前需重建表，千万级表耗时数分钟到数小时；8.0.12+ 默认 INSTANT，秒级完成
sql

MySQL DDL 的三种算法：

ALGORITHM过程是否锁表
COPY创建新表→复制数据→交换表名锁表（全程不可写）
INPLACE在原表上就地修改部分操作短暂锁表
INSTANT只修改元数据（MySQL 8.0.12+）不锁表（毫秒级，但仍要短暂拿 MDL 排他锁）

INSTANT 支持的操作有限（加列、删列、修改默认值、改列名等；8.0.29 前只能加到末尾，8.0.29 起可加在任意位置并支持 INSTANT 删列；单表最多累计 64 次行版本变更，超出需重建表），加索引走 INPLACE（允许并发 DML），改列类型只能 COPY（不允许并发 DML）。

pt-online-schema-change（Percona）#

原理：触发器 + 数据复制

1. 创建新表（schema 已修改）
2. 在原表上创建三个触发器：INSERT/UPDATE/DELETE
   → 增量变更实时同步到新表
3. 按 chunk 批量复制原表历史数据到新表
4. 交换表名（RENAME TABLE，原子操作）
5. 删除旧表和触发器
plaintext

优点： 成熟稳定、使用广泛 缺点：

  • 触发器有性能开销（写入 QPS 下降 10%-30%）
  • 原表已有触发器时默认无法使用（MySQL 5.7.2+ 可加 --preserve-triggers）
  • 外键处理复杂

gh-ost（GitHub）#

原理：binlog 复制，无触发器

1. 创建 ghost 表（schema 已修改）
2. 伪装为 MySQL 从库，从 binlog 读取增量变更
   → 实时回放到 ghost 表
3. 按 chunk 批量复制原表历史数据到 ghost 表
4. 原子切换表名（cut-over）
plaintext

优点：

  • 无触发器，对原表写入无额外开销
  • 可暂停/限速，灵活控制迁移速度
  • 支持动态调整参数（不用重启）

缺点：

  • 需要 binlog 为 ROW 格式
  • 实现更复杂

pt-osc vs gh-ost 选型#

维度pt-oscgh-ost
增量同步方式触发器binlog
写入性能影响较大（触发器开销）较小
可控性一般好（可暂停、限速）
适用场景通用写入密集型、binlog=ROW
复杂度简单较复杂

操作注意事项#

  • 磁盘空间：需要额外一倍的表空间（新旧表并存）
  • 执行时间：千万级表通常 30 分钟到数小时，取决于表大小和限速
  • 低峰执行：尽管不锁表，但会占用 IO 和带宽
  • 数据校验：切换前后抽样/按 chunk 计算 checksum 对比新旧表（注意 pt-table-checksum 是校验主从一致性的，不是比对新旧表）
  • 回滚方案：保留旧表一段时间，确认无误后再删除

面试回答（2分钟版）

千万级大表做需要重建表的 DDL（尤其改列类型这种只能 COPY、不允许并发写的操作）可能长时间阻塞写入，而且 DDL 在从库上重放还会造成数小时的复制延迟，所以生产环境通常用 pt-online-schema-change 或 gh-ost 来做不锁表的在线 DDL。两者核心思路一样：创建一张结构已修改的新表，一边批量复制存量数据，一边同步增量变更，最后原子切换表名。区别在于增量同步方式：pt-osc 在原表上建三个触发器实时捕获增量，简单但写入性能有 10%-30% 的开销；gh-ost 则伪装为从库直接从 binlog 读取增量，无触发器开销且支持暂停限速，但要求 binlog 格式为 ROW。实际操作中需要注意：执行前检查磁盘空间是否足够容纳两倍表大小，尽量在低峰期执行，完成后校验新旧表数据一致性，并保留旧表一段时间用于回滚。另外 MySQL 8.0 的 INSTANT DDL 让加列/删列（8.0.29 起任意位置）变成秒级元数据操作，加二级索引也是 INPLACE 且允许并发读写，这些场景可以先考虑原生 Online DDL；改列类型这类只能 COPY 的操作，或者担心主从延迟、需要限速暂停时，才需要这些工具。

追问与易错

追问方向：

  • “gh-ost 的 cut-over 怎么做到原子的？”→ 两个连接配合：一个连接先建哨兵表并 LOCK TABLES 锁住原表和哨兵表，另一个连接发起 RENAME TABLE 原表→_del, ghost→原表 被阻塞排队；确认增量回放追平后删哨兵表、释放锁，RENAME 优先于排队的 DML 执行，一次原子交换，业务只感到短暂阻塞；失败则释放锁回退、可重试
  • “迁移过程中原表还能写吗？”→ 可以，增量通过触发器/binlog 同步
  • “MySQL 8.0 的 INSTANT DDL 了解吗？”→ 8.0.12 引入，只改数据字典元数据不重建表；支持加列（8.0.29 起任意位置）、删列（8.0.29+）、改默认值等，8.0 起加列默认就是 INSTANT；不支持 ROW_FORMAT=COMPRESSED、含 FULLTEXT 索引的表；加索引仍需 INPLACE，改列类型需 COPY

易错点：

  • ❌ “MySQL 5.7+ 的 Online DDL 不锁表”——INPLACE 在开始和提交阶段仍要短暂拿 MDL 排他锁；如果此时有长事务占着 MDL 读锁，DDL 会被卡住，后续所有读写也跟着排队
  • ❌ 直接在高峰期执行 ALTER——即使用工具也应低峰操作
  • ❌ 不检查磁盘空间就执行——新表 + 旧表 需要双倍空间