这是我参与「第三届青训营 -后端场」笔记创作活动的的第5篇笔记。

前言

该文记录在青训营做极简版抖音中所碰到的有趣问题。

有趣问题

1、关注列表和粉丝列表

a、背景

1.数据库设计背景

2.service接口设计背景

原本的设计是用户模块负责人，操作用户表；关注模块负责人，操作关注表。

给定用户id，查询粉丝列表的step，

1-先从关注表中查询出N个粉丝id；

2-调用用户模块的方法----根据用户id查询用户信息。

问题1，粉丝id有N个，意味着要通过for循环来调用用户模块的方法，即再执行N次SQL；

问题2，用户信息除了用户表的基本信息，还需要该用户的粉丝数、关注数、给定用户id是否关注了该用户。这就意味者，通过用户id来查全部信息，还需要来调对应关注模块的3个方法。

综上所述，以该设计来互相调用，会执行 1 + N * 3 个SQL，非常耗时。

b、改进

可以在循环中开启多个goroutine来查询数据，但是依旧不能解决多SQL的耗时。

可以通过联表查询来，用一条SQL获取粉丝列表的粉丝全信息。同理，关注列表也可采用一条SQL来查询。

1.如何查询粉丝列表的全信息？

将follows表与users表相连，即可得到粉丝的基本信息，但要粉丝的粉丝数，就必须再左联follows表（毕竟粉丝可能为0），对于关注数，就是左联follows表（毕竟关注数可能为0）。

按照上面所想，就相当于查粉丝数和关注数时，要分成两个view分别进行统计，再根据用户id进行联表，实在麻烦，这里采用union all + 增加tag列来标记那个记录是关于粉丝的，那条记录是关于关注的，再外层配合count(if tag = ?，非null,null)来一起统计粉丝数和关注数。

select fid,id,`name`,
count(if(tag = 'follower' and cancel is not null,1,null)) follower_cnt,
count(if(tag = 'follow' and cancel is not null,1,null)) follow_cnt
from
	(
	# 查粉丝
	select f1.user_id fid,u.id,`name`,f2.cancel,'follower' tag
	from follows f1 join users u on f1.follower_id = u.id and f1.cancel = 0
	left join follows f2 on u.id = f2.user_id and f2.cancel = 0
	union all
	# 查关注
	select f1.user_id fid,u.id,`name`,f2.cancel,'follow' tag
	from follows f1 join users u on f1.follower_id = u.id and f1.cancel = 0
	left join follows f2 on u.id = f2.follower_id and f2.cancel = 0
	) T
group by fid,id,`name`

接下来，就是该用户id是否关注了自己的每一个粉丝了，这就必须再去follows表中查一下是否有这条关系了，所以follows右联上面查出来的所有粉丝（毕竟可能没有关系，但不能粉丝记录也没了吧。）

通过查出来的fid 与 id 在follows表中是否存在记录且cancel = 0（cancel = 1表示关注了，又取消了关注。）。当左联上follows表之后，如果存在关系，那么follows表的字段不为null；否则全字段为null。通过字段是否为null，即可判断两者是否存在关注关系。

with T as(
    select fid,id,`name`,
	count(if(tag = 'follower' and cancel is not null,1,null)) follower_cnt,
	count(if(tag = 'follow' and cancel is not null,1,null)) follow_cnt
	from
		(
		# 查粉丝
		select f1.user_id fid,u.id,`name`,f2.cancel,'follower' tag
		from follows f1 join users u on f1.follower_id = u.id and f1.cancel = 0
		left join follows f2 on u.id = f2.user_id and f2.cancel = 0
		union all
		# 查关注
		select f1.user_id fid,u.id,`name`,f2.cancel,'follow' tag
		from follows f1 join users u on f1.follower_id = u.id and f1.cancel = 0
		left join follows f2 on u.id = f2.follower_id and f2.cancel = 0
		) T
	group by fid,id,`name`
)
select T.*,if(f.cancel is null,'false','true') isFollow
from follows f right join T on f.user_id = T.id and f.follower_id = T.fid and f.cancel = 0
where fid = ?

2.如何查询关注列表的全信息

对于关注列表，跟上面一个道理，唯一不同的是，既然是关注列表，那必然对于每一个关注者都是关注状态。

所以外层不需要左联follows表，只需添加'true' isFollow即可。

select fid,id,`name`,
count(if(tag = 'follower' and cancel is not null,1,null)) follower_cnt,
count(if(tag = 'follow' and cancel is not null,1,null)) follow_cnt,
'true' is_follow
from
(
	# 查粉丝
	select f1.follower_id fid,u.id,`name`,f2.cancel,'follower' tag
	from follows f1 join users u on f1.user_id = u.id and f1.cancel = 0
	left join follows f2 on u.id = f2.user_id and f2.cancel = 0
	union all
	# 查关注
	select f1.follower_id fid,u.id,`name`,f2.cancel,'follow' tag
	from follows f1 join users u on f1.user_id = u.id and f1.cancel = 0
	left join follows f2 on u.id = f2.follower_id and f2.cancel = 0
) T
where fid = 3
group by fid,id,`name`

c、额外思考

对于评论中，也存在同样的情况，要找每条评论用户的全信息。这时就需要提前videos和comments表相连，寻得那个发布视频的人，才知道这个人和评论中的人是否存在关注关系，即isFollow的状态。

所以比起上面的粉丝列表，这里需要四表相连，目的就是为了找到发布视频的人。

select T.*,if(f.cancel is null,'false','true') isFollow
from follows f right join
(
	select cid,vid,author_id,id user_id,`name`,comment_text,create_date,
	count(if(tag = 'follower' and cancel is not null,1,null)) follower_cnt,
	count(if(tag = 'follow' and cancel is not null,1,null)) follow_cnt
	from
		(
		# 查粉丝数
		select c.id cid,v.author_id,v.id vid,u.id,`name`,f.cancel,
		comment_text,create_date,'follower' tag
		from videos v join comments c on v.id = c.video_id
		join users u on c.user_id = u.id and c.cancel = 0
		left join follows f on u.id = f.user_id and f.cancel = 0
		union all
		# 查关注
		select c.id cid,v.author_id,v.id vid,u.id,`name`,f.cancel,
		comment_text,create_date,'follow' tag
		from videos v join comments c on v.id = c.video_id
		join users u on c.user_id = u.id and c.cancel = 0
		left join follows f on u.id = f.follower_id and f.cancel = 0
		) T
		group by cid,author_id,vid,id,`name`,comment_text,create_date
) T on f.user_id = T.author_id and f.follower_id = T.user_id and f.cancel = 0
where vid = 10
order by create_date desc

看到别人的做法，将子查询写到select也是一种不错的做法。

总结

1）union all + 新增tag列字段 + count(if tag = ?)来完成多行的分类统计。