1118 - Row size too large (＞ 8126) 报错 与 ROW_FORMAT 解惑

 

1. 不出意外出的意外（起因）

周末闲来无事准备做下数据分析，翻了一个前期待办的数据出来，数据是小伙伴整理的 SQL 文件。在通过 mysql 运行时 8000+数据执行了 1 行…… @##¥@%¥#%#%……

定下心来喝了秋天的第一杯奶茶，仔细一看 SQL 执行时报了错：

1118 - Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

仔细检查了字段，小伙伴建表的字段全是 Varchar（255），嗯……加起来好像确实让狐头大……

按照错误提示调整了 ROW_FORMAT = COMPRESSED ，重新执行就可以了，后面都顺畅了……

2. ROW_FORMAT 是做什么的呢 ？

MySQL下用的比较多的存储引擎是InnoDB。

InnoDB存储引擎下数据记录的存储格式是——Row Format行格式，所谓Row Format行格式是指数据记录(或者称之为行)在磁盘中的物理存储方式。

2.1 静态表与动态表

若一张表里面不存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫静态表，就是说每条记录所占用的字节一样。其优点读取快，缺点浪费额外一部分空间。

其 row_format是fixed

若一张表里面存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫动态表，就是说每条记录所占用的字节是动态的。其优点节省空间，缺点增加读取的时间开销。

其 row_format是dynamic

2.2 row_format都有什么值

DEFAULT、FIXED、DYNAMIC、COMPRESSED、REDUNDANT、COMPACT

2.3 可设置的默认值

DEFAULT 为指定采用系统的默认模式。

InnoDB表的默认行格式，可以通过innodb_default_row_format变量设置，默认值为DYNAMIC。当建表时没有指定ROW_FORMAT时或者指定ROW_FORMAT=DEFAULT时，就会使用系统默认的行格式。  MySql5.7 之后默认值为Dynamic。

2.4 静态表用静态值

FIXED 为固定模式，既这种模式下 所有字段都是固定长度字段。

2.5 动态表用可变值

在了解剩下的四个可变值（DYNAMIC、COMPRESSED、REDUNDANT、COMPACT）之前，我们先要说下他们解决的问题。

2.5.1 什么是行溢出？

InnoDB存储引擎 可以将一条记录中的某些数据存储在真正的数据页面之外。

MySQL规定一个页中至少需要存放两条记录，而一个数据页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65533个字节，这样就可能出现一个页存放不了一条记录，这种现象称为行溢出。

2.5.2 如何解决行溢出？

解决行溢出的方法在于对页的扩展：在Compact和Reduntant行格式中， 对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据（只存储该字段值前768个字节的数据），把剩余的数据分散存储在几个其他的页中进行分页存储，然后记录的真实数据处用20个字节存储指向这些页的地址（即所谓的溢出页）。DYNAMIC、COMPRESSED则是全部记录的数据存储到溢出页。

2.5.3 Redundant

Redundant作为MySQL 5.0之前使用的一种行格式，其示意图如下所示，

长度偏移列表：存储记录中所有列(包括隐藏列)的长度信息。

2.5.4 Compact

Compact行格式在MySQL 5.0中被引入，其目标是为了更高效的存储数据记录。在该格式下，一条数据记录的组成部分如下所示。其大体分为两部分——记录的额外信息、记录的数据内容。

变长字段的长度列表：由于MySQL支持变长的数据类型(如VARCHAR等)，故对于该类型而言，不仅需要存储该字段的实际数据，还需要额外存储该数据的长度信息(即占用的字节数)。而这就是变长字段的长度列表的用途。

NULL 值标志位：对于记录中的特殊值NULL而言，如果直接存储到记录的数据内容部分则显得比较浪费空间，故这里使用位向量进行存储。

Redundant行格式与Compact行格式主要区别：Redundant拥有记录所有字段长度的 字段长度偏移列表。

2.5.6 Dynamic

