MySQL行格式原理深度解析

一、前言

MySQL被分为Server层和存储引擎层

• Server层：主要负责接收和处理来自客户端的指令。换句话说，它是MySQL的前端，与用户进行交互，解析SQL语句，制定查询计划等。

• 存储引擎层：当需要执行与数据相关的读取或写入操作时，Server层会调用存储引擎层提供的接口。存储引擎层是真正与数据打交道的地方，它负责数据的存储、检索和管理。

数据的存储：

在MySQL的生态中，存储引擎很多，但除了Memory存储引擎将数据存储在内存中之外，大多数存储引擎（如常用的InnoDB和MyISAM）都是将数据存储在磁盘上的。

这就引出了一个关键问题：数据在磁盘上是以什么格式存储的？因为不同的存储格式会直接影响数据的读取、写入和检索效率。

行格式：当我们向MySQL表中插入数据时，通常是以一条条记录的形式进行的。同样地，MySQL在存储这些数据时，也是以「记录」为单位进行的。

这些记录在磁盘上的存储格式，被称为「行格式」。不同的存储引擎可能会有不同的行格式，这些行格式决定了数据是如何被组织、存储和检索的。

了解这些底层细节对于优化数据库性能、选择合适的存储引擎以及处理大规模数据都是非常有帮助的。

二、MySQL中常见的几种行格式的详细介绍

MySQL 的行格式是指表中每一行的存储格式，包括数据、元数据、版本信息、行头等。不同的存储引擎可以支持不同的行格式，例如 InnoDB 存储引擎支持的行格式有 COMPACT、REDUNDANT 和 DYNAMIC 三种。

1. Redundant 行格式：

• 存储方式： Redundant是MySQL 5.0之前的行格式，现在基本没人用了。它在存储记录时会包含一些额外的信息，如字段长度和NULL值标记。
• 优点： 与旧版本的MySQL兼容。
• 缺点： 存储效率较低，因为包含了额外的信息。
• 适用场景： 主要用于与旧版本的MySQL保持兼容。

2. Compact 行格式

它是MySQL 5.6版本的默认行格式。

数据存储结构包括变长字段列表、NULL列表、记录头信息和列值。

变长字段列表采用1-2个字节表示字段长度。

NULL列表逆序存储，每个字段使用一个位来表示是否为NULL。

记录头信息使用5个字节，包含记录是否被删除、记录类型和下一条记录的相对偏移量。

溢出的数据会被单独放入一个页，并外加20字节存储额外页的信息。

优点： 存储效率较高，因为数据是紧密排列的，没有额外的空间浪费。

缺点： 对于某些类型的数据（如BLOB和TEXT），只存储部分数据，其余数据存储在外部页中，需要通过额外的指针来访问。

适用场景： 适用于大多数常规的数据存储需求。

3. Dynamic 行格式：

• MySQL 5.7版本后默认使用。
• 与Compact行格式相似，但在处理页面溢出时有所不同。
• 长可变长度的列值不会直接存储在数据页中，而是存储在页外，数据页中的索引记录只包含指向这些溢出页的指针。

4. Compressed 行格式：

• 它在Dynamic行格式的基础上使用了压缩算法。
• 压缩可以节省存储空间，但可能会增加CPU的使用率。

不同行格式对数据读写的影响？

行格式对数据读写效率有显著影响：

一些行格式设计得不够紧凑，导致单条记录占用较多磁盘空间，降低了每页的记录密度，进而可能拖慢DML操作的性能，因为查询时需要额外的磁盘IO来获取较少的数据。

另一些行格式采用压缩技术以减少磁盘空间占用，虽然提高了磁盘IO效率，但增加了CPU的解压负担。因此，在选择行格式时，必须权衡存储效率与计算资源消耗，根据实际应用场景做出合理决策。

行格式的选择

在选择行格式时，需要根据具体的存储需求、硬件资源和性能要求来进行权衡。通常，Compact和Dynamic格式是较为常用的选择，而Redundant格式主要用于与旧版本的MySQL保持兼容，Compressed格式则适用于特定的存储和性能要求。

MySQL版本5.7默认使用DYNAMIC行格式。

三、指定和查看行格式

指定行格式

你可以在创建或修改表的时候指定行格式。

创建表时指定行格式：

