Mysql DDL执行方式-pt-osc介绍 | 京东云技术团队

1 引言

大家好，接着上次和大家一起学习了《MySQL DDL执行方式-Online DDL介绍》，那么今天接着和大家一起学习另一种MySQL DDL执行方式之pt-soc。

在MySQL使用过程中，根据业务的需求对表结构进行变更是个普遍的运维操作，这些称为DDL操作。常见的DDL操作有在表上增加新列或给某个列添加索引。

DDL定义：

Data Definition Language，即数据定义语言，那相关的定义操作就是DDL，包括：新建、修改、删除等；相关的命令有：CREATE，ALTER，DROP，TRUNCATE截断表内容（开发期，还是挺常用的），COMMENT 为数据字典添加备注。

注意：DDL操作是隐性提交的，不能rollback，一定要谨慎哦！

下图是执行方式的性能对比及说明：

图1 易维平台说明图

下面本文将对DDL的执行工具之pt-osc进行简要介绍及分析。如有错误，还请各位大佬们批评指正。

2 介绍

pt-online-schema-change - ALTER tables without locking them.

pt-online-schema-change alters a table’s structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully.

pt-online-schema-change是Percona公司开发的一个非常好用的DDL工具，称为 pt-online-schema-change，是Percona-Toolkit工具集中的一个组件，很多DBA在使用Percona-Toolkit时第一个使用的工具就是它，同时也是使用最频繁的一个工具。它可以做到在修改表结构的同时（即进行DDL操作）不阻塞数据库表DML的进行，这样降低了对生产环境数据库的影响。在MySQL5.6之前是不支持Online DDL特性的，即使在添加二级索引的时候有FIC特性，但是在修改表字段的时候还是会有锁表并阻止表的DML操作，这样对于DBA来说是非常痛苦的，好在有pt-online-schema-change工具在没有Online DDL时解决了这一问题。

Percona 公司是成立于2006年，总部在美国北卡罗来纳的Raleigh。由 Peter Zaitsev 和 Vadim Tkachenko创立， 这家公司声称他们提供的软件都是免费的，他们的收入主要来与开源社区，企业的支持，以及使用他们软件的公司的支付他们提供support的费用。 而实际上这家公司"垄断"了业内最流行数据库支持类的软件，并且还开发了一些其他的与数据库相关的东西。

Percona-Toolkit工具集是Percona支持数据库人员用来执行各种MySQL、MongoDB和系统任务的高级命令行工具的集合，这些任务太难或太复杂而无法手动执行。这些工具是私有或“一次性”脚本的理想替代品，因为它们是经过专业开发、正式测试和完整记录的。它们也是完全独立的，因此安装快速简便，无需安装任何库。

Percona Toolkit 源自 Maatkit 和 Aspersa，这两个最著名的 MySQL 服务器管理工​具包。它由 Percona 开发和支持。

3 工作流程

pt-osc 用于修改表时不锁表，简单地说，这个工具创建一个与原始表一样的新的空表，并根据需要更改表结构，然后将原始表中的数据以小块形式复制到新表中，然后删除原始表，然后将新表重命名为原始名称。在复制过程中，对原始表的所有新的更改（insert，delete，update）都将应用于新表，因为在原始表上创建了一个触发器，以确保所有新的更改都将应用于新表。有关 pt-online-schema-change 工具的更多信息，请查阅手册文档 。

pt-osc大致的工作过程如下：

1.创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)；

2.在新表执行alter table 语句（速度应该很快）；

3.在原表中创建触发器3个触发器分别对应insert,update,delete操作，如果表中已经定义了触发器这个工具就不能工作了；

4.以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表，保证数据不会丢失（会限制每次拷贝数据的行数以保证拷贝不会过多消耗服务器资源，采用 LOCK IN SHARE MODE 来获取要拷贝数据段的最新数据并对数据加共享锁阻止其他会话修改数据，不过每次加S锁的行数不多，很快就会被释放）；

5.将原表Rename为old表，再把新表Rename为原表（整个过程只在rename表的时间会锁一下表，其他时候不锁表）；

6.如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理（根据修改后的数据，修改外键关联的子表），如果被修改表存在外键定义但没有使用
–alter-foreign-keys-method 指定特定的值，该工具不予执行；

7.默认最后将旧原表删除、触发器删除。

图2 pt-osc工作过程示意图

4 用法

Percona Toolkit 是成熟的，但是官方还是建议在使用前做到以下几点：

•阅读该工具的详细文档

•查看该工具的已知“错误”

•在非生产服务器上测试该工具

•备份您的生产数据并验证备份

下载安装：

