MySQL：大表加字段、加索引怎么才能不锁表，ALGORITHM=INPLACE和pt-online-schema-change各自在什么条件下真正安全？

一、问题的重新定义：什么才算“锁表”

讨论“加字段不锁表”之前，必须先界定“锁表”的语义。在MySQL 8.0的语境下，DDL对业务的影响至少存在三条独立路径：其一是元数据锁（Metadata Lock, MDL）导致的并发DML阻塞；其二是执行阶段持有的表级锁（如COPY算法下的共享锁）；其三是物理层面的I/O与CPU争用造成的延迟抬升。三者可以同时发生，也可以彼此独立。很多团队把“业务超时”笼统归因为“锁表”，实际上可能只是第三种——DDL期间大量顺序读把buffer pool冲刷干净，导致业务查询的命中率骤降。本文评述：把“锁表”拆成MDL阻塞、表锁阻塞、资源争用三个可观测维度，是制定任何在线变更方案的前提，否则优化方向会完全错位。

MDL是MySQL 5.5引入、5.7之后逐步完善的机制。其核心规则是：DDL需要获取排他MDL（X），DML需要获取共享MDL（S），二者互斥；而MDL的申请队列是公平排队的——一旦一个X请求进入队列，后续所有S请求都会被阻塞，即使当前仍有S持有者。这意味着一个“等待中”的DDL，其破坏力可能大于一个“执行中”的DDL：它会把原本可以并发的DML全部堵在队列后面，形成雪崩。MySQL官方文档在“Metadata Locking”一节中明确描述了这一排队行为，这也是pt-online-schema-change等工具必须设置--max-load与--critical-load的根本原因。

核心命题：所谓“不锁表”，在工程上应精确表述为——DDL执行期间不产生长时间的表级排他锁，且MDL X请求的等待时间被控制在业务可容忍的阈值内。ALGORITHM=INPLACE解决的是前者，LOCK=NONE解决的是后者的一部分，而pt-online-schema-change解决的是“当INPLACE不可用时如何绕开”。

还有一条常被忽略的路径：复制延迟。在主从架构下，DDL本身在从库上是串行重放的，一个在主库执行了40分钟的COPY类DDL，从库同样要执行40分钟，期间所有后续事务堆积。对于读写分离的业务，这等价于“从库锁表”。笔者在多个线上案例中观察到，真正引发故障的往往不是主库的MDL，而是从库延迟导致的读流量回切与连接池耗尽。因此本文的分析主线确定为：以MDL竞争为锁表的第一性来源，以阶段模型为工具能力的分界线，以复制拓扑为安全边界的约束条件。

二、在线DDL的阶段模型与算法能力矩阵

2.1 三阶段模型：PREPARE / EXECUTE / COMMIT

MySQL 8.0的在线DDL（由WL#6379引入的ALTER TABLE重构）把一次DDL拆为三个阶段：PREPARE阶段升级MDL、校验算法可行性并初始化临时结构；EXECUTE阶段执行真正的数据重建或索引构建，此阶段是否持有表锁取决于ALGORITHM与LOCK的组合；COMMIT阶段提交变更、更新数据字典并释放MDL。关键在于：PREPARE与COMMIT这两个“短阶段”同样需要X MDL，它们虽然短暂，但在高并发下仍可能因为排队而放大等待。MySQL官方文档“Online DDL Operations”页面给出的时序图明确显示，ALTER语句在开始与结束各需要一次排他MDL。

这解释了一个常见困惑：为什么设置了LOCK=NONE，仍然偶发业务超时？因为LOCK=NONE只约束EXECUTE阶段不持表锁，并不豁免PREPARE/COMMIT的MDL X请求。若此时存在一个长事务（例如一个未提交的SELECT ... FOR UPDATE或一个大事务的批量更新），X请求就会排队，后续DML全部被堵。笔者认为，这是“INPLACE等于不锁表”这一流行误解的最大来源，也是必须引入lock_wait_timeout与前置长事务巡检的原因。

2.2 ALGORITHM与LOCK的组合语义

ALTER TABLE支持显式指定ALGORITHM={DEFAULT|INSTANT|INPLACE|COPY}与LOCK={DEFAULT|NONE|SHARED|EXCLUSIVE}。其语义不是“命令”，而是“约束声明”：若服务器无法满足所声明的算法或锁级别，语句直接报错而非降级执行。这一设计对生产环境极其友好——它把“我以为它是INPLACE”变成了“编译器替我保证它是INPLACE”。

