MySQL8.0新特性-臨時表的改善

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