搜索菜单
当前位置: 首页 > SQL > SQL Server 分页查询性能分析与优化策略

SQL Server 分页查询性能分析与优化策略

Elena
Elena
发布时间：2026-10-14 | 4人浏览过
原创

在SQL Server开发中，高效的分页查询是提升Web应用响应速度的关键。本文深入分析了五种主流分页方案的执行逻辑，并通过实际测试揭示了不同方案在处理靠前与靠后页面时的性能差异，最终推荐结合ROW_NUMBER函数的优化方案及对应的通用存储过程。

五种分页方案的原理与基准测试

为了评估不同分页策略的效率，我们以ARTICLE表为例，模拟从大量数据中提取指定范围记录的场景。测试主要关注两个维度：首页（第1页）性能和深页（第1000页）性能。以下是五种典型方案的实现代码及平均查询100次的耗时对比。

第一种：NOT IN 排除法

利用ID NOT IN (SELECT TOP N ...)排除已显示的记录。这种写法逻辑直观，但在数据量较大时，子查询和排除操作会导致严重的I/O开销。

-- 获取最后30条，排除前45000条
SELECT TOP 30 * 
FROM ARTICLE 
WHERE ID NOT IN(
    SELECT TOP 45000 ID FROM ARTICLE 
    ORDER BY YEAR DESC, ID DESC
) 
ORDER BY YEAR DESC, ID DESC;
  • 首页耗时：约 80s
  • 第1000页耗时：约 45s
  • 评价：性能最差，不建议在生产环境使用。

第二种：多层嵌套 TOP

通过三层嵌套查询，先取前N+M条，再排序倒序取前M条，最后再次倒序。虽然避免了NOT IN的扫描，但多次全表排序依然消耗资源。

SELECT * FROM (
    SELECT TOP 30 * FROM (
        SELECT TOP 45030 * FROM ARTICLE ORDER BY YEAR DESC, ID DESC
    ) f ORDER BY f.YEAR ASC, f.ID DESC
) s ORDER BY s.YEAR DESC, s.ID DESC;
  • 首页耗时：约 30s
  • 第1000页耗时：约 138s
  • 评价：性能波动极大，深页表现极差。

第三种：子查询 + INNER JOIN

构造一个内部视图仅返回ID和排序字段，通过INNER JOIN关联主表获取完整数据。减少了对大字段的选择，提升了缓存命中率。

SELECT w1.*, w2.n 
FROM ARTICLE w1
INNER JOIN (
    SELECT ROW_NUMBER() OVER(ORDER BY YEAR DESC, ID DESC) AS n, ID 
    FROM ARTICLE
) w2 ON w1.ID = w2.ID 
WHERE w2.n BETWEEN 1 AND 30 
ORDER BY w2.n;

注：原文此处使用了类似TOP的子结构，现代推荐直接改用ROW_NUMBER进行过滤。原文第三种种方案为：

  • 首页耗时：约 12s
  • 第1000页耗时：约 21s
  • 评价：性能较为均衡，是较稳定的选择。

第四种：子查询 + IN 子句

与第三种类似，但使用ID IN (...) 代替JOIN。通常在SQL Server优化器下，两者的执行计划非常接近。

SELECT * FROM ARTICLE w1 
WHERE ID IN (
    SELECT TOP 30 ID FROM (
        SELECT TOP 50030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
    ) w ORDER BY w.YEAR ASC, w.ID ASC
) 
ORDER BY w1.YEAR DESC, w1.ID DESC;
  • 首页耗时：约 13s
  • 第1000页耗时：约 20s
  • 评价：与第三种方案表现相当，易于维护。

第五种：ROW_NUMBER() 窗口函数

利用SQL Server 2005引入的ROW_NUMBER()函数生成行号，直接在外部筛选行号范围。这是目前公认最优雅且高效的方案之一。

SELECT w2.n, w1.* 
FROM ARTICLE w1, (
    SELECT TOP 50030 ROW_NUMBER() OVER(ORDER BY YEAR DESC, ID DESC) AS n, ID 
    FROM ARTICLE
) w2 
WHERE w1.ID = w2.ID AND w2.n > 50000 
ORDER BY w2.n ASC;
  • 首页耗时：约 14s
  • 第1000页耗时：约 15s
  • 评价：在所有测试中表现最为稳定，尤其是深页查询优势明显，且语法清晰，推荐使用。

