企业官网建设流程全解析

6. InnoDB 磁盘文件

6.10 通用表空间 - General Tablespace

6.10.1 通用表空间的作用和特性？

• 通用表空间是使用CREATE tablespace语法创建的共享InnoDB表空间
• 通用表空间能够存储多个表的数据，与系统表空间类似也是共享表空间；
• 服务器运行时会把表空间元数据保存在内存中，在表的数量相同的情况下，通用表空间比独立表空间的数量更少，所以消耗的内存也就更少；
• 数据文件可以放置在数据目录或数据目录之外的其他位置，对于单独管理关键表非常有用；
• 支持所有的表格式和行格式的相关特性；

6.10.2 怎么创建通用表空间？

创建通用表空间可以使用CREATE TABLESPACE语法：

• tablespace_name：通用表空间的名字
• DATAFILE 'file_name'：指定通用表空间在磁盘的文件名
• FILE_BLOCK_SIZE：数据行格式是压缩格式时才涉及

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

注意：

tablespace_name表空间名区分大小写

总结：

创建通用表空间可以使用CREATE TABLESPACE语法，与创建表类似，语句里用TABLESPACE关键字指明创建的是表空间

6.10.2.1 创建通用表空间的示例

示例：在data目录下创建通用表空间

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

• ADD DATAFILE子句在MySQL 8.0.14及以后的版本是可选的，之前是必需的。如果没有指定ADD DATAFILE子句，则自动创建一个以UUID为文件名的表空间数据文件，通用表空间数据文件以.ibd为扩展名。

# 在数据目录中查看通用表空间数据文件 root@yudukai:/var/lib/mysql# ll *.ibd # 没有指定ADD DATAFILE子句，随机生成的通用表空间数据文件，指定了就没有这行 -rw-r----- 1 mysql mysql 114688 Jun 7 14:05 d4b703a0-6236-11f1-841a-fa163e0bcb5f.ibd # ts2 # 系统自带，存放mysql系统表和数据字典表的表空间数据文件 -rw-r----- 1 mysql mysql 26214400 May 29 19:06 mysql.ibd # 使用了ADD DATAFILE子句，使用指定的通用表空间数据文件 -rw-r----- 1 mysql mysql 147456 May 29 19:06 ts1.ibd

6.10.2.2 创建通用表空间时要注意什么？

• 可以在数据目录中创建通用表空间，也可以在数据目录之外创建通用表空间。为避免与隐式创建的独立表文件表空间冲突，不支持在data目录的子目录中创建通用表空间。（因为我们每创建一个数据库，都会在数据目录中生成一个与数据库名相同的子目录，为了避免自已在数据目录中创建的子目录与以后将要创建的数据库重名，所以不允许把通用表空间创建在数据目录下的子目录中）

  当在数据目录之外创建通用表空间时，该目录必须存在，并且必须在创建表空间之前让InnoDB识别，要使用自定义的目录可以通过系统innodb_directories指定。

  Innodb_directories是一个只读启动选项，配置后需要重新启动服务器。

• 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"

示例：不能在数据目录的子目录下创建通用表空间

# 在数据目录下创建子目录 root@yudukai:/var/lib/mysql# mkdir my_tablespace root@yudukai:/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子句

6.10.3 如何向通用表空间中添加表？

示例：向通用表空间中添加表，在创建表时使用TABLESPACE子句指定通用表空间即可

# 在ts1表空间中添加t1表 mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE ts1; Query OK, 0 rows affected (0.02 sec) # 在ts1表空间中添加t2表 mysql> CREATE TABLE t3 (c1 INT PRIMARY KEY) TABLESPACE ts1; Query OK, 0 rows affected (0.02 sec) # 把t1表移动到ts1表空间 mysql> ALTER TABLE t1 TABLESPACE ts1; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>

总结：

首先创建通用表空间，之后使用CREATE语句创建表时通过TABLESPACE子句指定通用表空间，语句执行成功后即在指定的通用表空间下创建了表

6.10.4 怎么删除通用表空间？

DROP TABLESPACE语句用于删除一个InnoDB通用表空间。

在删除通用表空间之前，必须将所有表从表空间中删除，如果表空间不为空，将返回错误。

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

mysql> 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.00 sec) mysql>

示例：一个完整的通用表空间删除流程

# 创建通用表空间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关键字指明删除的是表空间

6.10.5 使用通用表空间时要注意什么？

