MySQL大表在线加索引会阻塞业务吗？如何规避？

面试记录 2026-10-13

题目：MySQL大表在线加索引会阻塞业务吗？如何规避？

难度：high

标签：db/mysql/index, mysql/online-ddl, mysql/governance, difficulty/high

评分：⭐⭐⭐⭐☆（4/5）

我的回答

Mysql 大表在线加索引是会阻塞核心业务的哪怕是现在 MySQL 8 说支持 online DDL，也是不行的。它的核心流程还是会导致业务线程阻塞。

我们当时考虑规避，首先是建立子表。建立子表的时候，这个子表是不使用的，然后把数据迁移过去，然后进行目标的更新。更新了之后，再把表的名称进行更换。表名称的更换效率会更高一些，影响业务也就更小。

还有就是做索引、做表操作的时候，要选择业务低峰期，或者如果可以停机业务，要进行停机，进行发通告，我们进行某些更新，业务暂时停机，然后进行这方面的操作。毕竟执行过程中如果出现了什么特殊情况，会产生不可预料的各种问题，到时候你想找都没有办法找。

核心原则首先是规避业务封期，然后是使用通告的形式告知会产生业务变动，进行业务暂停，然后进行相关的操作。再有就是使用复制的方式，创建临时表，把数据复制过去，然后再进行更新，先更新结构，把表复制过去，然后再进行相应的处理。另外 MySQL 好像有单独的 online DDL 的加索引的方式，但是也是会有影响的。只是影响度比原本的操作方式要低一些。这具体要看数据量是多少。我们之前数据量大概是 3 个亿还是 5 个亿？三五个亿的数据更新了很久。中断了大概几分钟。

点评

优点：

• 抓到了核心问题：大表加索引即使 8.0 支持 Online DDL 也可能阻塞业务，这个认知非常关键
• 提出了 Ghost Table 方案：建子表 → 迁移数据 → 改名切换，这是经典大厂方案
• 意识到了错峰 + 通告 + 停机策略：说明有真实生产经验
• 提到了 MySQL 有 Online DDL 但仍有影响：知道有官方机制但不过度信任
• 给出了真实数据量级经验：3-5 亿数据更新很久、中断几分钟——非常有说服力

不足：

• 未讲 Online DDL 的具体机制：ALGORITHM=INPLACE LOCK=NONE 是 MySQL 5.6+ 支持的，加索引这种操作理论上可以做到几乎不阻塞（只拿短暂 MDL）
• 未讲 MDL（Metadata Lock）阻塞：ALTER 一开始和结束时会拿短暂 MDL 排他锁，如果有长事务持共享 MDL，ALTER 会一直等——这是生产事故最大原因
• 未讲 gh-ost / pt-online-schema-change 工具：这是大厂标配方案，比手工 Ghost Table 更优雅（自动建影子表、增量同步、平滑切换）
• 未讲 MySQL 8.0 的 Instant DDL：加列、改列名、DEFAULT 值修改等操作可以亚秒级完成，不重建表
• 未讲具体阻塞点分析：
• COPY 算法（老式）：重建整表，期间锁表，业务完全阻塞
• INPLACE 算法（5.6+）：不重建表，只在 B+ 树末尾追加索引，仅拿短暂 MDL
• INSTANT 算法（8.0）：修改元信息，不重建索引
• 未讲风险预案：gh-ost / pt-osc 支持 --cut-over=atomic 平滑切换；生产必须提前在从库演练
• 未讲长事务排查：information_schema.innodb_trx 找长事务 → kill 掉再执行 ALTER
• 表述上"3 个亿还是 5 个亿"这种不确定的说法在面试中略减分，应该给出明确数字或"约几亿"

补充：

• 加索引三种 DDL 算法对比：
• ALGORITHM=COPY：重建整表，全程锁表，业务阻塞
• ALGORITHM=INPLACE：不重建表，B+ 树追加索引，仅拿短暂 MDL
• ALGORITHM=INSTANT（8.0）：修改元信息，亚秒级，不重建任何数据
• Online DDL 语法：ALTER TABLE t ADD INDEX idx_a (a), ALGORITHM=INPLACE, LOCK=NONE
• 工具链：
• gh-ost：GitHub 开源，Facebook 生产方案，读 binlog 增量同步，--cut-over=atomic 平滑切换
• pt-online-schema-change：Percona Toolkit，触发器方式增量同步
• 加索引具体阻塞点：
• 老式 ALTER TABLE ADD INDEX：拿 MDL 排他锁，阻塞所有读写
• 5.6+ INPLACE：DDL 开始/结束拿短暂 MDL 排他锁，中间拿 MDL 共享锁（不阻塞 DML）
• 若中间有长事务持 MDL 共享锁 → 长事务结束后新事务排队 → ALTER 等长事务结束才能拿排他锁 → 排队事务阻塞 → 连接池爆
• 生产排查长事务：SELECT * FROM information_schema.innodb_trx WHERE TIME_TO_SEC(TIMEDIFF(NOW(), START_TIME)) > 60

