概叙

科普文：软件架构数据库系列之【详解MySQL索引：innodb索引高度和表的容量限制】-CSDN博客

科普文：软件架构数据库系列之【详解MySQL索引】-CSDN博客

影响行记录的因素

MySQL表的大小受多种因素影响，包括但不限于：

1. 数据类型：不同数据类型占用的存储空间不同（例如，INT占用4字节，VARCHAR占用实际字符长度+1或2字节）。
2. 表的结构：包括列的数量、数据类型、索引等。
3. 数据的填充率：空值和重复值会使表变大。
4. 行格式和压缩ROW_FORMAT：MySQL可以使用多种行格式row_format，如Compact, Redundant, Dynamic, Compressed。
5. 存储引擎：不同存储引擎如InnoDB, MyISAM, Memory等对表的存储方式不同。
6. 数据库的配置：如字符集选择会影响存储空间。

前面文章估算innodb表容量时，可以看到行记录在ibd数据文件中存储时，是有格式要求的，不同的ROW_FORMAT，行记录存储不一样。

ROW_FORMAT基本操作

在MySQL中，所谓Row Format行格式是指数据记录(或者称之为行)在磁盘中的物理存储方式。具体地，对于InnoDB存储引擎而言，常见的行格式类型有Compact、Redundant、Dynamic和Compressed。

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;去修改才能生效。

InnoDB的ROW_FORMAT决定了表的存储格式和行的格式。主要有以下几种类型：

1. COMPACT

2. REDUNDANT

3. DYNAMIC

4. COMPRESSED

5. REDUNDANT（已废弃）

fixed：默认格式，

1. 当表不包含变长字段(varchar / varbinary / blob / text)时使用，
2. 每行都是固定的，所以很容易获取行在页上的具体位置，存取效率比较高，
3. 但是占用磁盘空间较大

dynamic：

1. 每行都有一个行头部，包含bitmap，记录列为空的情况。（字符类型长度为0，或数字类型为0，而不是NULL值）
2. 所有字符串列都是动态存储的，除非长度小于4;
3. fixed->dynamic : 会导致CHAR->VARCHAR，反之亦然， 
4. MYISAM引擎可以修改ROW_FORMAT，InnoDB不可以，默认Compact
5. REDUNDANT为固长，有冗余，COMPACT更灵活

下面分别介绍这些格式的优缺点：

COMPACT

• 优点：每行数据都是紧密存储的，更适合高压缩比的存储和快速访问。

• 缺点：可能会浪费一些空间来存储额外的记录。

• 应用场景：适合存储大量小数据，或者是列经常更改，导致碎片不是问题的情况。

REDUNDANT

• 已废弃，不建议使用。

DYNAMIC

• 优点：动态行格式会根据行数据的实际使用情况来动态调整行的长度，减少了空间使用。

• 缺点：可能会增加读取操作的复杂度，因为需要处理行的内部结构。

• 应用场景：适合存储大量大数据，且数据更新频繁的情况。

COMPRESSED

• 优点：通过zlib压缩算法来减少磁盘空间的使用。

• 缺点：需要额外的解压开销来读取数据。

• 应用场景：适合存储大量不经常访问的数据，或者是需要减少存储空间的场景。

ROW_FORMAT设置、修改行格式信息

在创建、修改数据表的时候，可以显式地指定row format行格式。SQL语句语法如下

-- 创建数据表时,显示指定行格式
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称;
-- 创建数据表时,修改行格式
ALTER TABLE 表名 ROW_FORMAT=行格式名称;

与此同时，如果需要查看某数据表的行格式，可通过如下语句实现

show table status from 数据库名 like '<数据表名>';

ROW_FORMAT演示示例

下面通过一个示例来验证上述语句的使用及效果。在test1数据库中创建一张名为task2的数据表，并指定行格式的类型为compact

use test1;
-- 指定行格式
create table task2
(
    idx int auto_increment,
    primary key (idx)
) row_format = compact;

