04InnoDB记录存储结构

学习《MySQL是怎样运行的》，感谢作者！

问题 #

表数据存在哪，以什么格式存放，MySQL以什么方式来访问
存储引擎：对表中数据进行存储和写入
InnoDB是MySQL默认的存储引擎，这章主要讲InnoDB存储引擎的记录存储结构

InnoDB页简介 #

注意，是简介
InnoDB：将表中的数据存储到磁盘上
真正处理数据的过程：内存中。所以需要把磁盘中数据加载到内存中，如果是写入或修改请求，还需要把内存中的内容刷新到磁盘上
获取记录：不是一条条从磁盘读，InnoDB将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位。页大小-> 一般是16KB
一般情况：一次最少从磁盘读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中

mysql>  SHOW VARIABLES LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

只能在第一次初始化MySQL数据目录时指定，之后再也不能更改（通过mysqld –initialize初始化数据目录[旧版本]）

InnoDB行格式 #

以记录为单位向表中插入数据，而这些记录在磁盘上的存放形式也被称为行格式或者记录格式
目前有4中不同类型的行格式：COMPACT、REDUNDANT、DYNAMIC和COMPRESSED

compact [kəmˈpækt]契约
redundant[rɪˈdʌndənt] 冗余的
dynamic[daɪˈnæmɪk]动态的
compressed [kəmˈprest] 压缩的

指定行格式的语法 #

CREATE TABLE 表名(列的信息) ROW_FORMAT=行格式名称
ALTER TABLE 表名 ROW_FORMATE=行格式名称
如下，在数据库xiaohaizi下创建一个表

CREATE TABLE record_format_demo(
      c1 VARCHAR(10),
      c2 VARCHAR(10) NOT NULL,
      c3 CHAR(10),
      c4 VARCHAR(10)
      ) CHARSET=ascii ROW_FORMAT=COMPACT;  
#回顾：ascii每个字符1字节即可表示，且只有空格标点数字字母不可见字符
#插入两条数据
INSERT INTO record_format_demo(c1,c2,c3,c4) VALUES('aaaa','bbb','cc','d'),('eeee','fff',NULL,NULL);

查询

#查询
mysql> SELECT * FROM record_format_demo;
+------+-----+------+------+
| c1   | c2  | c3   | c4   |
+------+-----+------+------+
| aaaa | bbb | cc   | d    |
| eeee | fff | NULL | NULL |
+------+-----+------+------+
2 rows in set (0.01 sec)

COMPACT行格式 #

[kəmˈpækt]契约

额外信息 #

包括变长字段长度列表、NULL值列表、记录头信息
ly-20241212142154734

记录的真实数据 #

ly-20241212142154897

REDUNDANT行格式 #

[rɪˈdʌndənt] 冗余的
MySQL5.0之前使用的一种行格式（古老）
如图
ly-20241212142154951

下面主要和COMPACT行格式做比较

字段长度偏移列表 #

  1. 记录了所有列
  2. 偏移，即不是直接记录，而是通过加减

ly-20241212142154993

同样是逆序，如第一条记录
06 0C 13 17 1A 24 25，则
第1列(RD_ROW_ID)：6字节
第2列(DB_TRX_ID)：6字节 0C-06=6 第3列(DB_ROLL_POINTER)：7字节 13-0C=7 第4列(c1)：4字节
第5列(c2)：3字节
第6列(c3)：10字节
第7列(c4)：1字节

记录头信息 #

相比COMPACT行格式，多出了2个，少了一个

没有了record_type这个属性
多了n_field和1byte_offs_flag这两个属性：
ly-20241212142155036

#查询
mysql> SELECT * FROM record_format_demo;
+------+-----+------+------+
| c1   | c2  | c3   | c4   |
+------+-----+------+------+
| aaaa | bbb | cc   | d    |
| eeee | fff | NULL | NULL |
+------+-----+------+------+
2 rows in set (0.01 sec)

第一条记录的头信息为：00 00 10 0F 00 BC

即：00000000 00000000 00010000 00001111 00000000 1011 1100

前面2字节都是0，即预留位1，预留位2，deleted_flag，min_rec_flag，n_owned都是0
heap_no前面8位是0，再取5位：即 00000000 0001 0，即0x02
n_field：000 0000111，即0x07
1byte_offs_flag：0x01
next_record：00000000 1011 1100，即0xBC

记录头信息中的1byte_offs_flag的值是怎么选择的 #

字段长度偏移列表存储的偏移量指的是每个列的值占用的空间在记录的真 实数据处结束的位置
ly-20241212142155082

如上，0x06代表第一列(DB_ROW_ID)在真实数据的第6字节处结束；0x0C 代表第二列(DB_TRX_ID)在真实数据的第12字节处结束….

讨论：每个列对应的偏移量可以使用1字节或2字节来存储，那么什么时候1什么时候2
根据REDUNDANT行格式记录的真实数据占用的总大小来判断
如果真实数据占用的字节数不大于127时，每个列对应的偏移量占用1字节**[注意，这里只用到了1字节的7位，即max=01111111]**
如果大于127但不大于32767 （2^15-1，也就是15位的最大表示）时，使用2字节。

