MySQL Innodb行格式详解

2024-11-2 22:43| 发布者: 8b79| 查看: 131| 评论: 0

摘要: 目录1、指定行格式的语法2、COMPACT行格式2.1 变长字段长度列表2.2 NULL值列表2.3 记载头信息2.4 记载真实数据2.5 分析Compact行记载的内部结构3、Dynamic和Compressed行格式3.1 行溢出3.2、Dynamic和Compressed行格

1、指定行格式的语法

在创建或修改表的语句中指定行格式：

[code]CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称 ALTER TABLE 表名 ROW_FORMAT=行格式名称[/code] [code]CREATE TABLE record_test_table ( col1 VARCHAR(8), col2 VARCHAR(8) NOT NULL, col3 CHAR(8), col4 VARCHAR(8) ) CHARSET = ascii ROW_FORMAT = COMPACT; INSERT INTO record_test_table(col1, col2, col3, col4) VALUES ('zhangsan', 'lisi', 'wangwu', 'songhk'), ('tong', 'chen', NULL, NULL);[/code]

2、COMPACT行格式

COMPACT行格式，一条完整的记载实在可以被分为记载的额外信息和记载的真实数据两大部分

2.1 变长字段长度列表

MySQL支持一些变长的数据范例，好比VARCHAR(M)、VARBINARY(M)、TEXT范例，BLOB范例，这些数据范例修饰列称为变长字段，变长字段中存储多少字节的数据不是固定的，以是我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。

在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记载的开头部位，从而形成一个变长字段长度列表。

变长字段长度列表现意图

2.2 NULL值列表

Compact行格式会把可以为NULL的列统一管理起来，存在一个标记为NULL值列表中。如果表中没有允许存储 NULL 的列，则 NULL值列表也不存在了。

为什么定义NULL值列表？

之以是要存储NULL是由于数据都是需要对齐的，如果没有标注出来NULL值的位置，就有可能在查询数据的时候出现混乱。如果使用一个特定的符号放到相应的数据位表现空置的话，固然能到达效果，但是这样很浪费空间，以是直接就在行数据得头部开辟出一块空间专门用来记载该行数据哪些黑白空数据，哪些是空数据。

格式如下：

1.二进制位的值为1时，代表该列的值为NULL。

2.二进制位的值为0时，代表该列的值不为NULL。

例如：字段 a、b、c，此中a是主键，在某一行中存储的数依次是 a=1、b=null、c=2。那么Compact行格式中的NULL值列表中存储：01。第一个0表现c不为null，第二个1表现b是null。这里之以是没有a是由于数据库会主动跳过主键，由于主键肯定黑白NULL且唯一的，在NULL值列表的数据中就会主动跳过主键。

record_test_table的两条记载的NULL值列表就如下：

第一条记载

第二条记载

2.3 记载头信息

见 Inonodb页 4、User Records (用户记载)

2.4 记载真实数据

记载的真实数据除了我们自己定义的列的数据以外，还会有三个隐蔽列

实际上这几个列的真正名称实在是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。

一个表没有手动定义主键，则会选取一个Unique键作为主键，如果连Unique键都没有定义的话，则会为表默认添加一个名为row_id的隐蔽列作为主键。

2.5 分析Compact行记载的内部结构

1：创建表

[code]CREATE TABLE mytest ( col1 VARCHAR(10), col2 VARCHAR(10), col3 CHAR(10), col4 VARCHAR(10) ) ENGINE = INNODB CHARSET = latin1 ROW_FORMAT = COMPACT;[/code]

2：插入数据

[code]INSERT INTO mytest VALUES ('a', 'bb', 'bb', 'ccc'); INSERT INTO mytest VALUES ('d', 'ee', 'ee', 'fff'); INSERT INTO mytest VALUES ('d', NULL, NULL, 'fff');[/code]

表空间下文件mytest.ibd这个二进制文件。内容如下

分析：

1、该行记载从0000c070开始
2、03 02 01：变长字段长度列表，逆序
3、00：*NULL标记位，第一行没有NULL值
4、00 00 10 00 2c：Record Header，固定5字节长度
5、00 00 00 2b 68 00：RowID InnoDB主动创建，6字节
6、00 00 00 00 06 05：TransactionID
7、80 00 00 00 32 01 10：Roll Pointer
8、61：列1数据'a'
9、62 62：列2数据'bb'
10、62 62 20 20 20 20 20 20 20 20：列3数据'bb'
11、63 63 63：列4数据'ccc'
注意1：InnoDB每行有隐蔽列TransactionID和Roll Pointer。
注意2：固定长度CHAR字段在未能完全占用其长度空间时，会用0x20来举行添补。
Record Header的最后两个字节，这两个字节代表next_recorder，0x2c代表下一个记载的偏移量，即当前记载的位置加上偏移量0x2c就是下条记载的起始位置。
第二行将不做整理，除了RowID不同外，它和第一行大同小异，现在来分析有NULL值的第三行
03 01：变长字段长度列表，逆序
06：NULL标记位，第三行有NULL值
00 00 20 ff 98：Record Header
00 00 00 2b 68 02：RowID
00 00 00 00 06 07：TransactionID
80 00 00 00 32 01 10：Roll Pointer
64：列1数据'd'
66 66 66：*列4数据'fff'
第三行有NULL值，因此NULL标记位不再是00而是06，转换成二进制为00000110，为1的值代表第2列和第3列的数据为NULL。在厥后存储列数据的部分，用户会发现没有存储NULL列，而只存储了第1列和第4列非NULL的值。因此这个例子很好地阐明白：不管是CHAR范例还是VARCHAR范例，在compact格式下NULL值都不占用任何存储空间。