-- 查看行格式
show table status from test1 like 'task2';

从下图可以看出表的行格式类型被设置为compact

然后再将该表的行格式类型修改为dynamic

-- 修改行格式
alter table task2
    row_format = dynamic;

-- 查看行格式
show table status from test1 like 'task2';

从下图可以看出表的行格式类型已被修改为dynamic

InnoDB行存储

InnoDB表的数据存储在页（page）中，每个页可以存放多条记录。这些页以树形结构组织，这颗树称为B树索引。表中数据和辅助索引都是使用B树结构。维护表中所有数据的这颗B树索引称为聚簇索引，通过主键来组织的。聚簇索引的叶子节点包含行中所有字段的值，辅助索引的叶子节点包含索引列和主键列。

变长字段是个例外，例如对于BLOB和VARCHAR类型的列，当页不能完全容纳此列的数据时，会将此列的数据存放在称为溢出页(overflow page)的单独磁盘页上，称这些列为页外列(off-page column)。这些列的值存储在以单链表形式存在的溢出页列表中，每个列都有自己溢出页列表。某些情况下，为了避免浪费存储空间和消除读取分隔页，列的所有或前缀数据会存储在B+树索引中。

ROW_FORMAT 的影响

不同的 ROW_FORMAT 选项对数据库的性能和存储有以下影响：

1. 存储效率：COMPRESSED 格式可以显著减少数据的存储需求，但可能会增加 CPU 使用率。
2. 查询性能：FIXED 格式适用于固定长度的列，可以提高查询性能，但不适合可变长度的列。
3. 灵活性：DYNAMIC 格式提供了最大的灵活性，可以根据数据类型和大小自动选择最合适的存储格式。

使用 mermaid 语法，我们可以创建一个饼状图来展示不同 ROW_FORMAT 选项的分布情况：

 

了解 MySQL 的 ROW_FORMAT 默认值及其影响对于优化数据库性能和存储至关重要。选择合适的 ROW_FORMAT 可以提高查询效率，减少存储需求，并根据具体需求灵活调整。通过本文的代码示例和图表，希望读者能够更深入地理解 ROW_FORMAT 的作用和选择依据。

在实际应用中，建议根据数据的特点和查询需求，合理选择 ROW_FORMAT。同时，定期监控和评估数据库性能，以确保数据存储和查询的最优化。
 

Compact 行格式

Compact行格式在MySQL 5.0中被引入，其目标是为了更高效的存储数据记录。

在该格式下，一条数据记录的组成部分如下所示。

其大体分为两部分——记录的额外信息、记录的数据内容。后者比较好理解，其即是我们存储到数据库的各列(字段)数据值。而前者则是MySQL为了更好描述该条记录而添加的额外信息

1. 变长字段长度列表：变长字段长度最大不超过2字节（MySQL数据库varcahr类型的最大长度限制为65535）

2. NULL标识位：该位指示了该行数据中是否有NULL值，有则用1。

3. 记录头信息：固定占用5字节（40位）

4. 列N数据：实际存储每列的数据，NULL不占该部分任何空间，即NULL占有NULL标志位，实际存储不占任何空间。

PS：每一行数据除了用户定义的例外，还有两个隐藏列，事物ID列和回滚指针列，分别位6字节和7字节的大小，若InnoDB表没有定义主键，每行还未增加一个6字节的rowid列。

变长字段的长度列表

由于MySQL支持变长的数据类型(如VARCHAR等)，故对于该类型而言，不仅需要存储该字段的实际数据，还需要额外存储该数据的长度信息(即占用的字节数)。而这就是变长字段的长度列表的用途。而一个变长字段的数据长度信息可能会使用1～2个字节来进行表示，具体地，如果某变长类型的字段 允许存储的最大字节数不超过255个字节，显然长度信息只需要一个字节表示即可；而当 某变长类型的字段允许存储的最大字节数超过255个字节时，这个时候就需要分情况讨论了：

