mysql qu'est-ce qu'une table temporaire

Dans MySQL, une table temporaire fait référence à une table qui est temporairement utilisée et utilisée pour stocker certains ensembles de résultats intermédiaires ; la table temporaire n'est visible que dans la connexion actuelle. Lorsque la connexion est fermée, Mysql supprimera automatiquement la table et. libérer tout l'espace.

L'environnement d'exploitation de ce tutoriel : système windows7, version mysql8, ordinateur Dell G3.

Une table temporaire est une table utilisée temporairement.

Les tables temporaires sont des tables utilisées par MySQL pour stocker certains ensembles de résultats intermédiaires. Les tables temporaires ne sont visibles que dans la connexion actuelle. Lorsque la connexion est fermée, Mysql supprimera automatiquement la table et libérera tout l'espace.

Utilisez d'autres programmes clients MySQL pour vous connecter au serveur de base de données MySQL afin de créer des tables temporaires. Les tables temporaires ne seront détruites qu'à la fermeture du programme client. Bien entendu, elles peuvent également être supprimées manuellement.

Remarque : des tables temporaires ont été ajoutées dans la version 3.23 de MySQL. Si votre version de MySQL est inférieure à 3.23, vous ne pouvez pas utiliser les tables temporaires de MySQL. Cependant, il est généralement rare d'utiliser une version aussi basse du service de base de données MySQL. Deux tables temporaires dans MySQL sont appelées tables temporaires externes. Cette table temporaire n'est visible que par l'utilisateur actuel et sera automatiquement fermée à la fin de la session en cours. Cette table temporaire peut porter le même nom que la table non temporaire (la table non temporaire ne sera pas visible par la session en cours jusqu'à ce que la table temporaire soit supprimée).

Table temporaire interneLa table temporaire interne est une table temporaire légère spéciale utilisée pour l'optimisation des performances. Ce genre de table temporaire sera automatiquement créée par MySQL et utilisée pour stocker les résultats intermédiaires de certaines opérations. Ces opérations peuvent être incluses dans la phase d'optimisation ou dans la phase d'exécution. Ce type de table interne est invisible pour les utilisateurs, mais via EXPLAIN ou SHOW STATUS, vous pouvez vérifier si MYSQL utilise des tables temporaires internes pour vous aider à terminer une opération. Les tables temporaires internes jouent un rôle très important dans le processus d'optimisation des instructions SQL. De nombreuses opérations dans MySQL reposent sur des tables temporaires internes pour l'optimisation. Cependant, l'utilisation de tables temporaires internes nécessite le coût de création de tables et d'accès aux données intermédiaires. Les utilisateurs doivent donc éviter d'utiliser des tables temporaires lors de l'écriture d'instructions SQL.

Il existe deux types de tables temporaires internes :

L'une est une table temporaire HEAP. Toutes les données de cette table temporaire seront stockées en mémoire et les opérations sur ce type de table ne nécessitent pas d'opérations d'E/S.

L'autre est la table temporaire OnDisk Comme son nom l'indique, cette table temporaire stocke les données sur le disque. Les tables temporaires OnDisk sont utilisées pour gérer les opérations avec des résultats intermédiaires relativement importants.

Si les données stockées dans la table temporaire HEAP sont supérieures à MAX_HEAP_TABLE_SIZE, la table temporaire HEAP sera automatiquement convertie en table temporaire OnDisk.

La table temporaire OnDisk peut choisir d'utiliser le moteur MyISAM ou le moteur InnoDB via la variable système INTERNAL_TMP_DISK_STORAGE_ENGINE dans 5.7.

• Utilisation courante des tables temporaires externes

• Les tables temporaires externes sont exploitées via CREATE TEMPORARY TABLE et DROP TABLE, mais lorsque la commande SHOW TABLES affiche la liste des tables de données, vous ne pourrez pas voir la table temporaire que vous créé. Et après avoir quitté la session en cours, la table temporaire sera automatiquement détruite. Bien entendu, il peut également être détruit manuellement (DROP TABLE).

1. Type de moteur : ne peut être que : mémoire (tas), myisam, merge, innodb, le cluster mysql (cluster) n'est pas pris en charge.

2. Faites attention à quelques points lorsque vous utilisez des tables temporaires externes : 1) Le compte de base de données que vous utilisez doit avoir l'autorisation de créer des tables temporaires

2) Dans le même sql, vous ne pouvez pas associer les tables temporaires ; deux fois la même chose Table temporaire, sinon l'erreur suivante sera signalée :

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

3), la table temporaire est visible lors de l'établissement d'une connexion, et l'espace sera vidé lors de la fermeture, et la table temporaire sera supprimée ; 4), show tables ne listera pas la table temporaire ;

5), rename ne peut pas être utilisé pour renommer les tables temporaires. Cependant, vous pouvez modifier la table à la place : vous ne pouvez utiliser alter table old_tp_table_name rename new_tp_table_name;

6), ce qui affecte l'utilisation de la fonction de réplication

7), si vous déclarez un alias pour une table, lorsque vous pointez vers ce tableau, cet alias doit être utilisé. Voir "Mise à jour et suppression d'associations multi-tables MySQL"

Exemple :

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. Opérations sur les tables temporaires dans 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>

Utilisation courante des tables temporaires internes

Si l'utilisateur peut écrire des instructions SQL en utilisant le moins possible de tables temporaires internes pour l'optimisation des requêtes améliorera efficacement l'efficacité de l'exécution des requêtes.

Nous définissons d'abord une table t1,

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

Toutes les opérations suivantes sont basées sur la table t1 par exemple.

Utilisez l'indice SQL_BUFFER_RESULT dans les instructions SQL

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

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!