如何查看mysql生成的臨時表

MySQL 需要創(chuàng)建隱式臨時表來解決某些類型的查詢。往往查詢的排序階段需要依賴臨時表。例如，當您使用 GROUP BY，ORDER BY 或DISTINCT 時。這樣的查詢分兩個階段執(zhí)行：首先是收集數(shù)據(jù)并將它們放入臨時表中，然后是在臨時表上執(zhí)行排序。

創(chuàng)新互聯(lián)專注于昂昂溪網(wǎng)站建設服務及定制，我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗。 熱誠為您提供昂昂溪營銷型網(wǎng)站建設，昂昂溪網(wǎng)站制作、昂昂溪網(wǎng)頁設計、昂昂溪網(wǎng)站官網(wǎng)定制、小程序開發(fā)服務，打造昂昂溪網(wǎng)絡公司原創(chuàng)品牌,更為您提供昂昂溪網(wǎng)站排名全網(wǎng)營銷落地服務。

對于某些 UNION 語句，不能合并的 VIEW，子查詢時用到派生表，多表 UPDATE 以及其他一些情況，還需要使用臨時表。如果臨時表很小，可以到內(nèi)存中創(chuàng)建，否則它將在磁盤上創(chuàng)建。MySQL 在內(nèi)存中創(chuàng)建了一個表，如果它變得太大，就會被轉(zhuǎn)換為磁盤上存儲。內(nèi)存臨時表的最大值由 tmp_table_size 或 max_heap_table_size 值定義，以較小者為準。MySQL 5.7 中的默認大小為 16MB。如果運行查詢的數(shù)據(jù)量較大，或者尚未查詢優(yōu)化，則可以增加該值。設置閾值時，請考慮可用的 RAM 大小以及峰值期間的并發(fā)連接數(shù)。你無法無限期地增加變量，因為在某些時候你需要讓 MySQL 使用磁盤上的臨時表。

注意：如果涉及的表具有 TEXT 或 BLOB 列，則即使大小小于配置的閾值，也會在磁盤上創(chuàng)建臨時表。

技術(shù)分享 | 淺談 MySQL 的臨時表和臨時文件

本文內(nèi)容來源于對客戶的三個問題的思考：

以下測試都是在 MySQL 8.0.21 版本中完成，不同版本可能存在差異，可自行測試；

臨時表和臨時文件都是用于臨時存放數(shù)據(jù)集的地方；

一般情況下，需要臨時存放在臨時表或臨時文件中的數(shù)據(jù)集應該符合以下特點：

從臨時表|臨時文件產(chǎn)生的主觀性來看，分為2類：

用戶創(chuàng)建臨時表：

??-- 用戶創(chuàng)建臨時表(只有創(chuàng)建臨時表的會話才能查看其創(chuàng)建的臨時表的內(nèi)容)

??注意：

??可以創(chuàng)建和普通表同名臨時表，其他會話可以看到普通表(因為看不到其他會話創(chuàng)建的臨時表)；

??創(chuàng)建臨時表的會話會優(yōu)先看到臨時表；

??-- 同名表的創(chuàng)建的語句如下

??當存在同名的臨時表時，會話都是優(yōu)先處理臨時表(而不是普通表)，包括：select、update、delete、drop、alter 等操作；

查看用戶創(chuàng)建的臨時表：

??任何 session 都可以執(zhí)行下面的語句；

??查看用戶創(chuàng)建的當前 active 的臨時表(不提供 optimizer 使用的內(nèi)部 InnoDB 臨時表信息)

??注意

??用戶創(chuàng)建的臨時表，表名為t1，

??但是通過 INNODB_TEMP_TABLE_INFO 查看到的臨時表的 NAME 是#sql開頭的名字，例如：#sql45aa_7c69_2 ；

??另外 information_schema.tables 表中是不會記錄臨時表的信息的。

用戶創(chuàng)建的臨時表的回收：

用戶創(chuàng)建的臨時表的其他信息參數(shù)：

??會話臨時表空間存儲 用戶創(chuàng)建的臨時表和優(yōu)化器 optimizer 創(chuàng)建的內(nèi)部臨時表(當磁盤內(nèi)部臨時表的存儲引擎為 InnoDB 時)；

??innodb_temp_tablespaces_dir 變量定義了創(chuàng)建 會話臨時表空間的位置，默認是數(shù)據(jù)目錄下的#innodb_temp 目錄；

