贝壳产品技术

1. 探索 Alter 操作细节

MySQL 的 Alter 操作是我们平时最常用的操作，但是这个操作到底干了啥？会不会锁表？如果不知道 Alter 操作的过程，我们是很难回答以上问题的。这篇博客就专门分析 Alter 操作的详细过程。

在开始分析之前，我们先做几点解释和限制：

• OSC 意思为在线结构变更(online-schema-change)，是目前 Xmen 平台使用的方式。

• 建议大家复现时选用记录在百万及以上级别的表，小表 Alter 操作的探究价值不大。

2. 基本流程

2.1 单实例

我们从最简单场景开始，对单个实例的表进行结构变化。因为 MySQL5.6 以及后续版本都引入了原生的 OSC，MySQL5.6 中可以通过配置系统变量的方式开启或关闭（默认开启）。

首先，关闭 MySQL 原生的 OSC 功能：set old_alter_table = on;

通过阅读文档，我们知道在 MySQL5.5 及以前的版本中 Alter 操作可以分为以下几步：

• 创建和原表结构相同的中间表(不同于临时表，临时表只存在于内存)。

• 将 Alter 操作作用于该中间表。

• 锁住原表的写操作，只允许读。

• 将原表数据同步到新表。

• 通过 rename 操作，将新表改名为原表。

但是我们怎么验证这个过程呢？我们来实验一下。操作仅仅是对表增加字段，同时用另一个 DB 连接执行 update 操作，然后查看进程列表。SQL 如下：

1alter table link_customer_bak add column test varchar(15);2update link_customer_bak set status = 5 where id = 1;3show processlist;

我们看到结果如下，我们看到更新操作被 Hang 住，因为获取不到写锁。锁表操作得到验证。

同时，我们在数据目录下看到了如下图所示文件。我们知道 frm 文件实际就是表结构描述文件，但是这个是不是 Alter 操作执行结束后的表结构呢？

虽然我们无法直接查看 frm 文件的内容，但是我们可以用哈希算法来验证这个临时的 frm 文件和 Alter 执行完成后的 frm 文件是否一致。

两个文件的 md5 完全相同，由此看来，我们之前的结论是没有什么问题的。

2.2 主从

单实例场景是比较简单的，我们实际生产环境中也比较少使用，主从结构才是我们最常见的架构。在主从模式下，更改表结构是怎么样的呢？

我们知道从库的变化是通过监听主库的 binlog 来实现的，所以这个问题就可以拆解成两个问题：

• 主库执行 Alter 操作时 binlog 是在什么时候生成的？

• 同样的 Alter 操作，从库是否和主库有相同的行为？

我们先看第一个问题。通过 mysqlbinlog -v 命令我们能直接查看 binlog 的内容。在 Alter 开始后执行 mysqlbinlog -v mysql-bin.000001，我们看不到正在执行的 SQL 的 binlog。当 Alter 执行结束后，我们看到了刚刚执行的 SQL。

我们看到 Master 在执行完 Alter 操作后，Slave 收到了 binlog，负责同步 binlog 的线程状态变为执行 Alter 操作。

同时如果对 Slave 的该表执行 Update 操作，我们看到 Update 操作会 Hang 住。

综上，我们能够确定 Slave 是在 Mater 做完 Alter 操作之后才开始操作的，其行为和 Master 一致。所以如果是大表做 Alter 操作，会把主库和从库分别 Hang 住。这会造成两个问题：

• 主库 Hang 住，写入失败。

• 从库 Hang 住，主从延迟。

这些问题对我们的线上业务影响还是比较大的，因此在实际生产中要尽量避免大表 Alter。

3. OSC 工具

既然直接进行 Alter 操作会锁表，那么有没有什么方式能避免这个锁表呢？这里就要提到 OSC 了，目前业界常见的 OSC 有：

• gh-ost, github 开源的 OSC 工具，使用 binlog 同步大表数据拷贝期间产生的数据变化。

• oak-online-alter-table, openark-kit 中的一个组件，基于触发器实现。

• pt-online-schema-change, Percona Toolkit 中的一个组件，也是基于触发器实现。

• online schema change for MySql, Facebook 开源的 OSC 工具，也是基于触发器实现。

本文中，我们选用的对象是 pt-OSC，因为这是我司 DBA 同学选用的工具，以 pt-OSC 为例更接地气。

pt-OSC

