mysql-ibtmp1表空间暴涨
1、在做****巡检时，发现生产主机xxxx上的临时独立表空间ibtmp1暴涨至200G，借此梳理关于临时表空间相关的点
2、ibtmp1暴涨如何处理？
2.1 简单说明
ibtmp1是非压缩的innodb临时表的独立表空间,通过innodb_temp_data_file_path参数指定文件的路径，文件名和大小，默认配置为ibtmp1:12M:autoextend，也就是说在文件系统磁盘足够的情况下，这个文件大小是可以无限增长的。
2.2 解决方法
a) 停数据库服务进程
# 设置innodb_fast_shutdown参数
SET GLOBAL innodb_fast_shutdown = 0; # 此步骤可以省略
# 关闭数据库实例
/mysql/mysql5731/bin/mysqladmin -uroot -p'xxxx' -S /dbdata/data3306/my3306.sock shutdown
注意：在服务进程停止后，ibtmp1文件会自动清理
b) 修改my.cnf配置文件
为了避免ibtmp1文件无止境的暴涨导致再次出现此情况,可以修改参数，限制其文件最大尺寸。如果文件大小达到上限时，需要生成临时表的SQL无法被执行（一般这种SQL效率也比较低，可借此机会进行优化）
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G # 12M代表文件初始大小，5G代表最大size
c) 重启mysql服务，重启后查看是否生效
3、什么情况下会用到临时表？(本地环境模拟)
当EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 Using Temporary 就表示会用到临时表,例如如下几种常见的情况通常就会用到：
a) GROUP BY 无索引字段或GROUP BY+ ORDER BY 的子句字段不一样时；
/** 先看一下表结构 */
mysql> show create table test_tmp1\G
*************************** 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
/** 结果如下，其实该写法与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 作为对比,发现出现了使用临时表的情况*/
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 ...
/** 简单看一下本表的数据重复插入的情况 */
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)
小结： 上面列举的是最常见的使用临时表的情况,其中基本都是引起慢查询的因素，因此，如果遇到临时表空间文件暴涨是需要查看一下是否有大量的慢查询。
4、和临时表的独立表空间相关的参数
各参数之间相互影响，其中直接影响临时表空间的参数有如下几个：
innodb_temp_data_file_path #innodb临时表的独立表空间
tmp_table_size #内存临时表的大小设置(INNODB存储引擎)
max_heap_table_size #内存临时表的大小设置(MEMORY存储引擎)
default_tmp_storage_engine #默认内存临时表的存储引擎设置
internal_tmp_disk_storage_engine #默认磁盘临时表的存储引擎设置
5、本地模拟一个ibtmp1文件快速暴涨的例子
当临时表中需要存储的数据量超过了上限( tmp-table-size 或 max-heap-table-size 中取其大者),这个时候就需要产生基于磁盘的临时表了，也就是放在innodb_temp_data_file_path指定的临时表空间中，即ibtmp1文件中。
5.1 参数调数并重启服务
tmp_table_size = 2M
max_heap_table_size=2M
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:125M
5.2 建表数据
CREATE TABLE `t2` (
`id_` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`panel_id_` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '面板id',
`cust_width_` int(10) DEFAULT NULL COMMENT '自定义宽',
`cust_height_` int(10) DEFAULT NULL COMMENT '自定义高',
`sn_` int(10) DEFAULT NULL COMMENT '排序',
`user_id_` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '用户id',
`create_time_` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
此时的ibtmp1文件的大小
[root@db1 data]# du -sh * |grep ibtmp1
12M ibtmp1
5.3 利用insert into ...select * from...的方式插入
mysql> explain insert into t2 select * from t2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | INSERT | t2 | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 50 | 100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
mysql> insert into workflow.t2 select * from workflow.t2；
查看ibtmp1文件的大小
[mysql@db1 data]# du -sh * |grep ibtmp1
125M ibtmp1
注意：此时ibtmp1文件已经达到最大值了
5.4 继续测试，看看会发生什么？
mysql> insert into workflow.t1 select * from workflow.t1;
ERROR 1114 (HY000): The table '/mysql/data3307/tmp/#sql_4f98_0' is full
# 报错原因是因为执行的sql需要的系统个表空间大于了ibtmp1的大小
查看ibtmp1文件的大小
[root@db1 data]# du -sh * |grep ibtmp1
125M ibtmp1
5.5 查看max_heap_table_size和tmp_table_size 参数的大小
mysql> show variables like '%table_size';
+---------------------+---------+
| Variable_name | Value |
+---------------------+---------+
| max_heap_table_size | 2097152 |
| tmp_table_size | 2097152 |
+---------------------+---------+
5.6 既然内部临时表(Internal Temporary Table)用于排序，分组，当需要的存储空间超过 tmp-table-size 上限的时候，使用临时表空间。临时表空间是磁盘，速度比不上内存，那可以加大tmp_table_size来优化
调高tmp_table_size和max_heap_table_size参数值来调高内存临时表的上限
mysql> set session tmp_table_size=3221225472;
Query OK, 0 rows affected (0.00 sec)
mysql> set session max_heap_table_size=3221225472;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%table_size';
+---------------------+------------+
| Variable_name | Value |
+---------------------+------------+
| max_heap_table_size | 3221225472 |
| tmp_table_size | 3221225472 |
+---------------------+------------+
2 rows in set (0.00 sec)
mysql> insert into t2 select * from t2 ;
Query OK, 1638403 rows affected (43.84 sec)
Records: 1638403 Duplicates: 0 Warnings: 0
注意：当内存临时表够用时，会优先使用内存临时表
总结：
1、ibtmp1文件空间是可以重复利用的，大小即使达到设置的最大值，也可以继续使用；
2、ibtmp1文件空间不能在线回收，只能重启服务，自动释放；
2、ibtmp1暴涨如何处理？
2.1 简单说明
ibtmp1是非压缩的innodb临时表的独立表空间,通过innodb_temp_data_file_path参数指定文件的路径，文件名和大小，默认配置为ibtmp1:12M:autoextend，也就是说在文件系统磁盘足够的情况下，这个文件大小是可以无限增长的。
2.2 解决方法
a) 停数据库服务进程
# 设置innodb_fast_shutdown参数
SET GLOBAL innodb_fast_shutdown = 0; # 此步骤可以省略
# 关闭数据库实例
/mysql/mysql5731/bin/mysqladmin -uroot -p'xxxx' -S /dbdata/data3306/my3306.sock shutdown
注意：在服务进程停止后，ibtmp1文件会自动清理
b) 修改my.cnf配置文件
为了避免ibtmp1文件无止境的暴涨导致再次出现此情况,可以修改参数，限制其文件最大尺寸。如果文件大小达到上限时，需要生成临时表的SQL无法被执行（一般这种SQL效率也比较低，可借此机会进行优化）
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G # 12M代表文件初始大小，5G代表最大size
c) 重启mysql服务，重启后查看是否生效
3、什么情况下会用到临时表？(本地环境模拟)
当EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 Using Temporary 就表示会用到临时表,例如如下几种常见的情况通常就会用到：
a) GROUP BY 无索引字段或GROUP BY+ ORDER BY 的子句字段不一样时；
/** 先看一下表结构 */
mysql> show create table test_tmp1\G
*************************** 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
/** 结果如下，其实该写法与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 作为对比,发现出现了使用临时表的情况*/
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 ...
/** 简单看一下本表的数据重复插入的情况 */
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)
小结： 上面列举的是最常见的使用临时表的情况,其中基本都是引起慢查询的因素，因此，如果遇到临时表空间文件暴涨是需要查看一下是否有大量的慢查询。
4、和临时表的独立表空间相关的参数
各参数之间相互影响，其中直接影响临时表空间的参数有如下几个：
innodb_temp_data_file_path #innodb临时表的独立表空间
tmp_table_size #内存临时表的大小设置(INNODB存储引擎)
max_heap_table_size #内存临时表的大小设置(MEMORY存储引擎)
default_tmp_storage_engine #默认内存临时表的存储引擎设置
internal_tmp_disk_storage_engine #默认磁盘临时表的存储引擎设置
5、本地模拟一个ibtmp1文件快速暴涨的例子
当临时表中需要存储的数据量超过了上限( tmp-table-size 或 max-heap-table-size 中取其大者),这个时候就需要产生基于磁盘的临时表了，也就是放在innodb_temp_data_file_path指定的临时表空间中，即ibtmp1文件中。
5.1 参数调数并重启服务
tmp_table_size = 2M
max_heap_table_size=2M
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:125M
5.2 建表数据
CREATE TABLE `t2` (
`id_` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`panel_id_` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '面板id',
`cust_width_` int(10) DEFAULT NULL COMMENT '自定义宽',
`cust_height_` int(10) DEFAULT NULL COMMENT '自定义高',
`sn_` int(10) DEFAULT NULL COMMENT '排序',
`user_id_` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '用户id',
`create_time_` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
此时的ibtmp1文件的大小
[root@db1 data]# du -sh * |grep ibtmp1
12M ibtmp1
5.3 利用insert into ...select * from...的方式插入
mysql> explain insert into t2 select * from t2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | INSERT | t2 | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 50 | 100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
mysql> insert into workflow.t2 select * from workflow.t2；
查看ibtmp1文件的大小
[mysql@db1 data]# du -sh * |grep ibtmp1
125M ibtmp1
注意：此时ibtmp1文件已经达到最大值了
5.4 继续测试，看看会发生什么？
mysql> insert into workflow.t1 select * from workflow.t1;
ERROR 1114 (HY000): The table '/mysql/data3307/tmp/#sql_4f98_0' is full
# 报错原因是因为执行的sql需要的系统个表空间大于了ibtmp1的大小
查看ibtmp1文件的大小
[root@db1 data]# du -sh * |grep ibtmp1
125M ibtmp1
5.5 查看max_heap_table_size和tmp_table_size 参数的大小
mysql> show variables like '%table_size';
+---------------------+---------+
| Variable_name | Value |
+---------------------+---------+
| max_heap_table_size | 2097152 |
| tmp_table_size | 2097152 |
+---------------------+---------+
5.6 既然内部临时表(Internal Temporary Table)用于排序，分组，当需要的存储空间超过 tmp-table-size 上限的时候，使用临时表空间。临时表空间是磁盘，速度比不上内存，那可以加大tmp_table_size来优化
调高tmp_table_size和max_heap_table_size参数值来调高内存临时表的上限
mysql> set session tmp_table_size=3221225472;
Query OK, 0 rows affected (0.00 sec)
mysql> set session max_heap_table_size=3221225472;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%table_size';
+---------------------+------------+
| Variable_name | Value |
+---------------------+------------+
| max_heap_table_size | 3221225472 |
| tmp_table_size | 3221225472 |
+---------------------+------------+
2 rows in set (0.00 sec)
mysql> insert into t2 select * from t2 ;
Query OK, 1638403 rows affected (43.84 sec)
Records: 1638403 Duplicates: 0 Warnings: 0
注意：当内存临时表够用时，会优先使用内存临时表
总结：
1、ibtmp1文件空间是可以重复利用的，大小即使达到设置的最大值，也可以继续使用；
2、ibtmp1文件空间不能在线回收，只能重启服务，自动释放；
