MySQL :: MySQL 8.4 参考手册 :: 10.4.4 MySQL 内部临时表的使用

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 10 优化 / 10.4 优化数据库结构 / 10.4.4 MySQL 内部临时表的使用

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

10.4.4 MySQL 中的内部临时表使用10.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:

对UNION语句进行评估，但稍后会描述一些例外情况。Evaluation of UNION statements, with some exceptions described later.
对某些视图进行评估，例如使用TEMPTABLE算法、UNION或聚合的视图。Evaluation of some views, such those that use the TEMPTABLE algorithm, UNION, or aggregation.
对派生表进行评估（参见第 15.2.15.8 节，“派生表”）。Evaluation of derived tables (see Section 15.2.15.8, “Derived Tables”).
对公共表表达式进行评估（参见第 15.2.20 节，“WITH（公共表表达式）”）。Evaluation of common table expressions (see Section 15.2.20, “WITH (Common Table Expressions)”).
为子查询或半连接物化创建的表（参见第 10.2.2 节，“优化子查询、派生表、视图引用和公用表表达式”）。Tables created for subquery or semijoin materialization (see Section 10.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions”).
对包含 ORDER BY 子句和不同 GROUP BY 子句的语句进行评估，或者对 GROUP BY 或 ORDER BY 包含来自连接队列中第一个表以外的表的列的语句进行评估。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.
评估 DISTINCT 与 ORDER BY 结合的情况可能需要临时表。Evaluation of DISTINCT combined with ORDER BY may require a temporary table.
对于使用 SQL_SMALL_RESULT 修饰符的查询，MySQL 会使用内存中的临时表，除非查询还包含需要在磁盘上存储的元素（稍后描述）。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.
要评估从同一张表中选择并插入到同一张表的 INSERT ... SELECT 语句，MySQL 会创建一个内部临时表来保存 SELECT 的行，然后将这些行插入目标表。参见第 15.2.7.1 节，“INSERT ... SELECT 语句”。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 15.2.7.1, “INSERT ... SELECT Statement”.
多表 UPDATE 语句的评估。Evaluation of multiple-table UPDATE statements.
对 GROUP_CONCAT() 或 COUNT(DISTINCT) 表达式的评估。Evaluation of GROUP_CONCAT() or COUNT(DISTINCT) expressions.
对窗口函数的评估（参见第 14.20 节，“窗口函数”）在必要时使用临时表。Evaluation of window functions (see Section 14.20, “Window Functions”) uses temporary tables as necessary.

要确定一个语句是否需要临时表，请使用 EXPLAIN，并检查 Extra 列，看它是否显示 Using temporary（参见第 10.8.1 节，“使用 EXPLAIN 优化查询”）。EXPLAIN 并不总是对派生表或物化临时表显示 Using temporary。对于使用窗口函数的语句，使用 FORMAT=JSON 的 EXPLAIN 总是提供关于窗口步骤的信息。如果窗口函数使用临时表，每个步骤都会标明。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 10.8.1, “Optimizing Queries with EXPLAIN”). EXPLAIN does not necessarily say Using temporary for derived or materialized temporary tables. For statements that use window functions, EXPLAIN with FORMAT=JSON always provides information about the windowing steps. If the windowing functions use temporary tables, it is indicated for each step.

某些查询条件会阻止使用内存临时表，在这种情况下，服务器会改用磁盘表：Some query conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

表中存在 BLOB 或 TEXT 列。TempTable 存储引擎是 MySQL 8.4 中内存内部临时表的默认存储引擎，它支持二进制大对象类型。请参阅内部临时表存储引擎。Presence of a BLOB or TEXT column in the table. The TempTable storage engine, which is the default storage engine for in-memory internal temporary tables in MySQL 8.4, supports binary large object types. See Internal Temporary Table Storage Engine.
如果在 SELECT 列表中存在任何最大长度超过 512（二进制字符串为字节，非二进制字符串为字符）的字符串列，并且使用了 UNION 或 UNION ALL。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.
SHOW COLUMNS 和 DESCRIBE 语句使用 BLOB 作为某些列的类型，因此用于结果的临时表是一个磁盘表。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.

对于符合某些条件的UNION语句，服务器不会使用临时表。相反，它只保留创建临时表时所需的数据结构，以执行结果列类型转换。表不会被完全实例化，也不会有行被写入或读取；行会直接发送给客户端。这样可以减少内存和磁盘的使用，并且在发送第一行到客户端之前的延迟更小，因为服务器无需等待最后一个查询块执行完成。EXPLAIN和优化器跟踪输出反映了这种执行策略：UNION RESULT查询块不存在，因为该块对应于从临时表读取的部分。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. 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.

