MySQL 5.7-8.4.4 Internal Temporary Table Use in MySQL

In some cases, the server creates internal temporary tables while processing statements. Users have no direct control over when this occurs.

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

The server creates temporary tables under conditions such as these:

服务器在以下情况下创建临时表:

• Evaluation of UNION statements, with some exceptions described later.

• UNION语句的计算，稍后将介绍一些例外情况。

• Evaluation of some views, such those that use the TEMPTABLE algorithm, UNION, or aggregation.

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

• Evaluation of derived tables (see Section 13.2.10.8, “Derived Tables”).

• 派生表的求值

• Tables created for subquery or semijoin materialization (see Section 8.2.2, “Optimizing Subqueries, Derived Tables, and View References”).

• 为子查询或半连接具体化而创建的表

• Evaluation of statements that contain an ORDER BY clause and a different GROUP BY clause, or for which the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue.

• 计算包含ORDER BY子句和不同的GROUP BY子句的语句，或者ORDER BY或GROUP BY包含联接队列中第一个表以外的表的列。

• Evaluation of DISTINCT combined with ORDER BY may require a temporary table.

• 结合ORDER BY计算DISTINCT可能需要一个临时表。

• For queries that use the SQL_SMALL_RESULT modifier, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.

• 对于使用SQL_SMALL_RESULT修饰符的查询，MySQL使用内存中的临时表，除非查询也包含需要磁盘存储的元素(稍后介绍)。

• To evaluate INSERT ... SELECT statements that select from and insert into the same table, MySQL creates an internal temporary table to hold the rows from the SELECT, then inserts those rows into the target table. See Section 13.2.5.1, “INSERT ... SELECT Statement”.

• 要计算INSERT…MySQL创建一个内部临时表来保存SELECT中的行，然后将这些行插入到目标表中。

• Evaluation of multiple-table UPDATE statements.

• 多表UPDATE语句的计算。

• Evaluation of GROUP_CONCAT() or COUNT(DISTINCT) expressions.

• GROUP_CONCAT()或COUNT(DISTINCT)表达式的求值。

To determine whether a statement requires a temporary table, use EXPLAIN and check the Extra column to see whether it says Using temporary (see Section 8.8.1, “Optimizing Queries with EXPLAIN”). 

要确定一条语句是否需要一个临时表，请使用EXPLAIN，并检查Extra列是否显示了Using temporary(参见8.8.1节“用EXPLAIN优化查询”)。

EXPLAIN does not necessarily say Using temporary for derived or materialized temporary tables.

EXPLAIN并没有说对派生的或物化的临时表使用temporary。

Some query conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

一些查询条件阻止使用内存中的临时表，在这种情况下，服务器使用磁盘上的表:

• Presence of a BLOB or TEXT column in the table. This includes user-defined variables having a string value because they are treated as BLOB or TEXT columns, depending on whether their value is a binary or nonbinary string, respectively.

• 表中存在BLOB或TEXT列。这包括具有字符串值的用户定义变量，因为它们分别被视为BLOB列或TEXT列，这取决于它们的值是二进制字符串还是非二进制字符串。

• Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used.

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

• The SHOW COLUMNS and DESCRIBE statements use BLOB as the type for some columns, thus the temporary table used for the results is an on-disk table.

• SHOW COLUMNS和DESCRIBE语句使用BLOB作为某些列的类型，因此用于结果的临时表是磁盘上的表。

The server does not use a temporary table for UNION statements that meet certain qualifications. Instead, it retains from temporary table creation only the data structures necessary to perform result column typecasting.

对于满足特定条件的UNION语句，服务器不使用临时表。相反，它在创建临时表时只保留执行结果列类型转换所需的数据结构。

The table is not fully instantiated and no rows are written to or read from it; rows are sent directly to the client.

表没有完全实例化，没有行写入或读取;行被直接发送到客户端。

The result is reduced memory and disk requirements, and smaller delay before the first row is sent to the client because the server need not wait until the last query block is executed. 

结果是减少了内存和磁盘需求，在将第一行发送到客户端之前的延迟更小，因为服务器不需要等到最后一个查询块执行。

EXPLAIN and optimizer trace output reflects this execution strategy: The UNION RESULT query block is not present because that block corresponds to the part that reads from the temporary table.

