InnoDB 存储引擎＜四＞磁盘文件一

目录

磁盘文件

系统表空间 - System Tablespace 

独⽴表空间 - File-Per-Table Tablespace 

 撤销表空间 - Undo Tablespaces

磁盘文件

1.InnoDB存储引擎包含哪些磁盘⽂件？

分析过程：

从官⽹给出的InnoDB架构图中可以找到答案

InnoDB存储引擎架构链接

解答问题:

InnoDB的磁盘⽂件主要是表空间⽂件和其他⽂件，表空间包括：系统表空间、独⽴表空间、通⽤表空间、临时表空间和撤销表空间；其他⽂件有重做⽇志和双写缓冲区

衍⽣问题:

1. 什么是表空间？

(1)表空间可以理解为MYSQL为了管理数据⽽设计的⼀种数据结构，主要描述的对结构的定义，表空间⽂件是对定义的具体实现，以⽂件的形式存在于磁盘上，以后我们说的表空间指的就是表空间⽂件

(2) InnoDB存储引擎的表空间包括：系统表空间、独⽴表空间、通⽤表空间、临时表空间和撤销表空 间

系统表空间 - System Tablespace 

1.系统表空间的作⽤？

解答问题：

(1)系统表空间存储了MySQL中所有系统表的数据，也包括数据字典；

(2)系统表空间也是变更缓冲区的存储区域，当数据库服务器关闭时，没有合并到缓冲池的⼆级索引修改操作被保存到系统表空间；

(3)在以前的版本中，系统表空间也包含双写缓冲区，从MySQL 8.0.20开始，双写缓冲区从系统表空间中移到单独的⽂件中。

衍⽣问题:

1. 系统表空间⽂件保存在哪⾥？

系统表空间可以对应⼀个或多个数据⽂件，默认情况下，MySQL在 data ⽬录中创建⼀个系统表空间数据⽂件 ibdata1 。系统表空间数据⽂件的⼤⼩和数量由 innodb_data_file_path 启

动选项定义。在Linux中 这个文件是在/var/lib/mysql路径下

2.系统表空间都有哪些可以配置的选项？

分析过程：

1.可以通过 innodb_data_file_path 配置 选项定义，如果没有指定 innodb_data_file_path 的值，则默认创建⼀个⼤⼩可以⾃动扩展的数据⽂件，⽂件名为 ibdata1 ，初始⼤⼩ 12MB 

在这里系统表空间文件不允许动态修改，即MySQL系统/强相关的参数都只能通过配置文件选项进行修改，这样在启动时会自动读取

mysql> SHOW VARIABLES LIKE 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend | # 默认值
+-----------------------+------------------------+
1 row in set, 1 warning (0.04 sec)

2.数据⽂件命名规范的完整语法包括⽂件名、⽂件⼤⼩、⾃动扩展属性和max属性:

file_name:file_size[:autoextend[:max:max_file_size]]

通过在 file_size 值后⾯指定单位 K 、 M 或 G 来设置⽂件⼤⼩，单位为 千字节 、 兆字节 或 千兆字节 。如果以 K 为单位指定⽂件⼤⼩，应设置为1024的倍数。否则，千字节值四舍五⼊到最接近的兆字节 (MB) ，且⽂件⼤⼩⾄少为 12MB 。

 3.指定多个数据⽂件可以使⽤分号 ; 分隔。示例如下：

# mysqld节点
[mysqld]
# ⽂件1名称为：ibdata1 ⼤⼩为50M
# ⽂件2名称为：ibdata2 ⼤⼩为50M，⾃动扩容
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

4.autoextend 和 max 属性只能⽤于最后指定的数据⽂件(即不能在分号之前)，当指定 autoextend 属性时，数据 ⽂件的⼤⼩会根据需要⾃动扩容，默认每次增加 64MB 。可以通过系统变量innodb_autoextend_increment 控制增量的⼤⼩；如果要指定数据⽂件的最⼤容量，在autoextend 后⾯指定 max 属性。注意：只有在明确了解限制磁盘使⽤的情况下才使⽤ max 属性(设置最大容量时一定要考虑好使用场景)

示例如下：

