xyZGHio

樱花同载酒，终似少年游

0%

MySQL之InnoDB存储引擎：Row Format行格式

发表于 更新于 分类于 阅读数： 评论数：
本文字数： 5.9k 阅读时长 ≈ 12 分钟

MySQL下用的比较多、比较广的存储引擎就属InnoDB。这里我们来介绍下InnoDB存储引擎下数据记录的存储格式——Row Format行格式

abstract.png

基本操作

在MySQL中，所谓Row Format行格式是指数据记录(或者称之为行)在磁盘中的物理存储方式。具体地，对于InnoDB存储引擎而言，常见的行格式类型有Compact、Redundant、Dynamic和Compressed

设置、修改行格式信息

在创建、修改数据表的时候，可以显式地指定row format行格式。SQL语句语法如下

1
2
3
4
-- 创建数据表时,显示指定行格式
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称;
-- 创建数据表时,修改行格式
ALTER TABLE 表名 ROW_FORMAT=行格式名称;

与此同时，如果需要查看某数据表的行格式，可通过如下语句实现

1
show table status from 数据库名 like '<数据表名>';

演示示例

下面通过一个示例来验证上述语句的使用及效果。在test1数据库中创建一张名为task2的数据表，并指定行格式的类型为compact

1
2
3
4
5
6
7
8
9
10
use test1;
-- 指定行格式
create table task2
(
    idx int auto_increment,
    primary key (idx)
) row_format = compact;

-- 查看行格式
show table status from test1 like 'task2';

从下图可以看出表的行格式类型被设置为compact

figure 1.jpeg

然后再将该表的行格式类型修改为dynamic

1
2
3
4
5
6
-- 修改行格式
alter table task2
    row_format = dynamic;

-- 查看行格式
show table status from test1 like 'task2';

从下图可以看出表的行格式类型已被修改为dynamic

figure 2.jpeg

Compact 行格式

Compact行格式在MySQL 5.0中被引入，其目标是为了更高效的存储数据记录。在该格式下，一条数据记录的组成部分如下所示。其大体分为两部分——记录的额外信息、记录的数据内容。后者比较好理解，其即是我们存储到数据库的各列(字段)数据值。而前者则是MySQL为了更好描述该条记录而添加的额外信息

figure 3.png

变长字段的长度列表

由于MySQL支持变长的数据类型(如VARCHAR等)，故对于该类型而言，不仅需要存储该字段的实际数据，还需要额外存储该数据的长度信息(即占用的字节数)。而这就是变长字段的长度列表的用途。而一个变长字段的数据长度信息可能会使用1～2个字节来进行表示，具体地，如果某变长类型的字段 允许存储的最大字节数不超过255个字节，显然长度信息只需要一个字节表示即可；而当
某变长类型的字段允许存储的最大字节数超过255个字节时，这个时候就需要分情况讨论了：

  • 该字段数据实际使用的字节数不超过127个字节：依然只使用一个字节表示
  • 该字段数据实际使用的字节数超过127个字节：使用两个字节表示

对于该变长类型字段其允许存储的最大字节数该如何计算呢？这里我们以常见的VARCHAR(M)类型为例进行说明，我们知道该类型表示最多能存储M个字符。那一个字符占多少个字节呢？这就和我们之前所说的字符集有关了。我们知道字符集的Maxlen列信息，表示该字符集中一个字符最多需要几个字节来表示。比如utf8字符集下Maxlen列值为3、ascii字符集下Maxlen列值为1。故对于VARHCAR(M)来说，M x Maxlen 即为在某字符集下该变长类型字段允许存储的最大字节数

当然有人可能会问？MySQL在读记录的时候，如果发现某字段的M x Maxlen值超过255个字节了，那么它在读取记录的变长字段的长度列表时，是将该字节作为单独的字段长度信息还是作为半个的字段长度信息呢？即是读一个字节还是读两个字节。其实这个问题很简单，因为数值127写成二进制为 0111 1111。所以如果该字节的第一位为0，则该字节就是一个单独的字段长度信息；反之为1，则说明该字段只是半个的字段长度信息。即将字节的第一位作为标志位

Note

1. 变长字段的长度列表不存储值为NULL的长度信息

对于变长类型的字段而言，如果某记录下该字段的值为NULL，则变长字段的长度列表不会存储该字节的信息。因为是特殊值，会通过下面即将说明的NULL标志位进行存储

2. 变长字段的长度列表不是一定存在的

一方面，表中可能没有变长类型的字段；另一方面，如果该记录中所有的变长字段值均为NULL，根据第一点易知，此时变长字段的长度列表同样也没有存在的必要

3. 变长字段的长度列表中各字段长度信息是按列的顺序逆序排列的