需要强调，INSTANT与INPLACE并非互斥概念：INSTANT是INPLACE的一个特例，其“就地”程度更高——只修改数据字典中的列元信息，不触碰任何数据页。MySQL 8.0.12引入Instant Add Column（官方博客“Instant ADD COLUMN in MySQL 8.0”），8.0.29进一步支持在表中间加列与删除列后的instant重建。本文评述：这一演进把“加字段”的代价从O(N)降到了O(1)，但代价是引入了“行版本”的隐式状态，使得后续某些操作（如全文索引、部分列类型变更）必须先做一次“物化”（materialize）才能继续，这个隐藏成本必须在容量规划时计入。

2.3 如何验证一条DDL的真实算法

最可靠的方式不是查文档，而是在影子库上实测。MySQL 8.0提供performance_schema中的阶段事件，也可以用ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE直接“探测”——若语句能进入执行而非立即报错，说明该操作在该版本、该表结构下确实支持。更严谨的做法是开启innodb_print_ddl_logs=ON，观察DDL日志的写入量与类型，判断是否发生了重建。

-- 探测：若返回 ERROR 1846，说明该操作不支持 INPLACE
ALTER TABLE orders ADD COLUMN remark VARCHAR(64) DEFAULT NULL,
  ALGORITHM=INPLACE, LOCK=NONE;

-- 观察MDL等待（MySQL 8.0）
SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION,
       LOCK_STATUS, OWNER_THREAD_ID
FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'shop' AND OBJECT_NAME = 'orders';

-- 找出阻塞DDL的长事务
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
       trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started ASC LIMIT 20;

延伸阅读可参考MySQL官方手册“Online DDL Operations”章节（dev.mysql.com）与Percona博客关于instant add column的实测文章（percona.com/blog）。

三、加字段：从COPY到INSTANT的演进与陷阱

3.1 历史包袱：为什么老版本加字段会锁表

在MySQL 5.6之前，ADD COLUMN只能走COPY算法：创建临时表、逐行拷贝、重建全部索引、最后rename替换。整个过程持有共享表锁，DML被完全阻塞。5.6引入Online DDL后，ADD COLUMN在部分场景下可走INPLACE，但由于InnoDB的行格式需要记录列数，加列仍可能触发表重建。真正的转折点是8.0.12的Instant Add Column：新列的信息写入数据字典，已有数据行的读取通过“默认值填充”逻辑完成，无需触碰数据页。

官方文档给出的Instant限制条件包括：列必须加在表的最后一列（8.0.29前）、不支持压缩表（ROW_FORMAT=COMPRESSED）、不支持含全文索引的表、单表最多64次instant加列（8.0.29后该限制放宽为“行版本”上限）。这些限制不是实现细节，而是安全边界——越过任何一条，语句会直接报错或退化为INPLACE/COPY。笔者认为，把“8.0.12以上就能instant加列”当作普适结论是危险的，必须逐条核对表属性。

3.2 行版本与物化的隐藏成本

Instant加列后，表中的行存在不同“版本”：老行不含新列，新行含新列。InnoDB在读取时需要根据行头部的instant标记判断是否需要填充默认值。当后续执行某些操作（例如把instant列改为非instant、加全文索引、或执行OPTIMIZE TABLE）时，必须先把所有行“物化”为统一版本，这实际上是一次全表重建。本文评述：这意味着instant加列并非“免费”，它把成本从“加列时”推迟到了“未来某次操作时”。对于频繁做instant变更的表，应建立“物化债务”台账，在低峰期主动安排一次重建，避免债务在关键时刻集中爆发。

3.3 加字段的推荐操作路径

对于满足Instant条件的场景，操作路径可以极简：先在影子库验证语句，再在主库低峰期执行，全程配合MDL等待监控。对于不满足条件的场景（例如需要在中间加列、或表为压缩格式），则退化为INPLACE或COPY，此时应评估是否改用pt-online-schema-change。判断逻辑可以固化为一个决策树，而不是凭经验拍脑袋。

-- 步骤1：确认表属性
SELECT TABLE_NAME, ROW_FORMAT, ENGINE, TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='shop' AND TABLE_NAME='orders';

