如何优化Sequelize findAndCountAll的性能及计数准确性问题

问题概述 #

使用Sequelize实现分页功能时遇到两个核心问题：

• 关联模型查询的计数结果不准确
• 整体查询速度过慢
  尝试过设置separate: true、required: true等配置，但仅得到空关联数组，问题未解决。

相关代码 #

router.js #

router.post('/getall', async (req, res) => {
  try {
    const { q, page, limit, order_by, order_direction } = req.query;
    const { candidate, position, filters } = req.body

      let include = [
        {
          model: SortedLvl,
          where: {
            lvl: filters.jobLvl.name,
            months: { [Op.gte]: filters.jobMinExp.value },
          },
        },
        {
          model: SortedLastJob,
          where: { jobposition: filters.jobType.name }
        },
        {
          model: SortedSkills,
        }
      ]
      let search = {};
      let order = [];

      let filterCandidate = {}
      if (candidate) {
        if (candidate != undefined) {
          t = candidate.split(/[ ,]+/)
          let arr = new Array()
          t.map((el, index) => {
            console.log('el', el);
            if (typeof el == 'number') {
              arr.push({ first_name: { [Op.iLike]: `%` + `${el}` + `%` } }, { last_name: { [Op.iLike]: `%` + `${el}` + `%` } });
            } else {
              arr.push({ first_name: { [Op.iLike]: `%` + `%${el}%` + `%` } }, { last_name: { [Op.iLike]: `%` + `%${el}%` + `%` } });
            }
          });

          filterCandidate = {
            [Op.or]: arr
          };
        }
      }

      let filterPosition = {}
      if (position) {
        if (position != undefined) {
          filterPosition = { position: { [Op.iLike]: `%${position}%` } }
        }
      }

      if (filterCandidate.length > 0 || filterPosition.length > 0) {
        search = { where: { ...(filterCandidate || []), ...(filterPosition || []) } }
      }

      if (order_by && order_direction) {
        order.push([order_by, order_direction]);
      }

      const transform = (records) => {
        return records.map(record => {
          return {
            id: record.id,
            name: record.name,
            date: moment(record.createdAt).format('D-M-Y H:mm A')
          }
        });
      }
      const products = await paginate(Candidate, page, limit, search, order, include);

      return res.json({
        "success": true,
        "data": products
      })
    
  } catch (error) {
    console.log('Failed to fetch products', error);
    return res.status(500).send({
      "success": false,
      "message": 'Failed to fetch products'
    })
  }
});

paginate.js #

const paginate = async (model, pageSize, pageLimit, search = {}, order = [], include, transform, attributes, settings) => {
  try {
    const limit = parseInt(pageLimit, 10) || 10;
    const page = parseInt(pageSize, 10) || 1;

    let options = {
      "offset": getOffset(page, limit),
      "limit": limit,
      "distinct": true,
      "include": include,
    };

    if (Object.keys(search).length) {
      options = { ...options, ...search };
    }

    if (attributes && attributes.length) {
      options['attributes'] = attributes;
    }

    if (order && order.length) {
      options['order'] = order;
    }

    let data = await model.findAndCountAll(options);
  
    if (transform && typeof transform === 'function') {
        data = transform(data.rows);
    }

      return {
        "previousPage": getPreviousPage(page),
        "currentPage": page,
        "nextPage": getNextPage(page, limit, count),
        "total": count,
        "limit": limit,
        "data": data.rows
      }
    
  } catch (error) {
    console.log(error);
  }
}

const getOffset = (page, limit) => {
  return (page * limit) - limit;
}

const getNextPage = (page, limit, total) => {
  if ((total / limit) > page) {
    return page + 1;
  }
  return null
}

const getPreviousPage = (page) => {
  if (page <= 1) {
    return null
  }
  return page - 1;
}

module.exports = paginate;

问题分析 #

1. 计数不准确：

   • paginate.js中返回的count变量未定义，应使用data.count；
   • 关联查询时，多匹配行导致主模型重复统计，即使加distinct也可能因关联逻辑误差导致计数不准；
   • 默认关联查询会先拉取所有匹配行再分页，计数逻辑易出错。

2. 查询速度慢：

   • 关联查询未限制字段，拉取冗余数据；
   • Op.iLike未配索引，触发全表扫描；
   • 搜索词拆分逻辑冗余，生成过多OR条件；
   • offset分页在大数据量下性能极差，需跳过所有前序数据。

3. 关联数组为空：

   • required: true强制内连接，过滤掉无关联数据的主模型；
   • separate: true单独查询关联数据，若主模型无匹配关联则返回空数组，需检查关联条件和外键匹配情况。

解决方案 #

1. 修复计数准确性 #

• 修正paginate.js的计数变量错误：

  // 原返回逻辑
  return {
    "previousPage": getPreviousPage(page),
    "currentPage": page,
    "nextPage": getNextPage(page, limit, count),
    "total": count,
    "limit": limit,
    "data": data.rows
  }
  // 修改后
  return {
    "previousPage": getPreviousPage(page),
    "currentPage": page,
    "nextPage": getNextPage(page, limit, data.count),
    "total": data.count,
    "limit": limit,
    "data": transform ? transform(data.rows) : data.rows
  }
  

• 添加subQuery: false配置，先分页主模型再查询关联数据，提升计数准确性和性能：

  let options = {
    "offset": getOffset(page, limit),
    "limit": limit,
    "distinct": true,
    "subQuery": false, // 关键配置
    "include": include,
  };
  

• 明确关联查询的连接类型，避免歧义：

  let include = [
    {
      model: SortedLvl,
      required: true, // 内连接，仅返回有符合条件关联的主模型
      where: {
        lvl: filters.jobLvl.name,
        months: { [Op.gte]: filters.jobMinExp.value },
      },
      attributes: ['lvl', 'months'] // 仅查询需要的字段
    },
    {
      model: SortedLastJob,
      required: true,
      where: { jobposition: filters.jobType.name },
      attributes: ['jobposition']
    },
    {
      model: SortedSkills,
      required: false, // 左连接，保留无技能的主模型
      attributes: ['skill_name']
    }
  ]
  

2. 优化查询速度 #

• 给搜索字段（first_name、last_name、position）和关联表的过滤字段（lvl、months、jobposition）添加数据库索引，避免全表扫描；
• 简化搜索词拆分逻辑：

  let arr = [];
  const terms = candidate.split(/[ ,]+/).filter(term => term.trim()); // 过滤空字符串
  terms.forEach(term => {
    arr.push(
      { first_name: { [Op.iLike]: `%${term}%` } },
      { last_name: { [Op.iLike]: `%${term}%` } }
    );
  });
  filterCandidate = { [Op.or]: arr };
  

• 指定主模型需要的字段，减少数据传输：

  const products = await paginate(Candidate, page, limit, search, order, include, transform, ['id', 'name', 'createdAt']);
  

• 大数据量场景下改用游标分页替代offset：

  // 前端传递最后一条数据的id作为游标
  let where = {};
  if (req.query.lastId) {
    where.id = { [Op.gt]: req.query.lastId };
  }
  // 仅用limit获取下一页，无需offset
  

3. 解决关联数组为空问题 #

• 若需保留无关联数据的主模型，将required: false与separate: true配合使用：

  {
    model: SortedLvl,
    required: false,
    separate: true,
    where: { ... },
    attributes: [...]
  }
  

• 检查关联条件的参数值（如filters.jobLvl.name）是否正确，确认主模型与关联表的外键匹配。

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