这里是八股文学习的一天

mysql 一行记录时如何存储的#

数据存储文件#

首先我们得理解mysql的数据存储路径：/var/lib/mysql

  • db.opt

    用来存储当前数据库的默认字符集和字符校验规则

  • tablename.frm

    数据库的表结构会保存在这个文件，用来保存每个表的元数据信息

  • tablename.ibd

    数据库的表数据会保存在这个文件中

    表既可以存在共享表空间文件，也可以单独存放在表空间文件中

    • 共享表空间文件

      所有的表和索引数据都存储在同一个或一组共享的文件中。InnoDB默认使用一个名为

      文件名：ibdata1的共享表空间文件，以及可能的其他自动扩展的文件。

    • 表空间文件

      每个InnoDB表拥有自己的表空间文件，这种模式下，每个表的数据和索引存储在单独的

      文件名：表名字.ibd 文件中。

表空间文件的结构#

这幅图出自小林Coding,非常简单易懂，感谢

img

表空间（Tablespace）：

  • 表空间是数据库中最大的存储单位，它包含了一个或多个文件（或文件组），用于存储数据库对象，如表、索引等。

段（Segment）：

段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。

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

区（Extent）：

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

  • 区是表空间中的一部分，通常由多个连续的页组成。区的大小是数据库系统预先定义的，并且区的大小可能因数据库系统和配置而异。
  • 一个区可以包含多个页，例如，如果一个区的大小是1MB，页的大小是16KB，那么一个区将包含64个页（1MB / 16KB = 64）。

页（Page）：

  • 页是数据库中最小的存储单位，所有的数据都存储在页中。页通常包含行记录、索引记录或存储其他元数据。
  • 页的大小可以根据不同的需求进行配置，常见的页大小有4KB、8KB、16KB(默认)等。

行（row）:

  • 行是数据库表中的一个水平单位，包含了表中所有列的单个数据项。
  • 表中的每一行通常是唯一的，可以通过主键（Primary Key）来唯一标识。
  • 每行记录根据不同的行格式，有不同的存储结构。

InnoDB 的行格式#

行格式row_format，就是一条记录的存储格式

  • Redundant 远古格式，不学
  • Compact 一种紧凑的行格式，为了让一个数据页中存放更多的行记录
  • Dynamic （默认）和 Compressed 这个和compact差不多

下面接好 compact 到底什么样

img

  1. 变长字段长度列表

    这个区域存放的是变长字段，varchar(20)这个就是变长字段，这里存储的数据长度不固定

    在这个区域内存储数据需要把数据的长度也存储起来，由此来读取其长度

    下面看一个案例：

    id(不变) name（变） phone（变） age（不变）
    1 1 123 NULL
    2 bb 1234 NULL
    3 ccc NULL NULL
    • name 列的值为 a，真实数据占用的字节数是 1 字节，十六进制 0x01；
    • phone 列的值为 123，真实数据占用的字节数是 3 字节，十六进制 0x03；

    可以看到我们这边值的存放是 逆序 的 (为什么逆序，查gpt)

    因为NULL 是不会存放在行格式中记录的真实数据部分里的，所以当phone为空的时候，数据不会记录在变长字段列表中

    当数据表没有变长字段的时候，比如全部都是 int 类型的字段，这时候表里的行格式就不会有「变长字段长度列表」了

    变长字段列表 NULL值列表
    03(phone) 01(name)

  2. NULL 值列表

    每个NULL列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。

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

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

    最后填入列表的就是这一位表达的数字

    当然NULL值列表也可以不存在，即把字段设置为not null，因此我们应当多多设置not null，这样子才能减少资源损耗

  3. 记录头信息

    参考于http的header，有点类似于总结的，超脱于外的东西

  4. row_id

    它为表中的每一行提供了一个唯一的标识符。

  5. trx_id

    trx_id 是一个唯一的数字，用于标识数据库中的每个事务

  6. roll_ptr

    roll_ptr 是一个指针，用于指向与当前行相关的上一个版本的历史记录，通常在MVCC系统中用于事务的回滚和版本控制。

  7. 列1值|列2值|列n值

varchar(n)中n的最大取值#

一行记录除了 TEXT、BLOBs 类型的列，限制最大为 65535 字节，注意是一行的总长度，不是一列

但是这并不代表 n 的最大取值就是 65535

一行数据的最大字节数 65535，其实是包含「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。

所以 n 的最大取值就是 65535 - 变长字段长度列表大小 - NULL 值列表长度

  • 同时我们需要明白的是，哪怕字段都没有空的时候，还是要用1字节表示NULL值列表

  • 如果变长字段允许存储的最大字节数小于等于 255 字节，就会用 1 字节表示「变长字段长度」，当然一个不够就用两个

综上所述：

当在数据库表只有一个 varchar(n) 字段且字符集是 ascii 的情况下，varchar(n) 中 n 最大值 = 65535 - 2 - 1 = 65532。

行溢出怎么办#

InnoDB 的数据都是存放在 「数据页」中。但是当发生行溢出时，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中。

总结（也就是所谓的面经）#

MySQL 的 NULL 值是怎么存放的？

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

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

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

相信这些题目你肯定都已经会了🤭