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

Overview

There are two main types of temporary tables in MySQL, including external temporary tables and internal temporary tables. The external temporary table is through the statement create temporary table... For the temporary table created, the temporary table is only valid for this session. When the session is disconnected, the temporary table data will be cleaned automatically. There are two main types of internal temporary tables, one is temporary tables in information_schema, and the other is when a session executes a query, a temporary table is generated if the execution plan contains "Using temporary". One of the differences between internal temporary tables and external temporary tables is that we cannot see the table structure definition file frm for internal temporary tables. The table definition file frm of external temporary tables is generally composed of # sql {process id} _ {thread id} _ sequence numbers, so different sessions can create temporary tables with the same name.

Temporary watch

The main difference between a temporary table and a regular table is whether the data is automatically cleaned up after the instance, session, or statement ends. For example, the internal temporary table, if we want to store the intermediate result set in a query, and after the query is finished, the temporary table will be automatically reclaimed without affecting the user table structure and data. In addition, temporary tables for different sessions can be renamed, and when all multiple sessions execute queries, there is no worry about using temporary tables. 5.7 with the introduction of temporary tablespaces, all temporary tables are stored in temporary tablespaces (uncompressed), and the data in temporary tablespaces can be reused. Temporary tables not only support Innodb engine, but also support myisam engine, memory engine and so on. Therefore, we cannot see the entity (idb file) of the temporary table, but it is not necessarily a memory table and may be stored in a temporary tablespace.

Temporary table VS memory table

Temporary tables can be either innodb engine table or memory engine table. The so-called memory table here refers to the memory engine table. Through the table-building statement create table... engine=memory, the data is all in memory, and the table structure is managed by frm. The same internal memory engine table can not see the frm file, or even the directory of information_schema on disk. Within MySQL, temporary tables in information_schema contain two types: temporary tables for innodb engines and temporary tables for memory engines. For example, the TABLES table belongs to the memory temporary table, while columns,processlist belongs to the innodb engine temporary table. All the data of the memory table is in memory, and the data structure in memory is an array (heap table), and all data operations are completed in memory. For small data scenarios, the speed is relatively fast (no physical IO operations are involved). However, memory is a limited resource after all, so if the amount of data is relatively large, it is not suitable to use memory tables, but to use disk temporary tables (innodb engine). This temporary table uses B+ tree storage structure (innodb engine). The bufferpool resources of innodb are shared, and the data of temporary tables may have a certain impact on the hot data of bufferpool. In addition, the operation may involve physical IO. Memory engine tables can actually create indexes, including Btree indexes and Hash indexes, so the query speed is very fast, and the main drawback is the limited memory resources.

Scenarios using temporary tables

As mentioned earlier, when "Using temporary" is included in the execution plan, temporary tables are used, and here are two main scenarios.

The test table is structured as follows:

Mysql > show create table t1_normal\ gateway * 1. Row * * Table: t1_normalCreate Table: CREATE TABLE `t1y` (`id` int (11) NOT NULL AUTO_INCREMENT, `c1` int (11) DEFAULT NULL, `c2` int (11) DEFAULT NULL, `c3` int (11) DEFAULT NULL, `c4` int (11) DEFAULT NULL PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=770023 DEFAULT CHARSET=utf8

Scene 1:union

Mysql > explain select * from t1_normal union select * from t1_normal +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | PRIMARY | t1_normal | NULL | ALL | NULL | NULL | NULL | NULL | 523848 | 100.00 | NULL | 2 | UNION | t1_normal | NULL | ALL | NULL | NULL | NULL | NULL | 523848 | 100.00 | NULL | NULL | UNION RESULT | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +- -+

The meaning of the union operation is to take the union of the results of two subqueries, and only one row of duplicate data is retained. The problem of "deduplication" can be solved by establishing a temporary table with a primary key, and the final result set is stored through the temporary table, so you can see that there is a "Using temporary" in the Extra item in the execution plan. One operation related to union is union all, which also merges the results of two subqueries, but does not solve the problem of repetition. So for union all, there is no "de-duplicated" meaning, so there is no need for a temporary watch.

Mysql > explain select * from t1_normal union all select * from t1_normal +-+ | Id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+ -+ | 1 | PRIMARY | t1_normal | NULL | ALL | NULL | 523848 | 100.00 | NULL | 2 | UNION | t1_normal | NULL | ALL | NULL | 523848 | 100.00 | NULL | +- -+

Scene 2:group by

Mysql > explain select C1 count (*) as count from t1_normal group by C1 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | + -- +-+ | 1 | SIMPLE | t1_normal | NULL | ALL | NULL | NULL | 523848 | 100.00 | Using temporary Using filesort | +-+- -- +

Group by means to group by specified column and sort by default by specified column. The meaning of the above SQL statement is to group the data in t1_normal by the value of C1 column and count the number of records for each C1 column value. We see "Using temporary;Using filesort" from the execution plan. For group by, we first need to count the number of each value, which requires the help of a temporary table to quickly locate. If it does not exist, insert a record, if it exists, and accumulate the count, so we see "Using temporary". Then, because group by implies sorting, you also need to sort the records by column C1, so you see "Using filesort".

1)。 Eliminate filesort

