如何在Nest.js服务器上缓存客户端请求间的SQL查询结果？

核心思路是后端缓存大列查询结果，前端分批次请求分片数据，既避免重复查询数据库耗费时间，又防止一次性返回超大数据导致浏览器崩溃。

1. 后端实现缓存+分片返回 #

步骤1：启用Nest.js缓存 #

优先用Redis做分布式缓存（适配多实例部署场景），单实例部署可直接用内存缓存：

// app.module.ts
import { Module, CacheModule } from '@nestjs/common';
import * as redisStore from 'cache-manager-redis-store';

@Module({
  imports: [
    CacheModule.register({
      store: redisStore,
      host: 'localhost',
      port: 6379,
      ttl: 3600, // 缓存有效期1小时，可按需调整
    }),
    // 其他业务模块
  ],
})
export class AppModule {}

步骤2：修改查询逻辑，缓存结果并分片 #

// correlations.service.ts
import { Injectable, CACHE_MANAGER, Inject } from '@nestjs/common';
import { Cache } from 'cache-manager';
import { CorrelationsRepository } from './correlations.repository';

@Injectable()
export class CorrelationsService {
  constructor(
    private readonly correlationsRepository: CorrelationsRepository,
    @Inject(CACHE_MANAGER) private cacheManager: Cache,
  ) {}

  async getPaginatedSelectList(correlationId: string, page: number, pageSize: number) {
    const cacheKey = `select_list:${correlationId}`;
    // 优先从缓存获取数据
    let rawSelectList = await this.cacheManager.get<string>(cacheKey);

    if (!rawSelectList) {
      // 缓存未命中时执行数据库查询
      const queryResult = await this.correlationsRepository
        .createQueryBuilder('c')
        .select('c.select_list')
        .where({ id: correlationId })
        .execute();
      rawSelectList = queryResult[0]?.select_list;
      // 将查询结果存入缓存
      await this.cacheManager.set(cacheKey, rawSelectList);
    }

    // 解析大列数据并分片（假设select_list是JSON数组格式的字符串）
    const dataArray = JSON.parse(rawSelectList);
    const startIndex = (page - 1) * pageSize;
    const endIndex = startIndex + pageSize;
    const paginatedData = dataArray.slice(startIndex, endIndex);

    return {
      data: paginatedData,
      total: dataArray.length,
      hasMore: endIndex < dataArray.length,
    };
  }
}

步骤3：添加接口路由 #

// correlations.controller.ts
import { Controller, Get, Param, Query } from '@nestjs/common';
import { CorrelationsService } from './correlations.service';

@Controller('correlations')
export class CorrelationsController {
  constructor(private readonly correlationsService: CorrelationsService) {}

  @Get(':id/select-list')
  async getSelectList(
    @Param('id') correlationId: string,
    @Query('page') page: number = 1,
    @Query('pageSize') pageSize: number = 50,
  ) {
    return this.correlationsService.getPaginatedSelectList(correlationId, page, pageSize);
  }
}

2. 前端配合分片请求 #

以React的react-infinite-scroll-component为例，每次滚动触发时请求对应页码的分片数据：

import { useState } from 'react';
import InfiniteScroll from 'react-infinite-scroll-component';

export default function InfiniteSelectList({ correlationId }) {
  const [listData, setListData] = useState([]);
  const [currentPage, setCurrentPage] = useState(1);
  const [hasMoreData, setHasMoreData] = useState(true);
  const PAGE_SIZE = 50;

  const fetchNextPage = async () => {
    const response = await fetch(`/correlations/${correlationId}/select-list?page=${currentPage}&pageSize=${PAGE_SIZE}`);
    const result = await response.json();
    
    setListData(prev => [...prev, ...result.data]);
    setHasMoreData(result.hasMore);
    setCurrentPage(prev => prev + 1);
  };

  return (
    <InfiniteScroll
      dataLength={listData.length}
      next={fetchNextPage}
      hasMore={hasMoreData}
      loader={<div>加载中...</div>}
      endMessage={<div>已加载全部数据</div>}
    >
      {listData.map((item, idx) => (
        <div key={idx}>{item}</div>
      ))}
    </InfiniteScroll>
  );
}

3. 关键注意事项 #

• 如果select_list不是JSON数组格式，需根据实际格式处理（比如按特定分隔符拆分字符串）。
• 多实例部署必须用Redis等分布式缓存，避免单实例内存缓存的一致性问题。
• 当select_list数据更新时，要主动删除对应缓存键，确保用户获取最新数据。
• 可提前在后端将大列数据拆分成固定大小的分片存入缓存，减少每次请求的解析和分片开销。

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