ibtmp1是非壓縮的innodb臨時(shí)表的獨(dú)立表空間,通過(guò)innodb_temp_data_file_path參數(shù)指定文件的路徑，文件名和大小，默認(rèn)配置為ibtmp1:12M:autoextend，也就是說(shuō)在支持大文件的系統(tǒng)這個(gè)文件大小是可以無(wú)限增長(zhǎng)的。

臨時(shí)表釋放后，空間會(huì)釋放，但是磁盤(pán)空間不會(huì)釋放，空閑空間可以被復(fù)用。釋放磁盤(pán)空間只能重啟。

ibtmp1增長(zhǎng)的原因

ibtmp1增長(zhǎng)主要與SQL有關(guān)，尤其是大量的分組聚合，排序，join查詢SQL.通常如下情況會(huì)造成iptmp1上漲：

1.查詢語(yǔ)句會(huì)先查詢temp_table_size(內(nèi)存分配)的量，當(dāng)臨時(shí)存儲(chǔ)的量超過(guò)這個(gè)參數(shù)限制時(shí)，就會(huì)在iptmp1中申請(qǐng)占用空間。

2.select order group by GROUP BY 無(wú)索引字段或group by + order by 的子句字段不一樣時(shí)。

3.select (select) 子查詢

4.insert into select ... from ... 表數(shù)據(jù)復(fù)制

5.select union select 聯(lián)合語(yǔ)句

臨時(shí)表空間相關(guān)的參數(shù):tmp_table_size ；max_heap_table_size ；innodb_temp_data_file_path

#查看ibtmp1文件大小

SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE/1024/1024

AS TotalSize_MB, DATA_FREE/1024/1024 as FreeSize_MB, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES

WHERE TABLESPACE_NAME = 'innodb_temporary';

解決辦法

1.限制ibtmp1文件大小：innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G

2.優(yōu)化SQL，避免使用臨時(shí)表。

3.重啟mysql實(shí)例釋放ibtmp1文件

寶塔設(shè)置定時(shí)重啟數(shù)據(jù)庫(kù)方法

新建SHELL腳本，填入以下代碼：service mysqld restart

常見(jiàn)的使用tmp臨時(shí)表空間的場(chǎng)景

9、評(píng)估多表UPDATE語(yǔ)句。

實(shí)例分析

1、問(wèn)題現(xiàn)象

Mysql服務(wù)磁盤(pán)空間告警，經(jīng)過(guò)排查發(fā)現(xiàn)ibtmp1文件非常大，已經(jīng)超過(guò)TB級(jí)別，其他能清理的數(shù)據(jù)已經(jīng)做了清理。
ll -h ibtmp1-rw-r----- 1 mysql mysql 1.2T Aug 15 16:17 ibtmp1

2、問(wèn)題分析

3、解決方案

重啟數(shù)據(jù)庫(kù)，釋放臨時(shí)表空間，同時(shí)為了避免臨時(shí)表空間再次膨脹，可以將其設(shè)置一個(gè)最大的值。
--操作流程：
1）關(guān)閉數(shù)據(jù)庫(kù)實(shí)例。
關(guān)閉后ibtmp1文件會(huì)自動(dòng)清理。
2）修改my.cnf配置文件，限制tmp表空間的大小。
為了避免ibtmp1文件無(wú)止境的暴漲導(dǎo)致再次出現(xiàn)此情況,可以修改參數(shù)，限制其文件最大尺寸。
如果文件大小達(dá)到上限時(shí)，需要生成臨時(shí)表的SQL無(wú)法被執(zhí)行（一般這種SQL效率也比較低，可借此機(jī)會(huì)進(jìn)行優(yōu)化）。
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:50G # 12M代表文件初始大小，50G代表最大size
3）啟動(dòng)mysql服務(wù)，查一下是否生效。
show  variables like 'innodb_temp_data_file_path';+----------------------------+-------------------------------+| Variable_name | Value |+----------------------------+-------------------------------+| innodb_temp_data_file_path | ibtmp1:12M:autoextend:max:50G |+----------------------------+-------------------------------+

臨時(shí)表使用的幾點(diǎn)建議