# mysqld节点
[mysqld]
# 初始⼤⼩12M，允许⾃动扩容，最⼤可以扩容到500M
innodb_data_file_path=ibdata1:12M:autoextend:max:500M

 5.系统表空间⽂件默认创建在 data ⽬录下。如果指定其他的⽬录，使⽤innodb_data_home_dir 选项，修改data目录的路径时使用选项datadir

     在修改/指定自定义目录时，一定要检查或设置目标目录的权限，让MySQL可以进行读写操作，否则就会启动失败

  示例：要在名为 myibdata 的⽬录下创建⼀个系统表空间数据⽂件，可以使⽤如下配置:

# mysqld节点
[mysqld]
# 指定innodb数据⽬录
innodb_data_home_dir = /myibdata/
# 配置系统表空间
innodb_data_file_path=ibdata1:50M:autoextend

指定 innodb_data_home_dir 时，必须以斜杠 / 结尾，InnoDB不会⾃动创建⽬录，

所以在启动服务器之前要确保指定的⽬录已经存在，最终通过 innodb_data_home_dir 指定的路径与数据⽂件名组合起来⽣成完整路径，如果 innodb_data_home_dir 不指定，默认值

为" ./ "，即MySQL的数据⽬录 ，上述示例就为/myibdata/ibdata1

6. 如果 innodb_data_file_path 指定⼀个绝对路径，则不会读取 innodb_data_home_dir

的值，系统表空间⽂件根据指定的绝对路径创建，启动服务器之前必须确保指定的⽬录存在

7. 在添加新的数据⽂件时，不要指定现有的⽂件名，InnoDB在启动服务器时会创建并初始化新的数 据⽂件

解答问题

根据实际应⽤场景通过配置对应的系统变量来指定数据⽂件的⼤⼩、名称、数量和其他属性

衍生问题

1. 修改系统表空间配置后什么时候⽣效？

在修改系统表空间配置时，先停⽌MySQL服务，修改完成后，再重新启动MySQL服务之后⽣效。(设置完成后一定要重启)

独⽴表空间 - File-Per-Table Tablespace 

 1.独⽴表空间的作⽤？

解答问题

File-Per-Table 表空间包含单个InnoDB表的数据和索引，默认情况下每张表都对应⼀个表空

间数据⽂件，便于维护，所以称为 File-Per-Table Tablespace

衍⽣问题

1. 独⽴表空间⽂件保存在哪⾥？

File-Per-Table 表空间在 data/database_name/ ⽬录下的 table_name.ibd 表空间

数据⽂件中创建。 .ibd ⽂件与表同名。

例如，表 test_db.t1 的数据⽂件，如下所⽰：

USE test_db;
# 建表
CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(100)) ENGINE = InnoDB;//查询
root@guangchen-vm:/var/lib/mysql/test_db# ll
total 752
drwxr-x--- 2 mysql mysql 4096 10⽉ 26 18:29 ./
drwxr-x--- 8 mysql mysql 4096 10⽉ 26 15:32 ../
-rw-r----- 1 mysql mysql 114688 9⽉ 6 15:30 classes.ibd
-rw-r----- 1 mysql mysql 114688 9⽉ 6 15:30 course.ibd
-rw-r----- 1 mysql mysql 114688 9⽉ 6 15:30 score.ibd
-rw-r----- 1 mysql mysql 114688 9⽉ 6 15:30 student.ibd
-rw-r----- 1 mysql mysql 114688 10⽉ 26 18:29 t1.ibd # t1表对应的表空间数据⽂件

 2.每个表都对应⼀个独⽴表空间吗？ 不是

(1)默认每张表都对应⼀个表空间数据⽂件，但也可以通过系统变量 innodb_file_per_table[=

{OFF|ON}] 控制开启或禁⽤是否为每张表⽣成⼀个独⽴表空间⽂件，如果禁⽤会在系统表空间中创建表；

(2) 可以在选项⽂件中指定 innodb_file_per_table 设置，也可以在运⾏时使⽤ SET GLOBAL

语句设置

# 选项⽂件中的mysqld节点
[mysqld]
innodb_file_per_table=OFF # 禁⽤，强烈不建议# 在运⾏时通过SET GLOBAL 设置
mysql> SET GLOBAL innodb_file_per_table=ON;

 2.独⽴表空间的优点和缺点？

