临时表空间 - Temporary Tablespaces
1 什么是临时表？
✅ 解答问题
- 临时表存储的是临时数据，不能永久的存储数据，⼀般在复杂的查询或计算过程中⽤来存储过渡的中间结果，MySQL在执⾏查询与计算的过程中会⾃动⽣成临时表，⽐如表连接查询时得到的结果集就是⼀张临时表，因为结果中可能包含多个表中的字段并没有⼀张真实的表与之完全对应。
1.1 除了系统⾃动创建的临时表，可以⼿动创建临时表吗？
-
⽤⼾可以通过使⽤
CREATE TEMPORARY TABLE语句⼿动创建临时表 -
⽤⼾创建的临时表也称为外部临时表；MySQL在执⾏查询与计算的过程中⾃动⽣成的临时表称为内部临时表。
2 什么是外部临时表？
🔍 分析过程
- 使⽤ CREATE TEMPORARY TABLE 语句创建的临时表是外部临时表
# 创建⼀个名称为t1的临时表
CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
- 通过 INNODB_TEMP_TABLE_INFO 查询临时表元数据
-
TEMPORARY表只在当前会话中可⻅，并且在会话关闭时⾃动删除。这意味着两个不同的会话可以使⽤相同的临时表名，⽽不会相互冲突，临时表也不会与已有的⾮临时表名冲突，如果创建了与现有表同名的临时表，则现有表被隐藏，直到临时表被删除。 -
重启MySQL服务器后，再次查询临时表信息，得到空集合
✅ 解答问题
- 使⽤ CREATE TEMPORARY TABLE 语句创建的临时表是外部临时表，表只在当前会话中可⻅，并且在会话关闭时⾃动删除
3 什么是内部临时表？
🔍 分析过程
- 由服务器⾃动创建的临时表是内部临时表
- 服务器在以下情况会⾃动创建临时表，这个过程⽤⼾不能直接控制：
- 使⽤ UNION 语句合并查询结果
- 对视图时的⼀些操作，⽐如使⽤ UNION 或聚合函数
- 使⽤⼦查询
- 使⽤ DISTINCT 和 ORDER BY 的查询可能需要⼀个临时表
- 使⽤ INSERT…SELECT 语句向表中写⼊数据时，需要先⽤⼀个内部临时表来保存 SELECT 语句查询出来的⾏，然后将这些⾏插⼊到⽬标表中
- 使⽤ COUNT(DISTINCT) 和 GROUP_CONCAT() 表达式时
- 使⽤窗⼝函数时
✅ 解答问题
- 由服务器⾃动创建的临时表是内部临时表，通常MySQL在执⾏查询与计算的过程中会⾃动⽣成的内部临时表
3.1 如何确认服务器创建了临时表？
- 要确定SQL语句是否需要临时表，使⽤ EXPLAIN 并检查 Extra 列，在优化专题中我们再详细介绍
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 这两个系统变量中最⼩的值决定。
✅ 解答问题
- 通过配置对应的系统变量来指定临时表使⽤的存储引擎、使⽤内存的⼤⼩、表中的最⼤⾏数等选项。
5 临时表中的数据存在哪⾥？
🔍 分析过程
- 磁盘上的临时表数据存储在临时表空间中，MySQL8.0版本中磁盘上的临时表存储引擎⽀持InnoDB ，分为两种类型分别是：
- 会话临时表空间( session temporary tablespaces )
- 全局临时表空间( global temporary tablespace )。
5.1 会话临时表空间的作⽤？
- 磁盘上的会话临时表空间存储由⽤⼾创建的外部临时表和优化器创建的内部临时表；
5.2 会话临时表空间的数据存在哪⾥？
- 当MySQL接收到第⼀个创建磁盘临时表的请求时，从临时表空间池中分配会话临时表空间；⼀个会话最多分配两个表空间，⼀个⽤于⽤⼾创建的临时表，另⼀个⽤于优化器创建的内部临时表。会话的临时表空间⽤于存储会话创建的所有磁盘临时表，当会话断开连接时，临时表空间将被截断并释放回池中；
- 服务器启动时会创建⼀个包含 10 个临时表空间的临时表空间池，表空间会根据需要⾃动添加到池中，临时表空间池在MySQL正常关闭或中⽌初始化时被删除；
- 会话临时表空间⽂件扩展名为 .ibt ；
- 系统变量 innodb_temp_tablespaces_dir 可以指定会话临时表空间的位置。默认数据⽬录下的 #innodb_temp ⽬录(开头的 # 号是为了避免与数据库⽬录命名冲突)，如果⽆法创建临时表空间池，服务器则拒绝启动；
5.3 全局临时表空间的作⽤？
- 全局临时表空间存储对⽤⼾创建的临时表所做的更改，以便以后回滚操作
5.4 全局临时表空间的数据存在哪⾥？
- 系统变量
innodb_temp_data_file_path指定了全局临时表空间数据⽂件的相对路径、名称、⼤⼩和属性。如果没有指定，则默认在系统表空间⽬录(系统变量innodb_data_home_dir指定的⽬录)中创建，默认名为 ibtmp1 ，初始⽂件⼤⼩略⼤于12MB ；
- 全局临时表空间在正常关闭或中⽌初始化时被删除，并在每次启动服务器时重新创建，如果⽆法创建全局临时表空间，则拒绝启动；如果服务器意外停⽌，重启服务器时会⾃动删除并重新创建全局临时表空间。
✅ 解答问题
- 磁盘上的临时表数据存储在临时表空间中，临时表空间分为两种分别是：
- 会话临时表空间( session temporary tablespaces )，默认数据⽬录下的#innodb_temp ⽬录中
- 全局临时表空间( global temporary tablespace )，默认在数据⽬录下中创建，名为ibtmp1
6 怎么查看全局临时表空间的信息和⼤⼩？
- 可以通过 INFORMATION_SCHEMA.FILES 查看全局临时表空间的元数据：
- 要检查全局临时表空间数据⽂件的⼤⼩，可以查询 INFORMATION_SCHEMA.FILES 中的具体字段
- 默认情况下，全局临时表空间数据⽂件会⾃动扩展并根据需要增加⼤⼩，要确定全局临时表空间数据⽂件是否⾃动扩展，可以检查 innodb_temp_data_file_path 变更设置：
解答问题
可以通过 INFORMATION_SCHEMA.FILES 查看全局临时表空间的元数据
6.1 全局临时表空间数据⽂件的⼤⼩可以设置吗？
- 可以通过系统变量
innodb_temp_data_file_path指定最⼤⽂件⼤⼩，并重新启动服务器，语法与配置系统表空间⽂件相同
# mysqld节点
[mysqld]
3 innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M
