MySQL（九）：InnoDB 表空间（Tables）

最新推荐文章于 2024-05-06 20:52:06 发布

IT码客

最新推荐文章于 2024-05-06 20:52:06 发布

阅读量9.8k

点赞数 14

分类专栏：【数据库】MySQL 文章标签： mysql innodb tablespace 表空间

本文链接：https://blog.csdn.net/u010647035/article/details/105009979

版权

【数据库】MySQL 专栏收录该内容

10 篇文章 15 订阅

订阅专栏

本节着重分析一下表空间，通过本节我们将清楚以下几个问题：

1、什么是表空间（Tablespace）？

2、InnoDB 存储引擎有哪些表空间（Tablespace）？

3、InnoDB 存储引擎中的表空间（Tablespace）有什么用？

4、表空间（Tablespace）有哪些组成部分？及各个表空间的作用？

1、简述

InnoDB 表空间（Tablespace）可以看做一个逻辑概念，InnoDB 把数据保存在表空间，本质上是一个或多个磁盘文件组成的虚拟文件系统。InnoDB 表空间不仅仅存储了表和索引，它还保存了回滚日志（redo log）、插入缓冲（insert buffer）、双写缓冲（doublewrite buffer）以及其他内部数据结构。

默认情况下InnoDB存储引擎有一个共享表空间ibdata1，即所有数据都放在这个表空间内。如果我们配置了参数 innodb_file_per_table，则每张表内的数据可以单独放到一个表空间内。其对应的存储文件都放在 innodb_data_home_dir 指定的目录下。

当启用了 innodb_file_per_table 参数选项，需要注意的是，每张表的表空间内存放的只是数据、索引和插入缓冲，其它的数据，如撤销（Undo）信息、系统事务信息、二次写缓冲（double write buffer）等还是存放在原来的共享表空间内。这也就说明了另一个问题：即使在启用了参数 innodb_file_per_table ，共享表空间还是会不断地增加其大小。

2、InnoDB 逻辑存储结构

在这里插入图片描述

从 InnoDB 逻辑存储结构来看，所有的数据都被逻辑的存放在一个空间中，这个空间就叫做表空间（tablespace）。表空间有段（segment）、区（extent）、页（page）组成。

2.1、段（segment）

段(Segment)分为索引段，数据段，回滚段等。其中索引段就是非叶子结点部分，而数据段就是叶子结点部分，回滚段用于数据的回滚和多版本控制。一个段包含256个区(256M大小)。

2.2、区（extent）

区是页的集合，一个区包含64个连续的页，默认大小为 1MB (64*16K)。

2.3、页（page）

页是 InnoDB 管理的最小单位，常见的有 FSP_HDR，INODE, INDEX 等类型。所有页的结构都是一样的，分为文件头(前38字节)，页数据和文件尾(后8字节)。页数据根据页的类型不同而不一样。

每个空间都分为多个页，通常每页16 KiB。空间中的每个页面都分配有一个32位整数页码，通常称为“偏移量”（offset），它实际上只是页面与空间开头的偏移量（对于多文件空间，不一定是文件的偏移量）。因此，页面0位于文件偏移量0，页面1位于文件偏移量16384，依此类推。（InnoDB 的数据限制为64TiB，这实际上是每个空间的限制，这主要是由于页码是32位整数与默认页大小的组合： $2^{32}$ x 16 KiB = 64 TiB。）

如图所示，每个page都有38个字节的FIL header，以及8个字节的FIL trailer（FIL是file的简称）。FIL header包含了一个表示page type的字段，这个字段用来确定这个page数据的结构。

FIL header和trailer示意图如下所示：

在这里插入图片描述

FIL header和trailer包含以下结构：

