MySQL Online DDL

更多腾讯海量技术文章，请关注云加社区：https://cloud.tencent.com/developer

作者：黄稚禹

Online DDL in MySQL5.5

历史上看，MySQL 在 2007 年就完成了在线索引接口的设计。而 MySQL NDB Cluster、TokuDB 都早在 5.1 版本中就支持在线索引添加，然而直到 MySQL 5.6 用户才能真正享受到该功能（虽然 facebook 团队有推出 OSC），这也导致在 MySQL5.6 出现之前(5.5 版本及之前版本)，MySQL 数据库长期被吐槽的原因之一（特别是 Oracle DBA）

MySQL5.5 版本及之前版本的 DDL 实现方式：

上图不难看出，5.5 及之前版本 DDL 实现的方式存在如下问题：

copy data 的过程需要耗费额外的存储空间，并且耗时很长。

copy data 的过程有写锁，无法持续对业务提供正常服务。

虽然在 MySQL5.5 版本中增加了 IN-Place 方式，但依然会阻塞 INSERT、UPDATE、DELETE 操作

Online DDL in MySQL5.6

MySQL5.5 中对添加索引操作引入了新特性 Fast Index Create(FIC 特性)，在 MySQL5.6 中，开始支持更多的 alter table 类型操作来避免 copy data，同时支持了在线上 DDL 的过程中不阻塞 DML 操作，真正意义上的实现了 Online DDL。

但并不是所有的 DDL 操作都支持在线操作，这里附上 MySQL 官方文档对于 DDL 操作的总结：

http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

innodb_online_alter_log_max_size 参数

虽然 MySQL 5.6 支持了在线 DDL 操作，不过还是有些需要注意的问题，最为重要的就是参数 innodb_online_alter_log_max_size 的配置。在进行 DDL 操作时会保存操作时间内产生的日志，这与 facebook OSC 的临时表，但是其保存在内存中，而该内存的大小由参数 innodb_online_alter_log_max_size 定义，默认大小为 128M。如果产生的日志超出该容量大小，则会抛出类似如下的异常提示：

Error:1799SQLSTATE:HY000(ER_INNODB_ONLINE_LOG_TOO_BIG)

Message: Creating index 'idx_aaa' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.

根据测试经验，128M 通常在一个活跃的线上环境中是不够的，通常 512M 是比较推荐的值。好在该参数是动态的，可以基于会话级别进行调整。另外个人的体会是，在进行在线索引添加操作时，数据库性能会有 20～30%的下降。

另一个小技巧是使用 ALTER TABLE ... LOCK=NONE 来强制使用在线 DDL 操作，若操作不支持，则会抛出异常，如：

mysql> ALTER TABLE customers DROP PRIMARY KEY ,LOCK=NONE;

ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try LOCK=SHARED.

Online DDL in MySQL5.7

那到了 MySQL5.7，在 5.6 的基础上又增加了以下的新特性：

增加了 Alter table rename index 的语法支持，同时继续支撑 Online DDL 特性。

修改 Varchar 列的长度操作支持 Online 特性

ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(255);

ALTER TABLE t1 ADD COLUMN new_column ALGORITHM=INPLACE,LOCK=DEFAULT;

ALGORITHM 子句用来指定执行 DDL 采用的方式，可取值为 DEFAULT、INPLACE、COPY

ALGORITHM = DEFAULT| INPLACE| COPY

LOCK 子句描述持有的锁类型来控制 DML 的并发，取值 DEFAULT、NONE、SHARED、EXCLUSIVE

LOCK = DEFAULT| NONE | SHARED | EXCLUSIVE

同样，也附上 MySQL 官方文档对于 DDL 操作的总结：

http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

中文翻译如下图：

5.7 的 Online DDL 实现原理

5.7 的 Online DDL 使用限制与问题

1.仍然存在排他锁，有锁等待的风险。

2.跟 5.6 一样，增量日志大小是有限制的(由 innodb_online_alter_log_max_size 参数决定大小)

3.有可能造成主从延迟

4.无法暂停，只能中断

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 存在的风险

触发器是以解释型代码保存的，因此每次查询都需要对触发器的代码进行解释的开销。

触发器与原始查询共享相同的事务空间，原始查询在表上有锁竞争，触发器也会在另一张表上有锁竞争。同时，在触发器删除时同样会有元数据锁。

触发器无法暂停，当主库 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

这几个较少用到的参数

GH-OST 工具

GH-OST(gitHub’s Online Schema Transformer),意思是:GitHub 的在线表定义转换器

GH-OST 的优势：

无触发器的设计

切换方案的设计

