When will the temporary table be used? Summary of usage of MySQL temp table

Summary:In many cases, MySQL will use a temporary table to summarize when it will be used to temporary tables what is a temporary table mysql is used to store some intermediate result sets of the table temporary table only when the current connection is visible when the connection is closed, MySQL automatically deletes the table and frees all the space.

In many cases, MySQL uses temporary tables to summarize when temporary tables are used:

What is a temp table: MySQL is used to store some intermediate result sets of the table, the temporary table is only visible in the current connection, and when the connection is closed, MySQL automatically deletes the table and frees all the space. Why a temporary table is generated: typically due to complex SQL causing temporary tables to be created massively

Temporary tables are divided into two types, one is the temporary memory table, and the other is the disk temporary table. The memory temp table uses the Memories storage engine, which uses the MyISAM storage engine (the disk temp table can also use the InnoDB storage engine, which uses the Internal_tmp_disk_storage_engine parameter to control which storage engine is used. The default is the InnoDB storage engine from mysql5.7.6, and the previous version defaults to the MyISAM storage engine. Use the Created_tmp_disk_tables and created_tmp_tables two parameters respectively to see how many disk staging tables and all the resulting temporary tables (memory and disk) were generated.

The size of the memory temp table space is controlled by two parameters: Tmp_table_size and Max_heap_table_size. In general, the maximum value of the memory temporary table space is controlled by the smaller number of two parameters, and the temporary table that is created in memory is later transferred to the temporary tables on the disk because the data is too large, only controlled by the Max_heap_table_size parameter. There is no size control for temporary tables that are generated directly on disk.

The following actions are used to temporary tables:

1ãâunion Query

2ãâ operations for views, such as using some temptable algorithms, union, or aggregation

3ãâ Sub-query

4ãâsemi-join including not in, exist, etc.

Derived tables generated by the 5ãâ query

6ãâ Complex GROUP BY and order by

7ãâinsert Select the same table, MySQL generates a temporary table to cache select rows

8ãâ Multiple table Updates

9ãâgroup_concat () or COUNT (DISTINCT) statements

。。。

MySQL also blocks the use of memory table space and uses disk staging tables directly:

The 1ãâ table contains a BLOB or text column

2ãâ when using union or union ALL, the SELECT clause has a column greater than 512 bytes

3ãâshow columns or desc table, there is lob or text

4ãâgroup a column with a length greater than 512 bytes in a by or DISTINCT clause