• 使用TRUNCATE或DROP语句截断或删除表时，通用表空间的空闲容量并不会释放，并且只能用于新的InnoDB的新表而不能用于其他的引擎；
• 通用表空间不属于任何数据库，使用DROP DATABASE操作数据库和属于该数据库所有的表时，并不会删除通用表空间。
• tablespace_name表空间名区分大小写

6.11 临时表空间 - Temporary Tablespaces

6.11.1 什么是临时表？

临时表存储的是临时数据，不能永久的存储数据，一般在复杂的查询或计算过程中用来存储过渡的中间结果。

MySQL在执行查询与计算的过程中会自动生成临时表，比如表连接查询时得到的结果集就是一张临时表，因为结果中可能包含多个表中的字段并没有一张真实的表与之完全对应。

例如：student和score表是存在的，但是下面查询的大表是不存在磁盘中的，是两张表连接起来的结果，是在临时表里的结果。

当查询的结果所包含的列，并没有一个真实的表与之对应，那么这个结果集就用一个临时表把数据组织起来。

6.11.1.1 除了系统自动创建的临时表，可以手动创建临时表吗？

• 用户可以通过使用CREATE TEMPORARY TABLE语句手动创建临时表
• 用户创建的临时表也称为外部临时表；MySQL在执行查询与计算的过程中自动生成的临时表称为内部临时表。

6.11.2 什么是外部临时表？

使用CREATE TEMPORARY TABLE语句创建的临时表是外部临时表

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

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

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G *************************** 1. row *************************** TABLE_ID: 1295 # 临时表的表ID NAME: #sql288f17_30_b # 临时表的名称 N_COLS: 4 # 临时表中的列数(包含3个默认隐藏列) SPACE: 4243767290 # 临时表所在的临时表空间ID 1 row in set (0.00 sec) mysql>

1. TEMPORARY表只在当前会话中可见，并且在会话关闭时自动删除。
2. 这意味着两个不同的会话可以使用相同的临时表名，而不会相互冲突。
3. 临时表也不会与已有的非临时表名冲突，如果创建了与现有表同名的临时表，则现有表被隐藏，直到临时表被删除。

把临时表删了，就可以查看之前的同名的真实表了。

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

总结：

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

6.11.3 什么是内部临时表？

• 由服务器自动创建的临时表是内部临时表
• 服务器在以下情况会自动创建临时表，这个过程用户不能直接控制：
  • 使用UNION语句合并查询结果
  • 对视图时的一些操作，比如使用UNION或聚合函数
  • 使用子查询
  • 使用DISTINCT和ORDER BY的查询可能需要一个临时表
  • 使用INSERT…SELECT语句向表中写入数据时，需要先用一个内部临时表来保存SELECT语句查询出来的行，然后将这些行插入到目标表中
  • 使用COUNT(DISTINCT)和GROUP_CONCAT()表达式时
  • 使用窗口函数时

总结：

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

6.11.3.1 如何确认服务器创建了临时表？

要确定SQL语句是否需要临时表，使用EXPLAIN并检查Extra列，在优化专题中我们再详细介绍

6.11.4 临时表都有哪些设置？

• 系统变量internal_tmp_mem_storage_engine用于指定内存中内部临时表的存储引擎，值为TempTable(默认值)或MEMORY；
• TempTable存储引擎为VARCHAR和VARBINARY列以及其他二进制大对象类型进行了优化；
• 从MySQL 8.0.28开始tmp_table_size定义了由TempTable存储引擎创建的单个内部临时表允许使用内存的最大值，当达到tmp_table_size限制时，MySQL自动将内存中的内部临时表转换为磁盘上的InnoDB内部临时表。tmp_table_size的默认值是16MB；
• 系统变量temptable_max_ram定义TempTable存储引擎创建的所有临时表可以使用的最大内存，默认为1GB，超出限制后将内存中的内部临时表转换为磁盘上内部临时表；
• 当内存临时表使用内存存储引擎internal_tmp_mem_storage_engine=MEMORY时，系统变量max_heap_table_size可以限制内存内部临时表的最大行数，默认16777216，
• 内存存储引擎临时表变得太大，MySQL会自动将其转换为磁盘上的临时表，内存中临时表的大小由tmp_table_size和max_heap_table_size这两个系统变量中最小的值决定。

总结：

通过配置对应的系统变量来指定临时表使用的存储引擎、使用内存的大小、表中的最大行数等选项。

6.11.5 临时表中的数据存在哪里？

磁盘上的临时表数据存储在临时表空间中，MySQL8.0版本中磁盘上的临时表存储引擎支持InnoDB，分为两种类型分别是：

