网站开发类比赛,开发一个电商平台需要多少钱,临河网站建设,东莞网页制作费用四、InnoDB数据存储结构之行格式 4.1 行格式的语法4.2 COMPACT 行格式4.2.1 记录的额外信息01、变长字段长度列表02、NULL 值列表03、记录头信息 4.2.2 记录的真实数据 4.3 Dynamic 和 Compressed 行格式4.3.1 字段的长度限制4.3.2 行溢出4.3.3 Dynamic 和 Compressed 行格式 4… 四、InnoDB数据存储结构之行格式 4.1 行格式的语法4.2 COMPACT 行格式4.2.1 记录的额外信息01、变长字段长度列表02、NULL 值列表03、记录头信息 4.2.2 记录的真实数据 4.3 Dynamic 和 Compressed 行格式4.3.1 字段的长度限制4.3.2 行溢出4.3.3 Dynamic 和 Compressed 行格式 4.4 Redundant 行格式4.4.1 字段长度偏移列表4.4.2 记录头信息record header4.4.3 NULL 值处理 我们平时的数据都是以行记录为单位向表中插入数据的这些记录在磁盘上的存放形式也被称为
行格式或者
记录格式。设计 InnoDB 存储引擎的大叔到现在为止设计了 4 中不同类型的行格式分别是 COMPACT、REDUNDANT、DYNAMIC 和 COMPRESSED。随着时间的推移它们可能会设计出更多的行格式但是不管怎么变这些行格式在原理上大体都是相同的。 ——摘自《MySQL 是怎样运行的》 4.1 行格式的语法
我们可以在创建或者修改表的语句中指定记录所使用的行格式
# 创建表时指定行格式
CREATE TABLE 表名(列的信息) ROW_FORMAT 行格式名称
# 修改表的行格式
ALTER TABLE 表名 ROW_FORMAT 行格式名称4.2 COMPACT 行格式
COMPACT 表示紧凑的在 MySQL 5.1 版本中默认设置为 COMPACT 行格式。
一条完整的记录可以分为记录的额外信息和记录的真实数据两大部分
4.2.1 记录的额外信息
这部分信息时服务器为了更好地管理记录而不得不额外添加的一些信息。这些额外的信息分为三部分分别是变长字段长度列表、NULL 值列表和记录头信息。
01、变长字段长度列表
MySQL 支持一些变长的数据类型比如 varchar(M)、varbinary(M)、text 类型、blob 类型这些数据类型修饰列称为变长字段变长字段中存储多少字节的数据不是固定的所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。
也就是说这些变长字段占用的存储空间分为两部分
真正的数据内容该数据占用的字节数。
在 Compact 行格式中把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位从而形成一个变长字段长度列表且各变长字段的真实数据占用的字节数按照列的顺序逆序存放。
比如创建一张表
create table record_test_table(col1 varchar(8),col2 varchar(8) not null,col3 vhar(8),col4 varchar(8)
) charsetascii row_formatCompact并向表里插入数据
insert into record_test_table(col1, col2, col3, col4)
values
(zhangsan, lisi, wangwu, songhk),
(tong, chen, NULL, NULL);因为 record_test_table 表中 col1、col2、col4 列都是 varchar(8) 类型的所以这三个列的值的长度都会被存储在记录开头处。由于使用的是 ascii 字符集所以每个字符只需要 1 个字节来进行编码 又因为这些长度值需要按照列的顺序逆序存放所以最后变长字段长度列表的字节串用十六进制表示的效果就是06 04 08。 小贴士并不是所有记录都有这个变长字段长度列表部分如果表中所有的列都不是变长的数据类型或者所有列的值都是 NULL 的话就不需要有变长字段长度列表不冗余存储节省存储空间了。 02、NULL 值列表
一条记录中的某些列可能存储 NULL 值如果把这些 NULL 值都放到 “记录的真实数据” 中存储会很占地方。所以COMPACT 行格式把一条记录中值为 NULL 的列统一管理起来存储到 NULL 值列表中。
为什么要定义 NULL 值列表呢
之所以要存储 NULL 值是因为数据都是需要对齐的如果没有标注出来 NULL 值的位置就有可能在查询数据的时候出现混乱的情况。如果使用一个特殊符号来代替 NULL 值放到对应的位置虽然可以达到效果但是大量的 NULL 值列会严重浪费空间所以干脆就直接在行数据的头部开辟出一块空间专门用来记录该行哪些数据是非空数据哪些是空数据。
⭐ 规定
将每个允许存储 NULL 的列对应一个二进制位二进制位按照列的顺序逆序存放格式如下
二进制位为 1 时代表该列的值为 NULL二进制位为 0 时代表该列的值不为 NULL
举个例子字段 a、b、c其中 a 是主键在某一行中存储的数依次是 a 1b nullc 2。那么 COMPACT 行格式中的 NULL 值列表中存储的是01。第一个 0 表示 c 的值不为 null第二个 1 表示 b 是 null。这里需要注意以下之所以没有 a 的值是因为数据库会自动跳过主键因为主键肯定是非 null 且唯一的在 null 值列表的数据中就会自动跳过主键。
record_test_table 的两条记录的 NULL 值列表为
# 这里是上文中插入的两条数据
insert into record_test_table(col1, col2, col3, col4)
values
(zhangsan, lisi, wangwu, songhk),
(tong, chen, NULL, NULL);这样我们就可以回答问题了MySQL 中的 NULL 值是怎么存储的 答NULL 值是由 NULL 值列表记录的用列的二进制位逆序表示每行记录中的每一列是否为 NULL 值0 代表不为 NULL1 代表为 NULL 值。 假设现有一张表其中有 4 个字段col1、col2、col3、col4。向其插入一条记录‘a’, NULL, NULL, ‘dd’那么 NULL 值列表使用二进制位表示为0 1 1 0转化成十进制就是 06。 03、记录头信息
除了变长字段长度列表、NULL 值列表之外还有一个记录头信息的部分它是由固定的 5 字节组成用于描述一些属性的。5 个字节也就是 40 个二进制位不同的位代表不同的属性 其中重点来说几个属性。 属性一、delete_mask删除标记 这个属性标记着当前记录是否被删除占用 1 个二进制位
值为 0代表记录并没有被删除值为 1代表记录被删除掉了
⭐ 被删除的记录为什么还在页中存储呢
这些被删除的记录不会从磁盘上移除是因为一旦移除其他的记录还需要在磁盘上重新排列这会带来性能消耗。
尤其是对聚簇索引的叶子节点来说假设移除的是主键值为 1 的记录那么整个聚簇索引的叶子节点都会因为这一条记录的删除全部重新排序显然这样是不合适的。
所以只是将这些删除的记录打一个删除标记以区分正常记录和被删除的记录所有被删除的记录会组成一个垃圾链表它们所占用的空间被称为可重用空间之后如果有新记录插入到表中时可能会覆盖掉复用被删除的记录占用的存储空间。
这里需要注意将 delete_flag 属性设置为 1 和将被删除的记录加入到垃圾链表中其实是分为两个阶段。 属性二、min_rec_mask非叶子节点最小记录标记 B 树的每层非叶子节点中的最小记录都会添加该标记min_rec_mask 值为 1。如果我们自己插入的四条记录的 min_rec_mask 值都是 0意味着它们都不是 B 树的非叶子节点中的最小记录。 属性三、record_type记录类型 这个属性表示当前记录的类型一共有 4 种类型的记录
0普通记录1B 树非叶节点记录2最小记录3最大记录
再回过头来看数据页结构和索引结构就可以理解当时的图中为什么表示的 record_type 值不一样了。 heap_no在页中的相对位置 这个属性表示当前记录在本页中的位置设计 InnoDB 的大叔把记录一条一条亲密无间排列的结构称为堆这个属性也表示在堆中的相对位置。
其中值为 0 和 1 的记录分别代表最小和最大记录这两条记录并不是我们自己插入的所以有时候也称为伪记录或虚拟记录。 n_owned每行记录数 页目录中会将所有的记录分成若干个组每个组中的最后一条记录的头信息中会存储该组一共有多少条记录来作为 n_owned 字段的值而其他记录的 n_owned 值都是 0。 next_record记录的相对位置 它表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。
假设第一条记录的 next_record 值为 32意味着从第一条记录的真实数据的地址处向后找 32 个字节就是下一条记录的真实数据。
注意下一条记录指得并不是按照我们插入顺序的下一条记录而是按照主键值由小到大的顺序的下一条记录。
并且InnoDB 底层规定 Infimum 记录最小记录的下一条记录就是当前页中主键值最小的记录而当前页中主键值最大的记录指向的下一条记录就是 Supremum 记录最大记录。用箭头指向代替地址偏移量来表示 next_record 下面来分别演示一下删除一条记录的操作和增加一条记录的操作。
⭐ 删除一条记录的操作
根据上图所示假设删除第 2 条记录
# 删除主键值为2的记录
delete from page_demo where c1 2;删除后整个链表也会跟着变化
把第二条记录的 delete_mask 值设置为 1而并没有从存储空间中移除把第二条记录的 next_record 值设置为 0意味着没有下一条记录了第一条记录的 next_record 指向第三条记录最大记录的 n_owned 值从 5 变成了 4。
⭐ 增加一条记录的操作
在 “删除一条记录的操作” 中主键值为 2 的记录被删除了变成了垃圾链表但是存储空间并没有被回收。现在要把这条数据再次插入
# 新增主键值为2的记录
insert into page_demo values(2, 200, tong);新插入的数据因为指定了主键值为 2所以按照聚簇索引结构这条记录会按照顺序插入原来第 2 条记录的位置因为原来被删除的第 2 条记录并没有被真实删除仍然占有空间所以这次新插入的数据会复用原有的空间。链表也会发生变化
第 2 条记录的 delete_mask 的值变为 0第 2 条记录的 next_record 的值变为 32第 1 条记录的 next_record 指向第 2 条记录第 2 条记录的 next_record 指向第 3 条记录最大记录的 n_owned的值从 4 5。
所以不论我们怎么对页中的记录做增删改操作InnoDB 始终会维护一条记录的单链表链表中的各个节点是按照主键值由小到大的顺序连接起来的。
4.2.2 记录的真实数据
记录的真实数据除了我们自定义的列的数据之外MySQL 会为每个记录默认地添加一些列隐藏列
列名是否必须占用空间描述DB_ROW_ID否6字节行ID唯一标识一条记录DB_TRX_ID是6字节事务IDDB_ROLL_PTR是7字节回滚指针
为了方便看就把它们都写成小写的row_id、transaction_id、roll_pointer。
在 InnoDB 表中InnoDB 表的主键生成策略是这样的
优先使用用户自定义的主键作为主键如果用户没有定义主键则选取一个不允许存储 NULL 值的 UNIQUE 键作为主键如果表中连不允许存储 NULL 值的 UNIQUE 键都没有定义则会为表默认添加一个名为 row_id 的隐藏列作为主键。
举个例子
现创建一张表 mytest
create table mytest(col1 varchar(10),col2 varchar(10),col3 char(10),col4 varchar(10)
)engineinnodb charsetlatin1 row_formatcompact并向表中插入三条数据
insert into mytest values
(a, bb, bb, ccc),
(d, ee, ee, fff),
(d, NULL, NULL, fff);找到存储文件 mytest.idb 的位置用 notepad dakai建议安装一个解析插件将乱码文件解析成十进制的数据格式 解析第一行记录如下 由于 col3 列是定长所以不计入变长字段长度列表中。 第二行的数据同第一行这里就不一一列举了。
第三行的数据中有 NULL 值所以在存储上与第一行、第二行有些差异
至于 transaction_id 和 roll_pointer暂时还没学到哩等学到时候会做笔记的呀~
4.3 Dynamic 和 Compressed 行格式
4.3.1 字段的长度限制
char 与 varchar 的区别如下 也就是说一个 varchar 类型的字段最大容量为 65535 个字节。
现在已经存在了一张表分别查看 MySQL 8.0.26 版本和 MySQL 5.7.34 版本的默认字符集 由此可见MySQL 8.0.26 字符集默认采用 utf8mb4MySQL 5.7.34 字符集默认采用 utf8。这两者的区别就是
utf8使用 3 个字节表示字符utf8mb4使用 4 个字节表示字符可以存储一些 emoji 表情等。
说明统一采用 MySQL 8.0.26 版本来进行接下来的操作。
⭐ 采用默认字符集 utf8mb4
报错提示字段长度最大不能超过 16383因为 8.0.26 版本默认字符集为 utf8mb4也就是说一个字符等于 4 个字节那么16383 * 4 65532还差 3 个字节才等于 65535。反过来65535 除以 4 结果等于 1633.75由于字段长度不能带小数将其四舍五入改为 16384 显然还是不能超过 16383 的所以字符集 utf8mb4 允许的最大长度为 16382。
那么那 3 个字节跑哪里了呢
16383 * 4 6553265535 - 65532 3。实际上每一行记录除了存储真实数据之外还有记录的额外信息中默认会有变长字段长度列表2 字节和 NULL 值列表1 字节。所以如果该 varchar 类型的的列没有 NOT NULL 属性每一行记录都会默认空出来 3 个字节存储变长字段长度列表和 NULL 值的标识实际最多只能存储 65532 个字节的数据。
⭐ 采用字符集 utf8
由上文可知需要预留 3 个字节65535 - 3 6553265532 / 3 21844。
重复上述验证步骤 由此可知字符集 utf8 字段的最大长度限制为 21844。
⭐ 采用 ascii 字符集
由上述推断可知ascii 字符集允许存储字段的最大长度为 65532。
4.3.2 行溢出
根据上文所说的单个字段的最大长度根据不同的字符集会有不同的限制8.0.26 默认采用 utf8mb4字符集4 个字节表示一个字符最大容量为 65532 字节。
而 InnoDB 中一个数据页的大小是 16 KB16 * 1024 16386 个字节也就是说一个 varchar 的容量远远大于一个数据页的大小这样就可能出现一个页存储不下一行记录的情况这种情况就叫做行溢出。
在 Compact 和 Redundant 行格式中对于占用存储空间非常大的列在记录的真实数据处只会存储该列的一部分数据768 个前缀字节把剩余的数据分散存储在其他的页中这叫作分页存储。
然后记录的真实数据处用 20 个字节存储指向这些分散页的地址这 20 个字节中还包括存储了分散在各个页中的真实数据占用的字节数从而可以找到剩余数据所在的页这称为页的扩展如下图所示
4.3.3 Dynamic 和 Compressed 行格式
在MySQL 8.0中默认行格式就是 DynamicDynamic、Compressed 行格式和 Compact 行格式类似只不过在处理行溢出数据时方式不同 Compact 和 Redundant 两种格式会在记录的真实数据处存储一部分数据存放768个前缀字节。 Compressed 和 Dynamic 两种行格式不会在记录的真实数据处存储列真实数据的前 768 字节而是把所有的数据都存储到溢出页中只在记录的真实数据处存储指向这些溢出页的地址(20 字节实际的数据都存放在 Off Page溢出页中: Compressed 和 Dynamic 行格式的区别Compressed 行格式在 Dynamic 的基础上优化了一层存储在其中的行数据会以 zlib 的算法进行压缩因此对于 BLOB、TEXT、VARCHAR 这类大长度类型的数据能够进行非常有效的存储。 4.4 Redundant 行格式
Redundant 是 MySQL5.0 版本之前 InnoDB 的行记录存储格式MySQL 5.0 支持 Redundant 是为了兼容之前版本的页格式。
我们可以直接修改表的行格式为 Redundant
alter table record_test_table row_rormatRedundant;Redundant 行格式存储格式
对比 Compact 行格式主要有两大处不同
Compact 是变长字段长度列表Redundant 是字段长度偏移列表。Compact 有 NULL 值列表Redundant 没有 NULL 值列表。
4.4.1 字段长度偏移列表
为什么说 Redundant 行格式会有冗余说法
因为 Redundant 行格式的字段长度偏移列表会将该行记录中所有列包括隐藏列的长度信息都按照逆序存储起来。
偏移两个字意味着 Redundant 行格式计算列值的长度的方式不像 Compact 行格式那么直观它是采用两个相邻数值的差值来计算各个列值的长度。
比如第一行记录的字段长度偏移列表逆序是 2B 25 1F 1B 13 0C 06 因为它是按照逆序排列的所以按照顺序排列就是 06 0C 13 1B 1F 25 2B 可以看出有三个隐藏列和四个字段列。
按照两个相邻数值的差值来计算各个字段列值的长度的如下表所示 4.4.2 记录头信息record header
不同于 Compact 行格式Redundant 行格式中的记录头信息固定占用 6 个字节48 位每位的含义如下 与 Compact 行格式的记录头信息对比来看有两处不同
Redundant 行格式多了 n_field 和 1byte_offs_flag 这两个属性。Redundant 行格式没有 record_type 这个属性。
其中两个属性的含义 n_field 代表一行中列的数量占用 10 位所以 MySQL5.0 之前的版本最多只能包含 1023 个列。 1byte_offs_flags 属性定义了字段长度偏移列表占用 1 个字节还是 2 个字节使用 127 作为分界点是因为127 二进制表示为 01111111第一位为 NULL 比特位用来标记是否为 NULL。 当记录的真实数据占用的字节数不大于 127 时占用 1 字节 当记录的真实数据占用的字节数大于 127但不大于 32767 时占用 2 字节 当记录的真实数据大于 32767 时这部分的数据被存放到溢出页中使用 2 字节来存储梅格列对应的偏移量。
4.4.3 NULL 值处理
因为 Redundant 行格式没有 NULL 值列表所以在字段长度偏移列表中对各列对应的偏移量做了一些特殊处理将列对应的偏移量值的第一个比特位作为是否为 NULL 的依据该比特位也可以称之为 NULL 比特位。
也就是说在解析一条记录的某个列时首先看一下该列对应的偏移量的 NULL 比特位是否为 1如果为 1那么该列的值就是 NULL否则就不是 NULL。
