MySQL数据结构-行结构

数据的具体存储是交由具体的存储引擎实现的，所以同样的数据，在不同的存储引擎中的存储方式也是不同的，现在只讨论InnoDB引擎的数据结构。

CPU发出读取数据的指令后，Mysql进程需要把存储在硬盘中的数据读取到内存中，CPU真正处理数据的地方是在内存。如果每次读取或写入都要去操作内存和磁盘的话，那样太慢了。

Mysql的策略是：将真实数据划分为若干个页，内存与磁盘交互的最小单位是页，页的大小一般为16KB，也就是说，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

1.行结构

平时操作Mysql是以一行一行的数据为单位，表中的行也有他自己的行格式，每张表中所有行共享一种行格式，目前共有四种行格式供我们选择，他们大同小异，我们先介绍Compact：

  • Dynamic （5.7.0默认）
  • Compact
  • Compressed
  • Redundant （5.5.0废弃）
/**创建时指定行格式**/
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
/**修改行格式**/
ALTER TABLE 表名 ROW_FORMAT=行格式名称
/**查看表的行格式**/
show table status like '表名'

40%

Compact行格式包括：

  • 一条记录的额外信息

    • 变长字段长度
    • NULL值列表
    • 头信息

  • 一条记录的真实数据

    • 列的真实值

    • 隐藏列

变长字段长度列表(不必须)

如果行中有变长类型的字段（比如VARCHAR(M、各种TEXT类型，各种BLOB类型），并且这些字段的数据值不为null；或者这个表的字符集是变长字符集（比如utf8每个字符占用1-3个字节，而ascii固定每个字符固定占用1个字节），存储这些变长字段的时候，要把他们的真实数据的字节长度一并存入变长字段长度列表。变长字段长度列表最多占用2个字节。

每个变长字段的长度都占据一个或两个字节，他们逆序排列，组成了变长字段长度列表。

值得注意的有两点，

  1. 当变长数据类型的列数据为null时，不会存入变长字段长度列表
  2. 变长字段长度列表不是必须存在的，如果所有字段都是固定长度的，并且字符集也是定长字符集，则不存在变长字段长度列表

这里说一下定长和变长字段的区别，比如char(10)和varchar(10)都存"zz"，表编码为ascii，varchar列占用2个字节；而char列占用10个字节，没有使用的8个字节全部用0补齐。

括号内的10代表最多占用的字符数，尝试插入11个字符会报错。

NULL值列表(不必须)

如果字段中有允许为NULL的字段，则将这些字段是否为null的信息存储在NULL值列表中。NULL值列表占用1个字节。

值得注意的是，NULL值列表页不是必须的，如果表中没有允许存储 NULL 的列，则不存在NULL值列表。

记录头信息(必须)

记录头信息固定占用5个字节也就是40位，不同位代表不同信息，主要有：

  • delete_mask 标记该记录是否被删除

  • record_type 表示当前记录的类型

    0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录

  • next_record 表示下一条记录的相对位置

隐藏列(必须)

隐藏列中的信息因为与事务和主键有关，所以很重要，总共占用19个字节，有三列：

  • row_id (不必须) 替补主键id
  • trx_id 事务id
  • roll_pointer 回滚指针

这里需要提一下InnoDB表对主键的生成策略：

优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。

真实数据

Mysql存储数据的规定限制：

  • 每行最多65535个字节
  • 每页最少存储两行数据

//TODO 待整理

varchar(M)的最大M

mysql表中的一条记录占用的最大存储空间是有限的，除了BLOB和text类型的字段意外，其他所有的列占用的字节长度加起来不能超过65536个字节，这65535个字节除了这条记录本身的真实数据之外，还包括一些其他数据。

比如我们存储一个VARCHAR(M)类型的字段，总共可能需要占用3部分存储空间：

  • 真实数据占用的字节长度（1-2个字节）
  • Null值标识（1个字节）
  • 真实数据

所以正常来说每行真实数据最多占用65532个字节。

假设一张表中只有一个字段varchar,这张表的行格式是ascii字符集(ascii字符集每个字符占用一个字节)，而一行真实数据最多最多占用65532个字节，所以M最大可以是65532；

如果VARCHAR(M)类型的列使用的不是ascii字符集，那M的最大取值取决于该字符集表示一个字符最多需要的字节数。在列的值允许为NULL的情况下，gbk字符集表示一个字符最多需要2个字节，那在该字符集下，M的最大取值就是32766（也就是：65532/2）；utf8字符集表示一个字符最多需要3个字节，那在该字符集下，M的最大取值就是21844，就是说最多能存储21844（65532/3）个字符。

值得注意的是：上述所言都是在表中只有一个字段的情况下说的，要记住一个行中的所有列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节！

行数据溢出

上一节我们知道，一行数据最多可以有65535个字节，而一个页只有16kb，也就是16384个字节，很有可能出现一页装不下一行数据的尴尬情况，这就是行数据溢出。

在Compact和Reduntant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页，如图所示：

从图中可以看出来，对于Compact和Reduntant行格式来说，如果某一列中的数据非常多的话，在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中。如下图：

最后需要注意的是，不只是 VARCHAR(M) 类型的列，其他的 TEXT、BLOB 类型的列在存储数据非常多的时候也会发生行溢出。

溢出临界点

每行存储多少数据的时候会发生行溢出呢？

mysql规定每个数据页至少要存储两行数据，每个页除了记录数据以外，还会有132个字节存储页的信息，每行记录额外需要的存储空间是27字节:

  • 2个字节用于存储真实数据的长度
  • 1个字节用于存储列是否是NULL值
  • 5个字节大小的头信息
  • 6个字节的row_id列
  • 6个字节的transaction_id列
  • 7个字节的roll_pointer列

所以计算公式是：

132 + 2×(27 + n) < 16×1024

算出x<8099，也就是说当一行总数据量>=8099字节时，会发生行溢出。

Dynamic行格式

默认行格式Dynamic与Compact很像，只不过在处理行溢出数据时有点儿分歧，它不会在记录的真实数据处存储字段真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址，就像这样：

参考：《MySQL 是怎样运行的：从根儿上理解 MySQL》

posted @ 2020-05-16 22:15  孔令翰  阅读(2390)  评论(0)  编辑  收藏  举报
← → ↓ ↑