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

This article mainly introduces the relevant knowledge of how to use the mysql temporary table, the content is detailed and easy to understand, the operation is simple and fast, and has a certain reference value. I believe you will gain something after reading this article on how to use the mysql temporary table. Let's take a look.

In mysql, a temporary table refers to a table used temporarily to store some intermediate result sets; a temporary table is visible only in the current connection, and when the connection is closed, Mysql automatically deletes the table and frees up all space.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

A temporary table is a table for temporary use.

Temporary tables are tables that MySQL uses to store some intermediate result sets. Temporary tables are only visible in the current connection. When the connection is closed, Mysql automatically deletes the table and frees up all space.

If you use another MySQL client program to connect to the MySQL database server to create a temporary table, the temporary table will only be destroyed when the client program is closed, or it can be deleted manually.

Note: temporary tables are added in MySQL 3.23. If your MySQL version is earlier than 3.23, you will not be able to use MySQL temporary tables. However, it is rare to use such a low version of the MySQL database service now.

Two kinds of temporary tables in MySQL

External temporary table

Temporary tables created by CREATE TEMPORARY TABLE are called external temporary tables. This temporary table is visible only to the current user and is automatically closed when the current session ends. This temporary table can be named with the same name as a non-temporary table (after the same name, the non-temporary table will not be visible to the current session until the temporary table is deleted).

Internal temporary table

An internal temporary table is a special lightweight temporary table used for performance optimization. This temporary table is automatically created by MySQL and used to store the intermediate results of certain operations. These operations may be included in the optimization phase or the execution phase. This internal table is not visible to the user, but through EXPLAIN or SHOW STATUS you can see if MYSQL uses an internal temporary table to help with an operation. Internal temporary tables play a very important role in the optimization of SQL statements, and many operations in MySQL rely on internal temporary tables for optimization. However, using internal temporary tables requires the creation of tables and the access cost of intermediate data, so users should try their best to avoid using temporary tables when writing SQL statements.

There are two types of internal temporary tables:

One is the HEAP temporary table, where all the data of the temporary table is stored in memory, and no IO operation is required for such a table.

The other is the OnDisk temporary table, which, as its name implies, stores data on disk. The OnDisk temporary table is used to handle operations with large intermediate results.

If the data stored in the HEAP temporary table is larger than the MAX_HEAP_TABLE_SIZE,HEAP temporary table, it will be automatically converted to the OnDisk temporary table.

The OnDisk temporary table can choose to use either the MyISAM engine or the InnoDB engine through the INTERNAL_TMP_DISK_STORAGE_ENGINE system variable.

Common uses of external temporary tables

External temporary tables are operated through CREATE TEMPORARY TABLE and DROP TABLE, but when the SHOW TABLES command displays a list of datasheets, you will not be able to see the temporary tables you have created. And after exiting the current session, the temporary table is automatically destroyed. Of course, it can also be destroyed manually (DROP TABLE).

1. Engine type: only: memory (heap), myisam, merge, innodb. Mysql cluster (cluster) is not supported.

2. Pay attention to several points when using external temporary tables:

1) the database account you use must have the permission to create temporary tables

2) you cannot associate the same temporary table twice in the same sql, otherwise, the following error will be reported

Mysql > select * from temp_table, temp_table as T2; error 1137: can't reopen table: 'temp_table'

3) the temporary table is visible when the connection is established, and the space is cleared and the temporary table is deleted when it is closed.

4), show tables will not list temporary tables

5) temporary tables cannot be renamed using rename. However, you can use alter table instead: only use alter table old_tp_table_name rename new_tp_table_name

6) affect the use of replication function

If you declare an alias for a table, you must use that alias when you point to the table. See "MySQL multi-table association update and deletion"

Example:

