对带索引的id列ORDER BY批量查询时插入新行会有一致性问题吗？

当你用OFFSET & LIMIT结合ORDER BY id做批量遍历查询时，确实会出现一致性问题，具体分两种场景：

场景1：id为自增主键（新记录id更大） #

这种情况下，新插入的记录会排在结果集末尾。如果查询过程中插入新记录：

• 原本应该结束的循环（当某次查询返回空结果时）会继续执行，把新增的记录也纳入结果，导致最终数据包含了查询启动后才插入的内容，破坏了“查询开始时的全表快照”一致性。
• 极端情况下，如果插入频率高，可能会陷入无限循环（每次都有新记录插入，永远查不完）。

场景2：id非自增（允许插入id小于已有记录的情况） #

这种情况问题更严重：

• 新插入的id如果落在已查询范围和未查询范围之间，会导致后续查询的偏移量错位。比如第一次查了前1000条（id 1~1000），插入一条id=500的记录，第二次查OFFSET 1000 LIMIT 1000时，原本的id=1001会被挤到第1002位，这次查询会跳过前1000条（包含了新插入的id=500），导致原本的id=1000被重复查询，而id=1001可能被遗漏。

解决方案：改用键集分页（Keyset Pagination） #

放弃OFFSET，以上一次查询的最后一条记录的id作为下一次查询的条件，利用索引快速定位，同时避免偏移量错位问题。修改你的示例代码如下：

async function fetchDataInBatches(model, whereClause, batchSize = 1000) {
  let lastId = 0;
  let moreDataAvailable = true;
  let allData = [];
  while (moreDataAvailable) {
    const results = await model.findAll({
      where: {
        ...whereClause,
        id: { [Op.gt]: lastId } // 使用id大于上一次的最后id作为条件
      },
      limit: batchSize,
      order: [['id', 'ASC']],
    });
    if (results.length === 0) {
      moreDataAvailable = false;
      break;
    }
    allData = allData.concat(results);
    lastId = results[results.length - 1].id; // 更新最后一条记录的id
  }
  return allData;
}

为什么键集分页更可靠？ #

• 一致性保障：只会查询id大于lastId的记录，新插入的id更大的记录不会干扰已查询的结果；如果是插入id更小的记录，也不会被纳入后续查询（如果需要包含这类记录，可考虑事务快照）。
• 性能更优：OFFSET x需要数据库扫描前x条数据后再取结果，数据量越大越慢；而id > lastId可以直接利用id的索引定位，百万级数据下性能提升明显。

额外注意事项 #

如果需要严格的快照一致性（即只查询启动查询时存在的记录，完全排除后续插入的内容），可以在查询开始时开启一个只读事务，并设置事务的隔离级别为可重复读（Repeatable Read），这样整个批量查询过程中会基于事务启动时的快照读取数据，不受后续插入影响。示例代码如下：

async function fetchDataInBatches(model, whereClause, batchSize = 1000) {
  const transaction = await model.sequelize.transaction({ isolationLevel: 'REPEATABLE READ' });
  try {
    let lastId = 0;
    let moreDataAvailable = true;
    let allData = [];
    while (moreDataAvailable) {
      const results = await model.findAll({
        where: {
          ...whereClause,
          id: { [Op.gt]: lastId }
        },
        limit: batchSize,
        order: [['id', 'ASC']],
        transaction: transaction
      });
      if (results.length === 0) {
        moreDataAvailable = false;
        break;
      }
      allData = allData.concat(results);
      lastId = results[results.length - 1].id;
    }
    await transaction.commit();
    return allData;
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
}

内容的提问来源于stack exchange，提问作者Manu S Rao