上周五晚上我接到一个需求乍一看毫无技术含量给两张千万级数据量的表各加一个新字段做一次渠道信息回填。我当时第一反应就是——写一行 ALTER TABLE跑完收工。结果这句话差点让我把一整周交付的安心感全赔进去。先是 ALTER 卡住然后是锁等待超时再然后监控里冒出一堆 “Waiting for table metadata lock” 的会话整张表的读写像堵车一样越积越多。这篇文章就把这次踩坑的完整过程写出来包括直接 ALTER 为什么危险、pt-online-schema-change 和 gh-ost 这类在线改表工具的原理和用法以及我当时是怎么在 tablea 和 tableb 两张表上做字段添加和数据回填的。适合正在维护 MySQL 生产库、准备对大数据量表做结构变更的同学参考尤其是那些用过 ALTER TABLE 但没被坑过的人。1. 先复盘那次“一行 SQL 就能解决”的现场事故1.1 事发前我了解到的背景这次需求本身不复杂。业务方提出需要把不同业务库里的两张表关联起来做统计。tablea 在订单库里可以理解为源表记录着每笔业务的渠道来源tableb 在分析库里是目标表攒了大概 1200 万行后续报表查询都要从它上面捞数据。现在需要在 tableb 上新增一个 channel_id 字段用来记录渠道来源并且从 tablea 里把历史数据回填进去。我当时想的是这不就是先 ALTER TABLE 加个字段再 UPDATE 回填一下嘛MySQL 我熟。于是直接在测试环境跑了一遍ALTER TABLE tableb ADD COLUMN channel_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 来源渠道ID;测试库里 tableb 只有几十万行秒级完成没有任何问题。于是我觉得生产环境也可以照搬。等到业务低峰期我把这条 SQL 粘到了生产库的窗口里执行。结果不到 30 秒我就发现不对劲了。这条 ALTER 一直没有返回然后监控平台开始报锁等待show processlist 里出现了一大堆会话卡在 “Waiting for table metadata lock” 状态。原本只需要几百毫秒的查询全部积压在那个 ALTER 后面排队整个分析库的读请求都开始变慢。最后我做了个不太体面的决定杀掉那条 ALTER先恢复业务回到工位上查原因。1.2 为什么一条 ALTER 会拖垮一堆查询metadata lock 和 rebuild很多人对 ALTER TABLE 的理解停留在“MySQL 会自动加字段”的层面实际上大表加字段背后有两件容易被忽略的事元数据锁metadata lock简称 MDL和表重建rebuild。先说 metadata lock。MySQL 对表结构变更和 DML 之间是有锁协调的。当执行 ALTER TABLE 时当前会话需要拿到这张表的排他 MDL。如果此时正好有另一个事务在读写这张表而且一直没提交那么 ALTER 就得等着。更麻烦的是MySQL 的 MDL 队列一旦有了等待者后面所有想访问这张表的会话都会被阻塞排队包括普通的 SELECT。我当时生产环境里正好有一个定时任务连到了 tableb事务一直没有提交ALTER 被卡住其他查询也被连带堵住了别人看起来就像数据库挂了其实只是锁链问题。再说表重建。如果是 MySQL 5.6 之前的版本或者操作场景不能被 InnoDB Online DDL 覆盖ALTER TABLE 加字段时 MySQL 会生成一张临时表把原表所有行拷贝过去再重建索引最后完成切换。即使是在 5.7 里很多 ADD COLUMN 操作虽然支持 Online DDL不阻塞读写但内部仍然需要 rebuild 表也就是要复制全表数据并重建索引这本身就会带来巨大的 IO 压力、磁盘空间占用以及对主从复制延迟的影响。所以“ALTER TABLE 加字段就是一瞬间”这个认知在小表上是对的在千万级大表上完全不是。这也是我把这次经历写下来的原因能在一开始就意识到大表 DDL 的高危性后面就不会拿生产环境去试错。2. 大表加字段有哪些“能跑”的方案2.1 直接 ALTER TABLE什么场景能用什么场景千万别用先给一个相对保守的结论直接 ALTER TABLE 并不是完全不能用关键要看表规模和业务容忍度。如果表在百万行以内处于业务低峰期磁盘空间够主从延迟允许并且你能接受一个小规模的锁等待窗口那直接 ALTER 通常没太大问题。我处理过很多 50 万行以内的小表ALTER TABLE 加字段几乎都是秒级完成确实没必要上工具。但如果表超过千万行或者数据库处于 7x24 在线状态又或者读写混合很频繁我建议你先别直接跑 ALTER。原因有三点第一重构表带来的磁盘和 IO 压力非常真实1200 万行、单行均长 800 字节左右的表数据加索引往往超过 10GB重建一次需要大量临时空间第二ALTER 执行期间会产生大量 binlog从库要回放同样的 DDL 和 DML主从延迟可能飙到让人崩溃第三MDL 排队风险随时可能把整个库的查询拖住。MySQL 8.0 之后的版本多了一个 INSTANT 算法如果只是在表末尾加一列并且添加的列有确定的默认值可以做到不重建表就完成 DDL。但实际生产环境里我们加字段经常同时要求加索引或者把新字段加在表的中间位置这种情况下 INSTANT 就不适用了。而且 8.0 的 INSTANT 修改次数也不是无限的每个表可执行的即时列操作次数有限。所以不能总觉得“MySQL 8.0 有了 INSTANT 就可以为所欲为”。2.2 pt-online-schema-change原理和参数在千万级大表上加字段业内最常用的方案之一是 Percona Toolkit 里的 pt-online-schema-change以下简称 pt-osc。我当时实际用的也是它。pt-osc 的原理可以简单概括为四个步骤根据原表结构创建一个结构相同但没有任何数据的新表新表名字通常是_tableb_new。在源表上创建三个触发器分别对应 INSERT、UPDATE、DELETE把在线发生的增量变更同步到新表。按主键分批把原表数据拷贝到新表每批默认 1000 行左右。数据拷贝完成后在很短的时间内执行一次 RENAME TABLE把新表切为正式表原表被替换。这个方案的优点是把“一次性重建全表”分摊成“分批复制数据”业务读写压力就会平滑很多。它不是不占用资源而是不会长时间把表锁死。我当时的执行命令长这样pt-online-schema-change \ --host10.0.0.12 --port3306 \ --userdba --password*** \ Danalysis_db,ttableb \ --alter ADD COLUMN channel_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 来源渠道ID, ADD INDEX idx_channel_id(channel_id) \ --chunk-size1000 \ --max-lag5 \ --critical-loadThreads_running100 \ --max-loadThreads_running50 \ --recursion-methodprocesslist \ --execute几个参数我当时都踩过坑解释一下--chunk-size控制每批拷贝的行数。默认值是 1000如果单行很长或者主键范围很大可以调小到 500减少单次批量查询的锁和执行时间。--max-lag定义从库最大允许的延迟秒数。pt-osc 会主动检查从库回放情况一旦超过阈值就暂停拷贝等从库追平再继续。这个参数特别重要没有它你的主从延迟可能直接爆炸。--critical-load和--max-load设置一个阈值如果数据库线程数过高pt-osc 会暂停甚至中止操作避免把线上实例压垮。--recursion-method用来指定如何发现从库。常见的有 processlist、hosts 等。如果这个参数配错pt-osc 可能找不到从库或者报错退出。2.3 gh-ost用 binlog 换掉触发器除了 pt-oscGitHub 开源的 gh-ost 也是个大表在线改表的利器。它与 pt-osc 最大的区别在于不依赖触发器而是让 gh-ost 自己伪装成一个 MySQL 从库去解析源库的 binlog把增量变更应用到新表。这样做的好处是避免了触发器带来的额外开销尤其是在原表本身已经有触发器的情况下pt-osc 会很容易出问题而 gh-ost 基本不受影响。gh-ost 还支持动态限速可以通过命令临时暂停或调整拷贝速度这个特性在当时那种线上业务不可控的场景里非常实用。gh-ost 的基本用法如下gh-ost \ --host10.0.0.12 --port3306 \ --userdba --password*** \ --databaseanalysis_db \ --tabletableb \ --alterADD COLUMN channel_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 来源渠道ID \ --chunk-size1000 \ --max-lag-millis5000 \ --panic-flag-file/tmp/gh-ost.panic \ --execute如果让我对这两个工具做选型我会这么理解如果表上没有大量触发器团队对 Percona Toolkit 比较熟那就用 pt-osc如果原表触发器很多或者你想在操作过程中有更强的控制能力比如随时暂停、限速那就优先考虑 gh-ost。两者都要求表必须有主键或者唯一键因为分片复制要靠这个键定位范围。如果一个表连主键都没有那就得先把主键补上不然工具根本没法干活。3. 这次迁移的实操过程从检查到上线3.1 先给表做一次“体检”吸取了第一次直接 ALTER 的教训后我决定老老实实按流程走。第一步不是执行 DDL而是先了解 tablea 和 tableb 的真实状态。我查了目标表 tableb 的元数据SELECT table_schema, table_name, engine, table_rows, ROUND(data_length/1024/1024, 2) AS data_mb, ROUND(index_length/1024/1024, 2) AS index_mb, ROUND((data_length index_length)/1024/1024, 2) AS total_mb FROM information_schema.tables WHERE table_schema analysis_db AND table_name tableb;结果让我更确定不能继续用 ALTER 硬刚tableb 有 1230 万行数据文件接近 7GB索引文件接近 3GB加起来 10GB 左右。如果直接 ALTER需要额外再加一份这样的空间来放临时表。这种情况下不仅磁盘压力大IO 也会把业务查询拖慢一大截。紧接着我又检查了表结构、主键、触发器和外键SHOW CREATE TABLE tableb;tableb 有主键 id这是好消息。我又检查了它有没有触发器或外键因为 pt-osc 在执行时对这两类对象会有限制。确认没有触发器之后我才放了点心。然后是长事务检查。前面已经说过ALTER 被 MDL 卡住往往是因为有未提交事务所以必须先把数据库里跑着的长事务找出来SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx ORDER BY trx_started;这一步很重要。我当时就查到有一个定时统计任务连着 analysis_db事务已经跑了十几分钟没提交。这种长事务如果不处理后面无论你用什么工具只要靠 MDL 做结构切换都会卡在那里。3.2 在测试环境先预演估算时间和空间我没有直接在生产上跑而是先搭了一个从备份恢复的测试环境表结构和线上一致数据量也一样。这个预演花了一个多小时但它帮我发现了两个问题。第一磁盘空间。pt-osc 虽然不像 ALTER 那样需要原表拷贝后 rename但它在执行过程中也要创建一张新表新表数据量接近原表所以数据目录需要预留至少“原表数据大小 索引大小”的空间再算上 binlog 增长我当时估算至少需要 25GB 的余量。我检查了一下目标实例的磁盘剩余空间只有 18GB这是个明显风险点。解决办法是先清理了一部分过期 binlog腾出空间再把 clone 出来的临时表占了空间清掉最终把可用空间提到了 40GB 以上。第二执行时间。测试环境跑了一次 pt-osc把 1230 万行数据全部拷贝到新表大约耗时 26 分钟。加上增量回放和新表切换的窗口整体控制在 30 分钟内。这个时间窗口在凌晨两点执行是完全没有问题的。3.3 正式执行选择 pt-osc 并调整参数正式执行前我又做了一层保护把所有可能访问 tableb 的定时任务停掉或者错峰避免出现新的长事务顺手把 select 访问量大的报表任务改到另一个只读实例上读流量切走一部分。这次执行命令和测试环境基本一致只是把--chunk-size从 1000 调到了 500因为 tableb 的单行数据比较宽行数多500 行一批对源库的压力更小也更不容易触发从库延迟。命令如下pt-online-schema-change \ --host10.0.0.12 --port3306 \ --userdba --password*** \ Danalysis_db,ttableb \ --alter ADD COLUMN channel_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 来源渠道ID, ADD INDEX idx_channel_id(channel_id) \ --chunk-size500 \ --max-lag5 \ --critical-loadThreads_running100 \ --max-loadThreads_running50 \ --recursion-methodprocesslist \ --execute执行过程中我单独开了一个窗口盯着 processlistSHOW PROCESSLIST;能看到 pt-osc 在反复执行类似这样的 SQL分批拷贝数据INSERT INTO analysis_db._tableb_new (...) SELECT ... FROM analysis_db.tableb FORCE INDEX(PRIMARY) WHERE ((id ?)) AND ((id ?)) LOCK IN SHARE MODE;这说明它在按主键范围稳步推进。中间有几次从库延迟超过了 5 秒pt-osc 自动暂停了拷贝等到延迟降下来又继续。整个过程没有出现锁等待也没有影响正常读写。大约过了 23 分钟pt-osc 提示完成输出了一段类似日志Copying rows ... took 23m24s Renaming old table ... OK Dropping old table ... OK它最后的 RENAME TABLE 操作是原子性的新表切换只是瞬间完成业务完全无感知。3.4 新增字段成功后回填数据tablea 与 tableb 关联字段加成功后接下来就是回填数据也就是把 tablea 里的渠道信息根据业务关联 ID 更新到 tableb.channel_id。千万级表直接 UPDATE JOIN 是另一个大坑。如果写UPDATE tableb b JOIN order_db.tablea a ON b.biz_id a.biz_id SET b.channel_id a.channel_id;这会在生产环境造成非常大的临时表和锁范围。跨库连接如果两个库在同一个实例还好如果不在同一个实例SQL 都没法这样写。我当时遇到的情况是 tablea 和 tableb 不在同一个实例所以我把更新拆成了两步先从源库把 tablea 的关联结果导出成中间文件再通过 Load Data 导入分析库的临时表最后按主键分批 UPDATE。分批更新的方案是按 tableb 主键范围每批取 5000 行UPDATE tableb b JOIN tmp_channel_map m ON b.biz_id m.biz_id SET b.channel_id m.channel_id WHERE b.id BETWEEN 1000000 AND 2000000;这样每批更新行数有限不会产生超大事务主从延迟也可控。全部回填完成后我抽查了几组数tablea 和 tableb 的主键映射基本都对得上。这一步本身不复杂但它和加字段一起做容易让人在复盘时把问题混淆我后来特意把 DDL 和 DML 分开记录每一步单独留档。4. 从库延迟、磁盘空间、元数据锁三个最容易翻车的点4.1 主从延迟如何评估和兜底大表 DDL 导致主从延迟几乎是必然的原因不复杂主库在执行 DDL 或大批量 DML 时会产生大量 binlog而从库回放这些 binlog 是串行的一旦单库写入压力大回放速度就会跟不上。如果你在从库上有读写分离的报表查询延迟会让报表读到旧数据这时候业务就会来抱怨“数据不对”。我当时用的 pt-osc 已经带了--max-lag参数当从库延迟超过 5 秒时自动暂停拷贝给从库留出追赶时间。除了这个措施我还在执行前临时调整了从库的并行复制线程数。如果你的 MySQL 8.0 版本支持 MTS多线程复制可以适当调大slave_parallel_workers例如从 4 调到 8这样回放 DDL 后产生的临时表 DML 时从库的压力会小一点。判断延迟的命令很简单SHOW SLAVE STATUS\G主要看Seconds_Behind_Master这个值越大说明从库落后越多。生产上如果长期超过 30 秒就说明 DDL 的时间和规模超出了监控兜底范围需要进一步限速或加维护窗口。这里有一个经验分享在跑 pt-osc 或 gh-ost 时不要只盯主库的负载更不要只盯跑批的进度一定要把从库的回放状态和从库磁盘空间一并监控。从库一旦磁盘写满或者复制线程报错中断恢复起来往往比主库故障还麻烦。4.2 磁盘空间别等写满才后悔直接 ALTER 和 pt-osc 都要占用空间。直接 ALTER 需要把整张表复制一份pt-osc 也会先建一张新表同样需要空间。所以动手前必须估算空间。我当时用如下 SQL 对目标库里所有库做个快速盘点SELECT table_schema, table_name, ROUND(data_length/1024/1024/1024, 2) AS data_gb, ROUND(index_length/1024/1024/1024, 2) AS index_gb FROM information_schema.tables ORDER BY data_length DESC LIMIT 20;然后估算需要的额外空间原表大小 原索引大小 执行期间 binlog 增长量。binlog 增长量不好精确计算通常按原表大小的 30% 到 100% 估算比较保守因为 pt-osc 批量 INSERT 和 UPDATE 都会生成 binlog而且 binlog_format 如果是 ROW每条语句产生的日志体积会比语句模式大好几倍。千万表加字段看起来是加了 1 列实际底层可能动的是 10GB 甚至 20GB 的数据。所以动手前我强烈建议执行一次df -h /data/mysql确认数据目录所在分区有富余空间。如果空间不够宁可先清 binlog、归档日志或者挪走几个大文件也不要抱着侥幸心理开跑磁盘写满的结果往往比 DDL 失败难处理得多。4.3 metadata lock怎么提前发现和规避第一次直接 ALTER 失败的直接原因就是 metadata lock。我当时是通过两个地方发现的。第一是SHOW PROCESSLIST能看到大量会话状态是 “Waiting for table metadata lock”第二是查询 sys 库的锁等待视图SELECT * FROM sys.schema_table_lock_waits\G这个视图会列出谁是等待者、谁持有锁、哪个会话阻塞了 DDL。如果能看到一条未提交事务的 trx_started 时间非常久就基本锁定问题了。预防 metadata lock 的方法有几个执行 DDL 前先查information_schema.innodb_trx确认没有长事务。检查是否有mysqldump --single-transaction或其他备份任务还在跑备份过程中持有 MDL也可能导致 ALTER 排队。对核心表执行 DDL 时建议在凌晨低峰期做并且通过LOCK_WAIT_TIMEOUT控制等待时长避免无限排队。如果在业务高峰期不得不做考虑用 gh-ost 或 pt-osc 的同时配置一个执行前检测脚本先查进程列表再锁超时短一点把失败尽早暴露。5. 常见问题速查大表加字段排障实录5.1 高频问题与处理办法问题可能原因处理思路ALTER 一直不返回监控出现大量内存/连接堆积存在未提交长事务或备份任务持有 MDL导致 ALTER 排队先查 information_schema.innodb_trx杀掉长事务后重试再查 sys.schema_table_lock_waits 定位阻塞源头pt-osc 报错提示需要指定主键或唯一键表结构没有主键或唯一键工具无法定位分批范围先做主键补建再执行在线 DDLpt-osc 找从库失败提示 “No slaves found”从库发现方式配置不对或账号权限不足指定 --recursion-methodprocesslist 后重试确认账号有查询复制状态的权限主从延迟飙高大批量 DML 或 DDL 产生了大量 binlog从库回放跟不上调小 chunk-size设置 max-lag必要时推迟到低峰期执行表上已有触发器pt-osc 拒绝执行pt-osc 默认不能和已有触发器共存改造过程会冲突先评估触发器是否可移除或用 gh-ost 替代gh-ost 不依赖触发器磁盘空间不足pt-osc 创建新表需要额外空间binlog 也在增长提前清理 binlog 和日志文件给数据目录留足原表大小加索引大小的 1.5 倍以上新字段默认值导致业务查询变慢字段类型选择不当或者回填数据时 UPDATE 缺少合适索引回填前先建索引回填按主键分批不要一次性更新全表5.2 几个让我印象深刻的教训这次操作让我总结出几个特别想提醒后来者的点都是常规文档里不太好查到的东西。第一加字段时如果确定需要建索引尽量在 DDL 里一起建不要先加字段再单独建索引。等你第一次在线 DDL 跑完再跑第二次 CREATE INDEX等于把大表重建两遍时间成本和风险都翻倍。用 pt-osc 时--alter参数可以直接写成ADD COLUMN ... , ADD INDEX ...一条命令同时完成这也是我当时选择这种写法的原因。第二pt-osc 虽然不阻塞读写但也不是零风险。它会在源表上创建触发器如果源表本身写入量巨大触发器会带来额外开销并且批量拷贝期间源库的 IO 和主从复制压力会明显上升。所以工具能解决锁问题不代表你可以在业务高峰期随便跑。低峰执行永远是最稳妥的选择。第三操作过程一定要留档。我这次执行前把表结构、行数、空间大小、从库延迟、执行命令、执行结果都保存了下来后来复盘的时候非常有帮助。特别是如果加字段后出现异常你能快速判断到底是 DDL 阶段的问题还是回填数据阶段的问题。第四优先级最高的不是“把字段加上去”而是“加字段的过程中不影响业务”。为了这个目标你可以提前切走读流量、停掉定时任务、调小分批大小、增加监控甚至把一个看似简单的需求拆成多个小步骤完成。不要觉得这个过程繁琐等你真正踩过一次 metadata lock 或者磁盘写满的坑就会理解这些步骤的价值。这次之后我再遇到大表加字段已经养成了固定习惯先查表结构、行数、空间、长事务、从库延迟然后再决定用 ALTER、pt-osc 还是 gh-ost。改表这件事永远别拿生产环境去试错先评估再动手才是真正的效率。