checksum： 4个字节32位checksum保存在header中。
Offset： 页面初始化后，页面编号就存储在header中。根据从文件中获得的偏移量检查从该字段读取的页码是否与应该匹配的页码相符，这有助于指示读取正确，并且此字段已初始化表示页面已初始化。
Previous/Next Page： 指向此页面类型的逻辑上一页和下一页的指针，被保存在header中。这允许建立页面的双向链接列表，并且它用于INDEX页面以及同一级别的所有页面。
page type： 页面类型存储在标题中。为了解析其余页面数据，这是必需的。页面被分配用于文件空间管理，范围管理，事务系统，数据字典，撤消日志，blob，当然还有索引（表数据）。
space ID： 保存在header中，space的32位整型唯一编号。
Old-style Checksum： 旧格式32位checksum被保存在header中，不过已经被废弃，这块空间被申明为一些指针。
LSN： 页的最后修改的64位日志序列号（LSN）存储在header中，而同一LSN的低32位存储在尾部中。

3、表空间（Tablespace）分类

3.1、系统表空间

系统表空间是变更缓冲区的存储区。如果在系统表空间中创建表，而不是在每个表文件或常规表空间中创建表，则它也可能包含表和索引数据。在以前的MySQL版本中，系统表空间包含InnoDB数据字典。在MySQL 8.0中，InnoDB将元数据存储在MySQL数据字典中。在以前的MySQL版本中，系统表空间还包含doublewrite 缓冲区存储区。自MySQL 8.0.20起，此存储区位于单独的 doublewrite 文件中。

系统表空间可以有一个或多个数据文件。默认情况下，在数据目录中创建一个名为 ibdata1 的系统表空间数据文件。系统表空间数据文件的大小和数量由 innodb_data_file_path 启动项定义。

归纳一下，系统表空间包含数据字典，双写缓冲，变更缓冲区、undo日志，以及在系统表空间创建的表的数据和索引。

系统空间（space 0）在InnoDB中是特殊的，以固定页码分配的相当多页面，以存储对InnoDB操作至关重要的各种信息。由于系统空间是一个与其他空间一样的空间，它的前三个页面包括： FSP_HDR，IBUF_BITMAP 和 INODE页。

在这里插入图片描述

3.1、独占表空间

独立表空间就是每个表单独创建一个 .ibd 文件，该文件存储着该表的索引和数据。由 innodb_file_per_table 变量控制。禁用 innodb_file_per_table 会导致InnoDB在系统表空间中创建表。

innodb_file_per_table 设置可以在配置文件中指定，也可以在运行时使用 SET GLOBAL 语句进行配置。在运行时更改设置需要足够的权限来设置全局系统变量

通过配置文件设置：

[mysqld]
innodb_file_per_table=ON

运行时设置：

mysql> SET GLOBAL innodb_file_per_table=ON;

3.1.1、独占表空间文件结构

在这里插入图片描述

InnoDB 表空间文件 .ibd 初始大小为 96K，而InnoDB默认页大小为 16K，页大小也可以通过 innodb_page_size 配置。在ibd文件中，0-16KB偏移量即为0号数据页，16KB-32KB的为1号数据页，以此类推。页的头尾除了一些元信息外，还有C hecksum 校验值，这些校验值在写入磁盘前计算得到，当从磁盘中读取时，重新计算校验值并与数据页中存储的对比，如果发现不同，则会导致 MySQL 崩溃。

InnoDB页分为INDEX页、Undo页、系统页，IBUF_BITMAP页, INODE页等多种。

第0页是 FSP_HDR 页，主要用于跟踪表空间，空闲链表、碎片页以及区等信息。
第1页是 IBUF_BITMAP 页，保存Change Buffer的位图。
第2页是 INODE 页，用于存储区和单独分配的碎片页信息，包括FULL、FREE、NOT_FULL 等页列表的基础结点信息，这些结点指向的是 FSP_HDR 页中的项，用于记录页的使用情况，它们之间关系如下图所示。
第3页开始是索引页 INDEX(B-tree node)，从 0xc000(每页16K) 开始，后面还有些分配的未使用的页。

3.1.2、独占表空间优点

与共享表空间（例如系统表空间或常规表空间）相比，独占表空间具有以下优点。

