When will temporary tables be used in the MySQL Q&A series

Introduction to temporary tables

What is a temporary table: MySQL is a table that stores 1 intermediate result set. 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 generated: 1. Generally, a large number of temporary tables are created due to complex SQL

Temporary tables are divided into two types, one is memory temporary table and the other is disk temporary table. memory storage engine is used for memory temporary table, and myisam storage engine is used for disk temporary table (innodb storage engine can also be used for disk temporary table, which storage engine is controlled by internal_tmp_disk_storage_engine parameter, which defaults to innodb storage engine after mysql5.7. 6 and myisam storage engine in previous versions). The Created_tmp_disk_tables and Created_tmp_tables parameters are used to see how many disk temporary tables are generated and all generated temporary tables (memory and disk).

MySQL creates temporary tables in the following situations:

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 columns of ORDER and BY are not in the driving table;

5. When DISTINCT is queried and ORDER BY is added;

6. When SQL_SMALL_RESULT option is used in SQL;

7. Subquery in FROM;

8. Subquery or table created when semi-join;

EXPLAIN in the Extra column that looks at the results of the execution plan, if Using Temporary is included, a 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 you need to generate a disk-based temporary table.

Temporary disk tables are created in the following cases:

1. The data table contains BLOB/TEXT columns;

2. In the column of GROUP, BY or DSTINCT, there are character type columns exceeding 512 characters (or binary type columns exceeding 512 bytes, regardless of whether they exceed 512 bytes before 5.6. 15);

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

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

Starting from 5.7. 5, a new system option, internal_tmp_disk_storage_engine, defines the engine type of the disk temporary table as InnoDB, whereas until then only MyISAM was used. The new system option default_tmp_storage_engine after 5.6. 3 is the engine type that controls the temporary tables created by CREATE TEMPORARY TABLE, and the default is MEMORY before. Don't confuse the two.

See the following example for details

mysql> set default_tmp_storage_engine = "InnoDB";
-rw-rw---- 1 mysql mysql 8558 Jul 7 15:22 #sql4b0e_10_0.frm -- InnoDB Temporary table of 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.frm

mysql> set default_tmp_storage_engine = "MyISAM";
-rw-rw---- 1 mysql mysql 0 Jul 7 15:25 #sql4b0e_10_2.MYD -- MyISAM Temporary table of engine 
 -rw-rw---- 1 mysql mysql 1024 Jul 7 15:25 #sql4b0e_10_2.MYI

mysql> set default_tmp_storage_engine = "MEMORY";
-rw-rw---- 1 mysql mysql 8558 Jul 7 15:26 #sql4b0e_10_3.frm -- MEMORY Temporary table of engine 

Summarize