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
中的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操作最后锁定表的时间更长(锁定表,应用日志到表上)。 每一个变化的索引或者表都会分配一个。