怎么修改mysql數(shù)據(jù)庫(kù)臨時(shí)表空間大小

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

10年積累的成都網(wǎng)站制作、成都做網(wǎng)站經(jīng)驗(yàn)，可以快速應(yīng)對(duì)客戶對(duì)網(wǎng)站的新想法和需求。提供各種問(wèn)題對(duì)應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識(shí)你，你也不認(rèn)識(shí)我。但先網(wǎng)站設(shè)計(jì)后付款的網(wǎng)站建設(shè)流程，更有青神免費(fèi)網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。

也就是在 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)，通過(guò)查看對(duì)應(yīng)字典表，可知 45 號(hào)會(huì)話使用了 temp_8.ibt 這個(gè)表空間，通過(guò)把查詢保存成臨時(shí)表，可以用到會(huì)話臨時(shí)表空間，如下圖：

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

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

mysql如何建表空間

在mysql中，也出現(xiàn)了類似oracle中的表空間概念。

不過(guò)二者好像不同？具體不太清楚oracle是怎么回事。

mysql表空間是什么概念呢？

開(kāi)啟了Innodb的innodb_file_per_table這個(gè)參數(shù)之后【innodb_file_per_table = 1】，也就是啟用InnoDB的獨(dú)立表空間模式，便于管理。此時(shí)，在新建的innodb表的數(shù)據(jù)庫(kù)目錄下會(huì)多出來(lái)一個(gè).ibd這個(gè)文件。這個(gè)就是此時(shí)的數(shù)據(jù)文件了。mysql會(huì)把這個(gè)innodb表的數(shù)據(jù)存放在這個(gè)文件中。并且每個(gè)innodb表此時(shí)都會(huì)對(duì)應(yīng)這么一個(gè)ibd文件。

看官方文檔：

If innodb_file_per_table is disabled (the default), InnoDB creates tables in the system tablespace. Ifinnodb_file_per_table is enabled, InnoDB creates each new table using its own .ibd file for storing data and indexes, rather than in the system tablespace.

那么這樣做有什么好處呢？

可以實(shí)現(xiàn)單表在不同的數(shù)據(jù)庫(kù)之間移動(dòng)。具體怎么移動(dòng)呢？假設(shè)有兩個(gè)數(shù)據(jù)庫(kù)，一個(gè)test，一個(gè)tt。

InnoDB 默認(rèn)會(huì)將所有的數(shù)據(jù)庫(kù)InnoDB引擎的表數(shù)據(jù)存儲(chǔ)在一個(gè)共享空間中：ibdata1，這樣就感覺(jué)不爽，增刪數(shù)據(jù)庫(kù)的時(shí)候，ibdata1文件不會(huì)自動(dòng)收縮，單個(gè)數(shù)據(jù)庫(kù)的備份也將成為問(wèn)題。通常只能將數(shù)據(jù)使用mysqldump 導(dǎo)出，然后再導(dǎo)入解決這個(gè)問(wèn)題。共享表空間在Insert操作上少有優(yōu)勢(shì)。其它都沒(méi)獨(dú)立表空間表現(xiàn)好。當(dāng)啟用獨(dú)立表空間時(shí)，請(qǐng)合理調(diào)整一 下innodb_open_files 的值。

-------------------------------------------------------------------------------

需要說(shuō)明的是：

1、設(shè)置了獨(dú)立表空間之后，如果改成了共享表空間，那么，此時(shí)如果執(zhí)行表的插入操作，數(shù)據(jù)會(huì)存放在哪里呢？

對(duì)于之前已經(jīng)存在了的表，還是存放在獨(dú)立表空間。對(duì)于新建的表，就會(huì)存放在共享表空間了。

2、如果一開(kāi)始用了獨(dú)立表空間，后來(lái)改了innodb_file_per_table變量的值，改成獨(dú)立表空間了，那么數(shù)據(jù)如何存儲(chǔ)？

對(duì)于已經(jīng)存在了的innodb引擎的表來(lái)說(shuō)，數(shù)據(jù)還是存放在共享表空間的，而此時(shí)如果創(chuàng)建了新的表，那么就會(huì)在數(shù)據(jù)庫(kù)的目錄中多出一個(gè).ibd的文件用于存儲(chǔ)這個(gè)新表的數(shù)據(jù)。

總結(jié)上面的1、2，就是：原來(lái)的還是按照原來(lái)的方式存儲(chǔ)。新的表按照新的規(guī)則來(lái)存儲(chǔ)。

MYSQL存儲(chǔ)引擎InnoDB（三十五）：臨時(shí)表空間

InnoDB使用會(huì)話臨時(shí)表空間和全局臨時(shí)表空間。

在InnoDB配置為磁盤(pán)內(nèi)部臨時(shí)表的存儲(chǔ)引擎時(shí)，會(huì)話臨時(shí)表空間存儲(chǔ)用戶創(chuàng)建的臨時(shí)表和優(yōu)化器創(chuàng)建的內(nèi)部臨時(shí)表。從 MySQL 8.0.16 開(kāi)始，用于磁盤(pán)內(nèi)部臨時(shí)表的存儲(chǔ)引擎固定為InnoDB。（之前，存儲(chǔ)引擎由internal_tmp_disk_storage_engine的值決定 ）

