MySQL的一个表最多可以有多少个字段

问题由来

创建如下表，提示

[Err] 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

如果将下面表中的varchar（200），修改成text（或blob）：报错变为另一个：

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.

查阅了很多的资料，不确定The maximum row size到底是65535 还是8126？原理是什么？

三种报错的疑惑

整理了一下，其实类似的错误有三种：

• 错误1 创建表报maximum row size > 65535

[Err] 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

• 错误2 创建表报Row size too large (> 8126)

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.

• 错误3 表创建成功但是插入报 Row size too large (> 8126)

ERROR 1118 (42000): 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.

错误1：

这个报错其实我们查询MySQL官方手册就可以查询到， 对于一行记录最大的限制是65535字节。至于为什么是65535，手册也没说:)----一行数据里面字段长度定义有64k

错误2：

有了65535的限制以后还有一个8126的限制是为什么列？

MySQL是分两层的，MySQL Server层 + 存储引擎层。
第2个问题其实是MySQL除了在Server层做了一次限制还会在Innodb存储引擎层在做一次限制。

innodb为了保证B+TREE是一个平衡树结构，强制要求一条记录的大小不能超过一个页大小的一半。这也就是我们上面看到的第二个错误。

下面是innodb B+树的结构，我们可以想象一下二分查找时，一个页的只有一条数据会是什么样子？

每个页只有一条数据的查找就变成了链表查找了。这样就没有二分查找的意义了。
而MySQL中默认的页大小是16K，16K的一半是8196字节减去一些元数据信息就得出了8126这个数字。
这就是8126的由来

错误3：

突破错误2

8126是不是不能突破的列？

我们这里就有个案例：按照附1的建表语句建立一个150个字段，每个字段是100个字符（特地使用了ASCII字符集，这样一个字符就是一个字节）的表。

150 * 100=15000 > 8126。按照上面的说法，应该要报错的，
但是各位可以在自己的数据库上试一下，表能够建立成功，这是为什么列？

其实MySQL在计算字段长度的时候并不是按照字段的全部长度来记的。
列字段小于40个字节的都会按实际字节计算，如果大于20 * 2=40 字节就只会按40字节。

对应到MySQL代码中storage/innobase/dict/dict0dict.cc的dict_index_too_big_for_tree()中:

         field_max_size = dict_col_get_max_size(col);
         field_ext_max_size = field_max_size < 256 ? 1 : 2;


         if (field->prefix_len) {
             if (field->prefix_len < field_max_size) {
                 field_max_size = field->prefix_len;
             }
         } else if (field_max_size > BTR_EXTERN_FIELD_REF_SIZE * 2
              ¦ && dict_index_is_clust(new_index)) {


             /* In the worst case, we have a locally stored
             column of BTR_EXTERN_FIELD_REF_SIZE * 2 bytes.
             The length can be stored in one byte.  If the
             column were stored externally, the lengths in
             the clustered index page would be
             BTR_EXTERN_FIELD_REF_SIZE and 2. */
             field_max_size = BTR_EXTERN_FIELD_REF_SIZE * 2;
             field_ext_max_size = 1;
         }

也就是说，如果字段长度超过BTR_EXTERN_FIELD_REF_SIZE * 2，字段就只算20 * 2=40(BTR_EXTERN_FIELD_REF_SIZE=20)
举例如下:

• 创建一个300个字段长度类型为varchar(30)的表，在创建时不会创建成功。因为varchar(30)没有超过20 * 2，那么总长度就是300 * 30=9000 > 8126就会创建失败。
• 创建一个150个字段长度类型为varchar(100)的表可以创建成功。因为varchar(100) 大于了202那么就只会按40计算 总长度就是150 * 20 * 2=6000 < 8126 就会创建成功。
• 这里需要说明，这里的规则是针对创建表时的。

这个20字节是不是看着有点眼熟，可以联系到InnoDB的一个参数：innodb_file_format。该参数用于设置Innodb表内部存储的文件格式，该参数可设置为Antelope,Barracuda两种格式。

• Antelope是MySQL原始的记录格式，是较古老的记录格式。
  在这种格式记录下Innodb 对于大字段的处理如下:

对于大字段，innodb只会存放前DICT_ANTELOPE_MAX_INDEX_COL_LEN（768）字节在数据页中，超过768字节都会放到溢出页中。这种方式也是B+TREE结构，但是也并不是完美的，因为我们将大字段存放到了数据页中会造成叶子节点的个数会很多，同样会造成非叶子节点的的个数增加。最终导致索引层级增高，访问IO次数增加。

• Barracuda格式是InnoDB新的存储格式
  他的存储方式如下:
  
  在Barracuda格式下，会用20字节的指针指向溢出页，这样做的好处就是不会造成索引层级的增高。

回到错误3

回归正题，第二个错误我们可以越过去，但是我们是不是能够真的插入150个100字符的字段列。
用附3的插入语句试一下就知道，错误3也会报错出来。
也就是说表可以创建成功但是插入却失败，原因如下：

