原文:http://mp.weixin.qq.com/s?__biz=MzAwMjkyMjEwNg==&mid=2247483785&idx=1&sn=1d90a44915d1028c6dc150367e1af033#rd
问题由来引用我们客户的原话:
*创建如下表,提示我:*
*如果我将下面表中的varchar(200),修改成text(或blob):报错变为另一个:*
*我们查阅了很多的资料,不确定The maximum row size到底是65535 还是8126?原理是什么?*
● 错误1 创建表报maximum row size > 65535
● 错误2 创建表报Row size too large (> 8126)
● 错误3 表创建成功但是插入报 Row size too large (> 8126)
别急,一个问题一个问题的看。
错误1这个报错其实我们查询MySQL官方手册就可以查询到, 对于一行记录最大的限制是65535字节。为什么是65535,不要问我,手册也没说:)——一行数据里面字段长度定义有64k,我也是醉了。错误2既生瑜何生亮?有了65535的限制以后还有一个8126的限制是为什么呢?
MySQL是分两层的,MySQL Server层 + 存储引擎层。
第2个问题其实是MySQL除了在Server层做了一次限制还会在Innodb存储引擎层在做一次限制。
innodb为了保证B+TREE是一个平衡树结构,强制要求一条记录的大小不能超过一个页大小的一半。这也就是我们上面看到的第二个错误。
下面是innodb B+树的结构,我们可以想象一下二分查找时,一个页的只有一条数据会是什么样子?
每个页只有一条数据的查找就变成了链表查找了。这样就没有二分查找的意义了。
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)
在这种格式记录下Innodb 对于大字段的处理如下:
对于大字段,innodb只会存放前DICT_ANTELOPE_MAX_INDEX_COL_LEN(768)字节在数据页中,超过768字节都会放到溢出页中。这种方式也是B+TREE结构,但是也并不是完美的,因为我们将大字段存放到了数据页中会造成叶子节点的个数会很多,同样会造成非叶子节点的的个数增加。最终导致索引层级增高,访问IO次数增加。
他的溢出存储方式如下:
在Barracuda格式下,会用20字节的指针指向溢出页,这样做的好处就是不会造成索引层级的增高。
引用reference的原文如下:
附上测试的建表语句和insert语句,有兴趣的朋友可以自己按照上面的几种方式在Antelope和Barracuda的几种不同ROW_FORMAT格式上试试。
附2.insert语句