当前位置：首页 > 数据库 > MySQL教程 > MySQL8.0新特性-临时表的改善 > 正文

MySQL8.0新特性-临时表的改善

来源：这里教程网 时间：2026-03-01 15:13:23 作者：

官方文档： https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html 8.0.13 允许使用temptable engine（新引擎）、memory engine的临时表，此前5.6 为myisam，5.7为Innodb  在某些情况下，MySQL在处理语句时会创建内部临时表。都是server自动生成，用户无法直接控制内部临时表都创建。 默认会有如下情况创建使用临时表

派生表derived tables (see  Section 13.2.11.8, “Derived Tables”).

with as语句； 

为子查询或半联接物化创建的表

对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.

  INSERT ... SELECT插入当前表， MySQL 会暂时创建内部临时表，保存 select的数据，然后再插入表中；多表关联update更新 .

  •   GROUP_CONCAT() or  COUNT(DISTINCT) 语句

  • 开窗函数，

    判断一个语句是否用到临时表，使用explain看执行计划，并且看 Extra列是否有 Using temporary

     (see  Section 8.8.1, “Optimizing Queries with EXPLAIN”).  EXPLAIN will 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:

  • Presence of a  BLOB or  TEXT column in the table. However, the  TempTable storage engine, which is the default storage engine for in-memory internal temporary tables in MySQL 8.0, supports binary large object types as of MySQL 8.0.13. See  Internal Temporary Table Storage Engine.

  • 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.

  • 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.

    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.

    下面的情况，不会使用到内部临时表

  • 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

    内部临时表可以保存在内存中，由可试探的或内存存储引擎处理，或者由InnoDB存储引擎存储在磁盘上。 processed by the  TempTable  or  MEMORY  storage engine,

    Storage Engine for In-Memory Internal Temporary Tables

    The  internal_tmp_mem_storage_engine session variable defines the storage engine for in-memory internal temporary tables. Permitted values are  TempTable (the default) and  MEMORY.

    The  TempTable storage engine provides efficient storage for  VARCHAR and  VARBINARY columns. Storage of other binary large object types is supported as of MySQL 8.0.13. The  temptable_max_ram variable defines the maximum amount of RAM that can be occupied by the  TempTable storage engine before it starts allocating space from disk in the form memory-mapped temporary files or  InnoDB on-disk internal temporary tables. The default  temptable_max_ram setting is 1GiB. The  temptable_use_mmap variable (introduced in MySQL 8.0.16) controls whether the TempTable storage engine uses memory-mapped files or  InnoDB on-disk internal temporary tables when the  temptable_max_ram limit is exceeded. The default setting is  temptable_use_mmap=ON.

    Note

    The  temptable_max_ram setting 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.

    Use of memory-mapped temporary files by the  TempTable storage engine as an overflow mechanism for internal temporary tables is governed by these rules:

  • Temporary files are created in the directory defined by the  tmpdir variable.

  • 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.

  • Data is never moved between RAM and temporary files, within RAM, or between temporary files.

  • 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.

  • 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.

    If the  TempTable storage engine is configured to use  InnoDB on-disk internal temporary tables as the overflow mechanism ( temptable_use_mmap=OFF), an in-memory table that exceeds the  temptable_max_ram limit is converted to an  InnoDB on-disk internal temporary table, and any rows belonging to that table are moved from memory to the  InnoDB on-disk internal temporary table. The  internal_tmp_disk_storage_engine setting (removed in MySQL 8.0.16) has no affect on the  TempTable storage engine overflow mechanism.

    Consider using  InnoDB on-disk internal temporary tables as the  TempTable overflow mechanism if the TempTable storage engine often exceeds the the  temptable_max_ram limit and uses excessive space in the temporary directory for memory-mapped files. This may occur due to use of large internal temporary tables or extensive use of internal temporary tables.  InnoDB on-disk internal temporary tables are created in session temporary tablespaces, which reside in the data directory by default. For more information, see  Section 15.6.3.5, “Temporary Tablespaces”.

    当使用 MEMORY storage engine for in-memory temporary tables, 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.

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

    从MySQL 8.0.16开始，MySQL始终使用InnoDB存储引擎来管理磁盘上的内部临时表。8.0.15之前的版本使用internal_tmp_disk_storage_engine参数来定义内部临时表引擎，在MySQL8.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行或列限制的磁盘内部临时表的查询将返回Row size too large或者Too many columns错误。 解决方法是将internal_tmp_disk_storage_engine设置为MYISAM。

    Internal Temporary Table Storage Format

    When in-memory internal temporary tables are managed by the  TempTable storage engine, rows that include  VARCHAR columns,  VARBINARY columns, or other binary large object type columns (supported as of MySQL 8.0.13) 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 exceeds the  temptable_max_ram limit and starts allocating space from disk as memory-mapped files or  InnoDB on-disk internal temporary tables.

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

    Previous to MySQL 8.0.16, on-disk internal temporary tables were managed by the  InnoDB or  MyISAM storage engine (depending on the  internal_tmp_disk_storage_engine setting). Both engines store internal 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. Beginning with MySQL 8.0.16,  internal_tmp_disk_storage_engine is not supported, and internal temporary tables on disk are always handled by  InnoDB.

    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

    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 increasing the  tmp_table_size and  max_heap_table_size settings.

    Note

    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. This behavior is controlled by the  temptable_use_mmap variable, which is enabled by default.

    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 ( temptable_use_mmap=ON). If the  physical_disk instrument reports a value other than 0 and memory-mapped files are used as the TempTable overflow mechanism, the  temptable_max_ram threshold 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 26.12.18.10, “Memory Summary Tables”.

    • 编辑推荐：

    相关推荐

    最新软件资讯

    MySQL8.0新特性-临时表的改善
    MySQL8.0新特性-临时表的改善

    热文推荐

    天极热推