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，然后执行下面的命令进行安装（示例）：
执行类似下面的命令修改表结构：
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 操作的对比结果，仅供参考：
那么现在的问题是，我们应该使用哪种方法来执行 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，敬请期待哦！
作者：京东物流 刘邓忠
来源：京东云开发者社区
版权声明: 本文为 InfoQ 作者【京东科技开发者】的原创文章。
原文链接:【http://xie.infoq.cn/article/3c8f2b64702210457754a7322】。文章转载请联系作者。
拥抱技术，与开发者携手创造未来！ 2018-11-20 加入
我们将持续为人工智能、大数据、云计算、物联网等相关领域的开发者，提供技术干货、行业技术内容、技术落地实践等文章内容。京东云开发者社区官方网站【https://developer.jdcloud.com/】，欢迎大家来玩
评论