首先，用过数据库的小伙伴们（本文以 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）：

（引自：https://dev.mysql.com/doc/refman/5.5/en/alter-table.html）
以上是 MySQL 5.5 版本中的说明，而 MySQL 5.6 版本，则正式提出了 COPY 和 INPLACE 两种方式。

（引自：https://dev.mysql.com/doc/refman/5.6/en/alter-table.html）

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）：

（引自：https://dev.mysql.com/doc/refman/5.5/en/innodb-create-index-overview.html）
在 MySQL 5.5 的官方说明中，并没有明确说明 FIC 就是 INPLACE，并且此版本中的 FIC 只支持二级索引和辅助索引的增加和删除。而在 MySQL 5.6 官方说明中，则指出 Online DDL 特性基于 InnoDB FIC 构建。

（引自：https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html）
在 MySQL 8.0 的官方说明中，则指出了 FIC 是 Online DDL 的延伸和扩展。

（引自：https://dev.mysql.com/doc/refman/8.0/en/glossary.html）
所以，综上所述，“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）：

（引自：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html）
基于 Online 对比 COPY 和 INPLACE，COPY 方法从表中的数据导出来的存放位置叫作 tmp_table，这是一个临时表，是在 server 层创建的。INPLACE 方法从表中重建出来的数据是放在 tmp_file 里的，这个临时文件是 InnoDB 在内部创建出来的，整个 DDL 过程都在 InnoDB 内部完成。
Online DDL 实现过程主要包括三个阶段：Initialization 阶段， Execution 阶段，Commit Table Definition 阶段。我们看下官方给出的内容（MySQL 8.0）：

中文翻译如下图：

5.7 的 Online DDL 实现原理

1，PREPARE

• 创建 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 操作。

Percona 工具 PT-OSC(Percona Toolkit Online Schema Change)

首先简单说下 Percona Toolkit:

percona-toolkit 源自 Maatkit 和 Aspersa 工具，这两个工具是管理 mysql 的最有名的工具，但 Maatkit 已经不维护了，全部归并到 percona-toolkit。Percona Toolkit 是一组高级的命令行工具，用来管理 MySQL 和系统任务，主要包括：

1.验证主节点和复制数据的一致性 【pt-table-checksum、pt-table-sync】 2.有效的对记录行进行归档 【pt-archiver】 3.分析索引使用情况 【pt-index-usage】 4.总结 MySQL 服务器 【pt-summary】 5.从日志和 tcpdump 中分析查询 【pt-query-digest】 6.问题发生时收集重要的系统信息 【pt-stalk】 7.kill 符合条件的 mysql thread 【pt-kill】 8.在线修改表结构 【pt-online-schema-change】

这里，主要介绍在线修改表结构工具：PT-OSC(pt-online-schema-change)

该工具特点与优势：

1.支持并发 DML 操作

2.经过多年生产环境验证，可靠稳定。

PT-OSC 原理如下图：

PT-OSC 使用限制

1.原表不能存在触发器。

2.原表必须存在主键 PRIMARY KEY 或者 UNIQUE KEY

3.外键的处理需要指定 alter-foreign-keys-method 参数，存在风险

4.在 pt-osc 的执行过程中，如果有对主键的更新操作则会出现重复的数据,见下面链接的 bug 说明:

https://bugs.launchpad.net/percona-toolkit/+bug/1646713

PT-OSC 存在的风险

1. 触发器是以解释型代码保存的，因此每次查询都需要对触发器的代码进行解释的开销。
2. 触发器与原始查询共享相同的事务空间，原始查询在表上有锁竞争，触发器也会在另一张表上有锁竞争。同时，在触发器删除时同样会有元数据锁。
3. 触发器无法暂停，当主库 LOAD 变高，希望停止变更时，但触发器是不会停止。因此在整个操作过程中，触发器都会存在直到执行结束。

PT-OSC 使用示例

pt-online-schema-change  --alter="add c1 varchar(200) " \
--no-check-replication-filters  --recursion-method=none \
--user=root --password=123456--host=127.0.0.1 --port=3306  \
--set-vars innodb_lock_wait_timeout=50 --critical-load="Threads_running=200"  \
D=d1,t=t1--execute

这块各个参数的含义具体可以参见下官方文档，特别注意：

--critical-load

