数据库系列之数据库DDL操作算法介绍及影响分析

关注

原创最新推荐文章于 2026-10-15 12:02:49 发布 794 阅读 AI 写同款· GEO 优化 ›

PostgreSQL数据库元数据管理：注释规范与全库信息查询实战在关系型数据库设计中，元数据是描述数据的数据，是理解数据资产结构和业务逻辑的关键。其核心原理在于通过系统化的注释，将业务含义、设计意图和约束条件固化到数据库对象中，从而提升数据的可读性、可维护性和协作效率。从技术价值看，规范的元数据管理能有效避免因人员更迭导致的知识断层，是数据治理和团队协作的重要基础。在实际应用场景中，开发者和DBA需要频繁查询表结构、字段含义及关系，以支持日常开发、问题排查和文档生成。本文聚焦于PostgreSQL数据库，深入探讨如何利用`COMMENT ON`命令为表和字段添加注释，并阅读详情

MySQL 8.0.25版本InnoDB存储引擎支持COPY、INPLACE、INSTANT三种DDL执行算法，三种算法在实现机制、执行效率、锁粒度和业务影响方面存在显著差异。本文简要介绍这三种算法的实现原理、常见的DDL操作默认使用哪种算法以及业务影响如何。

1、DDL三种算法概述

MySQL的ALTER TABLE等DDL操作经历了三个阶段的演进：

阶段	版本	能力
全表拷贝	≤ 5.5	所有DDL均需重建表，执行期间阻塞读写
Online DDL	5.6 ~ 5.7	引入Inplace算法，多数DDL执行期间允许并发DML
Instant DDL	8.0.12+	引入Instant算法，纯元数据变更，毫秒级完成

在MySQL 8.0.25版本的InnoDB存储引擎中，支持三种DDL执行算法：COPY、INPLACE、INSTANT。三种算法在实现机制、执行效率、锁粒度和业务影响方面存在显著差异。从MySQL 8.0.12开始，对于任何支持的DDL操作，默认算法为INSTANT。MySQL会自动选择当前操作所支持的最高效算法，优先级为：INSTANT > INPLACE > COPY，也可通过ALGORITHM=子句显式指定。

1.1 COPY算法

COPY算法是最传统的DDL执行方式，在Server层完成表结构变更。其核心流程如下：

加锁阶段：DDL执行即刻获取表级排他MDL锁，此时所有针对该表的查询、写入操作全部挂起等待。
创建临时表：按照新的表结构创建一张临时表（.frm +表空间文件）
全量拷贝数据：逐行从原表读取数据，插入到临时表中
记录增量变更：DDL执行期间原表的DML操作会被阻塞
切换表文件：删除原表，将临时表重命名为原表名
重建索引：所有索引随数据拷贝一并重建
释放锁：元数据切换完成后释放排他锁，业务读写恢复正常。

COPY算法全程在Server层执行，需要调用引擎层接口逐行读写，过程中产生大量redo/undo日志，磁盘IO和CPU开销极高。

在这里插入图片描述

COPY算法是MySQL最原始的DDL实现方式，优点是兼容性最高、支持所有表结构变更；缺点是对业务影响极大，大表执行期间会导致长时间的业务中断，生产环境应尽量通过合理的表设计避免触发COPY算法。

1.2 INPLACE算法

INPLACE算法在InnoDB引擎层内部完成变更，避免了Server层逐行拷贝的开销。根据是否需要重建数据/索引，分为No-Build（仅元数据变更）和Build（数据/索引重建）两条执行路径，全程仅在提交阶段短暂持有排他锁。

1）准备阶段（Prepare）

持有共享MDL锁，允许并发读
创建临时索引文件/临时数据文件
分配Row Log缓冲区记录DDL期间的增量变更

2）执行阶段（Execute）

释放排他锁，允许并发DML读写
扫描原表数据，按新结构构建数据页或索引页
期间产生的DML增量写入Row Log缓冲区

