用存储过程分页在Oracle里其实还能更快点的办法分享

最基础也是最常见的分页方法是使用ROWNUM或者12c以后引入的OFFSET-FETCH语法，很多人写到存储过程里，大概就是这样：先排序，然后算出从第几条到第几条，再用ROWNUM包一层或者直接用OFFSET跳过多少行，FETCH取多少行，这个方法没错，能用，但在数据量非常大，尤其是翻到后面几页的时候，性能问题就出来了，因为OFFSET N的意思就是跳过前N行，数据库得老老实实地把前N条数据都找出来，然后再扔掉,这成本就很高了。

那怎么能更快点呢？核心思路就一条：别用OFFSET那种“跳过”的思路，而是用“直接定位”的思路。 这就好比翻一本很厚的书，笨办法是从第一页开始一页一页数到你想要的那页；而聪明的办法是利用目录或者书签，直接翻到大概的位置，我们今天要说的办法，就是给数据库一个“书签”。

这个办法通常被称为“键集分页”或者“游标分页”，它不依赖页码，而是依赖上一页最后一条记录的唯一标识（比如主键、或者有唯一约束的列）来获取下一页。

具体怎么在Oracle的存储过程里实现呢？我们一步一步说。

第一步：确定你的“书签”是什么。 这个书签必须是能唯一标识一行且有序的，最简单的情况就是自增的主键ID，假设我们有一张商品表products，主键是product_id，我们按product_id升序分页，如果你的排序字段不是唯一的，比如按create_time排序，但同时间可能有多条记录，那你的书签就得是(create_time, product_id)这样的组合,确保唯一性和顺序。

第二步：改造存储过程的参数和逻辑。 传统的分页存储过程参数是page_number（页码）和page_size（每页大小），我们现在要改一下,参数变成：

• p_page_size IN NUMBER：每页大小,这个不变。
• p_last_seen_id IN NUMBER DEFAULT NULL：上一页最后一条记录的ID，如果是第一页，这个值可以为空（NULL）。
• p_direction IN VARCHAR2 DEFAULT 'NEXT'：分页方向，‘NEXT’是下一页，‘PREVIOUS’是上一页，这里我们先重点讲下一页,原理是相通的。

第三步：编写查询SQL。 这是最关键的一步，我们不再使用OFFSET。

如果是获取第一页（当p_last_seen_id为NULL时）,查询非常简单：

SELECT *
FROM (
    SELECT p.*, ROWNUM as rn
    FROM products p
    WHERE 1=1
    ORDER BY product_id ASC
)
WHERE ROWNUM <= p_page_size;

这个和传统方法没区别,因为第一页总得从头开始拿。

重点是获取下一页（当p_last_seen_id有值时）：

SELECT *
FROM (
    SELECT p.*, ROWNUM as rn
    FROM products p
    WHERE p.product_id > p_last_seen_id  -- 关键在这里！直接定位到上次结束的位置之后
    ORDER BY product_id ASC
)
WHERE ROWNUM <= p_page_size;

看这个WHERE p.product_id > p_last_seen_id，它利用了主键索引的有序性，数据库可以直接在B+树索引上快速定位到p_last_seen_id所在的位置，然后顺着叶子节点向后扫描p_page_size条记录就行了，它完全避免了扫描和丢弃p_last_seen_id之前的所有数据，效率极高，即使你要翻到第1000页，只要传给我第999页最后一条的ID,我的查询速度也和翻第一页差不多。

第四步：处理上一页和排序变化。 上面只说了升序和下一页，如果要支持降序，或者翻上一页，原理是一样的,只是比较符号变一下。

• 降序+下一页：上一页最后一条ID是较大的数，下一页是更小的数，条件就是WHERE product_id < p_last_seen_id ... ORDER BY product_id DESC。
• 翻上一页：这需要客户端不仅记录上一页最后一条ID，还要记录第一条ID，翻上一页就相当于把顺序倒过来，用第一条ID作为锚点，然后取“上一页”，实现起来比下一页稍复杂一点，但核心思想依然是“定位”而非“跳过”。

为什么这个方法能更快？

1. 利用了索引的有序性：如果ORDER BY的字段上有合适的索引，这种WHERE id > ?的查询可以直接从索引的中间位置开始范围扫描,这是最快的访问路径之一。
2. 消除了OFFSET的巨大开销：随着页码增大，OFFSET的成本线性增长，而“键集分页”的性能是稳定的，与翻到第几页无关,只和页面大小有关。
3. 对数据库更友好：减少了大量的IO和CPU消耗。

需要注意的坑：

1. 排序稳定性：你必须确保排序的依据是绝对唯一的，如果按name排序，但有重名的人，当你翻页时，如果底层数据有新增或删除，可能导致同一记录在不同页重复出现或消失，书签”一定要用唯一键或组合唯一键。
2. 不适合跳页：这种方法非常适合“上一页”、“下一页”这种连续翻页的场景，如果你需要一个按钮让用户直接从第1页跳到第100页，它就不太方便了，因为你不知道第99页最后一条记录的ID是什么，在实际应用中，通常会把这种“精准跳页”功能弱化为“更早之前”或“更晚之后”的模糊加载,或者与传统分页结合使用。
3. 结果集变化：在分页过程中，如果底层数据有增删（比如第一页的数据被删了一条），那么后续页的数据位置会前移，这是所有分页方式都存在的共性问题,并非此方法独有。

在Oracle存储过程中实现更快的分页，诀窍就是抛弃OFFSET，采用基于唯一键的“键集分页”法，把存储过程的参数从“页码”转变为“上一页最后一条记录的标识”，然后在SQL的WHERE子句中用>或<来直接过滤，这种方法能极大地提升大数据量下的分页性能，尤其是在深分页场景下，效果非常显著，虽然它在使用上有一点点限制（比如不能随意跳页），但对于常见的流式加载、无限滚动或连续翻页需求来说,无疑是更优的选择。