Redis缓存与分页逻辑结合导致查询挂起的问题求助

可能的问题点 #

1. 事务与缓存中间件的冲突：你在Prisma事务里同时执行count和带skip的findMany查询，prisma-redis-middleware处理事务内异步缓存操作时，可能触发了阻塞或死锁。
2. 缓存键未包含分页参数：如果中间件生成缓存键时没把skip、take、orderBy这些动态参数算进去，缓存逻辑可能出现异常，导致查询无限挂起。
3. Prisma实例绑定错误：代码里创建的Prisma实例是client，但绑定中间件时用的是prisma?.$use，如果全局prisma变量没正确初始化，中间件绑定会出问题，进而引发查询异常。

可行的解决办法 #

1. 修复Prisma实例与中间件的绑定 #

直接修改prismadb.ts里的中间件绑定代码，用创建好的client实例：

// 替换原来的prisma?.$use(cacheMiddleware)
client.$use(cacheMiddleware)

2. 手动实现分页查询的缓存（推荐） #

放弃用中间件自动缓存分页查询，自己构建缓存键，确保所有分页、过滤参数都被包含，避免中间件的潜在bug：

export const getProjects = async (
  filter?: PaginationDataFilter,
  teamId?: string
): Promise<PaginationData<Project>> => {
  try {
    // 生成唯一缓存键，涵盖所有影响查询结果的参数
    const cacheKey = `projects:${teamId || 'all'}:${filter?.search || ''}:${filter?.limit || '10'}:${filter?.page || '1'}:${filter?.sort?.name || 'id'}:${filter?.sort?.order || 'asc'}`
    const countCacheKey = `${cacheKey}:count`

    // 先查缓存
    const cachedData = await redis.get(cacheKey)
    const cachedCount = await redis.get(countCacheKey)

    if (cachedData && cachedCount) {
      const data = JSON.parse(cachedData)
      const total = parseInt(cachedCount, 10)
      const limit = filter?.limit ? parseInt(filter.limit, 10) : total
      const page = filter?.page ? parseInt(filter.page, 10) : 1
      const response = {
        data,
        meta: {
          current_page: page,
          from: limit * (page - 1) + 1,
          last_page: Math.ceil(total / limit),
          to: Math.min(limit * page, total),
          total
        }
      }
      utilLogger({ meta: { ...meta, function: 'getProjects' }, data: response })
      return response
    }

    // 缓存未命中，执行数据库查询
    const [total, data] = await prisma.$transaction([
      prisma.project.count({
        where: {
          ...(teamId && { teamId }),
          ...(filter?.search && {
            OR: [
              { name: { contains: filter.search, mode: 'insensitive' } },
              { team: { name: { contains: filter.search, mode: 'insensitive' } } }
            ]
          })
        }
      }),
      prisma.project.findMany({
        where: {
          ...(teamId && { teamId }),
          ...(filter?.search && {
            OR: [
              { name: { contains: filter.search, mode: 'insensitive' } },
              { team: { name: { contains: filter.search, mode: 'insensitive' } } }
            ]
          })
        },
        take: filter?.limit ? parseInt(filter.limit, 10) : undefined,
        skip: filter?.limit && filter?.page ? parseInt(filter.limit, 10) * (parseInt(filter.page, 10) - 1) : undefined,
        orderBy: filter?.sort ? { [filter.sort.name]: filter.sort.order } : undefined,
        include: { searchhistory: true, writerhistory: true }
      })
    ])

    // 写入缓存
    await redis.set(cacheKey, JSON.stringify(data), 'EX', SiteSettings.DATABASE.CACHE_TTL)
    await redis.set(countCacheKey, total.toString(), 'EX', SiteSettings.DATABASE.CACHE_TTL)

    const response = {
      data,
      meta: {
        current_page: filter?.page ? parseInt(filter.page, 10) : 1,
        from: filter?.limit && filter?.page ? parseInt(filter.limit, 10) * (parseInt(filter.page, 10) - 1) + 1 : 1,
        last_page: filter?.limit ? Math.ceil(total / parseInt(filter.limit, 10)) : 1,
        to: filter?.limit ? Math.min(parseInt(filter.limit, 10) * (filter?.page ? parseInt(filter.page, 10) : 1), total) : total,
        total
      }
    }
    utilLogger({ meta: { ...meta, function: 'getProjects' }, data: response })
    return response
  } catch (e: any) {
    utilLogger({ meta: { ...meta, function: 'getProjects' }, error: e })
    return {
      data: [],
      meta: {
        current_page: 1,
        from: 0,
        last_page: 1,
        to: 0,
        total: 0
      }
    }
  }
}

同时调整中间件配置，只缓存count这类无分页参数的查询：

const cacheMiddleware: Prisma.Middleware = createPrismaRedisCache({
  storage: {
    type: 'redis',
    options: {
      client: redis,
      invalidation: { referencesTTL: SiteSettings.DATABASE.CACHE_TTL }
    }
  },
  cacheTime: SiteSettings.DATABASE.CACHE_TTL,
  models: [
    {
      model: 'Project',
      includeMethods: ['count'],
      // 确保count的缓存键包含所有过滤条件
      cacheKey: (params) => JSON.stringify({
        model: params.model,
        action: params.action,
        where: params.args.where
      })
    }
  ],
  onHit: (key) => {
    const params = JSON.parse(key).params
    void logger?.info(`Cache hit for model ${params.model}`, { ...params })
  },
  onMiss: (key) => {
    const params = JSON.parse(key).params
    void logger?.info(`Cache miss for model ${params.model}`, { ...params })
  },
  onError: (error: any) => {
    void logger?.error('Cache error', {
      error: {
        message: error.message,
        stack: error.stack
      }
    })
  }
})

3. 临时规避：跳过带skip的查询缓存 #

如果不想手动改缓存逻辑，可以在中间件里直接跳过带skip的findMany查询：

const cacheMiddleware: Prisma.Middleware = async (params, next) => {
  // 跳过带skip参数的findMany查询，避免挂起
  if (params.action === 'findMany' && params.args.skip !== undefined) {
    return next(params)
  }
  // 其余缓存逻辑交给原中间件处理
  const originalMiddleware = createPrismaRedisCache({
    // 你的原配置
  })
  return originalMiddleware(params, next)
}

4. 调整Redis中间件初始化时机 #

不要在redis.on('connect')回调里初始化中间件，直接在Redis实例创建后绑定：

// 替换原来的redis.on('connect')逻辑
if (redis != null) {
  const cacheMiddleware: Prisma.Middleware = createPrismaRedisCache({
    // 你的原配置
  })
  client.$use(cacheMiddleware)
}

额外排查点 #

• 打印缓存键的完整内容，确认skip、take等参数是否被正确包含在缓存键中。
• 升级prisma-redis-middleware到最新版本，查看是否有修复分页查询的相关bug。

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