In fact, group by can also be shown to eliminate "sort meaning".

Mysql > explain select C1 count (*) as count from t1_normal group by C1 order by null +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -+-+ | 1 | SIMPLE | t1_normal | NULL | ALL | NULL | 523848 | 100.00 | Using temporary | +-+-+ -+

You can see that after "order by null" is added to the statement, "Using filesort" no longer appears in the execution plan.

2)。 Eliminate temporary tables

Mysql > explain select SQL_BIG_RESULT C1 count (*) as count from t1_normal group by C1 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | t1_normal | NULL | ALL | NULL | NULL | NULL | NULL | 523848 | 100.00 | Using filesort | + -- +

You can see that there is no "Using temporary" in the execution plan, so group by does not necessarily rely on temporary tables, and the role of temporary tables in group by is mainly "de-duplicated". So, in fact, there is another way, not using temporary tables, but directly using sort_buffer sorting (when sort_buffer is not enough, file sorting is done, specifically, each ordered array is treated as a separate file, and then extrapolated and merged), and then scanned to get the aggregated result set.

3) .SQL_BIG_RESULT

At the same time, we use the hint of "SQL_BIG_RESULT" in our sentence. It is precisely because of this hint that we do not use temporary tables. Let's talk about the meaning of SQL_BIG_RESULT and SQL_SMALL_RESULT first.

SQL_SMALL_RESULT: displays the specified memory table (memory engine)

SQL_BIG_RESULT: displays the temporary table of specified disks (myisam engine or innodb engine)

The difference between the two is that disk temporary tables can be de-reordered with the help of primary keys, which is suitable for large amounts of data; using memory tables to write faster, and then sort in memory, suitable for small amounts of data. Here are excerpts from the MySQL manual.

SQL_BIG_RESULT or SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set has many rows or is small, respectively.

For SQL_BIG_RESULT, MySQL directly uses disk-based temporary tables if needed, and prefers sorting to using a temporary table with a key on the GROUP BY elements.

For SQL_SMALL_RESULT, MySQL uses fast temporary tables to store the resulting table instead of using sorting.

This should not normally be needed.

Back to the problem itself, here the MySQL optimizer uses disk temporary tables based on hint's knowledge, and ends up choosing a lighter approach such as array storage + file sorting.

How to avoid using temporary tables

The usual way of SQL optimization is to let the column of group by build an index, so when you execute group by, scan the column directly by index and count it, and you don't need temporary and filesort.

Mysql > alter table t1_normal add index idx_c1 (C1); Query OK, 0 rows affected (1 min 23.82 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > explain select C1 as count from t1_normal group by C1 order by null +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -- +-+ | 1 | SIMPLE | t1_normal | NULL | index | idx_c1 | idx_c1 | 5 | NULL | 523848 | 100.00 | Using index | +-+-- -+

Related parameters and status monitoring

1)。 Parameter description

Max_heap_table_size

This variable sets the maximum size to which user-created MEMORY tables are permitted to grow,The value of the variable is used to calculate MEMORY table MAX_ROWS values.

This parameter is mainly aimed at the user-created MEMORY table, which limits the maximum space size of the memory table. Note that it is not the number of records, but is related to the length of a single record. If the threshold is exceeded, an error is reported. ERROR 1114 (HY000): The table 'xxx' is full

Tmp_table_size

The maximum size of internal in-memory temporary tables.

For user-created memory tables, only the parameter max_heap_table_size works; for internally generated memory tables, parameters max_heap_table_size and tmp_table_size work at the same time. For internally generated memory tables (such as temporary tables generated by union,group by, etc.), the memory table (memory table) is used first, and then it is converted to a disk table when it exceeds the set threshold (max_heap_table_size,tmp_table_size), using the innodb engine or myisam engine, specified by the parameter internal_tmp_disk_storage_engine.

Tmpdir

If the memory temporary table exceeds the limit, MySQL automatically converts it to a disk-based MyISAM table, which is stored in the specified tmpdir directory

two。 Status monitoring

Created_tmp_tables, number of internal temporary tables

Created_tmp_disk_tables, number of disk temporary tables

3.information_schema correlation

Mysql > create temporary table t1_tmp (id int primary key,c1 int); Query OK, 0 rows affected (0.02 sec) mysql > SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO +-+-+ | TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED | +-+- -+ | 10063 | # sql693d_29_0 | 5 | 45 | FALSE | FALSE | + -+

Summary

This paper introduces in detail the core features of temporary tables in MySQL, which are created on demand and destroyed automatically, which is especially suitable for data with pure memory, but in order to avoid memory being out of control, there are not only memory temporary tables, but also disk temporary tables. Temporary tables and memory tables are not directly related, because temporary tables can be either a memory engine or an innodb engine. In fact, different types of temporary tables also use the advantages of different engines. The typical scenarios used by temporary tables are union and group by. To eliminate temporary tables, we need to add indexes on group by columns, or for large result sets, use SQL_BIG_RESULT, and so on. Finally, this paper introduces the parameters and state variables related to the temporary table, as well as the temporary table information in information_schema.

These are the details of the MySQL temporary table, please pay attention to other related articles!

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.