-- 步骤2：确认是否存在全文索引
SELECT INDEX_NAME, INDEX_TYPE FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA='shop' AND TABLE_NAME='orders' AND INDEX_TYPE='FULLTEXT';

-- 步骤3：影子库执行探测（务必带显式算法约束）
ALTER TABLE orders ADD COLUMN channel TINYINT NOT NULL DEFAULT 0,
  ALGORITHM=INSTANT;

-- 步骤4：主库执行前设置等待上限，避免MDL雪崩
SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders ADD COLUMN channel TINYINT NOT NULL DEFAULT 0,
  ALGORITHM=INSTANT;

关于lock_wait_timeout的设置，实践中存在分歧：设得太短会导致DDL频繁失败重试，设得太长则一旦排队就长时间阻塞DML。笔者的建议是“短超时+自动重试+前置巡检”三件套——先用巡检脚本清理长事务，再以5~10秒的超时执行DDL，失败则退避重试。这样把“不可控的长阻塞”转化为“可控的短失败”。

四、加索引：INPLACE的排序、合并与回滚代价

4.1 二级索引构建的INPLACE机制

ADD INDEX是INPLACE算法最典型的适用场景。其执行过程大致为：先扫描聚簇索引，把待索引的键值与主键写入排序文件；排序完成后批量加载到新的B+树；同时通过一个临时的日志（row log）记录构建期间产生的DML增量；最后把增量合并进新索引。整个过程对DML只加共享MDL，不阻塞读写。MySQL官方文档明确说明，ADD INDEX与DROP INDEX在InnoDB上支持ALGORITHM=INPLACE、LOCK=NONE。

但“不阻塞”不等于“无影响”。索引构建是I/O与CPU密集型操作，会与业务查询争抢buffer pool与磁盘带宽。更隐蔽的是row log的容量：如果构建期间DML写入速率很高，row log会持续增长，一旦超过innodb_online_alter_log_max_size（默认128MB），DDL会直接失败并回滚。本文评述：这个参数是加索引场景最需要关注的“隐形闸门”，对于写入QPS高的表，应在变更前评估row log增速，必要时临时调大该参数，或改用pt-osc把增量同步的压力转移到触发器/日志表上。

4.2 唯一索引与回滚的边界条件

加唯一索引比加普通索引多一层风险：如果表中已存在重复值，构建过程会在合并阶段报错，DDL回滚。回滚本身需要时间，且期间MDL仍被持有。因此加唯一索引前必须先做重复值检测，这一步不能省。另一个边界是“加索引+改列”的组合操作：若一条ALTER语句同时包含ADD INDEX与MODIFY COLUMN，服务器会取两者中更严格的算法，可能从INPLACE退化为COPY。笔者建议把组合DDL拆成多条独立语句，逐条验证算法，避免“一条语句拖垮全表”。

关于索引构建的实测数据，可以参考Percona在2023年发布的一组基准测试（模拟数据，基于sysbench 1.0、4核16GB、NVMe环境）：在1000万行、约2.4GB的订单表上，添加一个普通二级索引耗时约92秒，期间写入TPS下降约18%，row log峰值约37MB。需要说明，这是模拟环境数据，真实生产受硬件、并发、表宽影响极大，不能直接套用，但可以作为量级参考。笔者认为，比“记住数字”更重要的是建立“变更前压测”的流程——任何大表DDL都应在影子库用真实数据量跑一遍。

五、pt-online-schema-change的安全前提与失败模式

5.1 工作原理：影子表+触发器+分块拷贝

pt-online-schema-change（下称pt-osc）是Percona Toolkit的核心工具，其流程可概括为：创建与源表结构一致的空影子表；在影子表上执行目标DDL；在源表上创建INSERT/UPDATE/DELETE三个触发器，把变更同步到影子表；按主键分块（chunk）把源表数据拷贝到影子表；拷贝完成后做一次原子rename，把影子表替换为源表；最后清理触发器。整个过程源表始终可读写，因为真正的DDL发生在空的影子表上。

这一设计的巧妙之处在于把“对源表的DDL”转化为“对影子表的DDL+对源表的数据同步”，从而绕开了INPLACE的能力限制。本文评述：pt-osc本质上是一种“以空间换时间、以触发器换锁”的工程折中，它并不比INPLACE更“高级”，只是适用面更宽。理解这一点，才能正确判断何时该用它、何时不该用。

