通讯录发现共同好友及可能认识好友的数据库设计

社交app中通过通讯录发现共同好友及可能认识的好友的数据库设计及sql语句

在社交app中，发现好友与邀请好友是用户裂变的重要方式，那么他们是怎么实现的呢

1、通讯录发现好友

这种方式是社交类app最基本的功能，前期通过导入用户的手机通讯录来获来进行匹配，一旦发现有通讯录好友注册了该app，就立马弹出提示

这个数据库的设计很简单

用户表如下

新建一个手机通讯录表contact

新建一个用户好友表friend

通过left join 用户注册表来获取该用户通讯录中有多少个已经在平台注册的用户

sql如下

select a.phone from contact as a right join user as b on a.phone=b.phone where a.uid=用户编号

select a.phone,b.uid  from contact as a right join user as b on a.phone=b.phone where a.uid=用户编号 and b.uid not in (select c.frienduid from friend as c where c.uid=用户编号)    

ok,这样就是现实了通讯录还有之间的发现了

下面我们来讲讲qq之类的社交软件如何通过共同好友数量来进行好友推荐了

SELECT DISTINCT uid,
count(phone) as num from contact WHERE 
phone in (select a.phone from contact as a right join user as b on a.phone=b.phone where a.uid=用户编号  ) and uid<>用户编号 group by uid 
 order by num desc

当然还要排除已经是好友的用户

SELECT DISTINCT uid,
count(phone) as num from contact WHERE 
phone in (select a.phone from contact as a right join user as b on a.phone=b.phone where a.uid=用户编号  and b.uid not in (select d.frienduid from friend as d where d.uid=用户编号)) and uid<>用户编号 group by uid 
 order by num desc

后期建议用户好友推荐的时候采用异步消息队列处理，这样可以减少并发的冲击