Innodb表空间及存储格式

0. MySQL本身的文件：

表结构定义文件，以frm为后缀名，还可以存放视图的定义

1. 存储引擎的文件

1.1 表空间文件

（1）InnoDB将存储的数据按表空间（tablespace）进行存放。 以ibd为后缀名

启用参数innodb_file_per_table之后，每个表会创建一个单独的表空间。这个单独的表空间文件存放：该表的数据、索引和插入缓冲BITMAP等信息，其余信息（如undo信息、插入缓冲索引页、系统事务信息、doublewrite）仍存放在默认的表空间中（ibdata1）。

在本地中存储位置为/var/lib/mysql/mysql

1.2 重做日志文件

（1）当前redo log文件的命名规则为ib_redoXXX或ib_redoXXX_tmp，其中前者为活动重做日志文件，后者表示备用重做日志文件；

（2）InnoDB的redo log文件记录的是关于每个Page的更改情况。

而二进制日志文件仅在事务提交前进行提交，只写磁盘一次。在事务进行过程中，需要不断写入redo log。

2. 表

2.1 索引组织表

（1）索引组织表：InnoDB中，表都是按照主键顺序存放的。即数据以B+树的形式存放。

主键的来源有三种情况：

1. 创建表时，显示给定主键
2. 表中有唯一非空索引（选择建表时第一个定义的非空唯一索引作为主键）
3. 自动生成隐藏的6字节的自增值（GEN_CLUST_INDEX）

_rowid只适用于查看单个列为主键的情况，上述第3种情况也不适用。

（2）在 InnoDB 的内存中维护了一个Fil_system的数据结构来缓存整个文件管理。共有64个Fil_shard。
一个Fil_shard管理一个或多个 Tablespace，每个Tablespace有一个或多个文件（数据Tablespace仅有一个物理文件，而系统表有多个文件）

参考：InnoDB 的文件组织结构

2.2 Tablespace

Tablespace有一个存储元信息数据的page，为前4个page。

（1）Page 0；

XDES Entry为Extent的描述符；FSP_FREE链表管理空闲Extent。

（2）Page 1；

插入缓存的BitMap

（3）Page 2；

关于Segment信息的

（4）Page 3；

保存了聚集索引的root page

2.3 存储结构

（1）段包含数据段、索引段、回滚段等，因为InnoDB是索引组织表，数据段和索引段一样。

（2）一次从磁盘申请4~5个区；区由连续的页组成，其大小总为1MB，一般由64个连续的页组成

（3）在每个段开始时，先用32个页大小的碎片页来存放数据。使用完这些后，才是64个连续页的申请（即一个区的申请）。

这样申请可以基本确保是顺序IO。

（4）页是InnoDB磁盘管理的最小单位，一般为16KB，通过参数page_size设定

（5）按行存储，每页最多允许存放16KB/2 - 200个记录

2.4 行记录格式

InnoDB 到现在为止设计了4种不同类型的行格式 ，分别是 Compact 、 Redundant 、Dynamic 和 Compressed 行格式，mysql5.0之后默认为Compact ，5.7之后默认为Dynamic格式

2.4.1 Compact格式

1. 变长字段长度；只存储VARCHAR列对应数据的实际长度，以列顺序逆序存储；如果某个变长列为NULL，并非存储00，而是直接不存储。
2. NULL标志位；最少占用1字节；是BitMap作用，某位为1，代表对应列为NULL。仍然是按照列的顺序逆序排列
   每超过8个列，就多占用1个字节（这里统计的列是允许存储 NULL 的列，不包括主键列和使用 NOT NULL 修饰的列）
   当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了
3. 记录头信息；占用5字节；是否被删除标志、n_owned表示该槽拥有的记录数、next_record指向下一条记录（可以组成链表形式，其实存储的是到下一个记录的偏移量）等；
4. 主键信息。若没有定义主键，还会增加一个rowid列（隐藏主键，6 字节）
5. 隐藏列数据；每行数据还有两个隐藏列，事务ID列和回滚指针列（分别为6和7字节）。
6. 列数据；为NULL则不占用空间。
   对固定长度的字段，若实际长度小于固定长度，则会进行填充。

2.4.2 Redundant格式

