执行ALTER命令修改大表出现查询中断连接丢失的解决方法

ALTER执行期间INSERT、UPDATE是否必须等待 #

没有统一结论，取决于数据库版本、ALTER操作类型和执行方案：

• 5.5及更早版本的MySQL InnoDB引擎，执行ALTER时会全表拷贝并持有排他锁，全程阻塞所有写操作，INSERT、UPDATE必须等ALTER完全执行完成才能执行。
• 5.6及之后版本支持Online DDL，对于加普通索引、加不涉及默认值逻辑的列这类不触发全表数据重构的操作，执行过程中允许正常读写，仅在最后元数据切换的极短窗口持有排他锁，不需要全程等待；但如果是修改主键、变更字段数据类型、修改表字符集这类需要全表重建的操作，或是DDL启动后被长事务阻塞在元数据锁等待阶段，依然会阻塞后续写入。
• 若使用gh-ost、pt-online-schema-change这类第三方在线DDL工具执行改表，全程不会持有长时间的表排他锁，写入操作延迟影响在毫秒级，完全不需要等待ALTER完成。

大表ALTER导致大量查询终止、连接丢失的规避方案 #

核心思路是避免DDL抢占过多业务资源、避免锁队列堆积打满连接池，具体可落地操作如下：

• 选对执行窗口：所有大表ALTER操作全部放在业务低峰期执行，提前在相同规格的测试环境预跑预估执行时长，避开业务高峰时段的资源争抢。
• 不要直接裸跑原生ALTER：
  • 用原生Online DDL前先调优参数：关闭old_alter_table开关确保走在线DDL逻辑，把innodb_online_alter_log_max_size参数调至足够容纳DDL执行期间的增量写入数据，避免因为日志空间不足触发DDL回滚——DDL回滚阶段的锁占用和资源消耗远高于执行阶段，是触发大量查询中断、连接异常的核心诱因之一。
  • 百万行级以上的大表优先用第三方在线DDL工具执行改表，这类工具通过创建影子表同步存量、触发器/binlog追平增量的逻辑执行变更，不会长时间持有元数据排他锁，从根源上避免锁队列堆积。
• 提前规避元数据锁（MDL）拥堵：90%以上的ALTER相关连接故障，都不是ALTER本身执行慢导致的，而是ALTER启动时拿不到MDL写锁，在锁队列中排队，后续所有访问该表的查询都被堵在锁队列中，短时间内堆积大量连接把连接池打满，表现为连接丢失、查询被终止。执行ALTER前先检查目标表是否存在未提交的长事务、跑了很久的慢查询，提前清理完相关会话再启动DDL；同时给DDL会话设置较短的lock_wait_timeout（比如30秒），拿不到锁就自动退出，不要长时间占着锁队列堵业务请求。
• 做好资源限速：自建数据库可以通过cgroup等工具限制DDL进程的CPU、磁盘IO占用上限，避免DDL打满整机资源导致正常查询超时断开；云数据库直接在控制台开启DDL限速功能即可。
• 过亿行的超大表变更，不要直接走ALTER逻辑，优先用影子表双写方案：先建结构符合要求的新表，批量同步存量数据后开启双写，通过binlog追平增量数据后再原子切换表名，把变更风险降到最低。

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