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.