Mysql > CREATE TEMPORARY TABLE SalesSummary (- > product_name VARCHAR (50) NOT NULL->, total_sales DECIMAL (12Power2) NOT NULL DEFAULT 0.00->, avg_unit_price DECIMAL (7L2) NOT NULL DEFAULT 0.00->, total_units_sold INT UNSIGNED NOT NULL DEFAULT 0) Query OK, 0 rows affected (0.00 sec) mysql > INSERT INTO SalesSummary-> (product_name, total_sales, avg_unit_price, total_units_sold)-> VALUES-> ('cucumber', 100.25, 90, 2); mysql > SELECT * FROM SalesSummary +-+ | product_name | total_sales | avg_unit_price | total_units_sold | +- -+-+ | cucumber | 100.25 | 90.00 | 2 | +- -- + 1 row in set (0.00 sec) mysql > DROP TABLE SalesSummary Mysql > SELECT * FROM SalesSummary; ERROR 1146: Table 'RUNOOB.SalesSummary' doesn't exist

3. Temporary table operation in mybatis

CREATE TEMPORARY TABLE IF NOT EXISTS temp SELECT * FROM settlement_temp WHERE settle_date=# {settleDate} AND LENGTH (operator) IN (16,032) AND pay_status IN ('01mcm.06') ORDER BY settle_date,merchant_no DROP TEMPORARY TABLE IF EXISTS settlement_temp

Common uses of internal temporary tables

If users can use internal temporary tables for query optimization as little as possible when writing SQL statements, it will effectively improve the efficiency of query execution.

First we define a table T1

CREATE TABLE T1 (an int, b int); INSERT INTO T1 VALUES (1), (3)

All of the following operations are based on table T1.

Using SQL_BUFFER_RESULT hint in SQL statements

SQL_BUFFER_RESULT is mainly used to allow MySQL to release locks on the table as soon as possible. Because if the amount of data is large, it takes a long time to send the data to the client, and the time taken by the read lock to the table can be effectively reduced by buffering the data to the temporary table. For SQL_BUFFER_RESULT, see "mysql query Optimization part III: query Optimizer hint (hint)"

For example:

