MySQL5.7下algorithm=inplace给大表加字段的锁及替代方案咨询

1. algorithm=inplace最终提交阶段加锁的说法是否属实？ #

属实。在MySQL 5.7中，执行ALTER TABLE ... ALGORITHM=INPLACE添加字段（尤其是允许为NULL的字段）时，大部分流程支持并发DML操作，但在最终提交阶段，MySQL需要将这段时间内产生的并发DML变更合并到新表结构中，此时会对表施加排他锁（X锁），阻止新的DML操作，直到结构变更完全完成。

2. 锁持续时间及能否预测锁发生时间？ #

锁的持续时间并非固定几秒，主要受以下因素影响：

• 并发DML积累量：结构变更期间产生的DML操作越多，合并时需要处理的数据量越大，锁时间越长。
• 表的索引复杂度：表上索引越多，合并DML时同步更新索引的工作量越大，锁持有时间就越长。
• 服务器性能：CPU、IO的处理能力直接影响合并操作速度，性能越差，锁时间越长。

至于锁发生的时间，无法精确预测，但可以通过以下方式缩小时间范围：

• 选择业务低峰期执行变更，此时并发DML量最少，锁时间会显著缩短。
• 提前监控表的DML吞吐量，估算变更期间可能产生的增量数据量，大致判断锁的持续时长。

3. 大表添加字段的更优方案 #

• 使用pt-online-schema-change工具：创建临时表，逐步迁移原表数据，同时通过触发器同步原表的增量DML操作，最后替换原表。全程几乎不会对原表加锁，对业务影响极小。
• 使用gh-ost工具：基于binlog同步增量数据，无需创建触发器，相比pt-online-schema-change更轻量，服务器资源消耗更低，可实现无锁（或低锁）的表结构变更。
• 利用主从架构分批操作：先在从库执行表结构变更，同步完成后切换主从关系，将原从库作为新主库，再在原主库执行变更，最后重新同步数据。这种方式可将变更对业务的影响分散到不同节点。
• 分表拆分后变更：若业务允许，先将大表拆分为多个小表，分别对小表执行结构变更，最后合并数据。拆分后的小表变更速度更快，锁时间可忽略不计。

内容的提问来源于stack exchange，提问作者jithin giri