InnoDB存储引擎和大多数数据库一样,记录是以行的形式存储的,即页中保留着表中一行行的数据,这些记录在磁盘上的存放方式也被成为行格式;
InnoDB支持四种行格式:REDUNDANT,COMPACT,DYNAMIC,COMPRESSED
参考:[https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html]
指定行记录格式的语法
查看代码
-- 建表
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称;
-- 更改表结构
ALTER TABLE 表名 ROW_FORMAT=行格式名称;
Compact行格式
Compact行记录是在MySQL5.0中引入的,其设计目标是高效地存储数据;一个页中存放的行数据越多,其性能就越高;
分析Compact行记录的内部结构
表结构如下:
CREATE TABLE `record_format_demo` (
`c1` varchar(10) DEFAULT NULL,
`c2` varchar(10) NOT NULL,
`c3` char(10) DEFAULT NULL,
`c4` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=ascii ROW_FORMAT=COMPACT;
INSERT INTO record_format_demo(c1, c2, c3, c4) VALUES('aaaa', 'bbb', 'cc', 'd'), ('eeee', 'fff', NULL, NULL);
Compact行记录格式
变长字段长度列表
Compact行记录格式的首部是一个非NULL变长字段长度列表,
MySQL支持一些变长的数据类型,如:VARCHAR(M),VARBINARY(M),各种TEXT类型,各种BLOB类型,这些类型的数据为变长字段,变长字段中存储多少字节数是不固定的;Compact行记录格式的首部是一个非NULL变长字段长度列表,并且其是按照列的顺序逆序放置的;
第一条记录各变长字段内容的长度
列名 | 存储内容 | 内容长度(十进制表示) | 内容长度(十六进制表示) |
c1 | aaaa | 4 | 0x04 |
c2 | bbb | 3 | 0x03 |
c4 | d | 1 | 0x01 |
变长字段长度列表是按照列的顺序逆序放置的,如下
变长列占用的字节数的计算,定义以下几个变量
- 某个字符集中表示一个字符最多需要使用的字节数为W(即使用SHOW CHARSET语句的结果中的Maxlen列);
- 对于变长类型VARCHAR(M)来说,这种类型表示能存储最多M个字符,即这个类型能表示的字符串最多占用的字节数就是M * W;
存储长度分为两种规则
- M * W <= 255
那么使用1字节来表示真正字符串占用的字节数;
- M * W > 255
如果实际存储的字符串占用的字节数 <= 127,则用1个字节来表示存储的字符串占用的字节数;
如果实际存储的字符串占用的字节数 > 127,则用2个字节来表示存储字符串占用的字符数;
InnoDB在读记录的变长字段长度列表,如果某个变长字段允许存储的最大字节数大于255时,如果该字节的第一个位为0,那该字节就是一个单独的字段长度(使用一个字节表示不大于127的二进制的第一个位都为0),如果该字节的第一个位为1,那该字节就是半个字段长度;
如果该可变字段允许存储的最大字节数(M * W)超过255字节且实际存储的字符串的字节数超过127个字节,则用2字节表示,否则使用1字节表示;
变长字段列表只存储值为非NULL的列内容占用的长度,值为NULL的列的长度是不存储的,上面初始化脚本执行完后,填充玩变长字段长度列表的两条记录,如下:
NULL标志位
变长字段之后的第二个部分是NULL标志位,该位指示了该行数据中是否有NULL值;
如果表中没有允许存储NULL的列,那么NULL值列表不存在,否则将每个允许存储NULL的列对应一个二进制位,二进制为按照列的顺序逆序排列;
- 二进制位的值为1时,代表该列的值为NULL
- 二进制位的值为1时,代表该列的值不为NULL
上面的表结构允许c1,c3,c4列的值为NULL,其中二进制按照列的顺序逆序排列,如下:
MySQL规定NULL值列表必须用整数个字节的比特位表示,其中1个字节=8个比特位,如果使用的二进制位个数不是整数个字节,则在字节的高位补0;
1个字节=8个比特位,当允许值为NULL的列超过8个时,如9个,这时记录NULL值列表需要2个字节;
上面的表的第一行数据,NULL标志位填充后用0x00表示,如下:
上面的表的第二行数据,NULL标志位填充后用0x06表示,如下:
记录头信息
接下来的部分是记录头信息,Compact记录头信息是固定由5个字节组成
名称 | 大小(bit) | 描述 |
预留位1 | 1 | 没有使用 |
预留位2 | 1 | 没有使用 |
deleted_flag | 1 | 标记改行是否被删除 |
min_rec_flag | 1 | B+树的每层非叶子节点的最小记录都会被添加该标记 |
n_owned | 4 | 表示当前记录拥有的记录数 |
heap_no | 13 | 表示当前记录在记录堆中的位置 |
record_type | 3 | 记录类型,000表示普通,001表示B+树节点指针,010表示Infinmum,011表示Supremum,1xx表示保留 |
next_record | 16 | 页中下一条的相对位置 |
第一条记录Compact记录头信息记录为0x000010002D,如下:
第二条记录Compact记录头信息记录为0x000018FFC2,如下:
记录的真实数据
每行数据除了用户定义的列外,还有两个隐藏列,事务ID列和回滚指针列,分别为6字节和7字节的大小,若InnoDB表没有主键,每行还会增加一个6字节的rowid列;
列名 | 是否必须 | 占用字节 | 描述 |
DB_ROW_ID | 否 | 6字节 | 行ID,唯一标识一条记录 |
DB_TRX_ID | 是 | 6字节 | 事务ID |
DB_ROLL_PTR | 是 | 7字节 | 回滚指针 |
参考:[https://dev.mysql.com/doc/refman/5.7/en/information-schema-innodb-temp-table-info-table.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions-limitations.html]
测试表使用ASCII字符集,数据转换如下:
- 第一行
c1列的为'aaaa','a'对应的ASCII的16进制为0x61,'aaaa'的16进制为0x61616161;
c2列的为'bbb','a'对应的ASCII的16进制为0x62,'bbb'的16进制为0x626262;
c3列的为'cc','c'对应的ASCII的16进制为0x63,'cc'的16进制为0x6363,但c3列的数据类型为CHAR(10),该列仍然占用10个字节,除了真实数据'cc'外都用空格字符填充,空格字符的ACSII的16进制为0x20,该列数据的16进制为0x63632020202020202020;
c4列的为'd','d'对应的ASCII的16进制为0x64,'aaaa'的16进制为0x64;
第二行
以此类推,c1列数据的16进制为0x65656565,c2列的数据为0x666666,而c3列和c4列的数据为NULL,因为不管是CHAR类型还是VARCHAR类型,在Compact格式下NULL值都不占用任何空间;
查看表空间文件
Linux环境下,使用命令
hexdump -C -v record_format_demo.ibd > record_format_demo.txt
将文件结果重定向到了文件record_format_demo.txt中,打开,找到如下内容:
MySQL Supremum参考:[https://dev.mysql.com/doc/internals/en/innodb-infimum-and-supremum-records.html]
数据记录从0x0000C078开始,第一行数据存储,如下:
01 03 04 /* 变长字段长度列表,逆序 */
00 /* NULL标志位,第一行没有空值 */
00 00 10 00 2d /* 记录头信息 */
00 00 00 00 02 00 /* DB_ROW_ID */
00 00 00 00 34 16 /* DB_TRX_ID */
b6 00 00 01 2a 01 10 /* DB_ROLL_PTR */
61 61 61 61 /* c1列 */
62 62 62 /* c2列 */
63 63 20 20 20 20 20 20 20 20 /* c3列 */
64 /* c4列 */
记录头信息中最后两个字节代表下一个记录的偏移量,即当前记录的位置加上偏移量0x2d就是下一条记录的相对位置的起始位置,0x0000C080 + 0x2d = 0x0000C0AD,其中0x0000C080为记录头信息中next_record的地址,加上0x2d的偏移量后得到的是下一条记录中的记录头信息中的next_record的地址,如下图
第二行数据存储,如下:
03 04 /* 变长字段长度列表,逆序 */
06 /* NULL标志位,c3列,c4列表为空值 */
00 00 18 ff c2 /* 记录头信息 */
00 00 00 00 02 01 /* DB_ROW_ID */
00 00 00 00 34 16 /* DB_TRX_ID */
b6 00 00 01 2a 01 1f /* DB_ROLL_PTR */
65 65 65 65 /* c1列 */
66 66 66 /* c2列 */
Redudant行格式
Redudant行格式是MySQL5.0之前InnoDB的行记录存储方式,MySQL5.0支持Redudant是为了兼容之前版本的页格式,这种行格式非常老了,这里就不整理了;
行溢出数据
InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外,一般认为BLOB,LOB这类的大对象列类型的存储会把数据存放在数据页面之外,但是BLOB可以不将数据放在溢出页面,而且即便是VARCHAR列数据类型依然有可能被存放行溢出的数据;
VARCHAR(M)最多能存储的数据
VARCHAR(M)类型的列最多可以占用65535个字节,其中M表示该类型最多存储的字符数量,如果使用ASCII字符集,VARCHAR(65535)是否可用?
VARCHAR(M)存储包含3部分存储空间
- 真实数据占用字节长度
- 真实数据
- NULL值标识,如果该列有NOT NULL属性,那没有这部分空间的存储
如果该VARCHAR类型的列没有NOT NULL属性,那最多只能存储65532个字节的数据,其中,NULL值标识需要占用1个字节,真实数据可能占用2个字节;
如果该VARCHAR类型的列有NOT NULL属性,那最多只能存储65533个字节的数据,其中,NULL值标识不需要占用1个字节,真实数据可能占用2个字节;
上述创建的表其字符类型都是ASCII类型的,如果更换成GBK/UTF-8,会产生什么样的结果?
![](/i/l/?n=22&i=blog/1334716/202208/1334716-20220813204550707-757365191.png)
从执行结果可以看出,如果VARCHAR(M)类型的列使用的不是ASCII字符集,那么M的最大取值取决于该字符集表示一个字符最多需要多少的字节数;在列允许NULL的情况下,GBK的字符集表示一个字符最多需要2个字节,那在该字符集下,M的最大取值就是32766(也就是:65532/2),也就是说最多能存储32766个字符;UTF-8字符集表示一个字符最多需要3个字节,那在该字符集下,M的最大取值就是21844,最多能存储21844个字符;
VARCHAR(M)中的M值得是字符的长度,而文档中说明VARCHAR类型最大支持65535,单位是字节;此外需要注意的是,MySQL官方文档中定义的65535长度是指所有VARCHAR列的长度总和,如果列的长度总和超出这个长度,依然无法创建;
3个列长度总和是66000,因此InnoDB存储引擎再次报了同样的错误;
记录中数据太多产生行溢出
InnoDB存储引擎的页为16KB,即16384字节,如何存储65532字节?
REPEAT('a', 65532)是一个函数调用,它表示生成一个字符'a'重复65532次的字符串;
MySQL中磁盘与内存交互的基本单位是页,即MySQL是以页为单位管理存储空间的,每一条记录都会被分配到某个页中存储;
一般情况下,InnoDB存储引擎的数据都是存放在页类型为B-tree node中,但是发生行溢出(如上面的例子,MySQL一个页的大小为16KB,即16384字节,但VARCHAR(65532)最多存储65532个字节,这就会造成一个页存放不了一条记录)时,数据存放在页类型为Uncompress BLOB页中;
在Compact和Redundant行格式中,对于占用存储空间非常大的列,在记录的真实数据处只会存储该列的一部分数据,把剩余的数据分散存储在几个其他的页中,然后记录的真实数据处用20个字节存储指向这些页的地址(这20个字节中还包括这些分散在其他页面中的数据的占用的字节数),从而可以找到剩余数据所在的页;
对于Compact和Redundant行格式来说,如果某一列中的数据非常多,在本记录的真实数据处只会记录前768个字节和一个指向其他页的地址,然后把剩下的数据放到其他页,这个过程为行溢出,存储超过768个字节的那些数据页为溢出页,如下图;
参考:[https://dev.mysql.com/doc/refman/5.7/en/char.html
https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html]
行溢出的临界点
注:MySQL中规定一个页最少存放两行记录;CREATE TABLE varchar_size_demo(
c VARCHAR(65532)
) CHARSET=ascii ROW_FORMAT=Compact;
以上面的varchar_size_demo表为例子,它只有一个列,每条记录最少插入多少字节的数据才会出现行溢出;
- 每个页除了存放记录外,还需存储一些额外信息,这些额外信息加起来需要132个字节;
- 每条记录需要的额外信息是27个字节;
这27个字节包括:
-
- 2个字节用于存储真实数据的长度
- 1个字节用于存储列是否是NULL值
- 5个字节大小的头信息
- 6个字节的行ID
- 6个字节的事务ID
- 7个字节的回滚指针
需要满足以下这个式子:
132 + 2 * (27 + n) < 16384
针对上面的varchar_size_demo表,n < 8099,即如果一个列中存储的数据小于8099个字节,那么该列就不会为溢出列,否则为溢出列;
Dynamic和Compressed行格式
InnoDB 1.0.x版本开始引入新的文件格式,以前支持的Compact和Redundant格式称为Antelope文件格式,新的文件格式称为Barracuda文件格式;Barracuda文件格式下拥有两种行记录格式:Compressed和Dynamic(MySQL 5.7版本默认行格式是Dynamic); 新的两种格式对于存放在BLOB中的数据采用了完全的行溢出方式,在数据页只存放20个字节的指针,它们不会在记录的真实数据处存储字段真实数据的前768个字节,而是将所有的字节都存储到其他页面,只在记录的真实数据处存储其他页面的地址,如下图;![](/i/l/?n=22&i=blog/1334716/202208/1334716-20220814001233460-292522467.png)
CHAR的行结构存储
VARCHAR是存储变长长度的字符类型,而CHAR是存储固定长度的字符类型; 注:从MySQL 4.1版本开始,CHAR(M)中的M值得是字符的长度,而不是之前版本的字节长度,即在不同的字符集下,CHAR类型列内部存储的可能不是定长的数据;CREATE TABLE `record_format_demo` (
`c1` varchar(10) DEFAULT NULL,
`c2` varchar(10) NOT NULL,
`c3` char(10) DEFAULT NULL,
`c4` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=ascii ROW_FORMAT=COMPACT;
INSERT INTO record_format_demo(c1, c2, c3, c4) VALUES('aaaa', 'bbb', 'cc', 'd'), ('eeee', 'fff', NULL, NULL);
上面的record_format_demo表中c1,c2,c4列的类型是VARCHAR,而c3列的类型是CHAR的;
在使用ASCII字符集的情况下,这个字符集是一个定长的字符集,即表示一个字符采用一个固定的长度;但是,如果采用多字节的字符集(表示一个字符需要的字节数不确定,如GBK表示一个字符需要1~2个字节,UTF8表示需要1~3个字节) ,因此对于这种多字节的字符编码,CHAR类型不再代表固定长度的字符串;
如果采用多字节的字符集,上面record_format_demo表中的c3列的长度也会被存储到变长字段长度列表;
将c3列的字符集修改如下:
ALTER TABLE record_format_demo MODIFY COLUMN c3 CHAR(10) CHARACTER SET utf8;
![](/i/l/?n=22&i=blog/1334716/202208/1334716-20220814011053144-2095326448.png)