优化复杂 Sequelize 查询：搜索、排序与分页的正确实现

Source: Dev.to

问题

• 在关联表之间搜索（例如，customer + product）
• 对嵌套关系进行排序
• 由于 JOIN 重复导致分页失效
• findAndCountAll 返回的计数不正确

传统做法（大多数人会怎么做）

常见的解决方案包括：

• subQuery: false
• separate: true
• 通过 sequelize.literal 编写原始 SQL

为什么这有问题

• SQL 注入风险（字符串插值）
• 性能问题（使用 separate 会产生多次查询）
• 难以维护
• 不具备数据库无关性

改进实现

// modern-sequelize-query
const { offset = 0, limit = 10 } = req;

// Base query
// 🔍 SAFE SEARCH
const safeSearch = `%${search.trim()}%`;
queryOptions.where[Op.or] = [
  // add your search conditions here, using Op.like, fn, col, etc.
];

// 📊 SORTING (no raw injection)
// example:
switch (sortField) {
  case 'category':
    // add sorting logic
    break;
  default:
    // default sorting
    break;
}

// 🚀 Execute the query
const result = await Model.findAndCountAll({
  ...queryOptions,
  offset,
  limit,
  distinct: true, // ensures correct counts
});

关键改进

• 使用 required 的包含（scoped includes）进行关联
• 安全的替换方式（不使用字符串插值）
• 使用 distinct 并正确分组
• 仅在必要时使用子查询
• 对大数据集可选的基于游标的分页

为什么这样更好

✅ 没有 SQL 注入风险

将原始的 sequelize.literal(\… ${searchValue}`)替换为 Sequelize 原生操作符（Op.like、fn、col`）。

✅ 计数准确

使用 distinct: true（或相应列）避免因 JOIN 导致的重复计数。

✅ 更佳性能

• 除非真的需要，否则不使用 separate: true。
• 在单次查询中执行优化后的 JOIN。

✅ 可维护且跨数据库

所有逻辑都在 Sequelize API 内完成，使代码更易调试、扩展，并可在不同数据库引擎之间迁移。

何时使用 separate: true

• 需要对非常大的子数据集进行嵌套分页。
• 想要懒加载关联数据。

进阶：更好的做法（游标分页）

在大表上使用偏移分页会导致性能下降。可以考虑切换为：

• 基于游标的分页（例如，使用 createdAt 或 id 作为游标）
• 索引分页（利用已建索引的列进行快速查找）

最佳实践

• 尽可能避免使用原始 SQL。
• 使用 fn、col 和 where 实现安全查询。
• 为分页正确使用 distinct。
• 仅在必要时使用 separate: true。

Sequelize 功能强大——但前提是正确使用。

你在生产环境中遇到过 Sequelize 性能问题吗？在下方分享你的经验吧！