解答问题

1.优点:

(1) 使⽤ TRUNCATE 或 DROP 语句删除 File-Per-Table 表空间中的表后，磁盘空间会返回给操 作系统，从⽽提⾼磁盘利⽤率，⽽共享表空间(⽐如： System Tablespace )则不会回收磁盘空 间，⽽且在共享表空间中这些空间只能被InnoDB表重新使⽤；

(2) 执⾏时 TRUNCATE TABLE 时性能更好；

(3) 可以在其他⽬录或单独的存储设备上创建 File-Per-Table 表空间⽂件的数据⽂件，从⽽达到 I/O优化、空间管理或备份的⽬的；

# 指定DATA directory⼦句，可以在外部⽬录中创建表
CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/external/directory';

(4) ⽀持与 DYNAMIC 和 COMPRESSED ⾏格式，⽽系统表空间不⽀持；

(5)发⽣数据损坏、备份、⼆进制⽇志不可⽤或MySQL服务器实例⽆法重新启动时提⾼成功恢复的机会；

(6)单个表容量⼤⼩限制为 64TB ，所以可以存储更多的数据，⽽共享表空间中的表的总容量为 64TB 。

2.缺点

(1)每个表都可能有未使⽤的空间，这些空间只能由对应的表使⽤，如果管理不当，可能会导致空间浪费；

(2)当每个表都有⾃⼰的数据⽂件，操作系统需要维护更多的⽂件描述符，如果表⾮常多，可能会影响性能；

(3)可能会出现更多的磁盘碎⽚，会影响 DROP TABLE 表扫描性能；

(4)innodb_autoextend_increment 系统变量定义了⾃动扩展共享表空间⽂件的增量⼤⼩，但

对于 File-Per-Table 表空间⽂件不起作⽤， File-Per-Table 表空间⽂件始终⾃动扩展，初始⼤⼩根据表定义分配最⼩的空间，之后以 4MB 为增量进⾏扩容

 撤销表空间 - Undo Tablespaces

1.撤销表空间的作⽤？

前置知识：

每次对数据进行修改时，都会对上个版本的原始数据以撤销日志的方式记录到磁盘上，并保存下来，每个版本组成一个链表，当事务需要回滚时，就会根据记录日志的顺序反向逐一进行撤销操作，最终把数据还原成修改之前的样子

解答问题

撤销表空间中包含撤销⽇志(Undo Log)，撤销⽇志记录了如何撤销事务对聚集索引记录的最新更改(事务的回滚)，通过对事务的回滚，从⽽保证事务ACID特性中的原⼦性。

2.在使⽤MySQL时并没有⼿动创建撤销表空间，它是什么时候被创建的？

分析过程：

MySQL初始化时会在数据⽬录下创建两个默认的撤销表空间，数据⽂件名分别为 undo_001 和 undo_002 ，数据字典中对应undo表空间名称为 innodb_undo_001 和innodb_undo_002 

解答问题

MySQL初始化时会在数据⽬录下创建两个默认的撤销表空间，数据⽂件名分别为 undo_001 和undo_002

衍⽣问题

1.默认的撤销表空间名称和路径是什么？

要查看撤销表空间名称和路径，请查询 INFORMATION_SCHEMA.FILES

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_001，对应的数据⽂
为./undo_001
| innodb_undo_002 | ./undo_002 | # 数据字典中的名称为innodb_undo_002，对应的数据⽂
为./undo_002
+-----------------+------------+
2 rows in set (0.00 sec)

 3.可以⼿动创建撤销表空间吗？

# 语法
CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'file_name.ibu';# ⽰例
CREATE UNDO TABLESPACE tablespace_test ADD DATAFILE 'undo_log_test1.ibu'; 
Query OK, 0 rows affected (0.09 sec)# 查看所有撤销表空间
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 |
| tablespace_test | ./undo_log_test1.ibu | # 新创建的撤销表空间
+-----------------+----------------------+
3 rows in set (0.01 sec)

(3) 使⽤⾃⼰创建的撤销表空间需要注意什么？