如果超过32767，则本页中只保留前768字节和20字节的溢出页面地址（20字节还有别的信息）。这种情况下只是用2字节存储每个列对应的偏移量即可（127<768<=32767)

在头信息中放置了一个1byte_offs_flag属性，值为1时表明使用1字节存储偏移量；值为0时表明使用2字节存储偏移量

REDUNDANT行格式中NULL值的处理 #

REDUNDANT行格式并没有NULL值列表

将列对应的偏移量值的第一个比特位，作为是否为NULL的依据，也称之为NULL比特位
不论是1字节还是2字节，都要使用第1个比特位来标记该列值是否为NULL
对于NULL列来说，该列的类型是否为变长类型决定了该列在记录的真实数据处的存储方式。

分析第2条数据
ly-20241212142155125

字段长度偏移列表->按照列的顺序排放：06 0C 13 17 1A A4 A4
c3=NULL，且c3类型->CHAR(10) ==>真实数据部分占用10字节,0x00
c3 原偏移量为36=32+4 = 00100100->0x24，由于为NULL，所以首位(比特)为1，所以（真实）偏移量为10100100，0xA4
c2偏移量为0x1A，则c2字节数为0x24-0x1A=36-26=10
如果存储NULL值的字段为变长数据类型，则不在记录的真实数据部分占用任何存储空间
所以c4的偏移量应该和c3相同，都是00100100，且由于是NULL，所以首位为1->10100100,0xA4

从结果往回推理，c4也是0xA4，和c3相同，说明c4和c3一样都是NULL

COMPACT行格式的记录占用的空间更少

CHAR(m)列的存储格式 #

COMPACT中，当定长类型CHAR(M)的字符集的每个字符占用字节不固定时，才会记录CHAR列的长度；而REDUNDANT行格式中，该列真实数据占用的存储空间大小，就是该字符集表示一个字符最多需要的字节数和M的乘积：utf8的CHAR(10)类型的列，真实数据占用存储空间大小始终为30字节；使用gbk字符集的CHAR(10)，始终20字节

溢出列 #

溢出列 #

#举例
mysql> CREATE TABLE off_page_demo(
      c VARCHAR(65532)
      ) CHARSET=ascii ROW_FORMAT=COMPACT;
#插入一条数据
mysql> INSERT INTO off_page_demo(c) VALUES(REPEAT('a',65532));
Query OK, 1 row affected (0.06 sec)

ascii字符集中1字符占用1字节，REPEAT(‘a’,65532)生成一个把字符’a’重复65532次数的字符串

1页有16kb=1024*16=16384字节，65532字节远超1页大小
COMPACT和REDUNDANT行格式中，对于存储空间占用特别多的列，真实数据处只会存储该列一部分数据，剩余数据存储在几个其他的页中，在记录的真实数据处用20字节存储指向这些页的地址（当然，这20字节还包括分散在其他页面中的数据所占用的字节数）

原书加了括号里的话，不是很理解，我的理解是：这20字节指向的页中，包括了溢出的那部分数据

ly-20241212142155172

如上，如果列数据非常大，只会存储该列前768字节的数据以及一个指向其他页的地址（存疑，应该不止一个，有时候1个是放不下所有溢出页数据的吧？）
简化：
ly-20241212142155214
例子中列c的数据需要使用溢出页来存储，我们把这个列称为溢出列，不止VARCHAR(M)，TEXT和BLOB也可能成为溢出列

产生溢出列的临界点 #

MySQL中规定一个页至少存放2条记录

16KB=16384字节

每个页除了记录，还有额外信息，这些额外信息需要132字节。
每个记录需要27字节，包括

针对下面的表
mysql> CREATE TABLE off_page_demo( c VARCHAR(65532) ) CHARSET=ascii ROW_FORMAT=COMPACT;

注意，是COMPACT行格式

对于每一行记录
存储真实数据长度（2字节）
存储列是否为NULL值（1字节）
5字节大小的头信息
6字节的row_id列
6字节的row_id列
7字节的roll_pointer列

132+2*(27+n) <16384

至于为社么不能等于，这是MySQL设计时规定的，未知。
正常记录的页和溢出页是两种不同的页，没有规定一个溢出页页面中至少存放两条记录

对于该表，得出的解是n<8099，也就是如果一个列存储的数据小于8099，就不会成为溢出页

结论 #

如果一条记录的某个列中存储的数据占用字节数非常多，导致一个页没有办法存储两条记录，该列就可能会成为溢出列

DYNAMIC行格式和COMPRESSED行格式 #

ly-20241212142155265

这两个与COMPACT行记录挺像，对于处理溢出列的数据有分歧：
他们不会在记录真实处存储真实数据的前768字节，而是把该列所有真实数据都存储到溢出页，只在真实记录处存储20字节（指向溢出页的地址）。COMPRESSED行格式不同于DYNAMIC行格式的一点：COMPRESSED行格式会采用压缩算法对页面进行压缩

MySQL5.7默认使用DYNAMIC行记录

总结 #

REDUNDANT是一个比较原始的行格式，较为紧凑；而COMPACT、DYNAMIC以及COMPRESSED行格式是较新的行格式，它们是紧凑的（占用存储空间更少）