深度解读Oracle分页优化策略：技术专家实战经验分享

哎，说到Oracle分页，这玩意儿吧，真是让人又爱又恨，记得刚入行那会儿，觉得不就是个ROWNUM嘛，WHERE ROWNUM <= 20 AND ROWNUM >= 10，多简单，结果一上手，性能直接给你来个下马威，数据量稍微大点，页面翻到后面就跟老牛拉破车似的，慢得让人想砸键盘，后来被师父骂了几次，才慢慢琢磨出点门道，今天聊的这些，不是什么教科书上的标准答案，就是些实战里摸爬滚打出来的土办法，可能有点碎,您多担待。

最原始的那种分页，大家肯定都写过，就是子查询套子查询，先用ROWNUM捞出一个大结果集，然后再从这个结果集里选，看起来没问题对吧？但Oracle这老伙计，它处理ROWNUM的逻辑有点特别，它是在数据过滤、排序之前就给你生成这个行号的，所以你想想，当你写WHERE ROWNUM <= 1000 ORDER BY create_time DESC，它可能吭哧吭哧先给你捞出来1000条，但这1000条不一定是按时间排好序的前1000条，顺序可能是乱的！等你再在外层用ROWNUM去切分，比如拿第50到60条，结果可能完全不对，这个坑,我估计不少人都栽过跟头。

所以后来，我们一般都强制在内层查询就先完成排序，像这样：SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT * FROM big_table ORDER BY create_time DESC) a WHERE ROWNUM <= 60) WHERE rn >= 50，三层嵌套，看着是啰嗦了点，但起码顺序是准的，问题又来了，当big_table有几千万条数据，而且create_time上如果没索引… 那画面太美不敢看，排序操作能直接把临时表空间撑爆，数据库服务器CPU直接飙红，这时候你就得琢磨了，是不是能给create_time加个降序索引？或者,有没有更野的路子？

我遇到过最头疼的一个case，是一个电商平台的订单查询，用户能按时间、金额、状态各种组合条件筛，然后分页，你没法为所有组合都建上索引，那时候真是头大，天天看执行计划，看AUTOTRACE，后来发现，有时候优化器挺傻的，它明明可以用索引范围扫描，却偏偏选择了全表扫描，我们就得用/*+ FIRST_ROWS(n) */这种Hint去“哄”它，告诉它：“哥们，用户只要前几页数据，你快一点返回。” 但这种Hint吧，用起来得特别小心，有点像走钢丝，用对了效果立竿见影，用错了可能适得其反，把执行计划带沟里去，有一次我手贱加了个Hint，结果一个原本跑2秒的查询，硬生生变成了20秒,被DBA追着骂了半天。

再后来，数据量实在太大，简单的ROWNUM分页即使有索引，翻到一千页以后，性能也顶不住了，因为Oracle底层还是要从第一条开始数，数到第N万条才开始给你返回数据，这就像让你从一本一百万页的书的第99999页开始读，你得一页一页往前翻，能不慢吗？这时候就得考虑“游标分页”，或者叫“seek method”，思路就变了，我们不记页码，我们记位置，上一页最后一条的create_time是T，ID是X，那么下一页就是WHERE (create_time < T) OR (create_time = T AND ID < X) ORDER BY create_time DESC, ID DESC，然后取前10条，这样，无论你翻到多后面，查询条件都能利用索引快速定位，而不用扫描全部偏移量之前的记录，这个法子，对于那种无限滚动的场景，简直是救命稻草，不过实现起来前端也得配合,得把最后一条记录的位置信息带回来。

还有一次，搞过一个挺变态的优化，那个表有个status字段，90%的数据都是状态为‘已完成’的，用户却总是查状态为‘处理中’的，而这部分数据很少，如果直接WHERE status = '处理中' ORDER BY id再分页，虽然status有索引，但排序字段是id，可能还得回表排序，后来我们耍了个小花招，建了个函数索引，(status, id)，专门伺候这个分页查询，效果出奇的好，因为索引本身已经按status和id排好序了，数据库直接索引范围扫描就把数据按顺序拿出来了，几乎零排序成本，这种小技巧,就得对业务和数据分布特别了解才行。

说到底，分页优化没有银弹，你得像个老中医一样，先“望闻问切”：数据量多大？常用查询模式是什么？排序字段有没有索引？偏移量一般多大？用户是不是真的会翻到几百页之后？业务上做个妥协可能比技术硬优化更有效，比如只允许用户翻前100页，再往后就提示用更精确的条件筛选，这总比把数据库拖垮强,对吧？

唉，这些东西，说起来都是血泪史，每个优化策略背后，可能都是某个深夜的焦头烂额，或者是一次上线后的惊心动魄，数据库这东西，尤其是Oracle，太深了，感觉永远都学不完，今天说的这些，也就是我自个儿的一点粗浅体会，肯定有不周全的地方，大家随便听听,希望能抛砖引玉吧。