mysql 임시 테이블이란 무엇입니까?

mysql에서 임시 테이블은 임시로 사용되며 일부 중간 결과 세트를 저장하는 데 사용됩니다. 임시 테이블은 연결이 닫히면 자동으로 테이블을 삭제하고 모든 공간을 해제하십시오.

이 튜토리얼의 운영 환경: windows7 시스템, mysql8 버전, Dell G3 컴퓨터.

임시테이블은 임시로 사용되는 테이블입니다.

임시 테이블은 일부 중간 결과 세트를 저장하기 위해 MySQL에서 사용하는 테이블입니다. 임시 테이블은 연결이 닫히면 자동으로 테이블을 삭제하고 모든 공간을 해제합니다.

다른 MySQL 클라이언트 프로그램을 사용하여 MySQL 데이터베이스 서버에 연결하여 임시 테이블을 만듭니다. 임시 테이블은 클라이언트 프로그램을 닫을 때만 삭제됩니다. 물론 수동으로 삭제할 수도 있습니다.

참고: 임시 테이블은 MySQL 버전 3.23에 추가되었습니다. MySQL 버전이 3.23보다 낮은 경우 MySQL의 임시 테이블을 사용할 수 없습니다. 그러나 일반적으로 이러한 낮은 버전의 MySQL 데이터베이스 서비스를 더 이상 사용하는 경우는 없습니다. MySQL에서는 두 가지 종류의 임시 테이블을 외부 임시 테이블이라고 합니다. 이 임시 테이블은 현재 사용자에게만 표시되며 현재 세션이 끝나면 자동으로 닫힙니다. 이 임시 테이블은 비임시 테이블과 동일한 이름으로 이름을 지정할 수 있습니다. 비임시 테이블은 임시 테이블이 삭제될 때까지 현재 세션에 표시되지 않습니다.

내부 임시 테이블내부 임시 테이블은 성능 최적화를 위해 사용되는 특수 경량 임시 테이블입니다. 이러한 종류의 임시 테이블은 MySQL에 의해 자동으로 생성되며 특정 작업의 중간 결과를 저장하는 데 사용됩니다. 이러한 작업은 최적화 단계 또는 실행 단계에 포함될 수 있습니다. 이러한 종류의 내부 테이블은 사용자에게 보이지 않지만 EXPLAIN 또는 SHOW STATUS를 통해 MYSQL이 작업 완료를 돕기 위해 내부 임시 테이블을 사용하는지 여부를 확인할 수 있습니다. 내부 임시 테이블은 SQL 문의 최적화 프로세스에서 매우 중요한 역할을 합니다. MySQL의 많은 작업은 최적화를 위해 내부 임시 테이블에 의존합니다. 그러나 내부 임시 테이블을 사용하려면 테이블을 생성하고 중간 데이터에 접근하는 비용이 필요하므로 사용자는 SQL 문을 작성할 때 임시 테이블을 사용하지 않도록 노력해야 합니다.

내부 임시 테이블에는 두 가지 유형이 있습니다.

하나는 HEAP 임시 테이블입니다. 이 임시 테이블의 모든 데이터는 메모리에 저장되며 이러한 종류의 테이블에 대한 작업에는 IO 작업이 필요하지 않습니다.

다른 하나는 OnDisk 임시 테이블입니다. 이름에서 알 수 있듯이 이 임시 테이블은 디스크에 데이터를 저장합니다. OnDisk 임시 테이블은 중간 결과가 비교적 큰 작업을 처리하는 데 사용됩니다.

HEAP 임시 테이블에 저장된 데이터가 MAX_HEAP_TABLE_SIZE보다 큰 경우 HEAP 임시 테이블은 자동으로 OnDisk 임시 테이블로 변환됩니다.

5.7에서 OnDisk 임시 테이블은 INTERNAL_TMP_DISK_STORAGE_ENGINE 시스템 변수를 통해 MyISAM 엔진 또는 InnoDB 엔진을 사용하도록 선택할 수 있습니다.

• 외부 임시 테이블의 일반적인 사용법

• 외부 임시 테이블은 CREATE TEMPORARY TABLE, DROP TABLE을 통해 동작하지만, SHOW TABLES 명령어를 실행하면 데이터 테이블 목록이 출력될 때, 생성한 임시 테이블을 볼 수 없다. 생성되었습니다. 그리고 현재 세션을 종료하면 임시 테이블이 자동으로 삭제됩니다. 물론 수동으로 삭제(DROP TABLE)할 수도 있습니다.

1. 엔진 유형: 다음만 가능: 메모리(heap), myisam, merge, innodb, mysql 클러스터(cluster)는 지원되지 않습니다.

2. 외부 임시 테이블을 사용할 때는 다음 사항에 주의하세요. 1) 사용하는 데이터베이스 계정에는 임시 테이블을 생성할 수 있는 권한이 있어야 합니다.

2) 동일한 SQL에서는 동일한 작업을 수행할 수 없습니다. 임시 테이블을 두 번 연결합니다. 그렇지 않으면 다음 오류가 보고됩니다.

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

3) 연결을 설정할 때 임시 테이블이 표시되며 닫을 때 공간이 지워지고 임시 테이블이 삭제됩니다. 4) 테이블 표시는 임시 테이블을 나열하지 않습니다.

5) 이름 바꾸기를 사용하여 임시 테이블의 이름을 바꿀 수 없습니다. 그러나 대신 테이블을 변경할 수 있습니다. 테이블에 대한 별칭을 선언하는 경우 복제 기능 사용에 영향을 주는 alter table old_tp_table_name rename new_tp_table_name;

6)만 사용할 수 있습니다. 이 테이블에는 이 별칭을 사용해야 합니다. "MySQL 다중 테이블 연결 업데이트 및 삭제"를 참조하세요

예:

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

내부 임시 테이블의 일반적인 사용법

사용자가 SQL 문을 작성할 수 있는 경우 쿼리 최적화를 위해 내부 임시 테이블을 최소화하면 쿼리 실행 효율성이 효과적으로 향상됩니다.

먼저 테이블 t1을 정의합니다.

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

다음의 모든 작업은 예를 들어 테이블 t1을 기반으로 합니다.

SQL 문에서 SQL_BUFFER_RESULT 힌트 사용

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视频教程】

위 내용은 mysql 임시 테이블이란 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!