MySQL原理 - InnoDB引擎 - 行记录存储 - Redundant行格式

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: MySQL原理 - InnoDB引擎 - 行记录存储 - Redundant行格式
本文基于 MySQL 8

上一篇:MySQL原理 - InnoDB引擎 - 行记录存储 - Compact格式 中,我们介绍了什么是 InnoDB 行记录存储以及 Compact 行格式,在这一篇中,我们继续介绍其他三种行格式。


Redundant 行格式


这个是最古老的,最简单粗暴的行格式了,现在基本上已经不用了,因为占用空间最多,从而导致内存碎片化最严重,是最低效的行格式了(针对现在varchar字段使用的更多,而对于 varchar 字段改变长度的更新大部分情况下就是将原有行的数据标记为已删除,然后在其他空间足够的地方新建记录,Redundant 顾名思义,占用空间更多,所以碎片化,空间浪费会更严重)。


MySQL官网的 Internal Mannual 给出的行格式示例,其实就是 Redundant 格式的: InnoDB Record High-Altitude Picture

创建一个和上一篇中的示例一样的表,插入相同的数据:

CREATE TABLE `record_test_2` (
  `id` bigint(20) DEFAULT NULL,
  `score` double DEFAULT NULL,
  `name` char(4) DEFAULT NULL,
  `content` varchar(8) DEFAULT NULL,
  `extra` varchar(16) DEFAULT NULL,
  `large_content` varchar(1024) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT
INSERT INTO `record_test_2`(`id`, `score`, `name`, `content`, `extra`, `large_content`) VALUES (1, 78.5, 'hash', 'wodetian', 'nidetiantadetian', 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz');
INSERT INTO `record_test_2`(`id`, `score`, `name`, `content`, `extra`, `large_content`) VALUES (65536, 17983.9812, 'zhx', 'shin', 'nosuke', 'lex');
INSERT INTO `record_test_2`(`id`, `score`, `name`, `content`, `extra`, `large_content`) VALUES (NULL, -669.996, 'aa', NULL, NULL, NULL);
INSERT INTO `record_test_2`(`id`, `score`, `name`, `content`, `extra`, `large_content`) VALUES (2048, NULL, NULL, 'c', 'jun', '');
INSERT INTO `record_test_2`(`id`, `score`, `name`, `content`, `extra`, `large_content`) VALUES (-1, 26.75, 'xxxx', 'aaaa', 'bbbb', 'cccc');

我们来直接看底层存储的数据是什么样子的:


微信图片_20220624184601.png


所有字段长度列表:00 c1 00 3f 00 2f 00 27 00 23 00 1b 00 13 00 0c 00 06 
记录头信息:00 00 10 12 01 65 
隐藏列DB_ROW_ID:00 00 00 00 09 00 
隐藏列DB_TRX_ID:00 00 00 03 cb 08 
隐藏列DB_ROLL_PTR:a8 00 00 01 1c 01 10 
列数据id(1):80 00 00 00 00 00 00 01 
列数据score(78.5):00 00 00 00 00 a0 53 40 
列数据name(hash):68 61 73 68 
列数据content(wodetian):77 6f 64 65 74 69 61 6e 
列数据extra(nidetiantadetian):6e 69 64 65 74 69 61 6e 74 61 64 65 74 69 61 6e 
列数据large_content(abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz):61 62 63 64 65 66 67 68 69 6a 6b 6c 6d 6e 6f 70 71 72 73 74 75 76 77 78 79 7a 61 62 63 64 65 66 67 68 69 6a 6b 6c 6d 6e 6f 70 71 72 73 74 75 76 77 78 79 7a 61 62 63 64 65 66 67 68 69 6a 6b 6c 6d 6e 6f 70 71 72 73 74 75 76 77 78 79 7a 61 62 63 64 65 66 67 68 69 6a 6b 6c 6d 6e 6f 70 71 72 73 74 75 76 77 78 79 7a 61 62 63 64 65 66 67 68 69 6a 6b 6c 6d 6e 6f 70 71 72 73 74 75 76 77 78 79 7a 
所有字段长度列表:34 31 2b 27 23 1b 13 0c 06 
记录头信息:00 00 18 13 01 a8 
隐藏列DB_ROW_ID:00 00 00 00 09 01 
隐藏列DB_TRX_ID:00 00 00 03 cb 09 
隐藏列DB_ROLL_PTR:a9 00 00 02 01 01 10 
列数据id(65536):80 00 00 00 00 01 00 00 
列数据score(17983.9812):b5 15 fb cb fe 8f d1 40 
列数据name(zhx):7a 68 78 20 
列数据content(shin):73 68 69 6e 
列数据extra(nosuke):6e 6f 73 75 6b 65 
列数据large_content(lex):6c 65 78 
所有字段长度列表:a7 a7 a7 27 23 9b 13 0c 06 
记录头信息:00 00 00 13 01 de 
隐藏列DB_ROW_ID:00 00 00 00 09 02 
隐藏列DB_TRX_ID:00 00 00 03 cb 0e 
隐藏列DB_ROLL_PTR:ac 00 00 01 00 01 10 
列数据id(null):00 00 00 00 00 00 00 00 
列数据score(-669.996):87 16 d9 ce f7 ef 84 c0 
列数据name(aa):61 61 20 20 
所有字段长度列表:ab 2b 28 a7 a3 1b 13 0c 06 
记录头信息:00 00 28 13 02 18 
隐藏列DB_ROW_ID:00 00 00 00 09 03 
隐藏列DB_TRX_ID:00 00 00 03 cb 0f 
隐藏列DB_ROLL_PTR:ad 00 00 01 21 01 10 
列数据id(2048):80 00 00 00 00 00 08 00 
列数据score(null):00 00 00 00 00 00 00 00 
列数据name(null):00 00 00 00 
列数据content(c):63 
列数据extra(jun):6a 75 6e 
所有字段长度列表:33 2f 2b 27 23 1b 13 0c 06 
记录头信息:00 00 30 13 00 74 
隐藏列DB_ROW_ID:00 00 00 00 09 04 
隐藏列DB_TRX_ID:00 00 00 03 cb 10 
隐藏列DB_ROLL_PTR:ae 00 00 01 22 01 10
列数据id(-1):7f ff ff ff ff ff ff ff 
列数据score(26.75):00 00 00 00 00 c0 3a 40 
列数据name(xxxx):78 78 78 78 
列数据content(aaaa):61 61 61 61 
列数据extra(bbbb):62 62 62 62 
列数据large_content(cccc):63 63 63 63


Redundant - 所有字段长度列表

不同于 Compact 行格式,Redundant 的开头是所有字段长度列表,而不是变长字段列表 + NULL 值列表。这个字段长度列表的格式是:

  • 记录所有字段的长度偏移,包括隐藏列。偏移就是,第一个字段长度为 a,第二个字段长度为 b,那么列表中第一个字段就是 a,第二个字段就是 a + b。
  • 所有字段倒序排列

对于长度存储,是一字节还是两字节,以及存储的内容,Redundant 的规则比较特殊:

  • 根据整行记录的长度决定,到底每个字段用一个字节还是两个字节,每个字段用一个字节还是两个字节,在记录头信息里面有标记
  • 如果整行长度小于 128,则用一字节存储
  • 如果大于等于128,则每个字段用两个字节


  • 对于一字节存储,最高位标记字段是否为 NULL,如果为 NULL,则最高位为1,否则为0. 剩下的 7 位用来存储长度,所以最多是 127
  • 对于两字节存储,最高位还是标记字段是否为NULL第二位标记这条记录是否在同一页,如果在则为0,如果不在则为1,这其实就涉及到了后面要说的溢出页。剩下的 14 位表示长度,所以最多是 16383


来推算一下第一行的所有字段长度列表:

由于第一行实际存储的长度超过了128,所以需要两字节。第一列到最后一列的长度,分别是:隐藏列DB_ROW_ID-6字节,隐藏列DB_TRX_ID-6字节,隐藏列DB_ROLL_PTR-7字节,列数据id-int-固定8字节,列数据score-double-固定8字节,列数据name-char-固定4字节,列数据content-varchar-变长8字节,列数据extra-varchar-变长14字节,large_content-变长130字节。转换成偏移后为:0x06,0x0c,0x13,0x1b,0x23,0x27,0x2f,0x3f,0xc1。变成两字节,倒序过来就是:00 c1 00 3f 00 2f 00 27 00 23 00 1b 00 13 00 0c 00 06


对于第三行,包含了 NULL 列,记录长度小于 128,用一字节存储。。第一列到最后一列的长度,分别是:隐藏列DB_ROW_ID-6字节,隐藏列DB_TRX_ID-6字节,隐藏列DB_ROLL_PTR-7字节,列数据id-int-固定8字节,列数据score-double-固定8字节,列数据name-char-固定4字节,列数据content-varchar-变长0字节,列数据extra-varchar-变长0字节,large_content-变长0字节。转换成偏移后为:0x06,0x0c,0x13,0x1b,0x23,0x27,0x27,0x27,0x27。由于第一列和最后三列为 NULL,所以将 0x1b,最后三个 0x27,0x27,0x27 的最高位设置为1,变成 0x9b,0xa7,0xa7,0xa7.倒序过来就是:a7 a7 a7 27 23 9b 13 0c 06


Redundant - 记录头信息

Redundant 行格式的记录头(48位)信息比 Compact 的(40位)多了:

名称大小(bits)描述无用位2目前没用到deleted_flag1记录是否被删除min_rec_flag1B+树中非叶子节点最小记录标记n_owned4该记录对应槽所拥有记录数量heap_no13该记录在堆中的序号,也可以理解为在堆中的位置信息n_field10该记录的列数量,范围从1到10231byte_offs_flag11代表每个字段长度为1字节,0代表2字节next_record pointer16页中下一条记录的相对位置


Redundant 行格式的记录头与 Compact 行格式的记录头的区别就是少了record_type位,多了n_field1byte_offs_flag这两个。


n_field用来表示该记录的列数量,范围从1到1023。这里的每一行都是 9 列,所以n_field都是9,也就是00000010011byte_offs_flag用来表示字段长度列表每一列占用的字节数,1代表每个字段长度为1字节,0代表2字节。这里只有第一行为两字节,所以第一行的这一位为0


第一行记录头信息:00 00 10 12 01 65 
转换为2进制:00000000 00000000 00010000 00010010 00000001 01100101
n_field:000 0001001
1byte_offs_flag:0
第二行记录头信息:00 00 18 13 01 a8 
转换为2进制:00000000 00000000 00011000 00010011 00000001 10101000
n_field:000 0001001
1byte_offs_flag:1
第三行记录头信息:00 00 00 13 01 de
转换为2进制:00000000 00000000 00011000 00010011 00000001 11011110
n_field:000 0001001
1byte_offs_flag:1
第四行记录头信息:00 00 28 13 02 18 
转换为2进制:00000000 00000000 00101000 00010011 00000010 00011000
n_field:000 0001001
1byte_offs_flag:1
第四行记录头信息:00 00 30 13 00 74 
转换为2进制:00000000 00000000 00110000 00010011 00000000 01110100
n_field:000 0001001
1byte_offs_flag:1


Redundant - 具体列记录存储与 Compact 区别


1. 对 NULL 值的处理

对于 NULL,不像 Compact 那样有 NULL 值列表,仅在字段长度列表的每个字段长度最高位标记 1 表示这个字段为 NULL。


同时对于定长字段,还会占用相同长度的字节空间,每个字节都填充上 00,例如第三,四行:

所有字段长度列表:a7 a7 a7 27 23 9b 13 0c 06 
记录头信息:00 00 00 13 01 de 
隐藏列DB_ROW_ID:00 00 00 00 09 02 
隐藏列DB_TRX_ID:00 00 00 03 cb 0e 
隐藏列DB_ROLL_PTR:ac 00 00 01 00 01 10 
列数据id(null):00 00 00 00 00 00 00 00 
列数据score(-669.996):87 16 d9 ce f7 ef 84 c0 
列数据name(aa):61 61 20 20 
所有字段长度列表:ab 2b 28 a7 a3 1b 13 0c 06 
记录头信息:00 00 28 13 02 18 
隐藏列DB_ROW_ID:00 00 00 00 09 03 
隐藏列DB_TRX_ID:00 00 00 03 cb 0f 
隐藏列DB_ROLL_PTR:ad 00 00 01 21 01 10 
列数据id(2048):80 00 00 00 00 00 08 00 
列数据score(null):00 00 00 00 00 00 00 00 
列数据name(null):00 00 00 00 
列数据content(c):63 
列数据extra(jun):6a 75 6e

bigint 为空时,填充了8个字节的 0x00。double 为空时,填充了8个字节的 0x00。char(4) 为空时,填充了4个字节的 0x00. 这样,对于这些定长字段的修改,无论是从 NULL 改成非 NULL 还是从非 NULL 改成 NULL,或者更新为不同长度(但是在原始限制内),都不用将原有记录标记为删除,之后再寻找新的空间重建更新后的记录了,直接在原有记录上面修改。对于 Compact,从 NULL 改成非 NULL 还是从非 NULL 改成 NULL,是需要这种麻烦的更新方式的,因为 NULL 不占用空间。


对于可变长度字段,Redundant 和 Compact 是相同的,为 NULL 不占用空间。只要改变长度,就会将原有记录标记为删除,之后再寻找新的空间重建更新后的记录


2. CHAR 类型存储

无论字段是否为 NULL,或者长度是多少,char(M) 都会占用 M * 字节编码最大长度那么多字节。为 NULL 的话,填充的是 0x00,不为 NULL,长度不够的情况下,末尾补充 0x20.


例如上面的第四行:

列数据name(null):00 00 00 00

还有第二行:

列数据name(zhx):7a 68 78 20

我们将 name 的编码修改为 utf-8:

ALTER TABLE `record_test_2` 
MODIFY COLUMN `name` char(4) CHARACTER SET utf8 NULL DEFAULT NULL AFTER `score`;

再来看第四行的数据,变成了:

列数据name(null):00 00 00 00 00 00 00 00 00 00 00 00

因为 utf8 最大字节占用为3字节,所以这里占用 12字节。同理,第二行:

列数据name(zhx):7a 68 78 20 20 20 20 20 20 20 20 20

对于不同编码的处理,Compact 和 Redundant 有明显的区别,Compact 不会占用那么多字节,而是在某些情况下像 varchar 一样处理:


  • NULL 还是不占用空间
  • 字段所有字符占用1字节,则按照1字节大小填充末尾的 0x20
  • 如果有其他不同字节长度的字符,则按照实际占用字节大小存储,不补充末尾的 20

举个例子,将上一节的 Compact 行格式的表,name 这一列修改编码为 utf8,同时修改数据:

ALTER TABLE `record_test_1` 
MODIFY COLUMN `name` char(4) CHARACTER SET utf8 NULL DEFAULT NULL AFTER `score`;
update `record_test_1` set name = "我们" where id = 2048;


来看 id 为 2048 的数据,变成了:

列数据name(我们):e6 88 91 e4 bb ac


和 varchar 一样,占用 6 字节,正好是存储数据的大小。

其他行的数据存储不变,例如:

列数据name(zhx):7a 68 78 20
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
7天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
113 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
|
7天前
|
存储 安全 关系型数据库
InnoDB引擎特性
InnoDB事务型数据库的首选引擎,支持事务安全表(ACID),支持行锁定和外键。MySQL5.5.5之后,InnoDB作为默认存储引擎,InnoDB主要特性有: InnoDB给MySQL提供了具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供了一个类似Oracle的非锁定读。 InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘关系的数据库引擎所不能匹敌的。 InnoDB存储引擎完全与MySQL服务器整合,InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池
|
7天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的表空间
InnoDB是MySQL默认的存储引擎,主要由存储结构、内存结构和线程结构组成。其存储结构分为逻辑和物理两部分,逻辑存储结构包括表空间、段、区和页。表空间是InnoDB逻辑结构的最高层,所有数据都存放在其中。默认情况下,InnoDB有一个共享表空间ibdata1,用于存放撤销信息、系统事务信息等。启用参数`innodb_file_per_table`后,每张表的数据可以单独存放在一个表空间内,但撤销信息等仍存放在共享表空间中。
|
7天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的段、区和页
MySQL的InnoDB存储引擎逻辑存储结构与Oracle相似,包括表空间、段、区和页。表空间由段和页组成,段包括数据段、索引段等。区是1MB的连续空间,页是16KB的最小物理存储单位。InnoDB是面向行的存储引擎,每个页最多可存放7992行记录。
|
8天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL的InnoDB存储引擎
InnoDB是MySQL的默认存储引擎,广泛应用于互联网公司。它支持事务、行级锁、外键和高效处理大量数据。InnoDB的主要特性包括解决不可重复读和幻读问题、高并发度、B+树索引等。其存储结构分为逻辑和物理两部分,内存结构类似Oracle的SGA和PGA,线程结构包括主线程、I/O线程和其他辅助线程。
【赵渝强老师】MySQL的InnoDB存储引擎
|
1月前
|
存储 缓存 关系型数据库
详细解析MySQL中的innodb和myisam
总之,InnoDB和MyISAM各有千秋,选择合适的存储引擎应基于对应用程序特性的深入理解,以及对性能、数据完整性和可扩展性的综合考量。随着技术发展,InnoDB因其全面的功能和日益优化的性能,逐渐成为更广泛场景下的首选。然而,在特定条件下,MyISAM依然保留其独特的价值。
116 0
|
9天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
23 4
|
7天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
22 1
|
1月前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
61 3
Mysql(4)—数据库索引
|
16天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
82 1