MySQL 5.7的Online DDL:原子性与无锁的真相 MySQL 5.7的Online DDL不原子且非真正无锁,因ALGORITHM=INPLACE仅避免全表拷贝,仍需短暂LOCK=SHARED或EXCLUSIVE锁,且不支持事务回滚,失败易留半成品状态。 提起MySQL 5.7的Onli
MySQL 5.7在线修改大表结构:Online DDL原理与原子性改进详解
作者:技术拾光
时间:2026-05-06
浏览:388
文章目录
MySQL 5.7的Online DDL:原子性与无锁的真相
MySQL 5.7的Online DDL不原子且非真正无锁,因ALGORITHM=INPLACE仅避免全表拷贝,仍需短暂LOCK=SHARED或EXCLUSIVE锁,且不支持事务回滚,失败易留半成品状态。

提起MySQL 5.7的Online DDL,很多人的第一印象是“在线”、“无锁”。但真相是,它并不真正“原子”。即便使用了ALGORITHM=INPLACE,操作仍可能在执行中途失败,留下一个不完整的“半成品”表结构。这意味着,仅仅依赖这个特性是不够的,必须配合明确的参数控制和前置验证,才能将风险降到最低。
为什么 ALTER TABLE ... ALGORITHM=INPLACE 在 5.7 里不等于无锁
这里有个关键概念需要厘清:5.7版本支持的INPLACE,核心是避免全表数据拷贝,但这绝不代表全程无锁。实际上,它在操作的开始和结束阶段,仍然需要短暂地获取锁。这个锁可能是LOCK=SHARED(会阻塞写操作),也可能是LOCK=EXCLUSIVE(读写都会阻塞)。
- 添加二级索引:操作末尾会短暂加上
EXCLUSIVE锁,以完成元数据的最终切换。 - 修改
VARCHAR长度:即使只是增大长度、不重写行数据,也需要重写索引项,这会触发SHARED锁。 RENAME COLUMN:在5.7中这属于INPLACE操作,但它要求表上不能有长事务或未提交的DML,否则就会卡在等待MDL(元数据锁)的阶段。- 一个典型的征兆就是错误日志里出现
Waiting for table metadata lock。这往往不是你的语句写错了,而是有其他连接正占着锁。
ALGORITHM=INPLACE, LOCK=NONE 报错的常见原因
显式指定这两个参数,本意是追求最高的并发性。但在MySQL 5.7中,实际支持的范围相当有限,报错往往源于一些隐含的约束条件:
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported:这表明当前操作无法跳过数据重写。一个常见场景是,字段原本允许NULL值,你却想把它改为NOT NULL。ERROR 1847 (HY000): LOCK=NONE is not supported:即使算法本身支持,某些变更(例如修改主键、调整ROW_FORMAT)仍会强制要求共享锁。- 表引擎限制:Online DDL特性仅对
InnoDB引擎生效。如果表使用的是MyISAM或其他引擎,这个特性就失效了。 - 版本差异:MySQL 5.7.5之前的版本,不支持
LOCK=NONE与ADD COLUMN的组合;而稳定支持MODIFY COLUMN增大VARCHAR长度,则要等到5.7.23+版本。
如何判断一个 ALTER 是否真能在线执行
与其依赖文档里那长长的支持列表,不如用实际命令来试探,这往往更可靠:
- 试探性执行:直接在语句后加上
ALGORITHM=INPLACE, LOCK=NONE执行,看是否会立即报错。如果成功,可以通过查询information_schema.innodb_alter_table来确认操作进度。 - 清理长事务:对于大表,务必选择业务低峰期操作。执行前,先运行
SELECT * FROM information_schema.innodb_trx WHERE TIME > 60;,清理掉那些长时间运行的事务。 - 监控MDL锁:执行
SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_NAME = 'your_table';来监控元数据锁的状态(需要确保performance_schema已开启)。 - 注意复制格式:如果主库的binlog格式设置为
STATEMENT,那么某些INPLACE操作在从库上会退化为COPY算法,这可能导致主从延迟急剧上升。
大表改结构时,pt-online-schema-change 不是银弹
当原生Online DDL能力不足时,pt-online-schema-change(pt-osc)是常用的第三方工具。它通过创建影子表和触发器来绕过原生DDL的限制,但自身也存在一些硬伤:
- 触发器开销真实存在:原表上的每一条
INSERT、UPDATE、DELETE操作,都会触发一次向影子表的同步写入。当原表QPS很高时,CPU和I/O开销会非常明显。 - 外键约束处理复杂:它不能直接用于有外键引用的表。除非使用
--alter-foreign-keys-method=auto参数,但这又会额外锁定父表。 - 依赖主键或唯一索引:如果原表没有主键或唯一索引,pt-osc会拒绝执行。因为它依赖主键来对数据进行分块拷贝,没有主键就无法进行切片操作。
- 中断后的清理工作:如果执行过程中被中断,残留的
_table_new临时表和触发器需要手动清理,否则下次执行时会引发冲突。
话说回来,真正棘手的是那些“三不沾”的表:既不支持INPLACE、又无法安排停机窗口、同时还带有外键约束的大表。面对这种情况,往往没有一键解决的方案,只能拆解成多步操作:例如,先添加一个扩展字段(比如JSON类型),让业务层同时兼容新旧逻辑;然后逐步将数据迁移到新字段;最后再删除旧列。这个过程考验的不仅是技术,更是对业务影响的精细把控。
实用工具推荐
电子包浆
使用 ›绿化、压缩、噪点和虚拟水印,自由调节做旧程度。
图片压缩
使用 ›选择画质和输出格式,预览压缩前后的大小。