浅谈DDL技术解密

浅谈DDL技术解密

首先，用过数据库的小伙伴们(本文以 MySQL InnoDB 为例)都知道，MySQL 不止有增删改数据操作(DML)，还有改表结构的操作(DDL)，当新增加字段等修改表结构时，就需要进行 DDL 操作。可是，如果对一个存储了上百万甚至上千上万的数据表进行 DDL 操作，数据库是怎么做到的呢?会不会有一个很大的事务锁?会不会影响数据的插入和更新?今天就会聊聊这个问题，以及 PT-OSC、GH-OST 等技术，是如何高效的解决这个问题的。

Before MySQL 5.5

在 MySQL 5.5 版本及之前版本，DDL 操作主要有 copy table 和 inplace 两种方式。

1. Copy Table 方式

Copy Table 顾名思义，就是通过临时表拷贝的方式实现的。在 MySQL 5.5 版本及之前版本，修改表结构是表级锁，所以在整个 DDL 过程中表都是锁着不可写入的。这使得在修改时容易导致数据库 CPU、IO 等性能的消耗，以及主从同步的延迟。

上述过程，MySQL 自动完成转存数据，交换表名和删除旧表等操作，时间消耗最多的是在往临时表(Server 层)插入数据的过程，整个 DDL 过程中，表是不能执行 DML 的。

2. IN-Place 方式

在 MySQL 5.5 版本中，增加了 IN-Place 方式。所谓 IN-Place 方式，就是索引创建在原表上直接进行，不会 copy 整个表，只需要在原来的 idb 文件上，新建所需要的索引页，这比 Copy Table 节约极大的 IO 资源，且减少了 DDL 执行时长。

对比 Copy Table 和 IN-Place 两种方式，我们看下官网的内容(MySQL 5.5)：

以上是 MySQL 5.5 版本中的说明，而 MySQL 5.6 版本，则正式提出了 COPY 和 INPLACE 两种方式。

3. Fast Index Creation(FIC)

Innodb 存储引擎从 1.0.x 版本开始，对添加索引操作引入了新特性 Fast Index Creation(FIC 特性)。FIC 就是添加或删除二级索引的时候，可以不用复制原表，而是在创建或删除二级索引时会对原表加上一个 S 锁(共享锁)，允许其他会话进行读操作，但禁止写操作，根据当前表数据创建索引，新索引创建完成之后，解除 S 锁，允许写操作。

FIC 在创建索引时不需要拷贝整表数据，但只对二级索引有效，对主键索引无效，对于主键索引的创建和删除同样需要重建一个临时表。

对比 IN-Place 和 FIC，在网上查了一些资料，说“INPLACE 方式也称为 InnoDB fast index creation”，那两个应该不是一回事?我们看下官网的内容(MySQL 5.5)：

在 MySQL 5.5 的官方说明中，并没有明确说明 FIC 就是 INPLACE，并且此版本中的 FIC 只支持二级索引和辅助索引的增加和删除。而在 MySQL 5.6 官方说明中，则指出 Online DDL 特性基于 InnoDB FIC 构建。

在 MySQL 8.0 的官方说明中，则指出了 FIC 是 Online DDL 的延伸和扩展。

所以，综上所述，“INPLACE 方式也称为 InnoDB fast index creation”这句话是对的，Online DDL 方式延伸了 Fast Index Creation，并逐渐扩展了 FIC 的范围。

Since MySQL 5.6

在 MySQL 5.6 版本，引入了 Online DDL，这个新特性解决了早期版本 MySQL 进行 DDL 操作时带来的锁表问题，Online DDL 执行的过程中依然保证可以读写，不影响数据库对外提供服务。

Online DDL

Alter table …. , ALGORITHM [=] {DEFAULT|INPLACE|COPY}, LOCK [=] { DEFAULT| NONE| SHARED| EXCLUSIVE }

