临时表¶

顾名思义，临时表就是临时用来存储数据的表，是建立在系统临时文件夹中的表，如果使用得当，完全可以像普通表一样进行各种操作。

我们常使用临时表来存储中间结果集。如果需要执行一个很耗资源的查询或需要多次操作大表时，可以把中间结果或小的子集放到一个临时表里，再对这些表进行查询，以此来提高查询效率。临时表主要适用于需要临时保存数据的一些场景。

一般情况下，临时表通常是在应用程序中动态创建或者由 MySQL 内部根据需要自己创建。临时表可以分为内部临时表和外部临时表。

外部临时表¶

外部临时表也可称为会话临时表，这种临时表只对当前用户可见，它的数据和表结构都存储在内存中。当前会话中断或结束后，数据表数据就会丢失，MySQL 会自动删除表并释放其所占空间。

1）创建临时表

创建临时表很容易，在 CREATE TABLE 语句上添加 TEMPORARY 关键字，如下所示：

CREATE TEMPORARY TABLE <表名>...

临时表的命名可以和非临时表同名，但是同名后非临时表将对当前会话不可见，直到临时表被删除。

2）查询临时表

创建了临时表之后，运行 SHOW TABLES 命令不会列出临时表，以及在 INFORMATION_SCHEMA 数据库中也不存在临时表的信息，这不是 Bug，而是设计就是如此。

我们可以使用以下命令来查看临时表：

SHOW CREATE TABLE <表名>;

3）删除临时表

当然，我们也可以在当前会话中手动销毁临时表，SQL 语句如下：

DROP TABLE <表名>;

内部临时表¶

内部临时表是一种特殊轻量级的临时表，不同于手工创建的临时表，它是被 MySQL 自动创建的。

在 SQL 的执行过程中可能会用到临时表来存储某些操作的中间结果，该过程由 MySQL 自动完成，用户无法手工干预，且这种内部表对用户来说是不可见的。

我们可以通过 EXPLAIN 或者 SHOW STATUS 查看 MySQL 是否使用了内部临时表帮助完成某个操作。

内部临时表在 SQL 语句的优化过程中扮演着非常重要的角色，MySQL 中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价，所以用户在写 SQL 语句的时候应该尽量的去避免使用临时表。

通常情况下，内部临时表首先保留在内存中，以获得最佳查询性能。为避免内存分配过多，MySQL 提供了参数，可用于设置内存限制。

当达到该限制时，内部临时表就会溢出到磁盘存储。MySQL 可能会为内存中的内部临时表指定与磁盘存储中的内部临时表不同的存储引擎。

MySQL Server 会在以下情况下创建临时表：

评估 UNION 语句（后面会介绍一些例外情况）
评估某些视图，如使用 TEMPTABLE 算法、UNION 或聚合的视图
求值派生表(derived tables)
评估常用表表达式(common table expressions)
为子查询或半连接物化创建的表
评估包含ORDER BY子句和不同GROUP BY子句的语句，或者ORDER BY或GROUP BY包含来自连接队列中第一个表以外的表的列的语句
结合ORDER BY对DISTINCT进行评估可能需要使用临时表。
对于使用SQL_SMALL_RESULT修改器的查询，MySQL 会使用内存中的临时表，除非查询还包含需要磁盘存储的元素
要评估从同一个表中选择并插入到同一个表中的INSERT ... SELECT 语句，MySQL 会创建一个内部临时表来保存SELECT 中的记录，然后将这些记录插入到目标表中
评估多表UPDATE语句
评估GROUP_CONCAT()或COUNT(DISTINCT)表达式
评估窗口函数，必要时使用临时表。

内部临时表存储引擎¶

内部临时表可以通过TempTable或MEMORY引擎存储在内存中；或者通过InnoDB引擎存储物化到磁盘上。

位于内存中的内部临时表存储引擎是通过变量 internal_tmp_mem_storage_engine 来设置的。

在 8.0 之前，MySQL 使用 MEMORY 存储引擎作为内存内部临时表的默认存储引擎。
在 8.0 之后，内存临时表默认存储引擎已经改成了 TempTable，但是也可以改回 MEMORY

参数名	`internal_tmp_mem_storage_engine`
含义	定义内存内部临时表使用的存储引擎。
作用	当优化器决定在内存中创建一个临时表来执行复杂的查询（如 `GROUP BY`、`UNION`、`DISTINCT` 或 `ORDER BY`）时，该参数指定使用哪个表存储引擎。
适用范围	影响所有非 `TempTable` 存储引擎的内部临时表，主要是在 MySQL 8.0 之前的版本中，以及在 8.0 中用于特定类型的内部临时表（尽管 8.0 之后的版本主流是 `TempTable`）。
可选值	`TempTable` (MySQL 8.0.x 默认，推荐) 或 `MEMORY` (旧版本默认，传统)。
作用域	全局或会话级别。

TempTable存储引擎在可管理性方面的一个关键简化是，它为所有使用它的会话使用一个内存池。

由temptable_max_ram设置的内存限制适用于并发会话的内存消耗总和。

这与 MEMORY 存储引擎不同，MEMORY 存储引擎的内存限制由较小的tmp_table_size和max_heap_table_size设置，是针对会话的每个表。

TempTable 存储引擎也有自己的磁盘溢出机制。可以将其配置为溢出到**内存映射临时文件或 **InnoDB 磁盘内部临时表

内存映射文件提供了文件和内存空间之间的映射，可加快文件读写操作。

除了内存限制外，这两种不同的溢出路径还能展现内部临时表存储引擎的独特之处，并直接影响查询性能。

在 8.0.28 之前，MySQL 默认使用传统的 MEMORY 存储引擎来创建内存内部临时表。

