MySql系列：6、InnoDB的物理存储结构

家栋

首先上两官方手册上的图，来看看InnoDB整体的内存以及磁盘存储结构（5.7.41和8.0.32）：




1、Tables

5.7版本：

• ibd文件存储数据和索引。
  
• frm存储私有的数据字典信息。
  
• ibdataN存储系统的数据字典信息。
  

8.0版本：

• idb文件存储数据和索引以及私有SDI数据字典信息。
  
• mysql.ibd存储全局的数据字典信息。
  
• 取消了ibdata中的系统数据字典信息。
  

MySQL 8.0.20以前，Doublewrite Buffer存储于系统表空间，MySQL 8.0.20以后，默认存放于系统数据文件存放目录，可以通过innodb_doublewrite_dir指定。
8.0的数据字典改变 1、整个系统的数据字典，不再存储在ibdata1，存储在mysql.ibd里面 2、每个表的表空间自行管理json格式的私有数据字典信息，用于替换Frm。
2、Indexes

2.1、Clustered Indexes（聚簇索引/聚集索引）

聚簇索引的特点：

• 每个InnoDB表都有一个称为聚集索引的特殊索引，用于存储行数据。
  
• 当表上存在PRIMARY KEY时，InnoDB将其用作聚集索引。
  
• 当表上不存在PRIMARY KEY时，InnoDB将使用第一个NOT NULL的列的UNIQUE索引，作为聚集索引。
  
• 如果表没有PRIMARY KEY和合适的UNIQUE索引，InnoDB会自动生成一个名为GEN_CLUST_index的隐藏聚集索引（ROW_ID），6bit，自动增长。
  

聚簇索引的功能：

• IOT，索引组织表。
  
• 针对ID（PK）的查询快速找到记录（有序）。
  

通过聚集索引访问行很快，因为索引搜索直接指向包含行数据的页面。
2.2、Secondary Indexes（辅助索引）

• 聚集索引以外的索引称为辅助索引。
  
• 辅助索引中的每个记录都包含行的主键列，以及为辅助索引指定的列，使用这个主键值来搜索聚集索引中的行。
  
• 如果主键过长，则辅助索引使用更多的空间，因此使用短主键是有利的。
  
• 索引都是有序的。
  

索引覆盖和回表查询：

• 索引覆盖(use index)：查询过程中，直接从索引中查询出了所需要的值。
  
• 回表查询(use where)：查询过程中，不能直接从索引中取到所需要的值，需要回覆盖索引再取值。
  

3、Tablespaces

3.1、System Tablespace（系统表空间）

默认名称为ibdata1，随着版本的迭代，SystemTablespace中存储的内容越来越少。
5.7版本存储以下内容：

• InnoDB Data Dictionary
  
• Doublewrite Buffer
  
• Change Buffer
  
• Undo Logs(可以手工设置独立出来)
  
• Redo Logs(可以手工设置独立出来)
  

8.0版本：

• Change Buffe
  
• Doublewrite Buffer
  

8.0.20以后：

• Change Buffe(其余的均已独立出来)
  

配置建议： 5.7以及以前，设置2-3个，大小建议为512M或1G，最后一个设置为自动扩展。
innodb_data_file_path=ibdata1:512M;ibdata2:512M;ibdata3:512M:autoextend8.0中，设置一个就行，大小建议为512M或1G。
修改方式： 已有的话，第一个初始化的大小必须和原有的大小一致（真正磁盘上的文件大小），比如原有已经使用了300MB，那么设置应该为:
innodb_data_file_path=ibdata1:300M;ibdata2:512M;ibdata3:512M:autoextend需要重启数据库生效。
mysql> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
1 row in set (0.00 sec)

mysql> select @@innodb_autoextend_increment;
+-------------------------------+
| @@innodb_autoextend_increment |
+-------------------------------+
|                            64 |
+-------------------------------+
1 row in set (0.00 sec)3.2、File-Per-Table Tablespaces(独立表空间)

每个独立表空间包含单个InnoDB表的数据和索引，并存储在文件系统中的单个数据文件中。

• 每一个表就是一个表空间，一个表一个文件（分区表可能有多个）。
  
• 由参数innodb_file_per_table控制，默认打开（ON）。
  

在同版本的数据库中，可以利用独立表空间进行大表的快速迁移（同版本）：
1. 锁定源表
lock tables test.t1 read;2. 查看建表语句
show create table test.t1;3. 目标端创建空表
create table test.t1
......4. 单独删除空的表空间文件
alter table test.t1 discard tablespace;5. 将源端ibd文件拷贝到目标端，并赋权
6. 导入表空间
alter table test.t1 import tabeapace;7. 源端解除锁定
unlock tables;3.3、General Tablespaces（通用/常规表空间）

通用表空间是使用CREATE TABLESPACE语法创建的共享InnoDB表空间。
常规表空间提供以下功能：

• 是一个共享表空间，可以存储多个表的数据。
  
