InnoDB(4)行溢出--mysql从入门到精通（九)

上篇文章说了compact行格式中真实数据存储，真实数据innoDB会默认添加transaction_id事务id，roll_pointer回滚指针，其中row_id不是必须的，当用户设置了primery key主键默认用用户设置的，没设置，找一个unique列，若都没有，则会用row_id。

还说了char(M)类型存储，若是变长字符集，比如gbk，utf8，则会存储在变长字段长度列表，固定字符集则不会，而且需要注意的是，默认会在内存中占据M的字节。

InnoDB(3)记录真实数据--mysql从入门到精通（八)

行溢出数据

Varchar(M)类型最多存储多大？65535个字节，如果使用ascii字符集，一个字符代表一个字节，如果创建的话则会如下：

mysql> create table max_size(

   ->   c varchar(65535)

   -> ) charset=ascii row_format=compact;

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

很明显报错了，从错误可以看到，我们创建的字段过长，系统让我们改成text或者blob类型，因为这个65535个字节，除了我们需要存储的真是数据外，还有额外数据：变长字段长度列表，null值列表(若有not null)则可以省略。

因为我们没有设置not null，所以变长字段长度可能占两个字节，null占一个字节，所以65532个字节。

create table max_size_ascii(   c varchar(65532) ) charset=ascii row_format=compact;

Query OK, 0 rows affected (0.06 sec)

如果设置not null，则就可以存储65533个字节，这里就不写sql了。

如果不是使用ascii字符集呢，用gbk和utf8？

mysql> create table max_size_gbk(

   ->   c varchar(65535)

   -> ) charset=gbk row_format=compact;

ERROR 1074 (42000): Column length too big for column 'c' (max = 32767); use BLOB or TEXT instead

mysql> create table max_size_utf(

   ->   c varchar(65535)

   -> ) charset=utf8 row_format=compact;

ERROR 1074 (42000): Column length too big for column 'c' (max = 21845); use BLOB or TEXT instead

根据sql提示可以看到，创建gbk字符集的表，则最大可以用32767个字节(65532/2)，因为gbk一个字符占用的最大字节是2，而utf8一个字符占用的最大字节是3，所以报错21845(65532/3)。

一个表中所有列(不包括隐藏列和记录头信息)，占用的最大字节长度为65535个字节。

数据太多产生溢出怎么办

我们知道mysql处理数据是分成若干页，一个页大小约16kb，也就是16384字节，而varchar（M）中的m最大可存储65532字节，那溢出的就会放在其他页码中。repeat('a',65532)代表重复insert数据65532次，吧数据填满

mysql> insert into max_size_ascii1 (c1) values (repeat('a',65532));

Query OK, 1 row affected (0.01 sec)

在compact和redundant行格式中，真实数据存放处就会放指向后面页数据的内存地址，前面一部分存放780字节的真实数据，从而根据页码地址找到剩余的数据。

Dynamic和Compressed行格式

Mysql版本5.7后默认用的是dynamic行格式，他们和compact行格式基本一致，唯一有点不同的就是行数据溢出的存储方式，他们在真实数据列表不会存储真实数据，只存储页码的地址值，从而查询数据的页码。而compact行数据溢出是在前780左右字节存一部分真实数据。

而compressed和dynamic不同处：compressed会采用压缩算法来对页面进行压缩，节省空间。