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

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。