• 该字段数据实际使用的字节数不超过127个字节：依然只使用一个字节表示
• 该字段数据实际使用的字节数超过127个字节：使用两个字节表示

对于该变长类型字段其允许存储的最大字节数该如何计算呢？这里我们以常见的VARCHAR(M)类型为例进行说明，我们知道该类型表示最多能存储M个字符。那一个字符占多少个字节呢？这就和我们之前所说的字符集有关了。我们知道字符集的Maxlen列信息，表示该字符集中一个字符最多需要几个字节来表示。比如utf8字符集下Maxlen列值为3、ascii字符集下Maxlen列值为1。故对于VARHCAR(M)来说，M x Maxlen 即为在某字符集下该变长类型字段允许存储的最大字节数

当然有人可能会问？MySQL在读记录的时候，如果发现某字段的M x Maxlen值超过255个字节了，那么它在读取记录的变长字段的长度列表时，是将该字节作为单独的字段长度信息还是作为半个的字段长度信息呢？即是读一个字节还是读两个字节。其实这个问题很简单，因为数值127写成二进制为 0111 1111。所以如果该字节的第一位为0，则该字节就是一个单独的字段长度信息；反之为1，则说明该字段只是半个的字段长度信息。即将字节的第一位作为标志位

Note

1. 变长字段的长度列表不存储值为NULL的长度信息

对于变长类型的字段而言，如果某记录下该字段的值为NULL，则变长字段的长度列表不会存储该字节的信息。因为是特殊值，会通过下面即将说明的NULL标志位进行存储

2. 变长字段的长度列表不是一定存在的

一方面，表中可能没有变长类型的字段；另一方面，如果该记录中所有的变长字段值均为NULL，根据第一点易知，此时变长字段的长度列表同样也没有存在的必要

3. 变长字段的长度列表中各字段长度信息是按列的顺序逆序排列的

这里我们假设有一张ascii字符集的数据表，其各字段依次为vf1,vf2,vf3,f4。其中vf1,vf2,vf3字段均为变长类型的字段。假设有一条记录其在各字段的值依次为："a"，NULL，"ccc"，99。即变长字段vf1的长度为1，变长字段vf3的长度为3。则该记录的变长字段的长度列表内容为：0x03(即十进制的3)0x01(即十进制的1)

4. char类型字段的长度信息是否需要存储在 变长字段的长度列表 中取决于其所使用的字符集是否为变长字符集

MySQL中char(M)类型的字段表示该字段最多可以存储M个字符。对于定长字符集(如ascii字符集)而言，compact行格式下存储该字段所占有的空间固定为M x Maxlen个字节。但是对于变长字符集而言情况就大不一样了，例如utf8字符集，其存储M个字符所需的空间为M~3M个字节。在compact行格式下，char类型字段会由于使用变长字符集而导致其所占的字节数(即长度信息)不定，故此时char类型的数据同样也需要在变长字段的长度列表中存储其长度信息

NULL值标志位

对于记录中的特殊值NULL而言，如果直接存储到记录的数据内容部分则显得比较浪费空间，故这里使用位向量进行存储。如果某字段为主键或被NOT NULL所修饰，则其值自然不允许为NULL。故位向量中不是直接包含了所有字段，而是排除了值不能为NULL的字段，此举进一步减小了占用空间。具体地，位向量中某一位为1，则表明该列值为NULL；否则如果该位值为0，则表明值不为NULL

值得一提的是，首先位向量中代表的列同样是按照列的顺序逆序排列的，其次对于位向量不足一个字节的部分，高位需补0，即按照一字节对齐。假设这里有一张数据表，其字段定义分别为f1,f2,f3,f4。其中f2字段为主键，其余字段均可以为NULL，则有一条记录其值依次为："Amy",1,NULL,2，则该记录的NULL值标志位内容如下所示

Note

1. NULL值标志位不是一定存在的

