MySQL 行溢出

手机

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

家电

MySQL 行溢出

2022-12-14 11:24:58 来源：博客园

本文基于MySQL 5.7

行格式

InnoDB包含多张行格式

Compact
Redundant
Dynamic
Compressed

Compact行记录格式

Compact行记录是在MySQL 5.0中就引入的，其设计目标是高效地存储数据：一个页中存放的行数据越多，其性能就越高。

(资料图)

格式的首部是一个非NULL变长字段长度列表，并且是按照列的顺序逆序放置的，其长度为：
- 若列的长度小于255字节，则用1字节表示；
- 若大于255字节，则用2字节表示。
- 变长字段的长度最大不可超过2字节，这是因为VARCHAR类型的最大长度限制是65545(2^16 - 1)

如果是TEXT或者BLOB等情况会存在溢出页，行中不存储长度，具体后面会降到

第二部分是NULL标识位，标识这行数据中是否有NULL值，有则用1表示。占用1字节
接下来是记录头信息，固定占用5字节（40）位

Compact记录头信息

名称	大小(bit)	描述
()	1	未知预留位
()	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	表示下一条记录的相对位置
Total	40

每行记录除了用户数据外还会额外有一些隐藏列

列名	是否必须	占用空间	描述
`row_id`	否	`6`字节	行ID，唯一标识一条记录
`transaction_id`	是	`6`字节	事务ID
`roll_pointer`	是	`7`字节	回滚指针

光说不练没什么用我们实际搞点数据看看

相信大家下面的代码都能看懂

CREATE TABLE mytest(t1 VARCHAR(10),t2 VARCHAR(10),t3 CHAR(10),t4 VARCHAR(10)) ENGINE=INNODB  CHARSET=LATIN1 ROW_FORMAT = COMPACT;INSERT INTO mytest VALUES("a","bb","bb","ccc");

拿到idb文件，这里有两个文件在InnoDB中 frm是表结构文件，idb为表数据文件

用hexdump等工具打开idb文件查看，这里我用的是vscode的插件

hexeditor

表数据是从*0xc000* 开始直接跳到0xc000即可

03 02 01 是变长标识位分别对应t4,t2,t1的长度逆序存放

00 NULL标识位标识没有NULL值

00 00 10 FF EF 记录头数据

00 00 00 00 02 00 Row ID InnoDB自行创建

00 00 00 00 1A 02 transaction_id事物id

BD 00 00 01 35 01 10 roll_pointer 回滚指针

61 列1数据a

62 62 列2数据 bb

62 62 20 20 20 20 20 20 20 20 列3数据 bb 不足用20空格代替

63 63 63 列3数据

Redundant行记录格式

Redundant是MySQL5.0之前的InnoDB的行记录格式，之后只是为了兼容之前的版本所以这里不展开讲解

Dynamic与Compressed

Dynamic是MySQL 5.7默认的行格式

这两种格式为InnoDB 1.0.x版本后新引入的格式，在格式上与Compact基本相同只是在行溢出上处理不同

行溢出是什么下面会讲到

Compressed行记录还会对行数据进行zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度类型数据会有更好的存储优化

行溢出

试想一个问题VARCHAR ASCII(一个字符占用1个字节)编码下最大的大小是多少?

CREATE TABLE mytest2(t1 VARCHAR(100000)) ENGINE = INNODB CHARSET = ASCII

报错

1074 - Column length too big for column "t1" (max = 65535); use BLOB or TEXT instead, Time: 0.002000s

提示我们最大是65535也就是2^16 -1

那么我们思考一个问题 MySQL以页为单位进行操作每个页默认大小为16k，同时MySQL规定一个页中最少有两条数据，至于为什么和B+树结构有关（后面会给出引用），我们不考虑页额外占用的大小以及行额外占用的大小65532肯定大于8k，那数据是怎么存储的呢？

对于这种情况InnoDB会把TEXT、BLOB、VARCHAR等格式数据存放在页类型为Uncompress BLOB的页中，在行中存储前768个字节+偏移量指向溢出页，注意：这里不是绝对会存储前768个字节，不同行格式之间是不同的

在Compact和Reduntant行格式中溢出页是这样存储的

Dynamic和Compressed行格式溢出页存储有所不同

行溢出的边界

如果你看过一些八股文可能会说TEXT类型超过40个字节就会行溢出，或者看官方文档

真实情况是这样么？

CREATE TABLE `testText` (`id` int(11) unsigned NOT NULL,`before` char(6) NOT NULL DEFAULT "before",`data` VARCHAR(10240) NOT NULL,`after` char(5) NOT NULL DEFAULT "after",PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4insert into testText (id,data) values (1,repeat("0123456789abcdef",5));

新建一个表使用的格式是utf8mb4变长也就是1个字母占用一个字节，我用的MySQL版本为5.7所以默认行格式是Dynamic

接下来插入一条数据重复0123456789abcdef这里是16个字节重复5次这样肯定超过了40个字节对吧

分析发现还是在行中存储，你可能会问不对啊我看八股文和官方文档上确实说40个字节会导致行溢出，我们再来看官方文档

在40个字节之前还有个限定条件具体是否溢出还需要看页大小以及行大小

我们继续使用之前的数据这次改为重复506次，再来看二进制的数据

发现这里已经变成了20字节的指针

溢出页数据

所以我们可以知道只有在行长度太长的情况下才会出现页溢出，但是页溢出的大概值是多少呢