SQL Server 分页存储过程优化：避免全表扫描的执行计划陷阱

林舟

发布时间：2026-10-15| 6人浏览过

原创

在使用 SQL Server 进行大数据量分页时，常见的存储过程实现容易因执行计划不当引发全表扫描，导致性能随页码深入急剧下降。本文通过对比两种基于 ROW_NUMBER 的写法，解析为何“先过滤后关联”优于“直接多表关联”，并给出能显著降低逻辑读取次数、保持查询效率稳定的优化方案。

分页查询的性能痛点

在处理新闻或日志等大量数据时，开发者常使用 ROW_NUMBER() OVER() 函数生成行号并进行分页。然而，不同的 SQL 结构会引导 SQL Server 优化器生成截然不同的执行计划。如果结构选择不当，数据库可能放弃索引查找，转而执行代价高昂的全表扫描（Table Scan），使得第 10 页和第 100 页的查询时间相差巨大。

传统写法导致的性能瓶颈

以下是一种常见的分页存储过程实现方式：先生成行号存入临时表 #tb，然后立即将其与主表 news 进行关联查询，以获取完整的行记录。

-- 代码一：直接关联查询
DECLARE @cc INT
SELECT NewsId, ROW_NUMBER() OVER(ORDER BY SortNum DESC) AS RowIndex 
INTO #tb 
FROM news WITH(NOLOCK) 
WHERE NewsTypeId=@NewsTypeId AND IsShow=1

SET @cc = @@ROWCOUNT
-- 注意此处将分页过滤条件与主表关联写在了一起
SELECT n.* FROM news AS n WITH(NOLOCK), #tb As t 
WHERE t.RowIndex > @PageIndex*@PageSize 
  AND t.RowIndex <=(@PageIndex+1)*@PageSize 
  AND t.newsid=n.newsid

SELECT @cc
DROP TABLE #tb

这种写法的致命缺陷在于：当数据库引擎检测到 WHERE 表达式中的匹配记录可能超过一定阈值，或者统计信息不足以支持最优索引查找时，它可能会选择对主表 news 进行全表扫描。即使有索引存在，这种复杂的关联条件也可能导致优化器失效。

现象观察：高 IO 消耗

在执行上述代码一后，查看统计信息显示：

表 'news' 的扫描计数为 1，逻辑读取高达 342 次。
在后续查询中，表 'news' 的逻辑读取进一步飙升至 2805 次，预读操作也大幅增加。

这表明数据库实际上读取了大量非目标行的数据到内存中进行排序和过滤，而非精准定位所需页面。随着 @PageIndex 的增加，这种低效模式会被重复执行，且由于每次都要重新评估连接条件，整体性能呈下降趋势。

优化策略：分离筛选与数据获取

为了迫使数据库按预期执行，我们需要明确告诉优化器：先只获取满足分页条件的 ID，再去回查主表详情。这就是“代码二”的核心逻辑。

第 1 步：生成分页行号与总数

首先创建临时表 #tb，其中仅包含 NewsId 和计算出的 RowIndex。这一步与代码一相同，利用窗口函数快速标记每一行的顺序。

DECLARE @cc INT
SELECT NewsId, ROW_NUMBER() OVER(ORDER BY SortNum DESC) AS RowIndex 
INTO #tb 
FROM news WITH(NOLOCK) 
WHERE NewsTypeId=@NewsTypeId AND IsShow=1
SET @cc = @@ROWCOUNT

第 2 步：在子集中提取目标 ID

创建一个更小的临时表 #tb2，仅从 #tb 中提取符合当前页码范围的 NewsId。因为 #tb 是内存中的轻量级表，这里的过滤极快，不会产生磁盘 I/O 开销。

-- 代码二部分：提取 ID
SELECT NewsId INTO #tb2 
FROM #tb As t 
WHERE t.RowIndex > @PageIndex*@PageSize 
  AND t.RowIndex <= (@PageIndex+1)*@PageSize

第 3 步：基于 ID 列表回查主表

最后，使用 IN 子句或 JOIN 从主表 news 中精确获取这几条记录的完整数据。由于传入的 NewsId 数量极少（通常仅为每页显示的数量，如 20 或 40 条），SQL Server 优化器几乎总是会使用聚簇索引或主键索引进行点查找（Key Lookup / Seek），完全避免全表扫描。

-- 代码二部分：获取最终结果
SELECT * FROM news WITH(NOLOCK) 
WHERE NewsId IN (SELECT * FROM #tb2)

SELECT @cc
DROP TABLE #tb
DROP TABLE #tb2

优化效果对比分析

通过对比两种方案的执行统计信息，可以发现显著的差异：

代码一（低效）：
- 主表 news 逻辑读取：~2805 次。
- 存在大量的预读（Pre-read）操作，表明数据库在猜测需要更多数据。
代码二（高效）：
- 第一步生成行号时，主表 news 逻辑读取 342 次（这是不可避免的初始化成本）。
- 但在最后获取数据阶段，主表 news 的扫描计数为 0（意味着没有全表扫描），逻辑读取降至 131 次（实际取决于返回的行数和索引结构）。
- 中间临时表 #tb2 的逻辑读取仅为 2 次。

代码二的优势在于其稳定性。无论请求第 1 页还是第 100 页，第二步和第三步的 IO 操作数基本保持不变，因为它始终是基于少量 ID 进行的精确查找。而代码一的效率会随翻页深度增加而变得不可控。

总结

在 SQL Server 中优化分页存储过程，核心在于理解执行计划。避免在涉及大数据量表的复杂关联中直接使用范围过滤，而应采用“先提取 ID，再回查详情”的两阶段策略。这不仅降低了逻辑读取次数，还能确保查询响应时间不随页码深度增加而劣化。以上就是 SQL Server 分页存储过程优化的详细内容，更多关于 SQL Server 性能调优的资料请关注本站其它相关文章！