【7.MySQL行格式存储】

1.MySQL数据存放文件

• 我们每创建一个 database（数据库） 都会在 /var/lib/mysql/ 目录里面创建一个以 database 为名的目录,创建一个student表

[root@xiaodainiao ~]#ls /var/lib/mysql/my_test
db.opt  
student.frm  
student.ibd

• db.opt：用来存储当前数据库的默认字符集和字符校验规则。
• student.frm ：student的表结构会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。
• student.ibd：student 的表数据会保存在这个文件。(也叫表空间)
• 表空间由段（segment）、区（extent）、页（page）、行（row）组成
  • InnoDB 的数据是按「页」为单位来读写的,默认每个页的大小为 16KB(最多能保证 16KB 的连续存储空间)
  • 在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。
  • 表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。

2.行格式

• MySQL默认是默认行格式为COMPACT格式
  
• 记录的额外信息包含 3 个部分：变长字段长度列表、NULL 值列表、记录头信息。
• 记录的真是数据包含：row_id、trx_id、roll_ptr、列1值、列2值、列3值。

变长字段

• varchar(n) 和 char(n) 的区别是什么，相信大家都非常清楚，char 是定长的，varchar 是变长的，变长字段实际存储的数据的长度（大小）不固定的。
  
• name 列的值为 a，真实数据占用的字节数是 1 字节，十六进制 0x01；
• phone 列的值为 123，真实数据占用的字节数是 3 字节，十六进制 0x03；
  
  注意：变长字段字节数列表不是必须的。当数据表没有变长字段的时候，比如全部都是 int 类型的字段，这时候表里的行格式就不会有「变长字段长度列表」了

NULL值列表

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

注意：

• NULL 值列表也不是必须的。当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。(NULL 值列表至少占用 1 字节空间）
• 「NULL 值列表」的空间不是固定 1 字节的。当一条记录有 9 个字段值都是 NULL，那么就会创建 2 字节空间的「NULL 值列表」，以此类推。

记录头信息（省略）

记录真实数据

• row_id

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

• trx_id

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

• roll_pointer

  • 这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。（MVCC机制）

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

• 一行记录最大只能存储 65535 字节的数据。

• varchar(n) 字段类型的 n 代表的是最多存储的字符数量，并不是字节大小

• 65535字节的数据 = 变长字段长度列表 + NULL 值列表 + 真实数据

  • 创建表的时候，字段是允许为 NULL 的，所以会用 1 字节来表示「NULL 值列表」。
  • 「变长字段长度列表」所占用的字节数 = 所有「变长字段长度」占用的字节数之和。
    • 如果变长字段允许存储的最大字节数小于等于 255 字节，就会用 1 字节表示「变长字段长度」否则用2个字节代替

• varchar(n) 中 n 最大值 = 65535 - 2 - 1 = 65532。

4.行溢出

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

5.字节面试

• MySQL 的 NULL 值会占用空间吗？
  • MySQL 的 Compact 行格式中会用「NULL值列表」来标记值为 NULL 的列，NULL 值并不会存储在行格式中的真实数据部分。
  • NULL值列表会占用 1 字节空间，当表中所有字段都定义成 NOT NULL，行格式中就不会有 NULL值列表，这样可节省 1 字节的空间。
• MySQL 怎么知道 varchar(n) 实际占用数据的大小？
  • MySQL 的 Compact 行格式中会用「变长字段长度列表」存储变长字段实际占用的数据大小。
• varchar(n) 中 n 最大取值为多少？
  • 一行记录最大能存储 65535 字节的数据，但是这个是包含「变长字段字节数列表所占用的字节数」和「NULL值列表所占用的字节数」。所以， 我们在算 varchar(n) 中 n 最大值时，需要减去这两个列表所占用的字节数。
• 行溢出后，MySQL 是怎么处理的？
  • 如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。
  • Compact 行格式针对行溢出的处理是这样的：当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。

文章节选自https://www.xiaolincoding.com/