好友的概念和场景

在社交场景中，好友的关系场景无处不在，如何设计一个最优的表结构，来实现高效的存储和快速的查询尤为重要。
在数据库的好友设计中。共同好友的概念。针对互相都为好友的情况，会带来以下的操作和查询：

  • 建立互相好友关系
  • 删除互相好友关系
  • 获取某个用户的好友列表
  • 判断两个用户是否为好友

我们拿两个用户来举例说明场景 Alice（ID=5）和 Bob（ID=3）

Two-row model

使用两条记录来标识双向好友，Alice的好友为Bob；Bob的好友为Alice，插入两条userid关系

1
2
3
4
5
CREATE TABLE friendship (
  user1_id INTEGER NOT NULL,
  user2_id INTEGER NOT NULL,
  PRIMARY KEY (user1_id, user2_id)
);

建立好友关系

1
2
3
4
5
INSERT INTO friendship (user1_id, user2_id) VALUES ((3, 5), (5, 3));

| user1_id | user2_id |
|    3     |    5     |
|    5     |    3     |

获取Alice的好友列表很简单

1
2
3
4
SELECT user2_id FROM friendship WHERE user1_id = 5;

| user2_id |
|    3     |

判断Alice和Bob是否共同好友也容易

1
2
3
4
5
6
SELECT 1 FROM friendship WHERE user1_id = 5 AND user2_id = 3;
-- 或者
SELECT 1 FROM friendship WHERE user1_id = 3 AND user2_id = 5;

| 1 |
| 1 |

删除好友关系

1
DELETE FROM friendship WHERE (user1_id = 3 AND user2_id = 5) OR (user1_id = 5 AND user2_id = 3);

针对与场景的常用操作都能完成，但是会发现，其实一条记录就可以知道关系，两条记录增加了资源和操作的成本，那么如何用一条记录来进行好友关系标识呢？

Single-row model

还是用同样的表结构，实现只插入一条记录。肯定不能是随机插入了，我们可以定义规则较小的id插入user1_id字段，较大的id插入user2_id。

1
2
3
4
5
6
CREATE TABLE mutual_friendship (
  user1_id INTEGER NOT NULL,
  user2_id INTEGER NOT NULL,
  PRIMARY KEY (user1_id, user2_id),
  CONSTRAINT user1_user2_ids CHECK (user1_id < user2_id)
);

建立好友关系

1
2
3
4
5
INSERT INTO mutual_friendship (user1_id, user2_id)
VALUES ((3, 5));

| user1_id | user2_id |
|    3     |    5     |

获取Alice的好友列表
需要注意的是，既要查出用户id比Alice大的用user1_id字段记录的好友关系(此时Alice被记录在user1_id字段)，还要查出用户id比Alice小的用user2_id字段记录的好友关系(此时Alice被记录在user2_id字段)

1
2
3
SELECT user2_id FROM mutual_friendship WHERE user1_id = 5
UNION ALL
SELECT user1_id FROM mutual_friendship WHERE user2_id = 5;

判断Alice和Bob是否共同好友
只需要在代码中保证较小的用户ID作为user1_id字段的值即可

1
SELECT 1 FROM mutual_friendship WHERE user1_id = 3 AND user2_id = 5

删除好友关系

1
DELETE FROM mutual_friendship WHERE user1_id = 3 AND user2_id = 5

两种模式的特点

添加好友 删除好友 获取好友列表 判断是否好友 存储要求
双行 插入两条 删除两条 直接查询 直接查询 两倍空间存储
单行 预处理再插入一条 预处理再删除一条 两部分查询 预处理再查询 最优存储，针对查询需加索引

原文地址