mysql 字段扩容_关于MySQL5.7版本varchar字段宽度扩容的一些相关知识与实践-yh-优快云博客

本文介绍了MySQL5.7中varchar字段扩容的相关知识，包括不同字符集下的宽度区间，以及扩容对有无二级索引的影响。测试验证了扩容速度与耗时，强调合理预留字段宽度以避免后续操作风险。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

关于MySQL5.7版本varchar字段宽度扩容的一些相关知识与实践

一、字段宽度的一些扩展知识

在工作中，设计业务场景时，如果设计期能明确预计到未来线上业务表行数会很多，达到千万级别以上，且可能会存在新增字段的后续操作，可以适当的设计varchar类型的预留字段(不建议但理论层面可行，请多考虑潜在的风险点)，预留字段的宽度应当遵循指定区间内宽度最小的原则，相关区间和说明见下文总结：

我们根据MySQL5.7版本的onlineDDL快速扩大字段长度的相关文档，梳理了varchar类型字段宽度的区间定义，第一个区间为1-255字节，第二个区间为256字节以上

对应的存储字符个数根据字段字符集定义来决定：

(1)

当字段字符集为utf8时，字符个数对应的变更区间范围为1-85,86+两个区间；

(2)

当字段字符集为utf8mb4时，字符个数对应的变更区间范围为1-63,64+两个区间；

因为varchar后面括号里的整数值对应的单位是字符个数，DBA在对每个区间范围内进行modify字段类型操作时会很快，操作立刻会完成，同时进行rename操作和comment修改操作(此两项操作不会影响到线上业务，仅方便后续维护)即可将预留字段快速变更为生产可用字段(但是我们依然不推荐这样做，因为这样会增加项目的复杂程度以及作为整个系统的关键节点且是单点的数据库层面的风险系数)

同时，需要考虑另一个问题，扩充宽度的字段上是

否存在二级索引，如果存在二级索引，字段宽度在默认值范围内时，字段可以建立全宽度索引，超出则会变为前缀索引，前缀索引的索引位数默认按照服务端参数innodb_large_prefix，如果是ON，则索引最大为3072字节，如果是OFF，则索引最大为767字节

此参数默认值取决于版本默认值或者服务端设定值，在5.7.7及以上版本默认为ON，小于5.7.7版本默认是OFF，相关官方文档说明见如下截图：

对应的，当我们在扩大字段宽度的时候，如果扩大的宽度在上面所说的utf8的86+区间扩大或者utf8mb4的64+区间扩大时，当字段存在二级索引时，触发索引变成前缀索引的过程会导致DDL变更速度变缓慢，具体可以细分为如下几种情况。

(1)当类型为utf8，innodb_large_prefix值为ON时，变更区间在86-1024区间中，字段宽度扩大变更会很快完成，但是在1025+的时候，字段宽度扩大会变慢，变更完成后二级索引会变为前缀索引，索引字段前1024位

(2)当类型为utf8，innodb_large_prefix值为OFF时，变更区间在86-255区间中，字段宽度扩大变更会很快完成，但是在256+的时候，字段宽度扩大会变慢，变更完成后二级索引会变为前缀索引，索引字段前256位

(3)当类型为utf8mb4时，innodb_large_prefix值为ON时，变更区间在64-768区间中，字段宽度扩大变更会很快完成，但是在769+的时候，字段宽度扩大会变慢，变更完成后二级索引会变为前缀索引，索引字段前768位

(4)当类型为utf8mb4时，innodb_large_prefix值为OFF时，变更区间在64-191区间中，字段宽度扩大变更会很快完成，但是在192+的时候，字段宽度扩大会变慢，变更完成后二级索引会变为前缀索引，索引字段前768位

注意：以上几点都是基于数据库页大小为标准的16KB大小，如果是8KB大小或者4KB大小，则相应参数innodb_page_size值映射还会不同

汇总表格如下，其中符合某情形跨区间变更操作扩大字段宽度都会执行缓慢

序号

情形(innodb数据页大小为16KB)

区间1

区间2

区间3

utf8+

无二级索引

1-85

86+

utf8+

有二级索引(innodb_large_prefix=ON)

1-85

86-

1024

1025+

utf8+

有二级索引(innodb_large_prefix=OFF)

1-85

86-

255

256+

utf8mb4+

无二级索引

1-63

64+

utf8mb4+

有二级索引(innodb_large_prefix=ON)

1-63

64-

768

769+

utf8mb4+

有二级索引(innodb_large_prefix=OFF)

1-63

64-

191

192+

其中1024=3072/3向下取整

255=767/3向下取整

768=3072/4向下取整

191=767/4向下取整

5.7online DDL参考文档

二、关于以上内容的测试验证

========

以上宽度范围区间验证测试流程========

(0)

环境参数确认

show variables where variable_name in ('innodb_large_prefix' ,'innodb_page_size');

show variables like '%character%';

先确认前提条件

innodb_large_prefix在当前生产环境版本值为ON

innodb_page_size值在当前生产环境版本值为16K

数据库全局字符集为utf8mb4

以下为测试内容

(1)

创建表t_yhtest01，