完整讲解

第一部分：知识重点剖析

核心考点定位：考察是否清楚 Online DDL 的真实机制（INPLACE/INSTANT vs COPY）、MDL 阻塞链、大厂规避工具链。

知识点拆解：

1. DDL 的三种算法（MySQL 5.6+）

• COPY：重建整表，全程锁表读写阻塞，业务完全停顿
• INPLACE：不重建表，直接在原 B+ 树追加或修改索引，仅拿短暂 MDL；支持大部分 ADD INDEX、DROP INDEX、ADD COLUMN、MODIFY COLUMN
• INSTANT（8.0 新增）：只修改元信息，不重建数据，亚秒级完成；支持加列、改列名、改 DEFAULT 值、ALTER TABLE ... CONVERT TO CHARACTER SET 等

1. 加索引的具体阻塞点

• 老式 ALTER（无 ALGORITHM=INPLACE）：默认走 COPY 算法，全程锁表，业务完全阻塞
• 推荐语法：ALTER TABLE t ADD INDEX idx_a (a), ALGORITHM=INPLACE, LOCK=NONE
• INPLACE 只在 DDL 开始和结束各拿一次短暂 MDL 排他锁
• 中间过程只拿 MDL 共享锁，不阻塞 DML
• 8.0 INSTANT 适用场景：加普通列、改列名、改 DEFAULT 值；加索引仍需 INPLACE

1. MDL（Metadata Lock）阻塞链——生产事故最大原因

• 机制：所有 DML 拿 MDL 共享锁，DDL 拿 MDL 排他锁；拿排他锁前要等所有共享锁释放
• 事故场景：

1. 长事务 T1 执行 SELECT，持 MDL 共享锁不提交
2. ALTER TABLE 想拿 MDL 排他锁，等待 T1
3. 之后新事务 T2、T3... 想拿 MDL 共享锁，排队等 ALTER
4. ALTER 拿不到排他锁就一直等，排队事务全阻塞 → 连接池爆 → 服务雪崩

• 排查：SELECT * FROM performance_schema.metadata_locks 看锁等待；information_schema.innodb_trx 找长事务
• 解决：
• metadata_locks_timeout（8.0）：ALTER 等不到 MDL 直接超时失败，不再阻塞后续事务
• Kill 长事务
• DDL 用 ALGORITHM=INPLACE LOCK=NONE，拿不到锁立即失败

1. Ghost Table 手工方案（经典但易错）

• 流程：

1. CREATE TABLE t_new LIKE t 建影子表
2. INSERT INTO t_new SELECT * FROM t 全量同步
3. 触发器方式增量同步（INSERT/UPDATE/DELETE 触发器）
4. 等全量+增量追平
5. RENAME TABLE t TO t_old, t_new TO t 原子切换
6. 观察一段时间后 DROP t_old

• 风险：
• 触发器方式对写入性能有影响
• 切换瞬间可能丢数据（如果切换后有新写入到 t_old）
• 手工流程容易出错
• 优势：可控性强，切换瞬间业务影响最小

1. gh-ost / pt-online-schema-change（大厂标配）

• gh-ost：
• GitHub 开源，Facebook、Google 生产方案
• 读 binlog 做增量同步，不用触发器
• --cut-over=atomic：切换时用事务原子操作，避免数据丢失
• 支持限流（--max-lag-msec）避免影响主库
• 支持中断恢复
• pt-online-schema-change：
• Percona Toolkit
• 触发器方式增量同步
• 支持限流
• 两者对比：gh-ost 更成熟，binlog 方式更安全；pt-osc 依赖触发器，对写入有影响
• 生产演练：必须先在从库演练，评估数据量、耗时、影响

1. MySQL 8.0 Instant DDL 支持的操作

• 加列（非 FIRST/AFTER 位置的普通列）
• 改列名
• 改 DEFAULT 值
• 表级选项变更
• ALTER TABLE ... CONVERT TO CHARACTER SET
• 注意：加索引仍需 INPLACE，不是 INSTANT

1. 生产规避策略清单

• 首选：ALGORITHM=INPLACE, LOCK=NONE，加超时保护
• 次选：gh-ost 或 pt-osc 工具自动执行
• 错峰：低峰期执行，避免和业务高峰叠加
• 通告：提前告知业务方，准备好停机预案
• 长事务排查：ALTER 前先查 innodb_trx，kill 掉长事务
• 从库演练：先用从库数据量评估耗时
• 可回滚：准备回滚脚本，出问题能快速恢复
• 限流：gh-ost 用 --max-lag-msec=1000 限制复制延迟

