溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
什么是Internal Temporary？
臨時表分為兩種，一種是當執行一些SQL的時候MySQL會自動創建的一些中間結果集，稱為internal temporary，這些中間結果集可能放在memory中，也有可能放在disk上；
還有一種是手動執行create temporary table 語法生成的外部臨時表，這種臨時表存儲在memory上，數據庫shutdown，就會自動刪除；
本篇講的臨時表都是指內部臨時表，測試使用的MySQL版本是8.0.13；
怎么判斷有沒有使用內部臨時表？
執行計劃explain或explain format=json 中出現using temporary;
show status中Created_tmp_disk_tables或Created_tmp_tables數值增加；
什么情況下產生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的列是被驅動表中的列；
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()時產生臨時表：
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)時產生臨時表：
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)
什么情況下產生的內部臨時表不是在內存中，而是在磁盤上？
(1)表存在blob或text字段；
(2)在SELECT UNION、UNION ALL查詢中，存在最大長度超過512的列（對于字符串類型是512個字符，對于二進制類型則是512字節）；
(3)使用show columns和describe命令在存在blob列的表上；
內部臨時表使用什么存儲引擎？
MySQL8.0.2開始支持internal_tmp_mem_storage_engine參數；
(1)當internal_tmp_mem_storage_engine=TempTable時，
TempTable存儲引擎為varchar和varbinary數據類型提供高效的存儲，temptable_max_ram=1G定義臨時表最大可以使用的內存空間，但是如果參數temptable_use_mma=on，則表示可以繼續使用內存存儲臨時表，如果off，則臨時表超過閾值，只能使用磁盤存儲；
(2)當internal_tmp_mem_storage_engine=memory時：
內部臨時表大小超過參數tmp_table_size和max_heap_table_size時候，會自動從內存中轉移到磁盤上，內部臨時表在磁盤上默認使用的是innodb存儲引擎，由參數internal_tmp_disk_storage_engine決定.
參考鏈接
Internal Temporary Table Use in MySQL
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
