MS SQL Server下高效查询包含指定ID的分页数据页的实现方法

前提假设 #

• 表主键为自增整数类型ID，且具备聚集索引（和你现有游标分页的排序规则一致）
• 单页固定返回20条数据，排序规则为ID升序

实现逻辑 #

核心思路利用ID的有序性和索引特性，避免全表扫描和大偏移量分页的性能损耗：

1. 先统计目标ID之前的记录总数，计算出目标ID所在页的起始偏移量
2. 基于偏移量取对应页的20条数据即可

高效SQL示例 #

-- 声明变量：目标ID、页大小
DECLARE @TargetID INT = 1234;
DECLARE @PageSize INT = 20;
DECLARE @PrevCount INT;
DECLARE @Offset INT;

-- 统计目标ID之前的记录数，走ID索引，效率极高
SELECT @PrevCount = COUNT(1) FROM 你的表名 WHERE ID < @TargetID;
-- 计算当前页的起始偏移量
SET @Offset = FLOOR(CAST(@PrevCount AS FLOAT) / @PageSize) * @PageSize;

-- 取对应页的数据
SELECT * FROM 你的表名
ORDER BY ID ASC
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;

超大数据量优化方案 #

如果表数据量过千万，OFFSET大偏移量仍有性能损耗，可以改用ID范围查询进一步优化，全程走索引无扫描：

DECLARE @TargetID INT = 1234;
DECLARE @PageSize INT = 20;
DECLARE @StartID INT;

-- 直接定位目标ID所在页的起始ID
WITH PrevRecords AS (
    SELECT TOP (@PageSize) ID FROM 你的表名
    WHERE ID <= @TargetID
    ORDER BY ID DESC
)
SELECT @StartID = MIN(ID) FROM PrevRecords;

-- 基于起始ID取当前页20条数据
SELECT TOP (@PageSize) * FROM 你的表名
WHERE ID >= @StartID
ORDER BY ID ASC;

注意事项 #

• 如果你的分页排序规则不是ID升序，而是按其他字段（如创建时间）排序，需要将排序字段和ID做联合索引，调整查询条件中的排序逻辑即可
• 边界情况自动适配：目标ID为第一条时返回前20条，目标ID为最后一条时返回最后不足20条的所有数据
• 两种方案在ID有索引的前提下，哪怕亿级数据查询延迟都在毫秒级

内容的提问来源于stack exchange，提问作者DoomBro_Max