如何高效计算共同关注者？求优化数据库设计与查询方案

我现在需要计算用户之间的共同关注者，功能本身正常，但当用户拥有大量粉丝时，加载和对比的耗时过长。请推荐合适的数据库设计/查询优化方案。以下是我的现有实现：

现有数据库表结构（TypeORM实体） #

export enum FollowStatus {
  REQUESTED = "REQUESTED", // 请求中
  APPROVED = "APPROVED",   // 已通过
  DECLINED = "DECLINED"    // 已拒绝
}

@Entity("followers")
export class Followers {
  @PrimaryGeneratedColumn()
  @Property()
  id: number;

  @Property()
  @Column()
  followerId: string; // 关注者ID

  @ManyToOne(() => Users, { eager: true })
  @JoinColumn({ name: "followerId" })
  follower: Users; // 关注者关联用户

  @Property()
  @Column()
  followedId: string; // 被关注者ID

  @ManyToOne(() => Users, { eager: true })
  @JoinColumn({ name: "followedId" })
  followed: Users; // 被关注者关联用户

  @Column({
    type: "enum",
    enum: FollowStatus,
    default: FollowStatus.REQUESTED
  })
  @Property()
  status: FollowStatus; // 关注状态
}

现有查询逻辑（Repository层） #

async getFollowers(userId: string, page: number, limit: number) {
    const builder: SelectQueryBuilder<Followers> = this.createQueryBuilder("followers")
      .where("followers.followedId = :userId", { userId })
      .andWhere("followers.status = :status", { status: "APPROVED" })
      .leftJoinAndSelect("followers.follower", "follower")
      .leftJoinAndSelect("follower.followers", "followerFollowers")
      .take(limit)
      .skip((page - 1) * limit);

    const followers = await builder.getManyAndCount();
    return followers;
}

现有业务逻辑层代码 #

async getFollowers(userId: string, page: number, limit: number) {
    page = page || DEFAULT_PAGE_NUMBER;
    limit = limit || DEFAULT_PAGE_SIZE;

    const followers = await this.followersRepository.getFollowers(userId, page, limit);
    const myFollowers = followers[0].map((follower) => follower.followerId);

    const response: SearchResponse<FollowerUsersInterface> = {
      data: await Promise.all(
        followers[0].map(async (user) => {
          // 判断当前用户是否关注了该粉丝
          const isFollowing = user.follower.followers.findIndex((follower) => follower.followerId == userId);

          const userFollowers = user.follower.followers;

          // 计算共同关注者数量，排除用户自身
          const mutualFollowersCount = userFollowers.filter((follower) => {
            if (follower.followerId !== user.followerId) {
              return myFollowers.some((myFollower) => myFollower === follower.followerId && follower.status === FollowStatus.APPROVED);
            }
          }).length;

          return {
            isFollowing: isFollowing > -1,
            userId: user.follower.id,
            userName: user.follower.userName,
            profilePic: await this.s3.getSignedUrl(user.follower.profilePic),
            mutualFollowers: mutualFollowersCount
          } as FollowerUsersInterface;
        })
      ),
      totalCount: followers[1]
    };

    return response;
}

示例数据库记录 #

# id    status     followerId     followedId
20    APPROVED    3              PBpq
22    APPROVED    1              PBpq
24    APPROVED    2              PBpq
25    APPROVED    PBpq           2
26    APPROVED    1              2
28    APPROVED    INOr           PBpq
29    APPROVED    2NAo           PBpq
34    APPROVED    1              2NAo

预期输出 #

• 假设当前用户ID为PBpq
• 访问用户ID为2的页面时，共同关注者数量应为1
• 说明：我（PBpq）和用户ID1都关注了用户ID2，因此共同关注者数为1

一、数据库设计优化 #

1. 添加复合索引 #

当前查询的核心过滤条件是followedId + status，同时计算共同关注者需要频繁关联followerId和followedId，建议添加以下复合索引：

-- 针对获取用户粉丝的查询优化
CREATE INDEX idx_followers_followed_status ON followers(followedId, status);
-- 针对查询用户关注列表的优化（用于共同关注计算）
CREATE INDEX idx_followers_follower_status ON followers(followerId, status);
-- 用于快速判断双向关注关系
CREATE INDEX idx_followers_follower_followed ON followers(followerId, followedId, status);

这些索引可以大幅降低数据库查询时的扫描行数，避免全表扫描。

2. 关闭Eager加载 #

