• MySQL5.5和5.1版本中，以 InnoDB Plugin方式，优化了CREATE INDEX和DROP INDEX，避免了这种数据拷贝开销，详见Fast Index      Creation.

• MySQL 5.6开始增强了对多种ALTER TABLE操作的类型的支持，避免了这种数据拷贝开销;同时允许在DDL进行时，并发执行的DML操作. MySQL 5.7版本实现了ALTER TABLE RENAME INDEX操作，即支持在线的索引重命名.这些特性的综合，即大家常说的ONLINE DDL.

• pt-online-schema-change属于Percona-toolkit工具集(DBA应该很熟悉，有很多nice的工具)，通过改进原生ddl的方式，可做到不锁表在线修改表结构，但仍有些使用上应该注意的地方.

本文参考官方文档,对MYSQL5.6+的ONLINE DDL及pt-online-schema-change tool作一下总结介绍:

快速介绍PERCONA的pt-online-schema-change(下文称pt-osc)的情况：

2.1pt-osc工作过程:

1.创建一个和alter之前的原表A结构一样的空的新表B
2.在新表B执行alter table 语句
3.在原表A中创建基于insert，update，delete的3个触发器
4.以一定块大小(–chunk-time或–chunk-size-limit控制)从原表A向新表B导入数据，该过程中在原表进行的DML操作会通过触发器更新到新表B中，       这里由于replace into和insert ignore        的使用，可以保证数据的一致性
5.数据拷贝完成后，加排他锁，将原表A重命名为OLD，把新表B重命名为原表A的名称.
6.如果涉及参照该表的外键，根据–alter-foreign-keys-method参数的值来进行处理，这里有4个可选值:

• auto:自动选择，优先rebuild_constraints，否则drop_swap

• rebuild_constraints:这是优先使用的方法，具体会删除在子表上的外键约束并重新建立对新表B的外键参照约束.这里有一个例外，当子表(或多个)      的alter时间大于–chunk-time，这时不会选用rebuild_constraints方式.这里还有一点要注意，新的外键名称会有所改变.

• drop_swap:设置FOREIGN_KEY_CHECKS=0，之后DROP原表A，再重全名新表B为A的名称.+里有两个风险+:一是有段时间原表A是还存在的!应用程序如果查询到A，会报错;二是如果由于错误发生，不能成功将新表B重全名为A的名称时，后果是灾难性的，A的数据永久的丢失了!

• none:类似于drop_swap，但少了重全名这个阶段，可由DBA手动完成.

7.如果–no-drop-old-table启用，最后会将旧原表删除

2.2.pt-osc有若干使用限制:

• 原表上不能已有触发器存在

• 使用pt-OSC会使增加一倍的空间需求，包括索引和数据文件的大小，如果BINLOG_FORMAT为 Row，还会写一份binlog的产量也会增加

• pt-osc执行时间一般会较长，因为工具支持–max-load等参数作为空间里负载状态的监控，会动态地调整数据的拷贝速度.

• 不支持ALTER TABLE语法中的RENAME操作

• 不能通过 先DROP COLUMN，再ADD COLUMN的方式来重命名，这样会丢失数据。

• 当声明NOT NULL 时必须提供一个DEFAULT VALUE，工具不会帮忙.

• - -charset或许也是一个坑点，需要对编码有清楚认识(我考虑在另一篇尽量通俗地介绍一下编码常见的坑…)，或者使用统一的编码,应该是大家足以应对问题的基本准则

3.1、ONLINE DDL的显著特点

下面再说说ONLINE DDL的情况，它在性能， 并发， 可用性及扩展性等方面，改进了MYSQL 的操作.

• ONLINE DDL在进行中时，DML可并发地进行，相比之前的DDL操作，应用程序不再必须在等待表级锁的释放后才能完成数据的操作，这对7*24小时在线的业务意义比较大，你可以在业务低峰做到真正的在线变更.

• 之前的DDL复制表的方式下，一般至少需要有额外的被操作表大小的空闲磁盘空间，并且表复制时的IO开销也是对MYSQL服务性能造成影响的一个因素.in-place方式可极大的减少对磁盘空间的需求，并且节省了重建表所必需CPU时间.

