最近2018中文字幕在日韩欧美国产成人片_国产日韩精品一区二区在线_在线观看成年美女黄网色视频_国产精品一区三区五区_国产精彩刺激乱对白_看黄色黄大色黄片免费_人人超碰自拍cao_国产高清av在线_亚洲精品电影av_日韩美女尤物视频网站

什么是Internal Temporary？

網(wǎng)站建設(shè)、網(wǎng)站設(shè)計(jì)的關(guān)注點(diǎn)不是能為您做些什么網(wǎng)站，而是怎么做網(wǎng)站，有沒(méi)有做好網(wǎng)站，給創(chuàng)新互聯(lián)公司一個(gè)展示的機(jī)會(huì)來(lái)證明自己，這并不會(huì)花費(fèi)您太多時(shí)間，或許會(huì)給您帶來(lái)新的靈感和驚喜。面向用戶友好，注重用戶體驗(yàn)，一切以用戶為中心。

臨時(shí)表分為兩種，一種是當(dāng)執(zhí)行一些SQL的時(shí)候MySQL會(huì)自動(dòng)創(chuàng)建的一些中間結(jié)果集，稱為internal temporary，這些中間結(jié)果集可能放在memory中，也有可能放在disk上；

還有一種是手動(dòng)執(zhí)行create temporary table 語(yǔ)法生成的外部臨時(shí)表，這種臨時(shí)表存儲(chǔ)在memory上，數(shù)據(jù)庫(kù)shutdown，就會(huì)自動(dòng)刪除；

本篇講的臨時(shí)表都是指內(nèi)部臨時(shí)表，測(cè)試使用的MySQL版本是8.0.13；

怎么判斷有沒(méi)有使用內(nèi)部臨時(shí)表？

執(zhí)行計(jì)劃explain或explain format=json 中出現(xiàn)using temporary;

show status中Created_tmp_disk_tables或Created_tmp_tables數(shù)值增加；

什么情況下產(chǎn)生Internal temporary table?

(1)除了后面提到的特殊情況，所有使用union的SQL，但是使用union all沒(méi)有使用臨時(shí)表

(2)用到TEMPTABLE算法或者是UNION查詢中的視圖

mysql> desc select * from t_order union select * from t_group;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | t_order    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL            |
|  2 | UNION        | t_group    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL            |
| NULL | UNION RESULT |  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.01 sec)
但是使用union all沒(méi)有使用臨時(shí)表
mysql> desc select * from t_order union all select * from t_group;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | t_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
|  2 | UNION       | t_group | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

(3)使用衍生表

(4)子查詢和semi-join

mysql> desc select /*+ set_var(optimizer_switch='derived_merge=off') */ * from (select * from t_order)t;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     |  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
|  2 | DERIVED     | t_order    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)
mysql> desc format=json  select /*+ set_var(optimizer_switch='derived_merge=off') */ * from (select * from t_order)t;
......
      "materialized_from_subquery": {
        "using_temporary_table": true,

(5)order by和group by的子句不一樣時(shí)，或者表連接中order by或group by的列是被驅(qū)動(dòng)表中的列；

order by和group by 同時(shí)使用的時(shí)候：

mysql> desc select dept_no from t_order group by dept_no order by dept_no;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | t_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using temporary; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)
或者：
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> desc select dept_no from t_order group by dept_no order by emp_no;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | t_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using temporary; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

order by 和group by 分別和join使用的時(shí)候：

