異撤俾担現(xiàn)象
- 900M數(shù)據(jù)group by占用臨時磁盤將近9G
-
group by將磁盤打滿睁枕，SQL執(zhí)行報錯客燕，信息如下
image.png
實例基礎(chǔ)信息：
- 數(shù)據(jù)庫版本：mysql-5.7.12
- 表結(jié)構(gòu)（故意沒建立索引）谁鳍、表數(shù)據(jù)量鹰椒、及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 '行為發(fā)生的頁面:每個數(shù)字分別對應(yīng)"關(guān)注頁","個人主頁“,"發(fā)現(xiàn)頁","同城頁"或"其他頁"中的一個',
`video_id` int(11) NOT NULL COMMENT 'video_id',
`author_id` int(11) NOT NULL COMMENT '作者id',
`action_type` int(11) NOT NULL COMMENT '用戶行為類型:每個數(shù)字分別對應(yīng)"播放","關(guān)注","點贊","轉(zhuǎn)發(fā)","舉報"和"減少此類作品"中的一個',
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 發(fā)現(xiàn)磁盤目錄一直在減小，但是du -sh /tmp確一直為4k着逐，而且沒發(fā)先臨時文件崔赌，最終通過官方網(wǎng)站找到了結(jié)果意蛀，請參見：https://dev.mysql.com/doc/refman/8.0/en/temporary-files.html。 -
shell命令為lsof +L1| grep deleted 峰鄙，這種方式能夠找到/tmp目錄下的mysql臨時表文件
image.png