如果某条记录所有字段均不允许为NULL，则显然用于管理记录字段为NULL值的NULL值标志位在该条记录中是没有必要的，即不存在

记录头信息

记录头信息用于描述该条记录，其固定为5个字节，即40位。其定义如下

• 预留位1、2：暂未使用
• delete_mask：当前记录被删除的标志位
• min_rec_mask：B+树的每层非叶子节点中的最小记录的标志位
• n_owned：当前记录拥有的记录数
• heap_no：当前记录在记录堆中的位置
• record_type：当前记录类型。具体地，0: 普通记录；1: B+树非叶子节点记录（即所谓的目录项记录）；2: 最小记录；3: 最大记录
• next_record：下一条记录的相对位置

记录的数据内容

记录的数据内容，通常来说就是我们日常开发、使用过程中需存储的记录的数据内容。但其实，MySQL对于我们所定义的数据表，还会默认的插入一些其他列(字段)，即所谓的隐藏列。其字段定义说明如下

• DB_ROW_ID：该字段占6个字节，用于标识一条记录
• DB_TRX_ID：该字段占6个字节，其值为事务ID
• DB_ROLL_PTR：该字段占7个字节，其值为回滚指针

上述3个字段，除了DB_ROW_ID字段，其余两个字段均一定会被添加到数据表中的。一般地，当用户未指定数据表的主键时，MySQL会选择非NULL的Unique键作为主键。而如果非NULL的Unique键也没有的话，这个时候MySQL才会添加向数据表添加DB_ROW_ID字段用来作为主键。当然上述隐藏列的3个字段的值是由MySQL自动生成、存储的

Note

1. 值为NULL的记录不会存储于此

记录的数据内容不包括字段值为NULL的数据内容。前文已经提到，对于记录中某字段为NULL值的信息已经体现在NULL值标志位中了，故此处将不再重复存储以节省空间

2. char类型字段的存储方式

值得一提的是，对于变长字符集下的char(M)类型字段而言，出于存储优化的角度考虑，compact行格式要求存储该字段的值时至少占用M个字节，字段值所占字节数不足时则使用 空格字符(空格字符在ascii字符集下为0x20) 填充。比如某字段类型为char(10)，其使用utf8字符集，即使某条记录该字段值为"abc"，只使用了3个字节。但是在存储"abc"时，依然会通过在其后填充若干个空格字符的方式使其达到占用10个字节的空间来进行存储。此举目的其实也很简单，当该条记录下次需要更新时，如果新值使用的空间不超过10个字节时，则可以直接在此条记录处进行更新而无需重新分配一个新的记录空间，从而导致原有的记录空间成为碎片。当然在读值的时候会把填充的空格字符移除掉

Redundant 行格式

Redundant作为MySQL 5.0之前使用的一种行格式，其示意图如下所示，可以看到其与compact行格式在结构上大体还是比较相似的。这里主要来介绍下其与compact行格式不同之处

1. 字段偏移列表：同样是按照列的顺序逆序放置的，若列的长度小于255字节，用1字节表示，若大于255字节，用2字节表示。

2. 记录头信息：占用6字节（48位）

字段长度偏移列表

在Redundant行格式下，其通过字段长度偏移列表存储记录中所有列(包括隐藏列)的长度信息。首先计算记录中各字段的长度信息，然后再顺序计算长度的累计值，最后再按数据表各字段的顺序逆序排列，即为实际存储的字段长度偏移列表。反过来如果期望获取记录中某字段的长度信息，只需计算两个相邻长度累计值的差值即可

具体地，字段长度偏移列表中的各偏移量所占空间要么均为1个字节，要么均为2个字节。那么实际存储记录时，到底是使用1个字节还是使用2个字节来存储字段长度偏移列表呢？其实在该格式下，策略比较简单。若记录的整个数据内容部分(包括隐藏列)所占用的空间不超过127(0x7F)个字节，则该记录的字段长度偏移列表的各偏移量自然只需使用1个字节即可表示，毕竟所有列长度值加起来也不超过127，那么各个列所对应的偏移量自然也不会超过127了；反之如果超过127个字节但不超过32767(0x7FFF)个字节，则会使用2个字节来表示各偏移量。与此同时，该记录会通过记录头信息的1byte_offs_flag属性来表明该记录各偏移量的字节数，以便读取记录的时候方便解析