方案综合对比与结论

通过上述测试数据可以看出：

  1. 浅页查询（如第1页）：方案3、4、5的性能均优于方案1和2。其中方案3略快，但与5差距不大。
  2. 深页查询（如第1000页）：方案5（ROW_NUMBER）展现出绝对优势，耗时远低于其他方案。方案1随页数增加耗时急剧上升，而方案5保持平稳。
  3. 综合推荐：鉴于用户浏览习惯主要集中在前几页，但系统需兼顾整体负载稳定性，方案5（ROW_NUMBER）是最佳选择。

注意：SQL Server 2000及更早版本不支持ROW_NUMBER()函数，若需兼容旧版本，建议选择方案3或方案4。

通用分页存储过程实现

基于第四种方案的逻辑，以下提供一个通用的SQL Server分页存储过程sys_Page_v2。该过程支持动态表名、字段、条件、排序及分页参数，可灵活嵌入业务系统中。

第1步：创建存储过程

执行以下T-SQL脚本以创建sys_Page_v2存储过程。它会自动处理总记录数计算、总页数估算及异常页数修正。

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sys_Page_v2]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sys_Page_v2]
GO

CREATE PROCEDURE [dbo].[sys_Page_v2]
@PCount int OUTPUT,       -- 总页数输出
@RCount int OUTPUT,       -- 总记录数输出
@sys_Table nvarchar(100), -- 查询表名
@sys_Key varchar(50),     -- 主键列名
@sys_Fields nvarchar(500),-- 查询字段列表
@sys_Where nvarchar(3000),-- WHERE条件（不含WHERE关键字）
@sys_Order nvarchar(100), -- 排序字段及方式
@sys_Begin int,           -- 起始位置（通常指当前页之前跳过的行数，或使用 pageIndex*pageSize）
@sys_PageIndex int,       -- 当前页码（从1开始）
@sys_PageSize int         -- 每页大小
AS
BEGIN
    SET NOCOUNT ON
    SET ANSI_WARNINGS ON

    IF @sys_PageSize < 0 OR @sys_PageIndex < 0
    BEGIN
        RETURN
    END

    DECLARE @new_where1 NVARCHAR(3000)
    DECLARE @new_order1 NVARCHAR(100)
    DECLARE @new_order2 NVARCHAR(100)
    DECLARE @Sql NVARCHAR(4000)
    DECLARE @SqlCount NVARCHAR(4000)

    -- 计算起始偏移量：这里采用原逻辑，Begin为0-based跳过数
    IF(@sys_Begin <= 0)
        SET @sys_Begin=0
    ELSE
        SET @sys_Begin=@sys_Begin-1

    -- 拼接WHERE条件
    IF ISNULL(@sys_Where,'') = ''
        SET @new_where1 = ' '
    ELSE
        SET @new_where1 = ' WHERE ' + @sys_Where 

    -- 处理排序方向：内层升序外层降序，或反之，确保最终结果正确
    IF ISNULL(@sys_Order,'') <> '' 
    BEGIN
        SET @new_order1 = ' ORDER BY ' + REPLACE(@sys_Order,'desc','asc') 
        -- 注意：原逻辑在此处替换asc/desc，具体需根据实际排序字段调整
        SET @new_order1 = REPLACE(@new_order1,'asc','desc') 
        SET @new_order2 = ' ORDER BY ' + @sys_Order
    END
    ELSE
    BEGIN
        -- 默认排序：假设主键ID，按ID降序
        SET @new_order1 = ' ORDER BY ID DESC'
        SET @new_order2 = ' ORDER BY ID ASC'
    END

    -- 计算总记录数和总页数
    SET @SqlCount = 'SELECT @RCount=COUNT(1),@PCount=CEILING((COUNT(1)+0.0)/'+ CAST(@sys_PageSize AS NVARCHAR)+') FROM ' + @sys_Table + @new_where1

    EXEC SP_EXECUTESQL @SqlCount, N'@RCount INT OUTPUT,@PCount INT OUTPUT', @RCount OUTPUT, @PCount OUTPUT

    -- 边界检查：如果输入页码大于总页数，修正为总页数
    IF @sys_PageIndex > CEILING((@RCount+0.0)/@sys_PageSize)
    BEGIN
        SET @sys_PageIndex = CEILING((@RCount+0.0)/@sys_PageSize)
    END

    -- 构建分页SQL：使用IN子查询方案
    SET @sql = 'select '+ @sys_fields +' from ' + @sys_Table + ' w1 '
        + ' where '+ @sys_Key +' in ('
        + 'select top '+ ltrim(str(@sys_PageSize)) +' ' + @sys_Key + ' from '
        + '('
        + 'select top ' + ltrim(STR(@sys_PageSize * @sys_PageIndex + @sys_Begin)) + ' ' + @sys_Key + ' FROM ' 
        + @sys_Table + @new_where1 + @new_order2 
        +') w ' + @new_order1
        +') ' + @new_order2

    -- print(@sql) -- 调试时可开启打印
    Exec(@sql)
