别踩雷！mysql 加字段隐藏了4个巨大风险！这可能是目前最全面地分析

本篇文章主要帮助大家梳理清楚以下问题。

• 增删索引和字段会影响DML吗？会锁表吗？可以立即生效吗？
• 增删索引和字段时会导致主从延迟吗？还有哪些风险需要注意？
• MySQL 常见的DDL 工具、适用场景和原理。

一、名词解释

• DDL: 数据定义语言（Data Definition Language）的缩写。它用于定义和管理数据库中的数据结构，例如增加删除字段，增加删除索引。
• DQL：数据查询语言，主要是对表中数据的查询（select）操作。
• DML: 数据操作语言（Data Manipulation Language）的缩写，主要是表中数据的增删改。
• MDL: 元数据锁，在server层加的，适用于所有存储引擎 主要用于保障 MySQL表结构一致性。

二、 MDL 锁

MDL 分共享锁和排它锁，共享锁之间可共享，共享锁和排它锁互斥，排它锁和排它锁互斥。

DML、DQL 语句会申请共享锁，DDL语句执行期间可能申请排它锁，也可能申请共享锁。 当 DDL 申请共享锁时，可以执行 DML 语句，表可以并发写入；当DDL申请排他锁时，DML 语句会被阻塞住，表不能读写。

是否会锁表取决于DDL的执行方式。 MySQL 不同版本的 DDL 执行原理不同，所以同一个 DDL 语句在不同的 MySQL 版本，会有不同的表现（是否锁表）。

三、 DDL 的 4 种执行原理

3.1 Copy 方式

1. 创建新的临时表，复制原表结构，并执行 DDL 语句（新增新字段、索引）
2. 把原表中数据导入到临时表（阻塞原表写操作……）
3. 删除原表
4. 最后重命名临时表为原表名

应用Copy 方式，执行DDL时，会生成临时表，将原表数据逐行拷贝到新表中，在此期间会阻塞DML。

当采用COPY模式时，在这种情况下，DML语句会被阻塞。在COPY模式下，会先生成一个临时新表，完成操作后原表会被删除，新表会被重命名为原表名。一旦DDL操作开始，原表仅能进行只读操作，其他的DML操作会被阻塞。在COPY过程中，唯一会阻塞只读操作的时机是在清理旧表的结构和表定义缓存时。

在MySQL 5.6.7 版本之前，DDL操作采用Copy方式执行。这也意味着，无论是增加、删除索引还是添加、删除字段，所有这些DDL操作都会对表的写操作造成阻塞。即 DDL 会导致锁表。

3.2 InPlace 原地更新

无需拷贝全表数据，但是需要原地更新，这种情况下，在DDL的初始准备和结束时需要加MDL锁，metadata lock即元数据锁， 加元数据锁时，会阻塞 DML，除这些短暂时间，DDL期间不会阻塞DML。

Inplace 虽然不会建临时表，但是依然可能需要拷贝数据。如 mysql 5.7中，新增列和删除列需要重建表。因为需要拷贝数据，所以mysql 无法立即完成此类操作，但是不会阻塞 DML 语句。

3.2.1 Rebuild 重建表

Rebuild过程如下

• 新建临时文件，.frm和.ibd，临时文件的表结构包含新增的列
• 扫描原表的所有数据页，基于原表生成新的 B+树结构，存储到临时文件中
• 在重建临时文件时，原表DML 语句会记录到 日志文件中（row log）。
• 原表数据全部重建完成后，将日志文件（row log）应用到临时文件中。此时该日志文件相比原表增加了一列。
• 使用临时文件替换原表的数据文件和表结构文件。

需要注意的是：

• 拷贝期间虽然允许原表的 DML，但是不允许原表执行其他 DDL 语句。
• Copy方式和 Inplace 的区别？
  • copy：通过增加临时表，拷贝历史数据。是 MySQL server层操作的，并且通过 MDL 排它锁阻塞了 其他DDL 和 DML 语句，整个表被锁住，无法并发写入数据。
  • inplace: MySQL innodb存储引擎层，通过临时文件方式重建原表。 仅申请了 MDL 共享锁，阻止其他 DDL 语句，而不阻止 DML 语句，可并发写数据。

值得一提的是 innodb rebuild 方式在提交阶段会短暂的申请 MDL 写锁，由于MDL的共享锁和排它锁是互斥的，所以在此阶段 是不允许同时 DML读写表的。

为什么呢？如果在事务执行期间，发生表结构的改变，就会导致同一个事务中，出现混乱。 例如第二次查询时，多一列数据，则无法保证可重复读的事务隔离级别。

因此当前有事务持有 MDL 读锁，DDL 就不能申请 MDL 写锁，以保护元数据。在业务高峰期存在频繁的请求时，可能导致迟迟申请不到 MDL 写锁，出现DDL 长期等待的情况，尽量不要在高峰期执行 DDL 语句。