测试字段info上无二级索引的字段扩容操作

并插入数据达到一定量使变更操作耗时较为明显

create database yuhaodb;

use yuhaodb

create table t_yhtest01(

id int primary key auto_increment,

info varchar(1) not null default '' comment '字符串测试字段，宽度1字符'

)engine=innodb charset=utf8mb4 comment='测试表1';

insert into t_yhtest01 (info) values ('1');

insert into t_yhtest01 (info) select info from t_yhtest01;

以上插入操作执行多次，共计插入数据200W左右

(1.1)

测试字符串宽度扩充至第一个区间范围最大值63，测试执行时长，期望结果为立刻执行完毕

alter table t_yhtest01 modify column info varchar(63) NOT NULL DEFAULT '' COMMENT '字符串测试字段，宽度63字符';

耗时较短，0.73秒执行完毕，符合预期

(1.2)

测试字符串宽度跨区间扩宽度值下一区间最小值64，测试执行时长，期望结果为耗时很久,comment内容没有改这部分暂不考虑

alter table t_yhtest01 modify column info varchar(64) NOT NULL DEFAULT '' COMMENT '字符串测试字段，宽度63字符';

耗时很长，40秒执行完毕，符合预期

(1.3)

测试字符串在最大宽度区间内64+的字段扩充操作，测试执行时长，期望结果为立刻执行完毕

耗时很短，0.01秒执行完毕，符合预期

(1.4)

测试字符串宽度缩容操作，期望结果为耗时久，不会立刻完成

耗时25秒，执行时间较长，符合预期

(2)

创建表t_yhtest02，

测试字段info上有二级索引(且非前缀索引)的字段扩容操作

并插入数据达到一定量使变更操作耗时较为明显

use yuhaodb

create table t_yhtest02(

id int primary key auto_increment,

info varchar(1) not null default '' comment '字符串测试字段，宽度1字符',

key idx_info(info)

)engine=innodb charset=utf8mb4 comment='测试表2';

insert into t_yhtest02 (info) values ('1');

insert into t_yhtest02 (info) select info from t_yhtest02;

……重复以上插入操作

插入数据200W左右

另外提前记录一下表结构定义，注意二级索引idx_info

(2.1) 测试字符串宽度扩充至第一个区间范围最大值63，测试执行时长，期望结果为立刻执行完毕

alter table t_yhtest02 modify column info varchar(63) NOT NULL DEFAULT '' COMMENT '字符串测试字段，宽度63字符';

耗时较短，0.01秒执行完毕，符合预期

(2.2) 测试字符串宽度跨区间扩宽度值下一区间最小值64，测试执行时长，期望结果为耗时很久

alter table t_yhtest02 modify column info varchar(64) NOT NULL DEFAULT '' COMMENT '字符串测试字段，宽度64字符';

耗时很长，36秒执行完毕，符合预期

(2.3) 测试字符串在最大宽度区间内64+的字段扩充操作，但是未达到触发索引变为前缀索引的范围，测试执行时长，期望结果为立刻执行完毕(2.3.1)扩到700，期望结果为立刻执行完毕

alter table t_yhtest02 modify column info varchar(700) NOT NULL DEFAULT '' COMMENT '字符串测试字段，宽度700字符';

耗时较短，0.01秒执行完毕，符合预期

(2.3.2)扩到768(为当前区间上限值)，期望结果为立刻执行完毕

alter table t_yhtest02 modify column info varchar(768) NOT NULL DEFAULT '' COMMENT '字符串测试字段，宽度768字符';

耗时很短，0.01秒执行完毕，符合预期

另外记录一下此时的表结构

(2.4) 测试字符串在最大宽度区间内64+的字段扩充操作，扩至769、并且达到触发索引变为前缀索引的范围，测试执行时长，期望结果为耗时很长

alter table t_yhtest02 modify column info varchar(769) NOT NULL DEFAULT '' COMMENT '字符串测试字段，宽度769字符';

耗时较长，9秒执行完毕，符合预期

看一下此时表结构中的idx_info

索引变成了前缀索引且对应前缀能覆盖的字节数上限3072，也就是768字符

(2.5) 继续调大到10000宽度，期望能瞬时完成

alter table t_yhtest02 modify column info varchar(10000) NOT NULL DEFAULT '' COMMENT '字符串测试字段，宽度10000字符';

立刻执行完成，符合预期

(2.6) 调短一些，调到700宽度，预期耗时很长，同时观察idx_info定义

alter table t_yhtest02 modify column info varchar(701) NOT NULL DEFAULT '' COMMENT '字符串测试字段，宽度700字符';

耗时长，另外前缀索引变回了全长度的索引

(3)

创建表t_yhtest03，

测试字段info上有二级索引(前缀索引)的字段扩容操作

并插入数据达到一定量使变更操作耗时较为明显

use yuhaodb

create table t_yhtest03(

id int primary key auto_increment,

info varchar(1) not null default '' comment '字符串测试字段，宽度1字符',

key idx_info(info(8))

)engine=innodb charset=utf8mb4 comment='测试表3';

insert into t_yhtest03 (info) values ('1');