EXPLAIN和优化器跟踪输出反映了这种执行策略:UNION RESULT查询块不存在，因为该块对应于从临时表中读取数据的部分。

These conditions qualify a UNION for evaluation without a temporary table:

这些条件使UNION 有资格在没有临时表的情况下进行评价:

• The union is UNION ALL, not UNION or UNION DISTINCT.

• There is no global ORDER BY clause.

• The union is not the top-level query block of an {INSERT | REPLACE} ... SELECT ... statement.

Internal Temporary Table Storage Engine

内部临时表存储引擎

An internal temporary table can be held in memory and processed by the MEMORY storage engine, or stored on disk by the InnoDB or MyISAM storage engine.

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

If an internal temporary table is created as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table.

如果一个内部临时表作为内存表创建，但是太大了，MySQL会自动将其转换为磁盘上的表。

The maximum size for in-memory temporary tables is defined by the tmp_table_size or max_heap_table_size value, whichever is smaller.

内存中临时表的最大大小由tmp_table_size或max_heap_table_size值定义，以较小的值为准。

This differs from MEMORY tables explicitly created with CREATE TABLE. For such tables, only the max_heap_table_size variable determines how large a table can grow, and there is no conversion to on-disk format.

这与使用CREATE TABLE显式创建的内存表不同。对于这样的表，只有max_heap_table_size变量决定表可以增长到多大，并且不存在到磁盘上格式的转换。

The internal_tmp_disk_storage_engine variable defines the storage engine the server uses to manage on-disk internal temporary tables. Permitted values are INNODB (the default) and MYISAM.

internal_tmp_disk_storage_engine变量定义了服务器用来管理磁盘上的内部临时表的存储引擎。允许的值是INNODB(默认值)和MYISAM。

Note

When using internal_tmp_disk_storage_engine=INNODB, queries that generate on-disk internal temporary tables that exceed InnoDB row or column limits return Row size too large or Too many columns errors. The workaround is to set internal_tmp_disk_storage_engine to MYISAM.

当使用internal_tmp_disk_storage_engine=INNODB时，如果在磁盘上生成的临时表的行或列超过了INNODB的限制，则会返回“row size too large”或“too many columns”错误。解决方法是将internal_tmp_disk_storage_engine设置为MYISAM

When an internal temporary table is created in memory or on disk, the server increments the Created_tmp_tables value.

当在内存或磁盘上创建内部临时表时，服务器会增加Created_tmp_tables值。

When an internal temporary table is created on disk, the server increments the Created_tmp_disk_tables value. If too many internal temporary tables are created on disk, consider increasing the tmp_table_size and max_heap_table_size settings.

当在磁盘上创建内部临时表时，服务器会增加Created_tmp_disk_tables值。如果在磁盘上创建了过多的内部临时表，请考虑增加tmp_table_size和max_heap_table_size设置。

Internal Temporary Table Storage Format

内部临时表存储格式

In-memory temporary tables are managed by the MEMORY storage engine, which uses fixed-length row format. VARCHAR and VARBINARY column values are padded to the maximum column length, in effect storing them as CHAR and BINARY columns.

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

On-disk temporary tables are managed by the InnoDB or MyISAM storage engine (depending on the internal_tmp_disk_storage_engine setting). Both engines store temporary tables using dynamic-width row format. Columns take only as much storage as needed, which reduces disk I/O, space requirements, and processing time compared to on-disk tables that use fixed-length rows.

磁盘上的临时表由InnoDB或MyISAM存储引擎管理(取决于internal_tmp_disk_storage_engine设置)。这两个引擎都使用动态宽度行格式存储临时表。列只占用所需的存储空间，与使用固定长度行的磁盘表相比，这减少了磁盘I/O、空间需求和处理时间。

For statements that initially create an internal temporary table in memory, then convert it to an on-disk table, better performance might be achieved by skipping the conversion step and creating the table on disk to begin with. The big_tables variable can be used to force disk storage of internal temporary tables.

对于那些首先在内存中创建内部临时表，然后将其转换为磁盘上表的语句，跳过转换步骤并首先在磁盘上创建表可能会获得更好的性能。可以使用big_tables变量强制在磁盘上存储内部临时表。