这一章解决的问题是：MySQL 是如何存储数据的？存在哪里，以怎么样的格式存储。

存储引擎负责读取、写入数据。这里介绍 InnoDB 存储引擎的记录存储结构。

InnoDB 存储引擎在读取数据时并不是一条一条的从磁盘中读取，而是一次性读取一页，一页是 16 KB。也就是在一般情况下，一次最少从磁盘读取 16 KB 的内容到内存中，一次最少把 16KB 的内容刷新到磁盘中。

指定行格式

  1. CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称;
  3. ALTER TABLE 表名 ROW_FORMAT=行格式名称;
  1. CREATE TABLE record_format_demo (
  2.     c1 varchar(10),
  3.     c2 varchar(20) NOT NULL,
  4.     c3 char(10),
  5.     c4 varchar(10)
  6. )CHARSET=ascii ROW_FORMAT=COMPACT;
  8. INSERT INTO record_format_demo(c1, c2, c3, c4) values ('aaaa', 'bbb', 'cc',' d'), 
  9. ('eeee', 'fff', null, null);

COMPACT 行格式

  • 完整记录

    • 记录的额外信息
      • 变长字段长度列表
        • MySQL 支持一些变长的数据结构，例如 VARCHAR(M)、VARBINARY(M)、各种 TEXT 类型、各种 BLOB 类型。我们可以把拥有这些数据结构的列称为变长字段，变长字段存储多少字段是不固定的，所以我们需要存储字段真实占用的字节数。所有变长字段占用的字节数都存放在记录的开头位置，形成一个变长字段长度列表。各变长字段的真实数据占用的字节数按照列的顺序逆序存放。
      • NULL 值列表
      • 记录头信息
    • 记录的真实信息
      • 列1的值
      • 列2的值
      • …
      • 列n的值

        例子

        现在表中数据如图所示，因为 c1、c2、c4 都是变长的数据结构，所以这三个字段占用的字节数需要保存起来。
        aaaa 4个字节，bbb 3个字节，d 1个字节，所以在记录开头处，存储 010304。
        image.png

  • 因为字符集是 ascii，每个字符用一个字节编码，所以 aaaa 需要用 4 个字节，用二进制表示 4 就是：0000 0100，为了方便书写，将其表示为 04，以此类推 bbb 和 d，然后逆序得到 010304。

  • 如果一个字段的字节数大于 255（一个字节能表示的数最大是 255），用一个字节就无法表示了，就需要用到两个字节来表示。
    • InnoDB 规定，如果这个字段最多占用的字节数小于等于 255，那么只用 1 个字节表示长度。如果最多占用的字节数大于 255，并且实际占用字节数小于等于 127，那么也是用一个字节表示长度。如果最多占用的字符数大于 255，并且实际占用字节数大于 127，那么就用两个字节表示。127 这个数字是 7 位二进制数的最大值，因为一个字节有 8 位，第一位需要用来判断这个字节用来表示实际长度，还是这个字节加上下一个字节表示长度，所以能使用的就只有 7 位。
  • 如果某个字段占用的字节数特别多，InnoDB 可能把该字节的值的一部分数据存放到所谓的溢出页中。那么该字段只存储在本页面的长度，所以使用 2 字节就够了。尽管也是使用 2 个字节，但对于溢出字段而言，采用的方案不是单纯的将第一个二进制位置设置为 1。
  • 另外需要注意的是，如果一个字段值为 NULL，那么将不存记录这个字段的长度。

  • 并不是所有记录都有这个变长字段长度列表部分，如果表中所有的列都不是变长的数据类型，或者所有的列的值都是 NULL 的话，就不需要变长字段长度列表。

    NULL 值列表

    某些字段的值可能为 NULL，如果把 NULL 值都放到记录的真实数据中，会很占地方，这里在 NULL 值列表记录列的值是否为 NULL。

  • 先统计哪些列允许存储 NULL

  • 用二进制位记录某列是否为 NULL，1=NULL，0=不为NULL。同样是按照列的顺序逆序排列。NULL 值列表必须用整数个字节的位表示，比如一个字节的 8 位，2 个字节的 16 位，如果允许为 NULL 的列的数量不足，则在高位补 0，如果列的数量多的话，就用更多的字节来表示。

    例子

    第一行：00000 000
    第二行：00000 110

    记录头信息

    // 跳过

记录的真实数据

对于刚才建立的表而言，除了我们定义的几个列之外，MySQL 默认还添加了一些列。

列名 是否必须 占用空间 描述
row_id 否 6字节 行ID，唯一标识一条记录
trx_id 是 6字节 事务ID
roll_pointer 是 7字节 回滚指针
  • InnoDB 主键生成策略：使用用户自定义的主键 -> 选取一个不允许存储 NULL 值的 UNIQUE 键 -> 默认添加一个 row_id 键为主键
  • 真实数据中，变长字段只占用真正需要的字节，而定长字段即使不需要用到那么多个字节，也会占用固定数量的字节，没有用到的字节用空格填充。
  • 值为 NULL 的列是不存储的

  • 变长字段需要存储实际占用的字节数，但是 CHAR(M) 在某些情况下也需要保存字段长度，不过保存的不是实际数据的字节长度，而是实际占用的字节长度。

    • 因为某些字符集编码一个字符需要的字节数未知，所以 M 个字符占用的字节数是不固定的，所以这种情况下需要保存实际占用的字节数。例如：字段格式 CHAR(10)，存储的值为 cc，字符集为 utf8，实际数据长度为两个字节，但是实际占用的字节长度是 10 个，所以在变长字段长度列表中存储的是 0A。
    • 因为 CHAR(M) + 变长编码字符集最终需要的字节数未知，所以规定至少占用 M 个字节，这是为了当更新数据时，如果该字段变长，且新的长度不超过 M 个字节时，可以直接更新，而不需要重新分配空间，导致产生碎片。

      溢出列

  • 如果一条记录的某个列存储的数据占用的字节数非常多，该列就可能成为溢出列。

  • 对于溢出列，会在该列的位置存储一部分值，然后将其他值存储到其他页中。同时在该列的位置用 20 字节存储其他页的地址，以及在其他页占用的字节数。

  • 其他页可能有多个，它们使用链表连接起来。

    其他行格式

  • REDUNDANT

    • 5.0 之前的格式，已经非常古老，忽略
  • DYNAMIC
    • 在溢出列不存储数据，全部存储到溢出页中
  • COMPRESSED
    • 同上，并且使用压缩算法对页面进行压缩，以节省空空间