MySQL查询用户每天的阅读量以及Using temporary; Using filesort

青旅半醒 2022-05-31 23:40 283阅读 0赞

做后台需要得到用户每天在我们公司社区阅读文章的访问量，以及阅读量大于10的时间段（测试库数据量为5千多）

select b.user_name,COUNT(a.info_id) as cou,DATE_FORMAT(a.create_time,'%y-%m-%d') as time,a.create_time,a.user_id from user_read_info a LEFT JOIN user b on a.user_id = b.id 
     GROUP BY a.user_id,DATE_FORMAT(a.create_time,'%y%m%d') 
     HAVING cou >10

![SouthEast][]

查看一下运行情况（explain)：

EXPLAIN select b.user_name,COUNT(a.info_id) as cou,DATE_FORMAT(a.create_time,'%y-%m-%d') as time,a.create_time,a.user_id from user_read_info a LEFT JOIN user b on a.user_id = b.id 
     GROUP BY a.user_id,DATE_FORMAT(a.create_time,'%y%m%d') 
     HAVING cou >10

![SouthEast 1][]

a表的type 为ALL 也就是最耗时的情况，扫描行数2459行，Extra出现Using temporary; Using filesort说明用到了临时表（这里是group by的原因，创建一个临时表用以执行分组操作。所以查询执行计划中出现“Using temporary”字样就表示MySQL采用了常规的处理方式）和在文件内进行了排序，才返回出来，可知这不是最优路线

公司数据库的原因不敢随便建立索引，对于Using temporary; Using filesort应适当的使用索引来处理

对于日期

date 参数是合法的日期。format 规定日期/时间的输出格式。  
  
  
可以使用的格式有：  
  
  
格式描述  
%a缩写星期名  
%b缩写月名  
%c月，数值  
%D带有英文前缀的月中的天  
%d月的天，数值(00-31)  
%e月的天，数值(0-31)  
%f微秒  
%H小时 (00-23)  
%h小时 (01-12)  
%I小时 (01-12)  
%i分钟，数值(00-59)  
%j年的天 (001-366)  
%k小时 (0-23)  
%l小时 (1-12)  
%M月名  
%m月，数值(00-12)  
%pAM 或 PM  
%r时间，12-小时（hh:mm:ss AM 或 PM）  
%S秒(00-59)  
%s秒(00-59)  
%T时间, 24-小时 (hh:mm:ss)  
%U周 (00-53) 星期日是一周的第一天  
%u周 (00-53) 星期一是一周的第一天  
%V周 (01-53) 星期日是一周的第一天，与 %X 使用  
%v周 (01-53) 星期一是一周的第一天，与 %x 使用  
%W星期名  
%w周的天 （0=星期日, 6=星期六）  
%X年，其中的星期日是周的第一天，4 位，与 %V 使用  
%x年，其中的星期一是周的第一天，4 位，与 %v 使用  
%Y年，4 位  
%y年，2 位

[SouthEast]: /images/20220601/db5e918cab3c4b80aafb7a7375a5e4ca.png
[SouthEast 1]: /images/20220601/46938ea2fb9e4676a92383fc0b8bf303.png