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

最新推荐文章于 2024-08-29 10:43:19 发布

皮蛋小粥

最新推荐文章于 2024-08-29 10:43:19 发布

阅读量8.6k

点赞数 1

分类专栏：数据库

本文链接：https://blog.youkuaiyun.com/qq442270636/article/details/79061662

版权

数据库专栏收录该内容

7 篇文章

订阅专栏

这篇博客讨论了如何在MySQL中查询用户每日的阅读量，同时关注到效率问题。当查询计划显示'Using temporary; Using filesort'时，表明查询过程中使用了临时表和文件排序，这可能导致性能下降。作者建议通过合理创建索引来优化查询，并介绍了日期格式化的多种方式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

做后台需要得到用户每天在我们公司社区阅读文章的访问量，以及阅读量大于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

查看一下运行情况（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

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)
%p AM 或 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 位