• 与独立表空间相比，消耗的内存更少（服务器在表空间的生命周期内将表空间元数据保存在内存中，消耗更少的表空间元数据内存）。
  
• 可以将数据文件存放于MySQL数据目录之外的地方（可以规划分散IO，可以方便的扩容）。
  
• 常规表空间支持所有表行格式和相关功能。
  
• CREATETABLE时，可以使用TABLESPACE选项以在常规表空间、每个表空间的文件或系统表空间中创建表。
  
• 可以使用ALTERTABLE的TABLESPACE选项，在常规表空间、每个表的文件表空间和系统表空间之间移动表。
  
• 不支持创建临时通用表空间，也不支持在通用表空间中创建临时表。
  
• 截断或删除存储在通用表会在ibd数据文件内部产生空闲空间，但是不会释放回操作系统。
  
• 通用表空间不属于任何特定的数据库。
  

由于现阶段在实际生产中使用很少（本人没见过），就不过多赘述，后续可能有详细的测试。
3.4、Undo Tablespaces

5.7以及以前，需要在初始化时进行设置，但是8.0.14以后，可以在线进行设置调整。
添加UndoTablespaces
CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'file_name.ibu';查看UndoTablespaces
mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES
    -> WHERE FILE_TYPE LIKE 'UNDO LOG';
+-----------------+------------+
| TABLESPACE_NAME | FILE_NAME  |
+-----------------+------------+
| innodb_undo_001 | ./undo_001 |
| innodb_undo_002 | ./undo_002 |
+-----------------+------------+
2 rows in set (0.00 sec)删除UndoTablespaces
ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;
DROP UNDO TABLESPACE tablespace_name;undo的自动回收
mysql> SELECT @@innodb_purge_rseg_truncate_frequency;
+----------------------------------------+
| @@innodb_purge_rseg_truncate_frequency |
+----------------------------------------+
|                                    128 |
+----------------------------------------+
SET GLOBAL innodb_purge_rseg_truncate_frequency=32;关于此参数：innodb_purge_rseg_truncate_frequency网上说法众多，MySql手册的原文：
By default, the purgethread looks for undo tablespaces to truncate once every 128 times that purge is invoked.按照我的理解应该是：默认情况下，每128次purge被调用后，就进行一次查找并truncate的操作。
3.5、Temp Tablespaces

3.5.1、Session Temporary Tablespaces

当InnoDB配置为磁盘上内部临时表的存储引擎时，会话临时表空间存储用户创建的临时表和优化器创建的内部临时表(MySQL 8.0.16以后，内部临时表的存储引擎默认为InnoDB)。

• innodb_temp_tablespaces_dir可以指定会话临时表创建的位置，默认为数据目录下的#innodb_temp目录。
  
• INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES查看会话临时表空间。
  
• INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO查看用户在InnoDB实例中创建的临时表。
  

3.5.2、Global Temporary Tablespace

全局临时表空间（ibtmp1）存储对用户创建的临时表所做更改的回滚段。
mysql>  SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE
    -> AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES
    -> WHERE TABLESPACE_NAME = 'innodb_temporary';
+-----------+------------------+--------+--------------+----------------+-----------+--------------+
| FILE_NAME | TABLESPACE_NAME  | ENGINE | INITIAL_SIZE | TotalSizeBytes | DATA_FREE | MAXIMUM_SIZE |
+-----------+------------------+--------+--------------+----------------+-----------+--------------+
| ./ibtmp1  | innodb_temporary | InnoDB |     12582912 |       12582912 |   6291456 |         NULL |
+-----------+------------------+--------+--------------+----------------+-----------+--------------+
1 row in set (0.01 sec)建议初始化的时候设置好，一般2-3个，每个大小512M-1G。
innodb_temp_data_file_path=ibtmp1:512M;ibtmp2:512M;ibtmp3:512M:autoextend
mysql>  SELECT @@innodb_temp_data_file_path;
+------------------------------------------------+
| @@innodb_temp_data_file_path                   |
+------------------------------------------------+
| ibtmp1:512M;ibtmp2:512M;ibtmp3:512M:autoextend |
+------------------------------------------------+
1 row in set (0.00 sec)4、InnoDB Row Formats

MySQL目前有4种行格式：Redundant、Compact、Dynamic、Compressed MySQL 5.6 默认使用 Compact MySQL 5.7+ 默认使用Dynamic
4.1 基础存储结构：

• Mysql的记录按照聚簇索引组织，以链表的形式进行存储。
  
• 存储的单元为页（page），默认大小为16KB。
  
• 每页至少需要存储两条数据。
  
• 每条记录都会有记录头。
  
• 在内部，InnoDB向数据库中存储的每一行添加三个隐藏字段（列）：
  

• DB_TRX_ID：6字节DB_TRX_ID字段指示插入或更新行的最后一个事务的事务标识符。此外，删除在内部被视为更新，其中行中的一个特殊位被设置为将其标记为已删除。
  
