MySQL 8.0 表空间机制

墨墨导读：数据是以表空间来维护和存放的。在表空间中包含数据，结构，索引等信息，如何有效使用表空间对于MySQL来说非常重要。

数据库的表空间是用来存储数据的逻辑空间，也是存储数据的最大逻辑单元，其下还有段、区、页等逻辑数据类型。表空间设计是为了提升更高的IO，不同处理数据的解耦，便于管理。

通过表空间来实现对数据文件的灵活控制。目前MySQL8.0 版本Tablespaces，从原有的共享表空间，数据表空间分成如下5中表空间：

• System tablespace
• File-per-table tablespaces
• General tablespaces
• Undo Tablespace
• Temporary Tablespaces

下面逐步了解一下。

1. System tablespace

系统表空间：到目前为止的8.0.23保留下来的就是change buffer的存储区域。如果表在系统表空间创建的，而不是在file-per-table或General表空间中创建的，那么它还包含表和索引数据。之前版本中，系统表空间包含InnoDB数据字典。还包含doublewrite缓冲区存储区域。从MySQL 8.0.20开始 分离出来生成单独的doublewrite文件。

innodb_data_home_dir =/opt/data8.0/dbdata 
innodb_data_file_path = ibdata1:16M;ibdata2:16M:autoextend

备注：autoextend属性只能在innodb_data_file_path设置的最后一个数据文件中指定，对于系统表空间现有系统表空间的大小减少是不支持的。要实现更小的系统表空间，唯一的选择是将数据从备份恢复。Mysql里删除数据是不释放空间的。

所以之前版本的系统表空间是非常大的。为了避免使用大的系统表空间，可使用每个表文件的表空间。File-per-table表空间是默认的表空间类型，在创建InnoDB表时隐式使用。与系统表空间不同，在截断或删除在每个表文件表空间中创建的表之后，磁盘空间返回给操作系统。

2. File-per-table tablespaces

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

#my.cnf配置
[mysqld]
innodb_file_per_table=ON

#命令行方式
mysql> SET GLOBAL innodb_file_per_table=ON;

mysql>CREATE TABLE city(
    ID  int
)ENGINE=Innodb TABLESPACE=innodb_file_per_table;

独立表空间优势：

• truncate或drop在table操作，磁盘空间返回给操作系统。但共享表空间不会释放，只能用于InnoDB数据。换句话说，共享表空间数据文件的大小不会缩小。
• 在共享表空间中的表上执行表复制ALTER TABLE操作因为table-copying动作，会增加表空间所占用的磁盘空间增加。
• 独立表空间执行TRUNCATE表时，性能更好。
• 独立表空间数据文件可以在不同的存储设备上创建，用于I/O优化、空间管理或备份。
• 通过DISCARD/IMPORT TABLESPACE 方式迁移独立表空间 https://dev.mysql.com/doc/refman/8.0/en/innodb-table-import.html
• 独立表空间中创建的表支持与动态和压缩行格式相关的特性，而系统表空间不支持这些特性。
• 存储在独立表空间数据文件中的表可以节省时间，当数据损坏发生时，当备份或二进制日志不可用时，或者当MySQL服务器实例不能重新启动时，成功恢复的机会会增加。独立表空间数据可以通过第三方工具进行分析和数据的抽取。还可以通过单独恢复独立表空间。
• 每个表文件表空间，允许通过监视表空间数据文件的大小来监视表大小。
• 当innodb_flush_method设置为O_DIRECT时，普通的Linux文件系统不允许对单个文件(如共享表空间数据文件)进行并发写操作。因此，当结合此设置使用独立表空间时，可能会有性能改进。
• 共享表空间中的表的大小受到64TB表空间大小限制。相比之下，每个文件-每个表的表空间都有64TB的大小限制，这为各个表提供了足够的空间来增加大小。

缺点：

• 对于独立表空间，每个表可能都有未使用的空间，如果管理不当，可能会导致空间浪费。
• fsync操作在多个文件-每个表的数据文件上执行，而不是在一个共享表空间数据文件上执行。因为fsync操作是针对每个文件的，所以多个表的写操作不能组合在一起，这可能导致fsync操作的总数更高。
• mysqld必须为每个文件-每个表表表空间保留一个打开的文件句柄，如果有很多表独立表空间，这可能会影响性能。
• 当每个表都有自己的数据文件时，需要更多的文件描述符。
• 可能存在碎片，删除表和表扫描性能下降。
• 当删除独立表空间的表时，会扫描缓冲池，对于大型缓冲池来说，这可能需要几秒钟。虽然扫描使用宽的内部锁执行，这可能会延迟其他操作。
• innodb_autoextend_increment变量定义了增量大小，用于在自动扩展的共享表空间文件满时扩展其大小，但它不适用于独立表空间，因为不管innodb_autoextend_increment设置如何，这些表空间文件都会自动扩展。初始的每个表文件表空间扩展量很小，之后扩展量以4MB的增量进行。

