mysql 内部临时表_mysql8 参考手册--MySQL中内部临时表的使用

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

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

1、UNION 语句的 评估，但稍后会有一些例外。

2、评估某些视图，例如使用TEMPTABLE算法 UNION或聚合的视图 。

3、派生表的评估(请参见 第13.2.11.8节“派生表”)。

4、公用表表达式的求值(请参见 第13.2.15节“ WITH(公用表表达式)”)。

5、为子查询或半联接实现创建的表(请参见 第8.2.2节“优化子查询，派生表，视图引用和公用表表达式”)。

6、评估包含一个ORDER BY子句和另一个GROUP BY子句的语句，或者其中ORDER BY或GROUP BY包含来自联接队列中第一个表以外的表的列的语句。

7、评价DISTINCT结合 ORDER BY可能需要一个临时表。

8、对于使用SQL_SMALL_RESULT 修饰符的查询，MySQL使用内存中临时表，除非查询还包含需要磁盘存储的元素(稍后描述)。

9、为了评估 INSERT ... SELECT从同一表中选择并插入到该表中的语句，MySQL创建了一个内部临时表来保存中的行 SELECT，然后将这些行插入目标表中。请参见 第13.2.6.1节“ INSERT ... SELECT语句”。

10、评估多表 UPDATE语句。

11、GROUP_CONCAT() 或COUNT(DISTINCT) 表达式的 评估。

12、窗口函数的评估(请参见 第12.21节“窗口函数”)在必要时使用临时表。

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

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

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

1、表格中 存在BLOB或 TEXT列。但是，TempTable存储引擎是MySQL 8.0中内存中内部临时表的默认存储引擎，从MySQL 8.0.13开始支持二进制大对象类型。请参阅 内部临时表存储引擎。

2、SELECT如果使用UNION或 ，则列表中 存在最大长度大于512(字符串为二进制字符串，非二进制为字符)的任何字符串列UNION ALL 。

3、SHOW COLUMNS和 DESCRIBE语句中使用 BLOB作为用于某些列的类型，从而用于结果的临时表是磁盘上的表。

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

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

1、工会是UNION ALL，不是 UNION或UNION DISTINCT。

2、没有全局ORDER BY子句。

3、联合不是{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存储引擎 将内存映射的临时文件 用作内存中临时表的溢出机制受以下规则支配：

1、在tmpdir变量定义的目录中创建临时文件。

2、临时文件在创建和打开后会立即删除，因此在tmpdir目录中不会保持可见。临时文件打开时，操作系统将保留临时文件占用的空间。当TempTable存储引擎关闭临时文件或mysqld关闭进程时，将 回收空间 。

3、数据永远不会在RAM和临时文件之间，RAM内或临时文件之间移动。

4、如果在由定义的限制内有可用空间，则新数据将存储在RAM中 temptable_max_ram。否则，新数据将存储在临时文件中。

5、如果将表的某些数据写入临时文件后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磁盘内部临时表是在会话临时表空间中创建的，会话临时表空间默认情况下位于数据目录中。有关更多信息，请参见 第15.6.3.5节“临时表空间”。

的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。看到第26.12.17.10节“内存摘要表”。

将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变量可用于强制内部临时表进行磁盘存储。