ROWNUM 到 LIMIT：分页语义差异与改造方式

1. 对应简历段落

这篇文章对应简历中“负责 Oracle 到 OceanBase 信创迁移中的分页 SQL 改造、MyBatis 方言适配、列表页性能优化和新旧结果一致性验证”等经历。

面试中如果只说“把 ROWNUM 改成 LIMIT”，会显得很浅。资深 Java 面试官真正关心的是：你是否理解 Oracle ROWNUM 的生成时机，是否知道 ORDER BY 和分页截断的先后关系，是否处理了深分页性能问题，是否改造了框架层分页插件，以及是否验证了翻页结果不会重复、遗漏、乱序。

推荐把这段经历讲成一个完整闭环：我先扫描 Mapper 中所有 ROWNUM 分页 SQL，按“简单列表、嵌套排序、报表聚合、动态排序、深分页”分层；低风险 SQL 由分页拦截器统一生成 OceanBase MySQL 模式的 LIMIT；高风险 SQL 手工改造并补充稳定排序键；最后用新旧库双跑工具对同一查询条件下的总数、每页主键序列和边界页结果做对比。

2. 业务背景

老系统以 Oracle 为核心数据库，Java 应用层主要使用 Spring、MyBatis 和自研分页组件。大量客户列表、保单列表、活动名单、销售人员绩效列表、报表明细页都采用 Oracle 经典分页写法：

SELECT *
FROM (
    SELECT t.*, ROWNUM rn
    FROM (
        SELECT *
        FROM policy_order
        WHERE status = :status
        ORDER BY create_time DESC
    ) t
    WHERE ROWNUM <= :endRow
)
WHERE rn > :startRow;

迁移到 OceanBase MySQL 模式后，自然会想到改成：

SELECT *
FROM policy_order
WHERE status = :status
ORDER BY create_time DESC
LIMIT :offset, :pageSize;

但分页迁移的难点不在语法，而在语义和性能。很多历史 SQL 其实不是标准分页写法，有的先 ROWNUM 再 ORDER BY，有的动态排序字段来自前端，有的外层还有分组汇总，有的查询条件包含函数导致索引失效。迁移时如果机械替换，很容易出现“第一页看似正常，第二页开始数据错乱”的问题。

业务上，分页列表往往是高频入口。一个客户经理每天可能反复打开客户列表、搜索客户、翻页查看保单；运营人员会按时间、机构、产品、状态筛选活动名单；管理人员会打开业绩报表明细。如果分页结果不稳定，会直接影响业务判断。如果分页 SQL 从 Oracle 迁移后变慢，还会拖垮连接池和数据库租户资源。

因此分页改造必须同时解决三件事：结果一致、排序稳定、性能可控。

3. 核心原理

Oracle 的 ROWNUM 是伪列，它不是表中真实存在的字段，而是在结果行被返回时按顺序生成。关键点是：ROWNUM 的赋值发生在 SQL 执行过程中的行返回阶段，通常早于最外层 ORDER BY。

错误写法如下：

SELECT *
FROM policy_order
WHERE ROWNUM <= 10
ORDER BY create_time DESC;

这段 SQL 的语义不是“按创建时间倒序取前 10 条”，而是“先取满足条件的任意 10 条，再对这 10 条排序”。在数据量小、执行计划刚好按索引返回时，它可能看起来没问题；一旦执行计划变化、数据分布变化，结果就会错。

正确的 Oracle 写法通常是把排序放进内层：

SELECT *
FROM (
    SELECT t.*, ROWNUM rn
    FROM (
        SELECT *
        FROM policy_order
        WHERE status = 'PAID'
        ORDER BY create_time DESC, id DESC
    ) t
    WHERE ROWNUM <= 20
)
WHERE rn > 0;

LIMIT 的语义更直接，ORDER BY 完成后根据 offset 和 size 截取结果：

SELECT *
FROM policy_order
WHERE status = 'PAID'
ORDER BY create_time DESC, id DESC
LIMIT 0, 20;

但 LIMIT 也有两个常见陷阱。

第一，排序必须稳定。如果只按 create_time DESC 排序，而同一秒内有很多订单，那么不同数据库、不同执行计划、不同分页请求之间，相同时间的数据内部顺序可能不一致。第一页最后一条和第二页第一条之间可能重复或遗漏。改造时要补充唯一键作为尾部排序字段：

