MySQL 的 NULL 值是怎么存放的？-51CTO.COM

InnoDB页

InnoDB是一个将数据存储到磁盘上的存储引擎，所以就算我们关闭、重启服务器，数据还是存在的。而在真正处理数据的时候是在内存中进行的，所以需要把磁盘中的内容加载到内存中。

我们知道读写磁盘是很慢的。当我们想从表里获取数据的时候，InnoDB会一条一条的从磁盘中读出来吗？不会的！因为那样太慢了。它采取的方式是：将数据划分为若干页，以页做为磁盘和内存交互的基本单位。InnoDB中页的大小一般为16KB。

在服务器运行的过程中不可以修改页的大小，只能在初始化数据目录的时候指定。

InnoDB 行格式

行格式有哪些

行格式（row_format）：一条数据记录在磁盘上的存储结构。

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

我们可以在创建表或者修改表的语句中指定所使用的行格式

create table 'table info ..' row_format = '行格式名称'
alter table 'table name' row_format = '行格式名称'

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

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

Compact 格式

话不多说，直接看图

记录额外的信息

这部分信息是服务器为了更好的管理记录而不得不额外添加的一些信息，这些额外信息分为三个部分，分别是：变长字段长度列表、NULL值列表和记录头信息。

变长字段长度列表

在mysql中有一些变长的数据类型，比如varchar( )、varbinary( )、text类型、blob类型，我们把使用这个变长类型的列成为变长字段。

所以，在存储数据的时候，也要把数据占用的大小存起来，存到「变长字段长度列表」里面，读取数据的时候才能根据这个「变长字段长度列表」去读取对应长度的数据。

这些变长字段的真实数据占用的字节数会按照列的顺序逆序存放（后面会说为什么要这么设计）。

为了展示具体是怎么保存「变长字段的真实数据占用的字节数」，我们先创建这样一张表，字符集是 ascii（所以每一个字符占用的 1 字节），行格式是 Compact，student 表中 name 和 dream_school 字段是变长字段：

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

我们插入三条记录

我们看看这三条记录的行格式中的变长字段长度列表是怎样存储的。

先来看第一条记录：

name 列的值为 1，真实数据占用的字节数是 1 字节，十六进制 0x01；
dream_school 列的值为 qinghua，真实数据占用的字节数是 7 字节，十六进制 0x07；
age 列和 id 列不是变长字段，这里不用管。

再来看第二条

name 列的值为 1，真实数据占用的字节数是 2 字节，十六进制 0x02；
dream_school 列的值为 beida，真实数据占用的字节数是 5 字节，十六进制 0x05；

第三条记录

第三条记录 dream_school 列的值是 NULL，NULL 是不会存放在行格式中记录的真实数据部分里的。

null值列表一条记录中的某些列可能存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中存储会很占地方，所以 OMPACT 行格式把一条记录中值为 NULL 的列统一管理起来，存储到 NULL 值列表中.

处理过程：

首先统计表中允许存储 NULL 的列有哪些，主键列以及使用 NOT NULL 修饰的列都是不可以存储 NULL 值的，所以在统计的时候不会把这些列算进去。
如果表中没有允许存储 NULL 的列，则 NULL 值列表也就不存在了，否则将每个允许存储 NULL 的列对应一个进制位，二进制位按照列的顺序逆序排列。二进制位表示的意义如下

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

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

先来看第一条记录

第一条记录所有列都有值，不存在 NULL 值，用二进制来表示是这样的：

第二条记录

接下来看第二条记录，第二条记录 age 列是 NULL 值，用二进制来表示是这样的：

第三条记录

第三条记录 dream_school 列和 age 列是 NULL 值，用二进制来表示是这样的：

记录头信息

记录头信息由固定5个字节组成，用于描述记录的一些属性，这里的属性比较多，我们就列举几个相对来说重要点的。

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

记录真实数据

记录真实数据除了记录我们自定义的列的数据外，Mysql还会为每个记录默认添加一些列（隐藏列）

row_id：当我们创建表的时候没有指定主键，也没有唯一约束的列，innodb 就会自动的为这些记录添加row_id隐藏字段，占用6个字节。不是必须会有的。
trx_id：事务ID，这个列是必须的，占用6个字节。表示数据是有哪个事务生产的。
roll_pointer：回滚指针，表示当前记录上一个版本的指针，这个列也是必需的，占用 7 个字节。

MVCC机制就是依赖 trx_id 和 roll_pointer 来实现的。

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

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

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

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

总结

MySQL 的 NULL 值是怎么存放的？

MySQL 的 Compact 行格式中会用「NULL值列表」来标记值为 NULL 的列，NULL 值并不会存储在行格式中的真实数据部分。

NULL值列表会占用 1 字节空间，当表中所有字段都定义成 NOT NULL，行格式中就不会有 NULL值列表，这样可节省 1 字节的空间。

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

如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。

Compact 行格式针对行溢出的处理是这样的：当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。