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

问题由来

引用我们客户的原话:

*创建如下表,提示我:*

*如果我将下面表中的varchar(200),修改成text(或blob):报错变为另一个:*

*我们查阅了很多的资料,不确定The maximum row size到底是65535 还是8126?原理是什么?*

先把问题原因的总结和建议给大家列出来,有兴趣的朋友可以查看后面的问题细节描述,或者按照附录的创建表、插入表语句来手工验证一下。

总结

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

● InnoDB 最多只能有1000个字段

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

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

● 表结构中根据Innodb的ROW_FORMAT的存储格式确定行内保留的字节数(20 VS 768),最终确定一行数据是否小于8126,如果大于8126,报错。

优化建议

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

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

三种报错的疑惑

我们整理了一下,其实类似的错误有三种:

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

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

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

到底要闹哪样

这么多错误,还都不一样,MySQL到底要闹那样

别急,一个问题一个问题的看。

错误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字符集,这样一个字符就是一个字节)的表。(建表语句和insert语句参见附录)

150 * 100=15000 > 8126。按照上面的说法,应该要报错的,

但是各位可以在自己的数据库上试一下,表能够建立成功,这是为什么呢?

其实MySQL在计算字段长度的时候并不是按照字段的全部长度来记的。

列字段小于40个字节的都会按实际字节计算,如果大于20 * 2=40 字节就只会按40字节。

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

也就是说,如果字段长度超过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) 大于了20*2那么就只会按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字符的字段列。

用附2的插入语句试一下就知道,错误3也会报错出来。

也就是说表可以创建成功但是插入却失败,原因如下:

● Antelope格式下的COMPACT大字段按照DICT_ANTELOPE_MAX_INDEX_COL_LEN(768)字节溢出页。varchar(100)没有存储为溢出页。

● Barracuda的DYNAMIC和COMPRESSED格式下只有长字段才会用20字节溢出页的方式,varchar(100)也没有存储为溢出页。

引用reference的原文如下:

参考网址

MySQL reference: Limits on Table Column Count and Row Size

MySQL reference: innodb row format dynamic

mysqlserverteam:Externally Stored Fields in InnoDB

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

附1.建表语句

附上测试的建表语句和insert语句,有兴趣的朋友可以自己按照上面的几种方式在Antelope和Barracuda的几种不同ROW_FORMAT格式上试试。

附2.insert语句

原文发布于微信公众号 - 沃趣科技(woqutech)

原文发表时间:2016-07-04

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏程序员的SOD蜜

隐藏在程序旮旯中的“安全问题”

    作为一个真正的程序员,必须有高度的“安全意识”,因为我们作出的软件运行在复杂的环境中,不能把不该有异常抛给用户,更不能把漏洞留给“黑客”,当然也不能把“...

21850
来自专栏WindCoder

网易MySQL微专业学习笔记(四)-实践课数据库对象

这个系列属于个人学习网易云课堂MySQL数据库工程师微专业的相关课程过程中的笔记,本篇为其“MySQL数据库对象与应用”中的MySQL数据类型相关笔记。

8510
来自专栏更流畅、简洁的软件开发方式

【测试】两种数据库,四种分页算法的效率比较

分页算法本身没有什么快慢之分,对反应速度起到决定作用的是——能否有效地利用索引! 算法 评价 缺点 适用的数据库 max 效率最高的 只能有一...

31570
来自专栏逢魔安全实验室

SQL注入ByPass的一些小技巧

? 01 — 前言 SQL注入从古至今都是一个经久不衰的影响严重的高危漏洞,但是网络安全发展到现在,如果想通过SQL注入直接获取数据或者权限,多多少少都需要绕...

42790
来自专栏个人分享

前一天数据偏移至当日数据

客户画像项目中可能会遇到此类问题,因为客户画像要求的是对单个用户信息的查询。 但倘若以日周期为准,今日梳理的数据宽表中某个字段为null,但此类字段不能以nul...

15110
来自专栏Golang语言社区

山海传说ai 设计

一 城镇ai: 1.1 任务npc ai:当鼠标指向时,npc头顶会出现名字。并高亮显示npc模型。鼠标移开 后npc恢...

37480
来自专栏烙馅饼喽的技术分享

本人有生以来的第一篇博客,嘿嘿,就发这个吧, 怎样在虚拟主机上使用Castle框架的ActiveRecord

        我在某个私人项目中使用了Castle 的 ActiveRecord.用起来那是真叫个爽,整个项目里楞是一句SQL语句都没有,嘿嘿。超级喜欢上了这...

27850
来自专栏扎心了老铁

spark-streaming集成Kafka处理实时数据

在这篇文章里,我们模拟了一个场景,实时分析订单数据,统计实时收益。 场景模拟 我试图覆盖工程上最为常用的一个场景: 1)首先,向Kafka里实时的写入订单数据,...

83450
来自专栏决胜机器学习

设计模式专题(二十) ——职责链模式

设计模式专题(二十)——职责链模式 (原创内容,转载请注明来源,谢谢) 一、概述 职责链模式(Chainof Responsibility),是使多个对象都有...

35690
来自专栏Python爬虫与算法进阶

Scrapy中如何提高数据的插入速度

速度问题 最近工作中遇到这么一个问题,全站抓取时采用分布式:爬虫A与爬虫B,爬虫A给爬虫B喂饼,爬虫B由于各种原因运行的比较慢,达不到预期效果,所以必须对爬虫...

529110

扫码关注云+社区

领取腾讯云代金券