MySQL5.6-5.7-8.0在线DDL操作锁表介绍
目录
一、Mysql5.6-8.0在线DDL操作是否锁表介绍
time:2025/05/07
Author:skatexg
重要
所有的DDL操作均建议在业务低峰期进行，以避免对业务产生影响。
一、Mysql5.6-8.0在线DDL操作是否锁表介绍
| 操作 | 是否需要重建表(影响性能) | MySQL5.6是否允许并发DML | MySQL5.7是否允许并发DML | MySQL8.0是否允许并发DML | 备注 |
| 创建普通索引 | 不需要 | 允许 | 允许 | 允许 | 无。 |
| 创建全文索引 | 不需要 | 不允许 | 不允许 | 不允许 | 第一个全文索引需要通过Copy Table的方式创建；其后的全文索引可以通过Inplace方式创建。 |
| 删除索引 | 不需要 | 允许 | 允许 | 允许 | 仅修改表元数据metadata。 |
| 优化表 | 需要 | 允许 | 允许 | 允许 | 如果表上创建有全文索引，则不支持algorithm=inplace选项。 |
| 设置列默认值 | 不需要 | 允许 | 允许 | 允许 | 仅修改表元数据metadata。 |
| 修改自增列值 | 不需要 | 允许 | 允许 | 允许 | 仅修改表元数据metadata。 |
| 添加外键约束 | 不需要 | 允许 | 允许 | 允许 | set foreign_key_checks=0; 来关闭 foreign_key_checks，避免拷贝表。 |
| 删除外键约束 | 不需要 | 允许 | 允许 | 允许 | foreign_key_checks选项开启或者关闭都可以。 |
| 重命名列 | 不需要 | 允许 | 允许 | 允许 | 如果仅仅修改字段名称，而不要修改字段类型，是支持并发DML操作的。 |
| 添加列 | 需要 （mysql8.0不需要） | 允许 | 允许 | 允许 | 在添加auto_increment自增列时，是不允许并发 DML 操作的。 尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。 |
| 删除列 | 需要 | 允许 | 允许 | 允许 | 尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。 |
| 修改各列顺序 | 需要 | 允许 | 允许 | 允许 | 尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。 |
| 修改Row_Format属性 | 需要 | 允许 | 允许 | 允许 | 尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。 |
| 设置列为空值Null | 需要 | 允许 | 允许 | 允许 | 尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。 |
| 设置列不为空值NOT Null | 需要 | 允许 | 允许 | 允许 | 该操作需要将SQL_MODE 参数设置为STRICT_ALL_TABLES或STRICT_TRANS_TABLES才能成功。如果列值中包含空值（NULL），则该DDL 操作会失败。 尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。 |
| 修改列的数据类型 | 需要 | 不允许 | 不允许 | 不允许 | 无。 |
| 扩展VARCHAR列大小 | 需要 （mysql8.0不需要） | 不允许 | 允许 | 允许 | 无 |
| 添加主键 | 需要 | 允许 | 允许 | 允许 | 尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。 如果涉及的列需要转换为NOT NULL，则不支持Algorithm=INPLACE。 |
| 删除主键并添加新主键 | 需要 | 允许 | 允许 | 允许 | 仅当在同一个Alter Table语句中（删除主键的DDL语句）添加新主键才支持Algorithm=INPLACE。 因为数据实质上需要重新组织，因此操作的开销高昂。 |
| 删除主键 | 需要 | 不允许 | 不允许 | 不允许 | 无。 |
| Convert character set | 需要 | 不允许 | 不允许 | 不允许 | 如果新的字符集编码不同，需要重建表。 |
| 重建表 alter table ... engine=innodb | 需要 | 允许 | 允许 | 允许 | 如果表上有全文索引，则不支持Algorithm=Inplace选项。 |
| 修改表注释 | 不需要 | 允许 | 允许 | 允许 | 无。 |
- 从MySQL5.6升级到MySQL5.7降低阻塞概率，并提升性能
- MySQL官方文档请参见Online DDL 概览
- MySQL的DDL不同变更模式的对比（如下）
| 对比维度 | INSTANT | INPLACE | COPY TABLE |
| 定义 | 仅更新数据字典（元数据），不涉及表数据的物理修改或移动，操作耗时极短（毫秒级）。 | 在原表位置直接修改表结构，可能重建索引或重组数据，但不复制全表数据。 | 创建临时表（目标结构），将原表数据全量复制到临时表，再替换原表。 |
| 实现原理 | 仅修改元数据，不接触实际数据。 | 原表直接操作，可能重建索引 / 重组数据。 | 全量复制数据到临时表，再替换原表。 |
| 适用场景 | 元数据变更（如添加 nullable 列、改默认值）。 | 结构变更（如改列类型、增删索引、重命名）。 | 复杂变更或旧版本兼容，需全量重建表。 |
| 性能 | 最优（毫秒级，几乎无影响）。 | 中等（依赖数据量，需部分 IO/CPU）。 | 最差（全量复制，耗时久、资源占用高）。 |
| 空间占用 | 几乎不占用额外空间。 | 少量临时空间（用于索引重建等）。 | 需约 1 倍原表空间（临时表存储）。 |
| 支持版本 | MySQL 8.0.12+ | MySQL 5.6+（随版本优化） | 所有版本（传统模式） |
| 锁与阻塞 | 仅短暂元数据锁，不阻塞读写。 | 可能加排他锁（DDL 期间锁表时间较短）。 | 锁表时间长（复制数据时阻塞写操作）。 |
| 数据影响 | 无数据移动或转换。 | 部分数据可能重组（如索引碎片整理）。 | 全量数据复制，可能涉及字符集转换等。 |
---end---