MySQL中内部临时表的使用

在某些情况下，服务器在处理语句时会创建内部临时表。用户无法直接控制何时发生这种情况。

服务器在以下条件下创建临时表：

• UNION语句的评估，但稍后会有一些例外。
• 评估某些视图，例如使用TEMPTABLE算法UNION或聚合的视图。
• 派生表的评估（请参见“派生表”）。
• 公用表表达式的求值（请参见“ WITH（公用表表达式）”）。
• 为子查询或半联接实现创建的表（请参见“通过合并优化派生表、视图引用和公用表表达式”）。
• 评估包含一个ORDER BY子句和另一个GROUP BY子句的语句，或者其中ORDER BY或GROUP BY包含来自联接队列中第一个表以外的表的列的语句。
• 评价DISTINCT结合ORDER BY可能需要一个临时表。
• 对于使用SQL_SMALL_RESULT修饰符的查询，MySQL使用内存中临时表，除非查询还包含需要磁盘存储的元素（稍后描述）。
• 为了评估INSERT ... SELECT从同一表中选择并插入到该表中的语句，MySQL创建了一个内部临时表来保存中的行SELECT，然后将这些行插入目标表中。请参见“ INSERT ... SELECT语句”。
• 评估多表UPDATE语句。
• GROUP_CONCAT()或COUNT(DISTINCT)表达式的评估。
• 窗口函数的评估（请参见“窗口函数优化”）在必要时使用临时表。

要确定一条语句是否需要一个临时表，请使用EXPLAIN并检查该Extr 列是否显示Using temporary（请参见“使用EXPLAIN优化查询”）。对于派生或具体化的临时表，EXPLAIN不一定会说Using temporary。对于语句使用窗口功能，EXPLAIN与FORMAT=JSON始终提供有关窗步骤的信息。如果窗口功能使用临时表，则会在每个步骤中显示该表。

当服务器创建内部临时表（在内存或磁盘中）时，它将增加Created_tmp_tables状态变量。如果服务器在磁盘上创建表（无论是最初还是通过转换内存表），它将增加Created_tmp_disk_tables状态变量。

某些查询条件阻止使用内存中的临时表，在这种情况下，服务器将使用磁盘上的表来代替：

• 表格中存在BLOB或TEXT列。但是，TempTable存储引擎是MySQL 8.0中内存中内部临时表的默认存储引擎，从MySQL 8.0.13开始支持二进制大对象类型。请参阅内部临时表存储引擎。
• SELECT如果使用UNION或，则列表中存在最大长度大于512（字符串为二进制字符串，非二进制为字符）的任何字符串列UNION ALL。
• SHOW COLUMNS和DESCRIBE语句中使用BLOB作为用于某些列的类型，从而用于结果的临时表是磁盘上的表。

服务器不会将临时表用于UNION满足某些条件的语句。而是从临时表创建中仅保留执行结果列类型转换所需的数据结构。该表尚未完全实例化，并且没有向其写入或读取任何行；行直接发送到客户端。结果是减少了内存和磁盘需求，并减少了将第一行发送给客户端之前的延迟，因为服务器不必等到最后一个查询块执行完毕。EXPLAIN优化器跟踪输出反映了这种执行策略：UNION RESULT查询块不存在，因为该块对应于从临时表中读取的部分。

这些条件UNION不带临时表即可进行评估：

• 工会是UNION ALL，不是UNION或UNION DISTINCT。
• 没有全局ORDER BY子句。
• 联合不是{INSERT | REPLACE}... SELECT ...语句的顶级查询块。

内部临时表存储引擎

内部临时表可以保存在内存中，并由TempTable或MEMORY存储引擎处理，或由InnoDB存储引擎存储在磁盘上。

内存中内部临时表的存储引擎

internal_tmp_mem_storage_engine会话变量定义了用于在存储器内的临时表的存储引擎。允许的值为TempTable（默认值）和MEMORY。

该TempTable存储引擎提供了有效的存储VARCHAR和VARBINARY列。从MySQL 8.0.13开始，支持其他二进制大对象类型的存储。该temptable_max_ram变量定义TempTable存储引擎开始以内存映射临时文件（默认）或InnoDB磁盘内部临时表的形式从磁盘分配空间之前可以占用的最大RAM量。默认temptable_max_ram设置为1GiB。的temptable_use_mmap变量（在MySQL引入8.0.16）控制是否将不是Temptable存储引擎使用存储器映射的文件或InnoDBtemptable_max_ram超出限制时在磁盘上的内部临时表。默认设置为temptable_use_mmap=ON。

注意

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

