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

This article mainly introduces MySQL in what circumstances need to use temporary tables, the text is very detailed, has a certain reference value, interested friends must read!

Introduction to provisional tables

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

Temporary tables are divided into two types, one is memory temporary tables, one is disk temporary tables. Memory temporary table uses memory storage engine, disk temporary table uses myisam storage engine (disk temporary table can also use innodb storage engine, through the internal_tmp_disk_storage_engine parameter to control which storage engine to use, since mysql 5.7.6 default innodb storage engine, previous versions default to myisam storage engine). Use the Created_tmp_disk_tables and Created_tmp_tables parameters to see how many disk temporary tables are generated and all generated temporary tables (memory and disk).

MySQL creates temporary tables in several cases:

1. UNION query;

2. Use TEMPTABLE algorithm or view in UNION query;

3. When the clauses of ORDER BY and GROUP BY are different;

4. In the table join, the column of ORDER BY is not in the driver table;

5, DISTINCT query and add ORDER BY;

6. SQL_SMALL_RESULT option is used in SQL;

7. Subqueries in FROM;

8. Tables created during subquery or semi-join;

EXPLAIN In the Extra column of the execution plan results, if it contains Using Temporary, it means that the temporary table will be used.

Of course, if the amount of data that needs to be stored in the temporary table exceeds the upper limit (tmp-table-size or max-heap-table-size, whichever is greater), then a disk-based temporary table needs to be generated.

Disk temporary tables are created in several cases:

1. The data table contains BLOB/TEXT columns;

Character type columns with more than 512 characters in the GROUP BY or DSTINCT columns (or binary type columns with more than 512 bytes, regardless of whether they exceed 512 bytes before 5.6.15);

In SELECT, UNION, UNION ALL queries, there are columns with a maximum length of more than 512 (512 characters for string types and 512 bytes for binary types);

4. Execute SQL commands such as SHOW COLUMNS/FIELDS and DESCRIBE, because their execution results use BLOB column types.

As of 5.7.5, a new system option internal_tmp_disk_storage_engine defines the engine type of disk temporary tables as InnoDB, whereas previously only MyISAM could be used. The default_tmp_storage_engine option added after 5.6.3 is the engine type that controls the temporary tables created by CREATE TEMPORARY TABLE. Previously, the default was MEMORY. Don't confuse the two.

See below for details

mysql> set default_tmp_storage_engine ="InnoDB";-rw-rw---1 mysql 8558 Jul 7 15:22 #sql4b0e_10_0.frm--Temporal table of InnoDB engine-rw-rw---1 mysql mysql 98304 Jul 7 15:22 #sql4b0e_10_0. ibd-rw-rw---1 mysql mysql 8558 Jul 7 15:25 #sql4b0e_10_2.frmmmysql> set default_tmp_storage_engine ="MyISAM";-rw-rw---1 mysql mysql 0 Jul 7 15:25 #sql4b0e_10_2.MYD--Temporal table of MyISAM engine-rw-rw---1 mysql mysql 1024 Jul 7 15:#sql4b0e_10_2.MYImysql> set default_tmp_storage_engine =" MEMORY";- rw-rw---1 mysql mysql 8558 Jul 7 15:26 #sql4b0e_10_3.frm--Temporary tables of MEMORY engine The above is "MySQL under what circumstances need to use temporary tables" All the contents of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to 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.