做过一次千万级订单表加字段之后我彻底改掉了直接执行ALTER TABLE的习惯。当时业务方提需求说得很轻巧——“就在订单表加个备注列而已”但真把那条SQL丢到生产库上跑DML队列瞬间排起长队慢查询监控先报警紧接着连接数被打满。那次教训让我把在线DDL的原理、第三方工具方案、各种边界情况全部啃了一遍也才有了下面这套尽量不锁表的完整打法。如果你手里也有大表加字段类似的活这篇文章应该能帮你少走不少弯路。1. 一个简单的ALTER TABLE为什么能让千万级订单表卡死半小时先说结论加字段这个操作本身并不复杂复杂的是MySQL如何把它落到一张已经堆了几千万行数据的物理表上。搞清楚这一点你才知道哪些环节会锁、锁多久、能不能绕开。1.1 锁表到底在锁什么MDL锁与COPY算法的本质MySQL锁的表现形式很多但对一张千万级订单表执行ALTER TABLE ADD COLUMN时最要命的是两类锁Metadata Lock元数据锁简称MDLMySQL Server层为了保证表结构一致而加的锁。执行DDL期间该表的所有DML操作都要等待MDL释放。InnoDB层面的表锁/行锁当DDL需要重建表时InnoDB在复制数据阶段会对原表加排他锁阻塞所有写入和更新操作。这里有个很关键的点MDL锁早在MySQL 5.5就有了目的是防止DML在DDL执行过程中读写到结构不一致的数据。简单理解MDL就是一个修路期间只准行人走、不准车辆进的临时管制——路修完了才放行。所以哪怕InnoDB层面已经支持了在线DDLMDL切换的瞬间如果拿不到锁依然会把后面所有请求堵住。至于COPY算法它是最原始也最直观的方式把整张表的数据一行行拷到一张新表拷完之后用新表替换旧表。假设订单表有2000万行、每行平均1KB的数据这就意味着要发生2000万次IO写入。在普通云盘上这种复制速度慢得让人抓狂几十GB的表跑三四十分钟是常态。这个过程你基本动不了其他东西。1.2 在线DDL的隐藏代价5.6/5.7/8.0各版本的真实表现很多人听到在线DDL就以为不锁表、可以随便执行这是最大的误解。MySQL从5.6开始引入了ALGORITHM和LOCK参数但这个在线的程度是有条件的操作类型ALGORITHM是否允许DML是否需要重建表典型适用版本加普通列放在表尾INSTANT是否MySQL 8.0.12加普通列非表尾INPLACE是否5.6/5.7会重建5.6/8.0加索引INPLACE是否5.6修改列类型COPY否是全部版本改字符集COPY否是全部版本注意看表里那一列是否允许DML。MySQL 5.6/5.7针对加列这种操作官方文档说是INPLACE可以支持并发DML但在实际操作中它会先在内部做一次全表重建只是在重建期间允许一部分读请求进来。真正执行的时候你依然能看到Copy to tmp table这样的状态——这是在做全量数据拷贝只是你这个会话没把DML完全堵死而已。到了MySQL 8.0事情才有点改观。8.0.12之后引入了INSTANT算法只要新列加在表的末尾、不涉及字段顺序调整、没有加索引它可以在秒级完成只修改元数据不触碰数据文件。但前提是你得先升级到8.0而且用的还是MySQL官方版。如果公司还在跑5.7这个红利就吃不到。1.3 一张千万级订单表的DDL耗时估算以及为什么就加一列会引爆线上有人会觉得加个字段而已怎么可能半小时我给你算笔账。假设订单表t_orders有1800万行数据平均行长1.5KB那表的大小大约在27GB左右。这里的行长包含所有字段的字节数以及InnoDB内部的一些开销。如果走COPY算法全量重建机械盘或普通SSD的写入吞吐按200MB/s算27GB数据复制一遍需要约138秒也就是2分多钟但InnoDB重建不只是复制还要构建索引、更新数据字典碎片实际效率通常是理论值的1/3到1/2再加上在线DDL在复制过程中会不断写入undo日志、redo日志磁盘IO又被打薄。所以一台IOPS不到1000的普通云主机上27GB的表重建跑个15到30分钟一点都不夸张。而在这半小时里所有针对这张表的INSERT/UPSERT请求都在等MDL锁释放连接池的活跃连接全部堆积很快连接数打满接着就是雪崩式的超时和报错。这就是为什么千万级订单表新增字段从来不是一个SQL问题而是一个容量规划问题、一个变更管理问题、一个高可用问题。你不是在改一列数据你是在动整张表的物理存储结构。2. 不锁表加字段的三条路线在线DDL、pt-osc、gh-ost到底怎么选在动手之前先把可选方案摊开来看。不同公司的基础设施、数据库版本、业务容忍度不一样选型结果差别很大千万别照抄别人的命令。2.1 路线一低峰期直接ALTER TABLE——什么时候能用如果你的订单表就一个几十万行的中小表或者你已经升级到MySQL 8.0且新列放在表尾那么直接执行一条带上显式参数的ALTER TABLE是最省事的做法。真的不要为了炫技把所有表都搞成pt-osc杀鸡用牛刀还容易引入不必要的风险。举一个可以直接跑的场景ALTER TABLE t_orders ADD COLUMN user_remark VARCHAR(255) NULL DEFAULT NULL COMMENT 用户备注 AFTER id, ALGORITHMINSTANT, LOCKNONE;重点是显式指定ALGORITHMINSTANT和LOCKNONE让MySQL明确知道你的意图。如果实例版本不支持INSTANT或者表结构已经不满足INSTANT条件比如表上存在全文索引或某些特定列类型MySQL会直接报错而不是悄悄降级。让它报错总比让它锁你半小时强。那什么情况下低峰期直接ALTER变得不现实我归纳三条表大小超过50GB重建时间无法压进业务变更窗口业务7x24小时在线根本没有低峰期这一说主从架构下直接在主库执行ALTER TABLEDDL会同步阻塞从库的SQL线程造成主从延迟飚升。尤其第三条很多人会忽略。你以为在主库执行DDL只有主库忙实际上在binlog复制模式下从库重放DDL时同样要走一遍表重建从库的复制会卡很久导致主从延迟不可控。高延迟期间如果主库突然宕机你的恢复RPO/RTO会很难看。所以大表DDL有时候不是让人害怕锁主库而是害怕拖垮整个复制链路。2.2 路线二pt-online-schema-change——靠触发器同步增量pt-osc是Percona Toolkit里最出名的工具之一线上大表DDL的第一选择。我早期给订单表加字段基本都是靠它活下来。它的核心思路是影子表触发器根据原表结构创建一张新表在新表上执行你要做的ALTER TABLE在原表上创建三个触发器分别捕获INSERT、UPDATE、DELETE把增量变更实时同步到新表按主键范围分批把原表历史数据拷贝到新表每批之间sleep一小段时间降低对磁盘IO的冲击所有数据拷贝完毕后对原表加锁做最后一次增量同步然后执行RENAME TABLE原子地切换原表和新表删除原表和触发器。这个方案的最大优势是原表在整个过程中一直是可读可写的DML操作被触发器转发一份到新表业务无感知。代价则是触发器带来了双写放大效应——原来是一条写入现在是原表新表各写一份磁盘IO和CPU开销都会上升。触发器本身也可能成为性能瓶颈。我之前在一张高频更新的订单表上用pt-oscUPDATE语句多的时候触发器里的逻辑会拖慢主线事务高峰期延迟感特别明显。所以用pt-osc必须配合流量控制和观察窗口这个后面实操部分我详细讲。2.3 路线三gh-ost——无触发器的Binlog回放方案gh-ost的全称是GitHubs Online Schema Migration是GitHub开源的无触发器在线DDL工具。和pt-osc最大的不同在于它不依赖触发器而是通过解析Binlog来捕获增量变更。它的数据流是生成一张影子表执行结构变更从主库或从库拉取Binary Log解析出所有针对原表的变更事件在拷贝历史数据的同时把Binlog增量事件持续回放到影子表上最后申请短暂的锁切换表名。无触发器意味着没有双写放大对原表性能的干扰更小。但这里有一个硬性门槛binlog必须开启且格式为ROW。如果你们的实例因为历史原因用的是binlog_formatSTATEMENT或者MIXEDgh-ost是玩不转的。另外gh-ost默认会读写Binlog服务对Binlog的存储和网络都有额外压力。我见过有人在主库网络带宽比较紧张的环境里强上gh-ost结果Binlog拉取速度跟不上产生速度工具自己报错退出。所以它更适合对Binlog体系有良好基础的环境。2.4 三条路线的选型对比风险、速度、依赖条件直接给你一张对比表照着选就行维度直接ALTER TABLEpt-oscgh-ost是否需要重建表是8.0 INSTANT除外是是是否阻塞写操作会短则分钟级长则半小时否否额外写入放大无有触发器双写无依赖条件版本支持INSTANT/INPLACE无强依赖binlog_formatROW对主库压力高重建IO密集中等受chunk-size调节中低受Sleep/threads调节主从延迟风险高中中推荐场景表小/8.0版本/可空窗5.6/5.7大表通用方案对触发器敏感的环境我的习惯是5.6/5.7的千万级大表一律走pt-osc把--max-lag和--critical-load调保守一点8.0环境优先考察INSTANT条件gh-ost用在那些触发器、或者触发器会拖累业务的表上。工具是死的你才是那个做决策的人。3. 实战用pt-online-schema-change给千万级订单表加列的全流程下面这段以一张真实的订单表为例跑一遍完整的pt-osc流程。表名用t_orders目标是在status字段后面加一个user_remark列。3.1 动手前的检查清单磁盘、主从、超时参数一个都不能少我在生产环境执行任何在线DDL之前都会先跑一遍下面的检查项缺一个都不敢动手。这不是流程化表演每一个检查项背后都有真实的事故磁盘空间检查数据目录的剩余空间至少要有目标表当前大小的1.5倍。pt-osc会创建影子表影子表在拷贝过程中不断变大峰值时几乎等于原表大小同时Binlog、临时文件也在膨胀。我在4.1会具体讲磁盘怎么爆的。主从状态确认所有从库SQL线程正常Seconds_Behind_Master接近0。如果主从本来就有延迟一遍执行下去延迟只会更夸张而且影子表的变更也会传到从库加重负担。binlog与配置binlog_formatRBT实际是ROW、binlog_row_imageFULL。pt-osc创建触发器同步增量数据如果Row格式没有记录全字段某些场景下同步不完整。外键与触发器用SHOW CREATE TABLE看是否已经有触发器用information_schema.KEY_COLUMN_USAGE查外键依赖。pt-osc不是没办法处理外键但默认遇到就直接拒绝需要额外参数风险也高。有外键的表我通常建议走gh-ost或者先和外键业务方确认。超时参数会话级别的lock_wait_timeout建议改大。原表如果正被一个长事务占用MDL锁拿不到pt-osc会在等待超时后直接放弃。我一般设成3600秒起步。检查下大概长这样-- 1. 看表结构和触发器 SHOW CREATE TABLE t_orders; SELECT TRIGGER_NAME FROM information_schema.TRIGGERS WHERE EVENT_OBJECT_TABLE t_orders; -- 2. 看外键 SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME t_orders; -- 3. 看磁盘 df -h /var/lib/mysql这套检查跑完基本能过滤掉80%的潜在事故。3.2 命令怎么写核心参数逐一拆解检查没问题就开始写执行命令。我给你一条完整的生产可用命令再逐段解释关键参数pt-online-schema-change \ --useradmin \ --passwordxxxxx \ --host127.0.0.1 \ --alterADD COLUMN user_remark VARCHAR(255) NULL DEFAULT NULL COMMENT 用户备注 AFTER status \ --charsetutf8mb4 \ --max-lag10 \ --check-interval5 \ --chunk-size1000 \ --critical-loadThreads_running50 \ --max-loadThreads_running30 \ --recursion-methodhostname \ --no-check-replication-filters \ --execute \ Dtestdb,tt_orders几个核心参数的作用--alter只写从当前结构演进到目标结构的那部分pt-osc会在影子表上执行这句SQL。注意不要带ALTER TABLE前缀也不要加ALGORITHM参数。--chunk-size1000每批拷贝的行数。行数太少会导致拷贝进度太慢行数太多单批事务太大锁持有时间变长。1000是一个比较稳的起步值如果IO好可以调到2000-5000如果IO差就降。--max-lag10压测从库的复制延迟上限超过10秒工具会自动放慢拷贝节奏。这个参数是给自己留后路的必须设。--critical-loadThreads_running50当数据库中活跃线程数超过50时直接暂停工具防止拖垮业务。很多DBA会忽略我建议一定写。--recursion-methodhostnamept-osc需要探测从库列表来控制延迟这里按照hostname特征匹配从库。如果从库用了不同域名规则需要改成processlist或dsn方式否则探测不到从库--max-lag就是摆设。--no-check-replication-filters跳过复制过滤器的检查这个参数风险略高只有在你自己确认主从复制没有过滤规则时才建议加。--execute正式执行。不加这个参数的话工具只做安全检查并打印将要执行的SQL不会真的跑相当于dry-run模式。我第一次跑这个工具时一定先dry-run一遍看看打印出来的SQL和预想是否一致。这里要特别说一句第一次用pt-osc建议先在公司预发环境或者克隆的测试实例上跑一遍。工具本身的打印信息很详细但有些错误只有真跑一遍才会暴露比如触发器权限不足、从库探测失败、字符集冲突等。你不可能在生产表上边试错边调整那是在拿订单数据开玩笑。3.3 执行过程真实长什么样Copy to tmp table与RENAME切换执行之后你会在会话里看到类似这样的日志我来逐段解释它代表什么Operation, tries, wait, tries, wait Copying rows: 1000, 1, 2, 3...工具会反复循环读取一批主键范围 - 拷贝到影子表 - sleep一小段时间 - 读取下一批直到全部拷贝完成。期间你用SHOW PROCESSLIST去观察会看到两种典型状态Copy to tmp table这是InnoDB在执行批量插入影子表的物理写入阶段IO消耗最大、最容易让磁盘报警的阶段。Waiting for table metadata lock这是原表上当前有长事务导致MDL锁获取不到的情况。看到这个状态别慌先查information_schema.INNODB_TRX看哪个事务在持锁评估是等还是终止那个长事务。拷贝完成之后pt-osc会进入最关键的切换阶段在原表上加一个短暂的表级写锁比较原表和影子表当前的数据偏移把最后一批增量同步过去执行RENAME TABLE t_orders TO t_orders_old, t_orders_new TO t_orders这一步是原子的删除三个触发器完成。从RENAME执行开始到结束整体耗时就几十毫秒。这是整套方案里唯一一个真正的锁表窗口但因为它极其短暂业务基本上无感知。只要确保这几十毫秒内没有大批量写操作正好砸上去就不会造成问题。3.4 执行过程中的流量闸门如何控制节奏而不影响订单写入很多人以为pt-osc只有起始和结束两个状态中间完全交给工具自己跑这是错误的认知。工具内部有非常细的控制逻辑你要学会用这些逻辑和业务写入打配合。我用的一个经典组合是把--chunk-size和--max-load联动起来。假设业务高峰期每秒写入约5000条订单我会把--chunk-size稳定在800到1200之间这样单次拷贝事务的耗时控制在30到50ms触发器转发增量数据造成的额外延迟几乎不可感知。如果观察到业务的P99写入延迟出现了跳动我会手动把--chunk-size降到300到500同时增大--sleep参数让每批之间的静默时间更长。-- 如果业务流量上来了可以临时降低拷贝速度 --sleep1 --chunk-size500如果该表上有大量UPDATE类型的操作触发器执行路径会更长这时候我会把--chunk-size压到200宁可迁移跑慢一点也不要让订单写入的RT出现毛刺。整个迁移跑个3小时还是5小时对你的价值远远不如存量用户无感知这一点重要。4. 纸上谈兵都是好的实测中的坑与完整排查链路命令背得再熟真上生产还是会遇到各种意外。下面这几个坑我都踩过每条我都会把现象 - 排查 - 修复的完整链路写出来你遇到类似的情况可以照着查。4.1 第一坑跑着跑着磁盘满了为什么影子表会越吃越胖现象pt-osc执行到40%左右磁盘使用率从60%直接飙到93%然后工具报No space left on device退出。排查链路先用df -h看数据目录所在分区的使用率确认是否真的写满了用du -sh /var/lib/mysql/testdb/t_orders_new.ibd查影子表的物理文件大小你会发现它几乎和原表一样大继续查du -sh /var/lib/mysql/undo_*如果看到undo日志也在膨胀那说明拷贝期间有大量事务反复更新了同一批行导致undo版本链暴涨。根因影子表是完全复制原表结构的新表拷贝过程中体积接近原表。如果原表27GB你至少得预留27GB的额外空间。再加上undo日志、Binlog的增量预留空间按原表1.5到2倍来规划一点都不夸张。修复方案如果磁盘还差一口气先清理无用的Binlog和慢日志占位见缝插针调小--chunk-size减少单批事务量能够显著降低redo/undo的写入速率如果实在撑不住允许工具停在那里等运维扩容扩容完成后再用相同命令继续跑。pt-osc不是一次性的事务它支持断点续跑工具会基于影子表当前的状态继续拷贝。这里提醒一句跑大表迁移前把数据目录所在磁盘监控的告警阈值从90%调到70%。等你看到90%告警再处理手慢一点就等于眼睁睁看着目标表被写满那种无力感我不想你再体验。4.2 第二坑主从延迟持续拉高从库复制线程追不上现象pt-osc在跑主库业务也没明显抖动但SHOW SLAVE STATUS里Seconds_Behind_Master从0一路涨到300秒甚至更高。排查链路先区分是哪个环节慢了用SHOW SLAVE STATUS\G看SQL_Running_State字段如果显示Waiting for dependent transaction to commit或者Copy to tmp table on a replica基本可以判定是影子表的DML在从库上重放时也需要写一份加重了从库IO负担看从库的top确认磁盘IO是否已经在95%以上。触发器在从库一样生效所以主库的每条变更到从库都要执行两遍这个放大效应在主从环境中被进一步放大确认是不是--max-lag没有真正生效。如果--recursion-methodhostname匹配不到从库工具就探测不到延迟等于这台从库的延迟没有进入控制闭环。修复方案确认--recursion-methodhostname配置正确确保工具可以感知从库延迟降低--chunk-size到500以下给从库的SQL线程喘息空间调大--max-lag的容忍范围让工具在延迟达到之前就主动放慢甚至暂停拷贝如果延迟已经很高且追不上考虑直接kill掉pt-osc进程让主库先行恢复之后用同一个命令重新续跑。影子表还在不会被删除重启后会接着拷贝。记住一个原则分配给迁移的IO资源是有限的它会和正常业务抢资源。你的迁移节奏要把从库延迟当作第一信号源而不是等到业务告警了才去处理。4.3 第三坑触发器冲突与外键依赖工具直接拒绝执行现象pt-osc一启动就报类似于下面这样的错误Table testdb.t_orders has triggers on it. The --preserve-triggers option must be specified.或者Child table(s) referencing this table: t_order_items. Foreign key constraints may be in the way.排查链路用SHOW TRIGGERS LIKE t_orders确认表上是否已经有业务触发器用information_schema.KEY_COLUMN_USAGE查清楚外键来自哪张表、是哪种级别CASCADE/RESTRICT判断这些触发器/外键是否是迁移链路上的硬依赖。这里掉进过一个比较深的坑订单表上有一个记操作日志的触发器业务依赖它把每次订单状态变更写入审计表。pt-osc默认不允许有触发器的表做迁移因为它自己也要建触发器两个触发器名字可能冲突。我当时图省事直接在命令里加了--preserve-triggers结果迁移完成后原触发器和pt-osc创建的触发器叠加在一起业务日志双写了一份审计数据直接膨胀。正确做法是先和应用团队确认触发器的功能看能否在迁移窗口内先手工接管然后删除原触发器让pt-osc带着自己的触发器完成迁移迁移完成后再重建业务触发器。外键的处理逻辑类似。如果订单表被t_order_items通过order_id外键引用并且存在级联删除那么核心风险在于RENAME TABLE瞬间外键约束会被短暂断开因为这相当于原表被改了个名子表的外键定义会临时找不到父表。pt-osc提供了--alter-foreign-keys-methodauto来控制这个处理流程但对高规格外键依赖的表我再怎么也不太推荐直接上工具更稳妥的选择是先评估外键是否可以去掉重建或者换gh-ost来跑。4.4 迁移完成后的验证不是RENAME成功就万事大吉很多人以为看到RENAME TABLE跑完就结束了其实真正要花的功夫在验证阶段。我做在线DDL的验证分三层一层比一层严格第一层结构验证。对比新旧表结构确认新增列、默认值、注释、索引都符合预期。SHOW CREATE TABLE t_orders;第二层数据行数验证。统计新旧表的行数是否一致但千万级表用COUNT(*)会非常慢。正确做法是取各自表的自增ID范围和information_schema.TABLES中的TABLE_ROWS估算值或者用CHECKSUM TABLE做快速比对。pt-osc在切换前已经做过一次基于主键范围的行数校验但迁移后依然建议人工抽查几段关键数据。第三层灰度放量验证。别让全量流量立刻打到新表上先让一部分读流量或者内部报表先查几天确认无查询异常、无慢sql、无业务方反馈后再把老表t_orders_old清理掉。清理老表也是门学问不要直接DROP TABLE一把梭它占着几十GB磁盘如果删除中途崩溃重启时InnoDB要回滚那个大DROP操作极其难受。你可以用-- 确认业务无依赖后分成两步先改个名再慢慢删 RENAME TABLE t_orders_old TO t_orders_dropme; -- 如果你用的是5.6以上可以开instant drop table把删除变成元数据级 ALTER TABLE t_orders_dropme FLUSH, DROP TABLE t_orders_dropme;如果磁盘空间紧张也可以把老表RENAME到其他库再在低峰期执行DROP。因为老表已经不再有任何引用拖几天再删也不影响业务但能让你有个后悔药可以吃。跑完这次订单表加字段我最大的体会是千万级表上的任何DDL都不应该当成一条SQL去考虑而要当作一个完整的小项目——有选型论证、有前置检查、有执行窗口、有验证回滚。工具只是执行载体真正让线上安稳度过的是你对锁原理的理解、对参数的精调、以及在意外来临时能不能冷静地按链路排查。最后分享一个小技巧每次做这类大表变更我都会把pt-online-schema-change的dry-run输出完整保存下来连同当天的主从状态、磁盘用量一起归档。半年后再遇到类似需求时翻出来对照很多参数可以直接复用。这个习惯帮我省了不少事也让你在下次变更时不用从零开始踩坑。