innodb中大字段的限制

转载自：http://hidba.org/?p=144

 

今天开发在导入数据的时候报一个错误:

表结构大致如下：

发现导入的数据单行长度较长。

查看官方文档的解决办法为：

Solution:
1.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.

但为什么会出现上面的解释？

通过查询发现为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同理）：

ERROR 1030 (HY000): Got error 139 from storage engine

 

测试如下：

插入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字节用于指向溢出页。

转载于:https://www.cnblogs.com/markqian/p/3389957.html