• 对于in-place类型的操作，加为更少的数据需要进入BUFFPOOL中，所以避免了之后大量的从BUFFPOOL向磁盘的PURGE操作，也能在一定程度上减少MYSQL性能的降低。

  
  

3.2、影响ALTER TABLE 操作的因素:

3.3、ONLINE DDL的相关重要名词:

• INPLACE:ALTER TABLE后面紧跟的ALGORITHM
  关键字允许的两个值之一，或许可以理解为"就地"，避免重建表带来的IO/空间和CPU的开销，如果可能，应该优先被选择.

• COPY:ALTER TABLE后面紧跟的ALGORITHM
  关键字允许的两个值之一，即表复制，除以下的一些情形下并发的DML是允许的(值得注意的是，即使是进行了表复制，ONLINE DDL的速度也是比MYSQL5.5快的)，其它不支持并发的DML操作.表复制的情况下，由于有BUFFPOOL的使用，空间的需求，日志的产生等因素，其效果不如INPLACE方式.以下情形允许并发的DML:

• Adding， dropping， or reordering columns

• Adding or dropping a primary key.

• Changing the ROW_FORMAT or KEY_BLOCK_SIZE properties for a table.

• Changing the nullable status for a column.

• OPTIMIZE TABLE

• Rebuilding a table with the FORCE option

• Rebuilding a table using a “null” ALTER TABLE … ENGINE=INNODB statement

• LOCK
  选项控制是否锁表及锁的类型，同时也是一种安全性的"断言(ASSERTION)"，简单说当你为了更安全地执行DDL，可以使用LOCK
  声明可以接受的锁类型，比如为了不中断应用程序对该表的读，你声明了LOCK=None
  ，恰好你执行的是添加auto-increment的表，alter
  语句本身是需要共享锁的，这里"断言"就会失败，并不会真正开始DDL的实际变更.与PERCONA的pt-online-schema-change类似的，ONLINE DDL在开始执行初，会有短暂的排它锁.

• LOCK=NONE:不阻塞读写

• LOCK=SHARED:允许读操作，不允许写操作.

• LOCK=DEFAULT:在"尽可能不去锁表"的原则下，mysql来判断lock的实际值.

• LOCK=EXCLUSIVE:读写操作都不支持。

3.4、功能概览:

下表是官方给出的执行方式规则，这里作了简单翻译:

简单说明:

• 基于前面对概念的介绍，很明显，In-Place 为Yes/Copies Table为No/Allows Concurrent DML为Yes，都是应该优先考虑的，会有更好的效率.

• 带 * 号的Yes或No，应该注意Notes表的说明.

• 可以适当的"合并"或"拆分"多个DDL语句.大家都知道，5.5及之前，我们应该尽可能地合并多个ALTER TABLE语句为一条来执行，因为涉及复制/重建表仅发生一次，以减少总的执行时间.但这里其实不然，由于In-Place 和Table-Copying带来的处理方式的区别，应该反其道而行，如

ALTER TABLE t1 ADD INDEX i1(c1)， ADD UNIQUE INDEX i2(c2)，
  CHANGE c4_old_name c4_new_name INTEGER UNSIGNED;

拆分为:

ALTER TABLE t1 ADD INDEX i1(c1);
ALTER TABLE t1 ADD UNIQUE INDEX i2(c2);
ALTER TABLE t1 CHANGE c4_old_name c4_new_name INTEGER UNSIGNED NOT NULL;

当然对于多个使用表复制方式的DDL，仍然可用合并方式

3.5、In-Place 对比Table-Copying

一个DDL的性能如何，很大程度上由"是不是以 in-place方式"进行还是需要表复制并重建表决定.In-Place带来的性能提升，适用于二级索引，并不适用聚集索引(主键，数据在物理磁盘上的排列顺序);聚集索引情形下，可以使用ALGORITHM=INPLACE
，只是仍会有表的复制，但性能会比ALGORITHM=COPY
好，因为:

• 不涉及undo和redo日志

• .二级索引是预先有序的，可以顺序加载

• 不涉及change buffer，不会有随机数据插入二级索引中

当不确定一个DDL的开销时，这里有一个实用的方法辅助判断:

