如何使用MySQL 5.6的新特性Online DDL - ApsaraDB RDS

本文介紹如何使用MySQL 5.6的新特性Online DDL。

RDS MySQL 5.6支援Online DDL特性。

Online DDL（線上DDL）功能允許在表上執行DDL的操作（例如建立索引）的同時不阻塞並發的DML操作和查詢（select）操作。

說明

從低版本（例如 RDS MySQL 5.5）升級到 RDS MySQL 5.6，第一次執行 DDL 時有可能會因為表資料的檔案格式仍舊是 5.5 版本而不支援 Online DDL 特性。這種情況可以通過執行以下命令來轉換：

alter table <表名> engine=innodb;

更多版本特性請參見AliSQL核心小版本發布記錄。

Online DDL的限制

操作	是否支援Inplace方式	是否需要Copy Table	是否允許並發DML	是否允許並發查詢	備忘
建立普通索引	支援	不需要	允許	允許	-
建立全文索引	支援	不需要	不允許	允許	第一個全文索引需要通過Copy Table的方式建立；其後的全文索引可以通過Inplace方式建立。
刪除索引	支援	不需要	允許	允許	僅修改表中繼資料metadata。
最佳化表	支援	需要	允許	允許	如果表上建立有全文索引，則不支援algorithm=inplace選項。
設定列預設值	支援	不需要	允許	允許	僅修改表中繼資料metadata。
修改自增列值	支援	不需要	允許	允許	僅修改表中繼資料metadata。
添加外鍵約束	支援	不需要	允許	允許	`set foreign_key_checks=0;` 來關閉 foreign_key_checks，避免拷貝表。
刪除外鍵約束	支援	不需要	允許	允許	foreign_key_checks選項開啟或者關閉都可以。
重新命名列	支援	不需要	允許	允許	如果僅僅修改欄位名稱，而不要修改欄位類型，是支援並發DML操作的。
添加列	支援	需要	允許	允許	在添加auto_increment自增列時，是不允許並發 DML 操作的。儘管支援Algorithm=INPLACE ，但因為資料實質上需要重新組織，因此操作的開銷高昂。
刪除列	支援	需要	允許	允許	儘管支援Algorithm=INPLACE ，但因為資料實質上需要重新組織，因此操作的開銷高昂。
修改各列順序	支援	需要	允許	允許	儘管支援Algorithm=INPLACE ，但因為資料實質上需要重新組織，因此操作的開銷高昂。
修改Row_Format屬性	支援	需要	允許	允許	儘管支援Algorithm=INPLACE ，但因為資料實質上需要重新組織，因此操作的開銷高昂。
修改Key_Block_Size屬性	支援	需要	允許	允許	儘管支援Algorithm=INPLACE ，但因為資料實質上需要重新組織，因此操作的開銷高昂。
設定列為空白值Null	支援	需要	允許	允許	儘管支援Algorithm=INPLACE ，但因為資料實質上需要重新組織，因此操作的開銷高昂。
設定列不為空白值NOT Null	支援	需要	允許	允許	該操作需要將SQL_MODE 參數設定為STRICT_ALL_TABLES或STRICT_TRANS_TABLES才能成功。如果列值中包含空值（NULL），則該DDL 操作會失敗。儘管支援Algorithm=INPLACE ，但因為資料實質上需要重新組織，因此操作的開銷高昂。
修改列的資料類型	不支援	需要	不允許	允許	-
添加主鍵	支援	需要	允許	允許	儘管支援Algorithm=INPLACE ，但因為資料實質上需要重新組織，因此操作的開銷高昂。如果涉及的列需要轉換為NOT NULL，則不支援Algorithm=INPLACE。
刪除主鍵並添加新主鍵	支援	需要	允許	允許	僅當在同一個Alter Table語句中（刪除主鍵的DDL語句）添加新主鍵才支援Algorithm=INPLACE。因為資料實質上需要重新組織，因此操作的開銷高昂。
刪除主鍵	不支援	需要	不允許	允許	-
Convert character set	不支援	需要	不允許	允許	如果新的字元集編碼不同，需要重建表。
Specify character set	不支援	需要	不允許	允許	如果新的字元集編碼不同，需要重建表。
帶force選項重建表	支援	需要	允許	允許	如果表上有全文索引，則不支援Algorithm=Inplace選項。
重建表 alter table ... engine=innodb	支援	需要	允許	允許	如果表上有全文索引，則不支援Algorithm=Inplace選項。
設定表的 persistent statistics	支援	不需要	允許	允許	僅修改表的中繼資料metadata。

