mysql what is a temporary table

In mysql, a temporary table refers to a table that is temporarily used and is used to store some intermediate result sets; the temporary table is only visible in the current connection. When the connection is closed, Mysql will automatically delete it. table and free up all space.

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

Temporary table is a table used temporarily.

Temporary tables are tables used by MySQL to store some intermediate result sets. Temporary tables are only visible in the current connection. When the connection is closed, Mysql will automatically delete the table and release all space.

Use other MySQL client programs to connect to the MySQL database server to create temporary tables. The temporary tables will only be destroyed when the client program is closed. Of course, they can also be deleted manually.

Note: Temporary tables were added in MySQL version 3.23. If your MySQL version is lower than version 3.23, you cannot use MySQL's temporary tables. However, nowadays it is generally rare to use such a low version of MySQL database service anymore

Two temporary tables in MySQL

External temporary table

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

Internal temporary table

The internal temporary table is a special lightweight temporary table used for performance optimization. This kind of temporary table will be 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 kind of internal table is invisible to users, but through EXPLAIN or SHOW STATUS you can check whether MYSQL uses internal temporary tables to help complete an operation. Internal temporary tables play a very important role in the optimization process of SQL statements. Many operations in MySQL rely on internal temporary tables for optimization. However, using internal temporary tables requires the cost of creating tables and accessing intermediate data, so users should try to avoid using temporary tables when writing SQL statements.

There are two types of internal temporary tables:

• One is a HEAP temporary table. All data in this temporary table will be stored in memory. For this kind of table, The operation does not require IO operations.

• The other is the OnDisk temporary table. As the name suggests, this temporary table stores data on the disk. OnDisk temporary tables are used to handle operations with relatively large intermediate results.

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

In 5.7, the OnDisk temporary table can choose to use the MyISAM engine or the InnoDB engine through the INTERNAL_TMP_DISK_STORAGE_ENGINE system variable.

Common usage of external temporary tables

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

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

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

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

2), In the same SQL, the same temporary table cannot be associated twice, 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 when it is closed Space will be cleared and temporary tables will be deleted;

4), show tables will not list temporary tables;

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

6), which affects the use of the replication function;

7), if you declare an alias for a table, when You must use this alias when pointing to this table. See "MySQL Multi-table Association Update and Delete"

Example:

mysql> CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) 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

   <update id="createTempTable">
        CREATE TEMPORARY TABLE IF NOT EXISTS temp 
        SELECT * FROM settlement_temp
         WHERE settle_date=#{settleDate} AND LENGTH(operator) IN(16,32) AND  pay_status IN (&#39;01&#39;,&#39;06&#39;) 
         ORDER BY settle_date,merchant_no
    </update>

  <!-- 4、删除临时表 -->
    <update id="dropTempTable">  
        DROP TEMPORARY TABLE IF EXISTS settlement_temp;
    </update>

Common usage of internal temporary tables

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

First we define a table t1,

CREATE TABLE t1( a int, b int); 
INSERT INTO t1 VALUES(1,2),(3,4);

All the following operations are based on table t1 for examples.

• Use SQL_BUFFER_RESULT hint

  in SQL statements

SQL_BUFFER_RESULT主要用来让MySQL尽早的释放表上的锁。因为如果数据量很大的话，需要较长时间将数据发送到客户端，通过将数据缓冲到临时表中可以有效的减少读锁对表的占用时间。SQL_BUFFER_RESULT见《mysql查询优化之三：查询优化器提示（hint）》

例如：

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",
        ...

• 如果SQL语句中包含了DERIVED_TABLE。

在5.7中，由于采用了新的优化方式，我们需要使用 set optimizer_switch=’derived_merge=off’来禁止derived table合并到外层的Query中。

例如：

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,
        ...

• 如果我们查询系统表的话，系统表的数据将被存储到内部临时表中。

我们当前不能使用EXPLAIN来查看是否读取系统表数据需要利用到内部临时表，但是可以通过SHOW STATUS来查看是否利用到了内部临时表。

例如：

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

• 如果DISTINCT语句没有被优化掉，即DISTINCT语句被优化转换为GROUP BY操作或者利用UNIQUE INDEX消除DISTINCT, 内部临时表将会被使用。

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,
        ...

• 如果查询带有ORDER BY语句，并且不能被优化掉。下面几种情况会利用到内部临时表缓存中间数据，然后对中间数据进行排序。

1）如果连接表使用BNL（Batched Nestloop）/BKA(Batched Key Access)
例如：

1))BNL默认是打开的

mysql> explain format=json select * from t1, t1 as t2 order by t1.a;
EXPLAIN
{
  "query_block": {
  "select_id": 1,
  "cost_info": {
    "query_cost": "22.00"
  },
  "ordering_operation": {
    "using_temporary_table": true,
  ...

2))关掉BNL后，ORDER BY将直接使用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;
EXPLAIN
{
   "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "25.00"
    },
    "ordering_operation": {
      "using_filesort": true,
    ...

2）ORDER BY的列不属于执行计划中第一个连接表的列。 

例如：

mysql> explain format=json select * from t as t1, t as t2 order by t2.a;
EXPLAIN
{
   "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "25.00"
    },
    "ordering_operation": {
      "using_temporary_table": true,
    ...

3）如果ORDER BY的表达式是个复杂表达式。

那么什么样的ORDER BY表达式，MySQL认为是复杂表达式呢？

1))如果排序表达式是SP或者UDF。

例如：

drop function if exists func1;
delimiter |
create function func1(x int)
returns int deterministic
begin
declare z1, z2 int;
set z1 = x;
set z2 = z1+2;
return z2;
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))ORDER BY的列包含聚集函数