1、使用独占表空间，删除表后可以回收所占的磁盘空间。如果使用共享表空间的话，删除表后，共享表空间数据文件的大小不会缩小。

2、对驻留在共享表空间中的表进行 ALTER TABLE 操作可能会增加该表空间占用的磁盘空间量。此类操作可能需要与表中的数据以及索引一样多的额外空间。该空间不会像独占表空间那样释放占用的磁盘。

3、在驻留在独占表空间的表上执行操作时，TRUNCATE TABLE性能更好。

4、可以在单独的存储设备上创建独占表空间数据文件，以进行I/O优化，空间管理或备份。

5、可以从另一个MySQL实例导入独占表空间中的表。

6、在独占表空间中创建的表支持与 DYNAMIC 和 COMPRESSED 行格式相关联的功能，而系统表空间不支持这些功能。

7、当发生数据损坏，备份或二进制日志不可用或无法重新启动MySQL服务器实例时，存储在独占表空间数据文件中的表可以节省时间并提高成功恢复的机会。

8、可以使用MySQL Enterprise Backup快速备份或还原在独占表空间中创建的表，而不会中断其他InnoDB表的使用。这对于具有不同备份计划的表或需要较少备份频率的表很有用。

9、独占表空间允许通过监视表空间数据文件的大小来监视文件系统上的表大小。

10、当 innodb_flush_method 设置为O_DIRECT时，常见的Linux文件系统不允许并发写入单个文件，例如共享表空间数据文件。因此，结合使用此表时，可以使用独占表空间来提高性能。

11、共享表空间中的表的大小受64TB表空间大小限制。相比之下，每个表的每个文件表空间都有64TB的大小限制，这为单个表的大小增加提供了足够的空间。

3.1.3、独占表空间缺点

与共享表空间（例如系统表空间或常规表空间）相比，独占表空间具有以下缺点。

1、使用独占表空间，每个表可能有未使用的空间，只能由同一表的行使用，如果管理不当，则会浪费空间。

2、fsync 操作是对多个独占表空间数据文件而不是单个共享表空间数据文件执行的。由于fsync操作是针对每个文件的，因此无法合并多个表的写操作，这可能导致fsync操作的总数增加。

3、mysqld 必须为每个表文件空间保留一个打开的文件句柄，如果每个表文件空间中有许多表，则可能会影响性能。

4、每个表都有其自己的数据文件时，需要更多的文件描述符。

5、可能存在更多碎片，这可能会妨碍 DROP TABLE和表扫描性能。但是，如果管理碎片，则独占表空间可以提高这些操作的性能。

6、删除驻留在独占表空间中的表时，将扫描缓冲池，对于大型缓冲池可能要花费几秒钟。使用宽泛的内部锁定执行扫描，这可能会延迟其他操作。

7、innodb_autoextend_increment 变量定义了增量大小，用于在自动扩展共享表空间文件已满时扩展其大小，该变量不适用于独占表空间文件，无论innodb_autoextend_increment 设置如何，该文件均会自动扩展。独占表空间的初始文件表扩展很小，此后扩展以4MB为增量。

3.2、常规表空间

常规表空间是使用 CREATE TABLESPACE 语法创建的共享InnoDB表空间.

常规表空间提供了以下功能：

类似于系统表空间，常规表空间是共享表空间，可以存储多个表的数据
常规表空间比独占表空间具有潜在的内存优势。服务器在表空间的生存期内将表空间元数据保留在内存中。与独占表空间中的相同数量的表相比，常规表空间中的多个表元数据消耗的内存更少。
常规表空间数据文件可以放置在相对于MySQL数据目录或独立于MySQL数据目录的目录中，该目录为您提供了许多数据文件和独占表空间的存储管理功能。与独占表空间一样，将数据文件放置在MySQL数据目录之外的功能使您可以分别管理关键表的性能，为特定表设置RAID或DRBD或将表绑定到特定磁盘。
常规表空间支持所有表行格式和相关功能。
TABLESPACE选项可与CREATE TABLE一起使用，以在常规表空间，独占表空间或系统表空间中创建表。
TABLESPACE选项可与ALTER TABLE一起使用，以在常规表空间，独占表空间和系统表空间之间移动表。以前，不可能将表从独占表空间移动到系统表空间。使用常规表空间功能，您现在可以这样做。

