online ddl是mysql 5.6版本新增的功能,之前版本做ddl,为了避免堵塞DML一般都是选择pt-osc工具,或者是采用主从滚动操作的方式。采用滚动的方式,操作复杂,采用pt-osc工具则有一些限制,比如表需要主键或者唯一键,否则不予执行,而且触发器在一定情况下会导致死锁,对业务有一定的影响。mysql 5.6版本的online ddl则避免了上述限制,能够在不堵塞DML的前提下进行。online ddl指的是innodb表,而不是myisam表。下面是测试过程,以及online ddl的限制。

根据我们日常常见的ddl操作进行测试:

原表:

 CREATE TABLE `online_test` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `c1` bigint(20) DEFAULT NULL,

  `c2` bigint(20) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1.增加字段

alter table online_test add column c3 int;

不堵塞读写,同时表会rebuild,磁盘空间增加原表大表的一倍,整个过程中,会产生一个临时表。即使指定algorithm=inplace也是需要rebuild,执行时间和表大表有关

在操作完成之前,新加的列不能使用

如果指定algorithm=copy,则会堵塞写

2.删除字段

alter table online_test drop c3;

不堵塞读写,同时表会rebuild,磁盘空间增加原表大表的一倍,整个过程中,会产生一个临时表。即使指定algorithm=inplace也是需要rebuild,执行时间和表大表有关。

在ddl完成之前,删除列还能继续使用。

如果指定algorithm=copy,则会堵塞写

3.更改字段类型

alter table online_test modify c3 varchar(20);

整个过程堵塞写,Waiting for table metadata lock .磁盘空间增加原表大表的一倍,整个过程中,会产生一个临时表。执行时间和表大表有关

不能指定algothm=inplace,报错:ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

4.更改字段默认值

alter table online_test modify c3 varchar(20) default 'test';

不堵塞读写,同时整个过程非常迅速,基本不消耗时间

5.添加secondary key

alter table online_test add key(c3)

不堵塞读写,同时表不会rebuild,也就是磁盘空间并不需要增加一倍,但是还是会产生一个临时的frm文件。

如果指定algorithm=copy,则会堵塞读写,同时表需要rebuild。空间增加

6.更改字段名字段,类型不变

alter table online_test change c7 c8 int;

不堵塞,而且很快,基本不消耗时间

7.更改字段名字段,类型改变

alter table online_test change c8 c7 varchar(20);

堵塞写,同时表rebuild,空间增加

8.删除secondary key

alter table online_test drop key c7;

不堵塞读写,同时很快完成,基本不消耗时间

9.增加主键

alter table online_test add primary key(id)

不堵塞读写,表需要rebuild,空间增加

10.删除主键

alter table online_test drop primary key;

堵塞写,Waiting for table metadata lock .磁盘空间增加原表大表的一倍,整个过程中,会产生一个临时表。执行时间和表大表有关

11.增加自增字段

堵塞写,Waiting for table metadata lock .磁盘空间增加原表大表的一倍,整个过程中,会产生一个临时表。执行时间和表大表有关

12.将普通key调整为主键

不堵塞读写,但是表会rebuild的,因此会产生临时表,磁盘空间会增加,执行时间和表大小有关。

13.去除字段的自增属性

堵塞写,Waiting for table metadata lock .同时表会rebuild

14.更改表的主键(同一条语句删除旧主键,添加新主键)

不堵塞读写,但是表会rebuild

15.变更表的字符集(ALTER TABLE online_test CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;)

不堵塞读写,同时很快完成,基本不消耗时间

16.optimize table

不堵塞读写。optimize对innodb表采用的是rebuild+analyze 方式,因此磁盘空间增加。

17.analyze table

不堵塞读写,而且操作很快完成,基本不消耗时间

18.修改字段属性为自增

堵塞写,同时表会rebuild

基本日常的DDL操作都能覆盖了,归纳如下:

堵塞写的操作:

    指定algorithm=copy的都会导致堵塞写

    指定lock=exclusive都会导致堵塞写

    更改字段类型

    change更改列名同时更改了字段类型

    删除主键

    增加自增字段

    修改字段属性为自增

    删除字段的自增属性

其他操作均不会堵塞读写。相比pt-osc的需要主键或者唯一键的提前,online ddl则无需该限制。

online ddl在执行过程中,会将增量保存innodb_online_alter_log_max_size指定大小的内存中,如果该表较大同时DML频率较高,

则需要扩大该变量的值,以保证执行过程中该大小能容纳该表的所有的DML。否则会报错:

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