mysql> desc select * from t_group t1 join t_order t2 on t1.emp_no=t2.emp_no order by t2.emp_no;
+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref                 | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+---------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL  | NULL    | NULL                |   10 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | t2    | NULL       | ref  | ix_t1         | ix_t1 | 5       | employees.t1.emp_no |    1 |   100.00 | NULL                            |
+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+---------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> desc select * from t_group t1 join t_order t2 on t1.emp_no=t2.emp_no order by t1.emp_no;
+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref                 | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL  | NULL    | NULL                |   10 |   100.00 | Using filesort |
|  1 | SIMPLE      | t2    | NULL       | ref  | ix_t1         | ix_t1 | 5       | employees.t1.emp_no |    1 |   100.00 | NULL           |
+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)
mysql> desc select t1.dept_no from t_group t1 join t_order t2 on t1.emp_no=t2.emp_no group by t1.dept_no;
+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref                 | rows | filtered | Extra           |
+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+-----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL  | NULL    | NULL                |   10 |   100.00 | Using temporary |
|  1 | SIMPLE      | t2    | NULL       | ref  | ix_t1         | ix_t1 | 5       | employees.t1.emp_no |    1 |   100.00 | Using index     |
+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+-----------------+
2 rows in set, 1 warning (0.00 sec)
mysql> desc  select t2.dept_no from t_group t1 join t_order t2 on t1.emp_no=t2.emp_no group by t2.dept_no;
+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref                 | rows | filtered | Extra           |
+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+-----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL  | NULL    | NULL                |   10 |   100.00 | Using temporary |
|  1 | SIMPLE      | t2    | NULL       | ref  | ix_t1         | ix_t1 | 5       | employees.t1.emp_no |    1 |   100.00 | NULL            |
+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+-----------------+
2 rows in set, 1 warning (0.00 sec)

(6)使用distinct或者distinct集合ORDER BY時(shí)

mysql> desc select distinct * from t_order;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | t_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using temporary |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

(7)SQL中用到SQL_SMALL_RESULT選項(xiàng)時(shí)；

(8)INSERT ... SELECT針對(duì)同一個(gè)表操作的時(shí)候

mysql> desc insert into t_order select * from t_order;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | INSERT      | t_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | NULL            |
|  1 | SIMPLE      | t_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using temporary |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
2 rows in set, 1 warning (0.00 sec)

(9)使用GROUP_CONCAT() or COUNT(DISTINCT)

使用group_concat()時(shí)產(chǎn)生臨時(shí)表：

mysql> flush status;
Query OK, 0 rows affected (0.02 sec)
mysql> select dept_no,group_concat(emp_no) from t_order group by dept_no;
+---------+-------------------------+
| dept_no | group_concat(emp_no)    |
+---------+-------------------------+
| d002    | 31112                   |
| d004    | 10004                   |
| d005    | 24007,30970,40983,50449 |
| d006    | 22744                   |
| d007    | 49667                   |
| d008    | 48317                   |
+---------+-------------------------+
6 rows in set (0.00 sec)
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

使用count(distinct)時(shí)產(chǎn)生臨時(shí)表：

mysql> flush status;
Query OK, 0 rows affected (0.02 sec)
mysql> desc select count(distinct dept_no) from t_order;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.01 sec)

什么情況下產(chǎn)生的內(nèi)部臨時(shí)表不是在內(nèi)存中，而是在磁盤(pán)上？

(1)表存在blob或text字段；

(2)在SELECT   UNION、UNION ALL查詢中，存在最大長(zhǎng)度超過(guò)512的列（對(duì)于字符串類型是512個(gè)字符，對(duì)于二進(jìn)制類型則是512字節(jié)）；

(3)使用show columns和describe命令在存在blob列的表上；

內(nèi)部臨時(shí)表使用什么存儲(chǔ)引擎？

MySQL8.0.2開(kāi)始支持internal_tmp_mem_storage_engine參數(shù)；

(1)當(dāng)internal_tmp_mem_storage_engine=TempTable時(shí)，

TempTable存儲(chǔ)引擎為varchar和varbinary數(shù)據(jù)類型提供高效的存儲(chǔ)，temptable_max_ram=1G定義臨時(shí)表最大可以使用的內(nèi)存空間，但是如果參數(shù)temptable_use_mma=on，則表示可以繼續(xù)使用內(nèi)存存儲(chǔ)臨時(shí)表，如果off，則臨時(shí)表超過(guò)閾值，只能使用磁盤(pán)存儲(chǔ)；

(2)當(dāng)internal_tmp_mem_storage_engine=memory時(shí)：

內(nèi)部臨時(shí)表大小超過(guò)參數(shù)tmp_table_size和max_heap_table_size時(shí)候，會(huì)自動(dòng)從內(nèi)存中轉(zhuǎn)移到磁盤(pán)上，內(nèi)部臨時(shí)表在磁盤(pán)上默認(rèn)使用的是innodb存儲(chǔ)引擎，由參數(shù)internal_tmp_disk_storage_engine決定.

參考鏈接

Internal Temporary Table Use in MySQL

網(wǎng)頁(yè)題目：MySQLInternalTemporary
當(dāng)前地址：http://fisionsoft.com.cn/article/jddppe.html