袋鼠云技术团队 关注
手机版

MySQL 5.7 online ddl的一些改进

  1. 云栖社区>
  2. 袋鼠云技术团队>
  3. 博客>
  4. 正文

MySQL 5.7 online ddl的一些改进

newdba@dtstack 2018-04-15 21:28:07 浏览4349 评论0

摘要: MySQL DBA应该都知道,数据库操作里面,DDL操作(比如CREATE,DROP,ALTER等)代价是非常高的,特别是在单表上千万的情况下,加个索引或改个列类型,就有可能堵塞整个表的读写。 从MySQL 5.6开始,大家期待的Online DDL出现了,可以实现修改表结构的同时,依然允许DML操作(select,insert,update,delete)。

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锁

用云栖社区APP,舒服~

【云栖快讯】云栖社区技术交流群汇总,阿里巴巴技术专家及云栖社区专家等你加入互动,老铁,了解一下?  详情请点击

网友评论

newdba@dtstack
文章15篇 | 关注5
关注
MySQL 是全球最受欢迎的开源数据库,阿里云MySQL版 通过深度的内核优化和独享实例提供... 查看详情
提供海量、安全和高可靠的云存储服务。RESTful API的平台无关性,容量和处理能力的弹性... 查看详情
大数据开发套件(Data IDE),提供可视化开发界面、离线任务调度运维、快速数据集成、多人... 查看详情
为您提供简单高效、处理能力可弹性伸缩的计算服务,帮助您快速构建更稳定、安全的应用,提升运维效... 查看详情
建站4折

建站4折