1. 数据量级参考

• 100 万行：秒级完成，基本无感
• 1000 万行：几十秒到几分钟，低峰执行
• 1 亿行：几分钟到几十分钟，必须用 gh-ost 或 pt-osc
• 10 亿行以上：几小时到几天，必须分阶段执行、错峰、限流

第二部分：考察方向与延伸递进

考察方向：

• 深度：能否讲清 INPLACE/INSTANT/COPY 三种算法的差异和适用场景
• 广度：能否覆盖 MDL 阻塞链、gh-ost/pt-osc 工具、Instant DDL
• 应用：能否给出生产大表加索引的完整流程（含风险评估、演练、切换）

延伸递进面试题：

1. MySQL 8.0 的 INSTANT DDL 能加索引吗？

• 解答要点：不能。INSTANT 只支持修改元信息（加列、改列名、改 DEFAULT 等），加索引仍需 INPLACE 算法

1. MDL 阻塞导致连接池爆，如何快速恢复？

• 解答要点：先 SELECT * FROM performance_schema.metadata_locks 看是谁持锁；information_schema.innodb_trx 找长事务；kill 掉长事务后 MDL 排他锁释放，ALTER 继续；后续事务能拿到 MDL 共享锁，业务恢复

1. gh-ost 和 pt-osc 怎么选？

• 解答要点：gh-ost 用 binlog 增量同步，不用触发器，对主库写入影响小；pt-osc 用触发器，对写入有影响；生产推荐 gh-ost

第三部分：面试者参考回答（口语化）

面试官您好，大表加索引确实会阻塞业务，即使 MySQL 8.0 支持 Online DDL 也不能完全放心，我按"机制 + 规避方案 + 生产经验"来讲。

先讲机制。MySQL 5.6+ 的 DDL 有三种算法：COPY（老式，重建整表全程锁表）、INPLACE（不重建表，B+ 树追加索引，只拿短暂 MDL）、INSTANT（8.0 新增，只改元信息，亚秒级，支持加列、改列名、改 DEFAULT，但不支持加索引）。生产加索引正确姿势是 ALTER TABLE t ADD INDEX idx_a(a), ALGORITHM=INPLACE, LOCK=NONE——加索引走 INPLACE，拿不到锁立即失败而不是排队阻塞。

再讲真正的坑：MDL 阻塞链。这是生产事故最大原因。长事务 T1 持 MDL 共享锁不提交 → ALTER 拿排他锁等待 T1 → 后续所有 DML 拿共享锁排队等 ALTER → 连接池爆、服务雪崩。破解三招：metadata_locks_timeout（8.0）让 ALTER 等不到 MDL 就超时；ALTER 前先查 information_schema.innodb_trx kill 掉长事务；DDL 一定用 LOCK=NONE 拿不到锁立即失败。

规避方案三档。第一档是手工 Ghost Table：建影子表 → 全量同步 → 触发器增量同步 → RENAME TABLE 原子切换 → 观察后 DROP 旧表。优点是可控，缺点是触发器影响写入、切换瞬间可能丢数据。第二档是gh-ost：GitHub 开源，Facebook 生产方案，读 binlog 增量同步，不用触发器，--cut-over=atomic 平滑切换，支持 --max-lag-msec 限流。第三档是 pt-online-schema-change：Percona 出品，触发器方式，成熟但写入有影响。生产推荐 gh-ost，因为不用触发器对主库压力最小。

生产流程我按六步走。第一步，先在从库演练评估耗时和数据量影响。第二步，错峰执行，选业务低峰。第三步，发通告告知业务方。第四步，ALTER 前先 innodb_trx 排查长事务并 kill。第五步，工具执行加索引（gh-ost 或 pt-osc），限流避免影响复制延迟。第六步，切换后观察复制延迟和业务指标，出问题能立刻回滚。

数据量级参考：百万行秒级无感，千万行低峰执行，亿级必须用 gh-ost 或 pt-osc，十亿级以上要分阶段错峰。我之前遇到过 3-5 亿行的加索引场景，全程跑了几十分钟，中间有几秒业务中断——主要是切换瞬间的 MDL 排他锁拿了片刻。

MySQL 8.0 的 Instant DDL 不能加索引，只支持加列、改列名、改 DEFAULT 这类元信息操作，加索引仍需 INPLACE。这个知识点特别容易混。

当然，任何生产 DDL 都是风险操作，我的经验是——能不在线上做就不在线上做，能在从库演练就在从库演练，必须在线上做的用 gh-ost 工具 + 错峰 + 通告 + 限流，把风险降到最低。我踩过一次坑，长事务没 kill 干净导致 ALTER 排队把连接池打爆了，从那以后 ALTER 前必查 innodb_trx 已经成为肌肉记忆。