MySQL在线DLL测试

MySQL5.5和之前版本，在运行的生产环境对大表（超过数百万纪录）执行Alter操作是一件很困难的事情。因为将重建表和锁表，影响用户者的使用。
MySQL5.6提供了在线DDL，并且不锁表的功能，如果数据量不是特别大，通常可以使用alter。
MySQL各版本，对于add Index的处理方式是不同的，主要有三种：

• Copy Table方式
  

  这是InnoDB最早支持的创建索引的方式。顾名思义，创建索引是通过临时表拷贝的方式实现的。
  

  新建一个带有新索引的临时表，将原表数据全部拷贝到临时表，然后Rename，完成创建索引的操作。
  

  这个方式创建索引，创建过程中，原表是可读的。但是会消耗一倍的存储空间。
  

• Inplace方式
  

  这是原生MySQL 5.5，以及innodb_plugin中提供的创建索引的方式。所谓Inplace，也就是索引创建在原表上直接进行，不会拷贝临时表。相对于Copy Table方式，这是一个进步。
  

  Inplace方式创建索引，创建过程中，原表同样可读的，但是不可写。
  

• Online方式
  

  这是MySQL 5.6.7中提供的创建索引的方式。无论是Copy Table方式，还是Inplace方式，创建索引的过程中，原表只能允许读取，不可写。对应用有较大的限制，因此MySQL最新版本中，InnoDB支持了所谓的Online方式创建索引。

  InnoDB的Online Add Index，首先是Inplace方式创建索引，无需使用临时表。在遍历聚簇索引，收集记录并插入到新索引的过程中，原表记录可修改。而修改的记录保存在Row Log中。当聚簇索引遍历完毕，并全部插入到新索引之后，重放Row Log中的记录修改，使得新索引与聚簇索引记录达到一致状态。

  与Copy Table方式相比，Online Add Index采用的是Inplace方式，无需Copy Table，减少了空间开销；与此同时，Online Add Index只有在重放Row Log最后一个Block时锁表，减少了锁表的时间。

  与Inplace方式相比，Online Add Index吸收了Inplace方式的优势，却减少了锁表的时间。

MySQL 5.6的Alter Table有ALGORITHM=INPLACE和ALGORITHM=COPY两种不同算法。使用ALGORITHM=INPLACE可以允许在执行AlterDe时候并发执行DML语句。但是耗费的代价也比较大，在这种模式下Alter时间约是ALGORITHM=COPY算法的2倍左右。


在线DLL，分别为字段和索引，通常有四种办法，如下例，goods表，1000000W数据量，分别作字段和索引的DLL操作。

字段DLL：

一、alter table xxxxx;

特点：不阻塞读写，消耗时间长

二：alter table xxxxx,algorithm=inplace;
特点：不阻塞读写，消耗时间长

三：alter table xxxxx,algorithm=COPY;
特点：阻塞写，不阻塞读，消耗时间短

四：pt-online-schema-change；
特点：不阻塞读写，消耗时间比COPY略长
如下图，时间：约180s

索引DLL：

一、alter table xxxxx;

特点：不阻塞读写，消耗时间短

二：alter table xxxxx,algorithm=inplace;
特点：不阻塞读写，消耗时间短

三：alter table xxxxx,algorithm=COPY;
特点：不阻塞读写，消耗时间长

四：pt-online-schema-change；
特点：不阻塞读写，消耗时间长
如下图，时间：约240s

从上面的测试结果看出，在大数据量的情况下：
字段DLL时，默认的alter和pt-osc效率较高；
索引DDL时，默认的alter和alter带上algorithm=inplace参数的效率较高。