MySQL InnoDB 行记录格式(ROW_FORMAT)

一、行记录格式的分类和介绍

在早期的InnoDB版本中,由于文件格式只有一种,因此不需要为此文件格式命名。随着InnoDB引擎的发展,开发出了不兼容早期版本的新文件格式,用于支持新的功能。为了在升级和降级情况下帮助管理系统的兼容性,以及运行不同的MySQL版本,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 列表示当前所使用的行记录结构类型。

一行记录可以以不同的格式存在InnoDB中,行格式分别是Compact、Redundant、Dynamic和Compressed行格式。

我们可以在创建或修改表的语句中指定行格式:

CREATE TABLE 表名(列的信息) ROW_FORMAT=行格式名称
ALTER TABLE 表名 ROW_FORMAT=行格式名称

  

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

 

二、Compact 行格式

 

 

 MySQL InnoDB 行记录格式(ROW_FORMAT)

 

 

 

记录的额外信息:

  变长字段长度列表:

    PS:变长字段:VARCHAR(N),VARBINARY(N),Text, Blob类型。

    在COMPACT行格式中,所有变长字段的真实数据占用的字节数,都按照列的顺序逆序存放在记录的开头位置,如果没有非NULL变长字段,则省略这部分。

    需要注意的是:

      a. 这部分是以逆序的方式记录长度的(涉及大端、小端存储问题),如03 02 01代表的是第一个变长字段长度为1,第二个变长字段长度为2,第三个变长字段长度为3.至于变长字段怎么确定的序列,是根据创建的先后来规定的,即第一个创建的变长字段为第一个变长字段。

      b. Innodb 在读取记录的变长字段长度列表时,先查看表结构,如果某个变长字段允许最大字节数不大于255,可以认为只用1字节来表示真实数据占用的字节数。

      c. 假设某个字符集中最多需要W个字节表示一个字符,该列允许存储最多M个字符,实际占用字节数为L,如果该变长字段允许存储的最大字节数(M * W)超过255个字节,并且真实占用的字节数L 超过127个字节,则使用2 字节来表示真实数据占用的字节数,否则用一个字节。

      d. 如果采用变长编码字符集例如GBK,则CHAR(M)列所占用字节数也会被记录到变长字段长度列表。

 

  NULL 标志位:

  至少一个字节,表示该行是否有 NULL 值,1表示null,0表示不为null NULL标志位只标识没有声明not null的列

  处理过程:

    1. 统计表中允许存储NULL的列有哪些

    2. 允许存储NULL的列对应一个二进制位,二进制位按照列逆序排放,二进制位为1则代表该列值为NULL,为0则非NULL

    3. MYSQL规定NULL值列表必须用整数字节的位表示,如果使用的二进制位个数不是整数个字节,则在字节的高位补0

  例:表有三列,a有值,b NULL,c NULL , 则正向为0 1 1   逆向则为 1 1 0 ,高位补0 则为 00000110 NULL标志位存储结果为十六进制 0X06

 

  记录头信息:

  由五个字节组成,也就是40个二进制位,

名称 大小(位) 描述
预留位1 1 没有使用
预留位2 1 没有使用
deleted_flag 1 标志该记录是被删除
min_rec_flag 1 B+树每层非叶子节点中最小的目录项记录会添加此标记
n_owned 4 一个页面中的记录会被分为若干组(槽),每个组中有一个带头大哥,其余记录为小弟,带头大哥记录的n_owned则代表该组中所有的记录条数,小弟则为0
heap_no 13 表示当前记录在页面堆中的相对位置
record_type 3 表示当前记录的类型,0为普通记录,1为B+树非叶子节点的目录项记录,2表示Infimum记录,3表示Supremum记录
next_record 16 表示下一条记录的相对位置

 隐藏列:

  记录的真实数据除了我们自己定义的列的数据以外,还会有三个隐藏列(DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR)

列名 是否必须  占用空间  描述
DB_ROW_ID 6字节 行ID 唯一标识一条记录
DB_TRX_ID 6字节 事务ID
DB_ROLL_PTR 7字节 回滚指针

 

  优先使用用户自定义主键为逐渐,如果没有定义主键,则会选取一个Unique键作为主键,如果连Unique键都没有定义的话,则会为表默
认添加一个名为row_id的隐藏列作为主键。所以row_id是在没有自定义主键以及Unique键的情况下才会存在
的。这也是InnoDB可以为每个表创建B+Tree的原因。

 

三、行溢出数据(溢出列)

  什么是行溢出?

  由于 MySQL 中以页为基本单位来管理储存空间的,所有的记录都会被分配到页中。
  由于一个页一般为 16KB (16384 个字节),而一个 VERCHAR 最多可以存储 65532 个字节,所以会出现一个页存放不下一条记录的情况,造成行溢出。
  不仅是 VERCHAR,BLOB 和 TEXT 也会发生行溢出

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

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

  Compact + Redundant:保留前768Byte在当前页(B+Tree叶子节点),其余数据存放在溢出页768Byte后面跟着20Byte的数据,用来存储指向溢出页的指针。

 

四、DYNAMIC行格式和COMPRESSED行格式

  MYSQL5.7默认为DYNAMIC,与Compact 的区别在于不会在真实记录出存储溢出列真实数据的前768个字节,只在存储真实记录处存储20字节大小的指向溢出页的地址

  MySQL 5.7版本默认使用Dynamic格式,这两种记录格式与Compact一致,而在Compact的基础上,对行溢出时做了改进。即对于存放BLOB、大varchar等导致行溢出的字段时,采取的是完全行溢出方式。对溢出字段不再在B-tree Node页中存放768个前缀字节,而是只保存20个字节的Uncompressed BLOB Page的偏移量,所有的数据均保存在Uncompressed BLOB Page页中。

  此外compressed存储行数据会以zlib的算法进行压缩,因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据都能够进行有效的存储。

 

 

MySQL InnoDB 行记录格式(ROW_FORMAT)

上一篇:rails 给数据库表里加入索引


下一篇:MSSQL·ORDER BY 1 DESC是什么写法?