这些条件使 UNION 符合无需临时表即可评估的要求：These conditions qualify a UNION for evaluation without a temporary table:

该联合是 UNION ALL，而不是 UNION 或 UNION DISTINCT。The union is UNION ALL, not UNION or UNION DISTINCT.
没有全局 ORDER BY 子句。There is no global ORDER BY clause.
该联合不是 {INSERT | REPLACE} ... SELECT ... 语句的顶级查询块。The union is not the top-level query block of an {INSERT | REPLACE} ... SELECT ... statement.

内部临时表存储引擎Internal Temporary Table Storage Engine

内部临时表可以保存在内存中，由 TempTable 或 MEMORY 存储引擎处理，或者由 InnoDB 存储引擎存储到磁盘上。An internal temporary table can be held in memory and processed by the TempTable or MEMORY storage engine, or stored on disk by the InnoDB storage engine.

用于内存中内部临时表的存储引擎Storage Engine for In-Memory Internal Temporary Tables

internal_tmp_mem_storage_engine 变量定义了用于内存内部临时表的存储引擎。允许的值为 TempTable（默认值）和 MEMORY。The internal_tmp_mem_storage_engine variable defines the storage engine used for in-memory internal temporary tables. Permitted values are TempTable (the default) and MEMORY.

Note

配置 internal_tmp_mem_storage_engine 的会话设置需要 SESSION_VARIABLES_ADMIN 或 SYSTEM_VARIABLES_ADMIN 权限。Configuring a session setting for internal_tmp_mem_storage_engine requires the SESSION_VARIABLES_ADMIN or SYSTEM_VARIABLES_ADMIN privilege.

TempTable 存储引擎为 VARCHAR 和 VARBINARY 列以及其他二进制大对象类型提供高效的存储。The TempTable storage engine provides efficient storage for VARCHAR and VARBINARY columns, and other binary large object types.

以下变量控制 TempTable 存储引擎的限制和行为：The following variables control TempTable storage engine limits and behavior:

tmp_table_size：定义使用 TempTable 存储引擎创建的任何单个内存内部临时表的最大大小。当达到由 tmp_table_size 确定的限制时，MySQL 会自动将内存内部临时表转换为 InnoDB 磁盘内部临时表。默认值为 16777216 字节（16 MiB）。tmp_table_size: Defines the maximum size of any individual in-memory internal temporary table created using the TempTable storage engine. When the limit determined by tmp_table_size is reached, MySQL automatically converts the in-memory internal temporary table to an InnoDB on-disk internal temporary table. The default value is 16777216 bytes (16 MiB).
tmp_table_size 限制旨在防止单个查询消耗过多的全局 TempTable 资源，这可能会影响需要这些资源的并发查询的性能。全局 TempTable 资源由 temptable_max_ram 和 temptable_max_mmap 控制。The tmp_table_size limit is intended to prevent individual queries from consuming an inordinate amount of global TempTable resources, which can affect the performance of concurrent queries that require such resources. Global TempTable resources are controlled by temptable_max_ram and 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 限制。If tmp_table_size is less than temptable_max_ram, it is not possible for an in-memory temporary table to use more than tmp_table_size. If tmp_table_size is greater than the sum of temptable_max_ram and temptable_max_mmap, an in-memory temporary table cannot use more than the sum of the temptable_max_ram and temptable_max_mmap limits.
temptable_max_ram：定义 TempTable 存储引擎在开始从内存映射文件分配空间或在 MySQL 开始使用 InnoDB 磁盘内部临时表之前可使用的最大 RAM 内存量，这取决于您的配置。如果未显式设置，temptable_max_ram 的默认值为服务器可用总内存的 3%，最小值为 1 GB，最大值为 4 GB。temptable_max_ram: Defines the maximum amount of RAM that can be used by the TempTable storage engine before it starts allocating space from memory-mapped files or before MySQL starts using InnoDB on-disk internal temporary tables, depending on your configuration. If not set explicitly, the value of temptable_max_ram is 3% of the total memory available on the server, with a minimum of 1 GB and a maximum of 4 GB.
Note
temptable_max_ram 不包括分配给使用 TempTable 存储引擎的每个线程的线程本地内存块。线程本地内存块的大小取决于线程的第一次内存分配请求的大小。如果请求小于 1MB（大多数情况下都是这样），线程本地内存块大小为 1MB。如果请求大于 1MB，线程本地内存块大约与初始内存请求的大小相同。线程本地内存块会一直保存在线程本地存储中，直到线程退出。temptable_max_ram does not account for the thread-local memory block allocated to each thread that uses the TempTable storage engine. The size of the thread-local memory block depends on the size of the thread's first memory allocation request. If the request is less than 1MB, which it is in most cases, the thread-local memory block size is 1MB. If the request is greater than 1MB, the thread-local memory block is approximately the same size as the initial memory request. The thread-local memory block is held in thread-local storage until thread exit.
temptable_use_mmap：控制 TempTable 存储引擎是从内存映射文件分配空间，还是当超过由 temptable_max_ram 决定的限制时，MySQL 使用 InnoDB 内部磁盘临时表。默认值为 OFF。temptable_use_mmap: Controls whether the TempTable storage engine allocates space from memory-mapped files or MySQL uses InnoDB on-disk internal temporary tables when the limit determined by temptable_max_ram is exceeded. The default value is OFF.
Note
temptable_use_mmap 已弃用；预计在未来版本的 MySQL 中将移除对其的支持。将 temptable_max_mmap=0 设置为等同于将 temptable_use_mmap=OFF 设置。temptable_use_mmap is deprecated; expect support for it to be removed in a future version of MySQL. Setting temptable_max_mmap=0 is equivalent to setting temptable_use_mmap=OFF.
temptable_max_mmap：设置 TempTable 存储引擎在 MySQL 开始使用 InnoDB 磁盘上的内部临时表之前，允许从内存映射文件中分配的最大内存量。默认值为 0（禁用）。该限制旨在防止内存映射文件在临时目录中占用过多空间（tmpdir）。temptable_max_mmap = 0 禁止从内存映射文件分配内存，从而有效地禁用其使用，无论 temptable_use_mmap 的值如何。temptable_max_mmap: Sets the maximum amount of memory the TempTable storage engine is permitted to allocate from memory-mapped files before MySQL starts using InnoDB on-disk internal temporary tables. The default value is 0 (disabled). The limit is intended to address the risk of memory mapped files using too much space in the temporary directory (tmpdir). temptable_max_mmap = 0 disables allocation from memory-mapped files, effectively disabling their use, regardless of the value of temptable_use_mmap.