END
GO

第2步：调用示例

在实际应用中，可通过如下方式调用该存储过程：

DECLARE @PCount INT, @RCount INT;
EXEC sys_Page_v2 
    @PCount = @PCount OUTPUT,
    @RCount = @RCount OUTPUT,
    @sys_Table = 'Article',
    @sys_Key = 'ID',
    @sys_Fields = '*',
    @sys_Where = 'Year >= 2023',
    @sys_Order = 'Year DESC, ID DESC',
    @sys_Begin = 0,
    @sys_PageIndex = 1,
    @sys_PageSize = 30;

总结

SQL Server的分页性能直接关系到系统的用户体验。虽然在早期的NOT IN或嵌套TOP方案中存在争议，但随着ROW_NUMBER()函数的普及以及数据库优化器的改进，采用窗口函数或精简的JOIN/IN子查询已成为最佳实践。开发者应根据SQL Server版本和数据规模，选择合适的分页算法。对于大多数现代化部署，优先推荐基于ROW_NUMBER的实现；若需兼容老版本或复用现有架构，上述sys_Page_v2存储过程提供了一个稳健的解决方案。

以上就是SQL Server分页查询性能分析与优化策略的详细内容，更多关于SQL Server数据库优化的资料请关注本站其它相关文章！

相关文章

相关标签: SQL 数据库优化

本站声明

本文内容仅供参考，不代表本站观点。转载或引用请注明出处。如发现内容问题，请通过联系我们页面反馈。

作者最新文章

全部内容

Python 相关产品和应用

全部下载
Python Selenium
Selenium

Selenium

4.51.0 · Windows

用于浏览器自动化和 Web 测试的 Python 工具。
Python 机器学习
scikit-learn

scikit-learn

1.9.1 · Windows

常用 Python 机器学习算法与模型评估工具库。
Python 代码质量
Black

Black

26.10.1 · Windows

强调一致性的 Python 自动代码格式化工具。
Python 代码质量
Ruff

Ruff

0.17.1 · macOS

由 Rust 编写的高速 Python 代码检查和格式化工具。
Python pytest
pytest

pytest

9.1.1 · Windows

简洁而强大的 Python 自动化测试框架。
Python FastAPI API
FastAPI

FastAPI

0.146.0 · Windows

高性能 Python API 框架，支持类型提示和自动接口文档。
Python Flask Web开发
Flask

Flask

3.1.3 · Windows

轻量灵活的 Python Web 应用框架。
Python Django Web开发
Django

Django

6.1.2 · Windows

成熟的 Python Web 框架，适合构建内容站点和业务系统。
Python Spyder
Spyder

Spyder

6.1.7 · Windows

面向科学计算与数据分析的 Python 集成开发环境。
Python SciPy
SciPy

SciPy

1.18.1 · Windows

面向科学、工程和技术计算的 Python 算法库。
Python Pandas 数据分析
pandas

pandas

3.0.6 · Windows

Python 数据分析库，提供 DataFrame 与丰富的数据处理能力。
Python NumPy
NumPy

NumPy

2.5.4 · Windows

Python 科学计算基础库，提供高性能多维数组与数值运算。

文章分类

专题列表

ipython.cn 学习网
人工智能 Java AI行业服务