复制目标表的结构到测试表
插入少量的数据到测试表
在测试表上执行目标DDL
检查“rows affected”，非零的值表示该DDL需要重建表，或许有性能上的影响.

3.6、Online DDL条件下如何Crash Recovery

当SERVER崩溃时，尽管不会有数据的丢失，但实际对聚簇索引和二级索引的处理却有很大的不同.

• 如果是二级索引，在恢复时，MySQL会简单地DROP任何未创建成功的二级索引，你必须重新执行该ALTER TABLE 或 CREATE INDEX

• 如果是聚簇索引，情况比较复杂一些，因为整个表的数据必须被复制到一个新的聚簇索引中.MYSQL会创建一个临时表A，并且它是按新的聚簇索引方式来定义的.当完成了从原表向临时表A的数据复制，将原表    RENAME为另一临时表B，接着RENAME临时表A为新的表(表名同原表)，最后DROP临时表B

3.7、online ddl实现原理可参见这里

http://www.cnblogs.com/cchust/p/4639397.html

3.8、Online DDL 与分区表

3.9、ONLINE DDL的使用限制

• ONLINE DDL在需要进行表复制时，可能使用大量的磁盘空间，即务必使用MYSQL的datadir有足够的可用空间，特别的从MySQL 5.6.29/5.7.11开始，可使用  innodb_tmpdir来配置一个单独的临时目录，专用于online DDL操作.

• TEMPORARY TABLE上
  的ONLINE DDL会使用COPY
  方式，而不是Fast Index Creation
  

• 当尝试DROP一个被外键需要的索引时，会产生错误1553

• 主库上执行的alter table在完成时再写入binlog，所以当从库按顺序应用

• relay log时，大的ddl会导致从库产生延迟。（pt-osc可以控制延迟时间，所以这种场景下它更合适）

• 如果表上存在ON...CASCADE
  或ON...SET NULL
  设定，LOCK=NONE
  是不被允许的

• 在ALTER TABLE执行的初始阶段，根据设定的LOCK类型的不同，DDL操作可能需要获取一个排他锁，但这个锁的获得等待可能持续很长的时间，比如当前正存在大的事务在执行;同理一样可能会等待一个大事    务提交后DDL才可获取到招待最后原语所需的排他锁，也可能会持续较长时间.

• 执行ONLINE DDL的线程，会同时应用由并发执行DML操作的线程产生的“online log”(记录在当前表由其它线程并发的DML操作)，当DML操作被提交时，可能会出现"duplicate key entry error"，即使冲突的键只是      暂的并且稍后被处理为一致.

• 全局变量innodb_online_alter_log_max_size控制着online DDL招待期间，online log
  所允许的最大值，默认为128M，可手动调整.

• MYSQL5.6之前版本所创建的INNODB表，如果包含时间日期类型的列(DATE，DATETIME，TIMESTAMP)，并且未经 ALTER TABLE ...ALGORITHM=COPY
  说一句进行重建过，当被招待 ALTER TABLE ...             ALGORITHM=INPLACE
  操作时，会报出如下错误:

1. ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.
   

2. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
   

• 在INNODB类型表上进行的OPTIMIZE TABLE
  操作会转换为ALTER TABLE操作，将会重建表/更新索引数据/释放聚簇索引中未使用到的空间.在MySQL5.7.4之前 ，ONLINE DDL不支持此类操作

• 对于巨大的表，涉及到table copy时，没有办法暂停这个ddl，或者给IO/CPU的使用加上阈值限制.

• 从MySQL 5.7.6开始，可以通过 performance schema
  提供了监控ALTER TABLE进度信息的能力.

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

• ONLINE DDL在必须出现表复制时效率较低

• pt-osc在目标表上已经存在触发器时，不可用

• pt-osc对空间的需求较高，为硬性的

• 修改索引、外键、列名时，优先采用ONLINE DDL，优先指定

• ALGORITHM=INPLACE方式

积极采用样本测试执行效率，评估可行性。

后续将单独整理测试数据，待续。

参考:

http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid

http://www.cnblogs.com/cchust/p/4639397.html

https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html

以上内容为IPD原创，如需转载，请注明出处~

扫描下方二维码关注我们~IPDCHAT专注输出技术干货~！