Mysql > explain format=json select SQL_BUFFER_RESULT * from T1 EXPLAIN {"query_block": {"select_id": 1, "cost_info": {"query_cost": "2.00"}, "buffer_result": {"using_temporary_table": true, "table": {"table_name": "T1" Access_type: ALL,...

If DERIVED_TABLE is included in the SQL statement.

As a result of the new optimization approach, we need to use set optimizer_switch='derived_merge=off' to prevent derived table from merging into the outer Query.

For example:

Mysql > explain format=json select * from (select * from T1) as tt; EXPLAIN {"query_block": {"select_id": 1, "cost_info": {"query_cost": "2.40"}," table ": {" table_name ":" tt "," access_type ":" ALL ",. Materialized_from_subquery: {"using_temporary_table": true,...

If we query the system table, the data of the system table will be stored in the internal temporary table.

We cannot currently use EXPLAIN to see whether reading system table data requires the use of internal temporary tables, but we can use SHOW STATUS to see if internal temporary tables are utilized.

For example:

Mysql > select * from information_schema.character_sets;mysql > show status like 'CREATE%'

If the DISTINCT statement is not optimized, that is, the DISTINCT statement is optimized to convert to a GROUP BY operation or use UNIQUE INDEX to eliminate DISTINCT, the internal temporary table will be used.

Mysql > explain format=json select distinct a from T1; EXPLAIN {{"query_block": {"select_id": 1, "cost_info": {"query_cost": "1.60"}, "duplicates_removal": {"using_temporary_table": true,.

If the query has an ORDER BY statement and cannot be optimized. The following situations use internal temporary tables to cache intermediate data, and then sort the intermediate data.

1) if the join table uses BNL (Batched Nestloop) / BKA (Batched Key Access)

For example:

1) BNL is turned on by default

Mysql > explain format=json select * from T1, T1 as T2 order by t1.an explain {"query_block": {"select_id": 1, "cost_info": {"query_cost": "22.00"}, "ordering_operation": {"using_temporary_table": true,.

2) when BNL is turned off, ORDER BY will directly use filesort.

Mysql > set optimizer_switch='block_nested_loop=off';Query OK, 0 rows affected (0.00 sec) mysql > explain format=json select * from T1, T1 as T2 order by t1.a politics explain {"query_block": {"select_id": 1, "cost_info": {"query_cost": "25.00"}, "ordering_operation": {"using_filesort": true,...

2) the column of ORDER BY does not belong to the column of the first join table in the execution plan.

For example:

Mysql > explain format=json select * from t as T1, t as T2 order by t2.aExplin {"query_block": {"select_id": 1, "cost_info": {"query_cost": "25.00"}, "ordering_operation": {"using_temporary_table": true,.

3) if the expression of ORDER BY is a complex expression.

So what kind of ORDER BY expression does MySQL think is a complex expression?

1) if the sort expression is SP or UDF.

For example:

Drop function if exists func1;delimiter | create function func1 (x int) returns int deterministicbegindeclare Z1, Z2 int;set Z1 = x position set Z2 = Z1lesson 2 return Z2 political end | delimiter; explain format=json select * from T1 order by func1 (a); {"query_block": {"select_id": 1, "cost_info": {"query_cost": "2.20"}, "ordering_operation": {"using_temporary_table": true,.

2) the column of ORDER BY contains the aggregate function

To simplify the execution plan, we use INDEX to optimize the GROUP BY statement.

For example:

Create index idx1 on T1 (a); explain format=json SELECt a FROM T1 group by an order by sum (a) | | {"query_block": {"select_id": 1, "cost_info": {"query_cost": "1.20"}, "ordering_operation": {"using_temporary_table": true, "using_filesort": true, "grouping_operation": {"using_filesort": false | ... Drop index idx1 on t1

3) the column of ORDER BY contains SCALAR SUBQUERY, but of course the SCALAR SUBQUERY is not optimized.

For example:

Explain format=json select (select rand () from T1 limit 1) as a from T1 order by a | {"query_block": {"select_id": 1, "cost_info": {"query_cost": "1.20"}," ordering_operation ": {" using_temporary_table ": true," using_filesort ": true,...

4) if the query has both ORDER BY and GROUP BY statements, but the two statements use different columns.

Note: if it is 5. 7, we need to set sql_mode to non-only_full_group_by mode, otherwise an error will be reported.

Also to simplify the execution plan, we use INDEX to optimize the GROUP BY statement.

For example:

Set sql_mode='';create index idx1 on T1 (b); explain format=json select T1. A from T1 group by T1. B order by 1 | | {"query_block": {"select_id": 1, "cost_info": {"query_cost": "1.40"}," ordering_operation ": {" using_temporary_table ": true," using_filesort ": true," grouping_operation ": {" using_filesort ": false | ... drop index idx1 on t1

If the query has a GROUP BY statement and cannot be optimized. The following situations take advantage of internal temporary tables to cache intermediate data, and then GROUP BY the intermediate data.

1) if the join table uses BNL (Batched Nestloop) / BKA (Batched Key Access).

For example:

Explain format=json select t2.a from t1, t1 as t2 group by t1.a | | {"query_block": {"select_id": 1, "cost_info": {"query_cost": "8.20"}, "grouping_operation": {"using_temporary_table": true, "using_filesort": true | "cost_info": {"sort_cost": "4.00"

2) if the column of GROUP BY does not belong to the first join table in the execution plan.

For example:

Explain format=json select t2.a from t1, t1 as t2 group by t2.a | {"query_block": {"select_id": 1, "cost_info": {"query_cost": "8.20"}, "grouping_operation": {"using_temporary_table": true, "using_filesort": true, "nested_loop": [.

3) if the columns used by the GROUP BY statement are different from those used by the ORDER BY statement.

For example:

Set sql_mode=''; explain format=json select t1.a from t1 group by t1.b order by t1.a | | {"query_block": {"select_id": 1, "cost_info": {"query_cost": "1.40"}," ordering_operation ": {" using_filesort ": true | Grouping_operation: {"using_temporary_table": true, "using_filesort": false,...

4) if GROUP BY has ROLLUP and is based on multiple out-of-table joins.

For example:

Explain format=json select sum (T1. A) from T1 left join T1 as T2 on true group by t1.a with rollup | | {"query_block": {"select_id": 1, "cost_info": {"query_cost": "7.20"}, "grouping_operation": {"using_temporary_table": true | "using_filesort": true, "cost_info": {"sort_cost": "4.00"},.

5) if the column used by the GROUP BY statement is from SCALAR SUBQUERY and is not optimized.

For example:

Explain format=json select (select avg (a) from T1) as a from T1 group by a; | {"query_block": {"select_id": 1, "cost_info": {"query_cost": "3.40"}," grouping_operation ": {" using_temporary_table ": true," using_filesort ": true," cost_info ": {" sort_cost ":" 2.00 "},.

Convert IN expressions to semi-join for optimization

1) if the execution mode of semi-join is Materialization

For example:

Set optimizer_switch='firstmatch=off,duplicateweedout=off';explain format=json select * from T1 where ain (select b from T1) | {"query_block": {"select_id": 1, "cost_info": {"query_cost": "5.60"}, "nested_loop": [{"rows_examined_per_scan": 1, "materialized_from_subquery": {"using_temporary_table": true, "query_block": {"table": {"table_name": "T1", "access_type": "ALL",...

2) if the execution mode of semi-join is Duplicate Weedout

For example:

Set optimizer_switch='firstmatch=off';explain format=json select * from T1 where ain (select b from T1); | {"query_block": {"select_id": 1, "cost_info": {"query_cost": "4.80"}," duplicates_removal ": {" using_temporary_table ": true," nested_loop ": [{...

If the query statement has a UNION,MySQL, an internal temporary table will be used to help eliminate duplication of UNION operations.

For example:

Explain format=json select * from T1 union select * from T1; | {"query_block": {"union_result": {"using_temporary_table": true, "table_name": "",.

If the query statement uses multiple table updates.

Explain cannot see that the internal temporary table is being utilized here, so you need to check the status.

For example:

Update T1, T1 as T2 set t1.astat3 show status like 'CREATE%'

If the aggregate function contains the following functions, the internal temporary table will also be utilized.

1) count (distinct *) for example: explain format=json select count (distinct a) from T1 * 2) group_concat for example: explain format=json select group_concat (b) from T1

All in all, there are 10 cases listed above. MySQL will use internal temporary tables for intermediate result caching, and if the amount of data is large, internal temporary tables will store data on disk, which will obviously have an impact on performance. In order to minimize the performance loss, we need to avoid the above situation as much as possible.

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 table join, the column of ORDER BY is not in the driven table; 5, when DISTINCT query and add ORDER BY; 6, when SQL_SMALL_RESULT option is used in SQL; 7, subquery in FROM; 8, table created in subquery or semi-join

EXPLAIN looks at the Extra column of the execution plan result, and 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 (whichever is the greater in tmp-table-size or max-heap-table-size), you need to generate a disk-based temporary table.

A disk temporary table is created in the following cases:

1. The data table contains BLOB/TEXT columns; 2. There are character type columns with more than 512 characters in the columns of GROUP BY or DSTINCT (or binary type columns with more than 512 bytes, regardless of whether they exceed 512 bytes before 5.6.15). 3. In SELECT, UNION, 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) 4. Execute SQL commands such as SHOW COLUMNS/FIELDS, DESCRIBE, etc., because their execution results use the blob column type.

Starting with 5.7.5, a new system option, internal_tmp_disk_storage_engine, defines the engine type of the disk temporary table as InnoDB, whereas before that, only MyISAM could be 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, which used to be MEMORY by default, so don't confuse the two.

See the following example:

Mysql > set default_tmp_storage_engine = "InnoDB";-rw-rw---- 1 mysql mysql 8558 Jul 7 15:22 # sql4b0e_10_0.frm-temporary table for 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.frmmysql > set default_tmp_storage_engine = "MyISAM" -rw-rw---- 1 mysql mysql 0 Jul 7 15:25 # sql4b0e_10_2.MYD-- temporary table for MyISAM engine-rw-rw---- 1 mysql mysql 1024 Jul 7 15:25 # 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 for MEMORY engines. That's all for the article "how to use mysql temporary tables". Thank you for reading! I believe you all have a certain understanding of the knowledge of "how to use mysql temporary tables". If you want to learn more, you are 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.