基于PostgreSQL+PHP的社交好友/关注关系数据库设计优化问询

嘿，这个问题我太有共鸣了——之前做社交应用的时候，一开始也想过加个type字段标记好友/关注，但很快就发现维护这个字段简直是个噩梦：用户A关注B之后，B回关时要更新两条记录的type？或者只更新一条？取消关注的时候还要同步修改？并发场景下很容易出现数据不一致的情况。

其实你现有的两张表结构已经很好了，完全不需要加type字段，只要通过单条SQL查询就能动态区分好友和单向关注，而且数据一致性问题也能彻底避免。下面给你详细讲下方案：

核心思路：用双向关系的存在性判断类型 #

好友本质是「双向关注」，也就是你关注了对方，对方也关注了你。所以我们不需要提前把这个状态存在表中，而是在查询时通过检查反向关系是否存在来动态计算关系类型。

方案1：左连接自关联查询 #

假设你要查询user_id=1的所有关注对象，并区分关系类型，可以用下面的SQL：

SELECT
    u.id AS target_user_id,
    u.username AS target_username,
    -- 如果反向关系存在，就是好友，否则是单向关注
    CASE
        WHEN r2.user_id IS NOT NULL THEN 'friend'
        ELSE 'follow'
    END AS relation_type
FROM
    RELATION r1
-- 关联用户表获取目标用户信息
JOIN
    USERS u ON r1.follow_user_id = u.id
-- 左连接反向关系：检查对方是否也关注了当前用户
LEFT JOIN
    RELATION r2 
    ON r1.follow_user_id = r2.user_id 
    AND r1.user_id = r2.follow_user_id
WHERE
    r1.user_id = 1;

这个查询的逻辑很清晰：

• r1是当前用户的所有关注记录
• r2是目标用户对当前用户的关注记录（左连接保证即使没有回关也能返回结果）
• 通过r2.user_id是否为空，判断是否是双向关注（好友）

方案2：EXISTS子查询（性能更优） #

如果你的用户量很大，用EXISTS子查询可能性能更好，因为它不需要返回整个关联数据集，只要判断存在性即可：

SELECT
    u.id AS target_user_id,
    u.username AS target_username,
    CASE
        WHEN EXISTS (
            SELECT 1 FROM RELATION r2
            WHERE r2.user_id = r1.follow_user_id 
              AND r2.follow_user_id = r1.user_id
        ) THEN 'friend'
        ELSE 'follow'
    END AS relation_type
FROM
    RELATION r1
JOIN
    USERS u ON r1.follow_user_id = u.id
WHERE
    r1.user_id = 1;

关键优化：添加复合索引 #

为了让上面的查询在大数据量下依然快速，一定要给RELATION表加两个复合索引：

-- 优化当前用户关注列表的查询
CREATE INDEX idx_relation_user_follow ON RELATION(user_id, follow_user_id);
-- 优化反向关系的存在性检查
CREATE INDEX idx_relation_follow_user ON RELATION(follow_user_id, user_id);

这两个索引能让数据库快速定位到对应的记录，避免全表扫描。

为什么这个方案比加type字段更好？ #

• 避免数据一致性问题：不需要在用户关注/取消关注时额外更新type字段，完全依赖单向关系的存在性计算，不会出现“一方已回关，但type还是follow”的情况
• 数据冗余少：只存储单向关注记录，每条关系只存一次，节省存储空间
• 扩展性强：如果后续需要增加「拉黑」「屏蔽」等状态，只需要给RELATION表加一个status字段（比如active/blocked），不需要修改关系类型的判断逻辑

拓展：查询粉丝列表并区分关系 #

如果要查询当前用户的粉丝（谁关注了我），并区分“我也关注了对方（好友）”和“我没关注对方（普通粉丝）”，只要把查询逻辑反过来即可：

SELECT
    u.id AS follower_id,
    u.username AS follower_username,
    CASE
        WHEN EXISTS (
            SELECT 1 FROM RELATION r2
            WHERE r2.user_id = r1.user_id 
              AND r2.follow_user_id = r1.follow_user_id
        ) THEN 'friend'
        ELSE 'follower'
    END AS relation_type
FROM
    RELATION r1
JOIN
    USERS u ON r1.user_id = u.id
WHERE
    r1.follow_user_id = 1;

内容的提问来源于stack exchange，提问作者Erman Sinan Turan