上个月我接了一个听起来很简单的需求给主库上一张两千多万行的订单表新增一个字段。这个任务在纸面上就是一条ALTER TABLE的事但凡是操盘过千万级大表的人都明白给这种量级的表加字段真正的风险不在于 SQL 本身而在于你对数据库内部机制有没有敬畏。这篇文章就按操作手册的标准来写为什么加个字段能把实例搞挂、三种主流方案怎么选、动手前检查什么、pt-osc 全流程怎么跑以及我踩过的几个真坑。适合正在带大表、准备做结构变更的 DBA 和负责核心表的后端同学参考。先给结论千万别在业务高峰期对千万级大表直接敲ALTER TABLE ADD COLUMN。这句话不是吓唬人我见过不止一次因为一条加列语句把实例 IO 打到 90% 以上、主从延迟飙到几百秒、接口大面积超时的案例。理解了背后原理你才不会在遇到问题时慌乱也不会被工具的输出日志带着走。1. 加个字段为什么能把大表干趴先弄清 ALTER 的底层逻辑1.1 INPLACE 不等于无代价它要重写整棵聚簇索引很多人有个误区MySQL 5.7 以后 ALTER 支持 INPLACE 算法就以为修改表结构可以不碰数据了。实际上 INPLACE 只是说它不需要像老版本那样生成一个完整临时表再导数据但对于新增字段这种操作InnoDB 依然要把整张表的数据行逐行读出来、补上新列、再写回新的数据页本质上等于把整栋楼的每一层都重新装修一遍。拿我那两千万行的订单表举例数据加索引总共 20GB 出头。哪怕 SSD 顺序写能跑到 100MB/s光重写数据就得几分钟到十几分钟期间 buffer pool 被大量新页挤占原有热数据缓存命中率明显下降磁盘 IO 也被持续占住。这种温和的灾难不会一下打死实例但会让整体性能肉眼可见地下降。提示INPLACE 的意思不是无操作而是数据还是在原来的表空间里重写不额外生成整表副本。代价从行数、行宽、索引数量三个维度叠加行数越多、行越宽、二级索引越多重写越慢。1.2 两颗隐形炸弹MDL 锁等待与在线变更日志溢出加字段真正致命的往往不是数据重写本身而是两个容易被忽略的机制。第一是 MDL元数据锁。所有 DDL 语句开始前需要拿到表的元数据锁结束的瞬间还需要一个极其短暂的排它锁来切换表定义。如果业务里有长事务、长查询正握着这张表的 MDL你的 ALTER 就会卡在 Waiting for table metadata lock而它后面所有访问这张表的 SQL 都会跟着排队。一次 DDL 排队带来的是整个连接池的雪崩。第二是在线变更日志。MySQL 的 online DDL 会把执行期间并发的 DML 增量记录在一个内存日志里默认上限innodb_online_alter_log_max_size是 128MB。如果改表期间这张表的写入量太大增量日志被撑爆整个 ALTER 会直接报ERROR 1799 (HY000): DB_ONLINE_LOG_TOO_BIG。你在低峰期改表能成功高峰期一改就爆就是这个原因。1.3 一次真实事故复盘加列加出了主从延迟我之前处理过的一次线上事故就很典型。同事在 5.7 实例上给一张 1800 万行的用户行为表加字段直接在下午三点执行了ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT 0。语句本身跑了 13 分钟完成但执行期间实例 QPS 掉了三分之一Threads_running从 30 一路涨到 200主从延迟最高到了 90 秒读多写少的业务开始大面积超时。事后复盘原因很清楚行重写占满了 IO 和 buffer pool连接池里的查询开始堆积而主库的每一个改动都要同步到从库串行回放延迟自然被拉大。最讽刺的是这个字段实际上第二天才被代码用到完全可以在凌晨低峰期执行甚至可以用工具限速跑。1.4 千万级大表加字段先记住这六类风险MDL 排队与雪崩长事务卡住 DDL后续所有 SQL 排队接口超时。主从延迟放大主库重写数据 从库串行回放延迟在变更期间显著拉高。磁盘空间翻倍pt-osc/gh-ost 都要建影子表老表还要保留一段时间空间需求超出预期。无法秒级回滚加完列再想删等于再跑一次全表重写回滚根本不快。缓冲池与 IO 被挤占大量页读写把热数据缓存打没整体性能下降。中途失败后的残留工具中断后会留下影子表、触发器清理不及时会影响后续操作。2. 方案怎么选直接 ALTER、pt-osc、gh-ost 的取舍2.1 先看版本8.0.12 的 INSTANT 是白送的机会如果你的实例是 MySQL 8.0.12 及以上版本加字段有一个近乎作弊的选项ALGORITHMINSTANT。这是纯元数据操作只改数据字典不碰数据页秒级完成对业务几乎零影响。ALTER TABLE orders ADD COLUMN user_tag VARCHAR(32) NULL DEFAULT NULL COMMENT 用户标签, ALGORITHMINSTANT, LOCKNONE;当然 INSTANT 有限制8.0.12 到 8.0.28 之间基本只能加在最后一列压缩表、行格式受限、行大小超限等场景会直接报错8.0.29 之后部分场景的支持范围进一步放宽。我的建议是不管什么场景先在测试实例上带上ALGORITHMINSTANT跑一下能成就是白捡的便宜报错就老老实实往下看其他方案。2.2 pt-osc触发器方案为什么经典Percona Toolkit 里的pt-online-schema-change是很多团队操作千万级大表的首选。它的思路可以概括成五步根据原表结构创建一个带新字段的影子表如_orders_new。在原表上创建三个触发器INSERT/UPDATE/DELETE把变更实时同步到影子表。按主键或唯一键分批chunk把原表数据拷贝到影子表每批可限速。拷贝完成后用RENAME TABLE在一条语句里完成新旧表切换。删除触发器默认还会把老表 drop 掉。优点很清楚对原表不加锁、可限速、成熟稳定、参数丰富。缺点同样明显——热点表上每个行变更都会额外触发一次对影子表的写相当于把写放大了一倍如果表上已经有触发器它会直接拒绝执行最后切换时那个短暂的元数据锁依然存在。2.3 gh-ost把触发器换成 binlog 消费gh-ost是 GitHub 开源的方案核心改进是不建触发器。它把自己伪装成一个从库通过 binlog 获取原表上的增量变更应用到影子表上。这样既不影响原表的写入路径也没有触发器带来的写放大。代价是硬性环境要求binlog 必须开启且格式为 ROWbinlog_row_image必须是 FULL执行账号需要有读取 binlog 的权限。它同样要求表有主键或唯一键。在从库上做预演、精确的限流配置--max-lag-millis、--max-load、--throttle-control-replicas是它比 pt-osc 细腻的地方。2.4 三张表对比照着选就行维度直接 ALTERINPLACE/INSTANTpt-oscgh-ost核心原理重建聚簇索引 / 元数据修改影子表 触发器 分批拷贝影子表 消费 binlog对原表写入影响在线日志缓冲并发 DML重写占 IO触发器带来写放大无额外写放大环境要求版本支持即可必须有主键/唯一键无冲突触发器必须有主键/唯一键binlogROWFULL适用场景小表低峰或 8.0 走 INSTANT千万级大表日常首选高频写表、对写放大敏感主要风险在线日志溢出、MDL 排队触发器冲突、切换瞬间锁环境不满足直接无法启动我选方案的顺序很固定8.0 先试 INSTANT不行就看表有没有唯一键和现有触发器高频写表优先 gh-ost其余用 pt-osc如果两个工具的环境条件都不满足那就老实评估停机窗口。3. 动手前的检查清单别等执行到一半才发现没退路3.1 磁盘容量是硬指标任何影子表方案都意味着需要额外的磁盘空间。先搞清楚表当前占多大再决定留多少余量SELECT table_name, ROUND((data_length index_length) / 1024 / 1024, 2) AS total_mb, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(index_length / 1024 / 1024, 2) AS index_mb FROM information_schema.tables WHERE table_schema your_db AND table_name orders;执行期间影子表占一份空间RENAME之后老表还占一份空间binlog 因为 DDL 和增量事件也会变大。保守起见我要求变更前剩余磁盘空间至少是当前表大小的 2 倍。我的习惯是变更期间每 5 分钟手动看一眼df -h或者提前挂一个磁盘空间监控告警。如果是走 8.0 的 INSTANT这块基本不用操心。3.2 藏在表结构里的地雷触发器、外键、字符集、唯一键在真正执行之前把这些事全部验证一遍主键/唯一键pt-osc 和 gh-ost 都必须靠唯一键来切分 chunk没有就直接报错。如果表确实没有任何唯一键先单独立项补一个别想绕过。已有触发器pt-osc 在探测到表上已有触发器时会直接拒绝执行。这不是 bug是保护机制因为触发器嵌套会让数据一致性变得不可控。外键两个工具处理外键关系都很麻烦参数不同、行为不同。最稳妥的做法是变更前先确认这张表是否被外键关联有关联就评估拆掉或者改用官方 online DDL 在低峰执行。字符集与排序规则新列尽量和表内同类型列保持一致。VARCHAR 列字符集不一致在 JOIN 时容易触发隐式转换把本该走索引的查询打成全表扫描。默认值语义新增列如果是NOT NULL且无默认值已有行会被回填隐式默认值字符串填数字填0。如果业务预期是新增列代表未设置这个回填就是一次数据事故。我一般优先写成NULL DEFAULT NULL或者显式给出有业务含义的 DEFAULT。行大小上限InnoDB 单行有 65535 字节的限制表已经很宽的时候再加列可能直接失败。TEXT/BLOB 大字段会让每次 chunk 拷贝更慢评估耗时时要多留余量。3.3 复制状态与窗口评估工具跑起来之后主从延迟会明显波动所以动手前必须确认复制链路是健康的SHOW SLAVE STATUS\G -- 重点看三项Slave_IO_Running、Slave_SQL_Running、Seconds_Behind_Master多个从库就逐个确认任何一个从库上有延迟或异常都不适合在这个时间点做变更。窗口怎么选不能拍脑袋说凌晨没人。我通常是拉最近一周的 QPS 曲线把低谷时段挑出来同时查一下有没有定时统计、归档任务撞在同一时段。和业务方提前打好招呼变更期间如果有问题应用侧要有降级开关能先摘流量。3.4 回滚预案新增字段也要留后路一个容易忽略的事实加完字段后想回滚意味着要再跑一次DROP COLUMN这同样是一次全表重写根本做不到秒回。所以回滚预案的核心不是期望删得快而是万一有问题能把旧数据快速顶上。我的标准做法是云上实例就先打个磁盘快照这是成本最低、最可靠的兜底。pt-osc 执行时加--no-drop-old-tablegh-ost 不加--ok-to-drop-table把老表保留 24 小时。变更前把SHOW CREATE TABLE的输出和当前行数记下来作为变更后的对账基线。4. pt-osc 全流程实操一条命令背后的每个环节4.1 安装清单与最小权限pt-osc 在 percona-toolkit 包里装好即可# Debian/Ubuntu 系 sudo apt install percona-toolkit # CentOS/RHEL 系 sudo yum install percona-toolkit账号权限不用给超级管理员但要覆盖工具的全部操作GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, TRIGGER ON your_db.* TO ddl_worker%;另外建议补上PROCESS和REPLICATION CLIENT权限方便工具发现从库并检查延迟。如果账号连不到从库--max-lag的检查可能直接失效等于少了一层保护。4.2 核心参数逐个拆解--alter真正要执行的 DDL 变更语句注意不要包含ALTER TABLE前缀。--chunk-size每个批次拷贝的行数默认 1000。表行宽、有 TEXT/BLOB 时调小到 500 更稳。--max-lag主从延迟超过多少秒就暂停拷贝默认 1 秒偏保守我一般给 5~10 秒避免频繁暂停拖长整体时间。--max-load实例负载超过阈值暂停拷贝低于阈值恢复示例Threads_running60。--critical-load超过阈值直接中止操作示例Threads_running100按实例核数和历史峰值的 70% 估算。--check-interval检查负载和延迟的间隔默认 1 秒。--dry-run只做校验不真正执行第一次跑必加。--execute真正执行变更。--no-drop-old-table切换后保留原表重命名为_old后缀我永远带着它。--recursion-method从库发现方式默认走 processlist复杂拓扑可以用 dsn 方式。4.3 先干跑dry-run 与小表测速正式动生产之前至少做两层验证。第一层是 dry-runpt-online-schema-change \ --host10.0.0.1 --userddl_worker --passwordxxx \ --alterADD COLUMN user_tag VARCHAR(32) NULL DEFAULT NULL COMMENT 用户标签 \ Dyour_db,torders --dry-run这一步会校验 SQL 合法性、表结构、触发器、唯一键等把潜在问题全部暴露出来。第二层是测速在一台克隆实例或从库上把 orders 表按主键前 10% 的行复制成orders_test跑一遍同样的变更计时。比如 10% 的数据用了 3 分钟那全表大约 30 分钟再乘 1.5 留出余量用这个时间去申请变更窗口。4.4 正式执行与实时盯盘确认没有问题后把 dry-run 换成 executept-online-schema-change \ --host10.0.0.1 --userddl_worker --passwordxxx \ --alterADD COLUMN user_tag VARCHAR(32) NULL DEFAULT NULL COMMENT 用户标签 \ --chunk-size2000 --max-lag10 \ --critical-load Threads_running100 --max-load Threads_running60 \ --no-drop-old-table --execute \ Dyour_db,torders执行期间我不会只盯着终端而是开四个监控窗口SHOW PROCESSLIST看 chunk 的 SELECT/INSERT 是否正常推进有没有卡在 Waiting for table metadata lock。df -h每 5 分钟确认磁盘余量没有异常下降。SHOW SLAVE STATUS\G看从库延迟是否在--max-lag控制范围内。工具自身输出的Copying your_db.orders: 40% 05:30 remain一类进度信息作为整体节奏的参考。如果中途因为--critical-load触发而中止别急着重跑。先把残留清干净再定位原因DROP TABLE IF EXISTS your_db._orders_new; DROP TRIGGER IF EXISTS your_db.pt_osc_your_db_orders_ins; DROP TRIGGER IF EXISTS your_db.pt_osc_your_db_orders_upd; DROP TRIGGER IF EXISTS your_db.pt_osc_your_db_orders_del;清完后看是参数阈值给太低还是真的撞上了业务高峰调整后再执行。4.5 变更后的收尾检查RENAME完成不等于流程结束。我每次都会做四件事SHOW CREATE TABLE orders确认新字段在且注释、默认值、字符集正确。SHOW TRIGGERS确认工具创建的三个触发器已经被清理。行数对账拿变更前记录的行数快照和现在的COUNT(*)对比有差异说明拷贝丢行必须立刻介入。抽查几行数据看新列的默认值是否符合业务预期。老表先留着24 小时内不要动。确认业务稳定、代码发版无误之后再在低峰期DROP TABLE orders_old。别小看这个 drop大表删除在 8.0 里也可能在后台持续一段时间同样要避开高峰。5. 踩坑实录四个翻车现场与操作铁律5.1 已有触发器pt-osc 直接拒单第一次跑 pt-osc 的时候工具在预检阶段就直接退出提示表上已经有触发器。当时那张表确实挂着两个业务触发器pt-osc 认为它无法安全地叠加自己的触发器。解决方案有两种评估触发器是否可以下线如果必须保留就改用 gh-ost它不依赖触发器所以不受影响但前提是 binlog 格式满足要求。5.2 表没有主键/唯一键chunk 无从切起另一个常见报错是找不到主键或唯一键。工具要靠它切分 chunk没有就直接罢工。表本身没有唯一键的问题很难绕过去我的处理是先在低峰期给表补一个合适的唯一键本身也是一次 DDL单独排计划然后再做加字段。硬着头皮让工具硬跑只会换来一个执行到一半的失败现场。5.3 binlog 不是 ROWgh-ost 直接撂挑子gh-ost 对环境很敏感binlog 不是 ROW 或者binlog_row_image不是 FULL它会直接报错拒绝启动。遇到这种情况先执行SHOW VARIABLES LIKE binlog_format和SHOW VARIABLES LIKE binlog_row_image确认。调整这两个参数会影响整个实例的复制行为不能为了一个变更随意改全局配置。如果环境确实不支持就退回 pt-osc或者在维护窗口统一整改 binlog 配置。5.4 直接 ALTER 时在线日志爆掉有一次我在低峰期用官方 online DDL 给一张中等大小的表加索引结果并发写入稍微一多就报出DB_ONLINE_LOG_TOO_BIG。原因就是 1.2 里说的 128MB 在线日志被并发 DML 撑爆。临时解法是调大参数SET GLOBAL innodb_online_alter_log_max_size 1073741824;但治本的方法还是错峰或者干脆用 pt-osc/gh-ost。它们分批拷贝不依赖这个在线日志对并发的容忍度高得多。这也是我在千万级大表场景下不推荐直接 ALTER 的重要原因。5.5 我自己的几条铁律版本优先8.0.12 以上先试ALGORITHMINSTANT能省掉后面所有麻烦。工具不是免死金牌上工具之前该做的预检一项都不能少尤其是磁盘、唯一键、复制状态。磁盘按 2 倍预留变更中每隔几分钟看一眼磁盘空间耗尽比锁等待更难收场。新列允许 NULL 或显式默认值不要把数据语义交给隐式回填去赌。老表保留 24 小时这 24 小时内谁喊回滚都有救过了统一清理。变更当发布有窗口、有监控、有通知、有预案做完有复盘记录。最后分享一个我每次都会做的小事加列前把information_schema.tables里该表的行数、data_length、index_length存到变更记录里变更后拿COUNT(*)和空间量级对一遍账行数和空间都能对上基本可以确认影子表拷贝没丢行。数据库里的坑大多是重复的每次大表变更完把耗时、卡点、磁盘峰值简单记到文档里手册越写越厚以后再遇到千万级大表新增字段就真的只是按流程走一遍的事。