1. 字段长度偏移；倒序记录，每列都需要记录长度（包括隐藏列），前缀和形式。最高位标记字段是否为 NULL，除此外仍遵循长度累加。
2. 记录头信息；固定6个字节，包括列的数量n_field、列对应偏移量使用 1 字节还是 2 字节表示1byte_offs_flag等
   记录的真实数据大小（不包含头信息这些）小于 127 时，每个列对应的偏移量使用 1 字节
   大于 127，小于等于 32767 时，占用 2 字节
   大于 32767 时，发生页溢出，每列占用 2 字节（实际上在8000左右就会溢出，溢出后仍然会使用特殊的）
3. 隐藏列；
4. 列数据；CHAR类型的NULL占用空间，VARCHAR不占用。

2.4.3 行溢出

所有VARCHAR列的长度总和不超过65535。（并且应该包含 storage overhead 开销，即存储变长字段长度 + NULL标志位的字节（如果有的话） + 所有VARCHAR列的长度 + 其他列占用的字节 <= 65535）
溢出数据存放在Uncompress BLOB页中。（指向溢出页的大小为20字节）

每个页至少应该有两条行记录，否则就失去了B+Tree的意义了，变成链表了。因此，如果页只能存放一条记录，就会将数据存放到溢出页中。

2.4.4 Dynamic 和 Compressed 行格式

对存放在BLOB中的数据采用了完全的行溢出方式。

此外，Compressed对存储在其中的行数据以zlib算法进行压缩。

2.4.5 CHAR的存储

对于多字节字符编码的CHAR数据类型的存储，InnoDB在内部将其视为VARCHAR类型。

2.5 数据页格式

页类型为B-tree Node

（1）File Header；Checksum、页偏移、该页最后被修改的LSN、前/后一个页（PAGE_NEXT和PAGE_PREV）；

（2）Page Header；当前页在索引树中的位置、索引ID、槽数量（每个槽2字节）；

PAGE_FREE表示删除的 Record 的链表
PAGE_HEAP_TOP代表空闲空间开始位置的偏移量

（3） Infimum和Supremum；每个数据页都有的两个虚拟行记录，表示记录的边界，Infimum是比该页中任意记录主键值都要小。

1. Infimum 包含 recorder header 由记录头信息和实际存储内容组成（内容就是其名称）。
   通过记录的next_record就可以遍历整个页中的所有记录
   PAGE_NEXT和PAGE_PREV可以遍历所有的页，从而遍历整个表中的数据。

2. Supremum 与上述类似，只不过其偏移量为0；

3. 记录头信息（长度固定不存在变长字段），第2个字节存储的是组内记录数量，最后两个字节存储到第一条记录的偏移量

（4）User Records；是实际数据存放空间
（5）Free Space；链表结构
（6）Page Directory；将记录分为一个个的组，每个槽指向组中最大的主键元素，这些槽组成了目录，方便进行二分查找。

1. 槽是逆序存放的。槽中实际存放的是记录的相对位置。（页开始地址 + 记录的相对位置 = 记录存放位置）
2. 既然数据是按照主键顺序存放的，为什么不直接对记录进行二分查找，而要建立Page Directory？
   通过上节行记录格式可知，一方面记录中含有deleted_flag标志，可能有删除的记录。另一方面，User Records中并非全局有序的，可能部分是按照插入顺序存放的，只是组成的链表是有序的。
   并且槽的数据是按照主键顺序存放的。
3. Slot 的数量限制，防止某个 Slot 中元素过多，导致退化为 O(n) 的时间复杂度。
   第一个 Slot 中的记录只能有 1 条记录，即指向 Infimum 记录。
   最后一个 Slot 中的记录条数范围只能在 1-8 条之间，包含 Supremum 记录；
   剩下的分组中记录条数范围只能在 4-8 条之间。

（7）File Trailer；检查页是否被完整写回磁盘

上述这些都没有提到存储表一共有几列，哪一列是变长类型，列若为固定长度类型，长度为多少在哪存储的？等信息，猜测这些应该是存储在File Space Header页中。

插入过程

