MySQL的ibtmp1文件怎么这么大？
1. 啥情况呀
测试环境机器磁盘空间不足的告警打破了下午的沉寂，一群人开始忙活着删数据。但是，不久前刚清理了一波数据，测试环境在没做压测的情况下不至于短短一个月不到就涨了200G数据，于是，我悄悄的进入数据目录下，发现一个不寻常的点，ibtmp1文件有192G ll -h ibtmp1 -rw-r----- 1 mysql mysql 192G Aug 12 16:20 ibtmp1
2. 怎么处理
2.1 简单说明
ibtmp1是非压缩的innodb临时表的独立表空间,通过innodb_temp_data_file_path参数指定文件的路径，文件名和大小，默认配置为ibtmp1:12M:autoextend，也就是说在支持大文件的系统这个文件大小是可以无限增长的。
2.2 解决办法
a) 找个空闲时间关闭数据 # 设置innodb_fast_shutdown参数 SET GLOBAL innodb_fast_shutdown = 0; # 此步骤可以省略 # 关闭数据库实例 shutdown; # 因本实例为MySQL5.7 可以直接在SQL命令行中shutdown关闭
关闭后ibtmp1文件会自动清理
b) 修改my.cnf配置文件
为了避免ibtmp1文件无止境的暴涨导致再次出现此情况,可以修改参数，限制其文件最大尺寸。
如果文件大小达到上限时，需要生成临时表的SQL无法被执行（一般这种SQL效率也比较低，可借此机会进行优化） innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G # 12M代表文件初始大小，5G代表最大size
c） 启动mysql服务
启动数据库后可以查一下是否生效 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. 什么情况下会用到临时表
当EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 Using Temporary 就表示会用到临时表,例如如下几种常见的情况通常就会用到：
a) GROUP BY 无索引字段或GROUP BY+ ORDER BY 的子句字段不一样时 /** 先看一下表结构 */ 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 /** 结果如下，其实该写法与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 | | 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 tmp_table_size max_heap_table_size default_tmp_storage_engine internal_tmp_disk_storage_engine
5. 下面来模拟一个ibtmp1文件快速膨胀的例子
5.1 调整参数值
上面列出了主要的参数，那么先调整一下参数，以便于模拟 tmp_table_size = 16M innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G
调整后重启数据库
5.2 造一批数据 /** 造一张表或者从其他表复制一批数据，为了方便模拟，可以不创建主键及索引*/ 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 /** 此时是默认的初始大小*/
5.2 使用insert into ... select * from ...的方式插入 /** 此方式将会使用临时表空间,且 tmp_table_size参数已调小为16M，本表当前有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* /** 结果中已有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 继续测试,看看会发生什么
因为ibtmp1当前设置的最大值为5G，继续复制一个5.8G的数据，会不会异常，如果异常有什么表现？ /** 继续插入时 因临时表空间大小有限制，超过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，已达到上限*/
数据库日志里也会记录本次异常 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
以上测试实例因不同的版本可能会有差异，建议大家亲自测试一下。
另外，大家帮忙关注一下我的微信公众号： 数据库干货铺 ，将不定期有书籍及学习资料赠送
人民日报崔斌赞同孩子接触2至3个运动人民日报崔斌赞同孩子接触2至3个运动刘国梁委员建议每个孩子接触熟悉2个运动项目，人民日报崔斌表示赞同。时代的快速发展，加上生活节奏的加快，很多上班族每天的大部分时间都是在工作，在运
乌兹别克斯坦人赞成丁克吗？乌兹别克斯坦姑娘认为什么时候该生孩子？她们对丁克又有什么样的看法？让我们听一听她们怎么说。阿齐扎古利亚莫娃，25岁，塔什干市，博主企业家我丈夫和我有两个很棒的儿子。我提心吊胆地为第
特应性皮炎适用哪种润肤乳？求推荐！！！自从我家孩子走上寻医问药的路程，几乎所有看诊的医生都会说要注意皮肤保湿，刚开始没怎么注意，每天一次润肤乳都没有做到。随着孩子症状越来越严重，我也开始重视润肤这个问题了。从去年夏天开
孕28周后产检的时候，这三个问题一定要清楚！胎位正不正，该如何纠正胎位？怀孕对于任何一个女性而言都是一个特别幸福的经历，但是与此同时，孕期中所面临的问题也远不止于此。随着孕期的进行，婴儿的生长和发育非常迅速，与此同时也会面临
政策扶持支撑行业站上风口美恩早教托育园为入托婴幼儿设置了宽敞的户外活动场地。雅仔锦泰托育园在空间设计上力求通透，让入托婴幼儿更加感受到与外界环境的密切联系。正雅幼儿园外景。2月14日，汕头市举办第一批3岁
小王子中最经典的25个句子，值得我们回味一生小王子是作家安东尼德圣埃克苏佩里于1942年写成的著名法国儿童文学短篇小说。故事讲述了小王子从自己星球出发前往地球的过程中，所经历的各种历险。作者以小王子的孩子式的眼光，透视出成人
网红马小跳出轨成性，前妻晒照再曝猛料，每任小三他都给买戒指拥有一百多万粉丝的网红马小跳（原名马小飞），被前妻老梁曝出出轨，醉驾，违背妇女意志强行发生关系等黑料一事已经持续数天，获得了广大网友的关注。根据事件双方表现来看，网红马小跳几乎被实
近七年的四部现象级电视剧，狂飙只能屈居第二早些年，或许是因为没有太多的娱乐活动，亦或者是影视剧的发展还处于初始阶段，所以每当有一部电视剧或者是电影问世时，都能得到广大观众群体的集体追捧。像上个世纪八十年代诞生的西游记，曾创
甲流肆虐！孩子发热先护脑，瀚方医用退热凝胶安全退热物理降温！近日，甲流进入高发期，北京上海浙江天津等地均有学校因学生患甲流而停课。网上也有很多朋友表示自己中招，太难受了甲流很严重吗？很多人因为新冠的三年肆虐忽略了甲流，但甲流的传染性和致病率
还在一刀切不让孩子看短视频？优秀的家长都在这样做自从我家娃上了小学，我常常都在他不行和他好像还行之间来回横跳。我自己从小是那种很让老师家长省心的娃儿，没怎么为学习的事情烦恼过。我真没想到有朝一日，我隔三差五就要为了学习的事情点头
普通人每次打开电脑能做什么？今天教宝爸宝妈们给孩子做手抄报新学期已经过去好几周了，各位宝爸宝妈们孩子一周一到两张的手抄报做的还开心吗？心灵手巧多才多艺的宝爸宝妈们感觉和孩子一起设计完成手抄报是很快乐的亲子时光，但对于没有绘画天赋又有点小忙
