分页存储过程 使用

分页存储过程详解与实现方案

分页技术背景与核心需求

在数据库系统中,分页是解决海量数据展示的核心技术，当单表数据量达到百万级时，直接查询全表会导致严重的性能问题，分页存储过程的核心目标是通过高效的算法从大数据集中提取指定范围的数据，同时保证查询性能和资源占用的平衡。

主流分页实现方案对比

经典分页存储过程实现

基础版（OFFSET/FETCH）

CREATE PROCEDURE Pagination_Basic
    @PageIndex INT,
    @PageSize INT,
    @OrderColumn NVARCHAR(50) = 'ID',
    @OrderDirection NVARCHAR(4) = 'ASC'
AS
BEGIN
    DECLARE @Offset INT = (@PageIndex-1)@PageSize
    EXEC('SELECT  FROM YourTable 
          ORDER BY ' + @OrderColumn + ' ' + @OrderDirection + '
          OFFSET ' + CAST(@Offset AS NVARCHAR) + ' ROWS 
          FETCH NEXT ' + CAST(@PageSize AS NVARCHAR) + ' ROWS')
END

优化版（ROW_NUMBER()）

CREATE PROCEDURE Pagination_Optimized
    @PageIndex INT,
    @PageSize INT,
    @OrderColumn NVARCHAR(50) = 'ID'
AS
BEGIN
    WITH OrderedData AS (
        SELECT , ROW_NUMBER() OVER(ORDER BY @OrderColumn) AS RowNum 
        FROM YourTable
    )
    SELECT  FROM OrderedData 
    WHERE RowNum > (@PageIndex-1)@PageSize 
    AND RowNum <= @PageIndex@PageSize
    OPTION (RECOMPILE) -优化查询计划
END

高级版（键集分页）

CREATE PROCEDURE Pagination_Keyset
    @LastSeenId INT = NULL, -上次查询的最后一条记录ID
    @PageSize INT,
    @OrderColumn NVARCHAR(50)
AS
BEGIN
    IF @LastSeenId IS NULL BEGIN
        -首屏查询逻辑
        SELECT TOP (@PageSize)  FROM YourTable 
        ORDER BY @OrderColumn
    END ELSE BEGIN
        -后续分页查询
        SELECT TOP (@PageSize)  FROM YourTable 
        WHERE ID > @LastSeenId 
        ORDER BY @OrderColumn
    END
END

性能优化策略

1. 索引优化：确保分页字段（特别是ORDER BY列）建立聚集索引或复合索引
2. 预编译执行计划：使用OPTION (RECOMPILE)处理动态参数，减少计划编译开销
3. 内存管理：对高频分页场景，可考虑使用临时表缓存前N页数据
4. 并行查询：对超大规模分页，采用分区表+并行查询策略（需硬件支持）

特殊场景处理方案

完整示例代码（含动态排序）

CREATE PROCEDURE Pagination_Dynamic
    @PageIndex INT,
    @PageSize INT,
    @OrderColumn NVARCHAR(100), -允许自定义排序列
    @OrderDirection NVARCHAR(4) = 'ASC'
AS
BEGIN
    SET NOCOUNT ON;
    -参数校验
    IF @OrderDirection NOT IN ('ASC','DESC') SET @OrderDirection = 'ASC'
    IF @PageIndex < 1 SET @PageIndex = 1
    IF @PageSize < 1 SET @PageSize = 10
    DECLARE @Offset INT = (@PageIndex-1)@PageSize
    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT  FROM YourTable 
        ORDER BY ' + QUOTENAME(@OrderColumn) + ' ' + @OrderDirection + '
        OFFSET ' + CAST(@Offset AS NVARCHAR) + ' ROWS 
        FETCH NEXT ' + CAST(@PageSize AS NVARCHAR) + ' ROWS'
    EXEC sp_executesql @SQL -安全执行动态SQL
END

常见问题与解决方案

FAQs

Q1：为什么大页码时分页速度明显变慢？
A：OFFSET方案需要扫描前N(PageIndex-1)条记录，当偏移量超过百万级时，查询成本指数级上升，建议改用键集分页，或限制最大允许页码（如超过第1000页强制重置）。

Q2：如何处理包含NULL值的排序字段？
A：在ORDER BY子句中使用ISNULL(ColumnName,0)进行空值转换，

ORDER BY ISNULL(CreateTime,'1900-01-01') DESC

同时建议在业务层避免插入NULL值,保持排序字段的完整性