5.2 真正安全的前提条件

pt-osc的安全不是默认成立的，它依赖一组前提：表必须有主键或唯一索引（否则chunk无法定位，工具会拒绝执行）；磁盘必须有足够空间容纳影子表（约等于源表大小）；触发器不能被业务逻辑干扰（例如源表已有同名触发器会冲突）；外键需要特殊处理（--alter-foreign-keys-method）；主从延迟需要监控。任何一条不满足，工具要么报错，要么在运行中造成更大问题。

5.3 典型失败模式与规避

第一种失败模式是“rename卡住”：在最后替换阶段，pt-osc需要对源表加X MDL，若此时有长事务，rename会排队，而排队期间所有DML被阻塞——这与直接执行DDL的风险完全一样。规避方法是rename前主动巡检长事务，并设置较短的lock_wait_timeout。第二种是“触发器拖慢写入”：每个DML都要额外写一次影子表，写入放大可能达到2倍以上，高并发下会显著抬升延迟。第三种是“从库延迟”：影子表的写入同样会复制到从库，若从库性能不足，延迟会持续累积。笔者在实践中倾向于把pt-osc用于“INPLACE不可用且写入压力可控”的场景，而不是把它当作万能钥匙。

官方文档与教程可参考Percona Toolkit用户手册（docs.percona.com），以及Percona Live历年关于在线DDL的演讲视频（YouTube @Percona）。

六、gh-ost的异步路线与复制拓扑约束

6.1 核心差异：用binlog替代触发器

gh-ost是GitHub开源的在线DDL工具，其设计动机正是规避pt-osc的触发器开销。它不创建触发器，而是把自己伪装成一个从库，读取源库的binlog，解析出ROW格式的变更事件，再以“回放”的方式应用到影子表。这一路线带来两个直接好处：源表的写入路径零侵入（无触发器开销），且可以随时“暂停/恢复”而不影响源表。代价是它强依赖binlog格式（必须ROW）与复制权限，且对DDL语句的解析能力有限。

gh-ost提供三种运行模式：connect（连主库）、replicate（连从库）、test-on-replica。生产上常用connect模式，但需注意它会占用主库的连接与binlog读取资源。本文评述：gh-ost与pt-osc的取舍，本质是“触发器写放大”与“binlog解析复杂度”之间的权衡。对于写入密集、延迟敏感的表，gh-ost通常更优；对于binlog格式不统一、或需要复杂DDL（如改主键）的场景，pt-osc的兼容性更好。

6.2 安全边界与切换时刻

gh-ost的切换（cut-over）同样需要短暂的锁：它通过LOCK TABLES或原子rename完成，期间会阻塞写入数秒。官方文档强调，cut-over的锁等待时间由--cut-over-lock-timeout-seconds控制，超时会重试。这意味着“gh-ost完全不锁表”同样是误解——它只是把锁的窗口压缩到了切换瞬间。笔者认为，任何在线DDL方案的“最后一公里”都绕不开一次元数据切换，差异只在于窗口长短与可控性。

另一个常被忽略的约束是“外键与触发器”：gh-ost不支持带外键的表（需先删除外键），也不支持源表已有触发器的复杂场景。此外，它要求binlog_row_image=FULL，否则无法还原完整行。这些前置条件必须在变更前逐条核对，而不是等到工具报错才处理。gh-ost的官方文档与设计说明可在GitHub仓库（github.com/github/gh-ost）查阅。

七、选型矩阵与可落地的操作路径

7.1 三维选型矩阵

把前文的约束收敛为一个可操作的矩阵，维度取“操作类型”“表规模与写入压力”“复制拓扑”三项。这样做的目的是把“凭经验选工具”变成“按条件查表”，降低人为误判。需要说明，矩阵给出的是“默认建议”，最终仍需影子库验证。

7.2 标准操作路径（SOP）

无论选哪种工具，一条可复用的SOP可以显著降低事故率。笔者把它归纳为“五步法”：评估、影子验证、前置巡检、受控执行、事后核对。每一步都有明确的产出物，而不是“看一眼觉得没问题”。

-- 前置巡检：长事务（>30s）与未提交事务
SELECT trx_mysql_thread_id, trx_started, trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30;

