MySQL5.6在線表結(jié)構(gòu)變更（online ddl）總結(jié)

  MySQL從5.6.17以后，支持在線修改表結(jié)構(gòu)操作（online ddl），即在變更表結(jié)構(gòu)的過程中，不阻塞dml和dql操作.

  根據(jù)操作過程中是否需要表拷貝，online ddl可分為下面兩大類：

1.需要表拷貝的 ddl 操作：
增加、刪除、重排列。
增加、刪除主鍵。
改變表的 ROW_FORMAT 或 KEY_BLOCK_SIZE屬性。
改變的字段的null狀態(tài)。
執(zhí)行OPTIMIZE TABLE，優(yōu)化表。
使用 FORCE 選項重建表。
使用ALTER TABLE ... ENGINE=INNODB 語句。
首次創(chuàng)建全文索引。


2.不需要表拷貝的 ddl 操作：
創(chuàng)建、增加、刪除普通索引。
創(chuàng)建第二個及后續(xù)的全文索引。
為字段設(shè)置默認值。
改變auto-increment值。
刪除外鍵約束。
添加外鍵約束（ 只有在foreign_key_checks=off時）
僅僅改變列的名稱
設(shè)置表的持續(xù)統(tǒng)計選項（STATS_PERSISTENT, STATS_AUTO_RECALC STATS_SAMPLE_PAGES）

特別說明：全文索引需要特別注意，創(chuàng)建了全文索引的表基本上不支持在線ddl操作。

ySQL 5.6 Online DDL把這種特性擴展到了添加列、刪除列、修改列類型、列重命名、設(shè)置默認值等等，實際效果要看所使用的選項和操作類別來定。

1.1 Online DDL選項

MySQL 在線DDL分為 INPLACE 和 COPY 兩種方式，通過在ALTER語句的ALGORITHM參數(shù)指定。

• ALGORITHM=INPLACE，可以避免重建表帶來的IO和CPU消耗，保證ddl期間依然有良好的性能和并發(fā)。

• ALGORITHM=COPY，需要拷貝原始表，所以不允許并發(fā)DML寫操作，可讀。這種copy方式的效率還是不如 inplace ，因為前者需要記錄undo和redo log，而且因為臨時占用buffer pool引起短時間內(nèi)性能受影響。

上面只是 Online DDL 內(nèi)部的實現(xiàn)方式，此外還有 LOCK 選項控制是否鎖表，根據(jù)不同的DDL操作類型有不同的表現(xiàn)：默認mysql盡可能不去鎖表，但是像修改主鍵這樣的昂貴操作不得不選擇鎖表。

• LOCK=NONE，即DDL期間允許并發(fā)讀寫涉及的表，比如為了保證 ALTER TABLE 時不影響用戶注冊或支付，可以明確指定，好處是如果不幸該 alter語句不支持對該表的繼續(xù)寫入，則會提示失敗，而不會直接發(fā)到庫上執(zhí)行。ALGORITHM=COPY默認LOCK級別

• LOCK=SHARED，即DDL期間表上的寫操作會被阻塞，但不影響讀取。

• LOCK=DEFAULT，讓mysql自己去判斷l(xiāng)ock的模式，原則是mysql盡可能不去鎖表

• LOCK=EXCLUSIVE，即DDL期間該表不可用，堵塞任何讀寫請求。如果你想alter操作在最短的時間內(nèi)完成，或者表短時間內(nèi)不可用能接受，可以手動指定。

但是有一點需要說明，無論任何模式下，online ddl開始之前都需要一個短時間排它鎖(exclusive)來準備環(huán)境，所以alter命令發(fā)出后，會首先等待該表上的其它操作完成，在alter命令之后的請求會出現(xiàn)等待waiting meta data lock。同樣在ddl結(jié)束之前，也要等待alter期間所有的事務(wù)完成，也會堵塞一小段時間。所以盡量在ALTER TABLE之前確保沒有大事務(wù)在執(zhí)行，否則一樣出現(xiàn)連環(huán)鎖表。

1.2 考慮不同的DDL操作類別

從上面的介紹可以看出，不是5.6支持在線ddl就可以隨心所欲的alter table，鎖不鎖表要看情況：

提示：下表根據(jù)官方 Summary of Online Status for DDL Operations 整理挑選的常用操作。

• In-Place為Yes是優(yōu)選項，說明該操作支持INPLACE

• Copies Table為No是優(yōu)選項，因為為Yes需要重建表。大部分情況與In-Place是相反的

• Allows Concurrent DML?為Yes是優(yōu)選項，說明ddl期間表依然可讀寫，可以指定 LOCK=NONE（如果操作允許的話mysql自動就是NONE）

• Allows Concurrent Query?默認所有DDL操作期間都允許查詢請求，放在這只是便于參考

• Notes會對前面幾列Yes/No帶*號的限制說明

從表看出，In-Place為No，DML一定是No，說明ALGORITHM=COPY一定會發(fā)生拷貝表，只讀。但ALGORITHM=INPLACEE也要可能發(fā)生拷貝表，但可以并發(fā)DML:

• 添加、刪除列，改變列順序

• 添加或刪除主鍵

• 改變行格式ROW_FORMAT和壓縮塊大小KEY_BLOCK_SIZE

• 改變列NULL或NOT NULL

• 優(yōu)化表OPTIMIZE TABLE

• 強制 rebuild 該表

不允許并發(fā)DML的情況有：修改列數(shù)據(jù)類型、刪除主鍵、變更表字符集，即這些類型操作ddl是不能online的。

另外，更改主鍵索引與普通索引處理方式是不一樣的，主鍵即聚集索引，體現(xiàn)了表數(shù)據(jù)在物理磁盤上的排列，包含了數(shù)據(jù)行本身，需要拷貝表；而普通索引通過包含主鍵列來定位數(shù)據(jù)，所以普通索引的創(chuàng)建只需要一次掃描主鍵即可，而且是在已有數(shù)據(jù)的表上建立二級索引，更緊湊，將來查詢效率更高。

修改主鍵也就意味著要重建所有的普通索引。刪除二級索引更簡單，修改InnoDB系統(tǒng)表信息和數(shù)據(jù)字典，標記該所以不存在，標記所占用的表空間可以被新索引或數(shù)據(jù)行重新利用。

MySQL5.6幾種建索引方式比較。
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.17 |
+-----------+
mysql> CREATE TABLE test AS SELECT * FROM information_schema.columns;
mysql> INSERT INTO test SELECT * FROM test;
mysql> INSERT INTO test SELECT * FROM test;
mysql> SELECT COUNT(1) FROM test;
+----------+
| COUNT(1) |
+----------+
| 312928 |
+----------+
1 row in set (0.17 sec)

ALGORITHM=inplace，就地進行，如果允許的話則修改操作可以直接在該表上執(zhí)行。

mysql> CREATE INDEX ind_t_column_name ON test(column_name) ALGORITHM=inplace;
Query OK, 0 rows affected (1.54 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE test DROP INDEX ind_t_column_name;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0


mysql> CREATE INDEX ind_t_column_name ON test(column_name);
Query OK, 0 rows affected (1.70 sec)
Records: 0 Duplicates: 0 Warnings: 0

ALGORITHM=copy，用于標識改操作是否需要整個表。
mysql> CREATE INDEX ind_t_column_name ON test(column_name) ALGORITHM=copy;
Query OK, 312928 rows affected (5.93 sec)

Records: 312928 Duplicates: 0 Warnings: 0

注：

SET old_alter_table=0; --ALGORITHM=inplace

SET old_alter_table=1; --ALGORITHM=copy