Innodb行记录格式介绍

Innodb行记录格式介绍

原创未将对象引用到实例2021-02-12 11:23:13

Innodb 是将数据以索引的形式存储在硬盘上的，所以即使系统关机保存的数据也不会丢失。但我们知道硬盘查找数据的效率是非常低的，一张几千万记录的表如果每条都需要从硬盘一条一条读取、保存的话，那简直太慢了，业务系统显然无法接受。因此Innodb其实并不会每次都从硬盘一条一条地读取数据。而是对数据的交互都在内存中完成，而内存的速度是非常快的。每次数据到内存，Innodb都会从硬盘读取一页(根据局部性原理，其实会读取连续的多页)数据到内存，Innodb中硬盘和内存交互的基本单位就是页（16Kb),也就是每次至少要读取16kb数据到内存，或从内存更新16kb数据到硬盘。

Innodb 行记录格式简介:

我们向数据库表中插入的数据都是一条条的记录，而这些记录最终存放的硬盘的形式就是行记录格式，到目前为止Innodb主要有: Compact、Redundant、Dynamic和Compressed四种行记录格式，可能以后会有更多的格式，但本质都差不多。

创建表

下面我们创建一张表，后面的内容都依据这张表：

create table demo(
    id int not null primary key,
    name varchar(10),
    age int,
    address varchar(50)
) charset=ascii row_format=compact

insert into demo(1,NULL,10,'beijing')
insert into demo(2,'bbb',NULL,NULL)

这个建表语句后面的row_format 就是指定demo 表行记录格式为compact,字符集使用ascii,ascii字符集每个字符占用一个字节，后面举例比较好用一些。
此时表中存储数据如下所示。

mysql> select * from demo;
id        name       age     address
1         null        10			beijing
2         bbb        null     null

我们可以使用 show table status like 'demo'来查看表的记录格式：

 

Innodb Compact 行记录格式解析

下面我主要对Innodb的Compact 行记录格式进行分析，其他记录格式大同小异。
一条记录主要有两大部分组成：

• 记录本身的数据
• 记录所需要的为描述信息
  从图中可以看出一条行记录被分成了三部分，记录的一些额外描述信息，自动生成的id，还有真实的数据部分。

记录描述信息

Innodb不仅仅只会存储我们的记录，还需要存储一些记录的描述信息，主要包括记录头、变长字段列表、null值列表。

NULL值列表

我们应该都听过数据库使用null值的话是需要额外存储空间的，原因就在于null值列需要额外的空间记录。
Innodb 中使用一个二进制位来表示是否字段为null。

• 如果二进制值为1，则表示当前列为null
• 如果二进制值为0，则表示当前列不为null
  同时Innodb 要求null值列表必须证书个字节数标识，也就是必须要有1个、2个等整数个字节，如果null值个数没有满足整数字节，则高位补0。

此时我们第一条记录存储如图所示：

 

在Innodb中Null值列表只会存储允许null的列，因为主键ID不允许为null，所以是不需要存储的，同时Not Null的字段也是不需要要存储的。所以对于上面的demo表，只会存储name,age,address三个字段。
对于第一条记录 insert into demo(1,NULL,10,'北京') 因为只有name 为空，所以只有name二进制位为1，其他为0，同时不满足一位，所以需要高位补0，二进制列表最终就是 00000001，十六进制 0x01.
对于第二条记录因为age和address都为null，而name不为null，所以最终null值列表存储的就是 00000110，十六进制也就是0x06.
最终demo表两条记录存储便像下面的图一样：

 

注意： 如果表中没有Null类型列的话，Null值列表也是不存在的

变长字段列表

MySql支持很多长度不固定的字段类型，varchar,Text,Blob等，这些类型字段长度是不固定的，所以需要进行额外记录，否则数据库就会不知道字段到底占用的多少空间。
对于所有的边长字段都会有 字段数据和字段长度两个字段来进行标识。在Innodb中，所有的变长字段长度都被存放到行记录开始的地方。

 

