面经策

数据库表上新增一个字段，如果这个表正在进行读写操作，如何处理才能不影响现有读写操作？ ​

  • 标签：后端、场景题

🏛 业务架构师 ​

大表在线加字段，核心矛盾是：DDL 在大表上执行可能长时间锁表或 IO 打满，影响在线读写。我按 MySQL 版本和字段类型分情况给方案，并强调变更管理纪律。

情况一：MySQL 8.0，最理想

  • 加字段到表末尾：ALGORITHM=INSTANT，只改元数据，秒级完成，完全不碰数据行
  • 加索引也是 ONLINE DDL（ALGORITHM=INPLACE, LOCK=NONE），允许并发读写，但仍要重建索引消耗资源

情况二：MySQL 5.7 或 8.0 的复杂变更

原生 DDL 有锁表风险或耗时过长（亿级表 COPY 算法几个小时），用在线变更工具：

  • gh-ost（我首选）：基于 binlog 回放构建影子表，可限速、可暂停、可随时终止，对主库压力可控——高峰期发现延迟立即 throttle
  • pt-online-schema-change：触发器方案，老牌但触发器对写入有额外开销

执行纪律（架构师重点，事故都在这里）

  1. 变更窗口：避开业务高峰，凌晨执行，提前公告
  2. 先在从库/预发演练：记录耗时、观察延迟，评估对线上影响
  3. 限速执行：gh-ost 设定复制延迟阈值，超过自动暂停——主从延迟会切成读库读到旧数据的一致性问题
  4. 可中止：任何时刻能放弃变更且不留半成品（gh-ost 清理影子表）
  5. 大表 DDL 走审批 + DBA 双人复核：它是高危变更，不是普通工单
  6. 变更前检查磁盘剩余空间（影子表要双倍空间）、长事务（MDL 锁等待会阻塞后续所有查询——DDL 被 MDL 队列卡住时全表读写排队，这是经典事故，先 kill 长查询）

收尾：新字段设计时尽量可空、带默认值、加在末尾——让未来的变更永远是 INSTANT，这是业务表设计的远见。

🏗 系统架构师 ​

大表在线加字段，我按 MySQL 版本与现代工具讲，核心是不长时间锁表。

1. MySQL 8.0 的 INSTANT DDL（首选） 8.0 对"末尾加列"支持 ALGORITHM=INSTANT：只改元数据，毫秒级完成，不拷贝数据不锁表（8.0.29 后甚至支持任意位置加列）。这是 2026 年的默认答案：

sql
ALTER TABLE big_table
  ADD COLUMN remark VARCHAR(200) DEFAULT NULL,
  ALGORITHM=INSTANT;

局限：INSTANT 只覆盖部分操作（加列、改默认值、重命名列等），且 INSTANT 列数量上限 64；改列类型、加索引仍要走 INPLACE。

2. INPLACE（8.0 加索引、修改列类型）ALGORITHM=INPLACE, LOCK=NONE：在线构建，期间允许 DML，但要控制时长——大表重建耗时与行数、机器 IO 相关，期间 online DDL 日志增长、主从延迟放大。执行前评估：从库先执行验证（主从延迟可控时倒切）。

3. 第三方工具 gh-ost / pt-online-schema-change（复杂变更兜底） 原理相同：建影子表 → 拷贝数据（gh-ost 用 binlog 回放增量，pt-osc 用触发器）→ 原子 rename 切换。

  • 优点：可限速（--max-load 控制主从延迟与负载）、可暂停、可测试切换
  • 我的生产规范：亿级表或高峰期的 INPLACE 变更一律走 gh-ost，低峰执行，限延迟阈值（如延迟 > 1s 自动节流）

4. 流程规范（架构师必备）

  1. 变更评审：确认操作类型、行数、预计时长、回滚方案
  2. 先在预生产等量级数据演练
  3. 低峰执行 + 监控主从延迟与 threads_running
  4. 代码兼容：先加字段（带 DEFAULT）→ 应用发布读写新字段 → 再考虑旧数据回填，回填按主键分批（每批 1000 行 sleep），绝不用一条 UPDATE 全表
  5. 外键约束、长事务会阻塞 DDL 的 metadata lock——执行前查 information_schema.innodb_trx 杀长事务

💻 全栈开发专家 ​

线上表加字段，我的原则：优先 INSTANT，不行再 gh-ost，最后才考虑锁表窗口。

MySQL 8.0：INSTANT 秒加

8.0 大部分加尾列操作支持 ALGORITHM=INSTANT，只改元数据，秒级完成、不阻塞 DML：

sql
ALTER TABLE orders ADD COLUMN remark VARCHAR(200) DEFAULT NULL,
ALGORITHM=INSTANT;

限制：加列只能在最后、不能与其他 DDL 混合。8.0 早期版本（8.0.12~8.0.28）有限制细节差异，执行前 ALTER ... , ALGORITHM=INSTANT 显式声明，不支持会直接报错而不是退化，安全。

MySQL 5.7 / 不满足 INSTANT 条件：gh-ost / pt-osc

bash
gh-ost \
  --host=master --database=shop --table=orders \
  --alter="ADD COLUMN remark VARCHAR(200)" \
  --chunk-size=2000 --max-load=Threads_running=50 \
  --cut-over=default --allow-on-master

原理：建影子表 + binlog 回放同步增量，跑完原子切换（cut-over）。全程可限流（--max-load），可暂停，可动态改参数，对线上影响可控。

通用注意事项

  1. 长事务是 MDL 杀手：DDL 排队等 MDL 锁时会把后续所有查询堵住。执行前查 information_schema.INNODB_TRX 确认无长事务，设置 lock_wait_timeout=10 快速失败。
  2. 低峰执行 + 提前在预发演练（表结构与数据量一致的影子库验证耗时）。
  3. 大表加索引同理：8.0 并行 DDL（innodb_ddl_threads）或 gh-ost。