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
相关产品推荐
相关产品推荐