最大限度的减少了对主机的影响

实现了增量数据的获取;基本做到了原子性的切换

GH-OST 原理分析

GH-OST 工作模式探索

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 表阶段）

对应用请求的影响：所有请求在 cut-over 阶段直到临时表消失前会被阻塞，被释放后所有的请求都会在新表上执行，若 cut-over 阶段失败，则所有的请求一定会在旧表上执行。

对主从复制的影响：备机只能看到 rename 操作，锁的 event 不会写入 binlog，因此备机看到的是一个原子性的操作。

GH-OST 使用限制：

不能对有外键关系及触发器的表进行 Online DDL

要求所连接的获取增量数据的 mysql binlog 为 row 格式(会强制转换)

若有同名但是字母大小写不同的表如：MYtable 和 myTable，则无法对这两张表进行修改

不支持 MySQL5.7 Json 类型列的修改

不支持 MySQL5.7 generated column 的修改

使用案例：

各个工具的总结对比

实际测试结果对比

Online DDL 的测试：

PT-OSC 的测试：

另外，实际测试中还发现，如果使用 PT-OSC 修改的表，该表的主键不是自增字段，可以获得比较高的一个性能。

但在实际生产环境中，主键几乎都是自增字段，如果在写入较大的线上环境，且同时表主键为自增字段的话，使用 PT-OSC 可能会产生大量的自增锁，lock_mode=AUTO_INC

同时，这和 innodb 的参数:innodb_autoinc_lock_mode 的配置也有关系：

这里简单介绍下：

从 MySQL5.1.22 版本开始，InnoDB 存储引擎开始提供一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插入的性能。并且从该版本开始，InnoDB 存储引擎提供了一个参数 innodb_autoinc_lock_mode 来控制自增长的模式，该参数的默认值为 1.

在详细了解 innodb_autoinc_lock_mode 之前，我们需要对自增长的插入进行类别划分：

1、Insert-like : insert-like 指所有的插入语句，如 INSERT、REPLACE、INSERT....SELECT、REPLACE....SELECT、LOAD DATA 等

2、Simple inserts: simple inserts 指能在插入前就确定插入行数的语句。这些语句包括 INSERT、REPLACE 等。需要注意的是：simple inserts 不包含 INSERT... ON DUPLICATE KEY UPDATE 这类 SQL 语句

3、Bulk inserts: bulk inserts 指在插入前不能确定得到插入行数的语句，如 INSERT....SELECT、REPLACE....SELECT、LOAD DATA

4、Mixed-mode inserts: mixed-mode inserts 指插入中有一部分的值是自增长的，有一部分是确定的。如 INSERT INTO t1(c1,c2) VALUES(1,'a'),(NULL,'b'),(5,'c'),(NULL,'d');

也可以是指 INSERT...ON DUPLICATE KEY UPDATE 这类 SQL 语句

在了解了以上的分类后，我们接着来分析 innodb_autoinc_lock_mode 参数的各个设置对自增锁的影响，总共就 3 个值可以设置,即 0、1、2

1、innodb_autoinc_lock_mode = 0 这是 MySQL5.1.22 版本之前的自增长实现方式，即通过表锁的 AUTO-INC Locking 方式。因为有了新的自增长方式，0 这个选项不应该是新版用户的首选项。

2、innodb_autoinc_lock_mode = 1 这是该参数的默认值。对于 simple inserts，该值会采用互斥量【mutex】去对内存中的计数器进行累加的操作。对于 bulk inserts，还是会使用传统表锁的 AUTO-INC Locking 方式。在这种配置下，如果不考虑回滚操作，对于自增列的增长还是连续的。并且在这种方式下，statement-based 方式的 replication 还是能很好的工作。需要注意的是，如果已经使用 AUTO-INC Locking 方式去产生自增长的值，而这是需要再进行 simple inserts 的操作时，还是需要等待 AUTO-INC Locking 的释放。

3、innodb_autoinc_lock_mode = 2 在这个模式下，对于所有 insert-like 自增长的产生都是通过互斥量，而不是 AUTO-INC Locking 的方式。显然，这是性能最高的方式。然而，这会带来一定的问题，因为并发插入的存在，在每次插入时，自增长的值可能不是连续的。此外，最重要的是，基于 statement-Base Replication(SBR) 会出现问题。因此，使用这个模式，任何时候都应该使用 row-base repliaction(RBR)。这样才能保证最大的并发性能及 replication 主从数据的一致。

GH-OST 测试

测试结果，GH-OST 的使用过程对性能影响是最小的，果然，无触发器的设计在性能上可以最大程度的避免 MDL 锁和 DML 锁等待