mysql apakah jadual sementara

Dalam mysql, jadual sementara merujuk kepada jadual yang digunakan buat sementara waktu dan digunakan untuk menyimpan beberapa set hasil perantaraan hanya boleh dilihat dalam sambungan semasa dan akan dipadam secara automatik oleh Mysql apabila sambungan ditutup dan mengosongkan semua ruang.

Persekitaran pengendalian tutorial ini: sistem windows7, versi mysql8, komputer Dell G3.

Jadual sementara ialah jadual yang digunakan buat sementara waktu.

Jadual sementara ialah jadual yang digunakan oleh MySQL untuk menyimpan beberapa set hasil perantaraan hanya kelihatan dalam sambungan semasa Apabila sambungan ditutup, Mysql akan memadamkan jadual secara automatik dan melepaskan semua ruang.

Gunakan program klien MySQL yang lain untuk menyambung ke pelayan pangkalan data MySQL untuk membuat jadual sementara Jadual sementara hanya akan dimusnahkan apabila program klien ditutup, ia juga boleh dipadamkan secara manual.

Nota: Jadual sementara telah ditambahkan dalam MySQL versi 3.23 Jika versi MySQL anda lebih rendah daripada versi 3.23, anda tidak boleh menggunakan jadual sementara MySQL. Walau bagaimanapun, secara amnya jarang sekali untuk menggunakan versi rendah perkhidmatan pangkalan data MySQL seperti itu lagi

Dua jadual sementara dalam MySQL

Jadual sementara luaran

Jadual sementara yang dicipta oleh CREATE TEMPORARY TABLE ini dipanggil jadual sementara luaran. Jadual sementara ini hanya boleh dilihat oleh pengguna semasa dan akan ditutup secara automatik apabila sesi semasa tamat. Jadual sementara ini boleh dinamakan dengan nama yang sama seperti jadual bukan sementara (jadual bukan sementara tidak akan kelihatan kepada sesi semasa sehingga jadual sementara dipadamkan).

Jadual sementara dalaman

Jadual sementara dalaman ialah jadual sementara ringan khas yang digunakan untuk pengoptimuman prestasi. Jadual sementara jenis ini akan dibuat secara automatik oleh MySQL dan digunakan untuk menyimpan hasil perantaraan operasi tertentu. Operasi ini mungkin termasuk dalam fasa pengoptimuman atau fasa pelaksanaan. Jadual dalaman jenis ini tidak kelihatan kepada pengguna, tetapi melalui EXPLAIN atau SHOW STATUS anda boleh menyemak sama ada MYSQL menggunakan jadual sementara dalaman untuk membantu menyelesaikan operasi. Jadual sementara dalaman memainkan peranan yang sangat penting dalam proses pengoptimuman pernyataan SQL Banyak operasi dalam MySQL bergantung pada jadual sementara dalaman untuk pengoptimuman. Walau bagaimanapun, menggunakan jadual sementara dalaman memerlukan kos untuk mencipta jadual dan mengakses data perantaraan, jadi pengguna harus cuba mengelak daripada menggunakan jadual sementara semasa menulis pernyataan SQL.

Terdapat dua jenis jadual sementara dalaman:

• Satu ialah jadual sementara HEAP Semua data dalam jadual sementara ini akan disimpan dalam ingatan jadual Operasi tidak memerlukan operasi IO.

• Yang lain ialah jadual sementara OnDisk Seperti namanya, jadual sementara ini menyimpan data pada cakera. Jadual sementara OnDisk digunakan untuk mengendalikan operasi dengan hasil perantaraan yang agak besar.

Jika data yang disimpan dalam jadual sementara HEAP lebih besar daripada MAX_HEAP_TABLE_SIZE, jadual sementara HEAP akan ditukar secara automatik menjadi jadual sementara OnDisk.

Dalam 5.7, jadual sementara OnDisk boleh memilih untuk menggunakan enjin MyISAM atau enjin InnoDB melalui pembolehubah sistem INTERNAL_TMP_DISK_STORAGE_ENGINE.

Penggunaan biasa jadual sementara luaran

Jadual sementara luaran dikendalikan melalui CREATE TEMPORARY TABLE dan DROP TABLE, tetapi arahan SHOW TABLES memaparkan Apabila memasuki senarai jadual data, anda tidak akan dapat melihat jadual sementara yang anda buat. Dan selepas keluar dari sesi semasa, jadual sementara akan dimusnahkan secara automatik. Sudah tentu, ia juga boleh dimusnahkan secara manual (DROP TABLE).

1. Jenis enjin: hanya boleh: memori (timbunan), myisam, merge, innodb, mysql cluster (cluster) tidak disokong.

2. Beri perhatian kepada beberapa perkara apabila menggunakan jadual sementara luaran:

1) Akaun pangkalan data yang anda gunakan mesti mempunyai kebenaran untuk mencipta jadual sementara;

2) Dalam SQL yang sama, jadual sementara yang sama tidak boleh dikaitkan dua kali, jika tidak, ralat berikut akan dilaporkan;

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

3) Jadual sementara boleh dilihat apabila menubuhkan a sambungan. Apabila menutup, ruang akan dikosongkan dan jadual sementara akan dipadamkan; menamakan semula jadual sementara. Walau bagaimanapun, anda boleh mengubah jadual sebaliknya: anda hanya boleh menggunakan alter table old_tp_table_name rename new_tp_table_name;

6), menjejaskan penggunaan fungsi replikasi;

7), jika anda mengisytiharkan alias untuk jadual, apabila Anda mesti menggunakan alias ini apabila menunjuk ke jadual ini. Lihat "Kemas Kini dan Padam Persatuan Multi-jadual MySQL"

Contoh:

3 Operasi jadual sementara dalam mybatis

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

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

Penggunaan biasa jadual sementara dalaman

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

Jika pengguna boleh menggunakan jadual sementara dalaman sesedikit mungkin semasa menulis pernyataan SQL untuk pengoptimuman pertanyaan, ia akan bertambah baik dengan berkesan Kecekapan pelaksanaan pertanyaan. Mula-mula kita mentakrifkan jadual t1

Semua operasi berikut adalah berdasarkan jadual t1 sebagai contoh.

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

Gunakan petunjuk SQL_BUFFER_RESULT

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

Atas ialah kandungan terperinci mysql apakah jadual sementara. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!