目录

通⽤表空间 - General Tablespace

临时表空间 - Temporary Tablespaces 

---

通⽤表空间 - General Tablespace

对应磁盘上的文件需要用户手动创建

1.通⽤表空间的作⽤和特性？

 解答问题：

1.作用：可以把数据量比较小且强相关的表，都用一个通用表空间管理起来

2.通⽤表空间是使⽤ C REATE tablespace 语法创建的共享InnoDB表空间

3.通⽤表空间能够存储多个表的数据，与系统表空间类似也是共享表空间；

4.服务器运⾏时会把表空间元数据保存在内存中，在表的数量相同的情况下，通⽤表空间⽐独⽴表空间的数量更少，所以消耗的内存也就更少；

5.数据⽂件可以放置在数据⽬录或数据⽬录之外的其他位置，对于单独管理关键表⾮常有⽤

6.⽀持所有的表格式和⾏格式的相关特性；

2.怎么创建通⽤表空间？

分析过程：

创建通⽤表空间可以使⽤ CREATE TABLESPACE 语法。 

注意： tablespace_name 表空间名区分⼤⼩写

解答问题：

创建通⽤表空间可以使⽤ CREATE TABLESPACE 语法，与创建表类似，语句⾥⽤

TABLESPACE 关键字指明创建的是表空间

衍⽣问题

1. 创建通⽤表空间的⽰例

(1) ⽰例：在 data ⽬录下创建通⽤表空间

# 指定表空间⽂件名
CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
# 或使⽤随机⽂件名
CREATE TABLESPACE `ts1` Engine=InnoDB;

(2)ADD DATAFILE ⼦句在MySQL 8.0.14及以后的版本是可选的，之前是必需的。如果没有指定ADD DATAFILE ⼦句，则⾃动创建⼀个以 UUID 为⽂件名的表空间数据⽂件，通⽤表空间数据⽂件以 .ibd 为扩展名。

# 在数据⽬录中查看通⽤表空间数据⽂件
root@guangchen-vm:/var/lib/mysql# ll *.ibd
# 没有指定ADD DATAFILE⼦句，随机⽣成的通⽤表空间数据⽂件
-rw-r----- 1 mysql mysql 114688 10⽉ 30 10:45 57db20a7-76ce-11ee-82d2-
000c29049ee8.ibd
# 系统⾃带，存放mysql系统表和数据字典表的表空间数据⽂件
-rw-r----- 1 mysql mysql 27262976 10⽉ 30 10:45 mysql.ibd
# 使⽤了ADD DATAFILE⼦句，使⽤指定的通⽤表空间数据⽂件
-rw-r----- 1 mysql mysql 114688 10⽉ 30 10:44 ts1.ibd

 2.创建通⽤表空间时要注意什么？

(1)我们每创建一个数据库，都会在数据目录中生成一个与数据库名相同的子目录，因此为了避免自己在数据目录中创建的子目录与以后要创建的数据库重名，所以不允许把通用表空间创建在数据目录下的子目录中

(2)可以在数据⽬录中创建通⽤表空间，也可以在数据⽬录之外创建通⽤表空间。为避免与隐式创建的独⽴表⽂件表空间冲突，不⽀持在data⽬录的⼦⽬录中创建通⽤表空间。当在数据⽬录之外创建通⽤表空间时，该⽬录必须存在，并且必须在创建表空间之前让InnoDB识别，要使⽤⾃定义的⽬录可以通过系统 innodb_directories 指定。 Innodb_directories 是⼀个只读启动选项，配置后需要重新启动服务器。

(3)Innodb_directories 默认值是 NULL ，同时 innodb_data_home_dir ,

innodb_undo_directory 和 datadir 定义的⽬录会被附加到 innodb_directories 参

数值中，在InnoDB启动时会⾃动被识别(包括⼦⽬录)，⼿动指定⽬录的⽅式，如下所⽰：

# 通过启动选项指定，多个⽬录⽤分号隔开
mysqld --innodb-directories="directory_path_1;directory_path_2"# 通过选项⽂件指定，多个⽬录⽤分号隔开
[mysqld]
innodb_directories="directory_path_1;directory_path_2"

 (4)⽰例：不能在数据⽬录的⼦⽬录下创建通⽤表空间

