ibtmp1是非壓縮的innodb臨時表的獨立表空間,通過innodb_temp_data_file_path參數指定文件的路徑，文件名和大小，默認配置為ibtmp1:12M:autoextend，也就是說在支持大文件的系統這個文件大小是可以無限增長的。

臨時表釋放后，空間會釋放，但是磁盤空間不會釋放，空閑空間可以被復用。釋放磁盤空間只能重啟。

ibtmp1增長的原因

ibtmp1增長主要與SQL有關，尤其是大量的分組聚合，排序，join查詢SQL.通常如下情況會造成iptmp1上漲：

1.查詢語句會先查詢temp_table_size(內存分配)的量，當臨時存儲的量超過這個參數限制時，就會在iptmp1中申請占用空間。

2.select order group by GROUP BY 無索引字段或group by + order by 的子句字段不一樣時。

3.select (select) 子查詢

4.insert into select ... from ... 表數據復制

5.select union select 聯合語句

臨時表空間相關的參數: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.優化SQL，避免使用臨時表。

3.重啟mysql實例釋放ibtmp1文件

寶塔設置定時重啟數據庫方法

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

常見的使用tmp臨時表空間的場景

9、評估多表UPDATE語句。

實例分析

1、問題現象

Mysql服務磁盤空間告警，經過排查發現ibtmp1文件非常大，已經超過TB級別，其他能清理的數據已經做了清理。
ll -h ibtmp1-rw-r----- 1 mysql mysql 1.2T Aug 15 16:17 ibtmp1

2、問題分析

3、解決方案

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

臨時表使用的幾點建議