这里我们假设有一张ascii字符集的数据表，其各字段依次为vf1,vf2,vf3,f4。其中vf1,vf2,vf3字段均为变长类型的字段。假设有一条记录其在各字段的值依次为：”a”，NULL，”ccc”，99。即变长字段vf1的长度为1，变长字段vf3的长度为3。则该记录的变长字段的长度列表内容为：0x03(即十进制的3)0x01(即十进制的1)

4. char类型字段的长度信息是否需要存储在 变长字段的长度列表 中取决于其所使用的字符集是否为变长字符集

MySQL中char(M)类型的字段表示该字段最多可以存储M个字符。对于定长字符集(如ascii字符集)而言，compact行格式下存储该字段所占有的空间固定为M x Maxlen个字节。但是对于变长字符集而言情况就大不一样了，例如utf8字符集，其存储M个字符所需的空间为M~3M个字节。在compact行格式下，char类型字段会由于使用变长字符集而导致其所占的字节数(即长度信息)不定，故此时char类型的数据同样也需要在变长字段的长度列表中存储其长度信息

NULL值标志位

对于记录中的特殊值NULL而言，如果直接存储到记录的数据内容部分则显得比较浪费空间，故这里使用位向量进行存储。如果某字段为主键或被NOT NULL所修饰，则其值自然不允许为NULL。故位向量中不是直接包含了所有字段，而是排除了值不能为NULL的字段，此举进一步减小了占用空间。具体地，位向量中某一位为1，则表明该列值为NULL；否则如果该位值为0，则表明值不为NULL

值得一提的是，首先位向量中代表的列同样是按照列的顺序逆序排列的，其次对于位向量不足一个字节的部分，高位需补0，即按照一字节对齐。假设这里有一张数据表，其字段定义分别为f1,f2,f3,f4。其中f2字段为主键，其余字段均可以为NULL，则有一条记录其值依次为：”Amy”,1,NULL,2，则该记录的NULL值标志位内容如下所示

figure 4.jpeg

Note

  1. NULL值标志位不是一定存在的

如果某条记录所有字段均不允许为NULL，则显然用于管理记录字段为NULL值的NULL值标志位在该条记录中是没有必要的，即不存在

记录头信息

记录头信息用于描述该条记录，其固定为5个字节，即40位。其定义如下

figure 5.jpeg

  • 预留位1、2：暂未使用
  • delete_mask：当前记录被删除的标志位
  • min_rec_mask：B+树的每层非叶子节点中的最小记录的标志位
  • n_owned：当前记录拥有的记录数
  • heap_no：当前记录在记录堆中的位置
  • record_type：当前记录类型。具体地，0: 普通记录；1: B+树非叶子节点记录（即所谓的目录项记录）；2: 最小记录；3: 最大记录
  • next_record：下一条记录的相对位置

记录的数据内容

记录的数据内容，通常来说就是我们日常开发、使用过程中需存储的记录的数据内容。但其实，MySQL对于我们所定义的数据表，还会默认的插入一些其他列(字段)，即所谓的隐藏列。其字段定义说明如下

  • DB_ROW_ID：该字段占6个字节，用于标识一条记录
  • DB_TRX_ID：该字段占6个字节，其值为事务ID
  • DB_ROLL_PTR：该字段占7个字节，其值为回滚指针

上述3个字段，除了DB_ROW_ID字段，其余两个字段均一定会被添加到数据表中的。一般地，当用户未指定数据表的主键时，MySQL会选择非NULL的Unique键作为主键。而如果非NULL的Unique键也没有的话，这个时候MySQL才会添加向数据表添加DB_ROW_ID字段用来作为主键。当然上述隐藏列的3个字段的值是由MySQL自动生成、存储的

Note

1. 值为NULL的记录不会存储于此

记录的数据内容不包括字段值为NULL的数据内容。前文已经提到，对于记录中某字段为NULL值的信息已经体现在NULL值标志位中了，故此处将不再重复存储以节省空间

2. char类型字段的存储方式

值得一提的是，对于变长字符集下的char(M)类型字段而言，出于存储优化的角度考虑，compact行格式要求存储该字段的值时至少占用M个字节，字段值所占字节数不足时则使用 空格字符(空格字符在ascii字符集下为0x20) 填充。比如某字段类型为char(10)，其使用utf8字符集，即使某条记录该字段值为”abc”，只使用了3个字节。但是在存储”abc”时，依然会通过在其后填充若干个空格字符的方式使其达到占用10个字节的空间来进行存储。此举目的其实也很简单，当该条记录下次需要更新时，如果新值使用的空间不超过10个字节时，则可以直接在此条记录处进行更新而无需重新分配一个新的记录空间，从而导致原有的记录空间成为碎片。当然在读值的时候会把填充的空格字符移除掉

Redundant 行格式

Redundant作为MySQL 5.0之前使用的一种行格式，其示意图如下所示，可以看到其与compact行格式在结构上大体还是比较相似的。这里主要来介绍下其与compact行格式不同之处