ORDER BY create_time DESC, id DESC

第二，深分页代价高。LIMIT 100000, 20 通常意味着数据库要扫描或排序前 100020 行，然后丢弃前 100000 行。对后台报表或导出场景，这类 SQL 很容易成为慢 SQL。

4. 项目落地

项目中我会先做分页 SQL 盘点。扫描范围包括 Mapper XML、注解 SQL、自研 DAO、报表模板、批处理脚本和存储过程迁移后的 Java SQL。扫描关键词不只包括 ROWNUM，还包括 rn、row_number()、pageSize、startRow、endRow、offset、limit、ORDER BY ${} 等。

盘点后按风险分层。

低风险：标准三层嵌套 ROWNUM，内层有明确 ORDER BY，排序字段稳定，查询来自普通列表页。可以通过统一分页组件改造。

中风险：分页 SQL 中有动态排序、函数条件、复杂 join、聚合、子查询。需要人工检查索引和排序语义。

高风险：深分页导出、报表明细、跨机构大范围查询、排序字段不唯一、前端可传入任意排序字段。这类 SQL 需要结合业务重新设计。

在应用层，优先改造 MyBatis 分页组件，而不是让每个 Mapper 自己拼接分页。常见做法是在分页对象里保留 pageNo、pageSize、offset、orderBy、dialect 等信息，由拦截器根据数据库方言生成目标 SQL。Oracle 走 ROWNUM 包裹，OceanBase MySQL 模式走 LIMIT。

同时要收敛动态排序字段。历史系统里常见这种写法：

ORDER BY ${orderBy}

迁移时不能继续让前端原样传字段名，否则既有 SQL 注入风险，也会让执行计划不可控。更稳的方式是枚举白名单：

Map<String, String> orderByMapping = Map.of(
    "createTime", "t.create_time DESC, t.id DESC",
    "premium", "t.premium DESC, t.id DESC",
    "customerName", "t.customer_name ASC, t.id ASC"
);

然后 Mapper 中只接收后端转换后的安全片段。

对于深分页，我会区分“用户翻页”和“批量导出”。用户界面通常不会真的翻到几千页，可以限制最大页码，并用组合索引支撑前几页查询。导出场景则不适合用 offset 深分页，应该改成游标式分页或按主键分片扫描。

5. 示例 SQL 或流程

标准分页改造前：

SELECT *
FROM (
    SELECT temp.*, ROWNUM rn
    FROM (
        SELECT p.policy_no,
               p.customer_id,
               p.premium,
               p.create_time
        FROM policy_order p
        WHERE p.org_id = :orgId
          AND p.status = :status
        ORDER BY p.create_time DESC
    ) temp
    WHERE ROWNUM <= :endRow
)
WHERE rn > :startRow;

改造后：

SELECT p.policy_no,
       p.customer_id,
       p.premium,
       p.create_time
FROM policy_order p
WHERE p.org_id = :orgId
  AND p.status = :status
ORDER BY p.create_time DESC, p.policy_no DESC
LIMIT :offset, :pageSize;

配套索引：

CREATE INDEX idx_policy_org_status_time_no
ON policy_order(org_id, status, create_time, policy_no);

深分页改造前：

SELECT *
FROM policy_order
WHERE org_id = :orgId
ORDER BY create_time DESC, policy_no DESC
LIMIT 100000, 100;

游标式分页改造后：

SELECT *
FROM policy_order
WHERE org_id = :orgId
  AND (
        create_time < :lastCreateTime
        OR (create_time = :lastCreateTime AND policy_no < :lastPolicyNo)
      )
ORDER BY create_time DESC, policy_no DESC
LIMIT :pageSize;

对比验证流程可以这样设计：

1. 抽取分页 SQL、入参、排序字段、页码集合。
2. 在 Oracle 执行原 SQL，记录每页主键列表和总条数。
3. 在 OceanBase 执行改造后 SQL，记录同样结果。
4. 比较 count、主键顺序、首尾边界、空字段、金额字段。
5. 对不一致 SQL 标记原因：排序不稳定、空字符串差异、函数差异、数据同步差异或 SQL 改写错误。