ALGORITHM 子句指定执行 DDL 采用的方式，LOCK 子句描述持有锁类型来控制 DML 的并发。其中，某些 DDL 语句不支持 Online DDL 的采用 COPY 方式，支持的就采用 INPLACE 方式，因为 Online DDL 是对早期 INPLACE 方式的增强，所以 INPLACE 方式根据是否涉及记录格式的修改又分为：Rebuilds Table 和 No-Rebuilds Table，我们看下官方给出的内容(MySQL 5.7)：

基于 Online 对比 COPY 和 INPLACE，COPY 方法从表中的数据导出来的存放位置叫作 tmp_table，这是一个临时表，是在 server 层创建的。INPLACE 方法从表中重建出来的数据是放在 tmp_file 里的，这个临时文件是 InnoDB 在内部创建出来的，整个 DDL 过程都在 InnoDB 内部完成。

Online DDL 实现过程主要包括三个阶段：Initialization 阶段， Execution 阶段，Commit Table Definition 阶段。我们看下官方给出的内容(MySQL 8.0)：

我们详细描述下这三个过程的过程：

(1) Initialization

创建 frm 临时文件持有EXCLUSIVE_MDL锁，禁止读写根据 ALTER 操作，确定执行过程(COPY，Online-Rebuilds，Online-No-Rebuilds)更新数据字典的内存对象若是需要 Rebuilds，分配 row_log 对象记录增量若是需要 Rebuilds，新建 ibd 临时文件

(2) Execution(如果仅修改 MetaData，则无此部操作)

降低EXCLUSIVE-MDL锁，允许读写(COPY 仅允许读)记录执行期间产生的 DML 操作到 row_log(仅 Rebuilds 需要)扫描老表的聚集索引中每一条记录 record遍历新表的聚集索引和二级索引，逐一处理根据 record 构造对应的索引项将构造的索引项插入 sort_buffer 块中将 sort_buffer 块插入新的索引将 row_log 中的记录应用到新临时表，应用到最后一个 Block

(3) Commit Table Definition

升级到EXECLUSIVE-MDL锁，禁止读写重做 row_log 中最后一部分增量更新 InnoDB 的数据字典提交事务，写 InnoDB redo 日志修改统计信息Rename 临时的 ibd 和 frm 文件DDL 执行变更

我理解，Online DDL 中的 COPY 和 INPLACE 的区别在于有没有原地，COPY 会将数据从 InnoDB 存储层 copy 到 Server 层，而 INPLACE 不会;而 INPLACE 中的 Rebuilds 和 No-Rebuils 的区别在于，有没有重建表。

PT-Online-Schema-Change(PT-OSC)

全称 Percona Toolkit Online Schema Change，其中 Percona Toolkit 源自 Maatkit 和 Aspersa 工具，这两个工具是管理 MySQL 最有名的工具，但 Maatkit 已经不维护了，全部归并到 Percona Toolkit。Percona Toolkit 是一组高级的命令行工具，用来管理 MySQL 和系统任务。

PT-OSC(pt-online-schema-change)工具特点与优势是支持并发 DML 操作。

GitHub’s Online Schema Transformer(GH-OST)

GH-OST 是 GitHub 的在线表定义转换器，与 PT-OSC 的最大区别，在于 GH-OOST 的无触发器设计。

至此，我们对比下 Online DDL、PT-OSC 和 GH-OST 的优缺点：

(引自：吴夏《在线DDL原理、对比分析和实践》)

总结

传统的 DDL，多数的 ALTER TABLE 操作是通过创建一个满足需求的新表，之后拷贝数据到新表，在用新表替换老表，整个过程会加锁，不支持并发 DML。在 MySQL 5.5 版本中，以 InnoDB Plugin 方式，优化了新增和删除索引的操作，避免了这种数据 copy 的开销，出现了 FIC。在 MySQL 5.6 开始增强了对各种 ALTER TABLE 操作支持，避免数据 copy 的开销，同时允许在 DDL 进行中，并发执行 DML 操作。在 MySQL 5.7 实现了 ALTER TABLE RENAME INDEX 操作，即支持在线的索引重命名，这种特性的综合，即 ONLINE DDL。PT-OST 通过改造原生 DDL 的方式，实现不锁表的在线修改表结构。

戳这里，看该作者更多好文

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。