从mysql5.6开始以后的版本,支持OnlineDDL,这个功能是mysql梦寐以求的功能,要知道在mysql5.6以前的版本,做DDL变更,可是会锁表,业务无法做DML操作,只能查询,其中痛苦,只有经历过的同学才会知道。
本文只探索Mysql5.7版本的OnlineDDL之VARCHAR字段扩容操作,以前在生产做VARCHAR字段扩容时,发现有时候扩容操作非常快,基本在秒级就返回了,有时候扩容,要好几十秒,甚至好几分钟才返回,当时以为是业务表数据量导致的,后来注意到2个数据量差不多的表,做VARCHAR字段扩容操作,执行时间相差好几十倍时,才发现原来不仅仅是数据量的原因。
下面就来验证一下,首先用sysbench模拟出一张条记录的表,sysbench安装很简单,这里就不做介绍了,不会的同学,可以在网上搜索一下
首先,修改字段c3从varchar(10)变成varchar(11),如下所示
可以看到耗时毫秒,秒级返回的。再看看将字段c3从varchar(11)变成varchar(),如下所示
为什么会这样呢,同一个表,从长度10到11,秒级返回,变成时,执行时间竟然变成9分41秒,这时间也差得太多了。
于是去看了一下官方文档,看看是什么原因,原来varchar的字段,如果所占用的字节小于时,用一个字节记录占用字段长度,并且修改字段长度时,默认的算法是inplace,允许并发DML,此时只需要修改表的元数据信息,即可完成字段长度变更。
在这里需要注意,如果你的表的字符集是utf8,那么varchar(1)是占用3个字节长度,utf8mb4,那么varchar(1)是占用4个字节长度,也即是说当数据库字符集为uft8时,列定义少于85,数据库字符集为uft8mb4时,列定义少于63,字段长度扩容时,算法是inplace,此时只需要修改表的元数据信息,秒级完成字段扩容。
可是当字段占用字节数超过时,mysql会采用两个字节记录占用字段长度,这个时候,页内头部存储信息发生改变了,就只能采取copy算法去做字段长度扩容了。
采用copy算法,可以看到生成了一个临时表(#sql-_6.ibd),因为要将原来老表的所有数据全部拷贝到新表中,所以时间会非常长。
在这里再多说一句,MysqlOnlineDDL是如何做到并发DML的,原来在做DDL期间,mysql会记录所有在此表上所有的DML操作,并将日志写到一个内存区域里,这个内存区域大小由innodb_online_alter_log_max_size控制,其默认大小为M,如果产生的日志超出该容量大小,则会抛出类似如下的异常提示:
导致你的DDL语句执行失败,如果想继续执行DDL语句,则需要调整innodb_online_alter_log_max_size参数,比较繁忙的数据库,此参数建议调整为M。