如何通过SQL查询获取同时拥有指定多个兴趣的人员？

针对你的需求，以下是几种适用于大规模数据库的高效查询方案，前提是确保person_interest_relation表上已创建复合索引（推荐(interest_id, person_id)或(person_id, interest_id)），这是大表查询性能的关键。

方案一：分组统计筛选（通用型） #

通过分组后统计用户匹配的兴趣数量，筛选出数量等于目标兴趣数的用户：

SELECT p.person_id, p.name
FROM person p
JOIN person_interest_relation pir ON p.person_id = pir.person_id
WHERE pir.interest_id IN (1, 5)
GROUP BY p.person_id, p.name
HAVING COUNT(DISTINCT pir.interest_id) = 2;

• 若person_interest_relation表中person_id和interest_id是唯一组合（无重复记录），可去掉DISTINCT，改用COUNT(*)进一步提升效率。

方案二：多表连接（兴趣数量较少时优先） #

通过多次关联兴趣关系表，强制匹配所有指定兴趣，直接取交集：

SELECT p.person_id, p.name
FROM person p
JOIN person_interest_relation pir1 ON p.person_id = pir1.person_id AND pir1.interest_id = 1
JOIN person_interest_relation pir2 ON p.person_id = pir2.person_id AND pir2.interest_id = 5;

• 这种方式利用索引快速定位单个兴趣的用户集合，多次JOIN的性能在兴趣数量不多时优于分组统计。

方案三：EXISTS子查询（高性能优先） #

通过多个EXISTS子查询验证用户是否同时拥有所有指定兴趣，子查询找到匹配记录即停止，索引利用率极高：

SELECT p.person_id, p.name
FROM person p
WHERE EXISTS (
    SELECT 1 FROM person_interest_relation pir 
    WHERE pir.person_id = p.person_id AND pir.interest_id = 1
)
AND EXISTS (
    SELECT 1 FROM person_interest_relation pir 
    WHERE pir.person_id = p.person_id AND pir.interest_id = 5
);

• 当目标兴趣数量较多时，该方案的性能通常优于前两种，因为避免了分组或多表JOIN的额外开销。

额外性能优化建议 #

1. 避免使用SELECT *，仅查询需要的字段（如person_id、name），减少数据传输和内存占用。
2. 若业务允许，可定期对person_interest_relation表做数据清理，移除重复的person_id+interest_id记录。

内容的提问来源于stack exchange，提问作者manonthemoon