是否支援Inplace方式：對應DDL語句的Algorithm選項，通過Inplace方式執行DDL。相比Copy Table的方式，可以減少空間和I/O消耗。
是否需要Copy Table：對應DDL語句的Algorithm選項，通過Copy Table的方式執行DDL。DDL執行期間會佔用更大的磁碟空間和消耗更多的I/O。
是否允許並發DML：對應DDL語句的Lock選項，DDL執行期間是否支援並發DML操作。
是否允許並發查詢：DDL語句執行期間是否支援並發查詢操作（通常都是支援的）。
MySQL官方文檔請參見Online DDL 概覽。
DDL操作執行時需要修改表的中繼資料（metadata），有可能會遇到等待表中繼資料鎖的情況（waiting for table metadata lock），該情況的處理方式請參見解決MDL鎖導致無法操作資料庫的問題。
Inplace和Copy Table是相反的2種處理方式；但即使DDL支援Inplace選項，某些操作在整個執行過程中也會部分涉及到Copy Table，例如上表中的添加列操作。

Online DDL選項建議

Algorithm=Inplace ：為了避免Copy Table導致的執行個體效能問題（空間、I/O問題），建議在DDL中包含該選項。如果DDL操作不支援Algorithm=Inplace方式，DDL操作會立刻返回錯誤。
```
alter table area_bak algorithm=inplace, modify father text;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
```
Lock=None ：為了在DDL操作過程中不影響業務DML 操作，建議在DDL中包含該選項。如果DDL操作不支援Lock=None （允許並行DML操作）選項，DDL操作會立刻返回錯誤。
```
alter table area ALGORITHM=copy, lock=none,CONVERT TO CHARACTER SET utf8mb4;

ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.
```

預設情況下RDS MySQL會盡量使用algorithm=inplace以及lock=none來進行DDL操作，因此預設可以不指定這兩個選項。但如果擔心DDL操作對系統負載有影響或阻塞對目標表的DML操作，建議使用algorithm=inplace或lock=none選項來操作，這樣如果系統對某一個選項不支援，會立刻返回錯誤，避免影響業務。

樣本

alter table area algorithm=inplace, lock=none, add index idx_fa (father);

說明

所有的DDL操作均建議在業務低峰期進行，避免對業務產生影響。

對不支援Online DDL的操作（例如RDS MySQL 5.5），可以考慮通過Percona的Schema Online Change工具來操作。

Alter Table文法請參見ALTER TABLE Syntax。

異常處理

在對某些大表的Online DDL過程中，有時會碰到下面的錯誤：

alter table rd_order_rec add index idx_cr_time_detail (cr_time,detail);

ERROR 1799(HY000): Creating index 'idx_cr_time_detail' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.

原因

在進行Online DDL（不阻塞並發DML）的過程中，每個被修改的表或者建立的索引都會使用一個臨時日誌來儲存 DDL過程中並發DML操作的記錄。該臨時記錄檔的大小可以根據需要從參數innodb_sort_buffer_size指定的大小擴充到參數innodb_online_alter_log_max_size指定的大小。

如果有臨時記錄檔大小超過上限，則該DDL語句返回失敗並且所有沒有提交的並發DML操作會被復原。因此增加 innodb_online_alter_log_max_size參數的大小可以允許DDL過程中更多的並發DML操作，但是較大的值也會使在DDL操作末尾階段的鎖定表應用日誌中的資料的過程持續更長的時間。

解決方案

針對MySQL 5.6/5.7，可以在控制台修改innodb_online_alter_log_max_size參數值，詳細步驟請參見設定執行個體參數。