TypeORM查询优化：高效获取仅含最新消息的聊天室

原查询的性能瓶颈主要来自这几个问题：

• 通过左关联找“没有更晚消息”的方式会产生大量笛卡尔积，计算量陡增
• 重复关联r.members和profile，做了冗余操作
• 先拉取所有消息再筛选，数据处理量太大

下面是两种针对性的优化方案，配合索引调整能大幅提升查询速度：

方案1：子查询预取每个聊天室的最新消息时间 #

先通过子查询算出每个聊天室的最新消息时间，再关联获取对应消息，避免无效关联：

const rooms = this.roomRepository
  .createQueryBuilder("r")
  // 直接关联当前用户所属的聊天室，用innerJoin替代leftJoin减少无效数据
  .innerJoin("r.members", "m", "m.profile.id = :id", { id: profilesId })
  // 子查询：按聊天室分组，取每个组的最新消息时间
  .leftJoin(
    (subQuery) => {
      return subQuery
        .select("msg.roomId", "roomId")
        .addSelect("MAX(msg.createdAt)", "maxCreatedAt")
        .from(Message, "msg")
        .groupBy("msg.roomId");
    },
    "latest_msg_info",
    "r.id = latest_msg_info.roomId"
  )
  // 只关联每个聊天室的最新消息
  .leftJoinAndSelect(
    "r.messages",
    "msg",
    "msg.roomId = r.id AND msg.createdAt = latest_msg_info.maxCreatedAt"
  )
  // 一次性关联所有需要的关联表，避免重复操作
  .leftJoinAndSelect("msg.text", "text")
  .leftJoinAndSelect("msg.authorProfile", "authorProfile")
  .leftJoinAndSelect("r.members", "members")
  .leftJoinAndSelect("members.profile", "profile")
  .leftJoinAndSelect("profile.avatar", "avatar")
  .leftJoinAndSelect("profile.title", "title")
  // 按最新消息时间排序
  .orderBy("latest_msg_info.maxCreatedAt", "DESC")
  .limit(20)
  .getMany();

方案2：用窗口函数筛选最新消息（适用于PostgreSQL/MariaDB 10.3+） #

如果你的数据库支持窗口函数，用ROW_NUMBER()给每个聊天室的消息按时间排序，直接取第一条：

const rooms = this.roomRepository
  .createQueryBuilder("r")
  .innerJoin("r.members", "m", "m.profile.id = :id", { id: profilesId })
  // 子查询中用窗口函数给每个聊天室的消息编号，最新的排第1
  .leftJoinAndSelect(
    (subQuery) => {
      return subQuery
        .select("msg.*")
        .addSelect(
          "ROW_NUMBER() OVER (PARTITION BY msg.roomId ORDER BY msg.createdAt DESC)",
          "row_num"
        )
        .from(Message, "msg");
    },
    "msg",
    "msg.roomId = r.id AND msg.row_num = 1"
  )
  // 关联所需的关联表
  .leftJoinAndSelect("msg.text", "text")
  .leftJoinAndSelect("msg.authorProfile", "authorProfile")
  .leftJoinAndSelect("r.members", "members")
  .leftJoinAndSelect("members.profile", "profile")
  .leftJoinAndSelect("profile.avatar", "avatar")
  .leftJoinAndSelect("profile.title", "title")
  .orderBy("msg.createdAt", "DESC")
  .limit(20)
  .getMany();

必加的索引优化 #

给这两个表加联合索引，能让子查询和关联操作的速度提升数倍：

-- 加速按聊天室分组取最新消息的操作
CREATE INDEX idx_message_room_created ON message(roomId, createdAt DESC);
-- 加速查找用户所属聊天室的操作
CREATE INDEX idx_member_profile_room ON member(profileId, roomId);

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