3、Dynamic和Compressed行格式

3.1 行溢出

很多DBA喜欢MySQL数据库提供的VARCHAR(M)范例，认为可以存放65535字节。这是真的吗？如果我们使用 ascii字符集的话，一个字符就代表一个字节，我们看看VARCHAR(65535)是否可用。

验证VARCHAR(M)范例，是否可以存放65535字节

[code]CREATE TABLE varchar_size_demo ( c VARCHAR(65535) ) CHARSET = ascii ROW_FORMAT = COMPACT;[/code]

结果如下：

[code]ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs[/code]

报错信息表达的意思是：MySQL对一条记载占用的最大存储空间是有限制的，除BLOB大概TEXT范例的列之外，其他所有的列（不包括隐蔽列和记载头信息）占用的字节长度加起来不能超过65535个字节。这个65535个字节除了列自己的数据之外，还包括一些其他的数据。

如果该VARCHAR范例的列没有NOT NULL属性，那最多只能存储65532个字节的数据，由于变长字段的长度占用 2个字节，NULL值标识需要占用1个字节。

[code]CREATE TABLE varchar_size_demo ( c VARCHAR(65532) ) CHARSET = ascii ROW_FORMAT = COMPACT;[/code]

如果有not null属性，那么就不需要NULL值标识，也就可以多存储一个字节，即65533个字节

[code]CREATE TABLE varchar_size_demo ( c VARCHAR(65533) NOT NULL ) CHARSET = ascii ROW_FORMAT = COMPACT;[/code]

通过上面的案例，我们可以知道一个页的巨细一样平常是16KB，也就是16384字节，而一个VARCHAR(M)范例的列就最多可以存储65533个字节，这样就可能出现一个页存放不了一条记载，这种现象称为行溢出。

在Compact和Reduntant行格式中，对于占用存储空间非常大的列，在记载的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中举行分页存储，然后记载的真实数据处用20个字节存储指向这些页的地址。

3.2、Dynamic和Compressed行格式

在MySQL 8.0中，默认行格式就是Dynamic。Dynamic、Compressed行格式和Compact行格式挺像，只不外在处置惩罚行溢出数据时有分歧。

Compressed和Dynamic两种记载格式对于存放在BLOB中的数据采用了完全的行溢出的方式。如图，在数据页中只存放20个字节的指针（溢出页的地址），实际的数据都存放在Off Page（溢出页）中。

Compact和Redundant两种格式会在记载的真实数据处存储一部分数据（存放768个前缀字节）。

Compressed行记载格式的另一个功能就是，存储在此中的行数据会以zlib的算法举行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度范例的数据可以或许举行非常有效的存储。

4、Redundant行格式

Redundant是MySQL 5.0版本之前InnoDB的行记载存储方式，MySQL 5.0支持Redundant是为了兼容之前版本的页格式。

把表record_test_table的行格式修改为Redundant

[code]ALTER TABLE record_test_table ROW_FORMAT=Redundant;[/code]

从上图可以看到，不同于Compact行记载格式，Redundant行格式的首部是一个字段长度偏移列表，同样是按照列的次序逆序放置的。

4.1 字段长度偏移列表

注意Compact行格式的开头是变长字段长度列表，而Redundant行格式的开头是字段长度偏移列表，与变长字段长度列表有两处不同：

①少了“变长”两个字：Redundant行格式会把该条记载中所有列（包括隐蔽列）的长度信息都按照逆序存储到字段长度偏移列表。

②多了“偏移”两个字：这意味着盘算列值长度的方式不像Compact行格式那么直观，它是采用两个相邻数值的差值来盘算各个列值的长度。

4.2 记载头信息（record header）

不同于Compact行格式，Redundant行格式中的记载头信息固定占用6个字节（48位），每位的寄义见下表。

与Compact行格式的记载头信息对比来看，有两处不同：
1.Redundant行格式多了n_field和1byte_offs_flag这两个属性。
2.Redundant行格式没有record_type这个属性。

4.3 Redundant行格式中NULL值的处置惩罚

由于Redundant行格式并没有NULL值列表，以是Redundant行格式在字段长度偏移列表中的各个列对应的偏移量处做了一些特殊处置惩罚 —— 将列对应的偏移量值的第一个比特位作为是否为NULL的依据，该比特位也可以被称之为NULL比特位。也就是说在分析一条记载的某个列时，首先看一下该列对应的偏移量的NULL比特位是不是为1。如果为1，那么该列的值就是NULL，否则不是NULL。

到此这篇关于MySQL Innodb行格式的文章就先容到这了,更多相干MySQL Innodb行格式内容请搜索脚本之家从前的文章或继续欣赏下面的相干文章盼望各人以后多多支持脚本之家！

来源：https://www.jb51.net/database/327263w1q.htm
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

收藏分享邀请

上一篇：MySQL中EXPLAIN的/基本使用及字段详解下一篇：MySQL中字段的现实长度的实现示例代码

账号		自动登录	找回密码
密码			立即注册