pt-OSC 依赖的是 Perl 环境，主要依赖 DBD-MySql、IO-Socket-SSL 和 TermReadKey 三个组件。Perl 的组件可以通过 yum 安装，也可以直接下载 rpm 包安装。步骤如下：

• yum install perl-DBD-MySql perl-IO-Socket-SSL

• 下载 perl-TermReadKey-2.30-2.4.x86_64.rpm 和 percona-toolkit-3.0.4-1.el6.x86_64.rpm

• rpm -ivh perl-TermReadKey-2.30-2.4.x86_64.rpm && rpm -ivh percona-toolkit-3.0.4-1.el6.x86_64.rpm

安装好后我们可以尝试下。（注：该工具需要被操作的表有主键） 具体操作如下：

1pt-online-schema-change -h xx.xx.xx.xx -uroot -P6707 -p lianjia --alter='modify column test varchar(100)' --execute D=lianjia_newhouse_service_customer,t=link_customer_bak

输出如下：

从上面的提示我们大致能梳理出完整的执行过程：

创建新的中间表，同时将 Alter 操作作用于中间表。

创建触发器，将原表的 INSERT，UPDATE，DELTE 操作同步到新的中间表。

将原表所有的数据同步到中间表（可以通过 binlog 验证）。

优化表数据(Analyzing new table)，这里暂时不做深究。

交换中间表和原表(通过 rename 操作)。

删除所有的触发器。

以上的操作过程都是可验证的。首先是创建新新表和触发器，参考下图：

其次，新旧表交换操作是通过 rename 实现的。打开 pt-online-schema-change 文件，我们看到 swap_tables 方法里面有下面一段：

尽管我们没有逐步验证，但是其中最关键的触发器和表交换已经得到验证，这些对于理解流程已足够。

关于 pt-OSC 的问题和限制，我们在分析完 MySql 原生 OSC 之后再做讨论。先留个伏笔。

原生 OSC

MySQL 从 5.6 开始，引入了对原生 OSC 的支持，其过程太过复杂，我们本篇暂不展开，仅从使用者角度来介绍。具体来讲支持两种模式，可以通过 Alter 语句的 ALGORITHM 参数指定。具体如下：

• ALGORITHM=INPLACE 模式，可以避免重建表带来的 IO 和 CPU 消耗，保证 DDL 期间仍具有良好的性能和并发。

• ALGORITHM=COPY 模式，需要拷贝原始表，所以不允许并发 DML 写操作，DDL 期间可读，性能比 INPLACE 相差较多。

同时，为了支持不同的操作模式，MySQL5.6 版本中对 Alter 操作还引入了锁表支持，通过 LOCK 参数指定。根据不同的 DDL 操作类型有不同的表现：默认 MySql 尽可能不去锁表，但是像修改主键这样的昂贵操作不得不选择锁表。

LOCK=NONE，即 DDL 期间允许并发读写被修改的表，ALGORITHM=COPY 默认 LOCK 级别。

LOCK=SHARED，即 DDL 期间表上的写操作会被阻塞，但不影响读取。

LOCK=DEFAULT，让 MySQL 自己去判断 lock 的模式，原则是 MySQL 尽可能不去锁表。

LOCK=EXCLUSIVE，即 DDL 期间该表不可用，堵塞任何读写请求。如果你想 Alter 操作在最短的时间内完成，或者表短时间内不可用能接受，可以手动指定。

针对不同的操作类型类型，MySQL 做了不同类型支持，总的来说分为如下几种支持：

In-Place Yes 是优选项，说明该操作支持 INPLACE。

Copies Table No 是优选项，因为为 Yes 需要重建表。

Allows Concurrent DML Yes 是优选项，说明 DDL 期间表依然可读写，可以指定 LOCK=NONE（如果操作允许的话 MySql 自动就是 NONE）。

Allows Concurrent Query 默认所有 DDL 操作期间都允许查询请求，放在这只是便于参考。

具体的操作支持请参考 MySql 在线 DDL 官方说明

从参考文档的表里我们能看出来，如果 In-Place 是 No，那么并发 DML 一定是 NO，说明拷贝表的操作模式一定不支持并发 DML。但是，支持 In-Place 模式的操作也可能拷贝表，同时支持并发 DML。比如：

添加、删除列，改变列顺序

添加或更新主键

改变行格式 ROW_FORMAT 和压缩块大小 KEY_BLOCK_SIZE

改变列 NULL 或 NOT NULL

优化表 OPTIMIZE TABLE

