溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
什么是Internal Temporary？
臨時表分為兩種，一種是當(dāng)執(zhí)行一些SQL的時候MySQL會自動創(chuàng)建的一些中間結(jié)果集，稱為internal temporary，這些中間結(jié)果集可能放在memory中，也有可能放在disk上；
還有一種是手動執(zhí)行create temporary table 語法生成的外部臨時表，這種臨時表存儲在memory上，數(shù)據(jù)庫shutdown，就會自動刪除；
本篇講的臨時表都是指內(nèi)部臨時表，測試使用的MySQL版本是8.0.13；
怎么判斷有沒有使用內(nèi)部臨時表？
執(zhí)行計劃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沒有使用臨時表
(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 | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 3 rows in set, 1 warning (0.01 sec) 但是使用union all沒有使用臨時表 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 | <derived2> | 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的子句不一樣時，或者表連接中order by或group by的列是被驅(qū)動表中的列；
order by和group by 同時使用的時候：
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使用的時候：
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時
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選項時；
(8)INSERT ... SELECT針對同一個表操作的時候
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()時產(chǎn)生臨時表：
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)時產(chǎn)生臨時表：
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)部臨時表不是在內(nèi)存中，而是在磁盤上？
(1)表存在blob或text字段；
(2)在SELECT UNION、UNION ALL查詢中，存在最大長度超過512的列（對于字符串類型是512個字符，對于二進(jìn)制類型則是512字節(jié)）；
(3)使用show columns和describe命令在存在blob列的表上；
內(nèi)部臨時表使用什么存儲引擎？
MySQL8.0.2開始支持internal_tmp_mem_storage_engine參數(shù)；
(1)當(dāng)internal_tmp_mem_storage_engine=TempTable時，
TempTable存儲引擎為varchar和varbinary數(shù)據(jù)類型提供高效的存儲，temptable_max_ram=1G定義臨時表最大可以使用的內(nèi)存空間，但是如果參數(shù)temptable_use_mma=on，則表示可以繼續(xù)使用內(nèi)存存儲臨時表，如果off，則臨時表超過閾值，只能使用磁盤存儲；
(2)當(dāng)internal_tmp_mem_storage_engine=memory時：
內(nèi)部臨時表大小超過參數(shù)tmp_table_size和max_heap_table_size時候，會自動從內(nèi)存中轉(zhuǎn)移到磁盤上，內(nèi)部臨時表在磁盤上默認(rèn)使用的是innodb存儲引擎，由參數(shù)internal_tmp_disk_storage_engine決定.
參考鏈接
Internal Temporary Table Use in MySQL
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