现有实体中follower和followed都开启了eager: true，这会导致每次查询Followers时都自动关联查询Users数据，且在关联followerFollowers时会产生大量嵌套查询。建议改为懒加载（去掉eager: true），在需要时通过leftJoinAndSelect手动关联，减少不必要的数据加载。

修改后的实体字段：

@ManyToOne(() => Users) // 去掉eager: true
@JoinColumn({ name: "followerId" })
follower: Users;

@ManyToOne(() => Users) // 去掉eager: true
@JoinColumn({ name: "followedId" })
followed: Users;

二、查询逻辑优化 #

1. 将共同关注者计算移至数据库层 #

现有逻辑是把所有粉丝的关注列表拉取到内存中，再通过filter和some计算共同关注数，这种方式在粉丝量或关注量大时会产生大量内存开销和循环计算。建议直接通过SQL查询计算共同关注者数量，减少数据传输和内存处理。

优化后的Repository查询（同时获取粉丝信息、是否被当前用户关注、共同关注数）：

async getFollowersWithMutual(userId: string, page: number, limit: number) {
    const builder = this.createQueryBuilder("f")
      // 获取当前用户的粉丝（已通过）
      .select([
        "f.followerId AS userId",
        "u.userName AS userName",
        "u.profilePic AS profilePic",
        // 判断当前用户是否关注该粉丝
        "CASE WHEN EXISTS (SELECT 1 FROM followers f2 WHERE f2.followerId = :userId AND f2.followedId = f.followerId AND f2.status = 'APPROVED') THEN 1 ELSE 0 END AS isFollowing",
        // 计算共同关注者数量：当前用户和该粉丝都关注的用户数
        "(SELECT COUNT(*) FROM followers f1 JOIN followers f2 ON f1.followedId = f2.followedId WHERE f1.followerId = :userId AND f1.status = 'APPROVED' AND f2.followerId = f.followerId AND f2.status = 'APPROVED') AS mutualFollowers"
      ])
      .from("followers", "f")
      .leftJoin("users", "u", "u.id = f.followerId")
      .where("f.followedId = :userId", { userId })
      .andWhere("f.status = 'APPROVED'")
      .orderBy("f.id", "DESC") // 可根据需求调整排序方式
      .take(limit)
      .skip((page - 1) * limit);

    const result = await builder.getRawMany();
    const count = await this.createQueryBuilder("f")
      .where("f.followedId = :userId", { userId })
      .andWhere("f.status = 'APPROVED'")
      .getCount();

    return [result, count];
}

这个查询直接在数据库层面完成所有计算，只返回最终需要的数据，避免了大量数据传输到应用层后的内存处理。

2. 避免N+1查询 #

现有逻辑中Promise.all里调用this.s3.getSignedUrl会产生N次S3请求（N为当前页粉丝数量），可以改为批量获取签名URL，或者在用户表中存储已签名的URL（如果签名有效期较长），减少S3请求次数。

三、业务逻辑优化 #

1. 分页与数据量控制 #

确保分页逻辑正确，避免一次加载过多数据。如果业务允许，可以限制单页最大返回数量（比如最多50条），减少单次请求的数据处理量。

2. 缓存策略 #

对于共同关注者数量这类不要求实时更新的数据，可以添加缓存：

• 使用Redis缓存用户的粉丝列表和共同关注数，设置合理的过期时间（比如5分钟）
• 当用户的关注关系发生变化时，更新对应缓存
• 缓存当前用户的关注列表，避免重复查询

四、代码简化 #

优化后的业务逻辑层代码会大幅简化，无需再在内存中处理数组过滤和匹配：

async getFollowers(userId: string, page: number, limit: number) {
    page = page || DEFAULT_PAGE_NUMBER;
    limit = limit || DEFAULT_PAGE_SIZE;

    const [rawData, totalCount] = await this.followersRepository.getFollowersWithMutual(userId, page, limit);
    
    // 批量获取S3签名URL（假设实现批量方法）
    const profilePicUrls = await this.s3.getSignedUrls(rawData.map(item => item.profilePic));

    const response: SearchResponse<FollowerUsersInterface> = {
      data: rawData.map((item, index) => ({
        isFollowing: item.isFollowing === 1,
        userId: item.userId,
        userName: item.userName,
        profilePic: profilePicUrls[index],
        mutualFollowers: parseInt(item.mutualFollowers)
      })),
      totalCount
    };

    return response;
}

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