MySQL内核月报 2014.10-MySQL· 系统限制·text字段数-阿里云开发者社区

开发者社区> db匠> 正文

MySQL内核月报 2014.10-MySQL· 系统限制·text字段数

简介:
+关注继续查看

背景

  当用户从oracle迁移到MySQL时,可能由于原表字段太多建表不成功,这里讨论一个问题:一个InnoDB表最多能建多少个text字段。

  我们后续的讨论基于创建表的语句形如:create table t(f1 text, f2 text, ..., fN text)engine=innodb;


默认配置

  在默认配置下,上面的建表语句,N取值范围为[1, 1017]。 为什么是1017这个“奇怪”的数字。实际上单表的最大列数目是1024-1,但是由于InnoDB会增加三个系统内部字段(主键ID、事务ID、回滚指针),因此需要减3。而用于记录系统字典表也受1023的限制,又需要再增加三个该表的系统字段,因此每个表的最大字段数是1023-3*2。


插入异常

  上述描述说明的是表能够创建成功的最大字段数。但是这样的表是“插入不安全”的。我们知道text的长度上限是64k。而往上表中插入一行,每个字段长度为7,就会报错:Row size too large (> 8126).

  一个page是16k,空page扣掉页信息占用空间是16252,需要除以2,原因是每个page至少要包含两个记录。

  也就是说,虽然可以创建一个包含1017个text字段的表,但是很容易碰到插入失败。


如何保证插入安全

  上面的表结构,在保证插入安全的情况下,N的最大值是多少?text在存储的时候,当超过768字节的时候,剩余部分会保存在另外的页面(off-page),因此每个字段占用的最大空间为768+20+2=788. 20字节存储最短剩余部分的位置(SPACEID+PAGEID+OFFSET)。2字节存储本地实际长度。

  因此N最大值为lower(8126/790)=10。

  如果我们想在创建的表的时候,保证创建的表中的text字段都能安全的达到64k上限(而不是等插入的时候才发现),那么需要将默认为OFF的innodb_strict_mode设置为ON,这样在建表时会先做判断。

  但是,在设置为严格模式后,上述建表语句的最大N却并非10.


ROW_FORMAT

  在off-page存储时,本地占用790个字节,是基于默认的ROW_FORMAT,即为COMPACT,此时插入安全的N上限为10。

  而在InnoDB新格式Barracuda支持下,Dynamic格式的off-page存储时,在local保存的上限不再是768,而是20个字节。这样每个字段在数据页里面占用的最大值是40byte,再需要一个额外的字节存储实际的本地长度,因此每个text最大占用41字节。

  实际上很容易测试在严格模式下,建表的最大N为196. 以下为N=197时计算过程:


每行记录预留header 5个字节。

  每个bit保存是否允许null,需要 upper(197/8)=25个字节。

  三个系统保留字段 6+6+7=19.

  因此总占用空间 5+25+19+41*197=8126!


也就是说,当N=197时,刚好长度为8126,而代码中实现是 if(rec_max_size >= page_rec_max) reutrn(error).

  就这么不巧!


作为补充

  有经验的读者可以联想到,如果我们的表中自己定义一个int型主键呢?此时系统不需要额外增加主键,因此整个表结构比之前少2字节。

  也就是说,建表语句修改为: create table t(id int primary key, f1 text, f2 text, ..., fN text)engine=innodb;

  则此时的N上限能达到197。


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
评论字数限制和字数显示
评论字数限制和字数显示
1346 0
VB.NET版机房收费系统---SqlHelper
        SqlHelper,最早接触这个词儿的时候,好像是13年的暑假,那个夏天来的比往年来的稍晚一些,呵呵,sqlhelper,翻译成中文就是数据库助手,帮手。
947 0
mysql 系统用户最大文件打开数限制
mysql 系统用户最大文件打开数限制 纸上得来终觉浅,绝知此事多宕机...记录一下自己很蠢的一次故障处理过程。   上周的时候,一个刚上线的系统又开始反映登不上了,因为最近这个系统也老是出现这个问题,开发也一直在找问题中,所以也没太在意。
978 0
利用MySQL系统数据库做性能负载诊断
利用MySQL系统数据库做性能负载诊断某大师曾说过,像了解自己的老婆 一样了解自己管理的数据库,个人认为包含了两个方面的了解:1,在稳定性层面来说,更多的是关注高可用、读写分离、负载均衡,灾备管理等等high level层面的措施(就好比要保证生活的稳定性)2,在实例级别的来说,需要关注内存、IO、网络,热点表,热点索引,top sql,死锁,阻塞,历史上执行异常的SQL(好比生活品质细节)MySQL的performance_data库和sys库提供了非常丰富的系统日志数据,可以帮助我们更好地了解非常细节的,这里简单地列举出来了一些常用的数据。
2896 0
RDS发布会解读| AliSQL内核新特性
AliSQL在2020年做了不少事情,有必要总结分享一下,以便让大家更好地知道有哪些特性,可以在哪些业务场景中使用到,也是为了在2021年更好的向前发展。在年初时计划的一些企业级功能基本上都实现了,并且在过程中特别强调了功能的场景通用性,不再是从某个行业某个特定业务或应用场景设计(比如电商秒杀),而是从云上众多用户的不同场景出发,并且不需要用户应用或SQL改造配合(直接一个开关就可以开启的),还要求在RDS 56/57/80三个主流版本上都有同样的体验,从云场景而生并为云场景服务的技术,都是云原生技术。这一目标角度的调整的确是给自己加了不少难度,但研发让所有云上用户都能轻松受益享受技术红利的新
1851 0
+关注
db匠
rds内核团队秘密研发的全自动卖萌机. 追加特效: 发数据库内核月报. 月报传送: http://mysql.taobao.org/monthly/
497
文章
0
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载