1. 会在PAGE_FREE找到最近一个被删除的 Record, 判断大小是否合适（只判断头结点是否合适，如果大于要插入的记录，即可重用，但可能产生碎片）
   这些碎片空间在整个页面快使用完前不会被重新利用，当页面快满时，再插入新纪录后，页面中不能分配一条完整记录空间时，会查看 PAGE_GARBAGE 的空间和剩余可用空间的和是否可用容纳这条记录，如果可用，InnoDB 会尝试重新组织页内的记录，即先开辟一个临时页，把页面内的记录依次插入一遍，之后再把临时页的内容复制到本页面中（比较耗费性能）。

2. 若大小不合适，从PAGE_HEAP_TOP指向的位置分配空间

3. 根据页目录找到其应该插入的 Slot，更改前后 Record 的指针。
   找到主键值比待插入记录大且差值最小的 Slot（即第一个比它大的 Slot）

4. 更新数据页的页目录(Page Directory), 即对应的 REC_NEW_N_OWNED 自增1，假如超过了 PAGE_DIR_SLOT_MAX_N_OWNED 即8个Record, 数据目录需要分裂成两个
   （只有页目录指向的那个记录的n_owned不为0，其余都为0）

2.6 约束

主键的约束名为PRIMARY，UNIQUE索引的约束名与列名相同。

MySQL数据库的外键是即使检查的，在数据的导入操作，会对导入的每一行都进行外键检查，比较耗时，可以在导入过程中忽略外键检查。
InnoDB在外键建立时会自动对该列加一个索引

2.6.1 触发器

触发器的作用是在执行INSERT、DELETE和UPDATE命令之前或之后自动调用SQL命令或存储过程。

CREATE TRIGGER；创建触发器

当前只支持FOR EACH ROW的触发方式。

2.7 视图

（1）视图（View）是一个命名的虚表，可以当表使用，视图中的数据没有实际的物理存储。

（2）可更新视图（updatable view）：用户可以对某些视图进行更新操作，其本质是通过视图的定义来更新基本表。

定义视图时加上WITH CHECK OPTION，会对更新视图插入的数据进行检查，不满足视图定义条件的，会抛出异常。

（3）MySQL不支持物化视图，可以通过触发器实现部分物化视图的功能。

2.8 分区

分区是将一个表或索引分解为更小的部分（逻辑上仍为一个整体，但物理上由不同部分组成）。只支持水平分区（拆分行）。

局部分区索引：一个分区中即存放了数据又存放索引

注意：MySQL8.0之后 分区表功能移到引擎层实现

表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分（不需要整个唯一索引列都是分区列，允许NULL值）。

启用分区后，表不再是一个ibd文件，而由各个分区ibd文件组成。

2.8.1 分区类型

（1）RANGE分区；给定连续区间的值放入分区

1. 当插入一个不在分区中定义的值时，会抛出异常；
2. maxvalue可以视为是正无穷；
3. 主要用于日期列的分区，例如，提取年/月的函数 YEAR(date)，MONTH(data)
4. 查询优化时，可以进行Partition Pruning（分区修建）优化，即只需去变量某个分区，而不用遍历整个表。
   需要注意的是，优化器只能对YEAR()、TO_DAYS()、TO_SECONDS()、UNIX_TIMESTAMP()进行优化选择。
   当前版本只使用EXPALIN即可。
5. 使用VALUES LESS THAN
6. 分区中的NULL值，会将其视为小于任何一个非NULL值。

（2）LIST分区；离散的值

1. 使用VALUES IN
2. 插入多个值时，遇到分区未定义的值，不会数据插入
3. 必须显式指出哪个分区中放入NULL值，否则插入NULL会报错

（3）HASH分区；根据用户自定义的表达式的返回值进行分区，返回值不能为负数

1. PARTITION BY HASH(expr)
   PARTITIONS num，表示分割成分区的数量，如果不写该子句，默认为1
2. 任何分区函数都会将含有NULL值的记录返回0；

（4）KEY分区；根据MySQL提供的哈希函数进行分区

任何分区函数都会将含有NULL值的记录返回0；

（5）COLUMNS分区；可以直接使用非整型的数据进行分区。包括字符串类型、日期类型。与RANGE和LIST搭配使用

如果按照主键进行分区，进行主键的查询，分区是有意义的。对其他索引列查询可能就需要遍历所有分区。

2.8.2 子分区

允许在RANGE和LIST分区上再进行HASH或KEY的子分区（SUBPARTITION）

子分区的名字必须是唯一的