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.

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

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。

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.

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.

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