MySQL行溢出的原理

永夜海

　　InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。一般认为BLOB，LOB这类的大对象类型会把数据存放在数据页面之外。但是，这个理解有点偏差，BLOB可以不将数据存储在溢出页面，而且即便是VARCHAR列数据类型，依然有可能被存放为行溢出数据。
　　首先对VARCHAR数据类型进行研究。很多DBA喜欢MYSQL数据库提供的VARCHAR，因为相对于ORACLE VARCHAR2最大存放4000字节，SQL SERVER存放8000字节，MYSQL数据库的VARCHAR类型可以存放65535字节。但是，这是真的吗？真的可以存放65535字节吗？如果创建VARCHAR长度为65535的表，用户得到下面的错误信息：
　　localhost.test>create table test (a varchar(65533))  ENGINE=innodb;

　　ERROR 1118 (42000): Row>　　报错可知INNODB存储引擎不支持65533以上长度的VARCHAR，SQL_MODE为非严格模式时可以建表，但有警告。
　　localhost.test>create table test (a varchar(65532)) charset=latin1 ENGINE=innodb;
　　Query OK, 0 rows affected (0.00 sec)
　　可见支持的最大长度为65532。
　　另外需要注意，MYSQL官方手册中定义的65535长度是指所有VARACHR列的长度总和，如果列的长度总和超过这个长度，依然无法创建，如下所示：
　　localhost.test>create table test2 (a varchar(22000),b varchar(22000),c varchar(22000)) charset=latin1 ENGINE=innodb;

　　ERROR 1118 (42000): Row>　　即使是能存放65532字节，但是有没有想过，INNODB存储引擎的页为16KB，即16384字节，怎么能存放65532字节呢？因为，在一般情况下，INNODB存储引擎的数据都是存放在页类型为B-tree node中。但是当发生行溢出时，数据存放在页类型为Uncompress BLOB页中。来看一个下面的例子：
　　mysql> create table t(a varchar(65532)) engine=Innodb charset=latin1;
　　Query OK, 0 rows affected (0.02 sec)
　　mysql> insert into t select repeat('a',65532);
　　Query OK, 1 row affected (0.06 sec)
　　Records: 1 Duplicates: 0 Warnings: 0
　　g
　　通过工具可以观察到表空间有一个数据页节点B-tree Node，另外有4个未压缩的二进制大对象页Uncompressed BLOB Page，在这些页中才真正存放了65532字节的数据，既然实际存放的数据都在BLOB页中，那数据页中又存放了些什么内容呢？通过hexdump来读取表空间文件，从数据页c000开始查看：

http://note.youdao.com/yws/res/1549/7DB262A44DCD4468845270286F87FA31

　　。
　　。
　　。

http://note.youdao.com/yws/res/1546/720E837717E742F38836E6A55CB8A6C9

　　可以看到，从0x0000c093到0x0000c392数据页面其实只保存了VARCHAR(65532)的前768的前缀数据(这里都是a)，之后是偏移量，指向行溢出页，也就是前面看到的Uncompressed BLOB Page
　　经过测试行溢出的边界点在8098的位置：
　　mysql> create table t1(a varchar(65532)) engine=Innodb charset=latin1;
　　Query OK, 0 rows affected (0.07 sec)
　　mysql> insert into t1 select repeat('a',8098);
　　Query OK, 1 row affected (0.04 sec)
　　Records: 1 Duplicates: 0 Warnings: 0

http://note.youdao.com/yws/res/1548/CA5457FB50E1427890C264B91CD22F0D

　　没有出现Uncompressed BLOB Page
　　mysql> create table t2(a varchar(65532)) engine=Innodb charset=latin1;
　　Query OK, 0 rows affected (0.02 sec)
　　mysql> insert into t2 select repeat('a',8099);
　　Query OK, 1 row affected (0.08 sec)
　　Records: 1 Duplicates: 0 Warnings: 0

http://note.youdao.com/yws/res/1547/CBF2E5120B4C4F63A8E9870B7194AB48

　　出现bolb页，由此可见使用VARCHAR存储大于8098字符的数据时会出现行溢出，进而影响部分索引的性能。
　　============================================================================================================================================================================================

　　关于 MySQL: Error Code: 1118 Row>　　首先出现这个问题说明 这张表 插入或修改的数据 单行数据存储大小已经超过 innodb引擎的页的一半了，innodb页默认大小为16k，但实际存储时分为上下两页，数据存储在上页所以单行数据不能超过16k/2 也就是 8126 字节
　　接下来我们模拟一个表结构
　　CREATE TABLE `dc_dynamic_seminar` (
　　`dynamicid` int(10) unsigned NOT NULL,
　　`title` varchar(30) NOT NULL,
　　`phase` varchar(10) NOT NULL,
　　`complain` varchar(100) NOT NULL,
　　`now_history` varchar(500) NOT NULL,
　　`past_history` varchar(500) NOT NULL,
　　`physical` varchar(500) NOT NULL,
　　`sup_exa` varchar(500) NOT NULL,
　　`diagnosis` varchar(200) NOT NULL,
　　`basis` varchar(500) NOT NULL,
　　`further_exa` varchar(500) NOT NULL,
　　`treatment` varchar(500) NOT NULL,
　　`question` varchar(500) NOT NULL,
　　`answer` varchar(500) NOT NULL,
　　`reward` varchar(200) NOT NULL,
　　`state` tinyint(1) NOT NULL DEFAULT '1',
　　`end_state` tinyint(1) NOT NULL DEFAULT '0',
　　PRIMARY KEY (`dynamicid`)
　　) ENGINE=InnoDB DEFAULT CHARSET=utf8;
　　当这张表所有varchar字段都插满数据（汉字）数据时必然会报错，究其原因就是因为存储单行数据已经超过innodb数据页大小的限制了
　　分析一下表结构，忽略int字段，只看varchar字段，varchar(500)字段有9个，当innodb_file_format=compact时(默认) ,数据页中最多保存数据的前768字节的前缀数据，超出部分则会存在溢出页中，
　　根据上述内容，那么9个varchar(500) 字段所占数据页空间为 768x9=6912字节 其余3个字段varchar总和为500 字符集为utf8 ，500字符的话存储需要1500字节再加上数据库内部一些头信息很显然已经超出了8126
　　解决方案：
　　1.缩减字段长度，直到单行数据量小于8126
　　2.SET GLOBAL innodb_file_format='Barracuda';
　　alter table xxxx ROW_FORMAT=COMPRESSED ；
　　通过调整 innodb文件格式和表的行格式来解决。这种格式下每个字段只会占用20字节存储数据指针而不是之前的768字节。