这500多个字段引起的问题，大部分DBA都搞不定

背景介绍

本人在工作中接触到一个业务，由于需要创建一个非常大的表，字段比较多——超过了500个字段，但是在创建表的时候报了很多错误，让我折腾了很久才解决，于是为了防止问题复现，我决定一探究竟。

注：mysql 版本为5.7.18。

CREATE TABLE `process_xxxx` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `instance_id` varchar(255) NOT NULL,
   ...
   ...
   ...
  `F_202001081110400_959` text,
  `F_202001081110400_965` text,
  `F_202001081110400_991` text,
  `F_202001081110410_397` text,
  `F_202001081110410_847` text,
  `F_202001081110410_910` text,
  `F_202001081110410_934` text,
  `F_202001081110410_961` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `instance_id` (`instance_id`),
  KEY `instance_status` (`instance_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

以上为建表sql，有超过500个字段，省略了部分，在建表的时候，发生下面报错：

[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
1.
2.
3.

于是将表中的varchar，修改成text（或blob），结果报错变为另一个：

Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, 
BLOB prefix of 0 bytes is stored inline.
1.
2.

问题分析

到底是什么原因导致的呢？

我们知道，无论是 MySQL 还是 Oracle，或者是 SQL Server，其实都有这么两层存在，一个是 Server 层，另一个是存储引擎层。

那为什么在用SQL Server或者Oracle的时候几乎没什么接触存储引擎这个概念呢？其实这是因为这两家都是闭源数据库，底层怎么实现的你也不知道，但是装好了就用，不需要去考虑太多的东西。

然而MySQL不一样，MySQL是开源的，开源的东西，人人都可以看源码。只要你实现了那些接口，你就可以接入到 MySQL 中，作为一个存储引擎供 MySQL 的 Server 层使用。

下面是关于最大长度的限制，官方文档相关说明：

    Limits on Table Column Count and Row Size 
    https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html)
1.
2.

针对第一个错误，我们查询MySQL官方手册就可以查询到。

The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes.
1.

那么第二个错误的8126限制又是什么呢？由于innodb为了保证B+TREE是一个平衡树结构，一条记录的长度，不能超过innodb_page_size大小的一半。下面是innodb B+树的结构，我们可以想象一下二分查找时，一个页的只有一条数据会是什么样子。

每个页只有一条数据的查找就变成了链表查找，这样就没有二分查找的意义了。MySQL中默认的页大小是16K，16K的一半是8196字节减去一些元数据信息就得出了8126这个数字。除了长度，对每个表有多少个列的个数也是有限制的，这里简单说一下：

MySQL Server层规定一个表的字段个数最大为 4096；

InnoDB层规定一个表的字段个数最大为1017；

    [官方文档相关说明 - Limits on InnoDB Tables] 
    https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html
1.
2.

至于为什么有这个限制，也不去深究了，因为是代码中写死的。至于原因，猜测和MySQL的定位有关系，MySQL一直定位于OLTP业务，OLTP业务的特点就是短平快，字段数过多或者长度太长，都会影响OLTP业务的TPS。

TEXT类型

回到上面的问题，将varchar改成了text类型，为什么第一个错误就不报了呢？

    TEXT 字段介绍
    官方文档说明 
    - innodb-row-format-dynamic https://dev.mysql.com/doc/refma
1.
2.
3.

关于TEXT字段的存储方式和很多因素有关，他除了和本身记录的格式（参数INNODB_ROW_FORMART，当前默认格式为DYNAMIC有关系，同时和当前记录所在的页的存储长度也有关系，简单归纳一下：

首先，在 COMPACT 格式下，TEXT 字段的前 768 个字节存储在当前记录中，超过的部分存储在溢出页(overflow page)中，同时当前页中增加一个 20 个字节的指针（即 SPACEID + PAGEID + OFFSET）和本地长度信息（2 个字节），共计 768 + 20 + 2 = 790 个字节存储在当前记录。

其次，在 DYNAMIC 格式下，一开始会尽可能的存储所有内容，当该记录所在的页快要被填满时，InnoDB 会选择该页中一个最长的字段（所以也有可能是 BLOB 之类的类型），将该字段的所有内容存储到溢出页（overflow page）中，同时在原记录中保留20个字节的指针。

最后，当TEXT 字段存储的内容不大于40个字节时，这40 个字节都会存储在该记录中，此时该字段的长度为40+1（本地长度信息）= 41个字节。

这里提到溢出页，其实就是MySQL的一种数据存储机制，当一条记录中的内容,无法存储在单独的一个页内（比如存储一些大的附件），MySQL 会选择部分列的内容存储到其他数据页中，这种仅保存数据的页就叫溢出页（overflow page）。

计算text类型的最大列数

按照上述概念，我们可以算一下TEXT字段一共可以存储多少列（以目前默认的DYNAMIC格式，且 innodb_strict_mode=on），假设可以存储x列。

每个记录（ROW）中还存在元信息：

1. header信息（5个字节）

2. 列是否为null的 bitmap信息（ceil(x/8) 向上取整）

3. 系统字段：主键ID（6个字节）、事务ID（6个字节）、回滚指针（7个字节）

计算公式为：

5 + ceil(x/8) + 6 + 6 + 7 + x * 41 <= 8126
取整为 x = 197
1.
2.

查看mysql的源码，在当前MySQL版本（5.7.x）中，极端情况下，可以存储不超过197个TEXT类型字段。 storage/innobase/dict/dict0dict.cc

将innodb_strict_mode=off严格模式设置成关闭，所有TEXT类型字段都是以溢出页（overflow page）的方式存储，本地记录都是以指针（20个字节）进行存储，那就可以存储更多的字段。

计算公式为：

5 + ceil(x/8) + 6 + 6 + 7 + x * 20 <= 8126
取整为 x = 402
1.
2.

但是在实际操作中，我将500个字段的建表语句执行，发现也没失败：

CREATE TABLE `process_xxxx` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `instance_id` varchar(255) NOT NULL,
   ...
   ...
   ...
  `F_202001081110400_959` text,
  `F_202001081110400_965` text,
  `F_202001081110400_991` text,
  `F_202001081110410_397` text,
  `F_202001081110410_847` text,
  `F_202001081110410_910` text,
  `F_202001081110410_934` text,
  `F_202001081110410_961` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `instance_id` (`instance_id`),
  KEY `instance_status` (`instance_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> source create.sql
Query OK, 0 rows affected, 1 warning (0.02 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

执行成功，因为没有了严格模式的保护，mysql允许创建成功，但是给了一个warning。

这里有个疑问，500个字段是大于上面的计算结果402，为什么没创建失败？事实上，可以达到上面说的innodb 的最大限制1017个字段，超过1017个字段会有以下的报错：

mysql> source create.sql
ERROR 1117 (HY000): Too many columns
1.
2.

但是这样做了以后，虽然理论上可以建立1017个text 类型的列，但是业务上进行insert或者update的时候，mysql是无法保证能执行成功的。

所以项目上建议还是保持默认值，将innodb_strict_mode设置为on。

总结

MySQL Server最多只允许4096个字段

InnoDB 最多只能有1017个字段(innodb_strict_mode=off)

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

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

那么针对上面的建表报错，怎么处理呢？解决方法如下：

1、针对项目中这种超多字段，同时又只能用MySQL的场景下，我们可以使用 MySQL5.7中最新推出的JSON类型的字段，这样即使很多列数据只算在一个JSON字段。

2、或者进行分表创建，限制单表的字段个数，从业务层面规避创建单表字段过多的问题，通过编写规范的SQL语句以及采用合适的集群的架构，才能发挥出MySQL自身的潜力。

至此，答案已经非常清晰了。

作者：廖为基，腾讯互娱应用开发工程师

文章转自公众号：腾讯云数据库