3.2.2 DDL 期间 主从延迟问题

MySQL 官方文档中指出： 长时间运行的DDL 操作可能会导致主从复制滞后。 DDL 操作必须在主服务器上完成，然后才能在从服务器上执行。此外，主服务器上并发处理的 DML 仅在从服务器上的 DDL 操作完成后才会在从服务器上处理。

因此 MySQL 5.6.7 至 5.7 版本，Online DDL新增列时，如果表太大，DDL执行时间太长，会导致主从延迟时间增大，存在数据一致性风险，对业务有较大影响。

3.2.3 无法对 DDL 限速

MySQL 官方文档指出： 没有机制可以暂停Online DDL 操作或限制 Online DDL 操作的 I/O 或 CPU 使用率。

3.2.4 DDL 期间出现唯一键冲突，导致 DDL 失败

在新增字段期间，若出现 Duplicate entry 等唯一键冲突，会导致 DDL 执行失败。

如前面所述，在 InPlace 原地更新方式执行时， 原表数据全部重建完成后，将日志文件（row log）应用到临时文件中，Innodb 会缓存期间的 DML 语句，然后在临时文件中应用 DML 语句。 如果此时有 insert SQL 存在唯一键冲突，就会导致 DDL 执行失败。

需要特别强调的是：并非仅限于添加唯一索引时，恰好遇到了此唯一索引插入冲突，才会导致 DDL 失败。 只要 DDL 期间执行的 SQL 有唯一键冲突情况，都会导致 DDL 失败。

由于 DDL未执行成功，还未修改原表，因此不会影响业务运行，但是会导致 DDL 执行失败。（谁也不希望上线前，遇到这种事情~）

在MySQL Bug网站上，官方人员回复该现象并不是Bug，而是一种限制。

具体过程，可以参考网友的踩坑经验: blog.csdn.net/qq_39409031…

有什么办法能优化此类问题？有另外两种 DDL 方式可以解决此类问题。

1. MySQL 8.0 版本后 Online DDL支持 Instant 方式，瞬时新增列，可极大降低此类问题的概率
2. gh-ost 等开源 DDL 执行工具可以避免此类问题。

四、 MySQL 8.0 Online DDL Instant

4.1 什么是 Online DDL

MySQL 5.6.7 之前，在进行 DDL 操作的时候，整个表都会被阻塞，当用户或业务对表进行 DML 操作时就会被阻塞。为此 5.6.7 版本及以后，可以支持 DDL 操作期间，允许 DML 操作同时进行，减少对业务的影响。

Online DDL在 5.6.7 至 8.0.12 版本之前，使用 InPlace 的执行方式替代了 Copy的 DDL 执行方式。

然而正如前述，InPlace 方式依然需要复制数据，且大表长时间的 DDL 会导致主从延迟等问题，MySQL 8.0.12 版本提供了 Instant 方式执行 DDL。

4.2 Instant 瞬间完成 DDL

Instant方式只需修改数据字典中的元数据，无需拷贝数据也无需重建整表，整个DDL过程几乎是瞬间完成的，也不会阻塞DML。这个新特性是8.0.12 后引入的。 在MySQL 8.0.29之前，新增的列只能添加到表的最后一列。从MySQL 8.0.29开始，新增的列可以添加到表的任意位置。

由于仅修改了元数据，所以 DDL执行速度很快。先前Online DDL 派生出的主从延迟问题、限速问题、唯一键冲突问题 都迎刃而解。（然而并非所有的 DDL都能 Instant执行，参考第七节的 DDL 能力地图）

MySQL 在官方文档里特别感谢了腾讯游戏 DBA 团队贡献的Instant 特性。

4.3 DDL 原子能力

原子DDL是由MySQL 8.0中引入的MySQL数据字典实现的。

在早期的MySQL版本中，元数据存储在元数据文件、非事务表和存储引擎特定的字典中，这可能会导致部分提交。在之前，MySQL DDL 执行期间如果恰好MySQL 崩溃，可能会导致数据无法恢复。由MySQL数据字典提供的集中、事务性的元数据存储消除了这类问题，使得原子化地重新构建DDL语句成为可能。

一个原子DDL语句将与DDL操作相关的数据字典更新、存储引擎操作和二进制日志写入组合成一个单独的原子操作。该操作要么提交，将更改持久保存到数据字典、存储引擎和二进制日志中；要么回滚，即使在操作过程中服务器停机。

参考： dev.mysql.com/doc/refman/…

五、 MySQL DDL 能力时间线

想必大家已经被各种版本号搞蒙了，为此五阳特地制作了一个时间线，标注每个版本的 DDL执行方式。

六、 MySQL 5.7 Online DDL 能力地图