TempTable 存储引擎使用内存映射文件受以下规则约束：Use of memory-mapped files by the TempTable storage engine is governed by these rules:

临时文件会在由 tmpdir 变量定义的目录中创建。Temporary files are created in the directory defined by the tmpdir variable.
临时文件在创建和打开后会立即被删除，因此不会在 tmpdir 目录中可见。临时文件打开时，其占用的空间由操作系统保留。当 TempTable 存储引擎关闭临时文件，或 mysqld 进程关闭时，这些空间会被回收。Temporary files are deleted immediately after they are created and opened, and therefore do not remain visible in the tmpdir directory. The space occupied by temporary files is held by the operating system while temporary files are open. The space is reclaimed when temporary files are closed by the TempTable storage engine, or when the mysqld process is shut down.
数据从不在 RAM 和临时文件之间、RAM 内部或临时文件之间移动。Data is never moved between RAM and temporary files, within RAM, or between temporary files.
如果在temptable_max_ram定义的限制内有可用空间，新数据将存储在 RAM 中。否则，新数据将存储在临时文件中。New data is stored in RAM if space becomes available within the limit defined by temptable_max_ram. Otherwise, new data is stored in temporary files.
如果在将表的一部分数据写入临时文件后 RAM 中有可用空间，那么剩余的表数据可能会存储在 RAM 中。If space becomes available in RAM after some of the data for a table is written to temporary files, it is possible for the remaining table data to be stored in RAM.

当对内存临时表使用 MEMORY 存储引擎时 (internal_tmp_mem_storage_engine=MEMORY)，如果内存临时表变为磁盘表，MySQL 会自动将其转换为磁盘表太大了。内存临时表的最大大小由 tmp_table_size 或 max_heap_table_size 值定义，以两者为准较小。这与使用 CREATE TABLE 显式创建的 MEMORY 表不同。对于此类表，只有 max_heap_table_size 变量决定表可以增长的大小，并且不会转换为磁盘格式。When using the MEMORY storage engine for in-memory temporary tables (internal_tmp_mem_storage_engine=MEMORY), MySQL automatically converts an in-memory temporary table to an on-disk table if it becomes too large. The maximum size of an in-memory temporary table is defined by the tmp_table_size or max_heap_table_size value, whichever is smaller. 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.

磁盘内部临时表的存储引擎Storage Engine for On-Disk Internal Temporary Tables

MySQL 8.4 仅使用 InnoDB 磁盘内部临时表存储引擎表。（MYISAM 存储引擎不再支持此目的。）MySQL 8.4 uses only the InnoDB storage engine for on-disk internal temporary tables. (The MYISAM storage engine is no longer supported for this purpose.)

