器→工具, 工具软件, 术→技巧, 研发

MySQL InnoDB行记录格式

钱魏Way · · 175 次浏览

在早期的InnoDB版本中,由于文件格式只有一种,因此不需要为此文件格式命名。随着InnoDB引擎的发展,开发出了不兼容早期版本的新文件格式,用于支持新的功能。为了在升级和降级情况下帮助管理系统的兼容性,以及运行不同的MySQL版本,InnoDB开始使用命名的文件格式。

  • Antelope: 先前未命名的,原始的InnoDB文件格式。它支持两种行格式:COMPACT 和 REDUNDANT。6的默认文件格式。可以与早期的版本保持最大的兼容性。不支持 Barracuda 文件格式。
  • Barracuda: 新的文件格式。它支持InnoDB的所有行格式,包括新的行格式:COMPRESSED 和 DYNAMIC。与这两个新的行格式相关的功能包括:InnoDB表的压缩,长列数据的页外存储和索引建前缀最大长度为3072字节。

ROW_FORMAT值:

ROW_FORMAT 支持索引前缀 独立表空间压缩 系统表空间压缩
COMPRESSED 3072字节 支持 不支持
DYNAMIC 3072字节 不支持 不支持
COMPACT 768字节 不支持 支持
REDUNDANT 768字节 不支持 支持

在 msyql 5.7.9 及以后版本,默认行格式由innodb_default_row_format变量决定,它的默认值是DYNAMIC,也可以在 create table 的时候指定ROW_FORMAT=DYNAMIC。用户可以通过命令 SHOW TABLE STATUS LIKE ‘table_name’ 来查看当前表使用的行格式,其中 row_format 列表示当前所使用的行记录结构类型。

如果要修改现有表的行模式为compressed或dynamic,必须先将文件格式设置成Barracuda:set global innodb_file_format=Barracuda;,再用ALTER TABLE tablename ROW_FORMAT=COMPRESSED;去修改才能生效。

InnoDB行存储

InnoDB存储引擎和大多数数据库一样(如Oracle和Microsoft SQL Server数据库),记录是以行的形式存储的。这意味着页中保存着表中一行行的数据。这些页以树形结构组织,这颗树称为B树索引。表中数据和辅助索引都是使用B树结构。维护表中所有数据的这颗B树索引称为聚簇索引,通过主键来组织的。聚簇索引的叶子节点包含行中所有字段的值,辅助索引的叶子节点包含索引列和主键列。

行记录最大长度

  • 页大小(page size)为4KB、8KB、16KB和32KB时,行记录最大长度(maximum row length)应该略小于页大小的一半
    • 默认页大小为16KB,行记录最大长度应该略小于8KB ,因此一个B+Tree叶子节点最少有2个行记录
  • 页大小为64KB时,行记录最大长度略小于16KB

CHAR(N)与VARCHAR(N)

N指的是字符长度,而不是Byte大小,在不同的编码下,同样的字符会占用不同的空间,如LATIN1(定长编码)和UTF8(变长编码)

变长列

在InnoDB中,变长列(variable-length column)可能是以下几种情况

  • 长度不固定的数据类型,例如VARCHAR、VARBINARY、BLOB、TEXT等
  • 对于长度固定的数据类型,如CHAR,如果实际存储占用的空间大于768Byte,InnoDB会将其视为变长列
  • 变长编码下的CHAR

行溢出

当行记录的长度没有超过行记录最大长度时,所有数据都会存储在当前页

当行记录的长度超过行记录最大长度时,变长列(variable-length column)会选择外部溢出页(overflow page,一般是Uncompressed BLOB Page)进行存储

  • Compact + Redundant:保留前768Byte在当前页(B+Tree叶子节点),其余数据存放在溢出页。768Byte后面跟着20Byte的数据,用来存储指向溢出页的指针
  • Dynamic + Compressed:仅存储20Byte数据,存储指向溢出页的指针,这时比Compact和Redundant更高效,因为一个B+Tree叶子节点能存放更多的行记录

Redundant

MySQL 5.0之前的ROW_FORMAT

格式

字段偏移列表 记录头信息 ROWID Transaction ID Roll Pointer 列1 列n

字段偏移列表

  • 按照列的顺序逆序放置
  • 列长度小于255Byte,用1Byte存储
  • 列长度大于255Byte,用2Byte存储

记录头信息

名称 大小(bit) 描述
() 1 未知
() 1 未知
deleted_flag 1 该行是否已被删除
min_rec_flag 1 如果该行记录是预定义为最小的记录,为1
n_owned 4 该记录拥有的记录数,用于Slot
heap_no 13 索引堆中该条记录的索引号
n_fields 10 记录中列的数量,一行最多支持1023列
1byte_offs_flag 1 偏移列表的单位为1Byte还是2Byte
next_record 16 页中下一条记录的相对位置
Total 48(6Byte) nothing

隐藏列

  • ROWID:没有显式定义主键或唯一非NULL的索引时,InnoDB会自动创建6Byte的ROWID
  • Transaction ID:事务ID
  • Roll Pointer:回滚指针列

Compact

MySQL 5.0引入,MySQL 5.1默认ROW_FORMAT

对比Redundant

  • 减少了大约20%的空间
  • 在某些操作下会增加CPU的占用
  • 在典型的应用场景下,比Redundant快

格式

变长字段长度列表 NULL标志位 记录头信息 ROWID Transaction ID Roll Pointer 列1 列n

变长字段长度列表

  • 条件
    • VARCHAR、BLOB等
    • 变长编码(如UTF8)下的CHAR
  • 放置排序:逆序
  • 用2Byte存储的情况:需要用溢出页;最大长度超过255Byte;实际长度超过127Byte

NULL标志位

  • 行记录中是否有NULL值,是一个位向量(Bit Vector)
  • 可为NULL的列数量为N,则该标志位占用的CEILING(N/8)Byte
  • 列为NULL时不占用实际空间

记录头信息

名称 大小(bit) 描述
() 1 未知
() 1 未知
deleted_flag 1 该行是否已被删除
min_rec_flag 1 如果该行记录是预定义为最小的记录,为1
n_owned 4 该记录拥有的记录数,用于Slot
heap_no 13 索引堆中该条记录的索引号
record_type 3 记录类型,000(普通),001(B+Tree节点指针),010(Infimum),011(Supremum)
next_record 16 页中下一条记录的相对位置
Total 40(5Byte) nothing

Dynamic

MySQL版本是5.7,它的默认⾏格式就是Dynamic。

dynamic行格式,列存储是否放到off-page页,主要取决于行大小,它会把行中最长的那一列放到off-page,直到数据页能存放下两行。TEXT/BLOB列 <=40 bytes 时总是存放于数据页。这种方式可以避免compact那样把太多的大列值放到 B-tree Node,因为dynamic格式认为,只要大列值有部分数据放在off-page,那把整个值放入都放入off-page更有效。

Compressed

compressed 物理结构上与dynamic类似,但是对表的数据行使用zlib算法进行了压缩存储。在long blob列类型比较多的情况下用,可以降低off-page的使用,减少存储空间(一般40%左右),但要求更高的CPU,buffer pool里面可能会同时存储数据的压缩版和非压缩版,所以也多占用部分内存。

发表评论

邮箱地址不会被公开。 必填项已用*标注