Shulou(Shulou.com)06/01 Report--

What is Internal Temporary?

There are two kinds of temporary tables. One is some intermediate result sets, called internal temporary, that MySQL automatically creates when executing some SQL. These intermediate result sets may be placed in memory or on disk.

There is also an external temporary table generated by manual execution of create temporary table syntax. This temporary table is stored on memory, and the database shutdown is automatically deleted.

The temporary tables mentioned in this article all refer to internal temporary tables, and the MySQL version used in the test is 8.0.13.

How to tell if an internal temporary watch is used?

Using temporary appears in execution plan explain or explain format=json

Increase of Created_tmp_disk_tables or Created_tmp_tables values in show status

Under what circumstances does Internal temporary table occur?

(1) except for the special cases mentioned later, all SQL using union, but using union all does not use temporary tables

(2) using TEMPTABLE algorithm or views in UNION query

Mysql > desc select * from t_order union select * from t_group +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -- +-+ | 1 | PRIMARY | t_order | NULL | ALL | NULL | 10 | 100.00 | NULL | | 2 | UNION | t_group | NULL | ALL | | NULL | 10 | 100.00 | NULL | | NULL | UNION RESULT | | NULL | ALL | NULL | Using temporary | + -+ 3 rows in set 1 warning (0.01sec) but using union all without using temporary table mysql > desc select * from t_order union all select * from t_group +-+ | id | select_ Type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -- + | 1 | PRIMARY | t_order | NULL | ALL | NULL | 10 | 100.00 | NULL | 2 | UNION | t_group | NULL | ALL | NULL | 10 | 100.00 | NULL | +- -+ 2 rows in set 1 warning (0.00 sec)

(3) use derivative tables

(4) subquery and semi-join

