mysql 臨時(shí)空間不足怎么辦

以MySQL 8.0 來說，通過查看 8.0 的官方文檔得知，8.0 的臨時(shí)表空間分為會(huì)話臨時(shí)表空間和全局臨時(shí)表空間，會(huì)話臨時(shí)表空間存儲(chǔ)用戶創(chuàng)建的臨時(shí)表和當(dāng) InnoDB 配置為磁盤內(nèi)部臨時(shí)表的存儲(chǔ)引擎時(shí)由優(yōu)化器創(chuàng)建的內(nèi)部臨時(shí)表，當(dāng)會(huì)話斷開連接時(shí)，其臨時(shí)表空間將被截?cái)嗖⑨尫呕爻刂?；也就是說，在 8.0 中有一個(gè)專門的會(huì)話臨時(shí)表空間，當(dāng)會(huì)話被殺掉后，可以回收磁盤空間；而原來的 ibtmp1 是現(xiàn)在的全局臨時(shí)表空間，存放的是對(duì)用戶創(chuàng)建的臨時(shí)表進(jìn)行更改的回滾段，在 5.7 中 ibtmp1 存放的是用戶創(chuàng)建的臨時(shí)表和磁盤內(nèi)部臨時(shí)表；

創(chuàng)新互聯(lián)建站服務(wù)項(xiàng)目包括浙江網(wǎng)站建設(shè)、浙江網(wǎng)站制作、浙江網(wǎng)頁(yè)制作以及浙江網(wǎng)絡(luò)營(yíng)銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，浙江網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到浙江省份的部分城市，未來相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！

也就是在 8.0 和 5.7 中 ibtmp1 的用途發(fā)生了變化，5.7 版本臨時(shí)表的數(shù)據(jù)存放在 ibtmp1 中，在 8.0 版本中臨時(shí)表的數(shù)據(jù)存放在會(huì)話臨時(shí)表空間，如果臨時(shí)表發(fā)生更改，更改的 undo 數(shù)據(jù)存放在 ibtmp1 中；

實(shí)驗(yàn)驗(yàn)證：將之前的查詢結(jié)果保存成臨時(shí)表，對(duì)應(yīng)會(huì)話是 45 號(hào)，通過查看對(duì)應(yīng)字典表，可知 45 號(hào)會(huì)話使用了 temp_8.ibt 這個(gè)表空間，通過把查詢保存成臨時(shí)表，可以用到會(huì)話臨時(shí)表空間，如下圖：

下一步殺掉 45 號(hào)會(huì)話，發(fā)現(xiàn) temp_8.ibt 空間釋放了，變?yōu)榱顺跏即笮?，狀態(tài)為非活動(dòng)的，證明在 mysql8.0 中可以通過殺掉會(huì)話來釋放臨時(shí)表空間。

總結(jié)：在 mysql5.7 時(shí)，殺掉會(huì)話，臨時(shí)表會(huì)釋放，但是僅僅是在 ibtmp 文件里標(biāo)記一下，空間是不會(huì)釋放回操作系統(tǒng)的。如果要釋放空間，需要重啟數(shù)據(jù)庫(kù)；在 mysql8.0 中可以通過殺掉會(huì)話來釋放臨時(shí)表空間。

mysql 參數(shù)調(diào)優(yōu)（10）之 tmp_table_size 優(yōu)化臨時(shí)表

tmp_table_size默認(rèn)16M。tmp_table_size如果過小，存不下了就會(huì)存到磁盤上。對(duì)于group by會(huì)有性能影響。

下面的sql EXPLAIN 如下，出現(xiàn)了Using temporary。表示查詢會(huì)利用臨時(shí)表。

在默認(rèn)tmp_table_size大小16M下執(zhí)行：

查看臨時(shí)表統(tǒng)計(jì)信息，Created_tmp_disk_tables 為0，Created_tmp_tables 為1表示上訴sql執(zhí)行后生產(chǎn)了一張內(nèi)存里的臨時(shí)表。