??文件類似temp_[1-20].ibt ；

??查看會話臨時表空間的元數(shù)據(jù)：

??用戶創(chuàng)建的臨時表刪除后，其占用的空間會被釋放(temp_[1-20].ibt文件會變小)。

??在 MySQL 8.0.16 之前，internal_tmp_disk_storage_engine 變量定義了用戶創(chuàng)建的臨時表和 optimizer 創(chuàng)建的內(nèi)部臨時表的引擎，可選 INNODB 和 MYISAM ；

??從 MySQL 8.0.16 開始，internal_tmp_disk_storage_engine參數(shù)被移除，默認使用InnoDB存儲引擎；

??innodb_temp_data_file_path 定義了用戶創(chuàng)建的臨時表使用的回滾段的存儲文件的相對路徑、名字、大小和屬性，該文件是全局臨時表空間(ibtmp1)；

??可以使用語句查詢?nèi)峙R時表空間的數(shù)據(jù)文件大?。?/p>

SQL 什么時候產(chǎn)生臨時表|臨時文件呢？

??需要用到臨時表或臨時文件的時候，optimizer 自然會創(chuàng)建使用(感覺是廢話，但是又覺得有道理=.=!)；

??(想象能力強的，可以牢記上面這句話；想象能力弱的，只能死記下面的 SQL 了。我也弱，此處有個疲憊的微笑????)

下面列舉一些 server 在處理 SQL 時，可能會創(chuàng)建內(nèi)部臨時表的 SQL ：

??SQL 包含 union | union distinct 關(guān)鍵字

??SQL 中存在派生表

??SQL 中包含 with 關(guān)鍵字

??SQL 中的order by 和 group by 的字段不同

??SQL 為多表 update

??SQL 中包含 distinct 和 order by 兩個關(guān)鍵字

我們可以通過下面兩種方式判斷 SQL 語句是否使用了臨時表空間：

??# 如果 explain 的 Extra 列包含 Using temporary ，那么說明會使用臨時空間，如果包含 Using filesort ，那么說明會使用文件排序(臨時文件)；

??如果執(zhí)行 SQL 后，表的 ID 列變?yōu)榱藄how processlist 中的 id 列的值，那么說明 SQL 語句使用了臨時表空間

SQL創(chuàng)建的內(nèi)部臨時表的存儲信息：

??SQL 創(chuàng)建內(nèi)部臨時表時，優(yōu)先選擇在內(nèi)存中，默認使用 TempTable 存儲引擎(由參數(shù) internal_tmp_mem_storage_engine 確定)，

??當 temptable 使用的內(nèi)存量超過 temptable_max_ram 定義的大小時，

??由 temptable_use_mmap 確定是使用內(nèi)存映射文件的方式還是 InnoDB 磁盤內(nèi)部臨時表的方式存儲數(shù)據(jù)

??(temptable_use_mmap 參數(shù)在 MySQL 8.0.16 引入，MySQL 8.0.26 版本不推薦，后續(xù)應該會移除)；

??temptable_use_mmap 的功能將由MySQL 8.0.23 版本引入的 temptable_max_mmap 代替，

??當 temptable_max_mmap=0 時，說明不使用內(nèi)存映射文件，等價于 temptable_use_mmap=OFF ；

??當 temptable_max_mmap=N 時，N為正整數(shù)，包含了 temptable_use_mmap=ON 以及聲明了允許為內(nèi)存映射文件分配的最大內(nèi)存量。

??該參數(shù)的定義解決了這些文件使用過多空間的風險。

??內(nèi)存映射文件產(chǎn)生的臨時文件會存放于 tmpdir 定義的目錄中，在 TempTable 存儲引擎關(guān)閉或 mysqld 進程關(guān)閉時，回收空間；

??當 SQL 創(chuàng)建的內(nèi)部臨時表，選擇 MEMORY 存儲引擎時，如果內(nèi)存中的臨時表變的太大，MySQL 將自動將其轉(zhuǎn)為磁盤臨時表；

??其能使用的內(nèi)存上限為 min(tmp_table_size,max_heap_table_size)；

監(jiān)控 TempTable 從內(nèi)存和磁盤上分配的空間：

??具體的字段含義見：Section 27.12.20.10, “Memory Summary Tables”.

監(jiān)控內(nèi)部臨時表的創(chuàng)建：

