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

哎呀我的天吶發表於2020-05-24
MySql

官方文件： 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”.


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-2694017/，如需轉載，請註明出處，否則將追究法律責任。

相關文章