(1)通过系统变量 innodb_undo_directory 指定撤销表空间的默认存放路径，如果不指定默认位置为数据⽬录；

(2)撤销表空间⽂件名必须以 .ibu 为扩展名，定义undo表空间⽂件名时如果需要指定路径，必须使⽤绝对路径；不允许指定相对路径，建议使⽤唯⼀的撤销表空间⽂件名，避免在以后移动和复制的过程中发⽣⽂件名冲突；

(3)如果指定其他路径，那么路径必须在 innodb_directories 中定义，以便MySQL扫描并识别；

(4)最多⽀持 127 个 undo 表空间，包括实例初始化时创建的两个默认表空间；

(5)MySQL 8.0.23 开始初始撤销表空间⼤⼩通常为 16MB，并根据服务器负载以 [16MB, 256MB] 的增量进⾏扩容；

注： MySQL 8.0.14 之前版本，额外的撤销表空间通过配置系统变量 innodb_undo_tablespaces 来 创建，取值范围[2, 127]，MySQL 8.0.14 开始，此变量已弃⽤且不再可配置

4.如何删除撤销表空间？

分析过程

1.从 MySQL 8.0.14开始使⽤ CREATE UNDO TABLESPACE 语法创建的撤销表空间可以使⽤ DROP UNDO TABALESPAC 语法删除；

2.撤销表空间在被删除之前必须是空的，要清空撤销表空间，必须⾸先使⽤ ALTER UNDO

TABLESPACE 语法将撤销表空间标记为不活动，以便该表空间不再⽤于其他新的事务；

# 语法

ALTER UNDO TABLESPACE tablespace_name SET INACTIVE ;

3. 在将undo表空间标记为⾮活动后，等待当前undo表空间的事务完成后表空间被截断到初始⼤⼩， 当undo表空间为空，就可以进⾏删除操作

# 语法

DROP UNDO TABLESPACE tablespace_name ;

解答问题

从 MySQL 8.0.14开始使⽤ CREATE UNDO TABLESPACE 语法创建的撤销表空间可以使⽤

DROP UNDO TABALESPAC 语法删除，但要确保撤销表空间在被删除之前必须是空的，具体的操作步骤如下：

1.将撤销表空间标记为不活动

2.等待当前undo表空间的事务完成后表空间被截断到初始⼤⼩

3.执⾏删除操作

衍⽣问题

1.删除撤销表空间的⽰例

# 查询指定的表空间状态
mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACESWHERE NAME LIKE 'tablespace_test';
+-----------------+--------+
| NAME | STATE |
+-----------------+--------+
| tablespace_test | active | # 活动状态
+-----------------+--------+# 设置为不活动状态
mysql> ALTER UNDO TABLESPACE tablespace_test SET INACTIVE;
Query OK, 0 rows affected (0.00 sec)# 再次查询状态
mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
-> WHERE NAME LIKE 'tablespace_test';
+-----------------+-------+
| NAME | STATE |
+-----------------+-------+
| tablespace_test | empty | # 表空间为empty(已经清空)，也有可能是inactive(非活动，等待清空) 
+-----------------+-------+
1 row in set (0.00 sec)# 当表空间状态为empty时，要以进⾏删除操作
mysql> DROP UNDO TABLESPACE tablespace_test;
Query OK, 0 rows affected (0.01 sec)# 查询撤销表空间，发现删除成功
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.01 sec)

2.撤销表空间被置为不活动并且已被截断为初始⼤⼩，这时不想删除了是否可以重新启⽤？

undo表空间状态为空时，可以重新激活，⽅法如下：

# 语法

ALTER undo tablespace tablespace_name SET ACTIVE;

5.如何查看撤销表空间的状态？

mysql> SHOW STATUS LIKE 'Innodb_undo_tablespaces%';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| Innodb_undo_tablespaces_total | 2 | # 撤销表空间的总数
| Innodb_undo_tablespaces_implicit | 2 | # 隐式(InnoDB)创建撤销表空间数量
| Innodb_undo_tablespaces_explicit | 0 | # 显式(⽤⼾)创建撤销表空间数量
| Innodb_undo_tablespaces_active | 2 | # 活动中的撤销表空间数量
+----------------------------------+-------+
4 rows in set (0.01 sec)