MySQL——MySQ行记录大小超过限制(Row size too large (＞ 8126). Changing some columns to TEXT or BLOB or ......)

 

问题背景

问题出现在Spark写MySQL的场景：要写入MySQL的DataFrame中有90多个列，其中有10多个列为字符串类型，且长度较长(大于1000)；对应的要写入的MySQL表使用的是InnoDB引擎，这些较大的字符串所对应的列在MySQL中设置为text类型。

最终在写MySQL的时候，出现这样的报错：

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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.

MySQL版本

本人使用的MySQL版本为5.6、InnoDB引擎，以下内容也是以这个前提来展开的，MyISAM暂不做介绍。

注：

• MySQL 5.6版本默认InnoDB文件格式为Antelope，相应配置innodb_file_format=Antelope，此种文件格式不支持COMPRESSED和DYNAMIC的行格式。
• MySQL 5.7版本默认InnoDB文件格式为Barracuda，相应配置innodb_file_format=Barracuda，此种文件格式支持COMPRESSED和DYNAMIC的行格式。

名词术语

要理解上面报错的本质，首先要了解以下概念：

1. MySQL中的可变长度类型
2. 页(page)、页大小(page size)、off-page column、overflow page？
3. 页（Page）、行大小、行格式三种之间的关系

 

1. 可变长度类型

MySQL中的可变长度类型：VARCHAR、VARBINARY、BLOB和TEXT类型。

InnoDB将长度大于等于768字节的fixed-length字段当作可变长度字段，可以存储在off-page。

 

2. 页(page)、页大小(Page size)、off-page column、overflow page

i. 页(Page)

page代表InnoDB每次在磁盘和内存之间传输多少数据的一个单元。一个page可以包含一行或多行数据，这主要取决于每行数据的大小。如果一行记录不能全部放入到一个page中，InnoDB会用一个指针来引用这行数据。

可以使用COMPRESSED格式来使每个page容纳更多的数据。对于blob或者text类型的字段，COMPACT格式允许大长度的列和其他列分开存储，以便减少查询时的I/O负载和内存占用。

当InnoDB以批处理的方式读写一组page以增加I/O吞吐量时，它会一次读写一个区段的page。

ii. 页大小(Page size)

在MySQL 5.6版本之前，每个InnoDB page的大小都是固定的16KB，这是一个各方面取舍平衡的值：16KB能足以容纳大多数的行数据，同时也足够小到可以最小化将不必要的数据传输到内存的性能开销。

从MySQL 5.6开始，InnoDB page的大小可以是4KB、8KB或16KB，可通过innodb_page_size配置进行设置。在MySQL5.7.6中，InnoDB支持更大的page size(32KB和64KB)，但是这两种page size并不支持ROW_FORMAT=COMPRESSED， 并且最大记录大小为16KB。

iii. off-page column

一个可变长度列(比如BLOB和VARCHAR)中的数据因为太大而不能放入一个B-tree page中，那么数据就会存储在overflow pages中。

iiii. overflow page

专门分配的磁盘pages，用来存储那些因为数据太长而不能放入B-tree page的可变长度列，这些可变长度列就是上面提到的off-page column。

3. 行格式

表的行格式决定了表中行是如何在物理层面上被存储的，这反过来又会影响增删查改操作的性能。当越多的行能被存储在单个page中时，那查询操作和索引的查找都会更高效，buffer pool就需要越少的缓存，更新操作就需要越少的I/O。

每个表中的数据都是被划分为很多个page的，这些page都是保存在B-tree这种数据结构中的，表中的数据和二级索引都是使用的这种数据结构。

长度较长的可变长度列由于无法存储到单个B-tree page中，只能存储到单独分配的磁盘页(overflow pagess)上。这些列也被称为off-page column。off-page columns的值存储在overflow pages的单链表中，而且每一列都有自己的列表，从这个列表中可以知道这一列的值都存储在哪些overflow page中。根据列长度的不同，会将变长列的全部值或前缀存储在B-tree中，这样就能避免page的浪费，也避免了要读取多个page的情况。

MySQL中常用的InnoDB引擎支持4中行格式：

1. REDUNDANT
2. COMPACT
3. DYNAMIC
4. COMPRESSED

更多关于InnoDB Row Formats的细节，参考这里。

4. 页（Page）、行大小、行格式三种之间的关系

MySQL表中行的最大长度被限制为65535字节，即使使用的存储引擎能够支持更大的行，也不能超过这个限制。

表中行的最大长度略少于数据库page大小的一半，例如，对于默认的InnoDB page大小16KB，所对应的行最大长度为略小于8KB，这个值是通过配置项innodb_page_size来设定的。

如果表中一行没有超过半个page的限制，那么整行数据都是存储在page中的；如果超过了半个page大小，那么对于可变长度列，超过限制的数据会被存储在外部off-page storage(就是上面提到的overflow page)。

而可变长度列是如何存储在off-page storage中的，又跟行格式的不同而不同：

• COMPACT 和 REDUNDANT行格式
  在使用这两种行格式的情况下，当一个可变长度列被存储到外部的off-page storage中时，InnoDB引擎会把这一列的前768个字节存储在page中，剩下的数据存储在overflow pages中。每一个存储在overflow pages中的可变长度列都有一个自己的overflow pages列表。这768个字节中，有20字节用来存储这个列的真实长度和指向包含指向overflow list的指针。
• DYNAMIC和COMPRESSED行格式
  在使用这两种行格式的情况下，当一个可变长度列被存储到外部的off-page storage中时，InnoDB引擎会在page中存储一个20字节的指针，列中的剩余数据会全部存储到overflow pages中。

解决方案

在使用InnoDB建表时，默认的行格式为COMPACT（可通过show variables like "table_name"查看），这种行格式对应的默认page大小为16KB，那么相应每行的大小不能超过8KB。如果表中有20个列都为text类型，而且每个text类型列的值都超过了768字节，那么20 * 768字节=15360字节=15KB远大于8KB，所以必然会报错！
那么解决这个问题的方法就是修改行格式，以下是启用DYNAMIC行格式的步骤：

1. 首先是MySQL配置文件my.cnf中添加两个配置项：

   innodb_file_per_table=1 //
   innodb_file_format = Barracuda //DYNAMIC行格式只有在Barracuda文件格式下才支持
   

2. 修改表行格式ROW_FORMAT

   alter table table_name ROW_FORMAT=DYNAMIC;
   

修改之后，执行 show table status like ‘table_name’，可以看到Row_format这一列对应的值已经变成了dynamic，再写入数据的时候就不会报错了。

 

参考

1. https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.7/en/column-count-limit.html
2. https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html
3. https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_dynamic_row_format