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+

3

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