1. 啥情況呀
測試環(huán)境機(jī)器磁盤空間不足的告警打破了下午的沉寂，一群人開始忙活著刪數(shù)據(jù)。但是，不久前剛清理了一波數(shù)據(jù)，測試環(huán)境在沒做壓測的情況下不至于短短一個月不到就漲了200G數(shù)據(jù)，于是，我悄悄的進(jìn)入數(shù)據(jù)目錄下，發(fā)現(xiàn)一個不尋常的點(diǎn)，ibtmp1文件有192G
ll -h ibtmp1
-rw-r----- 1 MySQL mysql 192G Aug 12 16:20 ibtmp1
2. 怎么處理
2.1 簡單說明
ibtmp1是非壓縮的innodb臨時表的獨(dú)立表空間,通過
innodb_temp_data_file_path參數(shù)指定文件的路徑，文件名和大小，默認(rèn)配置為ibtmp1:12M:autoextend，也就是說在支持大文件的系統(tǒng)這個文件大小是可以無限增長的。
2.2 解決辦法
a) 找個空閑時間關(guān)閉數(shù)據(jù)
# 設(shè)置innodb_fast_shutdown參數(shù)
SET GLOBAL innodb_fast_shutdown = 0; # 此步驟可以省略
# 關(guān)閉數(shù)據(jù)庫實(shí)例
shutdown; # 因本實(shí)例為MySQL5.7 可以直接在SQL命令行中shutdown關(guān)閉
關(guān)閉后ibtmp1文件會自動清理
b) 修改my.cnf配置文件
為了避免ibtmp1文件無止境的暴漲導(dǎo)致再次出現(xiàn)此情況,可以修改參數(shù)，限制其文件最大尺寸。
如果文件大小達(dá)到上限時，需要生成臨時表的SQL無法被執(zhí)行（一般這種SQL效率也比較低，可借此機(jī)會進(jìn)行優(yōu)化）
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G # 12M代表文件初始大小，5G代表最大size
c） 啟動mysql服務(wù)
啟動數(shù)據(jù)庫后可以查一下是否生效
mysql> show variables like 'innodb_temp_data_file_path';
+----------------------------+-------------------------------+
| Variable_name | Value |
+----------------------------+-------------------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend:max:5G |
+----------------------------+-------------------------------+
1 row in set (0.01 sec)
3. 什么情況下會用到臨時表
當(dāng)EXPLAIN 查看執(zhí)行計劃結(jié)果的 Extra 列中，如果包含 Using Temporary 就表示會用到臨時表,例如如下幾種常見的情況通常就會用到：
a) GROUP BY 無索引字段或GROUP BY+ ORDER BY 的子句字段不一樣時
/** 先看一下表結(jié)構(gòu) */
mysql> show create table test_tmp1G
*************************** 1. row ***************************
Table: test_tmp1
Create Table: CREATE TABLE `test_tmp1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`col2` varchar(25) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
/** group by無索引字段*/
mysql> explain select * from test_tmp1 group by col2 ;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | test_tmp1 | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using temporary; Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
/** group by 與order by字段不一致時，及時group by和order by字段有索引也會使用 */
mysql> explain select name from test_tmp1 group by name order by id desc;
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+
| 1 | SIMPLE | test_tmp1 | NULL | range | name | name | 153 | NULL | 3 | 100.00 | Using index for group-by; Using temporary; Using filesort |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+
1 row in set, 1 warning (0.02 sec)
b) order by 與distinct 共用，其中distinct與order by里的字段不一致（主鍵字段除外）
/** 例子中有無索引時會存在，如果2個字段都有索引會如何*/
mysql> alter table test_tmp1 add key col2(col2);
Query OK, 0 rows affected (1.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
/** 結(jié)果如下，其實(shí)該寫法與group by +order by 一樣*/
mysql> explain select distinct col2 from test_tmp1 order by name;
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | test_tmp1 | NULL | index | col2 | col2 | 78 | NULL | 8 | 100.00 | Using temporary; Using filesort |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)
c) UNION查詢（MySQL5.7后union all已不使用臨時表）
/** 先測一下union all的情況*/
mysql> explain select name from test_tmp1 union all select name from test_tmp1 where id <10;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | PRIMARY | test_tmp1 | NULL | index | NULL | name | 153 | NULL | 8 | 100.00 | Using index |
| 2 | UNION | test_tmp1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 8 | 100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)
/** 再看一下union 作為對比,發(fā)現(xiàn)出現(xiàn)了使用臨時表的情況*/
mysql> explain select name from test_tmp1 union select name from test_tmp1 where id <10;
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | test_tmp1 | NULL | index | NULL | name | 153 | NULL | 8 | 100.00 | Using index |
| 2 | UNION | test_tmp1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 8 | 100.00 | Using where |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)
d) insert into select ...from ...
/** 簡單看一下本表的數(shù)據(jù)重復(fù)插入的情況 */
mysql> explain insert into test_tmp1(name,col2) select name,col2 from test_tmp1;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | INSERT | test_tmp1 | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1 | SIMPLE | test_tmp1 | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using temporary |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
2 rows in set (0.00 sec)
小結(jié)： 上面列舉的是最常見的使用臨時表的情況,其中基本都是引起慢查詢的因素，因此，如果遇到臨時表空間文件暴漲是需要查看一下是否有大量的慢查詢。
4. 和臨時表空間相關(guān)的參數(shù)有哪些
各參數(shù)之間相互影響,其中直接影響臨時表空間的參數(shù)如要有如下幾個
innodb_temp_data_file_path
tmp_table_size
max_heap_table_size
default_tmp_storage_engine
internal_tmp_disk_storage_engine
5. 下面來模擬一個ibtmp1文件快速膨脹的例子
5.1 調(diào)整參數(shù)值
上面列出了主要的參數(shù)，那么先調(diào)整一下參數(shù)，以便于模擬
tmp_table_size = 16M
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G
調(diào)整后重啟數(shù)據(jù)庫
5.2 造一批數(shù)據(jù)
/** 造一張表或者從其他表復(fù)制一批數(shù)據(jù)，為了方便模擬，可以不創(chuàng)建主鍵及索引*/
mysql> create table test_tmp3 select * from db1.tbname;
Query OK, 15948372 rows affected (2 min 27.24 sec)
Records: 15948372 Duplicates: 0 Warnings: 0
此時查看一下ibtmp1文件的大小
ll -h ibtmp1
-rw-r----- 1 mysql mysql 12M Aug 15 16:06 ibtmp1 /** 此時是默認(rèn)的初始大小*/
5.2 使用insert into ... select * from ...的方式插入
/** 此方式將會使用臨時表空間,且 tmp_table_size參數(shù)已調(diào)小為16M，本表當(dāng)前有2G多，所以會使用臨時表空間*/
mysql> insert into test_tmp3 select * from test_tmp3 ;
Query OK, 15948372 rows affected (2 min 7.40 sec)
Records: 15948372 Duplicates: 0 Warnings: 0
此時 查看一下ibtmp1文件的大小
ll -h ibtmp1
-rw-r----- 1 mysql mysql 2.8G Aug 15 16:17 ibtmp1 /** 此時已使用了2.8G*/
此時該表的size如下
ll -h bak_db/test_tmp3* /** 結(jié)果中已有5.8G*/
-rw-r----- 1 mysql mysql 8.9K Aug 15 16:04 bak_db/test_tmp3.frm
-rw-r----- 1 mysql mysql 5.8G Aug 15 16:16 bak_db/test_tmp3.ibd
5.3 繼續(xù)測試,看看會發(fā)生什么
因?yàn)閕btmp1當(dāng)前設(shè)置的最大值為5G，繼續(xù)復(fù)制一個5.8G的數(shù)據(jù)，會不會異常，如果異常有什么表現(xiàn)？
/** 繼續(xù)插入時 因臨時表空間大小有限制，超過5G后將異常，信息如下*/
mysql> insert into test_tmp3 select * from test_tmp3;
ERROR 1114 (HY000): The table '/App/data/mysql3306/tmp/#sql_32469_0' is full
此時 查看一下ibtmp1文件的大小
ll -h ibtmp1
-rw-r----- 1 mysql mysql 5.0G Aug 15 16:17 ibtmp1 /** 此時已使用了5.0G，已達(dá)到上限*/
數(shù)據(jù)庫日志里也會記錄本次異常
2019-08-15T08:23:47.016495Z 3 [ERROR] /usr/local/mysql5.7/bin/mysqld: The table '/app/data/mysql3306/tmp/#sql_32469_0' is full
以上測試實(shí)例因不同的版本可能會有差異，建議大家親自測試一下。
