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

家栋 · 发表于 2023-4-10 10:25:00

首先上两官方手册上的图，来看看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 &#39;file_name.ibu&#39;;查看UndoTablespaces
mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES
-> WHERE FILE_TYPE LIKE &#39;UNDO LOG&#39;;
+-----------------+------------+
| 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 = &#39;innodb_temporary&#39;;
+-----------+------------------+--------+--------------+----------------+-----------+--------------+
| 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）。