3）提交阶段（Commit）

升级为排他MDL锁，短暂阻塞DML
应用Row Log中的增量变更到新结构
更新数据字典，切换表空间文件
清理临时文件

INPLACE并非完全"不产生临时文件"，而是在引擎层内部完成数据重组，避免了Server层的逐行拷贝开销。对于仅修改元数据的操作（如删除索引），则无需扫描数据。

在这里插入图片描述

1）No-Build模式（无重建）

仅修改InnoDB数据字典中的元数据定义，不扫描、不修改任何物理数据页
无Row Log缓冲区开销，执行速度快，通常秒级内完成
典型场景：删除普通索引、修改字段默认值、同字节区VARCHAR扩容

2）Build模式（重建型）

需要全量扫描原表数据，按目标结构重新构建数据页或索引页
执行期间通过Row Log缓冲区记录并发DML的增量变更，提交阶段统一回放应用
耗时与数据量正相关，但全程仅提交阶段短暂锁表
典型场景：新增普通索引、调整主键、表碎片整理、非末尾新增字段

1.3 INSTANT算法

INSTANT是MySQL 8.0.12引入的最轻量级DDL算法，仅修改数据字典中的元数据，完全不触碰实际数据页，通过元数据版本机制实现读写兼容，执行耗时与数据量无关。

InnoDB为每张表维护元数据版本号，DDL仅递增版本号并更新结构定义，不触碰磁盘数据
执行INSTANT DDL时，仅更新数据字典中的表结构定义（列信息、默认值、注释等）
数据页本身不做任何修改，旧数据行按旧格式存储
后续读取数据时，InnoDB根据数据字典的最新定义解析行数据。新增列时读取旧行时自动填充默认值（NULL或常量默认值）；删除列时读取时跳过已删除列的字段偏移。
后续写入新数据时，按最新表结构写入完整行格式

该算法的本质是"元数据先行，数据懒更新"。物理数据页的格式统一会在后续DML更新或OPTIMIZE TABLE时逐步完成。

在这里插入图片描述

1.4 三种算法业务影响对比

下图以时间轴横向推进的方式对比COPY、INPLACE、INSTANT三种算法在完整执行周期内的锁状态变化，以及对业务DML读写的阻塞窗口。

在这里插入图片描述

1）COPY算法：全程排他锁

锁持有周期：DDL语句执行开始即获取排他MDL锁，直到整个表拷贝、切换完成后才释放。
业务影响：
- 阻塞窗口 = 整个DDL执行时长，与数据量线性相关，大表可达数小时。
- 期间所有针对该表的SELECT/INSERT/UPDATE/DELETE全部挂起，连接堆积，严重时引发服务雪崩。
- 典型场景：跨越字节边界的VARCHAR扩容、修改字段类型、变更字符集等。

2）INPLACE算法：两短一长，仅提交瞬间阻塞

准备阶段（毫秒级）：持有可升级共享MDL锁，允许并发DML读写，仅阻塞其他DDL操作；主要工作是创建临时文件、分配Row Log缓冲区。
执行阶段（分钟～小时级）：
- 维持共享锁，完全允许并发DML，业务读写正常。
- 后台扫描数据构建新结构，同时通过Row Log记录期间的增量变更。
- 对业务的影响仅体现为磁盘IO升高、性能轻微下降，无阻塞。
提交阶段（毫秒～秒级）：
- 升级为排他MDL锁，短暂阻塞所有DML。
- 回放Row Log增量、切换表空间、更新数据字典、清理临时文件。
- 这是INPLACE唯一的业务阻塞窗口。
隐藏风险：若表上存在长事务未提交（持有共享MDL锁），提交阶段的排他锁请求会进入等待队列，进而阻塞后续所有DML，形成MDL锁队列雪崩。

3）INSTANT算法：毫秒级锁，业务无感知

