mysql字段扩容_关于MySQL5.7版本varchar字段宽度扩容的⼀
些相关知识与实践-yh
关于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
1
utf8+
⽆⼆级索引
1-85
86+
2
utf8+
有⼆级索引(innodb_large_prefix=ON)
1-85
86-
1024
1025+
utf8+
有⼆级索引(innodb_large_prefix=OFF)
1-85
86-
255
256+
4
utf8mb4+
⽆⼆级索引
1-63
64+
5
utf8mb4+
有⼆级索引(innodb_large_prefix=ON)
1-63
64-
768
769+
6
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参考⽂档
⼆、关于以上内容的测试验证
========
以上宽度范围区间验证测试流程========
环境参数确认
show variables where variable_name in ('innodb_large_prefix' ,'innodb_page_size'); show variables like '%character%';
varchar2最大长度先确认前提条件
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;
insert into t_yhtest01 (info) select info from t_yhtest01;
insert into t_yhtest01 (info) select info from t_yhtest01;
insert into t_yhtest01 (info) select info from t_yhtest01;
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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。