You need to enable JavaScript to run this app. PostgreSQL 9.6 基于现有双向好友表实现二度好友查询与性能优化
优惠活动
大模型
产品
解决方案
定价
更多

PostgreSQL 9.6 基于现有双向好友表实现二度好友查询与性能优化

PostgreSQL 9.6 社交好友模型查询问题排查与优化

一、现有两种写法的问题定位

  • 第一种嵌套子查询写法:逻辑条件写反。WHERE子句里的CASE判断,要求当f2.user_id = t.friend_id时,必须满足f2.friend_user_id = t.friend_id,相当于强制同一好友关系的两端ID完全相等,根本没有取出“直接好友对应的其他好友”,查询结果必然为空,完全偏离查询目标。
  • 第二种多表关联写法:未适配双向好友的存储规则。所有JOIN条件只单向匹配「上一级关系的friend_user_id等于下一级关系的user_id」的场景,完全漏掉了“好友关系反向存储在friend_user_id字段”的情况——比如测试数据中用户1和用户5的好友关系记录是(5,1),这种反向存储的关系在单向关联时会被完全忽略,导致最终查出的二度好友范围严重残缺。

二、直接好友查询的优化实现

原写法用CASE做条件分支,可读性差、性能差。优化思路是把双向无向的好友关系,统一拆成可直接关联的标准化边结构,避免复杂的分支判断:

-- PostgreSQL 9.6 兼容的直接好友查询
WITH normalized_edges AS (
    -- 把单向存储的双向关系拆成两个方向的无向边,统一为(用户ID, 好友ID)结构
    SELECT user_id, friend_user_id AS friend_id FROM friendship
    UNION
    SELECT friend_user_id, user_id AS friend_id FROM friendship
)
SELECT DISTINCT u.id AS friend_id, u.first_name, u.avatar_url
FROM normalized_edges e
JOIN user_list u ON e.friend_id = u.id
WHERE e.user_id = 1
  -- 可选:排除脏数据导致的用户自关联
  AND e.friend_id != 1;

这个写法的优势:

  • 逻辑统一,不需要写CASE分支判断,后续扩展多度好友查询时可以直接复用标准化边的CTE逻辑
  • 可以通过给friendship表建联合索引实现索引扫描,比原写法的全表CASE匹配性能高很多

三、二度好友查询的正确实现

查询时需要满足三个业务约束:覆盖双向关系的全路径、排除用户自身、排除已经是直接好友的用户,避免结果冗余。

WITH normalized_edges AS (
    -- 复用前面的标准化边结构
    SELECT user_id, friend_user_id AS friend_id FROM friendship
    UNION
    SELECT friend_user_id, user_id AS friend_id FROM friendship
),
direct_friends AS (
    -- 先查出目标用户的所有直接好友
    SELECT friend_id FROM normalized_edges
    WHERE user_id = 1
)
SELECT DISTINCT u.id AS second_degree_friend_id, u.first_name, u.avatar_url
FROM normalized_edges e2
JOIN direct_friends df ON e2.user_id = df.friend_id -- 关联所有直接好友的关系边
JOIN user_list u ON e2.friend_id = u.id
WHERE e2.friend_id != 1 -- 排除用户自身
  AND e2.friend_id NOT IN (SELECT friend_id FROM direct_friends); -- 排除已经是直接好友的用户

拿预置测试数据验证:用户1的直接好友为用户2、3、5,这三个用户的其他关联关系均仅指向用户1和彼此,因此无新增的二度好友,查询结果为空是符合业务逻辑的。

四、配套表结构修正建议

当前friendship表的外键定义存在重复错误:第二个外键fk_friend2错误关联了user_id字段,和第一个外键完全重复,需要修正为关联friend_user_id字段:

-- 修正后的外键定义
CONSTRAINT fk_friend2
  FOREIGN KEY(friend_user_id) 
  REFERENCES user_list(id)

同时建议创建两个联合索引加速好友关系查询:

CREATE INDEX idx_friendship_user_friend ON friendship(user_id, friend_user_id);
CREATE INDEX idx_friendship_friend_user ON friendship(friend_user_id, user_id);

内容的提问来源于stack exchange,提问作者Code Guru

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.20 18:06:07