將tmp_table_size 調(diào)從16M調(diào)整為16K

再次執(zhí)行,查詢時(shí)間從4變成了18秒

重新統(tǒng)計(jì)

再次查看status，這次有在磁盤上創(chuàng)建1個(gè)臨時(shí)表。

設(shè)置為32M

Percona Server中的臨時(shí)表信息會(huì)記錄到慢查詢?nèi)罩?/p>

由于MySQL慢查詢?nèi)罩纠餂]有使用臨時(shí)表的信息，這就給我們?cè)\斷性能問題帶來了一些不便，第三方的版本如Percona Server，在慢查詢里可以有更詳細(xì)的信息，將會(huì)記錄臨時(shí)表使用的情況，從而有助于我們?cè)\斷和調(diào)優(yōu)。

mysql8中對(duì)臨時(shí)表有較大的優(yōu)化

臨時(shí)表引擎使用innodb（default 磁盤）和temptable(default 內(nèi)存)

MySQL如何使用臨時(shí)表

解決辦法：

當(dāng)臨時(shí)表不存在時(shí)，將查詢結(jié)果保存在臨時(shí)表中：

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name；

我的具體代碼是

CREATE temporary table tmp

(SELECT t1.pid a, t1.pname b,t2.pid c, t2.pdescribe d FROM

(SELECT pid,pname FROM admin_advert_place WHERE pid IN (3,4,5,6,7)) t1 LEFT JOIN

(SELECT pid,pdescribe FROM admin_advert_place

WHERE pgid IN (6,7,8)) t2 ON t1.pid = t2.pid)

技術(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)建的語(yǔ)句如下

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

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

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

??查看用戶創(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)；

??可以使用語(yǔ)句查詢?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 語(yǔ)句是否使用了臨時(shí)表空間：

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

??如果執(zhí)行 SQL 后，表的 ID 列變?yōu)榱藄how processlist 中的 id 列的值，那么說明 SQL 語(yǔ)句使用了臨時(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 語(yǔ)句，客戶端讀取文件并將其內(nèi)容發(fā)送到服務(wù)器，服務(wù)器將其存儲(chǔ)在 tmpdir 參數(shù)指定的路徑中；

??在 replica 中，回放 load data 語(yǔ)句時(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 語(yǔ)句或需要重建表的在線 alter table 語(yǔ)句，都有對(duì)應(yīng)的參數(shù)設(shè)置其存放臨時(shí)表|臨時(shí)文件的路徑；

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

mysql之臨時(shí)表

臨時(shí)表：一種特殊的表，用來存儲(chǔ)查詢的中間結(jié)果，并且會(huì)隨著當(dāng)前連接的結(jié)束而自動(dòng)刪除。mysql中有2種臨時(shí)表，分別是內(nèi)部臨時(shí)表和外部臨時(shí)表。

外部臨時(shí)表關(guān)鍵字：TEMPORARY 。

外部臨時(shí)表特征：

1、關(guān)鍵字TEMPORARY

2、臨時(shí)表創(chuàng)建完成之后，只有當(dāng)前可見，其他連接是看不到的，具有連接隔離性

3、臨時(shí)表在當(dāng)前連接結(jié)束之后，會(huì)自動(dòng)刪除。

臨時(shí)表可以存儲(chǔ)在內(nèi)存和磁盤。

當(dāng)前標(biāo)題：mysql怎么解決臨時(shí)表,Mysql 臨時(shí)表
文章路徑：http://www.rwnh.cn/article0/dsiipoo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供軟件開發(fā)、靜態(tài)網(wǎng)站、搜索引擎優(yōu)化、網(wǎng)站收錄、外貿(mào)網(wǎng)站建設(shè)、網(wǎng)站設(shè)計(jì)

廣告

聲明：本網(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)