mysql 行存储分布
前言
简单概括一下mysql的分布
正文
在查看mysql的存储分布的时候,我们知道有很多存储引擎,这里仅说明是innoDB。
InnoDB 采取的方式是:将数据划分为若干个页,以页作为磁盘和内存之间交互的基本单位,InnoDB中页的大小一般为 16 KB
也就是在一般情况下,一次最少从磁盘中读取16KB的内容到内存中,一次最少把内存中的16KB内容刷新到磁盘中。
那么既然分页了,那么页里面是怎么个存储呢?
页里面自然是行,那么行的结构是怎么样的呢?
那么对于行,先不看innodb的设计,单纯如果是我们来设计,我们会怎么设计呢?
首先希望行与行之间是紧凑的,这样避免浪费空间。
嗯,这样设计很理想。
那么有一个小问题啊,我们存储是为了查询,那么就需要一个行目录吧。
我们大致是这两个想法。
那么我们再总结一下,我们需要的是空间紧凑,还有一个是必须好查询。
那我们看下innodb是怎么设计的吧。
innodb 对于行的存储设计,那么分为了四种行格式,分别是:
compact、redundant、dynamic、compressed
分别是 紧凑、冗余、动态、匿名
例如指定了compact格式:
CREATE TABLE test0
(
column1 INT
) row_format = compact
这样就可以了。
当然我们可以进行修改,但是我们知道的,这代价将会很大,一般很少。
ALTER TABLE test0 row_format = dynamic
那么来看一下compact 格式。
存储分别是:
-
变长字段长度列表
-
null 值列表
-
记录头信息
-
记录真实数据
第一部分和第二部分,其实就是为了记录字段的长度,里面有两类特殊的,一类是可变长度,第二部分是null。
在 Compact 行格式中,把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位,从而形成一个变长
字段长度列表,各变长字段数据占用的字节数按照列的顺序逆序存放,我们再次强调一遍,是逆序存放!
这个逆序是什么意思呢?
比如可变字段:
那么存储是这样写的:
可变字符,到底是一个字节表示长度呢,还是两个字节表示长度呢。
- 假设某个字符集中表示一个字符最多需要使用的字节数为 W ,也就是使用 SHOW CHARSET 语句的结果中的
Maxlen 列,比方说 utf8 字符集中的 W 就是 3 , gbk 字符集中的 W 就是 2 , ascii 字符集中的 W 就是
1 。 - 对于变长类型 VARCHAR(M) 来说,这种类型表示能存储最多 M 个字符(注意是字符不是字节),所以这个类
型能表示的字符串最多占用的字节数就是 M×W 。 - 假设它实际存储的字符串占用的字节数是 L 。
如果 M×W <= 255 ,那么使用1个字节来表示真正字符串占用的字节数。
也就是说InnoDB在读记录的变长字段长度列表时先查看表结构,如果某个变长字段允许存储的最
大字节数不大于255时,可以认为只使用1个字节来表示真正字符串占用的字节数。
如果 M×W > 255 ,则分为两种情况:
- 如果 L <= 127 ,则用1个字节来表示真正字符串占用的字节数。
- 如果 L > 127 ,则用2个字节来表示真正字符串占用的字节数。
为什么这么做呢?因为觉得小于127的话,那么认为比较小,所以一个字节表示比较好。
InnoDB在读记录的变长字段长度列表时先查看表结构,如果某个变长字段允许存储的最大字节
数大于255时,该怎么区分它正在读的某个字节是一个单独的字段长度还是半个字段长度呢?
设计InnoDB的大叔使用该字节的第一个二进制位作为标志位:如果该字节的第一个位为0,那
该字节就是一个单独的字段长度(使用一个字节表示不大于127的二进制的第一个位都为0),
如果该字节的第一个位为1,那该字节就是半个字段长度。
这里考虑的就是第二种情况了,比如M×W > 255,因为如果小于255就是一个字节。
那么就是这个时候我们知道L<=127 就是一个字节,所以当检查到最大位是1的时候,那么肯定不是一个字节,那就是两个字节。
那么128怎么表示呢?0x8081 这样。
总结一下就是说:如果该可变字段允许存储的最大字节数( M×W )超过255字节并且真实存储的字节数( L )
超过127字节,则使用2个字节,否则使用1个字节。
另外需要注意的一点是,变长字段长度列表中只存储值为 非NULL 的列内容占用的长度,值为 NULL 的列的长度
是不储存的。
并不是所有记录都有这个 变长字段长度列表 部分,比方说表中所有的列都不是变长的数据类型的话,
这一部分就不需要有。
下面来介绍null:
null 这个介绍的是什么呢?
null 这个介绍的是标记着哪些字段为null。
但是不可为null的字段里面又没有去表达。
比如说:
如果c2 不为空的话,那么是不需要表达的。
二进制位的值为 1 时,代表该列的值为 NULL 。
二进制位的值为 0 时,代表该列的值不为 NULL 。
MySQL 规定 NULL值列表 必须用整数个字节的位表示,如果使用的二进制位个数不是整数个字节,则在字节
的高位补 0 。
表 record_format_demo 只有3个值允许为 NULL 的列,对应3个二进制位,不足一个字节,所以在字节的高
位补 0 ,效果就是这样:
还是那个事,最小的存储单位为字节。
接下来就是记录头信息:
除了 变长字段长度列表 、 NULL值列表 之外,还有一个用于描述记录的 记录头信息 ,它是由固定的 5 个字节组
成。 5 个字节也就是 40 个二进制位,不同的位代表不同的意思,如图:
这些二进制位代表的详细信息如下表:
那么真实数据如何存储的:
真实数据除了存储每列的数据外,还存储着一些额外的信息:
rowId,transationId,roll_pointer
这里rowid 一般是没有的,根据我们实际的业务来说呢,一般会自己做一些主键。
存储数据如下:
然后有些人这时候想varchar 这个时候是长度是可变的,那么char的时候长度是不可变的。
其实这两者区别不在这。
比如如果是utf8那么char是不是也是长度可变的呢,原因就是utf8,一个字符的长度是1-3对吧,那么肯定也是长度可变的。
那么char 和 varchar的区别在什么地方呢? char(10)的话,那么意思是至少会占用10个字节,不管存储的有多少,而varchar的话,是非常动态的,比如一个就是a,就是1个字节。
那么char(10)的有点是什么呢?它用空间换来了什么?
这是怕将来更新该列的值的字节长度大于原有值的字节长度而小于10个字节时,可以在该记录处直接更新,而不是在存储空间中重新分配一个新的记录空间,导致原有的记录空间成为所谓的碎片。
这里后面更新的时候再看是怎么回事吧,就知道用空间换来了什么。
后面我们还有几种格式介绍,但是我们毕竟是为了工程话,而不是为了学而学,那么我们就不学其他的,还有一个需要学,那就是dynamic。
为什么这个需要学呢?
因为默认是这个啊,一般情况下,我们都不会去改这个,那么就需要学一下dynamic。
为什么我们学了一下compact呢,因为dynamic和compact区别只是在行溢出的处理上。
首先在mysql 一行最大位65535长度,这不仅仅是数据的长度,还包括我们前面的这个可变列的长度、null 值列表。但不包括隐藏列和记录头信息。
也就是说这65535是分为3个部分的:
- 真实数据
- 真实数据占用字节的长度
- NULL 值标识,如果该列有 NOT NULL 属性则可以没有这部分存储空间
知道这个有啥用,知道这个可以解释一个东西。
比如说:
当我们这样就行保存的时候,那么是保存不了的,为什么呢?
自定帮我们设置了text类型。为啥呢?
理由就是,比如说这里是utf8md4,那么字节是1-4个。
这里就一个列,真实数据占用字节的长度那么就是一个字节,这里可为空,那么还得占用一个字节对吧,那么就是65535-2=65533
那么因为是utf8,那么最大是16383。
如果16384都不行。
这里我们发现一个问题,那就是一行数据可以达到65535,而且是大于的,因为还得包括固定的5个字节的行记录,还有额外的列。
有没有发现一个问题,65535好像大于16k,也就是大于一页。
大于一页会干什么呢?
对于占用存储空间非常大的列,在 记录的真实数据 处只会存储该列的一部
分数据,把剩余的数据分散存储在几个其他的页中,然后 记录的真实数据 处用20个字节存储指向这些页的地址
(当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数),从而可以找到剩余数据所在的页
对于 Compact 和 Reduntant 行格式来说,如果某一列中的数据非常多的话,在本记录的真实
数据处只会存储该列的前 768 个字节的数据和一个指向其他页的地址,然后把剩下的数据存放到其他页中,这个
过程也叫做 行溢出 ,存储超出 768 字节的那些页面也被称为 溢出页 。
最后需要注意的是,不只是 VARCHAR(M) 类型的列,其他的 TEXT、BLOB 类型的列在存储数据非常多的时候
也会发生 行溢出 。
那么是否是一行超过16k的时候会溢出呢?这个16k是否是临界点呢?
那肯定不是,因为mysql中要求一页至少有2条数据,那么我们知道起码是小于16k的就行了。
你不用关注这个临界点是什么,只要知道如果我们想一个行中存储了很大的数据时,可能发生 行溢出 的现象
那么我们不是默认是dynamic吗?
那肯定要学习的是dynamic了,这个行溢出有啥子区别呢?
区别就是compact会用768的字节加上一个指向其他页的地址,Dynamic 人家就直接是放地址不放前面768个字节。
结
大概就是一些行存储相关的信息
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· spring官宣接入deepseek,真的太香了~
2020-02-18 最长公共前缀
2020-02-18 字符串转整数