MySQL Innodb Engine -- 文件格式(innodb_file_format)

在InnoDB 1.0.x版本之前,InnoDB 存储引擎提供了 Compact 和 Redundant 两种格式来存放行记录数据,Redundant格式是为兼容之前版本而保留的,而Compact行格式在MySQL 5.0中引入,在 MySQL 5.6 版本中,默认设置为 Compact 行格式。

在compact行格式中:
1.使用5个字节来存放记录头信息(record header);
2.使用null位图来记录列中为null的列;
3.对于变长字段列,如果列长度小于255字节,则需要额外的1个字节来记录长度,如果列长度大于255个字节,使用2字节来记录列长度;
4.对于blob/text/varchar等大字段列,只存放768个字节在数据页中,而剩余的数据存储到溢出端(blob page)中

 

在compact行格式下,如果记录行包含较多的大字段列,即使每个大字段列只存放768个字节在数据页中,也会导致整个记录行的长度超过8KB,而默认数据页16k,每个数据页至少存放两行记录,因此导致下面错误:
Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

除innodb_file_format参数会导出上面错误外,参数max_allowed_packet和innodb_log_file_size设置过小也会触发上面问题:

max_allowed_packet: 服务器发送和接受的最大包长度,当单行数据较大时,需要调整该参数。
innodb_log_file_size: 该参数确定数据日志文件的大小,更大的设置可以提高性能,但也会增加恢复故障数据库所需的时间。
innodb_log_buffer_size: 该参数确定日志文件所用的内存大小,设置时用M单位进行设置。缓冲区更大能提高性能,但意外的故障将会丢失数据。MySQL开发人员建议设置为1~8M之间的值。
innodb_log_file_size参数在MySQL 5.6/5.7/8.0的默认值为48MB, 当插入记录总长度超过48M时,就会提示Row size too large(无论innodb_file_format)

Compact和redumdant的区别在行记录的首部不同,compact存储格式的首部为一个非NULL的变长字段长度列表,而redundant存储格式的首部是一个字段长度偏移列表。

对于Redundant格式,哪怕只占用769个字节,也会将多余的1个字节存储到额外的page中。

 

在InnoDB 1.0.X版本中开始引入新的文件格式Barracuda,新文件格式有两种新的行记录格式:Compressed 和 Dynamic。新的两种行记录格式采用完全溢出的方式来存放blob/text数据列,仅在数据页中保留20字节的指针,blob列的数据完全存放在Off page中。

off page将数据完全存储到一个独立的data page中(这些页是独享的,不会与同一行其他列或其他行的列共用),对于平均长度较小的blob/text数据列,会造成严重的磁盘空间浪费和IO资源消耗。

对于Compressed行记录格式,会将行数据以zlib的算法来进行数据压缩,因此可对于BLOB、TEXT、VARCHAR 这类大长度类型的数据能够进行非常有效的存储。

如果表采用Compressed行记录格式,可以有效降低IO开销,但是会增加CPU和内存的开销,在缓冲池中需要保留两份SIZE不同但是数据一致的数据页。

PS: 使用Compressed行记录格式的不仅仅对大长度类型的数据有效,对长度较小的变长列也有效果。但总的说来,压缩效率很一般。

 

compact行格式相对redumdant更节省存储空间,Compressed行格式相对于compact更节省存储空间,但强烈不建议使用Compressed行格式,会验证影响TPS。

## 查看默认的行格式
show variables like 'innodb_file_format';

##============================##
## 查看表的行格式
show table status like 'table_name'G

innodb_page_size参数在MySQL 5.6版本中引入,用来修改每个数据页的默认大小,默认值为16KB。在MySQL 5.6之前版本需要修改源代码才能修改数据页大小。

对于InnoDB存储引擎,数据页的填充率为15/16,预留出1/16的空间用来避免后续数据插入修改而导致页拆分的情况。当数据页的填充率小于1/2时,InnoDB会进行收缩,释放空闲空间。

 

原文地址:https://www.cnblogs.com/gaogao67/p/10480110.html