MySQL 行溢出
本文基于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）位
| 名称 | 大小(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的插件
表数据是从*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=utf8mb4
insert into testText (id,data) values (1,repeat('0123456789abcdef',5));
新建一个表使用的格式是utf8mb4变长也就是1个字母占用一个字节，我用的MySQL版本为5.7所以默认行格式是Dynamic
接下来插入一条数据重复0123456789abcdef这里是16个字节重复5次这样肯定超过了40个字节对吧
分析发现还是在行中存储，你可能会问不对啊 我看八股文和官方文档上确实说40个字节会导致行溢出，我们再来看官方文档
在40个字节之前还有个限定条件具体是否溢出还需要看页大小以及行大小
我们继续使用之前的数据这次改为重复506次，再来看二进制的数据
发现这里已经变成了20字节的指针
溢出页数据
所以我们可以知道只有在行长度太长的情况下才会出现页溢出，但是页溢出的大概值是多少呢
- 每个页16k会有一些额外信息大概136个字节
- 每个记录的额外字节大概是27 不是具体值可能会因为没有NULL值等情况
- 每个页最少两条行记录
所以可以得出 如果136 + 2×(27 + n) > 16384
n = 8098 这里只是大概值具体会有上下浮动，所以结论是：如果行长太长导致一行存不下时才会发生行溢出，如果有多列会溢出最长的那列直到一行能存下所有数据
影响
当出现行溢出时会查询溢出的页导致多余的磁盘I/O也会导致查询时间增加，所以建议在查询时只查询需要的列，对于可能溢出的列又用不到就不要去查了。
引用
MySQL技术内幕
MySQL是怎样运行的
InnoDB 如何处理 TEXT/BLOB 列
what is off page in mysql
InnoDB Row Formats
