MySQL Limits 列和行大小

原创 KevinCui 2023-06-09

1135

在MySQL数据库在使用当中，为了提高性能的同时，很好的维护好B+TRee结构，对于表列数和行大小做了限制。通常当超过限制的时候，就会提示ERROR[1118]如下报错信息：
1、列字节长度限制

#65535限制
mysql> CREATE TABLE t3(
         c1 VARCHAR(32765) NULL, c2 VARCHAR(32766) NULL
       ) ENGINE = MyISAM CHARACTER SET latin1;
ERROR 1118 (42000): 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

错误1118(42000):行大小太大。所使用的表类型(不包括TEXT或blob)的最大行长度是65535。必须将一些column更改为TEXT或blob类型（定义到这个长度也会报错，行本身维护也会占用字节）。

2、单行数据量大小限制
对于4KB、8KB、16KB和32KB InnoDB_page_size设置，InnoDB将单行数据大小限制为略小于数据库page设置的一半。如：对于默认16KB页面，限制为略低于8KB。

mysql> CREATE TABLE t4 (
       c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
       c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
       c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
       c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
       c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
       c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
       c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
       c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
       c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
       c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
       c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
       ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1;
ERROR 1118 (42000): 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.

错误1118(42000):行大小太大(> 8126)。将一些column更改为TEXT或BLOB可能会有所帮助。在当前的行格式中，0字节的BLOB前缀内联存储。

从上述两个错误提示中，可以了解到抛开text或blob类型，列的最大字节是65535，单行的数据记录，不操过8126字节。

从官方说明如下：

InnoDB对表的最大行大小强制执行65535字节的限制。表的BLOB和TEXT列的总大小不计入此限制。只有表的BLOB和TEXT列的指针才算到此限制。
InnoDB表可以存储在行的主数据页中的最大数据量取决于InnoDB_page_size系统变量的值。一行在行的主数据页上最多可以消耗的数据是innodb_page_size系统变量值的一半。默认值为16k时，这意味着一行在该行的主数据页上最多可以消耗8KB左右。但是，行的主数据页上的限制并不是行大小的绝对限制。

所有InnoDB行格式都可以在溢出页中存储某些类型的数据，因此InnoDB表的最大行大小可以大于行的主数据页中可以存储的最大数据量。某些行格式可以在溢出页中存储比其他行格式更多的数据。

Column Count Limits

MySQL每个表有4096列的硬限制，但是对于给定的表，有效的最大值可能更少。确切的列限制取决于几个因素:

表的最大行大小限制了列的数量(可能还有大小)，因为所有列的总长度不能超过这个大小。
单个列的存储需求约束了符合给定最大行大小的列的数量。某些数据类型的存储需求取决于存储引擎、存储格式和字符集等因素。
存储引擎可能会施加限制表列数的附加限制。例如，InnoDB每个表有1017列的限制。
功能键，如：隐藏的虚拟生成存储列，因此表索引中的每个功能键部分都会对表的总列限制进行计数。

Row Size Limits

给定表的最大行大小由以下几个因素决定:

MySQL表的内部表示的最大行大小限制为65,535字节，即使存储引擎能够支持更大的行。BLOB和TEXT列只向行大小限制贡献9到12个字节，因为内容与行其余部分分开存储。
InnoDB表的最大行大小适用于存储在数据库页中的本地数据，对于4KB、8KB、16KB和32KB的innodb_page_size设置，最大行大小略小于半页。例如，对于默认的16KB InnoDB页面大小，最大行大小略小于8KB。
如果一行包含的变长列超过了InnoDB的最大行大小，InnoDB会选择变长列作为外部页外存储，直到该行符合InnoDB的行大小限制。对于存储在页外的变长列，本地存储的数据量因行格式而异。
不同的存储格式使用不同数量的页头和尾数据，这会影响行可用的存储量。
InnoDB行格式,MyISAM表存储格式不同也有限制。

INNODB引擎限制

一个表最多可以包含1017列。虚拟生成的列包含在此限制中。
一个表最多可以包含64个辅助索引。
对于使用DYNAMIC或COMPRESSED行格式的InnoDB表，索引键前缀长度限制为3072字节。
对于使用REDUNDANT或COMPACT行格式的InnoDB表，索引键前缀长度限制为767字节。
索引键的最大长度将按比例降低，基于16KB页面大小3072字节的限制
对于4KB、8KB、16KB和32KB的页面大小，最大行大小（不包括页外存储的任何可变长度列）略小于页面的一半。例如，16KB的默认innodb_page_size的最大行大小约为8000字节。
一行的长度小于半页，则所有行都存储在该页的本地。如果行超过半页，则选择可变长度列进行页外外部存储，直到该行适合半页。
尽管InnoDB内部支持大于65535字节的行大小，但MySQL本身对所有列的组合大小施加了65535的行大小限制。
InnoDB日志文件的最大组合大小为512GB。
最小表空间大小略大于10MB。表空间的最大大小取决于InnoDB页面的大小。
如：InnoDB Page Size：16KB Maximum Tablespace Size：64T
InnoDB实例最多支持2^32（4294967296）个表空间，其中一小部分表空间保留给undo和临时表。
共享表空间最多支持2^32（4294967296）个表。

表的行格式决定了其行的物理存储方式，这反过来又会影响查询和DML操作的性能。由于单个磁盘页可以容纳更多行，查询和索引查找可以更快地工作，缓冲池中所需的缓存内存更少，写入更新值所需的I/O也更少。
InnoDB存储引擎支持四种行格式:冗余、紧凑、动态和压缩。

REDUNDANT

使用冗余行格式的表将可变长度列值(VARCHAR、VARBINARY、BLOB和TEXT类型)的前768字节存储在B-tree节点内的索引记录中，其余的存储在溢出页上，大于或等于768字节的固定长度列被编码为可变长度列，可以存储在页外。如果列的值为768字节或更少，则不会使用溢出页，并且可能会节省I/O，因为该值完全存储在B-tree节点中。这对于相对较短的BLOB列值很有效，但可能导致B-tree节点填充数据而不是键值，从而降低了效率。

COMPACT

COMPACT行格式是REDUNDANT的增强版，COMPACT行格式减少了大约20%的行存储空间，但代价是增加了某些操作的CPU使用量。工作负载受到CPU速度的限制，紧凑格式可能会慢一些。

使用COMPACT行格式的表将可变长度列值(VARCHAR、VARBINARY、BLOB和TEXT类型)的前768字节存储在B-tree节点内的索引记录中，其余的存储在溢出页上。

DYNAMIC

DYNAMIC行格式提供与COMPACT行格式相同的存储特性，但增加了针对长可变长度列的增强存储功能，并支持大索引键前缀。

当用ROW_FORMAT=DYNAMIC创建表时，InnoDB可以完全在页外存储长可变长度的列值(对于VARCHAR, VARBINARY, BLOB和TEXT类型)，集群索引记录只包含一个指向溢出页的20字节指针。大于或等于768字节的固定长度字段被编码为可变长度字段。

列是否存储在页外取决于页大小和行的总大小。当一行太长时，将选择最长的列作为页外存储，直到聚集索引记录适合b树页。小于或等于40字节的TEXT和BLOB列按行存储。

DYNAMIC行格式基于下，如果长数据值的一部分存储在页外，那么将整个值存储在页外通常是最有效的。使用DYNAMIC格式，较短的列可能保留在B-tree节点中，从而最大限度地减少给定行所需的溢出页数。
DYNAMIC行格式支持最多3072字节的索引键前缀。

COMPRESSED

压缩行格式提供与DYNAMIC行格式相同的存储特性和功能，但增加了对表和索引数据压缩的支持。
对于页外存储，COMPRESSED行格式使用了与DYNAMIC行格式类似的内部细节，同时对表和索引数据进行了额外的存储和性能考虑，并使用了更小的页面大小。对于COMPRESSED行格式，KEY_BLOCK_SIZE选项控制在聚集索引中存储多少列数据，以及在溢出页上放置多少列数据。

COMPRESSED行格式支持最多3072字节的索引键前缀。

使用InnoDB的压缩特性，可以帮助提高原始性能和可伸缩性。压缩意味着更少的数据在磁盘和内存之间传输，并且占用更少的磁盘和内存空间。对于具有二级索引的表，好处会被放大，因为索引数据也被压缩了。压缩对于SSD存储设备尤其重要。当然压缩也会消耗额外的cpu。
应用程序的总体性能、CPU和I/O利用率以及磁盘文件的大小都是衡量应用程序压缩效率的良好指标。

实践验证

不同长度的column字段限制：

1. 表结构创建：
对于字符集：utf8 2个字节，utf8mb4占用3字节，gbk占用2个字节，latin1占用1个字节。

mysql> CREATE TABLE t_char4 (
       c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
       c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
       c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
       c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
       c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
       c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
       c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
       c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
       c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
       c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
       c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

CREATE TABLE `t_varchar4` (
       c1 VARCHAR(255),c2 VARCHAR(255),c3 VARCHAR(255),
       c4 VARCHAR(255),c5 VARCHAR(255),c6 VARCHAR(255),
       c7 VARCHAR(255),c8 VARCHAR(255),c9 VARCHAR(255),
       c10 VARCHAR(255),c11 VARCHAR(255),c12 VARCHAR(255),
       c13 VARCHAR(255),c14 VARCHAR(255),c15 VARCHAR(255),
       c16 VARCHAR(255),c17 VARCHAR(255),c18 VARCHAR(255),
       c19 VARCHAR(255),c20 VARCHAR(255),c21 VARCHAR(255),
       c22 VARCHAR(255),c23 VARCHAR(255),c24 VARCHAR(255),
       c25 VARCHAR(255),c26 VARCHAR(255),c27 VARCHAR(255),
       c28 VARCHAR(255),c29 VARCHAR(255),c30 VARCHAR(255),
       c31 VARCHAR(255),c32 VARCHAR(255),c33 VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

备注：对于默认的innodb_page_size=16KB InnoDB页面大小，最大行大小略小于8KB（8kb * 1024=8192字节）。
1.t_char4因为column字段名 CHAR(255)，字符集（latin1 1字节）=255字节，所以column无法操过768字节。255*33=9504字节。对于CHAR无法页外存储。

2.t_varchar4因为column字段名 VARCHAR(255)，字符集（latin1 1字节）=255字节。当一行太长时，将选择最长的列作为页外存储，直到聚集索引记录适合B-tree page。那就保存20字节指针。
20*33=660字节。满足半页所以成功保存。

2.数据长度限制
测试1：写入数据长度小于8126的场景，240*33=7920，可以成功。

mysql> insert into t_varchar4 (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29,c30,c31,c32,c33)
values(repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),
repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),
repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),
repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),
repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240));
Query OK, 1 row affected (0.01 sec)