# 在数据⽬录下创建⼦⽬录
root@guangchen-vm:/var/lib/mysql# mkdir my_tablespace
root@guangchen-vm:/var/lib/mysql# ll
total 92892
# ... 省略
drwxr-xr-x 2 root root 4096 10⽉ 30 10:59 my_tablespace/
# ... 省略CREATE TABLESPACE `ts3` ADD DATAFILE './my_tablespace/ts3.ibd' Engine=InnoDB;
# 提⽰错误，因为⼦⽬录名有可能和数据库名重名
ERROR 3121 (HY000): The DATAFILE location cannot be under the datadir.

 InnoDB不是默认存储引擎的情况下，必须指定 ENGINE = InnoDB ⼦句

3.如何向通⽤表空间中添加表？

前置知识:

通⽤表空间能够存储多个表的数据

分析过程

⽰例：向通⽤表空间中添加表，在创建表时使⽤ TABLESPACE ⼦句指定通⽤表空间即可

# 在ts1表空间中添加t1表
CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;
Query OK, 0 rows affected (0.00 sec)# 在ts1表空间中添加t2表
CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE ts1;
Query OK, 0 rows affected (0.02 sec)# 把t1表移动到ts1表空间
ALTER TABLE t1 TABLESPACE ts1;

 解答问题：

⾸先创建通⽤表空间，之后使⽤ CREATE 语句创建表时通过 TABLESPACE ⼦句指定通⽤表空间，语句执⾏成功后即在指定的通⽤表空间下创建了表

4.怎么删除通⽤表空间？

分析过程：

(1)DROP TABLESPACE 语句⽤于删除⼀个InnoDB通⽤表空间，在删除通⽤表空间之前，必须将所有表从表空间中删除，如果表空间不为空，将返回错误。

查询通⽤表空间中的表，可以使⽤下⾯的语句：

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.NAME LIKE 'ts1';
+------------+------------+
| space_name | table_name |
+------------+------------+
| ts1 | test_db/t2 |
| ts1 | test_db/t3 |
| ts1 | test_db/t1 |
+------------+------------+
3 rows in set (0.02 sec)

 (2)⽰例：⼀个完整的通⽤表空间删除流程

# 创建通⽤表空间ts1
CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;# 在通⽤表空间中创建t1表
CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 Engine=InnoDB;# 删除t1表
DROP TABLE t1;# 删除通⽤表空间ts1
DROP TABLESPACE ts1;

 解答问题:

可以使⽤ DROP TABLESPACE 语句⽤于删除⼀个通⽤表空间，与删除表类似，语句⾥⽤

TABLESPACE 关键字指明删除的是表空间

衍⽣问题

1. 使⽤通⽤表空间时要注意什么？

(1)使⽤ TRUNCATE 或 DROP 语句截断或删除表时，通⽤表空间的空闲容量并不会释放，并且只能⽤于新的InnoDB表；

(2)通⽤表空间不属于任何数据库，使⽤ DROP DATABASE 操作数据库和属于该数据库所有的表时，并不会删除通⽤表空间。

(3)tablespace_name 表空间名区分⼤⼩写

临时表空间 - Temporary Tablespaces 

1.什么是临时表？

解答问题:

临时表存储的是临时数据，不能永久的存储数据，⼀般在复杂的查询或计算过程中⽤来存储过渡的中间结果，MySQL在执⾏查询与计算的过程中会⾃动⽣成临时表，⽐如表连接查询时得到的结果集就是⼀张临时表，因为结果中可能包含多个表中的字段并没有⼀张真实的表与之完全对应

衍⽣问题:

1.除了系统⾃动创建的临时表，可以⼿动创建临时表吗？

(1)⽤⼾可以通过使⽤ CREATE TEMPORARY TABLE 语句⼿动创建临时表

(2)⽤⼾创建的临时表也称为外部临时表；MySQL在执⾏查询与计算的过程中⾃动⽣成的临时表称为内部临时表。

2.什么是外部临时表？

分析过程:

(1) 使⽤ CREATE TEMPORARY TABLE 语句创建的临时表是外部临时表