3.2.1、创建常规表空间

常规表空间是使用CREATE TABLESPACE语法创建的。

CREATE TABLESPACE tablespace_name
    [ADD DATAFILE 'file_name']
    [FILE_BLOCK_SIZE = value]
        [ENGINE [=] engine_name]

常规表空间可以在数据目录中或在其外部创建。为避免与隐式创建的独占表空间冲突，不支持在数据目录下的子目录中创建常规表空间。在数据目录之外创建常规表空间时，该目录必须存在并且在创建表空间之前InnoDB必须知道。要使InnoDB知道未知目录，请将目录添加到 innodb_directories 参数值。 innodb_directories 是只读的启动选项。配置它需要重新启动服务器。

示例：

1、在数据目录中创建常规表空间：

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;

或

mysql> CREATE TABLESPACE `ts1` Engine=InnoDB;

从MySQL 8.0.14开始，ADD DATAFILE 子句是可选的，在此之前是必需的。如果在创建表空间时未指定ADD DATAFILE 子句，则会隐式创建具有唯一文件名的表空间数据文件。唯一文件名是128位UUID，格式为五组十六进制数字，中间用破折号（aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeeeee）分隔。常规表空间数据文件包括.ibd 文件扩展名。在复制环境中，在主服务器上创建的数据文件名与在从属服务器上创建的数据文件名不同。

2、在数据目录之外的目录中创建常规表空间：

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/my/tablespace/directory/ts1.ibd' Engine=InnoDB;

您可以指定相对于数据目录的路径，只要表空间目录不在数据目录下即可。在此示例中，my_tablespace目录与数据目录处于同一级别：

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '../my_tablespace/ts1.ibd' Engine=InnoDB;

注意：ENGINE = InnoDB子句必须定义为CREATE TABLESPACE语句的一部分，或者InnoDB必须定义为默认存储引擎（default_storage_engine = InnoDB）。

3.2.2、将表添加到常规表空间

创建InnoDB常规表空间后，可以使用 CREATE TABLE tbl_name … TABLESPACE [=] tablespace_name 或 ALTER TABLE tbl_name TABLESPACE [=] tablespace_name 将表添加到表空间，如以下示例所示：

CREATE TABLE:

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;

ALTER TABLE:

mysql> ALTER TABLE t2 TABLESPACE ts1;

注意：在MySQL 5.7.24中弃用了将表分区添加到共享表空间的支持，在MySQL 8.0.13中不再支持。共享表空间包括InnoDB系统表空间和常规表空间。

3.2.3、常规表空间支持的行格式

常规表空间支持所有表行格式（冗余，紧凑，动态，压缩：【REDUNDANT, COMPACT, DYNAMIC, COMPRESSED】），但要注意的是，由于物理页大小不同，压缩表和未压缩表不能在同一常规表空间中共存。

要使常规表空间包含压缩表（ROW_FORMAT = COMPRESSED），必须指定 FILE_BLOCK_SIZE，并且 FILE_BLOCK_SIZE 值必须是相对于 innodb_page_size 值的有效压缩页大小。另外，压缩表的物理页面大小（KEY_BLOCK_SIZE）必须等于 FILE_BLOCK_SIZE / 1024。例如，如果 innodb_page_size = 16KB 且 FILE_BLOCK_SIZE = 8K，则表的 KEY_BLOCK_SIZE 必须为8。

下表显示了允许的 innodb_page_size，FILE_BLOCK_SIZE 和 KEY_BLOCK_SIZE 组合。 FILE_BLOCK_SIZE 值也可以以字节为单位指定。要确定给定FILE_BLOCK_SIZE的有效KEY_BLOCK_SIZE值，请将FILE_BLOCK_SIZE值除以1024。表压缩不支持32K和64K InnoDB页面大小。

