Sequelize中Op.between与Op.gte/lte的查询性能差异问询

我最近优化了一个基于JavaScript、Node.js、Sequelize和MySQL的功能，把响应时间从约5秒降到了3秒，性能提升40%。最初以为是移除了不必要的表关联带来的效果，但测试后发现，核心影响因素是把查询条件里的Op.between替换成了Op.gte+Op.lte的组合——哪怕保留表关联，换操作符后性能依然有明显提升。

奇怪的是，我直接在数据库中执行两种方式生成的SQL时，它们的执行计划完全一致，性能也没区别；但只要在Sequelize代码里换回Op.between，性能就立刻回到原来的慢水平。想搞清楚：Sequelize层面，Op.between和Op.gte/Op.lte的组合真的存在性能差异吗？

原有代码 #

const startDate = moment(params.searchDate).format('YYYY-MM-DD 00:00:00')
const endDate = moment(params.searchDate).format('YYYY-MM-DD 23:59:59')

await receive.findAll({
  include: [
    {
      attributes: ['parking', 'parkingNm', 'parkingDetail', 'section', 'startDatetime', 'endDatetime', 'createdAt'],
      model: models.movement,
      separate: false,
      order: [['createdAt', 'desc']],
    },
  ],
  where: {
    [Op.or]: [
      {
        realUsetime: {
          [Op.between]: [startDate, endDate],
        },
      },
      {
        useDatetime: {
          [Op.between]: [startDate, endDate],
        },
      },
    ],
  },
})

优化后代码 #

const startDate = moment(params.searchDate).format('YYYY-MM-DD 00:00:00')
const endDate = moment(params.searchDate).format('YYYY-MM-DD 23:59:59')

await receive.findAll({
  where: {
    [Op.or]: [
      {
        realUsetime: {
          [Op.gte]: startDate,
          [Op.lte]: endDate,
        },
      },
      {
        useDatetime: {
          [Op.gte]: startDate,
          [Op.lte]: endDate,
        },
      },
    ],
  },
})

相关表结构 #

receives表 #

CREATE TABLE `receives` (
 `uid` int(11) NOT NULL AUTO_INCREMENT,
 `receive_no` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 `status` int(11) NOT NULL,
 `phone` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 `use_datetime` datetime DEFAULT NULL,
 `real_usetime` datetime DEFAULT NULL,
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 `deleted_at` datetime DEFAULT NULL,
 PRIMARY KEY (`uid`),
 UNIQUE KEY `receive_no` (`receive_no`),
 KEY `search_index` (`car_number`,`valet_type`,`phone`,`created_at`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

movements表 #

CREATE TABLE `movements` (
 `uid` int(11) NOT NULL AUTO_INCREMENT,
 `parking` int(11) DEFAULT NULL,
 `parking_detail` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `section` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 `start_datetime` datetime NOT NULL,
 `end_datetime` datetime NOT NULL,
 `receive_uid` int(11) NOT NULL,
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 `deleted_at` datetime DEFAULT NULL,
 PRIMARY KEY (`uid`),
 KEY `receive_uid` (`receive_uid`),
 CONSTRAINT `movements_ibfk_1` FOREIGN KEY (`receive_uid`) REFERENCES `receives` (`uid`) ON DELETE NO ACTION ON UPDATE CASCADE,
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

生成的SQL对比 #

原有代码生成的SQL #

SELECT
    `receive`.`uid`,
    `receive`.`receive_no` AS `receiveNo`,
    `receive`.`receiver`,
    `receive`.`status`,
    `receive`.`phone`,
    `receive`.`use_datetime` AS `useDatetime`,
    `receive`.`real_usetime` AS `realUsetime`,
    `receive`.`created_at` AS `createdAt`,
    `receive`.`updated_at` AS `updatedAt`,
    `movements`.`uid` AS `movements.uid`,
    `movements`.`parking` AS `movements.parking`,
    `movements`.`parking_detail` AS `movements.parkingDetail`,
    `movements`.`section` AS `movements.section`,
    `movements`.`start_datetime` AS `movements.startDatetime`,
    `movements`.`end_datetime` AS `movements.endDatetime`,
    `movements`.`created_at` AS `movements.createdAt`
FROM
    `receives` AS `receive`
LEFT OUTER JOIN `movements` AS `movements` ON
    `receive`.`uid` = `movements`.`receive_uid`
    AND (`movements`.`deleted_at` IS NULL)
WHERE
    (`receive`.`deleted_at` IS NULL
        AND (`receive`.`real_usetime` BETWEEN '2023-05-26 00:00:00' AND '2023-05-26 23:59:59'
            OR `receive`.`use_datetime` BETWEEN '2023-05-26 00:00:00' AND '2023-05-26 23:59:59')
        );

优化后代码生成的SQL #

SELECT
    `receive`.`uid`,
    `receive`.`receive_no` AS `receiveNo`,
    `receive`.`receiver`,
    `receive`.`status`,
    `receive`.`phone`,
    `receive`.`use_datetime` AS `useDatetime`,
    `receive`.`real_usetime` AS `realUsetime`,
    `receive`.`created_at` AS `createdAt`,
    `receive`.`updated_at` AS `updatedAt`,
    `movements`.`uid` AS `movements.uid`,
    `movements`.`parking` AS `movements.parking`,
    `movements`.`parking_detail` AS `movements.parkingDetail`,
    `movements`.`section` AS `movements.section`,
    `movements`.`start_datetime` AS `movements.startDatetime`,
    `movements`.`end_datetime` AS `movements.endDatetime`,
    `movements`.`created_at` AS `movements.createdAt`
FROM
    `receives` AS `receive`
LEFT OUTER JOIN `movements` AS `movements` ON
    `receive`.`uid` = `movements`.`receive_uid`
    AND (`movements`.`deleted_at` IS NULL)
WHERE
    (`receive`.`deleted_at` IS NULL
        AND ((`receive`.`real_usetime` >= '2023-05-26 00:00:00'
            AND `receive`.`real_usetime` < '2023-05-27 00:00:00')
        OR (`receive`.`use_datetime` >= '2023-05-26 00:00:00'
            AND `receive`.`use_datetime` < '2023-05-27 00:00:00')));

分析与结论 #

首先明确：MySQL层面，BETWEEN a AND b和>=a AND <=b是完全等价的，执行计划和性能不会有区别——这一点你直接执行SQL的测试已经验证了。所以性能差异肯定出在Sequelize的处理逻辑上，可能的原因包括：

1. 查询构建开销：Sequelize构建Op.between查询的内部逻辑比Op.gte+Op.lte更复杂，比如对数组参数的遍历、校验、AST节点生成步骤更多，在嵌套Op.or的场景下，这些额外步骤累积出了可感知的耗时。
2. 参数处理差异：Sequelize对Op.between的数组参数可能做了额外的序列化或类型转换操作，而Op.gte/Op.lte的单个参数处理更简洁，导致整体耗时增加。
3. 内部缓存策略：Sequelize的查询缓存（比如生成SQL的缓存）对两种查询结构的处理不同，Op.gte+Op.lte的查询更容易命中缓存，而Op.between每次都需要重新构建SQL。

验证建议 #

• 开启Sequelize的logging配置，对比两种查询的参数绑定和SQL生成过程，看是否有额外的处理步骤。
• 用console.time()在findAll调用前后计时，精确测量查询构建阶段的耗时，确认是构建慢还是数据库执行慢。
• 简化查询（比如去掉Op.or，只查单个字段），测试两种操作符的性能差异是否依然存在，缩小问题范围。

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