深入了解Innodb的行

站长

2023年03月27日 21:45 · 阅读数 18

行是Innodb当中用来存储具体的一次插入的数据的单位了，在了解Innodb的存储结构的过程当中，可以选择从行入手开始然后页，区，段，表，表空间这样的思路来进行，今天来深入聊一聊innodb的行。

Innodb的行根据版本有四种存储格式：Compact、Dynamic、Compressed、Redundant四种存储格式，其中compact和redundant是老版本的存储格式，目前默认的存储格式是Dynamic

首先来创建一个样例表做演示。

-- 创建表
create table worker(id int primary key auto_increment, w_name varchar(30), age int, gender char(2), education varchar(10));
-- 插入数据
insert into worker(w_name, age, gender, education) values ("张三", 18, "男","大专"),("李四", 23, "男","本科");

通过sql命令来查看表的行格式：

-- mysql 5.7
mysql> show table status where Name = "worker" \G;
*************************** 1. row ***************************
           Name: worker
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
1 row in set (0.00 sec)

ERROR:
No query specified

显然mysql 5.7默认的行格式是 Dynamic，当然也可以在创建表的时候指定行格式：

create table worker1(id int primary key auto_increment, w_name varchar(30), age int, gender char(2), education varchar(10)) row_format = Compact;
create table worker2(id int primary key auto_increment, w_name varchar(30), age int, gender char(2), education varchar(10)) row_format = Compressed;
create table worker3(id int primary key auto_increment, w_name varchar(30), age int, gender char(2), education varchar(10)) row_format = Redundant;

那么这个时候work,work1,work2,work3的行格式分别是：

mysql> show table status where Name like "worker%" \G;
*************************** 1. row ***************************
           Name: worker
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
*************************** 2. row ***************************
           Name: worker1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
*************************** 3. row ***************************
           Name: worker2
         Engine: InnoDB
        Version: 10
     Row_format: Compressed
*************************** 4. row ***************************
           Name: worker3
         Engine: InnoDB
        Version: 10
     Row_format: Redundant

行格式分类

Compact 格式

compact行记录格式是MySQL 5.0之后引入的，设计的目的是为了高效的存储数据，使用compact格式存储数据，数据量越大，性能越高。

以work表的第一条数据来看一下行结果

mysql> select * from worker where id = 1;
+----+--------+------+--------+-----------+
| id | w_name | age  | gender | education |
+----+--------+------+--------+-----------+
|  1 | 张三   |   18  | 男      | 大专      |
+----+--------+------+--------+-----------+
1 row in set (0.04 sec)

如果使用Compact格式，那么行数据结构应该如下：

深入了解Innodb的行

（字段有点多，中间的age,gender,education就省略了，嘿嘿嘿）

但是整体上来看，Compact格式存储数据分为两个大的部分：

额外数据

记录本行数据的特殊情况和描述，分为三个部分：

变长字段字节数列表

对于varchar这样不固定长度的类型，也就是变长字段类型，InnoDB在存储数据的时候，会把这些数据占用的真实字节数也保存下来，也就是变长字段是占用了两部分空间来存储的：

1、真实的数据内容。 2、占用的字节数。在COMPACT行格式中，把所有的变长字段所占用的字节数逆序排放在变长字段字节数列表中。

在案例表worker当中，w_name，education是varchar类型，插入数据：

insert into worker(w_name, age, gender, education) values ("a", 18, "男","dz");

这里,w_name的值a的占用的字节长度是1（十进制），deucation占用的字节长度是2（十进制），如果用十六进制表示，w_name的值a的占用的字节长度是0x01，deucation占用的字节长度是0x02,如果这一行使用COMPACT格式进行插入，那么格式如下：

深入了解Innodb的行

由于数据的长度都比较小，用一个字节就可以表示，但是如果变长字段占用的字节数比较多，就需要要用两个字节来表示了，这里Compact格式制定了一个标准：

1、如果列的长度小于255字节，用1个字节表示

2、如果大于255个字节，用2个字节表示

理解这个标准，需要注意两个点：

1、计算这个标准的长度，需要关注三个变量：

（1）数据的字符集,我们用变量W表示，因为不同的字符集一个字符需要占用的字节数不同，比如，ASCII一个字符1字节，GBK一个字符2字节，UTF8 一个字符3字节。

（2）varchar的指定长度，我们用变量M表示，比如，education varchar(10),那么就是10