1. 会话临时表空间( session temporary tablespaces )
2. 全局临时表空间( global temporary tablespace )

6.11.5.1 会话临时表空间的作用？

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

6.11.5.2 会话临时表空间的数据存在哪里？

• 当MySQL接收到第一个创建磁盘临时表的请求时，从临时表空间池中分配会话临时表空间。

  一个会话最多分配两个表空间，一个用于用户创建的临时表，另一个用于优化器创建的内部临时表。

  会话的临时表空间用于存储会话创建的所有磁盘临时表，当会话断开连接时，临时表空间将被截断并释放回池中；

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

• 会话临时表空间文件扩展名为.ibt；

• 系统变量innodb_temp_tablespaces_dir可以指定会话临时表空间的位置。默认数据目录下的#innodb_temp目录(开头的#号是为了避免与数据库目录命名冲突)，如果无法创建临时表空间池，服务器则拒绝启动；

# 数据目录下的临时表空间目录，以#开头就是为了与真实目录名称起冲突 root@yudukai:/var/lib/mysql# cd /var/lib/mysql/#innodb_temp # 自动创建的临时表空间 root@yudukai:/var/lib/mysql/#innodb_temp# ls temp_10.ibt temp_1.ibt temp_2.ibt temp_3.ibt temp_4.ibt temp_5.ibt temp_6.ibt temp_7.ibt temp_8.ibt temp_9.ibt

默认会创建10个会话临时表空间。

外部临时表和内部临时表分别保存在不同的表空间中。

临时表空间类似于通用表空间，每个临时表空间中可以保存多个临时表中的数据。

并不是说，10个临时表空间只能支持5个会话。只是一个会话使用了两个临时表空间而已，一个表空间里面其实可以保存很多个临时表，支持很多会话。

6.11.5.3 全局临时表空间的作用？

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

6.11.5.4 全局临时表空间的数据存在哪里？

系统变量innodb_temp_data_file_path指定了全局临时表空间数据文件的相对路径、名称、大小和属性。如果没有指定，则默认在系统表空间目录(系统变量innodb_data_home_dir指定的目录)中创建，默认名为ibtmp1，初始文件大小略大于12MB；

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

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

总结：

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

• 会话临时表空间( session temporary tablespaces )，默认数据目录下的#innodb_temp目录中
• 全局临时表空间( global temporary tablespace )，默认在数据目录下中创建，名为ibtmp1

6.11.6 怎么查看全局临时表空间的信息和大小？

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

mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE -> TABLESPACE_NAME='innodb_temporary'\G *************************** 1. row *************************** FILE_ID: 4294967293 FILE_NAME: ./ibtmp1 FILE_TYPE: TEMPORARY TABLESPACE_NAME: innodb_temporary TABLE_CATALOG: TABLE_SCHEMA: NULL TABLE_NAME: NULL LOGFILE_GROUP_NAME: NULL LOGFILE_GROUP_NUMBER: NULL ENGINE: InnoDB FULLTEXT_KEYS: NULL DELETED_ROWS: NULL UPDATE_COUNT: NULL FREE_EXTENTS: 2 TOTAL_EXTENTS: 12 EXTENT_SIZE: 1048576 INITIAL_SIZE: 12582912 MAXIMUM_SIZE: NULL AUTOEXTEND_SIZE: 67108864 CREATION_TIME: NULL LAST_UPDATE_TIME: NULL LAST_ACCESS_TIME: NULL RECOVER_TIME: NULL TRANSACTION_COUNTER: NULL VERSION: NULL ROW_FORMAT: NULL TABLE_ROWS: NULL AVG_ROW_LENGTH: NULL DATA_LENGTH: NULL MAX_DATA_LENGTH: NULL INDEX_LENGTH: NULL DATA_FREE: 6291456 CREATE_TIME: NULL UPDATE_TIME: NULL CHECK_TIME: NULL CHECKSUM: NULL STATUS: NORMAL EXTRA: NULL 1 row in set (0.00 sec) mysql>

要检查全局临时表空间数据文件的大小，可以查询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) mysql>

默认情况下，全局临时表空间数据文件会自动扩展并根据需要增加大小，要确定全局临时表空间数据文件是否自动扩展，可以检查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) mysql>

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

6.11.6.1 全局临时表空间数据文件的大小可以设置吗？

可以通过系统变量innodb_temp_data_file_path指定最大文件大小，并重新启动服务器，语法与配置系统表空间文件相同

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