在这里插入图片描述

3.2.4、常规表空间限制

1、生成的或现有的表空间无法更改为常规表空间。

2、不支持创建临时常规表空间。

3、常规表空间不支持临时表。

4、与系统表空间类似，存储在常规表空间中的表被删除会在常规表空间 .ibd 数据文件内部创建可用空间，该空闲空间仅可用于新的InnoDB数据。独占表空间的空间不会释放回操作系统。

此外，对驻留在共享表空间（常规表空间或系统表空间）中的表进行表复制ALTER TABLE操作可能会增加表空间使用的空间量。此类操作需要与表中的数据以及索引一样多的额外空间。复制表ALTER TABLE操作所需的额外空间不会像释放独占表空间那样释放回操作系统。

5、属于常规表空间的表不支持ALTER TABLE … DISCARD TABLESPACE和ALTER TABLE … IMPORT TABLESPACE。

6、在MySQL 5.7.24中弃用了将表分区放置在常规表空间中的支持，在MySQL 8.0.13中已删除。

7、在主服务器和从服务器位于同一主机上的复制环境中，不支持ADD DATAFILE子句，因为它将导致主服务器和从服务器在同一位置创建同名的表空间，这不支持。但是，如果省略了ADD DATAFILE子句，则在数据目录中创建表空间，并使用允许的唯一生成的文件名。

3.3、Undo 表空间

Undo 表空间包含 undo log，Undo 表空间是撤消日志记录的集合，其中包含有关如何通过事务撤消对聚集索引记录的最新更改的信息。撤消日志存在于撤消日志段中，撤消日志段中包含撤消日志段。 innodb_rollback_segments 变量定义分配给每个撤消表空间的回滚段数。

初始化MySQL实例时，会创建两个默认的Undo 表空间。默认的Undo 表空间是在初始化时创建的，以提供回滚段的位置，这些段必须存在才能接受SQL语句。至少需要两个Undo 表空间才能支持Undo 表空间的自动截断。

默认的Undo 表空间在 innodb_undo_directory 变量定义的位置中创建。如果未定义 innodb_undo_directory 变量，则会在数据目录中创建默认的Undo 表空间。默认的Undo 表空间数据文件名为 undo_001 和 undo_002。数据字典中定义的相应Undo 表空间名称是 innodb_undo_001 和 innodb_undo_002。

Undo 表空间数据文件的初始大小取决于 innodb_page_size 值。对于默认的16KB页面大小，初始撤消表空间文件大小为10MiB。对于4KB，8KB，32KB和64KB页面大小，初始Undo 表空间文件大小分别为7MiB，8MiB，20MiB和40MiB。

关于Undo 表空间更多操作参见：https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html

3.4、临时表空间

InnoDB 临时表空间包括会话临时表空间和全局临时表空间

3.4.1、会话临时表空间

当将InnoDB配置为磁盘内部临时表的存储引擎时，会话临时表空间将存储用户创建的临时表和由优化程序创建的内部临时表。从MySQL 8.0.16开始，用于磁盘内部临时表的存储引擎始终是InnoDB。（以前，存储引擎由internal_tmp_disk_storage_engine的值确定。）

在创建磁盘临时表的第一个请求上，会话临时表空间从临时表空间池分配给会话。最多可将两个表空间分配给一个会话，一个用于用户创建的临时表，另一个用于由优化程序创建的内部临时表。分配给会话的临时表空间用于该会话创建的所有磁盘上的临时表。当会话断开连接时，其临时表空间将被删除并释放回池中。启动服务器时，将创建10个临时表空间的池。池的大小永远不会缩小，并且表空间会根据需要自动添加到池中。在正常关闭或初始化中止时，将删除临时表空间池。会话临时表空间文件在创建时大小为5页，并具有.ibt文件扩展名。

为会话临时表空间保留了40万个空间ID。因为每次启动服务器时都会重新创建会话临时表空间池，所以在关闭服务器时，会话临时表空间的空间ID不会保留，并且可以重新使用。

