NestJS集成Prisma(Postgres)时RefreshToken操作耗时飙升问题排查

使用NestJS结合Prisma（PostgreSQL）开发时，最初仅为UserEntity创建仓库，运行状态正常。新增RefreshToken存储及对应仓库/服务后，在FitnessUserService中注入RefreshTokenService并调用其createRefreshSession方法，请求耗时从原本170ms急剧上升至50秒，需恢复性能至正常水平。

Prisma Schema #

generator client {
  provider = "prisma-client-js"
  output   = "../../../node_modules/.prisma/client"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

enum UserRole {
  trainer
  client
}

model UserEntity {
  userId            Int                     @id @default(autoincrement()) @map("user_id")
  userName          String                  @default("") @map("user_name")
  userMail          String                  @unique @default("") @map("user_mail")
  userAvatar        String                  @default("") @map("user_avatar")
  passwordHash      String                  @default("")
  userGender        String                  @default("") @map("user_gender")
  birthDate         String                  @default("") @map("birth_date")
  userRole          UserRole                @map("user_role")
  description       String                  @default("")
  location          String                  @default("")
  backgraundPicture String                  @default("") @map("backgraund_picture")
  createdAt         DateTime                @default(now()) @map("created_at")
  clientBody        ClientBody?
  trainerBody       TrainerBody?
  levelOfExperience String                  @default("") @map("level_of_experience")

  @@map("users")
}

model ClientBody {
  clientBodyId          Int        @id @default(autoincrement()) @map("user_body_id")
  user                  UserEntity @relation(fields: [userId], references: [userId], onDelete: Cascade, onUpdate: Cascade)
  userId                Int        @unique @map("user_id")
  timeOfTraining        String     @default("") @map("time_of_training")
  caloryLosingPlanTotal Int        @default(0) @map("calory_losing_plan_total")
  caloryLosingPlanDaily Int        @default(0) @map("calory_losing_plan_daily")
  readinessForTraining  Boolean    @default(false) @map("readiness_for_training")

  @@map("client_body")
}

model TrainerBody {
  trainerBodyId       Int        @id @default(autoincrement()) @map("trainer_body_id")
  user                UserEntity @relation(fields: [userId], references: [userId], onDelete: Cascade, onUpdate: Cascade)
  userId              Int        @unique @map("user_id")
  sertificate         String     @default("")
  merit               String     @default("")
  readinessForPrivate Boolean    @default(false) @map("readiness_for_private")

  @@map("trainer_body")
}

model RefreshToken {
  refreshTokenId Int      @id @default(autoincrement()) @map("refresh_token_id")
  tokenId        String   @map("token_id")
  userId         String   @map("user_id")
  createdAt      DateTime @default(now()) @map("created_at")
  expiresIn      DateTime @default(now()) @map("expires_in")

  @@map("refresh_token_store")
}

相关服务代码 #

@Injectable()
export class FitnessUserService {
  constructor(
    private readonly fitnessUserRepository: RefreshTokenService,
    private readonly jwtService: JwtService,
    private readonly refreshTokenService: RefreshTokenService,
    @Inject(jwtConfig.KEY)
    private readonly jwtOptions: ConfigType<typeof jwtConfig>
  ) {}


  public async createUserToken(user: User) {
    console.log({ user });
    const accessTokenPayload = createJWTPayload(user);
    const refreshTokenPayload = {
      ...accessTokenPayload,
      tokenId: crypto.randomUUID(),
    };
    //此处调用后耗时从120ms飙升至50000ms
    await this.refreshTokenService.createRefreshSession(refreshTokenPayload);

    return {
      accessToken: await this.jwtService.signAsync(accessTokenPayload),
      refreshToken: await this.jwtService.signAsync(refreshTokenPayload, {
        secret: this.jwtOptions.refreshTokenSecret,
        expiresIn: this.jwtOptions.refreshTokenExpiresIn,
      }),
    };
  }
}

1. 修复依赖注入的类型错误 #

FitnessUserService构造函数中，fitnessUserRepository被错误注入为RefreshTokenService，这会导致不必要的实例冲突或循环依赖，直接拖慢性能：

// 修正前
constructor(
  private readonly fitnessUserRepository: RefreshTokenService, // 类型不匹配
  // ...其他注入项
) {}

// 修正后
constructor(
  private readonly fitnessUserRepository: FitnessUserRepository, // 替换为正确的用户仓库类型
  private readonly jwtService: JwtService,
  private readonly refreshTokenService: RefreshTokenService,
  @Inject(jwtConfig.KEY)
  private readonly jwtOptions: ConfigType<typeof jwtConfig>
) {}

2. 修正RefreshToken模型的字段类型不匹配 #

UserEntity的userId是Int类型，但RefreshToken的userId定义为String，插入时PostgreSQL会进行隐式类型转换，大量转换会严重降低写入速度：

// 修正前
model RefreshToken {
  userId         String   @map("user_id")
  // ...其他字段
}

// 修正后
model RefreshToken {
  userId         Int      @map("user_id") // 与UserEntity的userId类型保持一致
  // ...其他字段
}

修改后执行prisma migrate dev更新数据库表结构。

3. 为RefreshToken表添加必要索引 #

针对高频操作的字段添加索引，提升写入和查询性能：

model RefreshToken {
  refreshTokenId Int      @id @default(autoincrement()) @map("refresh_token_id")
  tokenId        String   @map("token_id") @unique // 防止重复token，加速唯一性校验
  userId         Int      @map("user_id")
  createdAt      DateTime @default(now()) @map("created_at")
  expiresIn      DateTime @default(now()) @map("expires_in")

  @@map("refresh_token_store")
  @@index([userId]) // 为userId添加索引，后续查询用户的refresh token时更快
}

4. 精简createRefreshSession方法实现 #

确保该方法仅执行必要的插入操作，避免多余查询或循环：

// RefreshTokenService中的示例实现
async createRefreshSession(payload: { userId: number; tokenId: string; expiresIn: Date }) {
  return this.prisma.refreshToken.create({
    data: {
      userId: payload.userId,
      tokenId: payload.tokenId,
      expiresIn: payload.expiresIn,
    },
  });
}

5. 优化数据库连接配置 #

在schema.prisma中调整连接池大小，适配服务器性能：

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  pool_size = 10 // 根据服务器配置调整，默认值为5
}

同时可开启PostgreSQL慢查询日志，定位是否存在数据库层面的瓶颈。

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