4.1 准备工作
不同的存储引擎一般是由不同的人为实现不同的特性而开发的,真实数据在不同存储引擎中存放的格式一般是不同的。
4.2 InnoDB 页简介
将数据划分为若干个页,以页作为磁盘和内存之间交互的基本单位,InnoDB 中页的大小一般为 16KB。也就是在一般情况下,一次最少从磁盘中读取 16KB 的内容到内存中,一次最少把内存中的 16KB 内容刷新到磁盘中。
4.3 InnoDB 行格式
InnoDB 的 4 种不同类型的行格式:Compact、Redundant、Dynamic 和 Compressed。
查看表的行格式:
SHOW TABLE STATUS LIKE 'table_name';
返回的结果中,ROW_format就是行格式。
不同版本的MySQL的InnoDB支持的行格式:
- MySQL 5.0及更早版本:只支持
REDUNDANT
和COMPACT
行格式。 - MySQL 5.1:引入了
COMPRESSED
和DYNAMIC
行格式,但需要启用innodb_file_format
和innodb_file_per_table
配置选项。 - MySQL 5.5及更高版本:默认启用
innodb_file_format
和innodb_file_per_table
,因此默认支持COMPRESSED
和DYNAMIC
行格式。 - MySQL 5.7:引入了
Barracuda
文件格式,该格式支持COMPRESSED
和DYNAMIC
行格式。此外,innodb_large_prefix
配置选项默认启用,允许在COMPRESSED
和DYNAMIC
行格式的表中使用更长的索引键。 - MySQL 8.0:移除了
REDUNDANT
和COMPACT
行格式,所有新表默认使用DYNAMIC
行格式。此外,innodb_large_prefix
配置选项已被移除,因为所有行格式都支持长索引键。
4.3.1 指定行格式的语法
# CREATE TABLE 表名(列的信息) ROW_FORMAT=行格式名称
# ALTER TABLE 表名 ROW_FORMAT=行格式名称
CREATE TABLE record_format_demo (
c1 VARCHAR ( 10 ),
c2 VARCHAR ( 10 ) NOT NULL,
c3 CHAR ( 10 ),
c4 VARCHAR ( 10 )
) CHARSET=ascii ROW_FORMAT=COMPACT;
INSERT INTO record_format_demo ( c1, c2, c3, c4 )
VALUES
( 'aaaa', 'bbb', 'cc', 'd' ),
( 'eeee', 'fff', NULL, NULL );
4.3.2 COMPACT行格式
4.3.2.1 记录的额外信息
变长字段长度列表
- 真正的数据内容
- 占用的字节数
在 Compact 行格式中,把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位,从而形成了一个变长字段长度列表,各变长字段数据占用的字节数按照列的顺序逆序存放。
显然这里4,3,1只占用1个字节的大小,但是当varchar字段的长度变大时,所以占用的空间如下:
定义:W=字符集一个字符最多使用的字节数(utf8mb4=>4),M=变长类型的最大字符数(varchar(10)=>10),L=实际存储的字节数。则有
当M * W > 255 && L > 127时,使用2个字节,否则使用1个字节。
另外,变长字段长度列表中只存储值为非NULL的列内容占用的长度,值为NULL的列的长度是不储存的。
NULL 值列表
Compact行格式把值为NULL的列统一管理起来,以节省空间。
- 首先统计表中允许存储 NULL 的列有哪些
- 如果表中没有允许存储 NULL 的列,则 NULL 值列表也不存在了。否则将每个允许存储 NULL 的列对应一个二进制位(1:NULL,0:非NULL),二进制位按照列的顺序逆序排列。
- MySQL 规定 NULL 值列表必须用整数个字节的位表示,如果使用的二进制位个数不是整数个字节,则在字节的高位补 0。
PS:06 = 0x06 = 000000110
记录头信息
记录头信息用于描述记录,由固定的 5 个字节,也就是 40 个二进制位组成。
名称 | 大小(bit) | 描述 |
---|---|---|
预留位1 | 1 | 没有使用 |
预留位2 | 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 | 下一条记录的相对位置 |
4.3.2.2 记录的真实数据
除了自己定义的列的数据以外,MySQL会为每个记录默认添加一些列(也称隐藏列)
列名 | 是否必须 | 大小(byte) | 描述 |
---|---|---|---|
row_id | 否 | 6 | 行 ID,标识唯一一条记录 |
transaction_id | 是 | 6 | 事务 ID |
roll_pointer | 是 | 7 | 回滚指针 |
InnoDB 表对主键的生成策略:用户自定义主键 > 选取一个 Unique 键 > InnoDB 表默认添加一个名为 row_id 的隐藏列。
4.3.2.3 CHAR(M) 列的存储格式
对于CHAR(M)类型的列来说,当列采用的是定长字符集时,该列占用的字节数不会被回到变长字段长度列表,而如果采用变长字符集时,该列占用的字节数也会被加到变长字段长度列表。
4.3.3 REDUNDANT行格式
redundant:冗余的
MySQL5.0之前的格式。
- 字段长度偏移列表
- 记录头信息
- Redundant行格式中NULL值的处理
4.3.4 行溢出数据
4.3.4.1 VARCHAR(M)最多能存储的数据
VARHCAR(M)类型的列最多可以占用65535个字节,但是存储一个VARHCAR(M)需要占用3部分存储空间:
- 真实数据
- 真实数据占用字节的长度
- NULL值标识,如果该列有NOT NULL属性则可以没有这部分存在空间
综上:VARHCAR(M)最多存储65532(可以为NULL)/ 65533(不可为NULL)个字节,再根据使用的字符集计算出最多能存储的字符数。
4.3.4.2 记录中的数据太多产生的溢出
在Compact和Redundant行格式中,对于占用存储空间非常大的列,在记录的真实数据处只会存储该列的一部分数据,把剩余的数据分散存储在几个其他的页中,并用20个字节存储指向这些页的地址。
4.3.4.3 行溢出的临界点
MySQL一页16384(2的14次方)个字节
当n(列数据字节数)满足 136+2*(27+n)>16384时,发生行溢出,此时n=8089。
4.3.5 Dynamic行格式和Compressed行格式
Dynamic行格式和Compressed行格式都与Compact类似。
MySQL 5.7以后默认使用 Dynamic行格式。
Dynamic与Compact只在处理行溢出时有所分歧。Dynamic只记录真实数据的地址,并不会记录前768个字节
而Compressed和Dynamic不同的是,Compressed会对页面压缩以节省空间。
4.4 总结
- 页是MySQL中磁盘和内存交互的基本单位,也是MySQL管理存储空间的基本单位。
- 可以指定和修改行格式。
- InnoDB定义了4种行格式,分别是Compact、Redundant、Dynamic和Compressed。
- 一个页一般是16KB,当记录太大时,会发生行溢出。