6. 常见坑

第一个坑是忽略排序稳定性。很多人以为有 ORDER BY create_time 就够了，但时间字段不是唯一键。生产高并发写入时，同一秒甚至同一毫秒有大量记录，如果不追加主键排序，分页会出现重复和遗漏。

第二个坑是把所有分页都改成 LIMIT offset, size。对浅分页可以这样做，对导出、批处理和后台扫描则要改成 seek pagination、主键游标或分片查询。

第三个坑是 count SQL 生成错误。分页组件通常还会生成 SELECT COUNT(*)，如果原 SQL 有 GROUP BY、DISTINCT、UNION、窗口函数，简单去掉 ORDER BY 再包 count 可能得到错误总数。

第四个坑是动态排序导致索引失效。用户选择按保费、时间、姓名、机构等不同字段排序时，不可能每种组合都建索引。要按高频场景优化，低频场景接受较慢或转异步导出。

第五个坑是 MyBatis 参数顺序错乱。把 Oracle 外层 startRow/endRow 改成 MySQL offset/pageSize 时，如果参数绑定仍按旧顺序传，SQL 能执行但结果页码错。

第六个坑是分页插件重复生效。有些 SQL 已经手写 LIMIT，拦截器又追加一次 LIMIT，导致语法错误或结果异常。需要在分页对象和 SQL 标记上做幂等控制。

7. 面试追问

面试官可能问：ROWNUM 和 LIMIT 最大的语义差异是什么？

重点回答生成时机和排序关系。ROWNUM 是 Oracle 行返回过程中的伪列，如果排序没有放在内层子查询，可能先截断再排序；LIMIT 通常表达在排序后截取，但仍要保证排序字段唯一稳定。

面试官可能问：为什么分页要追加主键排序？

因为业务排序字段通常不唯一。如果只按时间或金额排序，相同值之间的顺序不确定，翻页时可能重复或遗漏。追加主键可以形成全序关系，让每次分页边界稳定。

面试官可能问：深分页怎么优化？

不要只说“加索引”。加索引能减少排序成本，但 LIMIT 100000, 20 仍要跳过大量记录。更好的方式是游标分页、按主键范围分片、限制最大可翻页数，或者把大范围导出改成异步任务。

面试官可能问：你如何验证分页改造没有改变结果？

可以回答：我会固定查询条件和排序字段，分别在 Oracle 与 OceanBase 执行首页、中间页、尾页和边界页，比较总数、主键序列、每页首尾记录、空字段和金额日期字段。对高频 SQL 纳入自动化双跑对比。

8. 推荐回答

可以这样组织回答：

“分页改造不是简单把 ROWNUM 替换成 LIMIT。我先理解原 SQL 的分页语义，重点检查 ORDER BY 是否在 ROWNUM 内层，因为 Oracle 如果写错层级会先截断再排序。迁移到 OceanBase MySQL 模式后，我把标准分页统一收敛到 MyBatis 分页组件生成 LIMIT offset, size，并要求所有分页排序都追加唯一键，避免翻页重复和遗漏。对于深分页和导出类 SQL，我没有继续使用大 offset，而是改成基于 create_time + id 或主键范围的游标分页。上线前通过新旧库双跑工具比较 count、每页主键顺序和边界记录，确保语义一致；上线后再结合慢 SQL 和执行计划补索引或调整查询方式。”

这个回答能体现三个能力：理解数据库语义、能做框架级收敛、能从性能和验证角度闭环。

9. 延伸学习路线

第一，系统学习 Oracle ROWNUM、ROW_NUMBER()、FETCH FIRST 的差异，理解伪列、窗口函数和标准 SQL 分页。

第二，学习 MySQL/OceanBase 的 LIMIT 执行代价，重点看 offset 扫描、filesort、覆盖索引和回表。

第三，掌握 seek pagination。能熟练写出基于 (sort_key, id) 的下一页查询，并理解为什么它适合无限滚动、导出和批处理。

第四，阅读 MyBatis 插件机制，理解 StatementHandler、BoundSql、参数映射和分页插件如何改写 SQL。

第五，建立分页 SQL 验证方法。不要只看页面是否能打开，要对比总数、主键序列、边界页和慢 SQL 指标。