企业官网建设流程全解析

上周五晚上我接到一个需求，乍一看毫无技术含量：给两张千万级数据量的表各加一个新字段，做一次渠道信息回填。我当时第一反应就是——写一行 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 的原理可以简单概括为四个步骤：

1. 根据原表结构，创建一个结构相同但没有任何数据的新表，新表名字通常是_tableb_new。
2. 在源表上创建三个触发器，分别对应 INSERT、UPDATE、DELETE，把在线发生的增量变更同步到新表。
3. 按主键分批把原表数据拷贝到新表，每批默认 1000 行左右。
4. 数据拷贝完成后，在很短的时间内执行一次 RENAME TABLE，把新表切为正式表，原表被替换。

这个方案的优点是把“一次性重建全表”分摊成“分批复制数据”，业务读写压力就会平滑很多。它不是不占用资源，而是不会长时间把表锁死。

我当时的执行命令长这样：

pt-online-schema-change \ --host=10.0.0.12 --port=3306 \ --user=dba --password='***' \ D=analysis_db,t=tableb \ --alter "ADD COLUMN channel_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '来源渠道ID', ADD INDEX idx_channel_id(channel_id)" \ --chunk-size=1000 \ --max-lag=5 \ --critical-load="Threads_running=100" \ --max-load="Threads_running=50" \ --recursion-method=processlist \ --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-osc，GitHub 开源的 gh-ost 也是个大表在线改表的利器。它与 pt-osc 最大的区别在于不依赖触发器，而是让 gh-ost 自己伪装成一个 MySQL 从库，去解析源库的 binlog，把增量变更应用到新表。

这样做的好处是避免了触发器带来的额外开销，尤其是在原表本身已经有触发器的情况下，pt-osc 会很容易出问题，而 gh-ost 基本不受影响。gh-ost 还支持动态限速，可以通过命令临时暂停或调整拷贝速度，这个特性在当时那种线上业务不可控的场景里非常实用。

gh-ost 的基本用法如下：

gh-ost \ --host=10.0.0.12 --port=3306 \ --user=dba --password='***' \ --database=analysis_db \ --table=tableb \ --alter="ADD COLUMN channel_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '来源渠道ID'" \ --chunk-size=1000 \ --max-lag-millis=5000 \ --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 \ --host=10.0.0.12 --port=3306 \ --user=dba --password='***' \ D=analysis_db,t=tableb \ --alter "ADD COLUMN channel_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '来源渠道ID', ADD INDEX idx_channel_id(channel_id)" \ --chunk-size=500 \ --max-lag=5 \ --critical-load="Threads_running=100" \ --max-load="Threads_running=50" \ --recursion-method=processlist \ --execute

执行过程中，我单独开了一个窗口盯着 processlist：

SHOW 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 高频问题与处理办法

5.2 几个让我印象深刻的教训

这次操作让我总结出几个特别想提醒后来者的点，都是常规文档里不太好查到的东西。

第一，加字段时如果确定需要建索引，尽量在 DDL 里一起建，不要先加字段再单独建索引。等你第一次在线 DDL 跑完，再跑第二次 CREATE INDEX，等于把大表重建两遍，时间成本和风险都翻倍。用 pt-osc 时，--alter参数可以直接写成ADD COLUMN ... , ADD INDEX ...，一条命令同时完成，这也是我当时选择这种写法的原因。

第二，pt-osc 虽然不阻塞读写，但也不是零风险。它会在源表上创建触发器，如果源表本身写入量巨大，触发器会带来额外开销，并且批量拷贝期间源库的 IO 和主从复制压力会明显上升。所以工具能解决锁问题，不代表你可以在业务高峰期随便跑。低峰执行永远是最稳妥的选择。

第三，操作过程一定要留档。我这次执行前把表结构、行数、空间大小、从库延迟、执行命令、执行结果都保存了下来，后来复盘的时候非常有帮助。特别是如果加字段后出现异常，你能快速判断到底是 DDL 阶段的问题，还是回填数据阶段的问题。

第四，优先级最高的不是“把字段加上去”，而是“加字段的过程中不影响业务”。为了这个目标，你可以提前切走读流量、停掉定时任务、调小分批大小、增加监控，甚至把一个看似简单的需求拆成多个小步骤完成。不要觉得这个过程繁琐，等你真正踩过一次 metadata lock 或者磁盘写满的坑，就会理解这些步骤的价值。

这次之后，我再遇到大表加字段，已经养成了固定习惯：先查表结构、行数、空间、长事务、从库延迟，然后再决定用 ALTER、pt-osc 还是 gh-ost。改表这件事，永远别拿生产环境去试错，先评估再动手，才是真正的效率。