参数名	tmp_table_size
含义	单个内存内部临时表的最大大小
作用	限制单个内部临时表在内存中可以增长的最大字节数
默认值	默认是16MB
转换机制	当一个内部临时表的大小达到 `tmp_table_size` 或 `max_heap_table_size` 中的较小值时，该表会被溢出被转换为磁盘临时表
作用域	可以是全局或会话级别

参数名	max_heap_table_size
含义	用户创建的 `MEMORY` 表（`CREATE TABLE ... ENGINE=MEMORY`）以及内存内部临时表的最大大小
作用	这个参数与 `tmp_table_size` 协同工作来限制内存内部临时表的大小。实际限制是 `MIN(tmp_table_size, max_heap_table_size)`
默认值	默认是16MB
转换机制	如果实际大小超过这个限制的较小值，表将被溢出转换为磁盘临时表
作用域	可以是全局或会话级别。
注意	如果一个会话将 `tmp_table_size` 设置得很大，但未设置 `max_heap_table_size`，则仍会受全局或会话级别的 `max_heap_table_size` 限制

参数名	`internal_tmp_disk_storage_engine`
作用	定义磁盘内部临时表使用的存储引擎
适用版本	MySQL 8.0.15 及更早版本（包括所有 MySQL 5.x 版本）
可选值	`InnoDB` 或 `MyISAM`
默认值	默认值是 `InnoDB`

注意：从MySQL 8.0.16开始，溢出的磁盘临时表只能使用InnoDB引擎，上述参数已经彻底 deprecated 失效。

从 MySQL 8.0 开始，引入了新的 TempTable 存储引擎来管理内部临时表，取代了传统的 MEMORY 引擎。从 8.0.28 开始，MySQL 引入了更精细的全局资源限制参数。

¶

TempTable引擎可以高效存储varchar、varbinary类型的列。TempTable引擎的控制变量有：

• tmp_table_size ：

从 MySQL 8.0.28 开始，tmp_table_size定义了由 TempTable 存储引擎创建的任何单个内存内部临时表的最大大小。当达到tmp_table_size限制时，MySQL 会自动将内存内部临时表转换为InnoDB基于磁盘的内部临时表。默认的tmp_table_size设置为 16777216 字节（16MB）。

tmp_table_size限制的目的是防止单个查询消耗过多的全局 TempTable 资源，以免影响需要 TempTable 资源的并发查询的性能。全局TempTable资源由temptable_max_ram和temptable_max_mmap设置控制。

如果tmp_table_size限制小于temptable_max_ram限制，内存中临时表所包含的数据就不可能超过tmp_table_size限制所允许的数量。如果tmp_table_size限制大于temptable_max_ram和temptable_max_mmap限制，则内存中临时表所包含的数据不可能超过temptable_max_ram和temptable_max_mmap限制。

• temptable_max_ram ：

定义在TempTable存储引擎开始从内存映射文件分配空间，或者在 MySQL 开始使用InnoDB基于磁盘内部临时表之前，TempTable存储引擎可以使用的最大内存容量，具体取决于配置。默认temptable_max_ram设置为 1073741824 字节（1GB）。

temptable_max_ram设置不包括分配给使用TempTable存储引擎的每个线程的线程本地内存块。线程本地内存块的大小取决于线程第一次内存分配请求的大小。如果请求小于 1MB（大多数情况下都小于 1MB），则线程本地内存块大小为 1MB。如果请求大于 1MB，则线程本地内存块的大小与初始内存请求大致相同。线程本地内存块保存在线程本地存储中，直到线程退出。

• temptable_use_mmap：

当超过temptable_max_ram限制时，控制TempTable存储引擎是从内存映射文件分配空间，还是 MySQL 使用InnoDB磁盘上的内部临时表。默认设置为temptable_use_mmap=ON。

temptable_use_mmap变量在 MySQL 8.0.16 中引入，在 MySQL 8.0.26 中被弃用；预计未来版本的 MySQL 将删除对它的支持。设置temptable_max_mmap=0等于设置temptable_use_mmap=OFF。

• temptable_max_mmap：

在 MySQL 8.0.23 中引入。定义在 MySQL 开始使用InnoDB磁盘内部临时表之前，允许 TempTable 存储引擎从内存映射文件中分配的最大内存量。默认设置为 1073741824 字节（1GiB）。该限制旨在解决内存映射文件占用临时目录（tmpdir）太多空间的风险。如果temptable_max_mmap=0设置为禁用内存映射文件的分配，则无论temptable_use_mmap设置如何，都将有效禁用内存映射文件。

TempTable 引擎使用内存映射文件(memory-mapped files)，有以下规则控制：

临时文件在tmpdir变量定义的目录中创建。
临时文件在创建和打开后会立即被删除，因此不会保留在tmpdir目录中。临时文件占用的空间在临时文件打开时由操作系统保留。当临时文件被TempTable存储引擎关闭或mysqld进程关闭时，这些空间会被回收。
数据不会在内存和临时文件之间、内存内部或临时文件之间移动
如果在temptable_max_ram定义的限制范围内有可用空间，新数据就会存储在内存中。否则，新数据将存储在临时文件中。
如果在表的部分数据写入临时文件后，内存中出现可用空间，则可以将剩余的表数据存储在内存中。

当对内存中的临时表使用 MEMORY 存储引擎（internal_tmp_mem_storage_engine=MEMORY）时，如果内存中的临时表过大，MySQL 会自动将其转换为磁盘上的表。内存中临时表的最大大小由tmp_table_size或max_heap_table_size值定义，以较小者为准。

这与使用CREATE TABLE明确创建的MEMORY表不同，对于这类表，只有max_heap_table_size变量决定表的大小，而不需要转换为磁盘上的格式。