# 创建⼀个名称为t1的临时表
CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;

 (2)通过 INNODB_TEMP_TABLE_INFO 查询临时表元数据。

SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
*************************** 1. row ***************************TABLE_ID: 194 # 临时表的表IDNAME: #sql7a79_1_0 # 临时表的名称N_COLS: 4 # 临时表中的列数(包含3个默认隐藏列)SPACE: 182 # 临时表所在的临时表空间ID

(3)TEMPORARY 表只在当前会话中可⻅，并且在会话关闭时⾃动删除。这意味着两个不同的会话可以使⽤相同的临时表名，⽽不会相互冲突，临时表也不会与已有的⾮临时表名冲突，如果创建了与现有表同名的临时表，则现有表被隐藏，直到临时表被删除。

(4)重启MySQL服务器后，再次查询临时表信息，得到空集合

SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
Empty set (0.00 sec)

解答问题：

使⽤ CREATE TEMPORARY TABLE 语句创建的临时表是外部临时表，表只在当前会话中可⻅，并且在会话关闭时⾃动删除

3.什么是内部临时表？

分析过程：

1.由服务器⾃动创建的临时表是内部临时表

2.服务器在以下情况会⾃动创建临时表，这个过程⽤⼾不能直接控制：

 (1)使⽤ UNION 语句合并查询结果

 (2)对视图时的⼀些操作，⽐如使⽤ UNION 或聚合函数

 (3)使⽤⼦查询

 (4)使⽤ DISTINCT 和 ORDER BY 的查询可能需要⼀个临时表

 (5)使⽤ INSERT…SELECT 语句向表中写⼊数据时，需要先⽤⼀个内部临时表来保存   (6) SELECT 语句查询出来的⾏，然后将这些⾏插⼊到⽬标表中

 (7)使⽤ COUNT(DISTINCT) 和 GROUP_CONCAT() 表达式时

 (8)使⽤窗⼝函数时

解答问题

由服务器⾃动创建的临时表是内部临时表，通常MySQL在执⾏查询与计算的过程中会⾃动⽣成的内部临时表

衍⽣问题

要确定SQL语句是否需要临时表，使⽤ EXPLAIN 并检查 Extra 列

4.临时表都有哪些设置？

分析过程

1.系统变量 internal_tmp_mem_storage_engine ⽤于指定内存中内部临时表的存储引擎，值

为 TempTable (默认值)或 MEMORY ；

2.TempTable 存储引擎为 VARCHAR 和 VARBINARY 列以及其他⼆进制⼤对象类型进⾏了优化；

3. 从MySQL 8.0.28开始 tmp_table_size 定义了由 TempTable 存储引擎创建的单个内部临时表 允许使⽤内存的最⼤值，当达到 tmp_table_size 限制时，MySQL⾃动将内存中的内部临时表 转换为磁盘上的InnoDB内部临时表。 tmp_table_size 的默认值是 16MB ；

4.系统变量 temptable_max_ram 定义 TempTable 存储引擎创建的所有临时表可以使⽤的最⼤内存，默认为 1GB ，超出限制后将内存中的内部临时表转换为磁盘上内部临时表；

5.当内存临时表使⽤内存存储引擎 internal_tmp_mem_storage_engine=MEMORY 时，系统

变量 max_heap_table_size 可以限制内存内部临时表的最⼤⾏数，默认 16777216 ，

6.内存存储引擎临时表变得太⼤，MySQL会⾃动将其转换为磁盘上的临时表，内存中临时表的⼤⼩由 tmp_table_size 和 max_heap_table_size 这两个系统变量中最⼩的值决定。

解答问题

通过配置对应的系统变量来指定临时表使⽤的存储引擎、使⽤内存的⼤⼩、表中的最⼤⾏数等选项。

5.临时表中的数据存在哪⾥？

分析过程:

磁盘上的临时表数据存储在临时表空间中，MySQL8.0版本中磁盘上的临时表存储引擎⽀持

InnoDB ，分为两种类型分别是：

    会话临时表空间( session temporary tablespaces )

    全局临时表空间( global temporary tablespace )。

 1.会话临时表空间的作⽤？