innodb_temp_tablespaces_dir 变量定义了创建会话临时表空间的位置。默认位置是数据目录中的#innodb_temp目录。如果无法创建临时表空间池，则拒绝启动。

shell> cd BASEDIR/data/#innodb_temp
shell> ls
temp_10.ibt  temp_2.ibt  temp_4.ibt  temp_6.ibt  temp_8.ibt
temp_1.ibt   temp_3.ibt  temp_5.ibt  temp_7.ibt  temp_9.ibt

在基于语句的复制（SBR）模式下，在从属服务器上创建的临时表驻留在单个会话临时表空间中，该表空间仅在MySQL服务器关闭时才被中断。

INNODB_SESSION_TEMP_TABLESPACES 表提供有关会话临时表空间的元数据。

INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO 表提供有关在InnoDB实例中处于活动状态的用户创建的临时表的元数据。

3.4.2、全局临时表空间

全局临时表空间（ibtmp1）存储回滚段，以对用户创建的临时表进行更改。

innodb_temp_data_file_path 变量定义全局临时表空间数据文件的相对路径，名称，大小和属性。如果没有为innodb_temp_data_file_path指定值，则默认行为是在 innodb_data_home_dir 目录中创建一个名为ibtmp1的自动扩展数据文件。初始文件大小略大于12MB。

全局临时表空间在正常关闭或初始化中止时被删除，并在每次启动服务器时重新创建。全局临时表空间在创建时会接收动态生成的空间ID。如果无法创建全局临时表空间，则拒绝启动。如果服务器意外停止，则不会删除全局临时表空间。在这种情况下，数据库管理员可以手动删除全局临时表空间或重新启动MySQL服务器。重新启动MySQL服务器会自动删除并重新创建全局临时表空间。

全局临时表空间不能驻留在原始设备上。

INFORMATION_SCHEMA.FILES提供有关全局临时表空间的元数据。发出与此查询类似的查询以查看全局临时表空间元数据：

mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G

默认情况下，全局临时表空间数据文件是自动扩展的，并根据需要增加大小。要确定全局临时表空间数据文件是否正在自动扩展，请检查 innodb_temp_data_file_path 设置：

mysql> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+

要检查全局临时表空间数据文件的大小，请使用与此类似的查询来查询INFORMATION_SCHEMA.FILES表：

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'\G
*************************** 1. row ***************************
      FILE_NAME: ./ibtmp1
TABLESPACE_NAME: innodb_temporary
         ENGINE: InnoDB
   INITIAL_SIZE: 12582912
 TotalSizeBytes: 12582912
      DATA_FREE: 6291456
   MAXIMUM_SIZE: NULL

TotalSizeBytes 显示全局临时表空间数据文件的当前大小。

检查操作系统上的全局临时表空间数据文件大小。全局临时表空间数据文件位于 innodb_temp_data_file_path 变量定义的目录中。

要回收全局临时表空间数据文件占用的磁盘空间，请重新启动MySQL服务器。重新启动服务器会根据 innodb_temp_data_file_path 定义的属性删除并重新创建全局临时表空间数据文件。

要限制全局临时表空间数据文件的大小，请配置innodb_temp_data_file_path以指定最大文件大小。例如：

[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M

配置 innodb_temp_data_file_path 要求重新启动服务器。

4、参考文献

IT码客

关注

14
点赞
踩
61

收藏

觉得还不错? 一键收藏
2
评论
MySQL（九）：InnoDB 表空间（Tables）

本节着重分析一下表空间，通过本节我们将清楚以下几个问题：1、什么是表空间（Tablespace）？2、InnoDB 存储引擎有哪些表空间（Tablespace）？3、InnoDB 存储引擎中的表空间（Tablespace）有什么用？4、表空间（Tablespace）有哪些组成部分？及各个表空间的作用？文章目录1、简述2、InnoDB 逻辑存储结构2.1、段（segment）2.2、区（e...
复制链接

扫一扫