Oracle 分页这个问题我在刚转过来做 Oracle 的时候被折磨得不轻。那时候从 MySQL 过来的人脑子里全是LIMIT ? OFFSET ?到了 Oracle 发现根本不认这套官方文档翻半天也没找到一个跟 MySQL 一模一样的用法。后来我才搞清楚Oracle 之所以分页 SQL 写得绕根源就在它那个独特的ROWNUM机制上。这篇就把我在实际项目里用到的几种 Oracle 分页写法、优化思路、还有 MyBatis-Plus 分页失效这些坑一次性讲透。1. 为什么 Oracle 分页这么绕先搞懂 ROWNUM 再写 SQL1.1 ROWNUM 的本质取一行编一个号很多第一次接触 Oracle 分页的人最容易踩的坑就是直接写WHERE ROWNUM 100然后惊讶地发现一条数据都查不出来。这不是 Bug而是 ROWNUM 的分配时机问题。ROWNUM 是 Oracle 在查询结果返回之前给每一行赋予的序号。关键在于这个序号是取一行编一个号不是先取完所有行再统一编号。当执行WHERE ROWNUM 100时Oracle 取出第一行给它编上 1发现 1 100 不成立直接丢弃再取第二行编上 1又不成立又丢弃。就这样所有行都被过滤掉了。所以记住一个结论ROWNUM 只支持这种从 1 开始的条件不支持 N和BETWEEN直接作为分页条件。正是因为这个限制Oracle 分页 SQL 才必须采用先取范围再截断再套外层的嵌套写法。1.2 理解执行顺序比死记模板更重要Oracle 的 SQL 执行顺序大致是FROM → WHERE → GROUP BY → HAVING → SELECT此时分配 ROWNUM → ORDER BY。这里有最关键的一点ORDER BY在 ROWNUM 分配之后才执行。这带来一个经典问题如果你在子查询里直接写WHERE ROWNUM 10 ORDER BY create_time DESC得到的结果并不是按时间排序后的前 10 条而是前 10 条数据再按时间排个序。这也是新手写 Oracle 分页最常见的错误。正确的思路必须是先让排序发生再分配 ROWNUM。所以标准的 Oracle 分页模板把排序放在最内层ROWNUM 限制放在外层子查询最终再包一层做起始位置的过滤。1.3 三层嵌套的模板是怎么来的Oracle 分页经典写法是三层嵌套SELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT * FROM emp ORDER BY empno ) t WHERE ROWNUM 20 ) WHERE rn 10;拆开看就很清晰了最内层子查询t负责排序把业务排序逻辑先做掉中层子查询此时执行到WHERE ROWNUM 20Oracle 会取到前 20 行并分配连续的 ROWNUM 序号这层干的是截断的活最外层通过WHERE rn 10过滤掉前 10 行得到第 11 到 20 条。这个模板解决了两件事一是绕开了ROWNUM不能直接大于某值的问题二是保证了排序逻辑先于行号分配执行。理解了这套逻辑你再看网上各种分页写法就能一眼判断哪些是对的、哪些是有问题的。2. 三种主流 Oracle 分页写法与适用场景2.1 经典 ROWNUM 嵌套全版本通用上面那套三层嵌套就是兼容性最好的写法。不管你是 10g、11g 还是 19c只要能跑 SQL 就能用。实际项目中我一般会把页码和每页大小抽成变量写成这样SELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT * FROM emp ORDER BY empno ) t WHERE ROWNUM #{pageNum} * #{pageSize} ) WHERE rn (#{pageNum} - 1) * #{pageSize};注意这里中层子查询的ROWNUM pageNum * pageSize是当前页的截止行号外层再用起始行号做过滤。比如第 2 页、每页 10 条中层先取前 20 条外层去掉前 10 条剩下 11 到 20 条。这套写法有个细节要留意内层排序字段一定要有唯一性。如果排序字段大量重复比如按status排Oracle 每次执行时相同排序值的行顺序可能不稳定分页就会出现上一页最后一条和下一页第一条重复或者丢数据的情况。稳妥的做法是在排序字段末尾追加主键比如ORDER BY create_time DESC, id DESC。2.2 ROW_NUMBER() 窗口函数排序稳定的另一种选择Oracle 9i 以后支持分析函数分页可以换个思路SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY create_time DESC, id DESC) rn FROM emp t ) WHERE rn BETWEEN 11 AND 20;这种写法的优势是ROW_NUMBER()在同一个查询里就能完成排序和编号代码语义更清晰。它跟 ROWNUM 最大的区别是ROW_NUMBER()是分析函数在排序完成后统一赋号因此可以直接用BETWEEN取中间任意区间。但这套写法在超大数据量下性能不一定比经典 ROWNUM 写法好因为ROW_NUMBER()会把全量结果都排完再编号而经典 ROWNUM 写法在中层就截断了CBO 有机会做更多优化。我的建议是数据量小、SQL 逻辑复杂的场景用 ROW_NUMBER() 更清晰数据量大、追求性能的场景用经典 ROWNUM 嵌套。另外说一句ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)这种带分组的分页我平时不大用除非业务明确需要每个分组分别取前 N 条否则分区反而会把分页语义搞复杂。2.3 OFFSET FETCH12c 以后的新选择如果你的数据库版本是 12c 及以上直接用 ANSI 标准的OFFSET FETCHSELECT * FROM emp ORDER BY create_time DESC, id DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;OFFSET 10表示跳过前 10 条FETCH NEXT 10 ROWS ONLY表示取后面 10 条。这套语法跟 MySQL 的LIMIT 10, 10语义非常接近可读性最好。而且 Oracle 12c 之后对OFFSET FETCH的优化器做了专门支持在普通翻页场景下性能并不逊色于经典 ROWNUM 写法。但有几个坑要提醒一下。一是OFFSET FETCH必须搭配ORDER BY不然结果集顺序不确定二是 12c 以下的版本完全不支持这套语法如果系统里还挂着 11g 的实例DBA 给你权限也白搭写了直接报ORA-00933三是这种写法同样存在深分页问题翻到几百页之后一样会慢它只是写起来方便不是性能银弹。2.4 三种写法究竟怎么选我整理了一份选型表照着选基本不会出大错。写法版本要求SQL 复杂度深分页性能适用场景ROWNUM 嵌套所有版本较高较好生产环境默认首选特别是大表ROW_NUMBER()9i较低一般逻辑复杂、分页区间不固定的小表OFFSET FETCH12c最低一般新项目、内部管理系统、版本允许在我维护的老系统里11g 的业务我几乎全用经典 ROWNUM 写法新项目如果是 19c我反而会优先用OFFSET FETCH不是为了炫技纯粹是代码更好维护后来接手的同事不用再背着三层嵌套的 SQL 一遍遍问这每层是干嘛的。3. 分页性能优化的核心怎么跳出越翻越慢的魔咒3.1 为什么翻到后面SQL 越来越慢不管用上面哪种写法只要翻页页数一深数据库都会变慢。很多刚接触 Oracle 的同学跑一条深分页 SQL看执行计划发现已经命中索引了但还是慢得离谱然后就开始怀疑是不是索引写错了。其实原理很简单。数据库分页是物理位置扫描不是逻辑跳转。你要取第 100000 到 100010 条数据库也必须从那排好序的结果集的第一条开始数一路数到 100000 条以后才能拿到你要的那 10 条。前 100000 行数据全部都要经过排序、比较、丢弃的过程这部分开销一分不少。所以深分页慢是必然的不是 SQL 写错了。这种情况在OFFSET FETCH和 ROWNUM 嵌套里都存在因为这两种方式本质都是偏移分页。3.2 用上一页最后一条做条件键集分页深分页优化最有效的手段是用上一页返回的最后一条记录的排序字段值来做下一页的起始条件。这种方案在业界叫键集分页Keyset Pagination也叫 seek 分页。举个例子你按create_time DESC, id DESC排序每页 10 条。上一页最后一条记录的create_time 2024-06-01 12:30:00id 10086那么下一页的 SQL 应该写成SELECT * FROM emp WHERE (create_time, id) (2024-06-01 12:30:00, 10086) ORDER BY create_time DESC, id DESC FETCH NEXT 10 ROWS ONLY;或者写成等价的元组比较形式Oracle 11g 里也可以用两个独立条件拼出来WHERE create_time 2024-06-01 12:30:00 OR (create_time 2024-06-01 12:30:00 AND id 10086)这种写法为什么快因为它把数 10 万行再丢弃 99990 行变成了直接通过索引定位到 10 万行之后的那个位置然后继续往后读 10 行。索引一跳就到位了扫描量从 O(N) 降到了接近 O(页码大小)。代价也很明确你不能直接跳页只能一页一页往后翻。所以适合的场景是瀑布流加载列表无限滚动用户极少跳页这类业务。如果你的业务强制要求用户点第 800 页那没辙只能承受深分页开销或者提前把总数和汇总数据算好缓存起来。3.3 延迟关联先取主键再回表还有一种我经常搭配使用的优化技巧叫延迟关联思路是避免在排序大字段和多余列上浪费 IO。比如表里有一大堆 TEXT/CLOB 字段你要是直接SELECT *去分页排序数据库得把这些大字段全部捞出来参与排序IO 和临时表空间消耗都非常大。改成先只查主键和排序字段SELECT * FROM emp e INNER JOIN ( SELECT id FROM emp ORDER BY create_time DESC, id DESC OFFSET 100000 ROWS FETCH NEXT 10 ROWS ONLY ) t ON e.id t.id ORDER BY e.create_time DESC, e.id DESC;内层查询只需要访问索引就能完成排序和偏移数据量小很多外层再拿 10 个主键回表取完整数据。这样大字段的读取量从 10 万行压缩到了 10 行性能提升非常明显。这个技巧跟非分页缓冲池占用很高这类内存问题是同一套治理思路用更少的逻辑读完成同样的业务。我在项目里排查慢 SQL 时遇到分页相关的高逻辑读 SQL第一反应就是检查是不是SELECT *带着一堆不用的列去排序。3.4 分页总数(count)的优化分页 SQL 里还有一个容易被忽略的性能炸弹就是SELECT COUNT(*)。很多框架自动生成的 count 语句会跟着一大堆条件子查询在大表上 count 一次可能就是秒级。如果业务对分页总数要求不高可以考虑用近似值替代精确值比如总条数超过 1000 时显示 1000。这是我做列表页常用的策略避免每次都跑一次精确 count。另外要留意 count 执行的时机最好只在第一页加载时 count 一次然后缓存一小段时间而不是每次翻页都重新 count。4. 从 SQL 到框架MyBatis-Plus 分页失效的那些坑4.1 分页插件为什么没生效Java 后端用 MyBatis-Plus 配 Oracle 分页我遇到过太多明明配了分页插件SQL 却把全表数据都查出来了的情况。Point 基本集中在几个地方。第一分页插件没被注册到 SqlSessionFactory。如果你自定义了MybatisSqlSessionFactoryBean或者用多数据源框架很容易出现新创建的 sessionFactory 并没有把PaginationInterceptor/MybatisPlusInterceptor装进去。这种情况表现就是调用Page参数确实传了但最终执行时 SQL 没有被拦截改写。第二新版 API 变了。MyBatis-Plus 3.4.0 之前用的是PaginationInterceptor3.4.0 之后换成了MybatisPlusInterceptor里面套PaginationInnerInterceptor。很多老博客还在抄旧代码版本不对直接 NoSuchMethodError 或者插件静默失效。正确的配置方式大概是Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor new MybatisPlusInterceptor(); PaginationInnerInterceptor paginationInterceptor new PaginationInnerInterceptor(DbType.ORACLE); paginationInterceptor.setOverflow(false); paginationInterceptor.setMaxLimit(500L); interceptor.addInnerInterceptor(paginationInterceptor); return interceptor; }这里特别注意DbType.ORACLE一定要按实际数据库版本设置填错了 MySQL 方言去生成 Oracle SQL一样会翻车。第三COUNT 查询和主查询拼接了FOR UPDATE。MyBatis-Plus 分页插件遇到FOR UPDATE会做特殊处理但如果 SQL 写得太复杂比如包含自定义 JOIN、UNION插件生成的 count 语句就可能不准甚至报错。这时候我一般会手动提供一个 count SQL 覆盖它的默认行为。4.2 分页插件在 Oracle 上到底做了什么MyBatis-Plus 在 Oracle 方言下生成分页 SQL本质就是帮你包一层 ROWNUM 子查询。原理参考我之前写的那套三层嵌套框架只是在 SQL 执行前用拦截器把原 SQL 包成了SELECT * FROM ( SELECT TMP.*, ROWNUM ROWNUM_ FROM (原SQL) TMP WHERE ROWNUM ? ) WHERE ROWNUM_ ?理解了这个原理你排查分页插件生成 SQL 慢的问题就会有一个很直观的方向先看看框架包出来的 SQL 是否让 Oracle 走了全表排序再对照原 SQL 的执行计划分析哪一层是瓶颈。4.3 分页插件的性能成本临时表和 count 双开销分页插件在 Oracle 上还有个隐藏成本就是每页查询可能产生临时表排序特别是当原 SQL 里带着复杂关联时。我见过一个报表列表接口主查询本身就要关联五张表分页插件每次执行时 Oracle 都得生成一个巨大的中间结果集再排序分页接口响应直接飙到 5 秒以上。这种场景下框架生成的 count 和分页 SQL 都很笨重。我的处理办法是放弃自动分页手写针对性的分页 SQL。把五张表的关联结果先做一次物化或者把核心业务表拆出去先分页再关联效果立竿见影。这个思路其实就是我上面说的延迟关联在框架层的变体。4.4 分页参数的安全问题分页参数一般是pageNum和pageSize很多同学直接用#{pageNum}传参这个没问题MyBatis 的预编译机制能防注入。但就怕有人图省事把排序字段拼成${orderBy}动态拼接进 SQL这就留下了一个注入点。我在代码审查时碰到过把pageSize直接拼进 SQL 的情况服务端没做大小限制最后被攻击者传了个pageSize99999999把整表拖走。分页参数一定要做兜底pageNum 不能小于 1pageSize 要设上限排序字段要白名单校验。这不是 SQL 写法问题是安全意识问题。5. 常见问题与排查技巧实录5.1 分页结果出现重复或丢失症状翻页后和上一页有重复数据或者某些数据一直翻不到。原因九成是排序字段不唯一。MySQL 里有类似问题Oracle 也一样。处理方案是给ORDER BY追加一个唯一字段常见做法是加主键id DESC收尾。还有一种情况是翻页期间正好有数据插入或删除比如用户翻到第 3 页时第 1 页插入了一条新数据所有行的整体位置后移第 3 页自然就会跟第 2 页有重复。这个属于翻页期间数据变化导致的业务层问题不是 SQL 的问题通常通过键集分页或者快照读思路来规避。5.2 ROWNUM 排序错乱我见过有人写成这样SELECT * FROM ( SELECT ROWNUM rn, e.* FROM emp e ) WHERE rn BETWEEN 11 AND 20 ORDER BY create_time DESC;看起来有排序其实内层SELECT ROWNUM, e.* FROM emp没有排过序ROWNUM 是按物理存储顺序分配的最后一层再排序只是把取到的 10 条结果排了一下整个分页结果完全乱掉。这种 SQL 我建议直接重写为经典三层嵌套模板。判断排序是否生效最直观的方法是打印执行计划看有没有SORT ORDER BY出现在 ROWNUM 分配之前。5.3 深分页导致临时表空间暴涨如果分页 SQL 排序的数据量太大Oracle 会使用临时表空间做排序溢出。有一次线上告警临时表空间使用率接近 100%排查下来就是某个报表接口被定时任务调用一次性往后翻了几百页每次都要对百万级数据排序。处理这类问题我一般分三步第一步限制单页大小和最大页码第二步给排序字段建组合索引让排序尽量走索引避免 sort 溢出第三步实在压不下去就改成键集分页让每次查询都只访问索引的一小段。这套组合拳打下来临时表空间压力能降一大截。5.4 分页慢 SQL 快速排查清单我把日常排查分页慢 SQL 的套路整理成了一张清单遇到问题直接对照着过一遍执行计划里有没有SORT ORDER BY如果有确认排序字段是否有索引支撑。ROWNUM分配的位置对不对重点看 ROWNUM 是在排序前还是排序后被截断。分页 SQL 是不是SELECT *带着大字段如果是试一下先分页主键再回表。count 查询是不是在反复执行是否缓存过多页总数是否用了框架自动分页而主 SQL 本身是超复杂 JOIN如果是考虑手写分页。看到TABLE ACCESS FULL了吗如果全表扫描还伴随排序基本是必慢的。这套清单我在团队里分享过很多次也能用来给新人上手 Oracle 分页优化时当检查手册用。6. 结合场景的取舍心得别让分页写法绑架了业务Oracle 分页归根结底是用排序键换查询范围的问题不同的业务形态适合不同的方案。比如内部管理系统数据量也就几万条用最稳妥的三层嵌套就够C 端用户中心的订单列表数据到了百万级我强烈建议一步到位用键集分页别等线上报警了再改。还有一个小建议在代码里尽量把分页 SQL 封装成统一的 DAO 层方法不要每个接口各写各的。因为将来换数据库或者升级版本的时候你会发现全部要改的 SQL 集中在几个文件里远比满项目搜分页关键字要省事。最后再分享一个小经验用 Oracle 分页别只看第一页快不快一定要拿最后一页最坏情况测。很多分页 SQL 第一页秒回翻到第 1000 页直接超时问题不是这时候才出现的而是一开始设计时就埋下了。我们在压测时专门设计了一条翻到最深页的用例专门锤分页性能比随机翻页测试有效得多。