mysql 5.7 online ddl_MySQL 5.7 online ddl的一些改进

MySQL DBA应该都知道，数据库操作里面，DDL操作(比如CREATE，DROP，ALTER等)代价是非常高的，特别是在单表上千万的情况下，加个索引或改个列类型，就有可能堵塞整个表的读写。

从MySQL 5.6开始，大家期待的Online DDL出现了，可以实现修改表结构的同时，依然允许DML操作(select,insert,update,delete)。但改变字段类型，比如从CHAR变为VARCHAR；修改字段类型，比如增加VARCHAR大小，这些在MySQL 5.7之前还是会锁表的。从MySQL 5.7开始就支持了对于tinyint、int、smallint、bigint等数值类型的数据类型，自身位大小的增大或减小是支持ONLINE的(注意：不支持从tinyint变更为int)。还支持了VARCHAR类型的在线增大，但也有些限制。

支持了对于tinyint、int、smallint、bigint等数值类型的数据类型，自身位大小的增大或减小是支持ONLINE的(注意：不支持从tinyint变更为int)，如下：

mysql> alter table t1 change id id bigint(10),ALGORITHM=INPLACE,LOCK=NONE;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t1 change id id bigint(30),ALGORITHM=INPLACE,LOCK=NONE;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

另外，如果是更改字段属性(类型不变)根据类型不同支持的Online DDL也是有限的，如把允许为NULL变更为不允许为NULL就不支持。

mysql> alter table sbtest change column dd dd varchar(100) not null,ALGORITHM=INPLACE,LOCK=NONE;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try ALGORITHM=COPY;

但是在线支持变更comment属性。

mysql> alter table info change dd dd varchar(100) null comment "test",ALGORITHM=INPLACE,LOCK=NONE;

需要注意的是，在MySQL 5.6之前使用ALTER TABLE … ALGORITHM=INPLACE的表不支持包含时间列(DATE，DATETIME，TIMESTAMP)，不然会报错。

MySQL 5.7版本支持重命名索引和修改varchar的大小(增大不能减小)，且无需table-copy(秒级增加varchar大小)。这两项操作在之前的版本中，都需要重建索引或表，适用于各引擎。

mysql> alter table sbtest ALGORITHM=INPLACE,change column dt td varchar(100);

但存在限制，即只支持0~255字节内的或者255以上字节间的增加，也就是说若从254增到256时不能使用INPLACE算法(增加到255可以)，必须使用COPY算法，否则会报错。这个原理就是varchar会在头部存储一个长度，如果小于255就是一个BYTES字节，8位；如果大于255当然就需要两个字节 了。头部都变了，自然要重新copy table了。另外使用INPLACE算法缩小VARCHAR的ALTER TABLE也是不支持的，必须用COPY算法。

mysql> alter table sbtest add column td varchar(10);

Query OK, 0 rows affected (10.81 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest ALGORITHM=INPLACE,change column td td varchar(254);

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest ALGORITHM=INPLACE,change column td td varchar(256);

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

总结：

1.对于tinyint、int、smallint、bigint等数值类型，自身位大小的增大或减小是支持ONLINE的(注意：不支持从tinyint变更为int)

2.VARCHAR类型，只支持0~255字节内的或者255以上字节间的增加，不支持缩小

3.online ddl尽量在业务低峰期执行，以免产生MDL锁