1 行记录格式的分类和介绍
在早期的InnoDB版本中,由于文件格式只有一种,因此不需要为此文件格式命名。随着InnoDB引擎的发展,开发出了不兼容早期版本的新文件格式,用于支持新的功能。为了在升级和降级情况下帮助管理系统的兼容性,以及运行不同的MySQL版本,InnoDB开始使用命名的文件格式。官网关于InnoDB 行格式介绍
1. Antelope: 先前未命名的,原始的InnoDB文件格式。它支持两种行格式:COMPACT 和 REDUNDANT。MySQL5.6的默认文件格式。可以与早期的版本保持最大的兼容性。不支持 Barracuda 文件格式。
2. Barracuda: 新的文件格式。它支持InnoDB的所有行格式,包括新的行格式:COMPRESSED 和 DYNAMIC。与这两个新的行格式相关的功能包括:InnoDB表的压缩,长列数据的页外存储和索引建前缀最大长度为3072字节。
在 msyql 5.7.9 及以后版本,默认行格式由innodb_default_row_format
变量决定,它的默认值是DYNAMIC
,也可以在 create table 的时候指定ROW_FORMAT=DYNAMIC
。用户可以通过命令 SHOW TABLE STATUS LIKE'table_name'
来查看当前表使用的行格式,其中 row_format 列表示当前所使用的行记录结构类型。
PS:如果要修改现有表的行模式为compressed
或dynamic
,必须先将文件格式设置成Barracuda:set global innodb_file_format=Barracuda;
,再用ALTER TABLE tablename ROW_FORMAT=COMPRESSED;
去修改才能生效。
mysql> show variables like "innodb_file_format";
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+
1 row in set (0.00 sec)
mysql> show table status like "test%"\G
*************************** 1. row ***************************
Name: test
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 4
Avg_row_length: 20
Data_length: 80
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2018-08-07 13:07:59
Update_time: 2018-08-07 13:08:01
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: row_format=DYNAMIC
Comment:
2 InnoDB行存储
一个表的行格式决定了它的行是物理存储,进而会影响查询和DML操作的性能。随着越来越多行适合单个磁盘页面,查询和索引查找可以更快,更少的缓存内存缓冲池中是必需的,和更少的I / O要求写出更新的值。
每个表中的数据分为页面。每个表的页面被安排在一个叫做b -树索引树的数据结构。表数据和二级索引都使用这种类型的结构。代表整个的b -树索引表被称为聚集索引,这是组织根据主键列。节点的聚集索引数据结构包含所有列的值的行。二级索引结构的节点包含索引列的值和主键列。
可变长度列是一个例外,列值存储在b -树索引节点。可变长度列b - tree页面太长,以适应分别存储在磁盘页称为溢出页分配。这样的列被称为线下列。线下列的值存储在单链表溢出页,每个这样的列都有自己的一个或多个溢出列表页面。根据列的长度,或者一个前缀的可变长度列值存储在b -树,以避免浪费存储和读取一个单独的页面。
InnoDB存储引擎支持四行格式:REDUNDANT
, COMPACT
, DYNAMIC
和 COMPRESSED
行记录格式表
Row Format | Compact Storage Characteristics | Enhanced Variable-Length Column Storage | Large Index Key Prefix Support | Compression Support | Supported Tablespace Types | Required File Format |
---|---|---|---|---|---|---|
REDUNDANT | No | No | No | No | system, file-per-table, general | Antelope or Barracuda |
COMPACT | Yes | No | No | No | system, file-per-table, general | Antelope or Barracuda |
DYNAMIC | Yes | Yes | Yes | No | system, file-per-table, general | Barracuda |
COMPRESSED | Yes | Yes | Yes | Yes | file-per-table, general | Barracuda |
1.REDUNDANT
(1)B-tree存储变长列(VARCHAR, VARBINARY, BLOB, TEXT)的前768字节,剩下的部分存储在溢出页中。
(2)固定长度列,超过768字节的视为变长列。
特点:
(1)每个索引记录包括6字节头,头部用于将连续的记录连接在一起,并用于锁定行。
(2)聚集索引中的记录,包括用户定义的全部列。另外,有一个6字节的事务ID和7字节的滚动指针。
(3)如果表中没有定义一个主键,记录也会包含一个6字节的行ID。
(4)每个二级索引记录包含主键列。
(5)每条记录的字段对应一个指针,如果字段的长度少于128字节,指针则是一个字节,否则就是两个字节。指针数组称为记录目录。指针指向的区域为记录的数据部分。
(6)固定长度的列,CHAR(10),以固定长度格式存储。VARCHAR的结尾空格不会截断。
(7)固定长度列大于768字节的,以变长列编码。
(8)SQl-null值存储一个或者两个字节在记录目录中,若为变长数据区域无值。若为固定长度,数据区域存储固定长度,值为null。为NULL保留固定长度允许列由null更新为non-null,避免索引页碎片。
2.COMPACT
compact格式比redundant存储空间减少20%。如果受限于cache命中和磁盘速度,compact格式会快一些,若受限于CPU速度,compact格式会慢一些。
compact格式存储变长列的前768字节于B-tree节点中,剩余部分存储在溢出页中。固定长度大于768字节的以变长列方式处理。
特点:
(1)每个索引记录包含5个字节头部,头部之前可能会有一个变长的头。头部用于链接连续的记录以及行级锁定。
(2)变长记录的头部包含bit向量标志NULL列。若列中可以为NULL的列数量为N,bit向量占CEILING(N/8)字节。头部变长部分也包含变长列的长度,视列最大长度而定,占1个或2字节。若全部列不能为null且有固定长度,记录头部则无变长部分。
(3)对于每个non-null变长字段,记录头部包含一个字节或两个字节长度,存储列长度。若列存储于溢出页或者最大列长度超过255字节,而且实际长度超过127字节时需要两个字节。对于外部存储的列,2字节长度表示内部存储列部分的长度加上指向外部存储的20字节指针。内部存储部分768字节,所以长度为768+20。20字节指针存储列的真实长度。
(4)聚集索引中的记录包括全部用户定义的列。另外,还有一个6字节的事务ID和7字节滚动指针。
(5)如果表内没有定义主键,聚集索引记录则会包含一个6字节的行ID。
(6)每个二级索引记录包含所有主键列。如果有变长的主键列,二级索引记录的头部有一个变长部分记录它们的长度,尽管二级索引定义在固定长度列上。
(7)内部,固定长度字符列以固定长度存储。对于VARCAHR尾部空格部位截断;
(8)对于变长字符,InnoDB尝试用N字节存储Char(N),包括去除尾部空格;若字符字节数超过N,最小字符长度为结尾空格剔除后的值,最大为字符最大字节长度*N。
3.DYNAMIC
基于compact格式,提高存储容量,支持大索引(large index)3072字节,由innodb_large_prefix参数控制。
(1) 行格式为dynamic时,变长列为完全页外存储,聚集索引记录包含一个20字节的指针指向溢出页。固定长度列,超过768字节时,以变长列方式存储。
(2) 列是否存储在页外时,依赖页大小和行总大小。
当一行太长时,将选择最长的列作为页外存储,直到聚集索引记录适合于B-tree页面。TEXT或者BLOB列小于40字节的存储一行。
(3) 可采用系统表空间,独立表空间,普通表空间。
4.COMPRESSED
基于dynamic格式,支持表和索引数据压缩。compressed行格式采用dynamic相同的页外存储细节,和额外的需要压缩的表和索引数据存储,更小的页大小。KEY_BLOCK_SIZE参数控制由多少列数据存储在聚集索引,多少存储在溢出页。
3 行格式详解
3.1 Compact 和 Redundant
(一)Compact
Compact行记录是在MySQL5.0中引入的,为了高效的存储数据,简单的说,就是为了让一个页(Page)存放的行数据越多,这样性能就越高。行记录格式如下:
1. 变长字段长度列表:变长字段长度最大不超过2字节(MySQL数据库varcahr类型的最大长度限制为65535)
2. NULL标识位:该位指示了该行数据中是否有NULL值,有则用1。
3. 记录头信息:固定占用5字节(40位)
4. 列N数据:实际存储每列的数据,NULL不占该部分任何空间,即NULL占有NULL标志位,实际存储不占任何空间。
PS:每一行数据除了用户定义的例外,还有两个隐藏列,事物ID列和回滚指针列,分别位6字节和7字节的大小,若InnoDB表没有定义主键,每行还未增加一个6字节的rowid列。
(二)Redundant
MySQL5.0之前的行记录格式:
1. 字段偏移列表:同样是按照列的顺序逆序放置的,若列的长度小于255字节,用1字节表示,若大于255字节,用2字节表示。
2. 记录头信息:占用6字节(48位)
(三)行溢出数据
1. 当行记录的长度
没有超过行记录最大长度
时,所有数据
都会存储在当前页。
2. 当行记录的长度
超过行记录最大长度
时,变长列(variable-length column
)会选择外部溢出页(overflow page
,一般是Uncompressed BLOB Page
)进行存储。
Compact
+ Redundant
:保留前768Byte
在当前页(B+Tree叶子节点
),其余数据存放在溢出页
。768Byte
后面跟着20Byte
的数据,用来存储指向溢出页的指针。
(四)概述
对于 Compact 和 Redundant 行格式,InnoDB将变长字段(VARCHAR, VARBINARY, BLOB 和 TEXT)的前786字节存储在B+树节点中,其余的数据存放在溢出页(off-page),如下图:
上面所讲的讲的blob或变长大字段类型包括blob,text,varchar,其中varchar列值长度大于某数N时也会存溢出页,在latin1字符集下N值可以这样计算:innodb的块大小默认为16kb,由于innodb存储引擎表为索引组织表,树底层的叶子节点为一双向链表,因此每个页中至少应该有两行记录,这就决定了innodb在存储一行数据的时候不能够超过8k,减去其它列值所占字节数,约等于N。
使用Antelope文件格式,若字段的值小于等于786字节,不需要溢出页,因为字段的值都在B+树节点中,所以会降低I/O操作。这对于相对较短的BLOB字段有效,但可能由于B+树节点存储过多的数据而导致效率低下。
3.2 Compressed 和 Dynamic
InnoDB1.0x开始引入心的文件格式(file format,用户可以理解位新的页格式)——Barracuda(图1),这个新的格式拥有两种新的行记录格式:Compressed和Dynamic。
新的两种记录格式对于存放BLOB中的数据采用了完全的行溢出的方式。如图:
Dynamic行格式,列存储是否放到off-page页,主要取决于行大小,他会把行中最长的一列放到off-page,直到数据页能存放下两行。TEXT或BLOB列<=40bytes时总是存在于数据页。这种方式可以避免compact那样把太多的大列值放到B-tree Node(数据页中只存放20个字节的指针,实际的数据存放在Off Page中,之前的Compact 和 Redundant 两种格式会存放768个字前缀字节)。
Compressed物理结构上与Dynamic类似,Compressed行记录格式的另一个功能就是存储在其中的行数据会以zlib的算法进行压缩,因此对于BLOB、TEXT、VARCHAR这类大长度数据能够进行有效的存储(减少40%,但对CPU要求更高)。
若有不恰当之处,还望指教,谢谢!