为了简化执行计划，我们利用INDEX来优化GROUP BY语句。

例如：

create index idx1 on t1(a);
  explain format=json SELECt a FROM t1 group by a 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))ORDER BY的列中包含有SCALAR SUBQUERY,当然该SCALAR SUBQUERY没有被优化掉。

例如：

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) 如果查询既带有ORDER BY同时也有GROUP BY语句，但是两个语句使用的列不相同。

注意: 如果是5.7，我们需要将sql_mode设置为非only_full_group_by模式，否则会报错。

同样为了简化执行计划，我们利用INDEX来优化GROUP BY语句。

例如：

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;

• 如果查询带有GROUP BY语句，并且不能被优化掉。下面几种情况会利用到内部临时表缓存中间数据，然后对中间数据进行GROUP BY。

1）如果连接表使用BNL（Batched Nestloop）/BKA(Batched Key Access)。

例如：

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) 如果GROUP BY的列不属于执行计划中的第一个连接表。

例如：

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) 如果GROUP BY语句使用的列与ORDER BY语句使用的列不同。

例如：

	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) 如果GROUP BY带有ROLLUP并且是基于多表外连接。

例如：

	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) 如果GROUP BY语句使用的列来自于SCALAR SUBQUERY，并且没有被优化掉。

例如：

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"
  },
...

IN表达式转换为semi-join进行优化

1) 如果semi-join执行方式为Materialization

例如：

set optimizer_switch='firstmatch=off,duplicateweedout=off';
explain format=json select * from t1 where a in (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) 如果semi-join执行方式为Duplicate Weedout

例如：

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

• 如果查询语句带有UNION，MySQL将利用内部临时表帮助UNION操作消除重复。

例如：

explain format=json select * from t1 union select * from t1;
| {
"query_block": {
"union_result": {
  "using_temporary_table": true,
  "table_name": "<union1,2>",
...

• 如果查询语句使用多表更新。

这里Explain不能看到内部临时表被利用，所以需要查看status。

例如：

update t1, t1 as t2 set t1.a=3;
show status like 'CREATE%';

• 如果聚集函数中包含如下函数，内部临时表也会被利用。

1) count(distinct *)
例如：
explain format=json select count(distinct a) from t1;
2) group_concat
例如：
explain format=json select group_concat(b) from t1;

总之，上面列出了10种情况，MySQL将利用内部临时表进行中间结果缓存，如果数据量比较大的话，内部临时表将会把数据存储在磁盘上，这样显然会对性能有所影响。为了尽可能的减少性能损失，我们需要尽量避免上述情况的出现。

MySQL在以下几种情况会创建临时表：

1、UNION查询；
2、用到TEMPTABLE算法或者是UNION查询中的视图；
3、ORDER BY和GROUP BY的子句不一样时；
4、表连接中，ORDER BY的列不是驱动表中的；
5、DISTINCT查询并且加上ORDER BY时；
6、SQL中用到SQL_SMALL_RESULT选项时；
7、FROM中的子查询；
8、子查询或者semi-join时创建的表；

EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 Using Temporary 就表示会用到临时表。

当然了，如果临时表中需要存储的数据量超过了上限（ tmp-table-size 或 max-heap-table-size 中取其大者），这时候就需要生成基于磁盘的临时表了。

在以下几种情况下，会创建磁盘临时表：

1、数据表中包含BLOB/TEXT列；
2、在 GROUP BY 或者 DSTINCT 的列中有超过 512字符 的字符类型列（或者超过 512字节的 二进制类型列，在5.6.15之前只管是否超过512字节）；
3、在SELECT、UNION、UNION ALL查询中，存在最大长度超过512的列（对于字符串类型是512个字符，对于二进制类型则是512字节）；
4、执行SHOW COLUMNS/FIELDS、DESCRIBE等SQL命令，因为它们的执行结果用到了BLOB列类型。

从5.7.5开始，新增一个系统选项 internal_tmp_disk_storage_engine 可定义磁盘临时表的引擎类型为 InnoDB，而在这以前，只能使用 MyISAM。而在5.6.3以后新增的系统选项 default_tmp_storage_engine 是控制 CREATE TEMPORARY TABLE 创建的临时表的引擎类型，在以前默认是MEMORY，不要把这二者混淆了。

见下例：

mysql> set default_tmp_storage_engine = "InnoDB";
-rw-rw----   1 mysql mysql  8558 Jul  7 15:22 #sql4b0e_10_0.frm -- InnoDB引擎的临时表
-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引擎的临时表
-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引擎的临时表

【相关推荐：mysql视频教程】

The above is the detailed content of mysql what is a temporary table. For more information, please follow other related articles on the PHP Chinese website!