锁持有周期：仅在更新数据字典的瞬间持有排他MDL锁，耗时通常 < 10ms，之后立即释放。
业务影响：
- 阻塞时间极短，正常业务完全无感知，不会造成连接堆积。
- 无数据扫描、无IO压力，对性能无影响。
注意：若表上存在运行中的长事务，INSTANT DDL同样会等待MDL锁，但等待的是事务结束，而非DDL本身执行慢。

4）以下是三种算法业务影响对比

在这里插入图片描述

1.5 常见DDL操作默认算法及时长

1.5.1 常见DDL操作默认算法

基于MySQL 8.0.25版本，以下列出各常见DDL场景的默认执行算法。如在末尾新增单个字段、修改字段默认值等都是默认INSTANT算法；AFTER语法新增字段、删除字段使用INPLACE（Rebuild）算法；修改字段类型、表字符集转换等使用COPY算法。

在这里插入图片描述

关于VARCHAR长度边界的补充说明。VARCHAR扩展规则核心是"长度前缀字节数是否变化"：0_{63用1字节、64}65535用2字节，同区间内扩展为Instant，跨区间为Copy；收缩VARCHAR长度同样需要Copy。VARCHAR 列的长度字节编码规则：

0~255字节：使用1字节存储长度信息
≥256字节：使用2字节存储长度信息

例如：

VARCHAR(10) → VARCHAR(50)（同属1字节区间）：INPLACE，仅元数据 
VARCHAR(300) → VARCHAR(500)（同属2字节区间）：INPLACE，仅元数据 
VARCHAR(50) → VARCHAR(100)（跨越1→2字节边界）：COPY，全表重建

以上按单字节字符集计算。若使用utf8mb4，每个字符占4字节，则VARCHAR(63)为252字节（1字节长度），VARCHAR(64)为256字节（2字节长度），即63→64会跨越边界触发COPY。

1.5.2 常见DDL操作执行时长参考

以百GB级InnoDB表（千万行级数据量）为参考基准。使用INSTANT算法基本是在10ms内完成，持有MDL锁对业务影响也在这个时间范围；INPLACE算法的DDL执行时间与表数据量正相关，如添加普通索引，需要重新Build index，耗时在索引构造这个过程，数据量越大耗时越长，对业务的影响是在prepare和commit期间持有锁，耗时在秒级；COPY算法的DDL执行耗时也与表数据量相关，并且在执行过程中阻塞业务。因此对于使用到COPY算法执行的DDL语句，需评估好停业窗口，或者采用工具将非在线DDL转换为在线DDL（在线DDL工具pt-osc/gh-ost等）、大表的DDL使用分阶段数据迁移方案等。

在这里插入图片描述

1.6 DDL风险提示

NSTANT 64次限制：每个表最多执行64次INSTANT变更，达到限制后需执行OPTIMIZE TABLE或ALTER TABLE … ENGINE=InnoDB重建表重置计数器
INSTANT静默降级：即使指定ALGORITHM=INSTANT，在表包含BLOB类型、全文索引或使用特殊存储格式时，可能静默降级为INPLACE
MDL锁链式阻塞：DDL等待MDL写锁时，会阻塞后续所有对该表的请求，可能导致连接池打满
高写入表：Inplace期间并发DML写入row_log，超过innodb_online_alter_log_max_size（默认 128MB）将导致DDL失败回滚。
磁盘空间校验：Rebuild和COPY类DDL会生成完整的临时表空间，大表操作前必须确认磁盘剩余空间 ≥ 原表大小的1.2倍，避免空间占满引发故障。

参考文档：

MySQL 8.0 Official Documentation: 17.12.1 Online DDL OperationsMySQL
MySQL 8.0 Official Documentation: 17.12.2 Online DDL Performance and ConcurrencyMySQL
MySQL Blog: MySQL 8.0 INSTANT ADD and DROP Column(s)MySQL
数据库系列之数据库中大表DDL变更问题
数据库系列之InnoDB中在线DDL实现机制