sql查询多一个字段 数据增加了几十倍是什么原因_Mysql Online DDL之VARCHAR字段扩容探索...

从mysql5.6开始以后的版本，支持Online DDL，这个功能是mysql梦寐以求的功能，要知道在mysql5.6以前的版本，做DDL变更，可是会锁表，业务无法做DML操作，只能查询，其中痛苦，只有经历过的同学才会知道。

本文只探索Mysql5.7版本的 Online DDL之VARCHAR字段扩容操作，以前在生产做VARCHAR字段扩容时，发现有时候扩容操作非常快，基本在秒级就返回了，有时候扩容，要好几十秒，甚至好几分钟才返回，当时以为是业务表数据量导致的，后来注意到2个数据量差不多的表，做VARCHAR字段扩容操作，执行时间相差好几十倍时，才发现原来不仅仅是数据量的原因。

下面就来验证一下，首先用sysbench模拟出一张10000000条记录的表，sysbench安装很简单，这里就不做介绍了，不会的同学，可以在网上搜索一下

mysql> select count(*) from sbtest1;+----------+| count(*) |+----------+| 10000000 |+----------+1 row in set (2.44 sec)

首先，修改字段c3从varchar(10)变成varchar(11)，如下所示

mysql>  alter table sbtest1 modify c3 varchar(11) not null default '';Query OK, 0 rows affected (0.32 sec)Records: 0  Duplicates: 0  Warnings: 0

可以看到耗时320毫秒，秒级返回的。再看看将字段c3从varchar(11)变成varchar(100)，如下所示

mysql>  alter table sbtest1 modify c3 varchar(100) not null default '';Query OK, 10000000 rows affected (9 min 41.85 sec)Records: 10000000  Duplicates: 0  Warnings: 0

为什么会这样呢，同一个表，从长度10到11，秒级返回，变成100时，执行时间竟然变成9分41秒，这时间也差的太多了。

于是去看了一下官方文档，看看是什么原因，原来varchar的字段，如果所占用的字节小于256时，用一个字节记录占用字段长度，并且修改字段长度时，默认的算法是inplace，允许并发DML，此时只需要修改表的元数据信息，即可完成字段长度变更。

在这里需要注意，如果你的表的字符集是utf8，那么varchar(1)是占用3个字节长度，utf8mb4，那么varchar(1)是占用4个字节长度，也即是说当数据库字符集为uft8时，列定义少于85，数据库字符集为uft8mb4时，列定义少于63，字段长度扩容时，算法是inplace，此时只需要修改表的元数据信息，秒级完成字段扩容。

可是当字段占用字节数超过255时，mysql会采用两个字节记录占用字段长度，这个时候，页内头部存储信息发生改变了，就只能采取copy算法去做字段长度扩容了。

[mysql@localhost sbtest]$ du -sm *1       db.opt1       sbtest1.frm2944    sbtest1.ibd1       #sql-9212_6.frm2648    #sql-9212_6.ibd

采用copy算法，可以看到生成了一个临时表(#sql-9212_6.ibd)，因为要将原来老表的所有数据全部拷贝到新表中，所以时间会非常长。

在这里再多说一句，Mysql Online DDL是如何做到并发DML的，原来在做DDL期间，mysql会记录所有在此表上所有的DML操作，并将日志写到一个内存区域里，这个内存区域大小由innodb_online_alter_log_max_size控制，其默认大小为128M，如果产生的日志超出该容量大小，则会抛出类似如下的异常提示：

Error:1799SQLSTATE:HY000(ER_INNODB_ONLINE_LOG_TOO_BIG)Message: Creating index 'idx_aaa' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.

导致你的DDL语句执行失败，如果想继续执行DDL语句，则需要调整innodb_online_alter_log_max_size参数，比较繁忙的数据库，此参数建议调整为512M。