Mysql一行记录是如何存储的

• MySQL 的 NULL 值会占用空间吗？
• MySQL 怎么知道 varchar(n) 实际占用数据的大小？
• varchar(n) 中 n 最大取值为多少？
• 行溢出后，MySQL 是怎么处理的？

这些问题关系到Mysql对数据的具体存储形式

1. Mysql的数据存储在哪个文件

通过sql查询

show VARIABLES like 'datadir';

db.opt:存储当前数据库的字符集和字符检验规则

.ibd:表数据的存储位置

2. 表空间文件的结构是怎么样的

表空间由段（segment）、区（extent）、页（page）、行（row）组成

行:行就是一行一行的数据,每行根据不同的行格式,有不同的存储结构

页:记录是按行进行存储的,但是磁盘进行一次I/O的数据传送通常是通过页进行传输的,默认每个页的大小是16KB,最多能够保证连续16KB的连续空间,

相比与传统操作系统的记录是4KB

区:我们知道InnoDB的实际存储结构是B+树,而存储这些B+树的时候,为了提高范围查询的效率,通常会将页与页之间用双向链表进行连接

然而,这些页虽然在逻辑上是连续的,但是他们在物理上可能是不连续的,可能位于不同的磁道,然后如果要读取另外一页,就要移动磁头,然后执行非常耗时的操作

因此就采用区的概念,在区上,页与页之间除了逻辑上是连续的,在物理存储上也是连续的

因此在读取页的下一页的时候就可以执行顺序IO了,这样的话就可以提高性能

段:表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。

• 索引段：存放 B + 树的非叶子节点的区的集合；
• 数据段：存放 B + 树的叶子节点的区的集合；
• 回滚段：存放的是回滚数据的区的集合

3. InnoDB的行格式有哪些

行格式（row_format），就是一条记录的存储结构。

InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和 Compressed 行格式。

• Redundant 是很古老的行格式了， MySQL 5.0 版本之前用的行格式，现在基本没人用了。
• 由于 Redundant 不是一种紧凑的行格式，所以 MySQL 5.0 之后引入了 Compact 行记录存储方式，Compact 是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从 MySQL 5.1 版本之后，行格式默认设置成 Compact。
• Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，因为都是基于 Compact 改进一点东西。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式。

Redundant 行格式我这里就不讲了，因为现在基本没人用了，这次重点介绍 Compact 行格式，因为 Dynamic 和 Compressed 这两个行格式跟 Compact 非常像。

4. 讲讲Compact的行格式是什么样的

记录的额外信息

变长字段列表:varchar(n)和char(n)的区别在于char是定长的,而varchar不是定长的,变长字段实际上存储的数据长度(大小)不固定的,因此在存储数据的时候要把这个变长字段的占用给存出来,这样在读取数据的时候,才知道你要读多长的数据

有点像HTTP中处理TCP粘包和半包问题的思路,提供一个变长的长度,每次读取指定数量的字节数

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL,
  `name` VARCHAR(20) DEFAULT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

对于第一条数据,name列的值为a真实数据的占用字节数为1个Byte,是十六进制的0x1

phone列的值为123,真实数据的占用字节数3个Byte

这些变长字段的真实数据占用的字节数会按照列的顺序逆序存放（等下会说为什么要这么设计），所以「变长字段长度列表」里的内容是「 03 01」，而不是 「01 03」。

第三条记录中 phone 列的值是 NULL，NULL 是不会存放在行格式中记录的真实数据部分里的，所以「变长字段长度列表」里不需要保存值为 NULL 的变长字段的长度。

为什么「变长字段长度列表」的信息要按照逆序存放？

这个设计是有想法的，主要是因为「记录头信息」中指向下一个记录的指针，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。

「变长字段长度列表」中的信息之所以要逆序存放，是因为这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。

同样的道理， NULL 值列表的信息也需要逆序存放。

除了这样能够便于数据库开发者对数据的读取之外,还有一个作用:

如果是按照向右读能够读取到数据,向左读读取到附加信息的话,那么逆序存放的目的就是为了缩小附加信息与其对应的字段的实际数据在内存编排上的距离

假设在Cache中,因为空间不足而将这些数据分开存放,那么他们在内存编排上的距离越小,那么就越有可能都在cache中,可能提高命中率

NULL值列表