接下来，我从官方文档挑选了关键DDL 操作的执行特性。

6.1 列操作

可以看到新增列的时候需要重建表，遇到行数多的大表，耗时会很久。

列操作	In Place	Rebuilds Table	并发 DML	仅修改元数据
新增列	Yes	Yes	Yes*	No
删除列	Yes	Yes	Yes	No
重命名列	Yes	No	Yes*	Yes
设置列默认值	Yes	No	Yes	Yes
修改类型	No	Yes	No	No
增加VARCHAR 类型长度	Yes	No	Yes	Yes
清楚列的默认值	Yes	No	Yes	Yes
修改 AutoIncrement值	Yes	No	Yes	No*
设置某列可为 NULL	Yes	Yes*	Yes	No
设置某列未为 NOT NULL	Yes*	Yes*	Yes	No

6.2 索引操作

索引操作	InPlace	Rebuilds Table	并发 DML	仅修改元数据
创建非主键索引	Yes	No	Yes	No
删除非主键索引	Yes	No	Yes	Yes
重命名索引	Yes	No	Yes	Yes
创建全文索引	Yes*	No*	No	No
创建空间索引	Yes	No	No	No
修改索引类型	Yes	No	Yes	Yes

6.3 表操作

表操作	In Place	Rebuilds Table	并发 DML	仅修改元数据
修改 ROW_FORMAT	Yes	Yes	Yes	No
修改 KEY_BLOCK_SIZE	Yes	Yes	Yes	No
指定 character set	Yes	Yes*	Yes	No
修改 character set	No	Yes*	No	No
Optimizing table	Yes*	Yes	Yes	No
rename Table	Yes	No	Yes	Yes

参考 MySQL 官方文档：dev.mysql.com/doc/refman/…

七、MySQL 8.0 Online DDL 能力地图

7.1 列操作

可以看到 MySQL 8.0后，增加了 Instant执行方式，新增列再也不需要等待那么久了。

列操作	Instant	In Place	Rebuilds Table	并发 DML	仅修改元数据
添加列	Yes*	Yes	No*	Yes*	Yes
删除列	Yes*	Yes	Yes	Yes	Yes
列重命名	Yes*	Yes	No	Yes*	Yes
设置默认值	Yes	Yes	No	Yes	Yes
修改列类型	No	No	Yes	No	No
增加 VARCHAR 长度	No	Yes	No	Yes	Yes
清除列默认值	Yes	Yes	No	Yes	Yes
修改 Auto-increment值	No	Yes	No	Yes	No*
设置列为 NULL	No	Yes	Yes*	Yes	No
设置列NOT NULL	No	Yes*	Yes*	Yes	No

7.2 索引操作

索引操作	Instant	In Place	Rebuilds Table	并发 DML	仅修改元数据
创建非主键普通索引	No	Yes	No	Yes	No
删除非主键普通索引	No	Yes	No	Yes	Yes
索引重命名	No	Yes	No	Yes	Yes
创建全文索引	No	Yes*	No*	No	No
创建空间索引	No	Yes	No	No	No
修改索引类型	Yes	Yes	No	Yes	Yes

7.3 表操作

表操作	Instant	In Place	Rebuilds Table	并发 DML	仅修改元数据
修改 ROW_FORMAT	No	Yes	Yes	Yes	No
修改 KEY_BLOCK_SIZE	No	Yes	Yes	Yes	No
指定 character set	No	Yes	Yes*	Yes	No
修改 character set	No	No	Yes*	No	No
Optimizing a table	No	Yes*	Yes	Yes	No
rename Table	Yes	Yes	No	Yes	Yes

参考 MySQL 官方文档： dev.mysql.com/doc/refman/…

八、外部 DDL 工具

在 MySQL 8.0之前，MySQL online DDL虽然解决了 DDL 期间，无法并发写入即锁表的问题，但是依然存在主从延迟问题，尤其是对大表执行 DDL时，问题会更加明显。

因此有第三方提供了在线改表工具，解决此类问题。如 GH－OST（gitHub’s online schema transformer），由github 提供的在线修改表结构的工具。它的过程大致如下

8.1 gh-ost 执行过程

1. 创建幽灵表：_xxx_gho（和原表结构一致），_xxx_ghc（用于记录变更日志）
2. 在步骤 1 中创建的幽灵表上执行 DDL 语句
3. 模拟成备库连接到真正的主库或备库
4. 将数据从原表拷贝到幽灵表
5. 应用 binlog events 到幽灵表
6. 完成切换（cut-over）

详细了解原理点击

在最后一步 Cut over 之前，原表可以正常执行 DML 语句，业务不受影响，也没有主从延迟问题。在 Cut Over阶段，通过 MDL排它锁防止并发问题，获取 MDL 锁后，会执行 rename 操作，将原表和幽灵表互换。

