row size is 8157 which is greater than maximum allowed size (8126) for a record on index leaf page

﹏ヽ暗。殇╰゛Y 2024-03-24 19:44 30阅读 0赞

内容大概是“\[Warning\] InnoDB: Cannot add field \`l10\_\*\*\*\_394\` in table \`tmp\`.\`\#sql\_5498\_1\` because after adding it, the row size is 8157 which is greater than maximum allowed size (8126) for a record on index leaf page.”，大意就是无法在表“tmp”中添加字段‘l10\_\*\*\*\_394’，因为添加字段后，行大小为8157，将大于索引叶页上记录的最大允许大小（8126）。项目使用MySQL数据库，初步判断是EF Core生成了过大的Sql查询语句，于是开始排查解决。

### 一、问题分析 ###

客户的生产环境为专用的内网，很难接触到生产机器和数据，只能先根据异常信息做远程分析和定位，大部分问题可以顺利解决，如果问题能够在开发环境复现，排查起来就很轻松了。

查看表的行格式的类型是什么

SELECT table\_schema,table\_name,engine,row\_format,table\_rows,create\_options FROM information\_schema.tables where table\_schema not in ('mysql','information\_schema','performance\_schema')  
![51b8efec57134e74b6d371dceab034d8.png][]

如果表的行格式为COMPACT的，需要转化为Dynamic;

ALTER TABLE TABLE\_NAME ROW\_FORMAT=DYNAMIC;

## MySQL Row Format详解 ##

MySQL Row Format指的是MySQL中的数据行格式，主要包括Compact、Redundant和Dynamic三种。这三种数据行格式的选择会直接影响着数据库的性能和存储效率。本文将从多个方面对MySQL Row Format进行详细的阐述。

### 一、Compact Row Format ###

MySQL的Compact Row Format是最基本的一种数据行格式，也是默认情况下的数据行格式。它的特点是占用存储空间少，但是插入、更新和删除操作的性能不如其他两种数据行格式。在该格式下，如果要插入、更新或删除一个记录，需要执行以下步骤：

1.  复制一份现有记录的内容到一个新的数据行中
2.  修改该记录的值
3.  从旧的数据行中将这条记录标记为删除

这种方法会导致许多空间浪费，因为删除的记录会占用存储空间，而且读取记录时需要执行一些额外的操作。

### 二、Redundant Row Format ###

Redundant Row Format是一种牺牲存储空间来换取性能的数据行格式。在这种格式下，如果要插入、删除或更新记录，则只需要在原始记录中更新或标记相应的标记位即可。这样做可以大大降低执行插入、更新和删除等操作的时间。这种格式需要更多的存储空间，但是在执行大量写操作的应用程序中往往更加有效。 下面是示例代码： \`\`\`sql ALTER TABLE t1 ROW\_FORMAT=REDUNDANT; \`\`\`

### 三、Dynamic Row Format ###

Dynamic Row Format是一种更智能的数据行格式，它在Compact和Redundant两种格式之间取得了一个平衡。它允许记录的长度可以动态调整，使得记录只占用需要的存储空间，同时可以避免在Compact格式下的空间浪费问题。同时，它也提供了比Redundant格式更好的性能。在大量更新、删除和插入操作的应用程序中，Dynamic格式是最佳选择。 下面是示例代码： \`\`\`sql ALTER TABLE t1 ROW\_FORMAT=DYNAMIC; \`\`\`

### 四、其他注意事项 ###

在使用MySQL Row Format时，需要注意以下几个问题：

#### 1、InnoDB和MyISAM的差异 ####

在默认情况下，InnoDB使用的是Dynamic格式，而MyISAM使用的是Compact格式。因此，在选择Row Format时需要特别注意数据库引擎的类型。如果使用MyISAM引擎，需要手动设置Row Format为Redundant或Dynamic。

#### 2、最佳实践 ####

在选择Row Format时，需要考虑数据表的实际情况。如果数据表经常需要进行大量的插入、更新或删除操作，则可以选择Redundant或Dynamic格式；如果数据表不经常更新，则可以选择Compact格式。

#### 3、兼容性问题 ####

需要注意的是，MySQL Row Format并不是所有MySQL版本都支持的。在选择Row Format时需要特别注意所使用的MySQL版本。

### 结论 ###

通过本文的介绍，相信您对MySQL 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

![format_png][]

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

-- 修改行格式
    alter table task2
        row_format = dynamic;
    
    -- 查看行格式
    show table status from test1 like 'task2';

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

![format_png 1][]

### **Compact 行格式** ###

Compact行格式在MySQL 5.0中被引入，其目标是为了更高效的存储数据记录。在该格式下，一条数据记录的组成部分如下所示。其大体分为两部分——记录的额外信息、记录的数据内容。后者比较好理解，其即是我们存储到数据库的各列(字段)数据值。而前者则是MySQL为了更好描述该条记录而添加的额外信息

![320f2990397382056756f7551e60326d.png][]

### **变长字段的长度列表** ###

由于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值标志位内容如下所示

![0b97020ab7a2a32ba4f3547c7b22ee87.png][]

**Note**

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

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

### **记录头信息** ###

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

![0882c355690db6aa5fd2a57ba3b3c542.png][]

*  **预留位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行格式不同之处

![8af1a0b27633af41f93b8f0e35520f8a.png][]

### **字段长度偏移列表** ###

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

![format_png 2][]

具体地，字段长度偏移列表中的各偏移量所占空间要么均为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位。其定义如下

![format_png 3][]

*  **预留位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个字节

### **Dynamic、Compressed行格式** ###

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

[51b8efec57134e74b6d371dceab034d8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/05488f9672f745fbaad5b58d99358213.png
[format_png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/04bfe980aef34c2e99ac0237af4fc219.png
[format_png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/ed57913c06f74ee4bd2c3b4d1a40ff44.png
[320f2990397382056756f7551e60326d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/8522b111052e4b67b235f31f3d33c3dd.png
[0b97020ab7a2a32ba4f3547c7b22ee87.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/e121fabd6b5f46af886de5fb0ab6d14d.png
[0882c355690db6aa5fd2a57ba3b3c542.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/d6cbfc83e7174c798439a0a5c357eea7.png
[8af1a0b27633af41f93b8f0e35520f8a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/8c0401b9612b47e99b7e547670d7fa23.png
[format_png 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/78af430f8d1646d59179ceb11ebe7bd5.png
[format_png 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/c05a2a78e86d4923add0acdbb5e6b823.png