磁盘上的会话临时表空间存储由⽤⼾创建的外部临时表和优化器创建的内部临时表；

2.会话临时表空间的数据存在哪⾥？

(1)当MySQL接收到第⼀个创建磁盘临时表的请求时，从临时表空间池中分配会话临时表空间；⼀个会话最多分配两个表空间，⼀个⽤于⽤⼾创建的临时表，另⼀个⽤于优化器创建的内部临时表。会话的临时表空间⽤于存储会话创建的所有磁盘临时表，当会话断开连接时，临时表空间将被截断并释放回池中；

(2)服务器启动时会创建⼀个包含 10 个临时表空间的临时表空间池，表空间会根据需要⾃动添加到池中，临时表空间池在MySQL正常关闭或中⽌初始化时被删除；

(3)会话临时表空间⽂件扩展名为 .ibt ；

(4)系统变量 innodb_temp_tablespaces_dir 可以指定会话临时表空间的位置。默认数据⽬录

下的 #innodb_temp ⽬录(开头的 # 号是为了避免与数据库⽬录命名冲突)，如果⽆法创建临时表空间池，服务器则拒绝启动；指定自定义路径时，需要注意目标路径的权限

# 数据⽬录下的临时表空间⽬录
root@guangchen-vm:/# cd /var/lib/mysql/#innodb_temp 
root@guangchen-vm:/var/lib/mysql# 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

3.全局临时表空间的作⽤？

全局临时表空间存储对⽤⼾创建的临时表所做的更改，以便以后回滚操作

4. 全局临时表空间的数据存在哪⾥？

(1)系统变量 innodb_temp_data_file_path 指定了全局临时表空间数据⽂件的相对路径、名

称、⼤⼩和属性。如果没有指定，则默认在系统表空间⽬录(系统变量

innodb_data_home_dir 指定的⽬录)中创建，默认名为 ibtmp1 ，初始⽂件⼤⼩略⼤于 12MB 

# 数据⽬录
root@guangchen-vm:/var/lib/mysql# ll
total 92932
# ... 省略
-rw-r----- 1 mysql mysql 12582912 10⽉ 30 12:08 ibtmp1 # 全局临时表空间
# ... 省略

 (2)全局临时表空间在正常关闭或中⽌初始化时被删除，并在每次启动服务器时重新创建，如果⽆法创建全局临时表空间，则拒绝启动；如果服务器意外停⽌，重启服务器时会⾃动删除并重新创建全局临时表空间。

解答问题

磁盘上的临时表数据存储在临时表空间中，临时表空间分为两种分别是：

1.会话临时表空间( session temporary tablespaces )，默认数据⽬录下的

#innodb_temp ⽬录中

2.全局临时表空间( global temporary tablespace )，默认在数据⽬录下中创建，名为

ibtmp1

6.怎么查看全局临时表空间的信息和⼤⼩？ 

 分析过程:

1.可以通过 INFORMATION_SCHEMA.FILES 查看全局临时表空间的元数据：

SELECT * FROM INFORMATION_SCHEMA.FILES WHERE
TABLESPACE_NAME='innodb_temporary'\G
*************************** 1. row ***************************FILE_ID: 4294967293FILE_NAME: ./ibtmp1FILE_TYPE: TEMPORARYTABLESPACE_NAME: innodb_temporary
# ... 省略

 2.要检查全局临时表空间数据⽂件的⼤⼩，可以查询 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 # 最⼤允许扩容的容量
1 row in set (0.00 sec)

3.默认情况下，全局临时表空间数据⽂件会⾃动扩展并根据需要增加⼤⼩，要确定全局临时表空间数据⽂件是否⾃动扩展，可以检查 innodb_temp_data_file_path 变更设置：

mysql> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend |
+------------------------------+
1 row in set (0.00 sec)

 衍⽣问题

1.全局临时表空间数据⽂件的⼤⼩可以设置吗？

可以通过系统变量 innodb_temp_data_file_path 指定最⼤⽂件⼤⼩，并重新启动服务器，

语法与配置系统表空间⽂件相同

# mysqld节点
2 [mysqld]
3 innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M