MySQL 大表在线 DDL 实战：从 MDL 锁雪崩到 gh-ost 无锁切换

dbaai

一、具体的问题

一张 4000 万行、62GB 的订单表要加一列备注字段。周五下午业务低峰期，DBA 直接在主库敲了 ALTER TABLE t_order ADD COLUMN remark VARCHAR(255)，几分钟后监控炸了：Threads_running 从 60 涨到 900，所有涉及这张表的请求——包括最普通的 SELECT——全部堆在 Waiting for table metadata lock，应用侧大面积超时。此刻最尴尬的是不敢动：kill 掉 ALTER 会不会丢数据？先 kill 哪个会话？堆积的 900 个连接怎么解？

这类事故几乎每个 DBA 都踩过，共同点是：问题不在"改表"本身，而在 DDL 前面排着的一条持有 MDL 读锁的查询。这篇文章把大表 DDL 的完整处理链路走一遍：怎么定位 MDL 阻塞源头、怎么试跑一条 DDL 确认它不会锁表、怎么用 gh-ost 在不停机的前提下完成变更。

二、核心原理

MySQL 对表结构的并发控制靠元数据锁（MDL）。任何 SELECT 或 DML 进来先拿表上的 MDL 读锁，DDL 需要拿 MDL 写锁，且写锁请求走的是公平队列：DDL 之后的每一个新请求（哪怕只是 SELECT）都要排在 DDL 后面等写锁拿到手。于是只要有一条未提交的查询或长事务持着读锁不放，DDL 等不到写锁，后面的业务全部堵死——这就是"一条查询 + 一条 DDL = 全表雪崩"的机制。

另一个容易混淆的点：8.0 的 Online DDL 解决的是"DDL 执行期间能否并发 DML"，不是"DDL 能不能立刻开始"。ALGORITHM=INPLACE 只保证执行阶段不锁全表，开始阶段仍然要等 MDL 写锁。而且 INPLACE 不等于不拷数据：8.0.12+ 的加列可以走 INSTANT（秒级、只改元数据），但改列类型、加全文索引等仍是 COPY，要拷全表。

对几千万行的大表，更稳的路是 gh-ost：它不碰原表，而是建一张幽灵表（ghost table）按原表结构加好新列，从 binlog 订阅增量回放到幽灵表，同时分批拷存量数据，最后在一个瞬间完成原子 RENAME 切换。全程不长期持有 MDL 写锁，可限流、可暂停、可随时中止，对主库和从库的压力都可控。相比基于触发器的 pt-osc，gh-ost 没有触发器双写放大的问题，也不会被"表上已有触发器"卡死。

三、实例参考（动手步骤）

步骤 0：记录现状基线。 改之前先留证据，事后写报告也好交代：

2. SELECT table_name, engine, table_rows,
3.        ROUND(data_length/1024/1024/1024,2) AS data_gb,
4.        ROUND(index_length/1024/1024/1024,2) AS idx_gb
5. FROM information_schema.tables
6. WHERE table_schema='shop' AND table_name='t_order';
7. SHOW GLOBAL STATUS LIKE 'Threads_running';
8. SHOW REPLICA STATUS\G   -- 记下 Seconds_Behind_Master 基线

复制代码

步骤 1：定位 MDL 阻塞源头（8.0）。 先开 MDL instrumentation（默认关闭，重启后需在 my.cnf 持久化）：

2. UPDATE performance_schema.setup_instruments
3. SET ENABLED='YES', TIMED='YES'
4. WHERE NAME='wait/lock/metadata/sql/mdl';
5. -- 直接看结论：
6. SELECT * FROM sys.schema_table_lock_waits\G

复制代码

输出里 blocking_pid 就是持锁元凶，waiting_query 是被堵的 ALTER。5.7 没有这个视图，用未提交事务兜底：

2. SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query
3. FROM information_schema.innodb_trx
4. ORDER BY trx_started LIMIT 10;   -- trx_started 越早越可疑

复制代码

步骤 2：先清源头，再发 DDL。 找到长查询后生成 KILL 语句，确认无业务风险再执行：

2. SELECT CONCAT('KILL ', id, ';') AS kill_sql, user, host,
3.        time, LEFT(info, 60) AS sql_text
4. FROM information_schema.processlist
5. WHERE time > 60 AND command <> 'Sleep'
6. ORDER BY time DESC;

复制代码

步骤 3：DDL 试跑，确认不锁表。 把 ALGORITHM/LOCK 写进语句尾部，不支持的组合会立即报错而不动数据，相当于免费 dry-run：

2. -- 加列优先试 INSTANT（8.0.12+，秒级完成）
3. ALTER TABLE t_order ADD COLUMN remark VARCHAR(255) NOT NULL DEFAULT '',
4.   ALGORITHM=INSTANT;
5. -- 不支持 INSTANT 再退 INPLACE；报 ER_ALTER_OPERATION_NOT_SUPPORTED
6. -- 则说明只能 COPY，大表改用 gh-ost
7. ALTER TABLE t_order ADD COLUMN remark VARCHAR(255) NOT NULL DEFAULT '',
8.   ALGORITHM=INPLACE, LOCK=NONE;