• DB_ROLL_PTR：称为滚动指针的7字节DB_ROLL_PTR字段。滚动指针指向写入回滚段的撤消日志记录。如果行已更新，则撤消日志记录包含在更新之前重新生成行内容所需的信息。
  
• DB_ROW_ID：一个6字节的 DB_ROW_ID字段包含一个行ID，该行ID随着插入新行而单调增加。如果InnoDB自动生成聚集索引，则索引包含行ID值。否则，DB_ROW_ID列不会出现在任何索引中（如果有主键，则是主键值）。
  

4.2 REDUNDANT Row Format

最古老的InnoDB行格式。在MySQL 5.0.3之前，它是InnoDB中唯一可用的行格式。从MySQL 5.0.3到MySQL 5.7.8，默认的行格式是COMPACT。从MySQL 5.7.9开始，默认行格式由innodb_default_row_format配置选项定义，该选项的默认设置为DYNAMIC。为了与旧的InnoDB表兼容，您仍然可以指定REDUNDANT行格式。
4.3 COMPACT Row Format

变长字段(记录的长度)列表 + NULL列表 + 记录头信息 + 列值，会额外采用1-2个字节来表示一个字段的长度 记录头信息：使用5个字节来表示，主要包含：该记录是否被删除，记录类型，下一条记录的相对偏移量； 隐藏列： rowId，transactionId，rollPointer
4.4 DYNAMIC Row Format

与Compact行格式很像，差异在于页面溢出的处理上；
4.5 COMPRESSED Row Format

在Dynamic的基础上使用了压缩算法；
4.6 Overflow Page(页溢出)

因为每页16KB，至少存储两行，所以每行大概有8KB的数据；抛开记录头信息等，大致每列超过768B就会产生页溢出；
Compact：
1、会将溢出的数据单独放入一个页；外加20B存储额外页的信息（plus the 20-byte pointer to the externally stored part）
2、索引可以使用前768B
Dynamic：
1、如果页溢出，则使用20B存储整个列信息(列数据都存储在溢出页上)（with the clustered index record containing only a 20-byte pointer to the overflow page）
2、可以使用前3072B字符的索引（--innodb-large-prefix决定）
建议： 5.7+ 版本Dynamic。大字段不要存储到MySQL，最好使用MongDB等相关数据库。



5、DoublewriteBuffer

页面写入过程中存在操作系统、存储子系统或意外的mysqld进程退出，InnoDB可以在崩溃恢复期间从doublewrite缓冲区中找到页面的完整副本。
MySql的page大小为16K，而OS中的io单元为4k的block，若是在写盘的过程中，出现意外crash，那么可能导致数据页不完整，而DWB就是为在发生这种意外的时候，能做crash recover。
默认情况下，为每个缓冲池实例创建两个双写文件：一个刷新列表双写文件和一个LRU列表双写文件。

• 刷新列表双写文件用于从缓冲池刷新列表中刷新的页面。
  
• LRU列表双写文件用于从缓冲池LRU列表刷新的页面。它还包含用于单页刷新的插槽。
  
• 双写文件的最大数量是缓冲池实例数量的两倍。
  

6、其他的一些相关概念

Tablespaces（表空间）
一个数据文件，可以保存一个或多个InnoDB表和相关索引的数据（原文为：A data file that can hold data for one or more InnoDB tables and associated indexes.）我认为这个描述不是很准确，有可能一个表空间中有多个数据文件的情况，而且还有使用裸设备的情况（虽然现在几乎没有人在MySql中使用裸设备）。

• 每个表空间都由数据库页面组成。
  
• MySQL实例中的每个表空间都有相同的页面大小。
  
• 默认情况下，所有表空间的页面大小都为16KB；您可以在创建MySQL实例时通过指定innodb_page_size选项将页面大小减少到8KB或4KB。您也可以将页面大小增加到32KB或64KB。
  

Segments（段）
表空间中的“文件”在InnoDB中称为段（表段）。（这些段与回滚段不同，回滚段实际上包含许多表空间段。）
Extents（区）
聚簇索引的分配单元，通常也是read-ahead的单元。对于大小不超过16KB的页面（64个连续的16KB页面、128个8KB页面或256个4KB页面），页面被分组为大小为1MB的范围。对于32KB的页面大小，数据块大小为2MB。对于64KB的页面大小，数据块大小为4MB。
Pages（页）
一个MySql进行数据读写的基本单位。表示InnoDB在磁盘（数据文件）和内存（缓冲池）之间任何时候传输的最小数据量。一个页面可以包含一行或多行，具体取决于每行中的数据量。如果一行不能完全容纳在一个页面中，InnoDB会设置额外的指针式数据结构，以便将有关该行的信息存储在另一个页中。 当InnoDB批量读取或写入页面集以提高I/O吞吐量时，它一次读取或写入一个数据块（Extents）。