在第一次請(qǐng)求創(chuàng)建磁盤(pán)臨時(shí)表時(shí)會(huì)話臨時(shí)表空間從臨時(shí)表空間池中被分配給會(huì)話。一個(gè)會(huì)話最多分配兩個(gè)表空間，一個(gè)用于用戶創(chuàng)建的臨時(shí)表，另一個(gè)用于優(yōu)化器創(chuàng)建的內(nèi)部臨時(shí)表。分配給會(huì)話的臨時(shí)表空間用于會(huì)話創(chuàng)建的所有磁盤(pán)臨時(shí)表。當(dāng)會(huì)話斷開(kāi)連接時(shí)，其臨時(shí)表空間將被截?cái)嗖⑨尫呕爻刂?。服?wù)器啟動(dòng)時(shí)會(huì)創(chuàng)建一個(gè)包含 10 個(gè)臨時(shí)表空間的池。池的大小永遠(yuǎn)不會(huì)縮小，并且表空間會(huì)根據(jù)需要自動(dòng)添加到池中。臨時(shí)表空間池在正常關(guān)閉或中止初始化時(shí)被刪除。會(huì)話臨時(shí)表空間文件在創(chuàng)建時(shí)大小為 5 頁(yè)，并且具有.ibt文件擴(kuò)展名。

InnoDB為會(huì)話臨時(shí)表空間保留了40 萬(wàn)個(gè)空間 ID。因?yàn)槊看螁?dòng)服務(wù)器時(shí)都會(huì)重新創(chuàng)建會(huì)話臨時(shí)表空間池，所以會(huì)話臨時(shí)表空間的空間 ID 在服務(wù)器關(guān)閉時(shí)不會(huì)保留，并且可以重復(fù)使用。

innodb_temp_tablespaces_dir 變量定義了創(chuàng)建會(huì)話臨時(shí)表空間的位置。默認(rèn)位置是 #innodb_temp數(shù)據(jù)目錄中的目錄。如果無(wú)法創(chuàng)建臨時(shí)表空間池，則會(huì)拒絕啟動(dòng)。

在基于語(yǔ)句的復(fù)制 (SBR) 模式下，在副本上創(chuàng)建的臨時(shí)表駐留在單個(gè)會(huì)話臨時(shí)表空間中，該臨時(shí)表空間僅在 MySQL 服務(wù)器關(guān)閉時(shí)被截?cái)唷?/p>

INNODB_SESSION_TEMP_TABLESPACES 表提供有關(guān)會(huì)話臨時(shí)表空間的元數(shù)據(jù)。

該INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO表提供有關(guān)在InnoDB實(shí)例中處于活動(dòng)狀態(tài)的用戶創(chuàng)建的臨時(shí)表的元數(shù)據(jù)。

全局臨時(shí)表空間 ( ibtmp1) 存儲(chǔ)對(duì)用戶創(chuàng)建的臨時(shí)表所做的更改的回滾段。

innodb_temp_data_file_path 變量定義了全局臨時(shí)表空間數(shù)據(jù)文件的相對(duì)路徑、名稱、大小和屬性。如果沒(méi)有為innodb_temp_data_file_path指定值 ，則默認(rèn)行為是創(chuàng)建innodb_data_home_dir目錄中命名為ibtmp1的單個(gè)自動(dòng)擴(kuò)展數(shù)據(jù)文件。初始文件大小略大于 12MB。

全局臨時(shí)表空間在正常關(guān)閉或中止初始化時(shí)被刪除，并在每次服務(wù)器啟動(dòng)時(shí)重新創(chuàng)建。全局臨時(shí)表空間在創(chuàng)建時(shí)會(huì)收到一個(gè)動(dòng)態(tài)生成的空間 ID。如果無(wú)法創(chuàng)建全局臨時(shí)表空間，則拒絕啟動(dòng)。如果服務(wù)器意外停止，則不會(huì)刪除全局臨時(shí)表空間。在這種情況下，數(shù)據(jù)庫(kù)管理員可以手動(dòng)刪除全局臨時(shí)表空間或重新啟動(dòng) MySQL 服務(wù)器。重新啟動(dòng) MySQL 服務(wù)器會(huì)自動(dòng)刪除并重新創(chuàng)建全局臨時(shí)表空間。

全局臨時(shí)表空間不能駐留在原始設(shè)備上。

INFORMATION_SCHEMA.FILES提供有關(guān)全局臨時(shí)表空間的元數(shù)據(jù)。發(fā)出與此類似的查詢以查看全局臨時(shí)表空間元數(shù)據(jù)：

默認(rèn)情況下，全局臨時(shí)表空間數(shù)據(jù)文件會(huì)自動(dòng)擴(kuò)展并根據(jù)需要增加大小。

要確定全局臨時(shí)表空間數(shù)據(jù)文件是否正在自動(dòng)擴(kuò)展，請(qǐng)檢查以下 innodb_temp_data_file_path 設(shè)置：

要檢查全局臨時(shí)表空間數(shù)據(jù)文件的大小，請(qǐng)使用與此類似的查詢來(lái)查詢INFORMATION_SCHEMA.FILES表：

TotalSizeBytes顯示全局臨時(shí)表空間數(shù)據(jù)文件的當(dāng)前大小。

或者，檢查操作系統(tǒng)上的全局臨時(shí)表空間數(shù)據(jù)文件大小。全局臨時(shí)表空間數(shù)據(jù)文件位于 innodb_temp_data_file_path 變量定義的目錄中。

要回收全局臨時(shí)表空間數(shù)據(jù)文件占用的磁盤(pán)空間，請(qǐng)重新啟動(dòng) MySQL 服務(wù)器。重新啟動(dòng)服務(wù)器會(huì)根據(jù)innodb_temp_data_file_path定義的屬性刪除并重新創(chuàng)建全局臨時(shí)表空間數(shù)據(jù)文件 。

要限制全局臨時(shí)表空間數(shù)據(jù)文件的大小，請(qǐng)配置 innodb_temp_data_file_path以指定最大文件大小。例如：

配置 innodb_temp_data_file_path 需要重新啟動(dòng)服務(wù)器。