异常现象
- 900M数据group by占用临时磁盘将近9G
-
group by将磁盘打满，SQL执行报错，信息如下
image.png
实例基础信息：
- 数据库版本：mysql-5.7.12
- 表结构（故意没建立索引）、表数据量、及SQL的explain如下
CREATE TABLE `user_activity_log` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` varchar(128) NOT NULL COMMENT '用户唯一标识,一个用户可以有多条记录',
`day` varchar(128) NOT NULL COMMENT '日期',
`page` int(11) NOT NULL COMMENT '行为发生的页面:每个数字分别对应"关注页","个人主页“,"发现页","同城页"或"其他页"中的一个',
`video_id` int(11) NOT NULL COMMENT 'video_id',
`author_id` int(11) NOT NULL COMMENT '作者id',
`action_type` int(11) NOT NULL COMMENT '用户行为类型:每个数字分别对应"播放","关注","点赞","转发","举报"和"减少此类作品"中的一个',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20643526 DEFAULT CHARSET=utf8 COMMENT='用户行为日志表'
(root@localhost)[bigdata]> select count(*) from user_activity_log;
+----------+
| count(*) |
+----------+
| 20607228 |
+----------+
1 row in set (11.51 sec)
(root@localhost)[bigdata]> explain select count(distinct(user_id)) from user_activity_log group by day;
+----+-------------+-------------------+------------+------+---------------+------+---------+------+----------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+----------+----------+----------------+
| 1 | SIMPLE | user_activity_log | NULL | ALL | NULL | NULL | NULL | NULL | 20540567 | 100.00 | Using filesort |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+----------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
问题排查
- 1.通过expain看出用到了filesort文件排序
- 2.show global variables like "%tmp%" 找到临时文件目录为/tmp
- 3.SQL运行时在临时文件目录寻找临时文件
在这一步通过df -hT 发现磁盘目录一直在减小，但是du -sh /tmp确一直为4k，而且没发先临时文件，最终通过官方网站找到了结果，请参见：https://dev.mysql.com/doc/refman/8.0/en/temporary-files.html。 -
shell命令为lsof +L1| grep deleted ，这种方式能够找到/tmp目录下的mysql临时表文件
image.png