说明：变长字段列表只会存储记录中非NULL的列，对于值为NULL的列是不会存储的，所以demo表中第一行记录只有address字段需要存储，同时我们使用的是ascii码，beijingz占用7个字节

注意：不是所有的记录都需要变长字段列表，变长字段列表是只存储有变长字段类型数据的，所有如果没有变长字段的话，变长字段列表是不需要的

说明：char 类型我们知道是定长数据类型，针对我们表的ascii字符集，确实是固定的，每个字符占用一个字节，但如果使用其他字符集，如UTR-8则char类型可能占用1-3个字节，此时变长字段列表也会存储char类型的字段

记录头信息

记录头是有固定5个字节的一些描述信息。

名称	大小(bit)	说明
预留位1	1	预留后面使用
预留位2	1	预留使用
delete_mask	1	删除标记
min_rec_mask	1	B+树每层非叶子节点中的最小记录
n_owned	4	当前节点拥有的记录数
heap_no	13	当前记录在记录堆中的位置信息
record_type	3	记录类型 0-普通记录 1-B+树非叶子节点记录 2-最小记录 3-最大记录
next_record	16	下一条记录相对位置

记录头中存储的信息还是比较多的，这里不一一介绍，在后面的各部分使用到的内容会在进行详细介绍。

记录额外id数据

Innodb还会为我没的每条记录生成3个额外的id，这些id列都是隐藏列，正常我没事看不到的。

列名	必须	大小	说明
DB_ROW_ID	否	6个字节	记录标识
DB_TRX_ID	是	6个字节	事物id
DB_ROLL_PTR	是	7个字节	回滚指针

Innodb主键生成策略

我们可能尝试过在创建表的时候不指定主键也是可以的，但数据库要求表示必须要求一个主键来表示记录的。此时如果我们在创建表的时候没有指定主键id，同时也没有唯一主键的列，mysql就会为我没生成单独的一列ROW_ID来作为主键。

如果没有指定主键列，同时有唯一列的话，则会找到第一个唯一列作为主键ID。

ROW_ID列不是必须的，只有在我们没有指定主键，同时表中有没有唯一主键的时候，Mysql才会为我们生成这个隐藏列。

事物id和回滚指针顾名思义是在事物回滚时候用到的，在后面事物章节会单独说明。这两个字段是必要的，数据库一定会为每条记录都生成。

行溢出

MySQL对于一条记录所能存储的最大长度是有限制的，也就是65535字节，所以如果超过这个长度的话就是报错。
根据上面的我们知道每条记录不至本身的记录数据信息，还会有Null值列表，变长字段列表等，所以如果有null值列表和变长字段列表的话，所以还需要减去几个字节。

mysql和磁盘的交互基本单位是页，16Kb，也就是16384字节，是小于65535字节的的，也就是如果我们某行字段特别长的话就会出现一页存储不了一条记录的情况。为了避免这种情况，Compact行记录格式并不会存储记录的所有数据在这个页中，而是只存储768个字节，剩余的字节存储到其他页中，使用指针指向这个地址。
所以最终的存储结构可能是这样的：

 

varchar、Blog、Text等变长字段类型都会产生行溢出

dynamic和Compressed行记录格式针对行溢出会把所有数据都存储到其他页中，而不只是只存储前768个字节，只保留一个指向额外也的指针。

总结

• MySql中硬盘和内存交互的基本单位是页，页也是mysql数据存储的基本单位，大小16Kb。
• 创建表的时候可以指定行记录格式

create table table_name（...） row_format=行记录格式类型

使用alter table table_name row_format=行记录格式类型修改行记录格式

• Innodb 目前有 Compact、Dynamic、Compressed、Redundant四种行记录格式
• 如果记录中数据太长，一页存放不下，会产生行溢出，也就是把多余的数据存到其他的数据也中。 compact和Redundant 会在当前记录存放前768个字节，其他的字节存到溢出页中。而Dynamic 和Compressed则会把所有数据存到其他数据页中，同时保留20字节的指针。