一:总结
- 默认的行格式定义 innodb_default_row_format
- 查看当前数据表行格式 SHOW TABLE STATUS 'table';
- 创建表指定行格式 CREATE TABLE table(.......)ROW_FORMAT=DYNAMIC;
- 修改表行格式 ALTER TABLE table ROW_FORMAT=DYNAMIC;
二:为什么MySQL的最大行长度最大是 65535 ?
- InnoDB是以聚簇索引组织数据的,也就是说,数据都存在叶子节点上。
- 那么最大的行长度是 65535(64KB),那不就意味着,叶子节点上,会存在 64KB 的数据。
- 但是,InnoDB 是以 页(page) 为最小单位存储数据的,默认为16KB。
- 如果一个行数据,超过了一页的一半,那么一个页只能容纳一条记录,这样B+Tree在不理想的情况下就变成了双向链表。
- 所以 MySQL 会存在限制,不会让行数据超过 页 的一半。(也就是说一个页最少有两条数据)
- 这里,就和我之前理解的不一样了(每行最大字节数是65535字节,每页默认是16K,最多放两行,不应该是8192字节吗?反过来推算的话是不是说页的大小是128K吗?)
三:理解 65535
- “65535”不是单个varchar(N)中N的最大限制,而是整个表非大字段类型的字段的bytes总合
-
- 根据MySQL官方文档,在进行数据存储的时候,会将数据分为两部分
- 可变长度的类型字段 (VARBINARY, VARCHAR, BLOB and TEXT), 这部分数据会根据 行格式,进行页溢出存储。
- 定长字段的占用空间大小,这些数据会存放在 叶子节点上,数据大小不超过页大小的一半(innodb_page_size)
- 也就是说,MySQL 使用了 页溢出来存储 可变长类型 的数据,那么,页溢出是如何存储的呢?
- 这就是今天要说的 行格式。
四:行格式
- MySQL 共有四种行格式的存储方式 REDUNDANT(冗余行格式) / COMPACT / DYNAMIC(动态行格式) / COMPRESSED(压缩行格式)
- REDUNDANT(冗余行格式) / COMPACT
- 原理
- 如果blob列值长度 <= 768 bytes,不会发生行溢出(page overflow),内容都在数据页(B-tree Node)
- 如果列值长度 > 768字节,那么前768字节依然在数据页,而剩余的则放在溢出页(off-page)
- 如下图:
-
- 问题
- 这对于相对较短的BLOB列值很好用,但是可能导致B树节点填充数据而不是键值,从而降低了效率。
- 具有许多BLOB列的表可能会导致B树节点变得太满,并且包含的行太少,这使得整个索引的效率低于行较短或列值存储在页面外的情况。
- 使用场景
- COMPACT行格式将行存储空间减少了约20%REDUNDANT,但代价是增加了某些操作的CPU使用率。
- 如果在 CPU 允许的情况下,使用 COMPACT。
- DYNAMIC(动态行格式) / COMPRESSED(压缩行格式)
- 原理
- 对blob采用完全行溢出,即聚集索引记录(数据页)只保留20字节的指针,指向真实存放它的溢出段地址:
- 如下图:
-
- 使用场景
- COMPRESSED行格式提供相同的存储特性和功能的 DYNAMIC行格式,但增加了对表和索引数据压缩的支持。
- 如果在 CPU 允许的情况下,使用 COMPRESSED
五:总结
- 数据行并不是全部放在 聚簇索引中的。而是根据行格式的不同/可变长的不同,会额外的存储在其他页中。(页溢出)
- 65535 是对变长字段的总和限制。
- 根据不同的场景选择不同的行格式。
- 参考