专栏首页架构说系统设计题(1) 连续5天登录用户(快手)

系统设计题(1) 连续5天登录用户(快手)

早安心语

听到别人生活不如意,就自我感觉良好;

见到他人晒幸福晒成绩,又觉得自己失败迷茫。

把时间都用来关注别人,哪还有功夫提升自己?

记住,你的注意力在哪里,成长就在哪里。

向着自己心中的愿景勇敢前进,踏实走好每一步,终有一天生活会垂青于你

题目:连续5天登录用户

现在我们有一张用户登录日志表,记录用户每天的登录时间,

我们想要统计一下,用户每次连续登录的开始日期和结束日期,以及连续登录天数。

用户ID

登录日期

1001

2017-01-01

1001

2017-01-02

1001

2017-01-04

1001

2017-01-06

1002

2017-01-02

1002

2017-01-03

同学们先思考下,整理下思路,如果没有思路或者某几个点不了解,就可以继续往下看了。

思路

  • 分组
  • 计算个数 必须大于5,并且按照等差数列 最大值和最小相差大于5

答案

mysql> select userid,count(*) as total,max(logindate)-min(logindate) as diff from test.tmloginlog group by userid HAVING total >5 and diff >5 ;
+---------+-------+------+
| user_id | total | diff |
+---------+-------+------+
| 1001 | 13 | 15 |
| 1002 | 15 | 16 |
+---------+-------+------+
2 rows in set (0.01 sec)
CREATE TABLE test.tmloginlog
(
userid int,
logindate date
)ENGINE=InnoDB;
-- 这里的数据是最简化的情况,每个用户每天只有一条登录信息,
insert into test.tmloginlog values(1001,'2017-01-01');
insert into test.tmloginlog values(1001,'2017-01-02');
insert into test.tmloginlog values(1001,'2017-01-04');
insert into test.tmloginlog values(1001,'2017-01-05');
insert into test.tmloginlog values(1001,'2017-01-06');
insert into test.tmloginlog values(1001,'2017-01-07');
insert into test.tmloginlog values(1001,'2017-01-08');
insert into test.tmloginlog values(1001,'2017-01-09');
insert into test.tmloginlog values(1001,'2017-01-10');
insert into test.tmloginlog values(1001,'2017-01-12');
insert into test.tmloginlog values(1001,'2017-01-13');
insert into test.tmloginlog values(1001,'2017-01-15');
insert into test.tmloginlog values(1001,'2017-01-16');
insert into test.tmloginlog values(1002,'2017-01-01');
insert into test.tmloginlog values(1002,'2017-01-02');
insert into test.tmloginlog values(1002,'2017-01-03');
insert into test.tmloginlog values(1002,'2017-01-04');
insert into test.tmloginlog values(1002,'2017-01-05');
insert into test.tmloginlog values(1002,'2017-01-06');
insert into test.tmloginlog values(1002,'2017-01-07');
insert into test.tmloginlog values(1002,'2017-01-08');
insert into test.tmloginlog values(1002,'2017-01-09');
insert into test.tmloginlog values(1002,'2017-01-10');
insert into test.tmloginlog values(1002,'2017-01-11');
insert into test.tmloginlog values(1002,'2017-01-12');
insert into test.tmloginlog values(1002,'2017-01-13');
insert into test.tmloginlog values(1002,'2017-01-16');
insert into test.tmloginlog values(1002,'2017-01-17');
insert into test.tmloginlog values(1003,'2017-01-18');

如果大量数据怎么办

如果这样问,我也不知道怎办? 不管百万,千万 ,还是一个记录 我思路查看 EXPLAIN执行结果

观察如下

  • Using temporary就意味着使用了内部临时表 ----->内存临时表和“磁盘临时表
  • Using filesort -就意味着MySQL 线程需要排序 - --->排序的时候用到了 sort buffer

画外音:

  • 什么是临时表

场景:union去重复

explain select id%10 as m, count(*) as c from t1 group by m;

explain select id%10 as m, count(*) as c from t1 group by m order by null; ExtraUsing index; Using temporary

要解决 group by 语句的优化问题,你可以先想一下这个问题:

执行 group by 语句为什么需要临时表?

group by 的语义逻辑,是统计不同的值出现的个数。