（3）具体数据占用了几个字符，我们用变量L表示，比如，插入在education的值是'本科',那么就是，这里采用的是utf8编码，那么长度就是2*3

那么就与这三个变量看标准：

（1）如果M*W < 255，那么使用一个字节。

（2）如果M*W > 255, 那么就要考虑具L体的情况：

如果L < 127,那么使用一个字节

如果L > 127,那么使用两个字节

2、MySQL varchar的长度最长是65535，所以标准当中最大不可以超过2个字节

这里，变长字段字节数列表不是必须的，如果一个表中所有的字段都不是变长的，那么就没有变长字段字节数列表了。

Null值列表

这个很好理解，就是如果插入数据当中有NULL值就用1表示，这个部分占用1个字节，所以这里也可以侧面的看出，如果没有必要的化，不要随便设置可以为空的字段，对索引不好，记录的时候还得多一个字节，嘿嘿嘿。

记录头信息

这里是对整行信息，的描述，包括下面的字段

名称	大小（bit）	描述
预留位1	1	没有使用
预留位1	1	没有使用
delete_flag	1	是否删除
min_rec_flag	1	是否是预先定义的最小记录
n_owned	4	拥有该记录的记录数
heap_no	13	索引堆当中该条记录的排序记录
record_type	3	记录类型，000普通类型，001是B+书节点指针，
next_record	16	页中下一条记录的相对位置
Total	40	总数

真实数据

记录本行具体的字段和对应的数据

Redundant格式

相比较CompactREDUNDANT属于「非紧凑」的行格式，这意味着它比较占用磁盘空间，间接导致查询时可能需要更多的磁盘IO。占空间、效率不高，这就是它被淘汰的主要原因。 REDUNDANT存储记录的格式：

深入了解Innodb的行

字段长度偏移列表

REDUNDANT没有区别对待定长和变长字段，将所有列占用的存储空间都逆序存放在字段长度偏移列表中。根据字段的偏移量就可以定位到字段的存储位置，和下一个偏移量的差值可以计算出字段的长度，从而取出字段的完整信息。

记录头信息

这里是对整行信息，的描述，包括下面的字段,和compact的格式类似，只是略有不同。

名称	大小（bit）	描述
预留位1	1	没有使用
预留位1	1	没有使用
delete_flag	1	是否删除
min_rec_flag	1	是否是预先定义的最小记录
n_owned	4	拥有该记录的记录数
heap_no	13	索引堆当中该条记录的排序记录
n_field	10	记录中列的数量
1byte_offs_flag	1	标识字段长度偏移列表里用1字节还是2字节存储长度
next_record	16	下一条记录的相对位置

这里有几个点要注意：

1、当记录所有列的总长度不超过127时，使用1字节存储，因为总长度都没拆过127，单个字段的长度肯定不会超过127。列总长度大于127时，使用2字节存储。2字节最多能表示65535，如果有一行记录占用的空间超过了65535，那么该列肯定属于「溢出列」了，记录的真实数据处只会保存前768字节的数据+20字节的指针，剩余的数据则存储在专门的「溢出页」中。

2、REDUNDANT会把第0位用来标记是否为NULL，第0位是1则代表值为NULL，是0就不为NULL。

3、如果定长列存储的是NULL值，则NULL值也会占用存储空间，数据全部用0x00字节填充。例如char(10)就会占用10个字节(与字符集有关，utf8则直接占用30字节)，这样做的好处是，以后update该列时，可以直接复用这一块空间。如果变长列存储的是NULL值，则NULL值本身不占空间。

REDUNDANT设计的简单粗暴，正因如此也导致它比较浪费磁盘空间，属于非紧凑的行格式。我们接下来看COMPACT，你就知道它设计的有多紧凑了。

Dynamic格式与Compressed格式

MySQL5.7默认使用的行格式就是DYNAMIC了，它和COMPACT非常像，是COMPACT的一个变体。区别是在处理「溢出列」时，COMPACT会在「真实数据」处存储前768字节数据+20字节指针，而DYNAMIC只会存储20字节指针，溢出列的所有数据全部存储在「溢出页」中。 COMPRESSED的特点是它可以使用压缩算法对页面进行压缩，包括「溢出页」，这对于像长文本、TEXT、BLOB类型的数据来说，可以极大的节省空间。但是检索数据时，必须先解压才可以进行后续操作，这会消耗更多的CPU资源，CPU和磁盘IO两者的开销，需要各位去权衡。

总结

跌跌撞撞的总结了一下，还请各位大佬多多指点。