异常现象

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