支持大文件的系统MYSQL的ibtmp1文件太大,占满了你的空间
SQL
0
2023年11月20日 每日一猜答案:。 答案:ABC。 每日一猜答案分析:
这近服务老时隔段时就报空间不足,找来找去,找到这个MYSQL的ibtmp1文件无序扩张,重启电脑就好,最后找下边的攻略。记录入来,方便大家遇到同样的问题好解决。 从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_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 |版权声明:除非特别标注原创,其它均来自互联网,转载时请以链接形式注明文章出处。