需要注意的是：MDL排它锁对 DML、DQL 语句是互斥的，需要等待所有存量 DML、DQL执行完成，才能完成 cut-over，为了尽快完成 DDL 操作，应该避免在 业务高峰期 执行 DDL。

此外由于 ghost 采用 binlog 方式追加应用DDL期间的 DML 语句。在业务高峰期执行时，可能会遇到业务写表太快，gh-ost同步 binlog 追不上的问题。 业界一般认为，当binlog 写入超过 500/s时，就可能导致追不上。

还有另一种 外部DDL 工具， pt-ost可解决追不上的问题。它的执行原理和 gh-ost类似，pt-ost是基于触发器同步更新数据的（gh-ost是 binlog），即对原表的 INSERT/UPDATE/DELETE 语句添加触发器，只要遇到 DML 语句，就在幽灵表（影子表）同步执行该 SQL。然而此种方式会增加业务写入耗时。

8.2 gh-ost优势

gh-ost相比 MySQL DDL有如下优势

1. gh-ost 执行原理和过程统一，MySQL 不同版本，DDL 执行方式不同。
2. gh-ost 无主从延迟问题
3. gh-ost 可以进行限速。拷贝原表数据和 应用binlog时 可自行控制速率，必要时可暂停。online ddl无法限速。

九、选用标准

MySQL Online DDL在不同版本的原理和效果不同，还有外部工具 gh-ost、pt_osc 可供选择，因此需要有一个使用标准。

1. MySQL 5.6.7 以前，选用外部 DDL 工具，gh-ost或 pt-osc

2. MySQL 5.6.7及以后版本，5.7.x版本

   • 若 Online DDL需要Rebuild表，耗时可能很长，选择第三方工具；
   • 若不需要 rebuild 表，耗时较短，可以使用 MySQL online DDL。

3. MySQL 8.0 及以后

   • 若DDL 执行方式是 Instant，使用 Online DDL。
   • 若 DDL 执行方式非 Instant，则使用第三方工具。

我的开源项目

最后夹带一点私货，五阳最近花了3个月的时间完成一个开源项目。

开源3周以来，已有近 230 多个关注和Fork

Gitee：gitee.com/juejinwuyan…

GitHub github.com/juejin-wuya…

开源平台上有很多在线商城系统，功能很全，很完善，关注者众多，然而实际业务场景非常复杂和多样化，开源的在线商城系统很难完全匹配实际业务，广泛的痛点是

• 功能堆砌，大部分功能用不上，需要大量裁剪；
• 逻辑差异点较多，需要大量修改；
• 功能之间耦合，难以独立替换某个功能。

由于技术中间件功能诉求较为一致，使用者无需过多定制化，技术中间件开源项目以上的痛点不明显，然而电商交易等业务系统虽然通用性较多，但各行业各产品的业务差异化极大，所以导致以上痛点比较明显

所以我在思考，有没有一个开源系统，能提供电商交易的基础能力，能让开发者搭积木的方式，快速搭建一个完全契合自己业务的新系统呢？

• 他们可以通过编排和配置选择自己需要的功能，而无需在一个现成的开源系统上进行裁剪
• 他们可以轻松的新增扩展业务的差异化逻辑，不需要阅读然后修改原有的系统代码！
• 他们可以轻松的替换掉他们认为垃圾的、多余的系统组件，而不需要考虑其他功能是否会收到影响

开发者们，可以择需选择需要的能力组件，组件中差异化的部分有插件扩展点能轻松扩展。或者能支持开发者快速的重新写一个完全适合自己的新组件然后编排注册到系统中？

memberclub 就是基于这样的想法而设计的。 它的定位是电商类交易系统工具箱， 以SDK方式对外提供通用的交易能力，能让开发者像搭积木方式，从0到1，快速构建一个新的电商交易系统！

具体介绍可参见

Gitee开源地址：gitee.com/juejinwuyan…

GitHub开源地址 : github.com/juejin-wuya…

在这个项目中你可以学习到 SpringBoot 集成 以下框架或组件。

1. Mybatis、Mybatis-plus 集成多数据源
2. Sharding-jdbc 多数据源分库分表
3. redis/redisson 缓存
4. Apollo 分布式配置中心
5. Spring Cloud 微服务全家桶
6. RabbitMq 消息队列
7. H2 内存数据库
8. Swagger + Lombok + MapStruct

同时你也可以学习到以下组件的实现原理

1. 流程引擎的实现原理
2. 扩展点引擎实现原理
3. 分布式重试组件实现原理
4. 通用日志组件实现原理 参考：juejin.cn/post/740727…
5. 商品库存实现原理： 参考：juejin.cn/post/731377…
6. 分布式锁组件： 参考：
7. Redis Lua的使用
8. Spring 上下文工具类 参考： juejin.cn/post/746927…