MySQL 5.6的Online DDL功能測試

MySQL 5.6的Online DDL功能測試

online DDL的前身是 innodb fast index creation(5.1和5.5), 5.6裡對這個功能做了擴充：很多alter table的操作繞開了 table copying，支援DML並行作業。

 一、online ddl的支援測試：
1、主鍵的增刪
 主鍵添加：
 支援online ddl，加主鍵過程中支援 並發的DML
主鍵刪除:
不支援online ddl，刪除主鍵過程需要copy table，導致DML被阻塞

2、索引的增刪
1）普通索引
5.5使用新的邏輯（fast index creation)，不再copy表，（參數old_alter_table）,加快的索引的建立和刪除，
                            但是會阻塞DML，可以select
 5.6中建立和刪除索引不再阻塞dml
 2）unique索引
 同上

3、欄位的增刪改
 新增：
 （alter table test add hehe2 int default 100）
5.5阻塞 DML，支援select
 5.6支援並發DML
刪除：
 （alter table test drop hehe2）
5.5阻塞 DML，支援select
 5.6支援並發DML
修改：
  只改欄位名：
(alter table test change hehe2 hehe20 int default '100')
 5.5阻塞 DML，支援select
 5.6支援online ddl，並發DML
  修改欄位類型：
 （alter table test change hehe20 hehe2 varchar(100) default '100'）
5.5阻塞 DML，支援select
 5.6不再支援online ddl

http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
中的Table 14.5列出了詳細的online ddl支援的操作類型， 但是實際上並不需要記憶這麼多：

二、新引入文法：
 在5.6中，alter table增加了新的文法：
ALGORITHM [=] {DEFAULT|INPLACE|COPY}
 LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
 ALGORITHM：
INPLACE： 不copy table
 COPY：    copy table
 DEFAULT：

LOCK:
 DEFAULT:    mysql自己選擇鎖定資源最少的方式
NONE:      支援select和DML
 SHARED：  支援select，不支援DML
 EXCLUSIVE：不支援select，不支援DML

可以借用這個新增文法測試是否alter table語句支援online DDL:
建立一個表結構一樣的表，儲存少量的資料：
root:3306:popo>alter table test change hehe2 hehe20 int default '100' ,LOCK=NONE;             
 ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Cannot change column type INPLACE. Try LOCK=SHARED.
根據提示，這個欄位類型修改的alter table不支援並發的DML操作

root:3306:popo>alter table test change hehe2 hehe20 int default 100,  ALGORITHM=inplace;
 ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
同樣 這個alter table 也需要copy table操作。

 三、online ddl相關參數和原理：
innodb_online_alter_log_max_size
online ddl的原理是，mysql把在ddl時間內的所有的 插入，更新和刪除操作記錄到一個記錄檔， 然後再把這些增量資料應用到相應的表上(等表上的事務完全釋放後)，這個臨時記錄檔的上限值由innodb_online_alter_log_max_size指定,每次擴充innodb_sort_buffer_size的大小 該參數如果太小有可能導致DDL失敗，這期間所有的未提交的並發DML操作都會復原；但是如果太大會可能會導致後DDL操作最後鎖定表的時間更長（鎖定表，應用日誌到表上）。 每一個變化的索引或者表都會分配一個。

本文永久更新連結地址：