• Antelope格式下的COMPACT大字段按照DICT_ANTELOPE_MAX_INDEX_COL_LEN（768）字节溢出页。varchar(100)没有存储为溢出页。
• Barracuda的DYNAMIC和COMPRESSED格式下只有长字段才会用20字节溢出页的方式，varchar(100)也没有存储为溢出页(原因下面会解释)。

引用reference的原文如下：

When a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, long variable-length column values (for VARBINARY,VARCHAR, BLOB, and TEXT columns) are stored fully off-page, and the clustered index record contains only a 20-byte pointer to the overflow page. InnoDB will also store long CHAR column values off-page if the column value is greater than or equal to 768 bytes, which can occur when the maximum byte length of the character set is greater than 3, as it is with utf8mb4, for example.

Whether any columns are stored off-page depends on the page size and the total size of the row. When the row is too long, InnoDBchooses the longest columns for off-page storage until the clustered index record fits on the B-tree page. TEXT and BLOB columns that are less than or equal to 40 bytes are always stored in-line.

文档大概意思是在ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED模式下，变长字段(for VARBINARY,VARCHAR, BLOB, and TEXT columns)都会按溢出页处理，并且聚集索引中只存储20个字节的指针，指向溢出页。
至于何时存储存储为溢出页呢，文档中说TEXT和BLOB字段小于或者等于40个字节都会存储在行内，如果大于40就会放在溢出页。
这里会有个疑问，这里指的小于等于40个字节是否包括VARCHAR类型呢？

在storage/innobase/data/data0data.cc文件 , dtuple_convert_big_rec函数中,针对溢出页处理判断如下:

/* In DYNAMIC and COMPRESSED format, store
      locally any non-BLOB columns whose maximum
      length does not exceed 256 bytes.  This is
      because there is no room for the "external
      storage" flag when the maximum length is 255
      bytes or less. This restriction trivially
      holds in REDUNDANT and COMPACT format, because
      there we always store locally columns whose
      length is up to local_len == 788 bytes.
      @see rec_init_offsets_comp_ordinary */
      if (!DATA_BIG_COL(ifield->col)) {
        goto skip_field;
      }

注释大概意思是，如果不是BLOB这种大字段，长度也没有超过255，就会存储在本地(也就没有溢出页)
DATA_BIG_COL宏定义如下:

/* For checking if mtype is BLOB or GEOMETRY, since we use BLOB as
the underling datatype of GEOMETRY(not DATA_POINT) data. */
#define DATA_LARGE_MTYPE(mtype)                         \
  ((mtype) == DATA_BLOB || (mtype) == DATA_VAR_POINT || \
   (mtype) == DATA_GEOMETRY)

/* For checking if data type is big length data type. */
#define DATA_BIG_LEN_MTYPE(len, mtype) ((len) > 255 || DATA_LARGE_MTYPE(mtype))

/* For checking if the column is a big length column. */
#define DATA_BIG_COL(col) DATA_BIG_LEN_MTYPE((col)->len, (col)->mtype)

• 这里可以做个试验

实验1:

创建一个表50个字段varchar(255):

1、因为字段类型varchar(255)超过了40字节，所以会按50*40计算，表可以创建成功

2、插入时，由于长度没有超过255字节，所以会存储在本地，50 * 255 超过了8126所以会报错

实验2:

创建一个表50个字段varchar(256):

1、因为字段类型varchar(256)超过了40字节，所以会按50*40计算，表可以创建成功

2、插入时，由于长度超过255字节，所以会按溢出页处理，50 * 20 没有超过8126所以不会报错

总结

• MySQL Server最多只允许4096个字段

• InnoDB 最多只能有1000个字段

• 字段长度加起来如果超过65535，MySQL server层就会拒绝创建表

• 字段长度加起来（根据溢出页指针来计算字段长度，大于40的，溢出，只算40个字节）如果超过8126，InnoDB拒绝创建表

• 表结构中根据ROW_FORMAT的存储格式确定行内部保留的字节数（20 VS 768），最终确定一行数据是否小于8126，如果大于8126，报错。

• 如果是非大字段类型(例如varchar)，没有超过255字节，不会按溢出页处理，存储在本地

建议

1、放弃使用Antelope这种古老的存储格式吧，原因上面也说到了把大字段的前768字节放在数据页中，这样会导致索引的层级很高，会直接影响到查询的性能。

2、对于大字段类型建议单独存放到一张表中，不要与经常访问的表放在一起，会造成物理IO的增加。

参考

• MySQL reference: Limits on Table Column Count and Row Size

• MySQL reference: innodb row format dynamic

• mysqlserverteam:Externally Stored Fields in InnoDB

• MySQL · 引擎特性 · InnoDB 文件系统之文件物理结构

为防止原文失效，转载此贴。原文链接：https://www.modb.pro/db/100458