Shulou(Shulou.com)05/31 Report--

This article mainly introduces under what circumstances will use the mysql temporary table, the article introduces in great detail, has a certain reference value, interested friends must read it!

MySQL uses temporary tables in many cases, so summarize when temporary tables will be used:

What is a temporary table: a table used by MySQL to store some intermediate result sets, the temporary table is visible only in the current connection, and Mysql automatically deletes the table and frees up all space when the connection is closed. Why temporary tables are generated: it is generally due to complex SQL that temporary tables are created in large numbers

There are two kinds of temporary tables, one is memory temporary table, the other is disk temporary table. The memory temporary table uses the memory storage engine, and the disk temporary table uses the myisam storage engine (the disk temporary table can also use the innodb storage engine, which is controlled by the internal_tmp_disk_storage_engine parameter. It defaults to the innodb storage engine after mysql5.7.6, while the previous version defaults to the myisam storage engine). Use the parameters Created_tmp_disk_tables and Created_tmp_tables to see how many disk temporary tables and all generated temporary tables (memory and disk) have been generated.

The size of the temporary memory tablespace is controlled by two parameters: tmp_table_size and max_heap_table_size. Generally speaking, the maximum value of the temporary table space in memory is controlled by the smaller number of the two parameters, while the temporary table created in memory is later transferred to the temporary table on disk because the data is too large, which is only controlled by the max_heap_table_size parameter. There is no size control for temporary tables generated directly on disk.

Temporary tables are used for the following actions:

1 "union query"

2 for view operations, such as using some TEMPTABLE algorithm, union, or aggregation

3. Subquery.

4 semi-join includes not in, exist, etc.

5 derived tables generated by the query

6 complex group by and order by

7 Insert select the same table, mysql will generate a temporary table to cache the row of select

8 updates for multiple tables

9 GROUP_CONCAT () or COUNT (DISTINCT) statement

. . .

Mysql also prevents the use of memory tablespaces and uses disk temporary tables directly:

1 the table contains BLOB or TEXT columns

2 when union or union all is used, the select clause has columns greater than 512 bytes

3 when the Show columns or desc table is updated, there is LOB or TEXT

4 column GROUP BY or DISTINCT clause contains columns greater than 512 bytes in length

The above is all the contents of the article "under what circumstances will you use mysql temporary tables"? thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.