文章目录6. InnoDB 磁盘文件6.10 通用表空间 - General Tablespace6.10.1 通用表空间的作用和特性6.10.2 怎么创建通用表空间6.10.2.1 创建通用表空间的示例6.10.2.2 创建通用表空间时要注意什么6.10.3 如何向通用表空间中添加表6.10.4 怎么删除通用表空间6.10.5 使用通用表空间时要注意什么6.11 临时表空间 - Temporary Tablespaces6.11.1 什么是临时表6.11.1.1 除了系统自动创建的临时表可以手动创建临时表吗6.11.2 什么是外部临时表6.11.3 什么是内部临时表6.11.3.1 如何确认服务器创建了临时表6.11.4 临时表都有哪些设置6.11.5 临时表中的数据存在哪里6.11.5.1 会话临时表空间的作用6.11.5.2 会话临时表空间的数据存在哪里6.11.5.3 全局临时表空间的作用6.11.5.4 全局临时表空间的数据存在哪里6.11.6 怎么查看全局临时表空间的信息和大小6.11.6.1 全局临时表空间数据文件的大小可以设置吗6. InnoDB 磁盘文件6.10 通用表空间 - General Tablespace6.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 EngineInnoDB; # 或使用随机文件名 CREATE TABLESPACE ts2 EngineInnoDB;ADD DATAFILE子句在MySQL 8.0.14及以后的版本是可选的之前是必需的。如果没有指定ADD DATAFILE子句则自动创建一个以UUID为文件名的表空间数据文件通用表空间数据文件以.ibd为扩展名。# 在数据目录中查看通用表空间数据文件 rootyudukai:/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.ibd6.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-directoriesdirectory_path_1;directory_path_2 # 通过选项文件指定多个目录用分号隔开 [mysqld] innodb_directoriesdirectory_path_1;directory_path_2示例不能在数据目录的子目录下创建通用表空间# 在数据目录下创建子目录 rootyudukai:/var/lib/mysql# mkdir my_tablespace rootyudukai:/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 EngineInnoDB; # 提示错误因为子目录名有可能和数据库名重名 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.SPACEb.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 EngineInnoDB; # 在通用表空间中创建t1表 CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 EngineInnoDB; # 删除t1表 DROP TABLE t1; # 删除通用表空间ts1 DROP TABLESPACE ts1;总结可以使用DROP TABLESPACE语句用于删除一个通用表空间与删除表类似语句里用TABLESPACE关键字指明删除的是表空间6.10.5 使用通用表空间时要注意什么使用TRUNCATE或DROP语句截断或删除表时通用表空间的空闲容量并不会释放并且只能用于新的InnoDB的新表而不能用于其他的引擎通用表空间不属于任何数据库使用DROP DATABASE操作数据库和属于该数据库所有的表时并不会删除通用表空间。tablespace_name表空间名区分大小写6.11 临时表空间 - Temporary Tablespaces6.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) ENGINEINNODB;通过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) mysqlTEMPORARY表只在当前会话中可见并且在会话关闭时自动删除。这意味着两个不同的会话可以使用相同的临时表名而不会相互冲突。临时表也不会与已有的非临时表名冲突如果创建了与现有表同名的临时表则现有表被隐藏直到临时表被删除。把临时表删了就可以查看之前的同名的真实表了。重启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(默认值)或MEMORYTempTable存储引擎为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_engineMEMORY时系统变量max_heap_table_size可以限制内存内部临时表的最大行数默认16777216内存存储引擎临时表变得太大MySQL会自动将其转换为磁盘上的临时表内存中临时表的大小由tmp_table_size和max_heap_table_size这两个系统变量中最小的值决定。总结通过配置对应的系统变量来指定临时表使用的存储引擎、使用内存的大小、表中的最大行数等选项。6.11.5 临时表中的数据存在哪里磁盘上的临时表数据存储在临时表空间中MySQL8.0版本中磁盘上的临时表存储引擎支持InnoDB分为两种类型分别是会话临时表空间( session temporary tablespaces )全局临时表空间( global temporary tablespace )6.11.5.1 会话临时表空间的作用磁盘上的会话临时表空间存储由用户创建的外部临时表和优化器创建的内部临时表6.11.5.2 会话临时表空间的数据存在哪里当MySQL接收到第一个创建磁盘临时表的请求时从临时表空间池中分配会话临时表空间。一个会话最多分配两个表空间一个用于用户创建的临时表另一个用于优化器创建的内部临时表。会话的临时表空间用于存储会话创建的所有磁盘临时表当会话断开连接时临时表空间将被截断并释放回池中服务器启动时会创建一个包含10个临时表空间的临时表空间池表空间会根据需要自动添加到池中临时表空间池在MySQL正常关闭或中止初始化时被删除会话临时表空间文件扩展名为.ibt系统变量innodb_temp_tablespaces_dir可以指定会话临时表空间的位置。默认数据目录下的#innodb_temp目录(开头的#号是为了避免与数据库目录命名冲突)如果无法创建临时表空间池服务器则拒绝启动# 数据目录下的临时表空间目录以#开头就是为了与真实目录名称起冲突 rootyudukai:/var/lib/mysql# cd /var/lib/mysql/#innodb_temp # 自动创建的临时表空间 rootyudukai:/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# 数据目录 rootyudukai:/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 )默认在数据目录下中创建名为ibtmp16.11.6 怎么查看全局临时表空间的信息和大小可以通过INFORMATION_SCHEMA.FILES查看全局临时表空间的元数据mysql SELECT * FROM INFORMATION_SCHEMA.FILES WHERE - TABLESPACE_NAMEinnodb_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_pathibtmp1:12M:autoextend:max:500M