关于mysql:Mysql-DDL执行方式ptosc介绍-京东云技术团队

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-DBIyum install perl-DBD-MySQLyum install perl-Time-HiResyum install perl-IO-Socket-SSL# 装置percona-toolkitrpm -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操作的比照后果，仅供参考：

那么当初的问题是，咱们应该应用哪种办法来执行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，敬请期待哦！

作者：京东物流 刘邓忠

起源：京东云开发者社区