标签:
今天开发在导入数据的时候报一个错误:
Rowsizetoolarge.Themaximumrowsizefortheusedtabletype,notcountingBLOBs,is8126.YouhavetochangesomecolumnstoTEXTorBLOBs
原因:发现导入的数据单行长度较长。
官方文档的解决办法为:
1.垂直表字段拆分或者大字段合并(大字段最多不超过768,业务进行合并+拆分),divide your table into small ones. If one table contain more than 10 text colums, and the data contain is a little bit long. this error will be thrown out.
2.修改表的存储引擎,modify InnoDB to MyISAM.
3.修改row_format为COMPRESSED或者DYNAMIC,当然前提需保证innodb_file_format =Barracuda.
但为什么会出现上面的解释?
通过查询发现为innodb的一个限制:
我们知道innodb的页块大小默认为16kb,表中数据是存放在B-tree node的页块中,但如果表中一行的数据长度超过了16k,这时候就会出现行溢出,溢出的行是存放在另外的地方,存放该溢出数据的页叫uncompresse blob page。
innodb采用聚簇索引的方式把数据存放起来,即B+树结构,因此每个页块中至少有两行数据,否则就失去了B+树的意义(每一个页中只有一条数据,整个树成为了一条双向链表),这样就得出了一行数据的最大长度就限制为了8k。
当插入的一行数据不能在一个数据页块中存放时,为了保证该页至少能存放两行数据,innodb将会自动部分数据溢出到另外页中,一部分数据将存放在数据页块中,其大小为该列的前768字节,同时接着还有偏移指向溢出页。
如上面所说大字段的前768字节会存放在数据页块中,那么如果有10个大字段(如varchar(1000),text,blob同varchar同样存储前768字节),同样会超过一行数据8k的限制(10*768<8000,11*768>8000)。如果插入的值超过8000字节,则会报错(BLOB或TEXT同理):
测试如下:
表结构:
插入10列数据(10*768<8000)可以插入:
插入11列数据(11*768>8000)报错:
Text数据类型测试:
Blob数据类型测试:
明白了是怎么一回事后,就可以解决出现问题了,减少varchar(1000)的字段数量,或者改存储引擎为myisam;还可以增加page_size的大小:如改为32k,64k。由于需求不好改动,数据库的页块大小改变需要改动源码,了解该表以查询为主,更新非常少,所以改为myisam存储引擎:
可以看到myisam存储引擎不受此限制。
从上面也可以看出,在mysql innodb存储引擎表收到页块大小,数据以B+树的方式组织数据,导致单行数据不能超过8k,从而影响了表中大字段数据类型varchar,text,blob个数限制,在16k页块大小下,最好不要超过10个,在表设计中需要注意这个限制。
在innodb plugin的版本中,mysql引入了新的文件格式:barracuda,梭鱼;改文件格式中拥有两种新的行记录:compressed,dynamic,这两钟格式对于BLOB数据完全采用行溢出方式,在数据页中只占用20字节用于指向溢出页。
Antelope是innodb-base的文件格式,Barracude是innodb-plugin后引入的文件格式,同时Barracude也支持Antelope文件格式。两者区别在于:
文件格式
Antelope(Innodb-base)
支持行格式
ROW_FORMAT=COMPACT
ROW_FORMAT=REDUNDANT
特性
Compact和redumdant的区别在就是在于首部的存存内容区别。
compact的存储格式为首部为一个非NULL的变长字段长度列表,redundant的存储格式为首部是一个字段长度偏移列表(每个字段占用的字节长度及其相应的位移)。在Antelope中对于变长字段,低于768字节的,不会进行overflowpage存储,某些情况下会减少结果集IO.
Barracuda(innodb-plugin)
支持行格式
ROW_FORMAT=DYNAMIC
ROW_FORMAT=COMPRESSED
特性
这两者主要是功能上的区别功能上的。另外在行里的变长字段和Antelope的区别是只存20个字节,其它的overflowpage存储。另外这两都需要开启innodb\_file\_per\_table=1(这个特性对一些优化还是很有用的)
备注:
这里有一点需要注意,如果要使用压缩,一定需要先使用innodb_file_format =Barracuda格式,不然没作用。
参考:
标签: