MySQL 5.7 online ddl的一些改进

2019独角兽企业重金招聘Python工程师标准>>>

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大小）。这两项操作在之前的版本中，都需要重建索引或表，适用于各引擎。

转载于:https://my.oschina.net/u/3611008/blog/1923194