figure 6.png

字段长度偏移列表

在Redundant行格式下，其通过字段长度偏移列表存储记录中所有列(包括隐藏列)的长度信息。首先计算记录中各字段的长度信息，然后再顺序计算长度的累计值，最后再按数据表各字段的顺序逆序排列，即为实际存储的字段长度偏移列表。反过来如果期望获取记录中某字段的长度信息，只需计算两个相邻长度累计值的差值即可

figure 7.jpeg

具体地，字段长度偏移列表中的各偏移量所占空间要么均为1个字节，要么均为2个字节。那么实际存储记录时，到底是使用1个字节还是使用2个字节来存储字段长度偏移列表呢？其实在该格式下，策略比较简单。若记录的整个数据内容部分(包括隐藏列)所占用的空间不超过127(0x7F)个字节，则该记录的字段长度偏移列表的各偏移量自然只需使用1个字节即可表示，毕竟所有列长度值加起来也不超过127，那么各个列所对应的偏移量自然也不会超过127了；反之如果超过127个字节但不超过32767(0x7FFF)个字节，则会使用2个字节来表示各偏移量。与此同时，该记录会通过记录头信息的1byte_offs_flag属性来表明该记录各偏移量的字节数，以便读取记录的时候方便解析

Note

  1. 偏移量包含NULL值标志

一方面，通过对比compact与redundant的结构图，可以发现在compact行格式下其没有NULL值列表；另一方面，偏移量在使用1、2个字节表示时，其能表示的最大值分别为0x7F、0x7FFF，即偏移量的第一位实际上是没有使用的。其实原因就在于，对于各列偏移量的第一位而言，其用于标识该记录下相应列是否为NULL值。如果是NULL值，则该位为1；反之，则为0

记录头信息

记录头信息用于描述该条记录，其固定为6个字节，即48位。其定义如下

figure 8.jpeg

  • 预留位1、2：暂未使用
  • delete_mask：当前记录被删除的标志位
  • min_rec_mask：B+树的每层非叶子节点中的最小记录的标志位
  • n_owned：当前记录拥有的记录数
  • heap_no：当前记录在记录堆中的位置
  • n_field：表示记录中列的数量
  • 1byte_offs_flag：标识字段长度偏移列表中各列的偏移量使用的字节数。0：意为每个偏移量均使用2个字节表示；1：意为每个偏移量均使用1个字节表示
  • next_record：下一条记录的相对位置

记录的数据内容

在redundant行格式下，其与compact行格式一样，同样有隐藏列的情况。这里就不再赘述了。这里我们就不同的地方进行一些介绍。前面我们提到在compact行格式下，对于char(M)类型的字段数据在定长字符集、变长字符集下的存储方式是有差异的，相对而言还是比较复杂；而在redundant行格式下，无论是使用变长字符集还是使用定长字符集，char(M)类型的字段总是占用M x Maxlen个字节的空间，字段值所占空间的字节数不足则同样会使用 空格字符(空格字符在ascii字符集下为0x20) 进行填充

上文提到compact行格式下，字段为NULL值是不会在记录的数据内容中占用存储空间，冗余存储的；而在redundant行格式下，对于字段为NULL值在记录的数据内容部分的存储则略有不同

  • 如果值为NULL的字段类型为变长数据类型，其同样不会在记录的数据内容中占用任何空间来进行存储的
  • 如果值为NULL的字段类型为定长数据类型，其使用0x00来填充该字段所需占用的空间。例如char(10)类型的字段，在ascii、utf8字符集中其Maxlen值分别为1、3。即该字段在数据内容部分会分别占用的10、30个字节。故当该字段为NULL值时，会使用0x00来填充这10、30个字节的位置

行溢出

众所周知，InnoDB存储引擎中内存与硬盘交互的基本单位是页，一般地页大小为16KB。MySQL规定一个页中至少需要存放两条记录。而所谓的行溢出是指：当某个记录的某个字段(varchar、text、blob等类型)的值长度过长、数据量过大，会导致一个页中放不下一条记录，为此在compact、redundant行格式中，如果该记录某字段中数据量过多时，则在该记录的数据内容的相应字段处只存储该字段值前768个字节的数据和一个指向存储剩余数据的其他页（即所谓的溢出页）的地址，该地址通常占用20个字节

Dynamic、Compressed行格式

对于Dynamic、Compressed行格式而言，其和compact行格式比较相似。不同的在于，对待处理行溢出的处理及策略，Dynamic、Compressed行格式会把记录中数据量过大的字段值全部存储到溢出页中，而不会在该记录的数据内容的相应字段处存储该字段值前768个字节的数据了。而compressed相比较dynamic行格式来说，前者会使用压缩算法对所有页面(自然也包括溢出页)进行压缩以减少存储占用

推荐文章
请我喝杯咖啡捏～

欢迎关注我的微信公众号：青灯抽丝