版本: mysql5.7+
问题: 操作mysql字段长度扩容,是否会影响业务?
影响说明:
1 如果大表操作,使用copy方式(非online方式),则添加过程中会造成主从延迟,表锁面积大等业务问题,所以在做modify的时候,需要提前知道操作风险.
2 针对变更主要是varchar变更,比如从vachar(10) modify varchar(100)
3 采用的是mysql自己变更方式,不采用pt,gh工具
4 如果字段存在二级索引,需要特殊处理
省流结论:
1 缩减字段一定是copy方式,锁表,时间长
2 增长字段如果跨越255字节(具体需要自己通过字符集计算),copy方式
3 如果在255字节内扩张,schema方式,不需要数据变更 norebuild
4 如果超过255字节变更,不需要数据变更 norebuild 存在溢出页768问题另算.
mysql 字段扩展详解
mysql5.7 对varchar() 类型的区间定义,第一个区间为1-255(byte)，第二个为256(byte)以上.
为什么这么定义: 官方定义,源码就这么写的,人家直接英文,latin-1,255够用了,为中国考虑一下? 不存在的....
varchar中的数字代表的是字符,所以具体字节需要自己计算:
1. 如果定义的是utf8 varchar(85)是节点
2. 如果定义的是utf8-mb4 则varchar(64)是节点
mysql5.6+之后变更支持online的方式,所以现在存在copy/inplace rebuild/inplace norebuild 方式:
[详细请看](https://juejin.cn/post/7345107078904610868)
因此如果变更范围内包含了255字节的情况,则会进行copy方式
copy方式会进行数据复制,表重建等操作,耗时很长,并且锁表
inplace norebuild 速度快,没有数据锁
如果这个字段存在二级索引,修改字段的时候相当于修改了二级索引,所以需要考虑二级索引情况:
二级索引的最大长度又参数--innodb-large-prefix决定:
如果扩容之后,字段宽度在默认范围之内,则建立全字段索引,但是如果超过限制,则会建立前缀索引,可能增加回表
--innodb-large-prefix 说明:
如果为ON:最大长度为3072字节 如果为OFF 则最大为3072字节
ps: mysql5.7.7 以上默认为ON,小于5.7.7则为OFF,可以动态修改。
show variables like "innodb_large_prefix"; 查看参数情况
所以如果变更表结构,并且这个字段存在二级索引,还需要看这个变更对二级索引的影响.
当字符集为UTF-8时:
1 innodb-large-prefix 为ON,则索引字段0-85内变更很快,85-1024内也会很快,
但是跨越85和1024的时候,就会存在性能等问题
2 innodb-large-prefix 为OFF,则上限变成了256情况
当字符集为UTF-8MB4:
1 innodb-large-prefix 为ON 则节点为:63和768
2 innodb-large-prefix 为OFF 则节点为:63和191
结论: 跨越节点操作慢,会产生性能问题和业务问题,谨慎操作.
mysql varchar溢出页问题
mysql的ROW格式也是存在版本变迁的,作为开源项目需要适配各种场景.但是作为普通用户用的最多的是默认:
show variables like "innodb_default_row_format"; 查看对应mysql的ROW格式.
通常存在:compact和dynamic这两种:
当行格式为compact时:
如果使用compact row format，当变长字段超过768字节后，在索引中仅存储前768字节，其余部分会溢出存储(单独页)
当定长字段超过768字节后，也会变成变长字段，如char(255)采用utf8mb4编码时最大会超过768字节；
当一列的大小不超过768字节时，不会使用溢出页，这会节省一些IO，因为值全部存储在b-tree节点，但这会导致b-tree
节点因为存储太多数据而不是k-v，使索引效率变差。表包含太多BLOB列会使b-tree节点变得很丰满，只能存储很少
的行，使得整个索引低效，相对来说，短行或者off-page列索引效率会高很多；
当行格式为dynamic时:
当使用dynamic row format时，InnoDB能将过长的变长字段完全存入off-page，聚簇索引记录里面仅包含20个字节
指向off-page。定长字段超过768字节会变成变长字段。
至于何时发生off-page存储，依赖于page size以及行大小，当一行太大，最长的列（并不一定是变长字段）将被
off-page存储，直到聚集索引上的记录符合page要求；TEXT和BLOB字段在行中存储不会超过40字节
dynamic row format通过存储一行的全部内容来保证效率（像compact一样），但是dynamic避免b-tree节点
存储太多的长字段类型，dynamic基于这一思想：如果一部分数据off-page存储，它通常最有效的方式是全部数据
off-page存储,dynamic尽量使短字段保留在b-tree节点、减少溢出页的数量。
从官方文档可以看出，compact和dynamic有明显的区别，一个是发生off-page存储时，b-tree节点上存储的数据大小，
前者是768字节，后者是20字节；另一个区别是何时发生off-page存储，compact说的很清楚，超过768就会发生，
而dynamic则只说依赖于page size及行大小。