--max-load

--set-vars

这几个较少用到的参数

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

gh-ost 工作时可以连上多个 MySQL 实例，同时也把自己以从库的方式连上其中一个实例来获取二进制日志事件。根据你的配置、数据库集群架构和你想在哪里执行修改操作，可以有许多种不同的工作模式。

a. 连接到从库，在主库做迁移

这是 gh-ost 默认的工作方式。gh-ost 将会检查从库状态，找到集群结构中的主库并连接，接下来进行迁移操作：

• 行数据在主库上读写
• 读取从库的二进制日志，将变更应用到主库
• 在从库收集表格式，字段&索引，行数等信息
• 在从库上读取内部的变更事件（如心跳事件）
• 在主库切换表

如果你的主库的日志格式是 SBR，工具也可以正常工作。但从库必须启用二级制日志(log_bin, log_slave_updates) 并且设置 binlog_format=ROW ( gh-ost 是读取从库的二级制文件)。

如果直接在主库上操作，当然也需要二进制日志格式是 RBR。

b. 连接到主库

如果你没有从库，或者不想使用从库，你可以直接在主库上操作。gh-ost 将会直接在主库上进行所有操作。你需要持续关注复制延迟问题。

• 你的主库的二进制日志必须是 RBR 格式。
• 在这个模式中你必须指定 --allow-on-master 参数

c. 在从库迁移/测试

该模式会在从库执行迁移操作。gh-ost 会简单的连接到主库，此后所有的操作都在从库执行，不会对主库进行任何的改动。整个操作过程中，gh-ost 将控制速度保证从库可以及时的进行数据同步

• migrate-on-replica 选项让 gh-ost 直接在从库上修改表。最终的切换过程也是在从库正常复制的状态下完成的。
• test-on-replica 表明操作只是为了测试目的。在进行最终的切换操作之前，复制会被停止。原始表和临时表会相互切换，再切换回来，最终相当于原始表没被动过。主从复制暂停的状态下，你可以检查和对比这两张表中的数据

关于 GH-OST 数据时序的问题

对与 insert 和 update 是没有问题的，因为无论 copy old row 和 apply binlog 的先后顺序，如果 apply binlog 在后，会覆盖掉 copy old row，如果 apply binlog 在前面，copy old row 因为使用 insert ignore，因此会被 ignore 掉；(binlog 的数据优先级是最高的)

对与 delete 数据，我们可以演算一下，abc 三个操作，可能存在三种情况（b 肯定在 a 的后面）：

• a.delete old row
• b.delete binlog apply
• c.copy old row
• cab，c 会将数据 copy 到 ghost 表，最后 b 会把 ghost 表中的数据 delete 掉；
• acb，c 空操作，b 也是空操作；
• abc，b 空操作，c 也是空操作；

CUT OVER 阶段

cut-over 阶段（即 rename 表阶段）

1. 对应用请求的影响：所有请求在 cut-over 阶段直到临时表消失前会被阻塞，被释放后所有的请求都会在新表上执行，若 cut-over 阶段失败，则所有的请求一定会在旧表上执行。
2. 对主从复制的影响：备机只能看到 rename 操作，锁的 event 不会写入 binlog，因此备机看到的是一个原子性的操作。

GH-OST 使用限制：

1. 不能对有外键关系及触发器的表进行 Online DDL
2. 要求所连接的获取增量数据的 mysql binlog 为 row 格式(会强制转换)
3. 若有同名但是字母大小写不同的表如：MYtable 和 myTable，则无法对这两张表进行修改
4. 不支持 MySQL5.7 Json 类型列的修改
5. 不支持 MySQL5.7 generated column 的修改
   至此，我们对比下 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 的方式，实现不锁表的在线修改表结构。

Reference
https://segmentfault.com/a/1190000014924677
https://www.cnblogs.com/abclife/p/7505064.html
http://seanlook.com/2016/05/24/mysql-online-ddl-concept/
https://cloud.tencent.com/developer/article/1005177
https://www.cnblogs.com/xinysu/p/6732646.html
http://m.mamicode.com/info-detail-2311611.html
http://www.debugger.wiki/article/html/1550203202837500
https://www.cnblogs.com/TeyGao/p/9089997.html
https://www.w3xue.com/exp/article/20192/21034.html