如何查看mysql生成的臨時(shí)表

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

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

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

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

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

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

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

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

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

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

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

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

??注意：

??可以創(chuàng)建和普通表同名臨時(shí)表，其他會(huì)話可以看到普通表(因?yàn)榭床坏狡渌麜?huì)話創(chuàng)建的臨時(shí)表)；

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

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

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

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

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

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

??注意

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

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

??另外 information_schema.tables 表中是不會(huì)記錄臨時(shí)表的信息的。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

??SQL 中存在派生表

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

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

??SQL 為多表 update

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

??其能使用的內(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)部臨時(shí)表的創(chuàng)建：

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

??當(dāng)在磁盤上創(chuàng)建內(nèi)部臨時(shí)表時(shí)，服務(wù)器會(huì)增加 Created_tmp_disk_tables 的值，

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

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

例外項(xiàng)：

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

因?yàn)檫@些例外項(xiàng)一般需要較大的空間，所以需要考慮是否要將其存放在獨(dú)立的掛載點(diǎn)上。

其他：

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

??通過 lsof +L1 可以查看標(biāo)識(shí)為 delete ，但還未釋放空間的文件。

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

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

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

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

??當(dāng)臨時(shí)表|臨時(shí)文件使用完畢后，會(huì)自動(dòng)回收臨時(shí)表空間文件的大??；

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

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

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

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

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

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

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

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

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

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

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

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]

當(dāng)前文章：mysql臨時(shí)表怎么判斷,mysql 如何查看臨時(shí)表
分享地址：http://www.rwnh.cn/article26/dsdjsjg.html

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

廣告

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