insert into t_yhtest03 (info) select info from t_yhtest03;

……重复以上插入操作

(3.1) 测试字符串宽度扩充至第一个区间范围最大值63，测试执行时长，期望结果为立刻执行完毕

alter table t_yhtest03 modify column info varchar(63) NOT NULL DEFAULT '' COMMENT '字符串测试字段，宽度63字符';

耗时较长，9.19秒执行完毕，不符合预期，这个地方有进一步需要探究的可能性且目前存在不确定性，暂时是知识盲区。

(3.2) 测试字符串宽度跨区间扩宽度值下一区间最小值64，测试执行时长，期望结果为耗时很久

alter table t_yhtest03 modify column info varchar(64) NOT NULL DEFAULT '' COMMENT '字符串测试字段，宽度64字符';

耗时很长，22.28秒执行完毕，符合预期

(3.3) 测试字符串在最大宽度区间内64+的字段扩充操作，期望结果耗时很短立刻完成

alter table t_yhtest03 modify column info varchar(10000) NOT NULL DEFAULT '' COMMENT '字符串测试字段，宽度10000字符';

耗时较短，0.01秒执行完毕，符合预期

(3.4) 调短一些，调到100宽度，预期耗时很长，同时观察idx_info定义

alter table t_yhtest03 modify column info varchar(100) NOT NULL DEFAULT '' COMMENT '字符串测试字段，宽度100字符';

耗时很长，27.44秒执行完毕，符合预期

三、varchar相关的字段预留与变更操作流程示例

(1)准备表与数据

建表t_yhtest05，并设计两个相关的预留字段ext1和ext2

use yuhaodb

create table t_yhtest05(

id int primary key auto_increment,

info varchar(10) not null default 'abcdefg' comment '业务字段',

ext1 varchar(1) not null default '' comment '扩展预留字段1，在区间A内',

ext2 varchar(64) not null default '' comment '扩展预留字段2，在区间B内'

)engine=innodb charset=utf8mb4 comment='测试表5';

预留字段因为暂时无相关业务对照关系，故不创建索引，仅在对应区间内创建最小宽度，默认值空字符串

插入数据

insert into t_yhtest05 (info) values ('aaaaa');

insert into t_yhtest05 (info) select info from t_yhtest05;

重复以上操作几次至插入较多的数据

(2)测试将预留字段ext1调整到特定区间内宽度63，并重命名为info01，修改comment内容，期望结果为立刻生效

alter table t_yhtest05 change column ext1 info01 varchar(63) not null default '' comment '预留字段1正式启用命名为info01，在区间A内';

执行耗时0.01s，立刻生效，符合期望

注意：默认值不建议修改，推测如果修改会有全表update的一个过程，会有不短的耗时，见下图示例

(3)测试将预留字段ext2调整到特定区间宽度10000，并命名为info02，修改comment内容，期望结果为立刻生效

alter table t_yhtest05 change column ext2 info02 varchar(10000) not null default '' comment '预留字段2正式启用命名为info02，在区间B内';

执行耗时0.02s，立刻生效，符合期望

注意：默认值不建议修改，推测如果修改会有全表update的一个过程，会有不短的耗时

(4)

最后查看表结构

(5)

再加两个预留默认可以为null的字段ext3和ext4进行变更尝试

alter table t_yhtest05 add column ext3 varchar(1) comment '扩展预留字段3,在区间A内',add column ext4 varchar(64) comment '扩展预留字段4,在区间B内';

(6) 测试将预留字段ext3调整到特定区间内宽度63，并重命名为info03，修改comment内容，期望结果为立刻生效

alter table t_yhtest05 change column ext3 info03 varchar(63) comment '预留字段3正式启用命名为info03，在区间A内';

执行耗时0.01s，立刻生效，符合期望

(7)测试将预留字段ext4调整到特定区间内宽度5000，并重命名为info04，修改comment内容，期望结果为立刻生效

alter table t_yhtest05 change column ext4 info04 varchar(5000) comment '预留字段4正式启用命名为info04，在区间B内';

执行耗时0.01s，立刻生效，符合期望

注意：(2)(3)(6)(7)不可对原字段是否为空的定义进行修改，否则会耗时很久，无法做到立刻生效

四、varchar宽度设置过大的缺点

首先在磁盘上是变长存储，而char在磁盘上是固定长度存储，因此char不易产生碎片，而varchar可能会产生碎片；另外，更长的宽度会导致对内存更大的消耗，所以给出的宽度上限应当是合理估计的数值而非超过估计值上限过多的值。

引自《高性能MySQL》

五、总结

对于后续可能会有加字段或扩字段的操作，我们通过以上的知识和实验，可以有更多的前期应对策略，对于很大的业务表，我们可以适当的做一些预留字段且让字段值宽度在对应区间的最小值，这样在需要启用预留字段的时候，可以做到快速变更，而无需使用第三方工具做长时间且让人心惊胆战的变更操作了。以上内容仅供参考，测试的场景并未涵盖所有可能存在的情形，请各位读者在此基础上进一步探索，如果有错误的地方也欢迎各位指出，我会在确认后修改文章中的错误之处。

如需转载，请注明出处