MySQL如何实现在不停止服务的情况下修改表结构_利用Online DDL

MySQL如何实现在不停止服务的情况下修改表结构_利用Online DDL

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

ALTER TABLE 为什么默认会锁表

在MySQL 5.6之前，如果你执行一个ALTER TABLE操作，那基本就意味着要和业务说再见了——哪怕只是给一个字段增加点长度。为什么呢？因为老版本的处理方式相当“粗暴”：它会触发一次全表拷贝（业内常说的copy-alter），并且在整个过程中，对原表加上一把MDL_WRITE锁。这把锁一上，所有的INSERT、UPDATE、DELETE操作都得乖乖排队等着。想象一下，就因为加了个VARCHAR(255)字段，整个业务可能就要卡上几个小时，这谁受得了？

究其根本，是当时的架构没有能力把“元数据变更”和“数据物理重组”这两件事分开。表结构一旦要改，数据就得跟着重新排列、索引要重建、磁盘文件也要重写一遍，整个过程无法并发。

好在，从5.6版本开始，MySQL引入了Online DDL机制。但这里有个常见的误区：并不是所有的ALTER操作都能“在线”完成。一个操作到底会不会锁表，取决于三个关键因素：操作的具体类型、使用的存储引擎（目前只有InnoDB支持完整的Online DDL），以及你是否显式地指定了正确的算法和锁级别。

哪些 ALTER 操作能真正 Online（InnoDB）

那么，哪些操作可以真正做到“无感”修改呢？在MySQL 5.6及之后的版本中，以下这些常见操作可以做到ALGORITHM=INPLACE且LOCK=NONE，也就是读写都不会被阻塞：

• 添加或删除二级索引（ADD INDEX / DROP INDEX）
• 修改列的默认值（ALTER COLUMN ... SET DEFAULT）
• 重命名列（注意，仅限于改名，不能改变数据类型或约束）
• 添加虚拟列（ADD VIRTUAL COLUMN）
• 调整自增序列的起始值（ALTER TABLE ... AUTO_INCREMENT = N）

然而，有些操作就没那么“自由”了。即使你指定了ALGORITHM=INPLACE，它们仍然至少需要LOCK=SHARED级别的锁，这意味着写操作会被阻塞，但读操作可以继续：

• 修改列的数据类型（比如VARCHAR(50)改成VARCHAR(100)在某些情况下可以原地进行，但INT改成BIGINT通常不行）
• 添加一个没有默认值的NOT NULL列（因为需要全表扫描来填充NULL值，这个过程会锁住写）
• 修改主键（通常DROP PRIMARY KEY和ADD PRIMARY KEY的组合会触发全表拷贝）

如何强制控制 Online DDL 行为

MySQL给了我们方向盘，让我们可以显式地通过ALGORITHM和LOCK这两个提示来控制DDL的行为。如果你不写，MySQL会自己选择一个它认为最优的策略，但问题是，它的“最优”有时偏向保守，可能导致意料之外的锁表。

所以，一个稳妥的建议是：始终显式声明你的意图。例如：

ALTER TABLE users ADD COLUMN status TINYINT DEFAULT 0, ALGORITHM=INPLACE, LOCK=NONE;

这几个关键参数的含义一定要搞清楚：

• ALGORITHM=INPLACE：要求进行原地修改，禁止拷贝整张表。如果做不到，就直接报错，不会自动降级。
• ALGORITHM=COPY：强制使用全表拷贝的方式，等同于5.6之前的老行为。
• LOCK=NONE：目标是不锁任何读写，但只有部分操作支持。
• LOCK=SHARED：允许读，但阻塞写。
• LOCK=DEFAULT：让MySQL自己决定最小的锁级别（这是默认值，但并不可靠）。

这里有个非常重要的点：ALGORITHM=INPLACE并不自动保证LOCK=NONE！两者必须配合起来检查。在执行前，我们可以利用EXPLAIN FORMAT=JSON（MySQL 8.0.12+支持）来窥探一下MySQL的真实计划：

EXPLAIN FORMAT=JSON ALTER TABLE users ADD INDEX idx_email (email);

在返回的JSON结果里，重点关注alter_algorithm和lock_level这两个字段，它们就是MySQL最终决定采用的策略。

线上执行前必须验证的三件事

Online DDL虽好，但绝非银弹，尤其是在面对数据量庞大的表时。稍有不慎，就可能因为磁盘IO、内存不足、未结束的长事务或者复制延迟而引发线上问题。动手之前，务必做好这三项检查：

• 确认没有活跃的长事务：跑一下SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(NOW() - trx_started) > 60;。一个未提交的长事务会一直持有元数据锁，导致你的DDL操作排队甚至超时失败。
• 检查磁盘空间：即使是ALGORITHM=INPLACE，在创建索引等操作时也需要大量的临时排序空间。建议为tmpdir预留至少表大小20%的额外空间。
• 观察从库延迟：通过SHOW SLA VE STATUS\G查看Seconds_Behind_Master。Online DDL在主库可能很快完成，但在从库上，由于经典的SQL线程单线程回放机制，可能会造成严重的复制延迟和积压。

最稳妥的执行策略是什么？通常是先在从库上执行一次，确认没有异常、延迟没有飙升后，再在主库操作。或者，也可以使用pt-online-schema-change（来自Percona Toolkit）这类第三方工具来兜底。它通过创建影子表和触发器来模拟Online DDL，兼容性更强，但也会带来额外的性能开销和风险点，比如触发器失效或对binlog格式有要求。

说到底，线上DDL的麻烦，往往不是语法写错了，而是那些你没注意到的、正在某个角落运行着的未提交事务，或者那个凌晨启动的报表查询。这些看不见的“拦路虎”，才是真正需要警惕的。