MySQL大表ALTER TABLE锁表导致业务卡死，线上应急处理与安全变更方案

MySQL大表ALTER TABLE锁表导致业务卡死，线上应急处理与安全变更方案
摘要
线上MySQL业务，一张千万级大表，执行alter table修改字段、增加索引，执行之后业务全部卡死，大量查询堆积，数据库CPU飙升，网站接口全部超时。很多开发人员本地测试ALTER很快，直接在线上大表执行DDL，引发线上故障。本文还原故障场景，分析MySQL DDL锁表机制，介绍pt‑online‑schema‑change、gh‑ost在线无锁变更方案，同时提供故障发生之后应急回滚处理步骤。
QQ20260812-153558.png
问题现象
业务MySQL InnoDB表，数据量1200万行，业务正常读写。开发人员需要给表增加一个索引，直接执行SQL：

ALTER TABLE `order_list` ADD INDEX idx_create_time(create_time);

语句提交之后，命令行长时间卡住。业务网站瞬间大量请求超时，数据库连接数打满，业务完全不可用。数据库监控看到大量select、update语句处于Waiting for table metadata lock等待元数据锁状态。新的业务SQL全部阻塞，旧的业务读写全部卡住。强行kill掉alter线程，大量堆积的业务SQL瞬间涌入数据库，出现数据库雪崩。

复现步骤

  1. MySQL5.7 / 8.0 InnoDB大表，千万级别数据。
  2. 业务持续读写，表有活跃事务。
  3. 直接执行ALTER TABLE增加索引、修改字段。
  4. DDL操作申请元数据锁MDL，遇到未提交事务，DDL阻塞，后续所有DML语句排队等待MDL锁，业务全部卡死。

补充：MySQL5.6开始支持Online DDL，但是不是所有alter操作都可以真正在线，部分DDL依旧会锁表；同时如果存在未提交事务，就算Online DDL，依旧会被MDL元数据锁卡住。

根因分析

  1. MDL元数据锁（Metadata Lock）
    MySQL InnoDB访问表的时候会获取MDL锁。DML（select update insert）获取MDL读锁；ALTER TABLE DDL操作申请MDL写锁。
    读锁之间可以并发；写锁和所有锁互斥。
    当有活跃事务没有提交，持有MDL读锁，DDL申请写锁就会阻塞。DDL一旦被阻塞，后续所有新的查询、更新语句，都需要申请MDL读锁，全部排队等待DDL，整张表读写全部卡住。

    很多人以为只有MyISAM才锁表，InnoDB行锁只锁行，但是MDL元数据锁是表级别，会引发整张表阻塞。

  2. Online DDL的局限
    MySQL5.6引入Online DDL，部分DDL可以避免锁表，但是有前提条件：存储引擎、操作类型、参数innodb_online_alter_log_max_size，并且不能有长时间未提交事务。一旦有长事务，Online DDL同样会卡住，并且堵塞后续全部业务。

  3. 直接kill DDL不是万能急救手段
    ALTER被卡住之后，很多DBA直接kill这个alter会话。kill之后，堆积大量等待MDL锁的业务SQL会瞬间全部执行，瞬间巨大流量压垮数据库，直接雪崩。

  4. 大表拷贝数据耗时
    部分DDL需要重建整张表数据，千万级表会消耗大量IO，磁盘IO打满，数据库响应变慢。

    故障发生后的应急处理步骤（线上已经卡死场景）

    注意：操作生产数据库务必谨慎，优先在低峰演练。

  5. 找到持有MDL锁的会话，找到长时间未提交的事务
SELECT  FROM information_schema.innodb_trx;

查看trx_started，找到运行时间很长的事务。

  1. 查询当前线程列表
show processlist;

找到处于Waiting for table metadata lock的线程，找到正在执行alter的线程id。

  1. 优先杀掉持有长事务的会话，而不是先kill alter。长事务不释放，kill alter没有意义。
KILL 会话ID;

长事务释放之后，MDL锁释放，alter才可以继续执行，或者被kill。

  1. 如果业务已经大量堆积，在业务允许短暂停机的前提下，再kill alter语句。

    风险：kill alter之后，大量排队SQL瞬间释放，瞬间并发会打满数据库，需要业务侧做好限流。

    线上安全修改大表的方案
    方案一：pt‑online‑schema‑change（pt‑osc）
    Percona工具集pt‑osc，原理：创建一张临时新表，执行DDL变更，通过触发器把原表的数据DML同步到临时表，分批拷贝原表数据，最后原子rename替换原表。全程几乎不会锁原表，业务读写不受影响。

示例命令，给order_list表增加索引：

pt‑online‑schema‑change \
‑‑user=root ‑‑password="xxx" \
‑‑host=127.0.0.1 \
D=business,t=order_list \
‑‑alter="ADD INDEX idx_create_time(create_time)" \
‑‑execute

pt‑osc注意要点：

  1. 原表必须要有主键或者唯一索引。
  2. 避免业务高峰期执行，拷贝数据会消耗IO。
  3. 参数控制拷贝批次大小，防止数据库压力过大。

  4. 禁止在有大量触发器的表使用pt‑osc。

    方案二：gh‑ost
    github开源gh‑ost，不依赖触发器，通过binlog完成增量同步，相比pt‑osc对业务影响更小。
    工作流程：

  5. 创建幽灵表，执行DDL。
  6. 分批迁移原表数据。
  7. 消费binlog同步增量数据。

  8. 原子切换表名。
    gh‑ost不需要触发器，对业务影响更低，现在很多互联网公司优先选择gh‑ost。

    方案三：MySQL原生Online DDL（适合小表，确认无长事务）
    如果确认表数据量不大，业务没有长事务，可以直接原生online ddl。

ALTER TABLE order_list ADD INDEX idx_create_time(create_time), ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM=INPLACE不拷贝原表，LOCK=NONE尽量不加锁。

⚠️重要：就算指定LOCK=NONE，一旦存在长事务持有MDL读锁，DDL依旧会阻塞，并且阻塞全表业务。线上大表不建议直接原生ALTER。

方案四：业务低峰期执行
如果没有pt‑osc、gh‑ost工具，只能选择业务访问量最低的凌晨时段执行，执行前确认没有未提交长事务。

日常运维避坑建议

  1. 禁止业务高峰期直接对千万级大表执行ALTER TABLE，本地开发环境表小，感受不到锁表问题，线上直接引发故障。
  2. 数据库监控增加长事务告警，运行超过30秒的事务直接告警。长事务是MDL锁故障最主要来源。
  3. 上线DDL变更，优先使用pt‑osc或者gh‑ost工具。
  4. 变更前，先查询information_schema.innodb_trx确认没有活跃长事务。
  5. 开发测试环境模拟线上数据量，测试DDL行为，不要只拿空表测试。

  6. MySQL8.0依旧存在MDL锁问题，不要误以为升级8.0就可以随便执行大表DDL。

    故障复盘总结
    很多开发者只知道InnoDB行锁，忽略MDL元数据锁。只要DDL被长事务卡住，后续所有读写全部阻塞，一张表的DDL故障，直接搞垮整套业务。遇到Waiting for table metadata lock，不要上来kill DDL，优先找到源头长事务会话。大表结构变更优先使用在线DDL工具，不要直接原生alter。

凡尘版权

友情链接：凡尘博客
凡尘博客文章|凡尘博客文摘
凡尘影院
凡尘乡音|凡尘街坊
凡尘博客|雨落凡尘博客|羽落凡尘博客
凡尘博客|雨落凡尘博客|羽落凡尘博客

标签: none

添加新评论

  • 上一篇:
  • 下一篇: