关于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-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 操作的比照后果，仅供参考：

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

作者：京东物流 刘邓忠

起源：京东云开发者社区