复制代码

步骤 4：用 gh-ost 执行在线变更。 前提检查：binlog_format=ROW 且 binlog_row_image=FULL；主库磁盘剩余空间 ≥ 表大小（幽灵表要双倍）。执行：

2. gh-ost \
3.   --host=127.0.0.1 --user=gh_admin --password='***' \
4.   --database=shop --table=t_order \
5.   --alter="ADD COLUMN remark VARCHAR(255) NOT NULL DEFAULT ''" \
6.   --max-load=Threads_running=60 \
7.   --critical-load=Threads_running=200 \
8.   --chunk-size=1000 \
9.   --max-lag-millis=1500 \
10.   --postpone-cut-over-flag-file=/tmp/ghost.postpone \
11.   --initially-drop-ghost-table \
12.   --allow-on-master --execute

复制代码

参数含义：--max-load 超过 60 就暂停拷贝；--critical-load 超过 200 直接放弃回滚；--max-lag-millis 从库延迟超 1.5 秒降速。拷贝期间随时 echo throttle > /tmp/ghost.postpone 暂停、echo no-throttle > /tmp/ghost.postpone 恢复。业务窗口到了再解除切换挂起：

2. echo unpostpone > /tmp/ghost.postpone   # 触发 cut-over，原子 RENAME
3. gh-ost ... --test-on-replica            # 可选：先在从库演练一遍

复制代码

步骤 5：切换后验证。 新表行数对账（SELECT COUNT(*) 与原表最后一轮日志对比）、抽样业务接口回归、观察 24 小时后 DROP TABLE _gh_ost_backup_t_order（备份表按 --ok-to-drop-table 策略处理），并确认 SHOW CREATE TABLE t_order 里新列与索引符合预期。

四、实操检查清单

• 发 DDL 前查 innodb_trx 与 processlist，确认没有 time > 60s 的长查询和未提交事务（低峰也要查，监控探活查询同样会堵 DDL）。
  
• 8.0 持久化开启 wait/lock/metadata/sql/mdl instrument，出事时 sys.schema_table_lock_waits 一条 SQL 定位元凶。
  
• 每条 DDL 都带上 ALGORITHM=, LOCK= 试跑，确认 INSTANT/INPLACE 可用后再上线；COPY 类变更一律走 gh-ost。
  
• gh-ost 前置检查三项：binlog ROW 格式、binlog_row_image=FULL、磁盘剩余 ≥ 表大小两倍。
  
• gh-ost 限流三件套配齐：--max-load、--critical-load、--max-lag-millis；用 --postpone-cut-over-flag-file 把切换控制在自己的业务窗口。
  
• 切换后对账行数、验证新列默认值回填、确认从库延迟回落，再择期清理 _ghc/备份表。
  
• 复杂变更（改列类型、拆字段）先在从库 --test-on-replica 或独立环境演练，估出真实耗时再排窗口。
  

几个容易踩的坑

• 把 Online DDL 当成"不会锁表"：INPLACE 只解决执行期并发，开始前仍要等 MDL 写锁，长事务不清理照样雪崩。
  
• 监控探活进程的定时 SELECT 也是 MDL 持有者：事务未提交（哪怕只 SELECT 了就 sleep）一样堵 DDL，排查时别只盯着业务账号。
  
• innodb_online_alter_log_max_size 默认 128MB：在线 DDL 执行期间并发 DML 会写入 online log，高写入量的表跑长 DDL 报 ER_INNODB_ONLINE_LOG_TOO_BIG 只能重试，大表优先 gh-ost。
  
• pt-osc 在已有触发器的表上直接报错，且触发器双写放大写压力；gh-ost 走 binlog，无此限制（同样要求 ROW 格式）。
  
• gh-ost 的 cut-over 需要短暂 MDL 写锁，虽然只有一瞬，仍要避开整点报表任务等 MDL 密集时段。
  
• 8.0 INSTANT 加列有上限：instant 列/默认值合计不超过 64 个字段字节额度，超过会自动退回 INPLACE，别拿秒级加列当常态。
  

治理前后对比

指标	直接 ALTER（事故）	清源头 + gh-ost
变更总耗时	卡死 47 分钟后人工回滚	52 分钟完成（含拷贝）
业务影响	900 会话堆积，超时报错 14 分钟	QPS 波动 < 3%，无报错
Threads_running 峰值	900	68（触发限流即暂停）
从库延迟峰值	不可控（DDL 独占）	1.6s（限流自动降速）
cut-over 切换	无	0.8 秒完成
回滚代价	ALTER 中断即回滚 47 分钟	暂停即可，原表全程未动

大表 DDL 的纪律只有一条：变更方案先选算法，再看持锁方，最后才动手。把这三步固化成工单模板，"周五下午 ALTER 炸库"这种事故就不会再上演。