InnoDB磁盘上的内部临时表默认在数据目录中创建会话临时表空间。有关详细信息，请参见第 17.6.3.5 节，“临时表空间”.InnoDB on-disk internal temporary tables are created in session temporary tablespaces that reside in the data directory by default. For more information, see Section 17.6.3.5, “Temporary Tablespaces”.

内部临时表存储格式Internal Temporary Table Storage Format

当内存内部临时表由 TempTable 存储引擎管理时，包含 VARCHAR 列、VARBINARY 列和其他二进制大对象类型列的行在内存中由单元数组表示，每个单元包含 NULL 标志、数据长度和数据指针。列值按连续顺序放置在数组后面的单个内存区域中，没有填充。数组中的每个单元使用 16 字节的存储空间。当 TempTable 存储引擎从内存映射文件分配空间时，适用相同的存储格式。When in-memory internal temporary tables are managed by the TempTable storage engine, rows that include VARCHAR columns, VARBINARY columns, and other binary large object type columns are represented in memory by an array of cells, with each cell containing a NULL flag, the data length, and a data pointer. Column values are placed in consecutive order after the array, in a single region of memory, without padding. Each cell in the array uses 16 bytes of storage. The same storage format applies when the TempTable storage engine allocates space from memory-mapped files.

当内存内部临时表由 MEMORY 存储引擎管理时，使用固定长度的行格式。VARCHAR 和 VARBINARY 列的值会填充到最大列长度，实际上将它们存储为 CHAR 和 BINARY 列。When in-memory internal temporary tables are managed by the MEMORY storage engine, fixed-length row format is used. VARCHAR and VARBINARY column values are padded to the maximum column length, in effect storing them as CHAR and BINARY columns.

磁盘上的内部临时表始终由 InnoDB 管理。Internal temporary tables on disk are always managed by InnoDB.

使用 MEMORY 存储引擎时，语句最初可以创建一个内存中的内部临时表，然后如果表变得过大，再将其转换为磁盘表。在这种情况下，通过跳过转换并从一开始就在磁盘上创建内部临时表，可能会获得更好的性能。可以使用 big_tables 变量来强制内部临时表存储在磁盘上。When using the MEMORY storage engine, statements can initially create an in-memory internal temporary table and then convert it to an on-disk table if the table becomes too large. In such cases, better performance might be achieved by skipping the conversion and creating the internal temporary table on disk to begin with. The big_tables variable can be used to force disk storage of internal temporary tables.

监控内部临时表的创建Monitoring Internal Temporary Table Creation

当在内存或磁盘上创建内部临时表时，服务器会增加 Created_tmp_tables 值。当在磁盘上创建内部临时表时，服务器会增加 Created_tmp_disk_tables 值。如果在磁盘上创建了过多的内部临时表，请考虑调整内部临时表存储引擎中描述的引擎特定限制。When an internal temporary table is created in memory or on disk, the server increments the Created_tmp_tables value. 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 adjusting the engine-specific limits described in Internal Temporary Table Storage Engine.

Note

由于已知的限制，Created_tmp_disk_tables 不会统计在内存映射文件中创建的磁盘临时表。默认情况下，TempTable 存储引擎的溢出机制会在内存映射文件中创建内部临时表。请参阅内部临时表存储引擎。Due to a known limitation, Created_tmp_disk_tables does not count on-disk temporary tables created in memory-mapped files. By default, the TempTable storage engine overflow mechanism creates internal temporary tables in memory-mapped files. See Internal Temporary Table Storage Engine.

memory/temptable/physical_ram 和 memory/temptable/physical_disk性能模式工具可用于监视内存和磁盘的TempTable空间分配。 memory/temptable/physical_ram 报告分配的 RAM 量。 memory/temptable/physical_disk 报告当内存映射文件用作 TempTable 溢出机制时从磁盘分配的空间量。如果 physical_disk 工具报告 0 以外的值，并且内存映射文件用作 TempTable 溢出机制，则在某个时刻达到了 TempTable 内存限制。可以在性能架构内存摘要表中查询数据，例如 memory_summary_global_by_event_name。请参阅第 29.12.20.10 节“内存摘要表”。The memory/temptable/physical_ram and memory/temptable/physical_disk Performance Schema instruments can be used to monitor TempTable space allocation from memory and disk. memory/temptable/physical_ram reports the amount of allocated RAM. memory/temptable/physical_disk reports the amount of space allocated from disk when memory-mapped files are used as the TempTable overflow mechanism. If the physical_disk instrument reports a value other than 0 and memory-mapped files are used as the TempTable overflow mechanism, a TempTable memory limit was reached at some point. Data can be queried in Performance Schema memory summary tables such as memory_summary_global_by_event_name. See Section 29.12.20.10, “Memory Summary Tables”.

首页上一级下一节