做了这么多年Oracle数据库开发和调优几乎每个项目都会遇到分页查询的需求。这东西看着简单但真踩过坑的人都知道Oracle的分页和MySQL完全是两码事——MySQL一个LIMIT搞定Oracle却要跟ROWNUM、ROW_NUMBER()或者12c以后的FETCH FIRST打交道。尤其是数据量上了百万级之后写法不一样性能差距能拉到几十倍。这篇文章我就把Oracle分页查询的三种主流方法一次性讲透从原理到SQL写法再到性能对比和避坑技巧都是我实际项目中验证过的经验希望能帮正在被Oracle分页折磨的兄弟们省点时间。1. 为什么Oracle分页查询不能直接套用MySQL的思维很多从MySQL转过来的开发上来就习惯性写LIMIT 10, 20在Oracle里直接报错。这不是语法不习惯的问题而是Oracle的SQL执行机制跟MySQL有本质区别。要想把分页写好必须先理解Oracle的数据获取方式。1.1 先搞懂ROWNUM是什么否则后面全是懵的ROWNUM是Oracle在返回查询结果时为每一行临时分配的伪列从1开始递增。关键点在于ROWNUM是在结果集生成的过程中逐行分配的不是在最终结果确定后统一编号的。这就导致了一个经典坑WHERE ROWNUM 5永远查不到任何数据。为什么因为Oracle读取第一行时给它分配ROWNUM 1然后判断1 5不成立这一行被丢弃。接着读取第二行它仍然是ROWNUM 1因为第一行被丢了判断还是不成立又被丢弃。如此循环每一行都被分配为ROWNUM 1全部被过滤掉最终结果是空集。换句话说ROWNUM只能在“小于某个值”的条件下使用不能直接做“大于”判断——因为编号是边查边发的大于条件永远等不到满足条件的那一行出现。理解了ROWNUM的分配机制就理解了为什么Oracle分页必须用子查询嵌套先通过ROWNUM给数据一个“序号”再基于这个序号做范围筛选。这不是绕弯子而是Oracle执行引擎的底层逻辑决定的。1.2 分页参数的本质你真正要算的是什么分页逻辑永远离不开两个参数页码pageNo和每页条数pageSize。在Oracle里你要算的是起始行号和结束行号起始行 (pageNo - 1) * pageSize 1结束行 pageNo * pageSize比如第3页、每页20条那就是第41行到第60行。这个换算看着简单但实际开发中很多人直接在SQL里写死数字换页就要重写SQL完全不灵活。正确的做法是使用绑定变量:pageSize和:offset让同一个SQL能复用。这里还要多提一句分页语句的排序稳定性问题。如果ORDER BY的字段不是唯一的比如只按日期排序而同一日期有多条数据那么两次查询同一页返回的数据可能不同。这不是Oracle的Bug而是排序本身就不稳定导致的。解决方案也很简单——在ORDER BY后面追加一个唯一字段作为第二排序键比如主键ID。2. 方法一ROWNUM三层嵌套法最经典8i到21c通用这套写法从Oracle 8i就开始用了一直到现在都不过时。虽然写法看着啰嗦但它不依赖任何新特性兼容性最好而且只要索引得当性能完全可以做到非常理想。2.1 正确写法和为什么必须是三层嵌套先看一个标准示例查询第3页每页10条数据按员工ID排序。SELECT * FROM ( SELECT t.*, ROWNUM AS rn FROM ( SELECT emp_id, emp_name, salary FROM emp ORDER BY emp_id ) t WHERE ROWNUM 30 ) WHERE rn 21;为什么是三层每一层都有不可替代的作用最内层负责排序生成一个有明确顺序的结果集中间层用ROWNUM 30先截断前30行也就是当前页之前的所有数据加当前页数据同时把ROWNUM作为一个字段保存为别名rn。这里必须用“小于等于”截断也是ROWNUM机制的硬性约束最外层对已编号的结果集做rn 21的过滤取出当前页的数据这套逻辑的关键洞察在于Oracle不会一次性把全部数据都取出来排序中间层的ROWNUM 30其实起到了“提前终止”的作用——一旦取满30行Oracle就不再继续扫描表了这在全表扫描的场景下能大幅减少IO开销。2.2 最常见的错误写法你中招过几个我见过无数初学者甚至老开发写错ROWNUM分页总结下来主要有这么几类-- 错误1直接把ROWNUM当普通字段过滤 SELECT * FROM emp WHERE ROWNUM BETWEEN 21 AND 30; -- 错误2在排序后直接加ROWNUM条件 SELECT * FROM emp ORDER BY emp_id WHERE ROWNUM 30; -- 错误3ROWNUM只包了一层排序作用域不对 SELECT * FROM ( SELECT * FROM emp WHERE ROWNUM 30 ) WHERE ROWNUM 21;错误1的问题前面已经分析过了ROWNUM不能做大于判断。错误2是语法层面就不可能通过WHERE必须写在ORDER BY之前。错误3最隐蔽——内层先截断前30行但截断的顺序是没有排序的是表扫描的自然顺序然后再排序取出来的完全是错的数据。正确的做法永远只有一个先排好序再编号最后过滤。跟炒菜一个道理你得先把菜洗干净切好最内层排序再下锅翻炒中间层编号截断最后装盘上桌外层过滤范围。顺序一旦错了味道就全变了。2.3 给ROWNUM分页加索引性能能提升百倍ROWNUM分页在百万级数据下的性能瓶颈主要出现在最内层的ORDER BY。如果没有合适的索引Oracle必须做全表排序这个代价是灾难性的。比如上面那个例子如果SQL是按emp_id排序那就在emp_id上建一个索引CREATE INDEX idx_emp_id ON emp(emp_id);有了索引之后最内层就不再是全表扫描排序而是直接走索引顺序扫描配合中间层的ROWNUM 30提前终止Oracle只需要读索引的前30个条目然后回表IO消耗肉眼可见地降下来。这里还有一个经验之谈排序字段和过滤字段尽量设计成同一个索引。如果SQL里有WHERE dept_id :deptId ORDER BY emp_id那建联合索引(dept_id, emp_id)的收益比单独建两个索引高得多。因为Oracle可以在索引内部就完成过滤和排序不用回表后再sort。3. 方法二ROW_NUMBER()分析函数法逻辑最清晰团队协作首选如果你觉得ROWNUM三层嵌套阅读起来太绕那ROW_NUMBER()分析函数方案更符合现代SQL的思维方式。它把“编号”这个动作从隐式变成显式逻辑一目了然在复杂业务场景下可维护性极高。3.1 标准SQL写法一眼就能看懂分页逻辑SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (ORDER BY e.emp_id) AS rn FROM emp e ) WHERE rn BETWEEN 21 AND 30;是不是清爽多了内层通过ROW_NUMBER() OVER (ORDER BY e.emp_id)显式地生成一个行号序列外层直接做范围过滤。这个方案的思路更接近关系模型的“声明式”风格你告诉数据库你想要什么而不必关心它是怎么做到的。如果你想在分页的同时做过滤直接把WHERE条件放在内层即可SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (ORDER BY e.salary DESC) AS rn FROM emp e WHERE e.dept_id 100 ) WHERE rn BETWEEN 1 AND 20;3.2 ROW_NUMBER()和ROWNUM的本质区别这两种方案虽然最终效果一样但底层逻辑有本质差异对比项ROWNUMROW_NUMBER()编号时机结果集逐行产生时分配整个结果集确定后统一编号分配顺序取数顺序OVER子句指定的排序顺序是否可指定排序不能天然按取数顺序可以通过ORDER BY显式指定是否受WHERE影响是编号在WHERE之后是编号在WHERE之后SQL标准支持Oracle特有标准SQL其他数据库也能用这里有个细节需要特别注意ROW_NUMBER()的编号是在内层查询的WHERE过滤之后执行的所以如果你在内层已经写了WHERE条件编号会基于过滤后的结果集。这个行为刚好符合分页的业务预期——先筛掉不需要的数据再编号切片。3.3 分析函数分页的适用场景和隐藏坑Rownum方案的隐式编号方式在涉及多表关联或GROUP BY聚合时很容易出错。而ROW_NUMBER()因为编号的时机和范围非常明确在这些复杂场景下反而不容易出Bug。举个例子分页查询每个部门的员工薪资排名用ROWNUM做你会很痛苦但用ROW_NUMBER()配合PARTITION BY就非常自然SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rn FROM emp e ) WHERE rn 3;这就是“部门内薪资前三名”的经典查询。不过要提醒一个陷阱ROW_NUMBER()方案对性能的敏感度很高。中间层需要对全量数据排序编号如果内层结果集特别大比如千万级排序成本就很可观。相比之下ROWNUM方案的中间层因为有ROWNUM 30这道门槛反而能提前终止排序在大数据量分页场景下性能更好。所以我的经验是数据量小百万以内、逻辑复杂、团队协作频繁用ROW_NUMBER()数据量大、追求极致性能用ROWNUM。4. 方法三OFFSET...FETCH子句12c新特性最简洁的写法如果你用的是Oracle 12c及以上版本那可以用官方对标MySQL LIMIT的语法——OFFSET ... FETCH。这套语法在12.1版本引入19c已经非常稳定是三种方法里写起来最舒服的。4.1 对标MySQL LIMIT的体验一张纸说清楚SELECT emp_id, emp_name, salary FROM emp ORDER BY emp_id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;这个SQL的意思非常直白跳过前20行取接下来10行也就是第21行到第30行即第3页数据。OFFSET是偏移量FETCH NEXT是指定取多少行整套语法就是为分页而生的。如果你只想取前N条OFFSET可以省略-- 取薪资最高的前10个人 SELECT emp_id, emp_name, salary FROM emp ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY;4.2 OFFSET...FETCH的规则和限制别踩这些雷这套语法有几点限制值得注意必须配合ORDER BY使用。如果省略ORDER BYOracle会报错。这个设计其实很有道理没有排序的分页毫无意义Oracle直接帮你堵住了这个逻辑漏洞OFFSET的值不能为负数只能是0或正整数FETCH NEXT里的N可以是表达式甚至可以是绑定变量这意味着你可以很轻松地实现动态分页参数SELECT emp_id, emp_name FROM emp ORDER BY emp_id OFFSET :offset ROWS FETCH NEXT :pageSize ROWS ONLY;如果你想要取到剩余的所有行比如最后一页数据不够了可以写成FETCH NEXT 10 ROWS WITH TIES但这里有个微妙的概念WITH TIES会额外返回与第10行排序键值相同的行。举个例子如果第10名的薪资和第11名一样用FETCH FIRST 10 ROWS WITH TIES会返回11行。如果不需要这个特性就用ONLY严格限制行数另外这套语法的底层实现其实也是ROW_NUMBER()。也就是说12c的OFFSET...FETCH只是把ROW_NUMBER()封装得更友好并没有引入新的执行机制。所以它的性能特征跟ROW_NUMBER()类似大数据量下同样存在全量排序的问题。4.3 版本兼容性不是所有环境都能用这个写法这是最关键的实际问题。很多企业的生产环境还在用11g甚至更老的版本这套语法完全不可用。我做过一个项目开发环境是19c写代码用OFFSET...FETCH很舒服结果测试环境是11gSQL跑起来直接报ORA-00933: SQL command not properly ended整个模块差点延期。所以在项目开始时第一件事就是确认所有环境的数据库版本。如果有11g环境存在我建议代码里统一用ROWNUM方案因为它全版本兼容。如果你非要用新特性必须做好数据库版本升级计划或者在DAO层做版本判断动态切换SQL。下面是一个简单的版本检查SQL你可以用来快速确认环境版本SELECT * FROM v$version;5. 三种方法横向对比与选型建议直接抄作业我整理了一张对比表覆盖了三种方案在关键维度上的差异实际选型时照着这张表判断就行对比项ROWNUM三层嵌套ROW_NUMBER()OFFSET...FETCH最低版本要求8i8i12c R1SQL复杂度三层嵌套理解成本高两层逻辑清晰最简单最直观代码可读性差新人容易看懵好最好性能大数据量最优可提前终止扫描一般需全量排序一般需全量排序是否支持绑定变量支持支持支持排序稳定性控制手动手动手动复杂业务场景适配一般嵌套易错优秀支持PARTITION BY一般仅支持简单排序看完这张表我的选型建议是生产环境是11g及以下没得选直接用ROWNUM生产环境是12c及以上且数据量在百万级以内优先用OFFSET...FETCH代码简洁维护成本低生产环境是12c及以上数据量千万级以上ROWNUM并且必须在排序字段上建立合适的索引涉及复杂分组、排名等业务ROW_NUMBER()时不可替代的比如取每个部门前N条这里要特别强调一点不管用哪种方案分页SQL的ORDER BY字段都强制要求有索引而且最好是唯一索引或者联合索引中包含唯一列。否则随着页码越来越大查询会越来越慢直到把数据库拖垮。6. 分页查询的常见问题与排查技巧这部分全部来自我实际项目中踩过的坑一个一个说。6.1 分页数据重复或丢失先查排序字段是否唯一有一次做报表分页用户反馈说第3页和第4页出现了重复数据我排查了很久最后发现是ORDER BY的字段是创建日期而那段时间恰好有大量同一秒创建的记录。日期一样时Oracle返回的顺序不受控制所以分页边界就乱了。解决方法是把排序改成ORDER BY create_time, id用ID作为第二排序键保证唯一性。这个坑非常隐蔽数据量小的时候很难触发等数据量大了才暴露。6.2 深分页查询极慢怎么办所谓深分页就是翻到第100页、第1000页这种场景。ROWNUM方案在深分页时中间层的ROWNUM 10000会把前10000行都取出来再丢掉前9990行代价随页码线性增长。这也是所有分页方案的通用痛点。常规解法是改成“基于游标的分页”或“基于键集的分页”-- 记录上一页最后一条数据的ID下一页直接从这里开始 SELECT emp_id, emp_name FROM emp WHERE emp_id :lastEmpId ORDER BY emp_id FETCH FIRST 20 ROWS ONLY;这种方式的性能跟页码大小无关无论翻到多深都是恒定的。适合“加载更多”这种交互场景但不适合带有具体页码跳转的业务需求。6.3 count(*)和分页SQL到底应该怎么配合分页通常需要返回总条数很多人的做法是单独执行一条SELECT COUNT(*) FROM emp WHERE ...。这在数据量大时会有两个问题一是全表扫描可能很慢二是跟分页SQL是两条独立SQL中间如果有数据变更总数和列表可能不一致。对于总数查询我建议在WHERE条件涉及的字段上建立合适的复合索引让COUNT查询走索引扫描而不是全表扫。另外如果业务对总数精度要求不高而数据量又特别大可以考虑缓存总数或者先用估算值等翻页时再刷新精确值。6.4 12c不生效OFFSET...FETCH先看兼容性参数有一次开发反馈说OFFSET...FETCH在12c上报错我看了SQL完全没问题最后发现是数据库的兼容性参数设置太低。该参数控制数据库允许使用的功能级别如果版本设置低于12即使实际版本是19c也无法使用12c的新特性。检查方法SELECT name, value FROM v$parameter WHERE name compatible;如果显示的值低于12.2.0就需要评估后调整到合适版本。不过这个修改是全局性的可能影响其他功能必须谨慎操作最好在维护窗口内进行。6.5 查询速度时快时慢重启后又正常了这种问题的根源往往不是分页SQL本身而是统计信息过期导致优化器选择了错误的执行计划。比如数据量从10万涨到了1000万但统计信息还停留在10万时的状态优化器就会误判全表扫描比走索引更划算。这种情况的排查方法很直接用EXPLAIN PLAN FOR查看执行计划对比执行计划里是否出现了全表扫描如果确实是统计信息问题执行EXEC DBMS_STATS.GATHER_TABLE_STATS(SCHEMA,EMP);更新统计信息6.6 一张速查表排查问题直接对照现象可能原因解决方案ROWNUM条件查不出数据ROWNUM与大于号直接比较使用三层嵌套写法分页数据重复/丢失ORDER BY字段不唯一追加ID作为第二排序键深分页越来越慢全量排序大量偏移改用键集分页12c上OFFSET语法报错compatible参数版本过低调整该参数SQL执行计划突变统计信息过期更新统计信息分页速度慢但CPU不高缺少合适索引检查执行计划并建索引7. 最后再分享一个细节绑定变量怎么写才最稳前面几种分页方案都提到了绑定变量这里展开说一下。使用绑定变量不只是为了防止SQL注入更重要的是让Oracle能共享游标减少硬解析带来的CPU消耗。在高并发分页场景下这个优化收益非常明显。-- 错误示范SQL文本每次不同无法复用执行计划 SELECT * FROM ( SELECT e.*, ROWNUM rn FROM ( SELECT * FROM emp ORDER BY emp_id ) e WHERE ROWNUM 200 ) WHERE rn 191; -- 正确示范用绑定变量同一条SQL可以重复执行 SELECT * FROM ( SELECT e.*, ROWNUM rn FROM ( SELECT * FROM emp ORDER BY emp_id ) e WHERE ROWNUM :endRow ) WHERE rn :startRow;实际开发中配合MyBatis等框架时注意#{startRow}这种写法就是预编译的绑定变量而${startRow}是字符串拼接后者不仅性能差还有注入风险一定要避免。还有一个容易被忽略的点绑定变量的数据类型必须稳定。如果这个参数有时候传数字、有时候传字符串Oracle可能会因为找不到匹配的执行计划而反复硬解析反而比不用绑定变量更慢。我在实际项目里做分页优化时最深的体会是分页本身不难难的是理解数据库引擎到底怎么执行你的SQL。ROWNUM方案、ROW_NUMBER()方案、OFFSET...FETCH方案本质上是三种不同的思维模型——第一个是过程式的“边取边编号”第二个是声明式的“先排序再编号”第三个是纯语法糖。理解了这一层不管遇到什么数据库什么分页需求你都能一眼看穿本质写出又快又稳的SQL。希望这篇文章能帮你少走一些我当年走过的弯路。