测试2：写入数据长度小于8126的场景，250*33=8250，可以成功。

mysql> insert into t_varchar4 (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29,c30,c31,c32,c33)
values(repeat('a',250),repeat('a',250),repeat('a',250),repeat('a',250),repeat('a',250),repeat('a',250),repeat('a',250),
repeat('a',250),repeat('a',250),repeat('a',250),repeat('a',250),repeat('a',250),repeat('a',250),repeat('a',250),
repeat('a',250),repeat('a',250),repeat('a',250),repeat('a',250),repeat('a',250),repeat('a',250),repeat('a',250),
repeat('a',250),repeat('a',250),repeat('a',250),repeat('a',250),repeat('a',250),repeat('a',250),repeat('a',250),
repeat('a',250),repeat('a',250),repeat('a',250),repeat('a',250),repeat('a',250));
ERROR 1118 (42000): 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.

备注：因为varchar类型长度是可以缩小的。所以创建的时候不超过65535字节，就不会创建不成功的。单实际数据要是操作，长度还是操作半页长度，还是被限制。

总结

在设计表结构的时候应该考虑好列保证在单行在InnoDB_page_size一半以内。同时保证不超过65535字节。对于行溢出(VARCHAR、VARBINARY、BLOB和TEXT类型)类型，虽然突破了半页限制，每次跨页操作，性能会受到不小的影响。

参考：
https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html

墨力计划 mysql

最后修改时间：2023-06-09 10:34:29

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

专家专栏 | 崔虎龙（共139篇）

云和恩墨开源MySQL技术顾问，长期服务于数据中心（银行，金融、游戏、物流）行业，熟悉数据中心运营管理的流程及规范、自动化运维等方面。擅长MySQL，Redis，MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自我学习通过了MySQL OCP5.6 和 MySQL OCP5.7认证证书。