CREATE TABLE mytable (  
    id INT PRIMARY KEY,  
    name VARCHAR(100),  
    data BLOB  
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

修改表的行格式：

想改变它的行格式，可以使用 ALTER TABLE 命令：

ALTER TABLE mytable ROW_FORMAT=COMPACT;

查看行格式

要查看表的当前行格式，你可以查询 information_schema 数据库中的 TABLES 表：

SELECT TABLE_NAME, ENGINE, ROW_FORMAT  
FROM information_schema.TABLES  
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

我们查看ibd文件中 行内容长这样，具体每列代表什么不用特别关注：

四、DYNAMIC行格式解读

下面我们来详解下DYNAMIC行格式：

1. 变长字段长度列表

• 首部是一个非NULL变长字段长度列表，并且其是按照列的顺序逆序放置的，其长度为：

• 1字节：若列的长度小于255字节
• 2字节：大列的长度大于255字节

• 变长字段的长度最大不可以超过2字节，因为MySQL数据库中varchar类型的最大长度限制为65535

2. NULL标志位

• 该位用来指示该行数据中是否有NULL值，哪一个列的值为NULL，其哪一位的之就为1，反之则为0
• 该部分所占的字节应该为1字节

3. 记录头信息（record header）

• 该部分固定占用5字节（40位），每位的含义如下：
  

4. 隐藏列

• 每行数据除了用户定义的列之外，还包含两个隐藏列：6字节的事务ID列和7字节的回滚指针列。
• 对于NULL值，它只占用NULL标志位，不占用额外的存储空间。

• 此外，如果表没有定义主键，InnoDB还会为每行添加一个6字节的rowid列。这些隐藏列和rowid列对于保证事务的ACID特性和数据的恢复至关重要。

5. 数据字段

之后的部分就是实际存储每个列的数据。

五、InnoDB如何确定VARCHAR字段的实际长度？

在InnoDB的Dynamic行格式下，处理变长数据类型（如VARCHAR、TEXT和BLOB）的需要存储额外的信息来追踪实际数据的长度，因为这些字段中存储的数据字节数是不固定的：

1. 长度信息的存储：

• 长度信息对于变长字段是必要的，因为数据库需要知道每个字段值的开始和结束位置，尤其是在处理多字节字符集（如UTF-8）时，一个字符可能由多个字节组成。
• 存储长度信息有助于数据库正确地解释和重构数据，特别是在进行读取、更新或删除操作时。

2. VARCHAR(M)类型：

• 对于VARCHAR类型，InnoDB会存储一个或两个字节的变长字段长度，这取决于列的最大可能长度M。如果M的值小于或等于255，则使用一个字节来表示长度；如果M的值大于255，则使用两个字节。

• 在Dynamic行格式中，如果VARCHAR字段的实际长度加上长度前缀超过了页内存储的限制，那么该字段的值会被存储在页外，而在页内只保留一个20字节的指针。

3. TEXT和BLOB类型：

• TEXT类型（包括TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT）和BLOB类型（包括TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB）的处理方式与VARCHAR类似，但通常它们被设计为存储更大的数据。
• 对于这些类型，InnoDB同样会存储长度信息，以确定字段的实际长度。不过，与VARCHAR不同，TEXT和BLOB类型通常默认就会存储在页外，因为它们的大小往往超过了页内可以直接存储的限制。

• 在Dynamic行格式中，页内仅包含指向实际数据存储位置的指针，实际数据则存储在单独的溢出页中。

4. 存储效率：

Dynamic行格式通过仅存储变长字段的引用（指针）而不是实际数据，实现了更高效的空间利用。这意味着，无论字段的实际大小如何，固定数量的空间（指针大小）都被用于在B-tree索引页中引用该字段。

这种设计允许数据库在处理包含大量长字段的表时更加灵活，因为它减少了由于单个行过大而导致的页分裂的可能性。

总的来说，在InnoDB的Dynamic行格式下，变长数据类型通过存储长度信息和可能的页外引用，实现了灵活且高效的数据存储。这种格式特别适合于需要存储大量长文本或二进制数据的应用场景。

六、varchar(M) 能存多少个字符，为什么M提示最大16383？

我们来理解为什么VARCHAR(M)中的M表示字符数，但在实际存储时我们需要考虑字节数。这是因为不同的字符集可能需要不同数量的字节来表示一个字符。例如，在utf8mb4字符集中，一个字符可能需要最多4个字节。

现在，我们来看InnoDB是如何处理VARCHAR字段的

首先，我们需要明白几个关键点：

字符与字节：字符是文本的基本单位，而字节是计算机存储的基本单位。一个字符可能由多个字节组成，这取决于使用的字符集。

utf8mb4字符集：在utf8mb4字符集中，一个字符最多可以占用4个字节。这是因为它支持存储从基本多文种平面到其他辅助平面的所有Unicode字符。

InnoDB的记录结构：InnoDB使用一种复杂的记录结构来存储数据，其中包括用于记录每个变长字段长度的空间。这个空间是有限的，最多使用2个字节来记录一个变长字段的长度。

现在，我们来计算为什么VARCHAR(M)在utf8mb4下最大只能存储约16383个字符：

InnoDB使用2个字节来记录变长字段的长度，这意味着它可以记录的最大长度是2^16 - 1，即65535个字节。

在utf8mb4字符集中，一个字符最多可以占用4个字节。

因此，如果我们想要存储尽可能多的字符，我们需要将65535个字节除以4，得到的结果是16383.75。由于字符数必须是整数，所以最大字符数是16383。

然而，这只是一个理论上的最大值。在实际应用中，由于行中还包括其他信息（如变长字段列表、NULL值列表、记录头信息等），实际可存储的字符数可能会更少。

此外，当字段的数据太大时，InnoDB会将其分成多个部分存储，这称为溢出列。这也意味着即使理论上可以存储16383个字符，实际上也可能达不到这个值。

影响每行实际可用空间的因素有哪些？

VARCHAR(16383) 在理论上应该能够存储 16383 个字符，但在实际应用中可能存不到这么多字符，主要是由于以下几个因素影响了每行实际可用空间：

行格式开销：InnoDB 存储引擎使用特定的行格式（如 Dynamic 或 Compact）来组织数据。每种行格式都有其自身的开销，包括用于存储记录头信息、NULL 值列表、变长字段长度列表等的空间。这些额外的信息会占用行的一部分空间，从而减少可用于实际数据的空间。

记录头信息：每行记录都需要一些额外的字节来存储记录头信息，这些信息包括指向行中各个字段的指针以及记录的其他元数据。

NULL 值列表：如果表中的某些列被定义为允许 NULL 值，并且实际上存储了 NULL 值，那么 InnoDB 需要在行中为每个 NULL 列分配一个额外的字节（在某些情况下，多个 NULL 列可能共享相同的字节，但这取决于具体的行格式和列的数量）。

变长字段长度列表：对于变长字段（如 VARCHAR、TEXT、BLOB），InnoDB 需要在行中存储额外的字节来表示每个字段的实际长度。这个长度前缀通常是 1 或 2 个字节，取决于字段的最大可能长度。

页空间限制：InnoDB 的数据是以页（通常为 16KB）为单位存储的。每页除了存储行数据外，还需要留出空间用于存储页的头信息和尾信息。如果一行数据太大以至于无法完整地放入一个页中，那么它会被分割成多个部分，分别存储在不同的页中。这称为行溢出。

多字节字符集：使用多字节字符集（如 utf8mb4）时，每个字符可能需要多达 4 个字节的存储空间。因此，即使 VARCHAR 字段的长度定义为 16383 个字符，实际存储这些字符所需的字节数也可能远远超过这个数值。

其他列的空间占用：如果表中除了这个 VARCHAR 列之外还有其他列，那么这些列也会占用行的一部分空间，从而减少可用于 VARCHAR 列的空间。

内部碎片和空间复用：InnoDB 可能会在行内留出一些空间以便将来的更新操作，这可能导致一些空间的浪费。此外，由于删除和更新操作，页内可能会留下一些未使用的空间，这些空间可能不会被完全利用。

总的来说，虽然VARCHAR(M)的M值可以很大，但由于多种因素（包括字符集、InnoDB的记录结构和行格式等）的限制，实际可存储的字符数可能会远小于M。

六、为什么开发规范中不推荐NULL？

开发规范中不推荐使用NULL，并且倾向于使用NOT NULL约束，这样做有几个好处：

数据完整性：使用NOT NULL可以确保列中始终有值，这有助于维护数据的完整性和一致性。

避免歧义：NULL在SQL中是一个特殊的值，表示“未知”或“不存在”。它不同于空字符串或0，而且在进行比较和计算时，NULL会导致一些意想不到的结果。避免使用NULL可以减少这种歧义。

性能优化：不使用NULL可以简化数据库的内部处理。例如，您提到的NULL值列表就是InnoDB为了跟踪哪些列包含NULL值而维护的额外数据结构。如果表中的所有列都不允许NULL值，那么InnoDB就不需要维护这个列表，从而节省了空间并可能提高性能。

关于NULL值列表的处理过程简要概括一下：

InnoDB首先会确定哪些列允许存储NULL值。这包括那些没有明确设置为NOT NULL的列。

对于允许NULL值的列，InnoDB会为它们分配一个二进制位，这些位组合在一起形成了NULL值列表。这个列表用于跟踪每一行中哪些列的值是NULL。

二进制位的排列是逆序的，这意味着表中的第一列（如果它允许NULL值）将对应于列表中的最后一个二进制位，而最后一列将对应于第一个二进制位。

当二进制位的值为1时，表示对应的列值为NULL；当值为0时，表示对应的列值不为NULL。

通过这种方式，InnoDB能够高效地存储和检索NULL值信息，尽管这会增加一些额外的存储开销。但是，如果表中的所有列都设置为NOT NULL，那么这个NULL值列表就完全不需要了，从而节省了这部分空间。这也是为什么开发规范中经常推荐尽量避免使用NULL的原因之一。

七、行溢出

InnoDB 行溢出是 InnoDB 存储引擎处理行数据过大，无法完全存储在一个数据页（通常是 16KB）中的一种机制。当一行数据的大小超过页内剩余可用空间时，InnoDB 会使用行溢出技术来存储这部分超出的数据。

首先，需要明确的是，InnoDB 的数据是以页为单位进行存储的。每个页除了存储行数据外，还需要存储页的头信息、行目录、最小记录、尾信息等。因此，每个页内实际可用于存储行数据的空间是小于页的大小的。

当一行数据的大小超过了这个可用空间时，InnoDB 会采取以下步骤来处理行溢出：

1. 存储部分数据： InnoDB 首先会尝试将行数据的前 768 字节（这个数字可能会因 InnoDB 的版本和配置而有所不同）存储在数据页内。这部分数据被称为行的“前缀”或“固定部分”。
2. 使用溢出页： 超出的数据部分会被存储在另外的页中，这些页被称为“溢出页”。InnoDB 会在原始数据页内的行记录中保存一个指向溢出页的指针。溢出页中存储了剩余的数据，可以通过数据页内的指针来访问。
3. 
   
4. 动态行格式： 使用行溢出的记录通常会采用动态行格式（DYNAMIC）。在这种格式下，记录的头信息中会包含指向溢出页的指针以及溢出数据的长度等信息。
   

现在，关于 BLOB 和 VARCHAR 列的数据存储，有几点需要澄清：

BLOB 列： 虽然 BLOB 列通常用于存储大量的二进制数据，但并不意味着 BLOB 列的数据一定会被存储在溢出页中。如果 BLOB 列的数据量很小，并且行的总大小没有超过数据页内的可用空间，那么这些数据仍然会被存储在原始数据页内。

VARCHAR 列： VARCHAR 列虽然通常用于存储较短的字符串，但在某些情况下，如果 VARCHAR 列的数据非常长，并且导致行的总大小超过了数据页内的可用空间，那么 VARCHAR 列的数据也可能被存储在溢出页中。

性能考虑：

• 行溢出可能会影响性能，因为读取溢出行需要额外的磁盘 I/O 操作。然而，InnoDB 通过一些优化策略（如将溢出页尽可能地放置在相邻的物理位置上）来减少这种影响。
• 设计数据库时，应尽量避免频繁的行溢出，例如通过合理地选择数据类型和限制字段长度。

Compressed、Dynamic格式对于存放在BLOB中的数据采用了完全的行溢出方式

如下图所示：

• 数据页中只存放20个字节的指针
• 实际的数据都存放在Off Page中
• Compact和Redundant两种格式会存放768个前缀字节

总结来说，行溢出不仅仅限于 BLOB 或其他大对象数据类型。任何列的数据，如果导致行的大小超过了数据页内的可用空间，都有可能被存储在溢出页中。InnoDB 通过动态调整数据存储方式，确保了即便在数据量很大的情况下，仍然能够有效地存储和检索数据。

八、总结一下

InnoDB行格式是InnoDB存储引擎用来存储表数据的方式。简单来说，InnoDB行格式的原理包括以下几点：

固定与动态存储：InnoDB可以根据列的数据类型和长度，以固定或动态的方式存储数据。对于固定长度的字段，如CHAR类型，InnoDB会预先分配固定长度的存储空间。而对于可变长度的字段，如VARCHAR或BLOB类型，InnoDB则只会使用实际所需的空间来存储数据，这种方式称为动态存储。

行溢出处理：当一行数据的大小超过数据页内可用于存储行数据的空间时，InnoDB会使用行溢出机制。这意味着部分数据会被存储在额外的页中，而在原始数据页内会保留一个指向这些额外数据的指针。

紧凑存储：为了节省存储空间，InnoDB会尽量紧凑地存储数据。例如，对于NULL值的列，InnoDB不会分配实际的存储空间，而是通过特定的标记来表示。

总的来说，InnoDB行格式通过固定与动态存储、行溢出处理和紧凑存储等机制，旨在高效地存储和检索表数据。这些原理保证了InnoDB在处理大量数据时仍然能够保持良好的性能和存储效率。