Innodb表的物理存储结构及特性

Innodb表不同存储格式的物理结构和特性：

当innodb表使用冗余（REDUNDANT）行存储格式有如下特性：

• 1.每个索引记录包含一个6字节的头部，用于将逻辑上连续的记录链接到一起，同时也用于行级锁。
• 2.聚集索引记录包含所有用户定义的列，即包含表中所有字段。此外，还有6字节的事务ID域，和7字节的回滚指针域。
• 3.如果表没有定义主键，每条聚集索引记录则会包含6字节的ROW ID域。
• 4.每个普通索引（ secondary index）包含主键定义的所有字段。
• 5.每条记录都包含指向该记录中每个字段的指针，如果行中所有记录的总长度小于127字节，则指针占用1个字节，否则占用两个字节。这些指针阵列称之为记录目录。这些指针指向的区域就是数据部分。
• 6.Innodb在内部使用定长格式存储定长字符类型的字段，比如char类型，Innodb不会截断varchar类型字段的尾部空格。
• 7.指针区域为每个null字段保留1～2个字节，除此以外，如果字段定义为变长类型，在记录的数据域不占用任何字节，如果定义为定长类型，每个null字段占用固定长度的字节。为null保留定长的空间，能够使字段从null变成not null时，不会造成索引页碎片。

当innodb表使用紧凑（COMPACT ）行存储格式有如下特性：

• 紧凑型行存储格式相比冗余型行存储格式能减低大约20%的存储空间，代价就是某些操作会增加cpu消耗。 如果系统负载是典型的受限于cache命中率和磁盘I/O带宽，那么使用COMPACT很可能会更快；但如果系统负载是罕见的受限于cpu，则使用COMPACT可能会更慢。
• 每个索引记录包含5字节的定长头部，这个可能在变长头部前面，用于将逻辑上连续的记录链接到一起，同时也用于行级锁。
• 记录的可变长头部包含一个位图矢量，用于指示包含包含null的字段。如果一个索引中包含N个定义为null的字段，则位图矢量需要CEILING(N/8)字节。定义为null的字段除了位图矢量占用空间外，其它地方不需要存储空间。可变长头部也包含可变长字段的长度信息，其长度为1～2字节，具体视可变长字段的最大长度而定。
•  如果索引中所有字段都是非null类型，并且都是定长的，则没有可变长头部。
• 对于每个非null的可变长字段，在记录头部为每个字段使用1～2个字节用于记录字段长度信息，如果一个字段的一部分被存储在外部的溢出页上，或者最大长度超过255字节并且实际长度超过127字节则需要2个字节，否则只需要一个字节。对于一个外部存储字段，这2字节用于标识内部存储部分的长度信息和用于指向的外部存储部分的20个字节的指针。
• 记录的头部跟在非null字段数据内容后面。
• 聚集索引记录包含所有用户定义的列，即包含表中所有字段。此外，还有6字节的事务ID域，和7字节的回滚指针域。
• 如果表没有定义主键，每条聚集索引记录则会包含6字节的ROW ID域。
• 每个普通索引（ secondary index）包含主键定义的所有字段；如果主键中存在任何可变长的字段，即使普通索引中定义的字段都是定长字段，每个普通索引记录在头部也会包含可变长度部分，用于记录这些字段的长度。
• 在内部，Innodb使用定长格式存储定长字符类型的字段，比如char类型，Innodb不会截断varchar类型字段的尾部空格。
• 在内部，Innodb试图通过截断字段尾部空格的方式将utf8 CHAR(N) 跟 utf8mb4 CHAR(N) 的字段存储在N个字节中。如果往定义为CHAR(N)的字段中存储超过N字节的值，Innodb会截断值尾部部分，以最小化值。 定义为CHAR(N)的字段最大长度为字段中最大字符占用字节数×N，即相应字段在系统表INFORMATION_SCHEMA.COLUMNS的CHARACTER_OCTET_LENGTH列的值。innodb为CHAR(N)类型字段最小保留N个字节，保留这部分空间在很多情况下可以避免字段进行在线更新时产生索引页碎片。

总结：

1.   Innodb表字段类型的存储会影响数据库的查询和DML性能，一个数据页中能存放更多的由数字类型和短的字符串类型字段组成的记录，这样的记录查询起来速度更快，只需要 InnoDB buffer pool中更小的cache，以及更少的io。
2.   每个innodb表数据被分成数据页存储，这些组成表的数据页由一种叫B-Tree index的数据结构排列组成。表中的数据和普通索引都是由这种数据结构组成。它是根据主键列组织起来的代表整个表的B-Tree index称之为聚集索引。
3.   聚集索引节点包含特定记录的所有字段值，普通索引节点包含主键列和索引列。
4.   可变长字段是个列外，如varchar，blog类型字段，如果这些类型的字段值太长以至于填满整个数据页，则会被分开存储在一个叫溢出页的数据页上。这样的字段称之为离页字段，这些字段被存储在溢出页单链表中，每个字段都有一个独立由一个或多个溢出页组成的单链表；其它情况下（指字段值不会填满数据页），字段的所有值或部分前缀被存储在同一页中，避免浪费存储以及消除需要单独读取一个单独的数据页的情况。
5.   相比较而言， 行存储格式为冗余型（ROW_FORMAT=REDUNDANT）， utf8 和 utf8mb4 ；行存储格式为动态型（ROW_FORMAT=DYNAMIC）以及压缩性（COMPRESSED），CHAR类型的存储方式与紧凑型（ ROW_FORMAT=COMPACT）一样。