MySQL5.7 Online DDL

1. ALter table （5.7）

    一般情况下，alter table  都会对原有的表做一个临时的副本拷贝，然后将所做的该表应用到副本，之后再将原表删除，rename 副本。在这个过程中，原表对外是可读的；但是对该表DML会被堵塞，直到alter 完成。

    The exception referred to earlier is that ALTER TABLE blocks reads (not just writes) at the point where

it is ready to install a new version of the table .frm file, discard the old file, and clear outdated table

structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To

do so, it waits for current readers to finish, and blocks new reads (and writes). 

在 alter table读取块，安装新建的.frm 。丢弃就得文件和数据时，会加排它锁。

下面操作一般不需要创建临时副本：

    1）ALTER TABLE tbl_name RENAME TO new_tbl_name without any other options ;

    2）只改变表的元数据而不改动数据时 ：

        （1）列的重命名；

        （2）改变列的默认值；

        （3）对ENUM和SET列在成员列表最后添加列表（但是向具有8个成员的SET列添加成员会将所需的存储空间每个值从1个字节更改为2个字节;  在列表中间添加成员会导致现有成员重新编号，这些需要一个表副本。）

     3）表空间的丢弃和导入；

     4）索引重命名，添加和删除索引（for innodb and NDB）

2 Summary of Online Status for DDL Operations

（1）重命名列

    只改变列名而不改变列的属性，可以进行在线操作；

    对于修改外键所在的列，不能使用ALGORITHM=COPY选项。

（2）改变VARCHAR 列的字符长度

    若原VARCHAR列定义的长度在0-255范围内，修改后也在该范围则可以使用ALGORITHM=INPLACE；

    若原VARCHAR列定义的长度在256以上，修改后也在该范围则也可以使用ALGORITHM=INPLACE；

    但是若从小于256范围变为大于256的范围，或者varchar列长度由大变小，则会进行表的副本拷贝。

  （3）下列操作，虽然进行表的副本拷贝但是依然支持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

  注意：若需要进行表的副本拷贝，则表副本会临时在参数tmpdir 指定的路径下。任何DDL语句都会等待当前的事务结束才会开始执行，因为在DDL执行伊始和最后都短暂的加排他锁。

  虽然对于主键的修改需要做表的副本拷贝，但是使用ALGORITHM=INPLACE是被允许的，且比ALGORITHM=COPY的效率要高。因为ALGORITHM=INPLACE 不需要记录相应的undo和redo日志，二级索引已经存储好，可以顺序load，由于没有随机的二级索引插入所以也没使用到change buffer。

  （4）DDL操作是执行了 inplace 还是copy ，最直观的表现就是查看操作完成后的“rows affected”,如下：

（5） 对于一个大表的操作，需要确认相应DDL执行的效率：

    1）. Clone the table structure.

    2）. Populate the cloned table with a tiny amount of data.

    3）. Run the DDL operation on the cloned table.

    4）. Check whether the “rows affected” value is zero or not. A non-zero value means the operation willrequire rebuilding the entire table, which might require special planning. For example, you might do the DDL operation during a period of scheduled downtime, or on each replication slave server one at a time.

For a deeper understanding of the reduction in MySQL processing, examine the performance_schema and INFORMATION_SCHEMA tables related to InnoDB before and after DDL operations, to see the number of physical reads, writes, memory allocations, and so on.