3. General tablespaces

通用表空间，通过理解 就是共享表空间的扩展，但只对于业务表。独立于MySQL数据目录的目录中，可以在共享表空间，独立表空间，通用表空间数据转移。可以方便迁移数据，特别是空间不够的情况。

通用表空间的位置是不是随意放的，只能在配置的目录下。这个参数就是innodb_directories参数，注意read only属性。

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

备注： 1. engine目前只能支持innodb引擎。 2. file_block_size基于innodb_pase_size指定默认，无特殊需求不需要指定。

mysql默认页大小是16kb，但启动压缩机制的时候,页的大小是参数key_block_size控制

配置my.cnf如下：

[mysqld]
innodb_directories=/opt/data8.0/tmpdata

mysql>use db4
mysql>CREATE TABLE ext_table(a INT PRIMARY KEY, b CHAR(4)) DATA DIRECTORY='/opt/data8.0/tmpdata';
![image.png](https://oss-emcsprod-public.modb.pro/image/editor/20210320-aba2ea47-9c83-4c83-9a24-4efdbadf2706.png)

底层生成的文件按照db名称 之后 数据ibd文件

mysql>use db4
mysql>CREATE TABLESPACE ext_ts ADD DATAFILE '/opt/data8.0/tmpdata/ext_ts.ibd';

其他操作：

##查看表空间信息
SELECT a.NAME AS space_name, b.NAME AS table_name FROM INFORMATION_SCHEMA.INNODB_TABLESPACES a,        INFORMATION_SCHEMA.INNODB_TABLES b WHERE a.SPACE=b.SPACE and a.SPACE_TYPE='General' ;

##从独立表空间或系统表空间移动到  普通表空间，
ALTER TABLE tbl_name TABLESPACE [=] tablespace_name;


##从普通表空间或移动到系统表空间
ALTER TABLE tbl_name TABLESPACE [=] innodb_system;

##从系统表空间 或 普通表空间移动到 独立表空间。
ALTER TABLE tbl_name TABLESPACE [=] innodb_file_per_table;

##普通表空间重命令
ALTER TABLESPACE s1 RENAME TO s2;

##删除普通表空间，但必须把里边的表移走。
DROP TABLESPACE ts1;

限制内容：

• 不能将已存在的表空间更改为普通表空间。
• 不支持创建临时通用表空间。
• 一般表空间不支持临时表。
• DISCARD and IMPORT TABLESPACE 不支持。
• 可以再以下innodb_data_home_dir和innodb_directories目录下生成普通表空间
• clone不支持普通表空间

4. Temporary Tablespaces

InnoDB使用会话临时表空间和全局临时表空间。

Session临时表空间

当InnoDB被配置为磁盘内部临时表的存储引擎时，会话临时表空间存储了用户创建的临时表和优化器创建的内部临时表。（从MySQL 8.0.16开始，临时表的存储引擎是InnoDB并且 internal_tmp_disk_storage_engine指定。)

• 每个会话临时表空间 最多2个 表空间：一个用于用户创建的临时表，另一个用于优化器创建的内部临时表。
• 当会话断开连接时，它的临时表空间将被截断并释放回池中。
• 当服务器启动时，将创建一个包含10个临时表空间的池。池的大小永远不会缩小，并且表空间会根据需要自动添加到池中。临时表空间池在正常关闭或终止初始化时被删除。其大小为5个page大小，扩展名为.ibt。

• 会话临时表空间保留了40万个空间id。由于每次服务器启动时都会重新创建会话临时表空间池，因此在服务器关闭时，会话临时表空间的空间id不会持久存在，可能会被重用。
• 静态变量innodb_temp_tablespaces_dir临时表空间的位置。