强制 rebuild 该表

不允许并发 DML 的情况有：修改列数据类型、删除主键、变更表字符集，即这些类型操作的 DDL 是不能在线完成的。

看来，MySQL5.6 里对 Alter 操作的性能做了很大的改进，我们常用的 DDL 操作有了较好的性能提升。

4. 限制

通过上面章节的分析，我们对在线 DDL 的实现有了一定了解，在了解其优势的同时我们还应该了解一些问题和限制，以方便我们后续遇到此类问题时有更全面的考虑。

pt-OSC

首先，其要求被操作的表有主键。这个还好，因为我们绝大部分表都有主键。

其次，因为其本质是基于触发器实现的，而且 MySQL 目前单表对于同类型的触发器仅支持一个，所以当原表存在触发器时，该方案就不是很优雅。但是想要支持触发器也是可能的，思路就是：重写原表的触发器，使其同时满足原触发器和新触发器的功能，最后操作完成后再将原表的触发器作用于新表。

再次，如果表有外键，除非使用 --alter-foreign-keys-method 指定特定的值，否则工具不予执行。

再再次，这个工具本质将 Alter 过程拆解成了 Alter+Create Trigger+Insert+Rename，所以在主从模式下从库会时时同步从主库传来的 binlog。因为本身提供了速度控制工具，所以主从延迟问题能很大缓解，但是无疑会增加很大的网络传输，因为整张表都要进行一次 Insert 操作。

最后，这个工具是 MySQL 非原生支持，其稳定性有待验证，是否可在线上大规模应用有待考察。

原生 OSC

首先，在进行 Alter 操作时，首先要评估当前实例的数据目录下有足够的磁盘空间。在涉及到有 COPY 类型的 Alter 操作时，MySQL 会在当前目录下进行重建表操作，所以要求该空间下能放下一张完整的被操作表。当然，这个问题 pt-OSC 工具也存在。

其次，在主从环境下，主库执行 Alter 命令在完成之前是不会进入 binlog 记录事件，如果允许 DML 操作则不影响记录时间，所以期间不会导致延迟。然而，由于从库是单个 SQL Thread(5.7 以前)按顺序应用 relay log，轮到 ALTER 语句时直到执行完才能下一条，所以从库会在 master DDL 完成后开始产生延迟。（这就是 MySQL5.5 中存在的问题，在 5.6 中也没有很好解决，但 pt-OSC 解决了该问题）

再次，因为在 In-Place 模式允许并发的 DML 操作，而且 DDL 期间肯定需要有一些操作是需要获取写锁的，所以 DDL 开始前需要等待写锁，DDL 结束前同样也需要写锁。 如果线上系统频繁出现长 Transaction，那么 DDL 的完成时间会被拖延。

最后，如果 DDL 执行时间很长，期间又产生了大量的 DML 操作，以至于超过了 innodb_online_alter_log_max_size 变量所指定的大小，会引起 DB_ONLINE_LOG_TOO_BIG 错误（默认为 128M），特别对于需要拷贝大表的 Alter 操作，需要考虑临时加大该值，以此获得更大的日志缓存空间。

5. 结论

所以，谈了这么多，在做 DDL 时究竟需要注意什么，是选择原生 OSC 还是 pt-OSC 呢？下面就直接上干货吧。

• 在原生 DDL 必须要拷贝表时，可以考虑使用 pt-OSC。

• 在存在外键，触发器时或修改索引、列名时优先采用原生 DDL。

• pt-OSC 的速度可控，对于从库机器性能不佳，或者是 binlog 会触发其他操作（比如说检索），可以考虑用 pt-OSC。（个人认为其实现思路借鉴价值比较大，可用于我们开发自己工具）

• 对同一类型的 DDL 操作(eg. 同一个表增加多个字段)，建议合并成同一条 Alter 语句，避免重复建表(DBA 也建议我们这么做)。

• 特殊情况需要利用主从特性，先 Alter 从库，主备切换，再改原主库。

• 无论哪种方式，强烈建议在业务低峰期间进行 DDL 操作，并且在操作前备份数据。

作者介绍：

杨通，链家网新房研发部研发工程师，负责新房 Link 研发工作。2015 年加入链家网，曾在大数据、新房研发部等部门工作。

本文转载自公众号贝壳产品技术（ID：gh_9afeb423f390）。

原文链接：

https://mp.weixin.qq.com/s/GHgJRVkU3hsrSLy-Rhviwg