从官方网站下载percona-toolkit，然后执行下面的命令进行安装（示例）：

# 安装依赖包
yum install perl-TermReadKey.x86_64 
yum install perl-DBI
yum install perl-DBD-MySQL
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL# 安装percona-toolkit
rpm -ivh percona-toolkit-3.1.0-2.el7.x86_64.rpm

执行类似下面的命令修改表结构：

pt-online-schema-change --alter="add column c1 int;" --execute D=test,t=table,u=user,p=password

alter参数指定修改表结构的语句，execute表示立即执行，D、t、u、p分别指定库名、表名、用户名和密码，执行期间不阻塞其它并行的DML语句。pt-online-schema-change还有许多选项，具体用法可以使用pt-online-schema-change --help查看联机帮助。

5 限制

pt-online-schema-change也存在一些局限性：

1.在使用此工具之前，应为表定义PRIMARY KEY或唯一索引，因为它是DELETE触发器所必需的；

2.如果表已经定义了触发器，则不支持 pt-osc ；（注：不是不能有任何触发器，只是不能有针对insert、update、delete的触发器存在，因为一个表上不能有两个相同类型的触发器）；

3.如果表具有外键约束，需要使用选项
–alter-foreign-keys-method，如果被修改表存在外键定义但没有使用 --alter-foreign-keys-method 指定特定的值，该工具不予执行；

4.还是因为外键，对象名称可能会改变（indexes names 等）；

5.在Galera集群环境中，不支持更改MyISAM表，系统变量 wsrep_OSU_method 必须设置为总序隔离（Total Order Isolation，TOI）；

6.此工具仅适用于 MySQL 5.0.2 及更新版本（因为早期版本不支持触发器）；

7.需要给执行的账户在 MySQL上授权，才能正确运行。（应在服务器上授予PROCESS、SUPER、REPLICATION SLAVE全局权限以及 SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、ALTER 和 TRIGGER 表权限。Slave 只需要 REPLICATION SLAVE 和 REPLICATION CLIENT 权限。）

6 对比OnLine DDL

下面的表格是国外技术牛人进行的测试数据，是Online DDL和pt-osc对一个包含1,078,880行的表应用一些alter操作的对比结果，仅供参考：

	online ddl	pt-osc
更改操作	受影响的行	是否锁表	时间（秒）	受影响的行	是否锁表	时间（秒）
添加索引	0	否	3.76	所有行	否	38.12
下降指数	0	否	0.34	所有行	否	36.04
添加列	0	否	27.61	所有行	否	37.21
重命名列	0	否	0.06	所有行	否	34.16
重命名列更改其数据类型	所有行	是	30.21	所有行	否	34.23
删除列	0	否	22.41	所有行	否	31.57
更改表引擎	所有行	是	25.3	所有行	否	35.54

那么现在的问题是，我们应该使用哪种方法来执行alter语句呢？

虽然pt-osc允许对正在更改的表进行读写操作，但它仍然会在后台将表数据复制到临时表，这会增加MySQL服务器的开销。所以基本上，如果Online DDL不能有效工作，我们应该使用 pt-sc。换句话说，如果Online DDL需要将数据复制到临时表（algorithm=copy）并且该表将被长时间阻塞（lock=exclusive）或者在复制环境中更改大表时，我们应该使用 pt-osc工具。

pt-osc官方文档：
https://docs.percona.com/percona-toolkit/pt-online-schema-change.html

7 总结

本次和大家一起学习了解pt-online-schema-change工具，介绍了其产生的背景、基本工作流程、用法及相应的一些限制。还介绍了其与Online DDL执行方式的一些对比，如果错误还请指正。

目前可用的DDL操作工具包括pt-osc，github的gh-ost，以及MySQL提供的在线修改表结构命令Online DDL。pt-osc和gh-ost均采用拷表方式实现，即创建个空的新表，通过select+insert将旧表中的记录逐次读取并插入到新表中，不同之处在于处理DDL期间业务对表的DML操作。

到了MySQL 8.0 官方也对 DDL 的实现重新进行了设计，其中一个最大的改进是 DDL 操作支持了原子特性。另外，Online DDL 的 ALGORITHM 参数增加了一个新的选项：INSTANT，只需修改数据字典中的元数据，无需拷贝数据也无需重建表，同样也无需加排他 MDL 锁，原表数据也不受影响。整个 DDL 过程几乎是瞬间完成的，也不会阻塞 DML，不过目前8.0的INSTANT使用范围较小，后续再对8.0的INSTANT做详细介绍吧。

下一期文章将和大家一起学习、了解github的gh-ost，敬请期待哦！

作者：京东物流 刘邓忠

来源：京东云开发者社区