Note

1. 偏移量包含NULL值标志

一方面，通过对比compact与redundant的结构图，可以发现在compact行格式下其没有NULL值列表；另一方面，偏移量在使用1、2个字节表示时，其能表示的最大值分别为0x7F、0x7FFF，即偏移量的第一位实际上是没有使用的。其实原因就在于，对于各列偏移量的第一位而言，其用于标识该记录下相应列是否为NULL值。如果是NULL值，则该位为1；反之，则为0

记录头信息

记录头信息用于描述该条记录，其固定为6个字节，即48位。其定义如下

• 预留位1、2：暂未使用
• delete_mask：当前记录被删除的标志位
• min_rec_mask：B+树的每层非叶子节点中的最小记录的标志位
• n_owned：当前记录拥有的记录数
• heap_no：当前记录在记录堆中的位置
• n_field：表示记录中列的数量
• 1byte_offs_flag：标识字段长度偏移列表中各列的偏移量使用的字节数。0：意为每个偏移量均使用2个字节表示；1：意为每个偏移量均使用1个字节表示
• next_record：下一条记录的相对位置

记录的数据内容

在redundant行格式下，其与compact行格式一样，同样有隐藏列的情况。这里就不再赘述了。这里我们就不同的地方进行一些介绍。前面我们提到在compact行格式下，对于char(M)类型的字段数据在定长字符集、变长字符集下的存储方式是有差异的，相对而言还是比较复杂；而在redundant行格式下，无论是使用变长字符集还是使用定长字符集，char(M)类型的字段总是占用M x Maxlen个字节的空间，字段值所占空间的字节数不足则同样会使用 空格字符(空格字符在ascii字符集下为0x20) 进行填充

上文提到compact行格式下，字段为NULL值是不会在记录的数据内容中占用存储空间，冗余存储的；而在redundant行格式下，对于字段为NULL值在记录的数据内容部分的存储则略有不同

• 如果值为NULL的字段类型为变长数据类型，其同样不会在记录的数据内容中占用任何空间来进行存储的
• 如果值为NULL的字段类型为定长数据类型，其使用0x00来填充该字段所需占用的空间。例如char(10)类型的字段，在ascii、utf8字符集中其Maxlen值分别为1、3。即该字段在数据内容部分会分别占用的10、30个字节。故当该字段为NULL值时，会使用0x00来填充这10、30个字节的位置

行溢出

众所周知，InnoDB存储引擎中内存与硬盘交互的基本单位是页，一般地页大小为16KB。

MySQL规定一个页中至少需要存放两条记录。

而所谓的行溢出是指：当某个记录的某个字段(varchar、text、blob等类型)的值长度过长、数据量过大，会导致一个页中放不下一条记录，为此在compact、redundant行格式中，如果该记录某字段中数据量过多时，则在该记录的数据内容的相应字段处只存储该字段值前768个字节的数据和一个指向存储剩余数据的其他页（即所谓的溢出页）的地址，该地址通常占用20个字节。

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

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

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

Dynamic、Compressed行格式

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要求更高）。

对于Dynamic、Compressed行格式而言，其和compact行格式比较相似。不同的在于，对待处理行溢出的处理及策略，Dynamic、Compressed行格式会把记录中数据量过大的字段值全部存储到溢出页中，而不会在该记录的数据内容的相应字段处存储该字段值前768个字节的数据了。而compressed相比较dynamic行格式来说，前者会使用压缩算法对所有页面(自然也包括溢出页)进行压缩以减少存储占用。

Compact 和 Redundant 行格式

对于 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+树节点存储过多的数据而导致效率低下。