但是,由于每一行的 id%100 的结 果是无序的,所以我们就需要有一个临时表,来记录并统计结果。

那么,如果扫描过程中可以保证出现的数据是有序的,是不是就简单了呢?

explain select SQLBIGRESULT id%100 as m, count(*) as c from t1 group by m; Using index; Using filesort select z, count(*) as c from t1 group by z; explain select id%10 as m, count(*) as c from t1 group by m;

区别是什么:字段 id%10 是无序的,字段 z是有序的。

  • 什么是sort_buffer?
  1. sortbuffersize,就是 MySQL 为排序开辟的内存(sort_buffer)的大小。
  2. 如果要排序的数据量小于 sortbuffersize,排序就在内存中完成。
  3. 但如果排序数据量太大,内存放不下,则不得不利用磁盘临时文件辅助排序
  1. SELECT * FROM information_schema. OPTIMIZER_TRACE\G

ALTER TABLE test.tmloginlog ADD INDEX useridlog(userid,login_date)

优化后

explain select userid,count(*) as total,max(logindate)-min(logindate) as diff from test.tmloginlog group by userid HAVING total >5 and diff >5 ;
Extra 字段里面多了“Using index”,表示的就是使用了覆盖索引,性能上会
快很多

思考与行动

基于上面的 union、union all 和 group by 语句的执行过程的分析,MySQL 什么时候会使用内部临时表?

  1. 如果语句执行过程可以一边读数据,一边直接得到结果,是不需要额外内存的,否则就 需要额外的内存,来保存中间结果;
  2. joinbuffer 是无序数组,sortbuffer 是有序数组,临时表是二维表结构;
  3. 如果执行逻辑需要用到二维表特性,就会优先考虑使用临时表。比如我们的例子中, union 需要用到唯一索引约束, group by 还需要用到另外一个字段来存累积计数
  • 验证
案例1 count()虽然相当于另外一个字段。但是不需要获取内容 比较特殊
explain select user_id,count() from test.tmloginlog group by user_id;
explain select userid,count(logindate) from test.tmloginlog group by user_id;
Using index
案例2 count(*)不变,但是 user_id%10 结果发生变化
explain select userid%10 as m,count(*) from test.tmlogin_log group by m;
Using index;
Using temporary;
Using filesort
案例3 :Using index for group-by
explain select userid,max(logindate) from test.tmloginlog group by user_id;
案例4:select userid,logindate,count(*) from test.tmloginlog group by userid,logindate;
请使用一条语句 统计各班的男生和女生人数
SELECT classid, gender, COUNT(*) num FROM students GROUP BY classid, gender;

参考

分享最实用的经验 , 希望每一位来访的朋友都能有所收获! 不保证一定正确,如果更好方式,疑问,请联系我

本文分享自微信公众号 - 架构说(JiaGouS),作者:王传义

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-07-08

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 专栏RPC实战与核心原理-第三天学习

    服务发现的作用就是实时感知集群 IP 的变化,实现接口跟服务集群节点 IP 的映射。

    程序员小王
  • 漫谈递归-746. 使用最小花费爬楼梯

    程序员小王
  • 记一次内存占用问题的调查过程

    最近在维护一台CentOS服务器的时候,发现内存无端"损失"了许多,free和ps统计的结果相差十几个G,搞的我一度又以为遇到灵异事件了,后来Google了许久...

    程序员小王
  • python3学习之列表

    备注:append将obj作为一个整体追加,无论obj是字符串、数字、字典、列表等,当是字典时全部插入,跟L.extend(iterable) 不同

    py3study
  • Java RMI之HelloWorld,你不可不知的RPC协议

    三哥
  • 联想的麻烦

    人称T客
  • 嵌入式linux之go语言开发(七)protobuf的使用

    之前写过一篇博文:《如果终端采用protobuf与采集前置通信,能带来哪些变革?https://blog.csdn.net/yyz_1987/article/d...

    特立独行的猫a
  • 【一起玩蛇】Nodejs代码审计中的器

    aerfa
  • ESP8266 SDK开发: 外设篇-PWM,呼吸灯

    uint32 duty[1]={1023/1000*100};//高电平时间约是100us

    杨奉武
  • 拉链表是什么

    木东居士

扫码关注云+社区

领取腾讯云代金券