表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中。

如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。

• 二进制位的值为1时，代表该列的值为NULL。
• 二进制位的值为0时，代表该列的值不为NULL。

另外，NULL 值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。

还是以 t_user 表的这三条记录作为例子：

记录头信息

• delete_mask ：标识此条数据是否被删除。从这里可以知道，我们执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete_mask 标记为 1。
• next_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。在前面我也提到了，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
• record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录

隐藏字段

row_id、trx_id、roll_pointer

• row_id

如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的，占用 6 个字节。

• trx_id

事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节。

• roll_pointer

这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。

5. varchar(n)的最大取值是多少?

我们要清楚一点，MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。

注意,65535的字节长度是包含了所有的数据的长度

单字段的情况

一行数据的最大字节数 65535，其实是包含「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。所以， 我们在算 varchar(n) 中 n 最大值时，需要减去 storage overhead 占用的字节数。

这是因为我们存储字段类型为 varchar(n) 的数据时，其实分成了三个部分来存储：

• 真实数据
• 真实数据占用的字节数
• NULL 标识，如果不允许为NULL，这部分不需要

前面我创建表的时候，字段是允许为 NULL 的，所以会用 1 字节来表示「NULL 值列表」。

「变长字段长度列表」所占用的字节数 = 所有「变长字段长度」占用的字节数之和。

• 条件一：如果变长字段允许存储的最大字节数小于等于 255 字节，就会用 1 字节表示「变长字段长度」；
• 条件二：如果变长字段允许存储的最大字节数大于 255 字节，就会用 2 字节表示「变长字段长度」；

我们这里字段类型是 varchar(65535) ，字符集是 ascii，所以代表着变长字段允许存储的最大字节数是 65535，符合条件二，所以会用 2 字节来表示「变长字段长度」。

会用 2 字节来表示「变长字段长度」。

6. 行溢出后，MySQL 是怎么处理的？

MySQL 中磁盘和内存交互的基本单位是页，一个页的大小一般是 16KB，也就是 16384字节，而一个 varchar(n) 类型的列最多可以存储 65532字节，一些大对象如 TEXT、BLOB 可能存储更多的数据，这时一个页可能就存不了一条记录。这个时候就会发生行溢出，多的数据就会存到另外的「溢出页」中。

如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。在一般情况下，InnoDB 的数据都是存放在 「数据页」中。但是当发生行溢出时，溢出的数据会存放到「溢出页」中。

当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。大致如下图所示。

Compressed 和 Dynamic 这两个行格式和 Compact 非常类似，主要的区别在于处理行溢出数据时有些区别。

这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。

回答问题

MySQL 的 NULL 值会占用空间吗？

首先,NULL在Mysql规定的Compact中,在附加信息部分中有一个叫做NULL值列表的字段,这个字段存储的是这条记录中,那几列为NULL,因此,可以说一个NULL值会占据一个bit的存储空间,用来表示这个列的具体值

而存储这个NULL格式是用位标记来存储的,例如说有8个列,那么就是一个字节8个bit的数据,当bit为1,那么就代表不为NULL,否则的话就是NULL,当不足一个字节的时候,高位补0

MySQL 怎么知道 varchar(n) 实际占用数据的大小？

对于这种变长的数据类型,Mysql在行格式的附加信息字段有一个叫做变长字段长度列表,这个列表里面记录了每个变长字段的长度。

varchar(n) 中 n 最大取值为多少？

而一个记录的规定是:除了text、blob类的这类对象的实际+「变长字段长度列表」和 「NULL 值列表」的实际长度,不能超过65535的字节长度

假设,目前一个varchar的n是给了65533,那么变长字段长度列表就需要占两个字节,不允许为NULL

那么这时候就可以到65533,如果不为NULL,那就到65532

如果是用ascii编码,一个字符是用8个bit来进行表示的,然后用UTF-8

那么就还要除于3

行溢出后，MySQL 是怎么处理的？

这道题要分类讨论,如果是Compact的行格式定义,它会尽可能地利用本页中存储空间,将部分数据存储到本条记录中,同时留一个20字节的指针,指向下一页,然后这下一页存储剩余的数据

而对于Dynamic或者Compressed格式的话,那么它在本页不会存储这条数据,而是直接留下一个指针,然后新开一个页存储到别的地方