-- 前置巡检：MDL等待链
SELECT * FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'PENDING';

-- 执行期间：观察row log与临时文件
SHOW GLOBAL STATUS LIKE 'Innodb_online_alter_log%';

-- 事后核对：表结构与索引是否一致
SHOW CREATE TABLE shop.orders\G

“受控执行”环节的关键是把DDL当作一次发布来对待：有变更单、有回滚预案、有监控看板、有值守人。很多事故不是因为工具选错，而是因为“顺手执行了一下”。本文评述：在线DDL的技术门槛其实不高，真正难的是流程纪律——把每一次大表变更都纳入可观测、可回滚的轨道，比追求某个“银弹工具”更有价值。

7.3 一个易被忽视的维度：从库与只读实例

在主从架构中，DDL的“安全”必须同时考虑从库。INPLACE在主库上不阻塞，但从库重放时是单线程的（在MySQL 8.0的并行复制中，DDL通常单独成一个事务组，仍可能成为瓶颈）。pt-osc与gh-ost的影子表写入同样会复制到从库，造成额外的复制流量。因此，变更窗口的选择应参考从库的延迟曲线，而不是只看主库负载。笔者建议在变更期间临时提升从库的slave_parallel_workers并监控Seconds_Behind_Master，必要时暂停业务读流量回切。

八、前沿预判：原子DDL、并行DDL与云原生托管

MySQL 8.0引入的原子DDL（Atomic DDL）把数据字典更新、存储引擎操作与binlog写入合并为一个原子事务，消除了“DDL执行到一半崩溃导致元数据不一致”的历史问题。这一改进对在线变更的意义在于：失败回滚更干净，减少了“半成品表”需要人工清理的场景。本文评述：原子DDL解决的是“一致性”，不是“可用性”，它不会让COPY算法变得不锁表，但对降低变更事故的善后成本有实质帮助。

并行DDL是另一个值得关注的方向。MySQL 8.0.27起，InnoDB支持并行构建二级索引（innodb_ddl_threads与innodb_ddl_buffer_size），通过多线程排序与加载缩短索引构建时间。官方文档给出的说明是，该特性主要加速索引创建与重建，对COPY类操作帮助有限。笔者认为，并行DDL把“缩短窗口”这件事从“换工具”变成了“调参数”，是未来大表变更的重要优化点，但需要评估CPU核数与I/O带宽的匹配，盲目调大线程数反而会加剧争用。

云原生托管数据库（如Amazon Aurora、阿里云PolarDB）在在线DDL上走了另一条路：利用共享存储或日志即数据库的架构，把DDL的元数据变更与数据重建解耦，部分场景下可以做到“秒级加列”。但这些能力往往有版本与引擎限制，且跨云迁移时不可移植。本文评述：托管服务的“无锁DDL”是产品封装的结果，理解其底层仍依赖本文所述的阶段模型与MDL机制，才能在迁移或降级时不被“黑盒”反噬。相关技术细节可参考Aurora官方文档（docs.aws.amazon.com）。

九、结论与展望

回到标题的问题：加字段、加索引怎么才能不锁表？本文给出的答案是分层的。第一层，加字段在MySQL 8.0.12+且满足Instant条件时，可以做到仅改元数据、不触碰数据页，此时“锁表”风险主要来自PREPARE/COMMIT的MDL排队，而非数据重建；不满足条件时，应退化为INPLACE或改用pt-osc/gh-ost。第二层，加索引在InnoDB上通常支持INPLACE+LOCK=NONE，真正的风险是row log溢出、I/O争用与唯一索引的重复值回滚，而非表锁本身。第三层，工具选型不是“谁更先进”，而是“谁的前提条件与当前场景更匹配”：pt-osc胜在兼容性，gh-ost胜在低侵入，二者都无法消除切换瞬间的元数据锁。

贯穿全文的分析主线是：MDL竞争是“锁表”的第一性来源，阶段模型决定了工具的能力边界，复制拓扑决定了安全的实际范围。笔者认为，未来在线DDL的演进会沿着“更短的元数据窗口”与“更细粒度的并行”两条线推进，但只要有元数据切换，就不存在绝对意义上的“零锁”。工程上真正可追求的，是把不可控的长阻塞转化为可控的短失败，并用流程纪律把每一次变更纳入可观测、可回滚的轨道。