【Mysql】 InnoDB引擎深入- 行溢出

知识点介绍：

ascii字符集： n/1 （一个字符代表一个字节）

gbk字符集： n/2

utf8字符集: n/3

​ 对于可变长度列最大存储长度为65535个字节（64kb），也就是说一行数据最大长度就是65535个字节，但是由于上面介绍过行格式中还存在其他相关信息的存储，例如null值、回滚指针的信息，所以最大长度为65535-2 个字节。

​ 如果在ascii字符集下，也就是一行最多可以存储65532个字符；

​ 在gbk字符集下65532/2字符；

​ 在utf8字符集下65532/3字符；

 根据数据页了解我们发现，一页的大小是16kb（16384字节），而一行最大可以存储65535字节，那溢出数据怎么放呢？

对于较大的可变长度列溢出的数据，会被存储到外部溢出页中存储，一般为BLOB PAGE。

（这里需要注意的是，当表行数很多时，会有更多数据页来存储，如果列超大可变长度列溢出使用 外部溢出页存储）

对于不同行格式，稍有不同，如下：

REDUNDANT

​ 使用REDUNDANT行格式的表将前 768 个字节的可变长度列值（VARCHAR、 VARBINARY和 BLOB和 TEXT类型）存储在 B 树节点内的索引记录中，其余部分存储在溢出页上。大于或等于 768 字节的固定长度列被编码为可变长度列，可以在页外存储。

​ 如果列的值是 768 字节或更少，则不使用溢出页面，并且可能会节省一些 I/O，因为该值完全存储在 B 树节点中。这适用于相对较短的BLOB列值，但可能会导致 B-tree 节点填充数据而不是键值，从而降低其效率。具有许多BLOB列的表可能会导致 B-tree 节点变得太满，并且包含的行太少，从而使整个索引的效率低于行较短或列值存储在页外的情况。

COMPACT

​ 使用COMPACT行格式的表将可变长度列值的前 768 个字节（VARCHAR、 VARBINARY和 BLOB和 类型）存储在B 树TEXT节点内的索引记录中，其余的存储在溢出页上。大于或等于 768 字节的固定长度列被编码为可变长度列，可以在页外存储。

DYNAMIC

​ 当使用 , 创建表时 ROW_FORMAT=DYNAMIC，InnoDB 可以完全离页存储 long 可变长度列值（对于 VARCHAR、 VARBINARY和 BLOBand TEXT类型），聚集索引记录仅包含指向溢出页的 20 字节指针。大于或等于 768 字节的固定长度字段被编码为可变长度字段。

​ 列是否存储在页外取决于页面大小和行的总大小。当一行太长时，选择最长的列进行页外存储，直到聚集索引记录适合B 树页面。 小于或等于 40 字节的列存储在行中 TEXT。 BLOB

​ 如果合适的DYNAMIC话，行格式保持了将整行存储在索引节点中的效率（就像 COMPACTandREDUNDANT 格式一样），但是DYNAMIC行格式避免了用长列的大量数据字节填充B树节点的问题。行DYNAMIC格式基于这样一种思想，即如果长数据值的一部分存储在页外，则将整个值存储在页外通常是最有效的。使用DYNAMIC格式，较短的列可能会保留在 B 树节点中，从而最大限度地减少给定行所需的溢出页数。

COMPRESSED

​ 行格式对页外存储使用与DYNAMIC行格式类似的内部细节，DYNAMIC但表和索引数据被压缩并使用更小的页面大小需要额外的存储和性能考虑。使用COMPRESSED行格式，该 KEY_BLOCK_SIZE选项控制在聚集索引中存储多少列数据，以及在溢出页上放置多少。

总结：

1. 当行记录的长度没有超过行记录最大长度时，所有数据都会存储在当前页。

2. 当行记录的长度超过行记录最大长度时，变长列（variable-length column）会选择外部溢出页（overflow page，一般是Uncompressed BLOB Page）进行存储。

   Compact 和 Redundant 行格式：

InnoDB将变长字段(VARCHAR, VARBINARY, BLOB 和 TEXT)的前786字节存储在B+树节点中，其余的数据存放在溢出页(off-page)，如下图：

Compressed 和 Dynamic

​ 新的两种记录格式对于存放BLOB中的数据采用了完全的行溢出的方式。

​ Dynamic行格式，列存储是否放到off-page页，主要取决于行大小，他会把行中最长的一列放到off-page，直到数据页能存放下两行。TEXT或BLOB列<=40bytes时总是存在于数据页。这种方式可以避免compact那样把太多的大列值放到B-tree Node（数据页中只存放20个字节的指针，实际的数据存放在Off Page中，之前的Compact 和 Redundant 两种格式会存放768个字前缀字节）。

 所以在数据库设计的时候，尽量根据实际情况对字段大小进行设置，大字段数据也是会给sql代码效率问题。