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

MySQL数据存储相关相关面试题

• MySQL 的 NULL 值会占用空间吗？

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

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

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

上述面试题不知道大家都是否了解呢？如果对某个点有所疑问，那么就一起在本文中寻找答案吧！ 

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

表空间由段、区、页、行组成，InnoDB存储引擎的逻辑存储结构大致如下图：

行（row）

数据库表中的记录都是按行存放的，每行记录根据不同的行格式，有不同的存储结构。

页（page）

记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。

        因此，InnoDB 的数据是按「页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。

默认每个页的大小为 16KB，也就是最多能保证 16KB 的连续存储空间。

        页是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

区（extent）

我们知道 InnoDB 存储引擎是用 B+ 树来组织数据的。

        B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机I/O，随机 I/O 是非常慢的。

        解决这个问题也很简单，就是让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高。那具体怎么解决呢？

        在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。

段（segment）

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

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

• 数据段：存放 B + 树的叶子节点的区的集合；

• 回滚段：存放的是回滚数据的区的集合， MVCC 就是利用了回滚段实现了多版本查询数据。

InnoDB 行格式

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

• 由于 Redundant 不是一种紧凑的行格式，所以 MySQL 5.0 之后引入了 Compact 行记录存储方式，Compact 是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从 MySQL 5.1 版本之后，行格式默认设置成 Compact。

• Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，因为都是基于 Compact 改进一点东西。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式

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

COMPACT 行格式

一条完整的记录分为「记录的额外信息」和「记录的真实数据」两个部分。

记录的额外信息

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

  注意：数据库表的行格式不一定有「变长字段字节数列表」

  「变长字段长度列表」只出现在数据表有变长字段(varchar)的时候。当数据表没有变长字段的时候，表里的行格式就不会有「变长字段长度列表」了

• NULL 值列表：表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中。每个列对应一个二进制位（bit），二进制位按照列的逆序排列。

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

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

  最后将这个二进制数转换为十进制存入到NULL 值列表中

  

  注意：数据库表的行格式不一定有「NULL 值列表」

          当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。

  所以在设计数据库表的时候，通常都是建议将字段设置为 NOT NULL，这样可以至少节省 1 字节的空间（NULL 值列表至少占用 1 字节空间）。

• 记录头信息：记录头信息中包含的内容很多，我就不一一列举了，这里说几个比较重要的：

  • delete_mask ：标识此条数据是否被删除。从这里可以知道，我们执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete_mask 标记为 1。

  • next_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。在前面我也提到了，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。

  • record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录

记录的真实数据

记录真实数据部分除了我们定义的字段，还有三个隐藏字段，分别为：row_id、trx_id、roll_pointer

1. row_id：这是行标识符，用于唯一标识表中的每条记录。如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段

2. trx_id：这事务id，表示这个数据是由哪个事务生成的

3. roll_pointer：回滚指针，指向undo日志中的一个特定点，用于在需要撤销操作时恢复数据到之前的状态。

然后剩余位置就是存储正式的数据内容了

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

        一行记录最大能存储 65535 字节的数据，但是这个是包含「变长字段字节数列表所占用的字节数」和「NULL值列表所占用的字节数」。所以， 我们在算 varchar(n) 中 n 最大值时，需要减去这两个列表所占用的字节数。

• 如果一张表只有一个 varchar(n) 字段，且允许为 NULL，字符集为 ascii。varchar(n) 中 n 最大取值为 65532。

计算公式：

        65535 - 变长字段字节数列表所占用的字节数 - NULL值列表所占用的字节数 = 65535 - 2 - 1 = 65532

• 如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。

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

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