Mysql > desc select / * + set_var (optimizer_switch='derived_merge=off') * / * from (select * from t_order) t +-+ | id | | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+ -- + | 1 | PRIMARY | | NULL | ALL | NULL | 10 | 100.00 | NULL | 2 | DERIVED | t_order | NULL | ALL | NULL | 10 | 100.00 | NULL | + -+ 2 rows in set 1 warning (0.01 sec) mysql > desc format=json select / * + set_var (optimizer_switch='derived_merge=off') * / * from (select * from t_order) t . "materialized_from_subquery": {"using_temporary_table": true

(5) when the clauses of order by and group by are different, or the column of order by or group by in the table join is the column of the driven table

When using order by and group by at the same time:

Mysql > desc select dept_no from t_order group by dept_no order by dept_no +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | t_order | NULL | ALL | NULL | 10 | 100.00 | Using temporary Using filesort | +-+- -+ 1 row in set 1 warning (0.00 sec) or: mysql > set session sql_mode='' Query OK, 0 rows affected (0.00 sec) mysql > desc select dept_no from t_order group by dept_no order by emp_no +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | t_order | NULL | ALL | NULL | 10 | 100.00 | Using temporary Using filesort | +-+- -+ 1 row in set 1 warning (0.00 sec)

When using order by and group by and join respectively:

Mysql > desc select * from t_group T1 join t_order T2 on t1.emp_no=t2.emp_no order by t2.emp_no +- -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+- -- + | 1 | SIMPLE | T1 | NULL | ALL | NULL | 10 | 100.00 | Using temporary Using filesort | | 1 | SIMPLE | T2 | NULL | ref | ix_t1 | ix_t1 | 5 | employees.t1.emp_no | 1 | 100.00 | NULL | +- -+-- + 2 rows in set 1 warning (0.00 sec) mysql > desc select * from t_group T1 join t_order T2 on t1.emp_no=t2.emp_no order by t1.emp_no +- -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | T1 | NULL | ALL | NULL | 10 | 100.00 | Using filesort | | 1 | SIMPLE | T2 | NULL | ref | ix_t1 | ix_t1 | 5 | employees.t1.emp_no | 1 | 100.00 | NULL | +- -+ 2 rows in set 1 warning (0.00 sec) mysql > desc select t1.dept_no from t_group T1 join t_order T2 on t1.emp_no=t2.emp_no group by t1.dept_no +- -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | T1 | NULL | ALL | NULL | NULL | 10 | 100.00 | Using temporary | | 1 | SIMPLE | T2 | NULL | ref | ix_t1 | ix_t1 | 5 | employees.t1.emp_no | 1 | 100.00 | Using index | + -+ 2 rows in set 1 warning (0.00 sec) mysql > desc select t2.dept_no from t_group T1 join t_order T2 on t1.emp_no=t2.emp_no group by t2.dept_no +- -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | T1 | NULL | ALL | NULL | NULL | 10 | 100.00 | Using temporary | | 1 | SIMPLE | T2 | NULL | ref | ix_t1 | ix_t1 | 5 | employees.t1.emp_no | 1 | 100.00 | NULL | + -+ 2 rows in set 1 warning (0.00 sec)

(6) when using distinct or distinct to collect ORDER BY

Mysql > desc select distinct * from t_order +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | t_order | NULL | ALL | NULL | 10 | 100.00 | Using temporary | + -+ 1 row in set 1 warning (0.00 sec)

(7) when the SQL_SMALL_RESULT option is used in SQL

(8) INSERT... When SELECT operates on the same table

Mysql > desc insert into t_order select * from t_order +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -- + | 1 | INSERT | t_order | NULL | ALL | NULL | 1 | SIMPLE | t_order | NULL | ALL | NULL | NULL | 10 | 100.00 | Using temporary | +- -+ 2 rows in set 1 warning (0.00 sec)

(9) use GROUP_CONCAT () or COUNT (DISTINCT)

Generate a temporary table when using group_concat ():

Mysql > flush status;Query OK, 0 rows affected (0.02 sec) mysql > select dept_no,group_concat (emp_no) from t_order group by dept_no +-+-+ | dept_no | group_concat (emp_no) | +-+-+ | d002 | 31112 | d004 | 10004 | | d005 | 24007, 30970, 40983, 50449 | | d006 | 22744 | | d007 | 49667 | | d008 | 48317 | +-+-+ 6 rows in set (49667 sec) mysql > show status like'% tmp%' | +-+-+ | Variable_name | Value | +-+-+ | Created_tmp_disk_tables | 0 | Created_tmp_files | 0 | Created_tmp_tables | 1 | +-- -+-+ 3 rows in set (0.00 sec)

Generate temporary tables when using count (distinct):

Mysql > flush status;Query OK, 0 rows affected (0.02 sec) mysql > desc select count (distinct dept_no) from t_order +-+ | id | select_ Type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -- + | 1 | SIMPLE | t_order | NULL | ALL | NULL | 10 | 100.00 | NULL | + -+ 1 row in set 1 warning (0.00 sec) mysql > show status like'% tmp%' +-+-+ | Variable_name | Value | +-+-+ | Created_tmp_disk_tables | 0 | Created_tmp_files | 0 | Created_tmp_tables | 1 | +-- -- +-+ 3 rows in set (0.01 sec)

When is the internal temporary table generated not in memory, but on disk?

(1) the table is stored in blob or text field

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

(3) use the show columns and describe commands on tables with blob columns

What storage engine is used for internal temporary tables?

MySQL8.0.2 starts to support internal_tmp_mem_storage_engine parameters

(1) when internal_tmp_mem_storage_engine=TempTable

The TempTable storage engine provides efficient storage for varchar and varbinary data types. Temptable_max_ram=1G defines the maximum memory space that can be used by temporary tables, but if the parameter temptable_use_mma=on, temporary tables can continue to be stored in memory. If off, temporary tables exceed the threshold and can only be stored on disk.

(2) when internal_tmp_mem_storage_engine=memory:

When the size of the internal temporary table exceeds the parameters tmp_table_size and max_heap_table_size, it will be automatically transferred from memory to disk. By default, the internal temporary table uses the innodb storage engine on disk, which is determined by the parameter internal_tmp_disk_storage_engine.

Reference link

Internal Temporary Table Use in MySQL

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.