##配置方式
[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:10G
innodb_temp_tablespaces_dir=/opt/data8.0/temp

##查看大小
mysql> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+

##查看临时表空间
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 
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES; 
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO;

5.Undo Tablespaces

Undo表空间包含Undo日志，它是Undo日志记录的集合，其中包含关于如何撤销事务对聚集索引记录的信息。Undo日志段包含在回滚段中。innodb_rollback_segments变量定义了分配给每个undo表空间的回滚段的数量。

mysql> SHOW VARIABLES LIKE '%undo%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| innodb_max_undo_log_size | 1073741824         |
| innodb_undo_directory    | /opt/data8.0/mysql |
| innodb_undo_log_encrypt  | OFF                |
| innodb_undo_log_truncate | ON                 |
| innodb_undo_tablespaces  | 2                  |
+--------------------------+--------------------+

备注:innodb_undo_log_truncate自动截断undo表空间需要至少两个活动的undo表空间，超过innodb_max_undo_log_size变量定义的大小限制的undo表空间将会被截断。

临时表空间操作：

##创建表空间
mysql> CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'file_name.ibu';

##查看表空间
mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG';


##删除表空间
#撤销表空间在被删除之前必须为空。要清空undo表空间，必须首先将undo表空间标记为#inactive，
#这样该表空间就不再用于为新事务分配回滚段。
mysql> ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;  #SET INACTIVE 
mysql> DROP UNDO TABLESPACE tablespace_name;

##undo  truncate查看
mysql> SELECT NAME, SUBSYSTEM, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%truncate%';

##undo运行情况
mysql> SHOW STATUS LIKE 'Innodb_undo_tablespaces%';

• 一个MySQL实例最多支持127个undo表空间。
• MySQL实例初始化时创建的缺省undo表空间(innodb_undo_001和innodb_undo_002)不能被删除。但是，可以使用ALTER UNDO TABLESPACE tablespace_name SET inactive语句使它们处于非活动状态。任何时候都至少需要两个活动的undo表空间来支持自动截断undo表空间。
• innodb_rollback_segments变量定义了分配给每个undo表空间和全局临时表空间的回滚段的数量。
• MySQL关闭后，可以手动删除undo表空间文件，但不建议这样做，因为如果在关闭服务器时存在打开的事务，则在服务器重启后，关闭的undo表空间可能会包含活动的undo日志。
• 加速自动truncate Undo表空间 purge thread负责清空和truncate undo表空间。频率由innodb_purge_rseg_truncate_frequency变量控制 默认情况下128，purge thread调用128次之后。

Undo表空间文件的性能影响

当undo表空间被截断时，undo表空间中的回滚段将被去激活。其他undo表空间中的活动回滚段负责整个系统的负载，这可能会导致性能略有下降。性能下降的程度取决于以下几个因素:

• undo表空间的数目
• undo日志数量
• 撤消表空间大小
• I/O子系统的速度
• 现有的长期运行事务
• 系统负载

在MySQL 8.0.21之前，在undo表空间截断操作期间会执行两个刷新操作。 第一个刷新操作从缓冲池中删除旧的undo表空间页。 第二个刷新操作将新的撤销表空间的初始页写入磁盘。 在一个繁忙的系统上，如果需要删除大量页面，第一次刷新操作可能会暂时影响系统性能。

从MySQL 8.0.21开始，这两个刷新操作都被删除。 最近最少使用时被动释放，完整检查点释放。

在截断操作期间，新的undo页的初始页将被重做记录，而不是刷新到磁盘。

如日常运维中看见undo_space_number_trunc.log（该日志在innodb_log_group_home_dir下）文件，在截断操作期间发生系统故障，临时日志文件允许启动进程识别被截断的undo表空间，并继续执行该操作。

6. 总结

1.从目前表空间的设计来看 innodb引擎的表空间有 fiel-per-table，general ，temporary，其他的表空间有自己的格式支持。

2.General tablespace让用户灵活控制数据，进行 存储空间，迁移的问题。

3.Temporary tablespace提高处理性能。但对MySQL来说尽量少用临时表少用

3.Data dictionary从系统表空间分离之后 系统表空间比较单一只存有change buffer。

分离这些表空间确实提高了文件控制的灵活度，但IO的压力是肯定上去。目前MySQL的使用场景，很多瓶颈出在io上。拆分多文件，应该跟有效的处理好MySQL和IO之间交互问题。

“时间只是一人文字表述和理解，其实动的是地球。”

关于作者 崔虎龙：云和恩墨MySQL技术顾问，长期服务于金融、游戏、物流等行业的数据中心，设计数据存储架构，并熟悉数据中心运营管理的流程及规范，自动化运维等。擅长MySQL、Redis、MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自学通过了MySQL OCP 5.6和MySQL OCP 5.7认证。2年多开发经验，10年数据库运维工作经验，其中专职做MySQL工作8年；曾经担任过项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职务；涉及行业：金融（银行、理财）、物流、游戏、医疗、重工业等。

墨天轮原文链接：https://www.modb.pro/db/47539（复制到浏览器中打开或者点击“阅读原文”立即查看）