TempTable存储引擎将内存映射的临时文件用作内存中临时表的溢出机制受以下规则支配：

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

如果将TempTable存储引擎配置为使用InnoDB磁盘内部临时表作为溢出机制（temptable_use_mmap=OFF），则将超出temptable_max_ram限制的内存中表转换为InnoDB磁盘内部临时表，并将移动属于该表的所有行从内存到InnoDB磁盘内部临时表。的internal_tmp_disk_storage_engine（在MySQL 8.0.16移除）变量设置已经在没有影响TempTable存储引擎溢出机制。

如果TempTable存储引擎经常超出变量定义的内存限制，并且在临时目录中为内存映射文件使用了过多空间，请考虑使用InnoDB磁盘内部临时表作为TempTable溢出机制temptable_max_ram。这可能是由于使用大型内部临时表或大量使用内部临时表而发生的。InnoDB磁盘内部临时表是在会话临时表空间中创建的，会话临时表空间默认情况下位于数据目录中。有关更多信息，请参见“临时表空间”。

memory/temptable/physical_ram和memory/temptable/physical_disk性能架构器械可以用于监测TempTable从内存和磁盘空间分配。memory/temptable/physical_ram报告已分配的RAM数量。memory/temptable/physical_disk当内存映射文件用作TempTable溢出机制（temptable_use_mmap=ON）时，报告从磁盘分配的空间量。如果physical_disk仪器报告的值不是0，并且将内存映射文件用作TempTable溢出机制，则temptable_max_ram在某个时间点已达到阈值。可以在Performance Schema内存摘要表（例如）中查询数据memory_summary_global_by_event_name。看到“内存摘要表”。

将MEMORY存储引擎用于内存中的临时表时，如果MySQL内存中的临时表太大，则会自动将其转换为磁盘上的表。内存中临时表的最大大小由tmp_table_size或max_heap_table_size值定义，以较小者为准。这与使用MEMORY明确创建的表不同CREATE TABLE。对于此类表，仅max_heap_table_size变量确定表可以增长到多少，并且不转换为磁盘格式。

磁盘内部临时表的存储引擎

从MySQL 8.0.16开始，服务器始终使用InnoDB存储引擎来管理磁盘上的内部临时表。

在MySQL 8.0.15和更早版本中，该internal_tmp_disk_storage_engine变量用于定义用于磁盘内部临时表的存储引擎。在MySQL 8.0.16中已删除此变量，并且用于此目的的存储引擎不再可由用户配置。

在MySQL 8.0.15和更早版本中：对于公用表表达式（CTE），用于磁盘内部临时表的存储引擎不能为MyISAM。如果为internal_tmp_disk_storage_engine=MYISAM，则使用磁盘临时表实现CTE的任何尝试都会发生错误。

在MySQL 8.0.15和更早版本中：使用时internal_tmp_disk_storage_engine=INNODB，生成超过InnoDB行或列限制的磁盘内部临时表的查询将返回行大小太大或列过多错误。解决方法是设置internal_tmp_disk_storage_engine为MYISAM。

内部临时表存储格式

当存储内部内存临时表由TempTable存储引擎管理时，包含VARCHAR列，VARBINARY列或其他二进制大对象类型列（自MySQL 8.0.13起受支持）的行在内存中由一个单元格数组表示，每个单元格包含NULL标志，数据长度和数据指针。列值以连续的顺序放置在数组之后的单个内存区域中，而无需填充。阵列中的每个单元格都使用16个字节的存储空间。当TempTable存储引擎超出temptable_max_ram限制并开始从磁盘分配空间作为内存映射文件或InnoDB磁盘上的内部临时表。

当MEMORY存储引擎管理内存内部临时表时，将使用固定长度的行格式。VARCHAR和VARBINARY列值被填充为最大列长度，实际上将它们存储为CHAR和BINARY列。

在MySQL 8.0.16之前，磁盘上的内部临时表由InnoDB或MyISAM存储引擎管理（取决于internal_tmp_disk_storage_engine设置）。两个引擎都使用动态宽度行格式存储内部临时表。列仅占用所需的存储空间，与使用固定长度行的磁盘表相比，这减少了磁盘I / O，空间需求和处理时间。从MySQL 8.0.16开始，internal_tmp_disk_storage_engine不支持，并且磁盘上的内部临时表始终由处理InnoDB。

使用MEMORY存储引擎时，语句可以首先创建内存内部临时表，然后在表变得太大时将其转换为磁盘上的表。在这种情况下，跳过转换并开始在磁盘上创建内部临时表可能会获得更好的性能。该big_tables变量可用于强制内部临时表进行磁盘存储。