上一篇
高效的分页存储过程如何实现？，有哪些性能优化技巧？
在实际开发中，分页显示数据是极其常见的需求，但当数据量达到百万甚至千万级别时，不恰当的分页实现会导致严重的性能问题，查询耗时急剧增加，甚至拖垮数据库。高效的分页存储过程正是为了解决这一问题而生，它通过合理利用索引、避免全表扫描和减少不必要的数据传输，确保分页操作在大数据量下依然快速稳定，下面从实现原理、核心方法、对比分析以及最佳实践几个方面详细阐述如何编写高效的分页存储过程。
核心分页方法及其性能对比
目前主流的关系型数据库（如 SQL Server、MySQL、PostgreSQL）都提供了多种分页手段，但不同方法在性能上差异巨大,以下是几种常见的高效分页方案：
| 方法 | 实现方式 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|---|
| ROW_NUMBER() 分页 | 使用子查询或 CTE 对结果集编号，然后筛选指定范围 | 通用性强，几乎适用于所有 SQL Server 版本（2005+） | 需要扫描所有符合条件的行，直到你需要的页码，数据量大时性能下降明显 | 中小数据量（几十万行以内） |
| OFFSET FETCH 分页 | 使用 ORDER BY ... OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY | 语法简洁，SQL Server 2012+ 原生支持，优化器可生成更高效的执行计划 | 仍然需要扫描跳过的行，页码越大性能越差 | 中等数据量，页码不太大 |
| 键集分页（Keyset Pagination） | 基于上一页最后一条记录的排序键作为起点，通过 WHERE Key > @LastKey 而不是 OFFSET 实现 | 扫描行数恒定，不受页码大小影响，性能极高 | 无法直接跳转到随机页，只能逐页翻看；需要唯一且稳定的排序键 | 海量数据，只需顺序翻页（如新闻列表、日志浏览） |
| 临时表 / 表变量分页 | 将主键或唯一键先插入临时表，再与主表 JOIN 获取完整数据 | 可控性强，可先过滤主键，再回表查详情 | 需要额外 I/O 和临时存储，编写复杂，不一定比直接 OFFSET 快 | 复杂排序或需要多次分页的场景 |
| 游标分页 | 使用游标逐行滚动 | 精准控制，避免重复扫描 | 资源消耗大，并发低，严禁用于生产环境 | 极少数特殊需求 |
从性能角度看，键集分页是最优的，它避免了“跳过”行带来的额外开销，但它的局限性在于无法支持随机页面跳转，只适合“上一页/下一页”的浏览模式，如果业务要求必须支持跳转到第 N 页，OFFSET FETCH 或 ROW_NUMBER() 是更直接的选择,但需要通过索引优化来尽可能提升性能。
编写高效分页存储过程的关键原则
-
确保排序键上有索引
无论使用哪种分页方法，ORDER BY 子句的字段必须建立索引，且索引能够覆盖排序和筛选条件，最理想的情况是创建包含所有 WHERE 条件列和 ORDER BY 列的覆盖索引，避免额外的书签查找（Key Lookup），如果经常按 CreateTime DESC 分页且筛选 Status=1，则索引应为 (Status, CreateTime DESC)。
-
避免使用 `SELECT `
只返回真正需要的列，减少数据从磁盘读取和网络传输的量，如果必须返回大量字段，考虑先查询主键，再通过主键 JOIN 原表获取剩余字段,这样能让分页主查询更轻量。
-
合理使用 OPTION (RECOMPILE)
当参数变化较大导致执行计划不稳定时，可考虑使用 OPTION (RECOMPILE) 让 SQL Server 根据当前参数生成最优计划，但注意这会增加 CPU 开销,适合仅在查询不频繁但参数变化大的场景使用。
-
利用键集分页处理连续翻页
对于只需要“上一页/下一页”的应用（如 APP 列表、IB 日志），采用键集分页远远优于 OFFSET 方式，存储过程接收上一页最后一条记录的排序键值（如 @LastId 或 @LastCreateTime），然后通过 WHERE CreateTime < @LastCreateTime 或 WHERE Id > @LastId（取决于排序方向）来获取下一页，搭配 TOP (@PageSize) 或 FETCH NEXT @PageSize ROWS ONLY 即可，这样每次查询都只扫描必要的行，即使数据量超过千万,分页速度也几乎不变。
-
避免在分页存储过程中使用复杂的函数或嵌套子查询
例如在 ORDER BY 中使用函数会导致索引失效，需要改写为计算列或使用应用层排序，同样，WHERE 条件中的函数也应尽量避免。
- 优先选择键集分页,如果业务只支持顺序翻页。
- 必须支持随机跳页时，使用 OFFSET FETCH（SQL Server 2012+）或 ROW_NUMBER(),并在排序键上建立覆盖索引。
- 定期更新统计信息,确保优化器选择正确的执行计划。
- 对大表的分页查询进行缓存，如果数据变化不频繁,可在应用层缓存结果集。
- 监控分页查询的执行计划，避免出现 Table Scan、Key Lookup 等低效操作。
示例：基于键集分页的高效存储过程（SQL Server）
CREATE PROCEDURE dbo.GetNextPage @PageSize INT, @LastId INT = NULL, -上一页最后一条记录的 Id（降序排列时传最大值） @SortDirection VARCHAR(4) = 'DESC' AS BEGIN SET NOCOUNT ON; -假设按 Id 降序排列 IF @SortDirection = 'DESC' BEGIN SELECT TOP (@PageSize) Id, Title, Content, CreateTime FROM dbo.Articles WHERE (@LastId IS NULL OR Id < @LastId) -降序时取小于上一页最小 Id 的记录 ORDER BY Id DESC; END ELSE BEGIN SELECT TOP (@PageSize) Id, Title, Content, CreateTime FROM dbo.Articles WHERE (@LastId IS NULL OR Id > @LastId) -升序时取大于上一页最大 Id 的记录 ORDER BY Id ASC; END END;
该存储过程配合 Id 上的聚集索引，每次扫描的页数不超过 @PageSize，性能极好，如果业务需要支持跳页，可结合 OFFSET 并辅以 @PageNumber 参数,但必须确保在索引字段上排序。
通过以上原则和实现，可以编写出真正高效的分页存储过程,让用户即使面对海量数据也能获得流畅的翻页体验。
相关问答 FAQs
问：为什么 OFFSET FETCH 分页在跳转到较大页码时性能会急剧下降？
答：因为 OFFSET FETCH 在内部仍然需要扫描所有被跳过的行，要查询第 1000 页（每页 20 条），数据库需要先读取前 20000 行，然后丢弃它们，只返回最后 20 行，这些被跳过的行会占用大量 I/O 和排序开销，且页码越大，扫描的行数越多，性能自然越来越差，而键集分页通过直接定位到上一页的边界，不需要扫描任何无关行,因此性能稳定。
问：在 MySQL 中如何实现类似键集分页的高效存储过程？
答：MySQL 同样支持键集分页思想，假设按 id 降序排列，下一页的查询可以用 WHERE id < @last_id ORDER BY id DESC LIMIT @page_size，注意 MySQL 的 LIMIT 不支持 OFFSET 中的大偏移量，但键集分页正好避免了这个问题，存储过程可以写成 PROCEDURE GetNextPage(IN page_size INT, IN last_id INT)，内部使用预编译参数，如果需要在 MySQL 中实现随机跳页，则必须使用 LIMIT offset, page_size，提前在 ORDER BY 字段上建立索引，并保证 offset 不要太大（例如禁止用户直接跳转到数百万页）。