??當在內(nèi)存或磁盤上創(chuàng)建內(nèi)部臨時表，服務器會增加 Created_tmp_tables 的值；

??當在磁盤上創(chuàng)建內(nèi)部臨時表時，服務器會增加 Created_tmp_disk_tables 的值，

??如果在磁盤上創(chuàng)建了太多的內(nèi)部臨時表，請考慮增加 tmp_table_size 和 max_heap_table_size 的值；

??created_tmp_disk_tables 不計算在內(nèi)存映射文件中創(chuàng)建的磁盤臨時表；

例外項：

??臨時表/臨時文件一般較小，但是也存在需要大量空間的臨時表/臨時文件的需求：

因為這些例外項一般需要較大的空間，所以需要考慮是否要將其存放在獨立的掛載點上。

其他：

??列出由失敗的 alter table 創(chuàng)建的隱藏臨時表，這些臨時表以#sql開頭，可以使用 drop table 刪除；

??通過 lsof +L1 可以查看標識為 delete ，但還未釋放空間的文件。

???如果想釋放這些 delete 狀態(tài)的文件，可以嘗試下面的方法(不推薦，后果自負)：

普通的磁盤臨時表|臨時文件(一般需要較小的空間)：

??臨時表|臨時文件的一般所需的空間較小，會優(yōu)先存放于內(nèi)存中，若超過一定的大小，則會轉(zhuǎn)換為磁盤臨時表|臨時文件；

??磁盤臨時表默認為 InnoDB 引擎，其存放在臨時表空間中，由 innodb_temp_tablespaces_dir 定義表空間的存放目錄，表空間文件類似：temp_[1-20].ibt ；MySQL 未定義 InnoDB 臨時表空間的最大使用上限；

??當臨時表|臨時文件使用完畢后，會自動回收臨時表空間文件的大?。?/p>

??innodb_temp_data_file_path 定義了用戶創(chuàng)建的臨時表使用的回滾段的存儲文件的相對路徑、名字、大小和屬性，該文件是全局臨時表空間(ibtmp1)，該文件可以設置文件最大使用大?。?/p>

例外項(一般需要較大的空間)：

??load data local 語句，客戶端讀取文件并將其內(nèi)容發(fā)送到服務器，服務器將其存儲在 tmpdir 參數(shù)指定的路徑中；

??在 replica 中，回放 load data 語句時，需要將從 relay log 中解析出來的數(shù)據(jù)存儲在 slave_load_tmpdir(replica_load_tmpdir) 指定的目錄中，該參數(shù)默認和 tmpdir 參數(shù)指定的路徑相同；

??需要 rebuild table 的在線 alter table 需要使用 innodb_tmpdir 存放排序磁盤排序文件，如果 innodb_tmpdir 未指定，則使用 tmpdir 的值；

若用戶判斷產(chǎn)生的臨時表|臨時文件一定會轉(zhuǎn)換為磁盤臨時表|臨時文件，那么可以設置 set session big_tables=1;讓產(chǎn)生的臨時表|臨時文件直接存放在磁盤上；

??對于需要較小空間的臨時表|臨時文件，MySQL 要么將其存儲于內(nèi)存，要么放在統(tǒng)一的磁盤臨時表空間中，用完即釋放；

??對于需要較大空間的臨時表|臨時文件，可以通過設置參數(shù)，將其存儲于單獨的目錄|掛載點；例如：load local data 語句或需要重建表的在線 alter table 語句，都有對應的參數(shù)設置其存放臨時表|臨時文件的路徑；

??當前只有 innodb_temp_data_file_path 參數(shù)可以限制 用戶創(chuàng)建的臨時表使用的回滾段的存儲文件的大小，無其他參數(shù)可以限制臨時表|臨時文件可使用的磁盤空間；

MySql判斷臨時表是否存在，不存在就創(chuàng)建

MySql判斷臨時表是否存在，不存在就創(chuàng)建之實例：

CREATE TEMPORARY TABLE IF NOT EXISTS myTempTbl1

(id int,item varchar(10));

附Mysql 創(chuàng)建表語法如下供參考：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

[(create_definition,...)]

[table_options] [select_statement]

當前文章：mysql臨時表怎么判斷,mysql 如何查看臨時表
分享地址：http://jinyejixie.com/article26/dsdjsjg.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供電子商務、Google、App設計、營銷型網(wǎng)站建設、做網(wǎng)站、動態(tài)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)