新版 MySql 中Dynamic 为默认设置 ，与Compact 存储结构类似，区别在于对于存放在BLOB中的数据采用了完全的行溢出的方式。在数据页中只存放20个字节的指针(溢出页的地址)，实际的数据都存放在Off Page (溢出页)中。而不会像Compact一样在该记录的数据内容的相应字段处存储该字段值前768个字节的数据了。

2.5.7 Compressed

Compressed行记录格式 与Dynamic相同， 但是此模式会对存储在其中的行数据进行zlib的算法压缩，能够更有效的对大长度类型的数据进行的存储。

2.6 如何修改 ROW_FORMAT

修改行格式: ALTER TABLE table_name ROW_FORMAT =DEFAULT

fixed--->dynamic: 这会导致CHAR变成VARCHAR

dynamic--->fixed: 这会导致VARCHAR变成CHAR

3. 与 ROW_FORMAT相关的优化

优化和升级的意义就是为了更合理更高效。但部分小伙伴在进行调整时，可能会遇到Compressed不生效的情况，这时需要检查 mysql 的版本 及其他参数设置 。Compressed 需要对 mysql 进行其他参数的配置才能更好的发挥作用。

3.1 优化存储空间

设置独立表空间，修改Innodb_File_Per_Table 参数。

vi /etc/my.cnf 中添加

innodb_file_per_table = ON

Innodb存储引擎可将所有数据存放于ibdata*的共享表空间，也可将每张表存放于独立的.ibd文件的独立表空间。共享表空间以及独立表空间都是针对数据的存储方式而言的。

共享表空间：某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在data目录下。 默认的文件名为:ibdata1  初始化为10M。

• 优点：可以将表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同的文件上）。数据和文件放在一起方便管理。
• 缺点：所有的数据和索引存放到一个文件中，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。

独立表空间：每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。 其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。

• 优点：每个表都有自已独立的表空间。每个表的数据和索引都会存在自已的表空间中。可以实现单表在不同的数据库中移动。空间可以回收（除drop table操作处，表空不能自已回收）
• 缺点：单表增加过大，如超过100个G。

3.2 优化innodb字符集

vi /etc/my.cnf 添加

innodb_file_format = Barracuda

mysql innodb 表要压缩，字符集需要调整为 Barracuda ，mysql 早期版本默认为 antelope ，此模式下压缩设置不会生效。

压缩模式需要生效，需先设置字符集给事为 innodb_file_format=barracuda，ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;(默认的key_block_size=16)。

根据经验，一般压缩比例可以达到30%-40%

有时需要与早期版本的InnoDB不兼容的数据文件格式来支持新功能。 为了帮助管理升级和降级情况的兼容性，以及运行不同版本MySQL的系统，InnoDB使用命名文件格式。 InnoDB目前支持两种命名文件格式，Antelope和Barracuda。

• Antelope是原始的InnoDB文件格式，以前没有名称。 它支持InnoDB表的COMPACT和REDUNDANT行格式。

• Barracuda是新的文件格式。 它支持所有InnoDB行格式，包括较新的COMPRESSED和DYNAMIC行格式。 与COMPRESSED和DYNAMIC行格式相关的功能，包括压缩表，页外列的高效存储以及多3072字节的索引键前缀（innodb_large_prefix）。

3.3 启用表压缩生效

ALTER TABLE $TABLE

ENGINE=InnoDB

ROW_FORMAT=COMPRESSED

KEY_BLOCK_SIZE=8;(默认的key_block_size=16)

或者建表 

create table t(id int,a varchar(10)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED  

注意： 

在innodb_file_format=antelope的情况下，建立压缩表（表结构中带有row_format=compressed），然后在设置innodb_file_format=barracuda ，此时建立的压缩表会忽略压缩参数

4. 参考文档

MySQL :: MySQL 5.7 Reference Manual :: 14.10 InnoDB File-Format Management