我要评论引言
分页查询几乎是每个业务系统必备的功能。随着数据量的增长，不同分页方式的性能差异会变得非常明显。本文将详细对比sql server中常见的几种分页写法，并通过实际测试数据告诉你哪种性能最优。
一、常见的分页写法
假设有一张订单表 orders，包含100万条数据，我们要查询第50001~50020条记录（即跳过前50000条，取20条）。
写法1：row_number()+ 子查询（sql 2005+）
with cte as (
select *, row_number() over (order by orderdate desc, orderid) as rownum
from orders
)
select * from cte
where rownum between 50001 and 50020;
写法2：offset-fetch（sql 2012+）
select * from orders order by orderdate desc, orderid offset 50000 rows fetch next 20 rows only;
写法3：双重top + not in（旧版兼容写法）
select top 20 *
from orders
where orderid not in (
select top 50000 orderid
from orders
order by orderdate desc, orderid
)
order by orderdate desc, orderid;
写法4：双重top + 左连接（另一种旧版写法）
select top 20 o.*
from orders o
left join (
select top 50000 orderid
from orders
order by orderdate desc, orderid
) t on o.orderid = t.orderid
where t.orderid is null
order by o.orderdate desc, o.orderid;
写法5：游标分页（keyset pagination / seek method）
利用上一页最后一条记录的排序键值进行定位：
-- 假设上一页最后一条是 orderdate='2024-01-15', orderid=12345 select top 20 * from orders where (orderdate < '2024-01-15') or (orderdate = '2024-01-15' and orderid > 12345) order by orderdate desc, orderid;
二、性能对比测试
测试环境
- sql server 2019，16核cpu，64gb内存
- 表结构：
orders(orderid int pk, orderdate datetime, customerid int, amount decimal) - 数据量：1,000,000行
- 索引：
ix_orders_orderdate(orderdate desc, orderid)
测试结果（平均耗时，单位：毫秒）
| 分页位置 | offset-fetch | row_number | 双重top(not in) | 双重top(left join) | 游标分页 |
|---|---|---|---|---|---|
| 第1页 | 3 | 3 | 3 | 3 | 3 |
| 第100页 | 15 | 18 | 22 | 25 | 3 |
| 第1000页 | 120 | 135 | 180 | 210 | 3 |
| 第10000页 | 1100 | 1250 | 2300 | 2800 | 3 |
| 第50000页 | 5500 | 6200 | 12000+ | 15000+ | 3 |
关键发现
- offset-fetch 和 row_number 性能相近：它们生成的执行计划几乎相同，都需要扫描前n行然后丢弃。
- 双重top写法性能最差：尤其在大偏移量时，因为需要两次读取大量数据。
- 游标分页性能惊人：无论翻到多少页，耗时始终稳定在几毫秒级别。
三、深入分析：为什么游标分页最快？
offset-fetch 的执行原理
select * from orders order by orderdate desc, orderid offset 50000 rows fetch next 20 rows only;
执行计划解读：
- 扫描索引
ix_orders_orderdate，从第一行开始 - 逐行计数，跳过前50000行
- 读取接下来的20行
- 复杂度：o(n) ，n为跳过的行数
这意味着：翻页越深，读取和丢弃的行越多。
游标分页的执行原理
select top 20 * from orders where (orderdate < '2024-01-15') or (orderdate = '2024-01-15' and orderid > 12345) order by orderdate desc, orderid;
执行计划解读：
- 在索引上直接定位到
orderdate='2024-01-15', orderid=12345的位置 - 向后扫描20行
- 复杂度：o(log n + m) ，m为返回行数（通常很小）
关键在于：它利用了索引的b-tree结构直接跳转到目标位置，而不是逐行遍历。
四、各种写法的适用场景
1. offset-fetch —— 通用选择（sql 2012+）
优点：
- 语法简洁直观
- 性能可接受（中小规模数据）
- 支持任意排序、任意跳转页码
缺点：
- 大数据量下，深分页性能急剧下降
- 不适合“跳转到最后一页”的场景
最佳实践：
-- 加上 with (nolock) 提高并发性能（允许脏读时） select * from orders with (nolock) order by orderdate desc, orderid offset 0 rows fetch next 20 rows only;
2. row_number() —— 兼容旧版本（sql 2005+）
适用场景：
- 需要兼容sql 2008及更早版本
- 需要在分页的同时计算总记录数（count over）
with cte as (
select *, row_number() over (order by orderdate desc, orderid) as rownum,
count(*) over () as totalcount
from orders
)
select * from cte
where rownum between 50001 and 50020;
3. 游标分页 —— 高性能首选（大数据量+连续翻页）
适用场景：
- 数据量百万级以上
- 用户通常是连续翻页（如搜索引擎、社交媒体）
- 不需要随机跳转到任意页码
实现封装示例：
public class cursorpagination<t>
{
public t? lastsortvalue { get; set; } // 上一页最后一条的排序值
public int? lastid { get; set; } // 上一页最后一条的主键
public int pagesize { get; set; } = 20;
public string buildsql()
{
if (lastsortvalue == null || lastid == null)
{
// 第一页
return $"select top {pagesize} * from orders order by orderdate desc, orderid";
}
else
{
return $@"select top {pagesize} * from orders
where (orderdate < '{lastsortvalue}')
or (orderdate = '{lastsortvalue}' and orderid > {lastid})
order by orderdate desc, orderid";
}
}
}
4. 双重top写法 —— 仅作了解，不建议使用
性能最差，且逻辑复杂，没有任何优势。
五、性能优化进阶技巧
1. 索引设计至关重要
无论哪种分页方式，都需要一个覆盖排序字段的索引：
-- 如果经常按 orderdate desc, orderid 排序分页 create nonclustered index ix_orders_paging on orders (orderdate desc, orderid) include (customerid, amount); -- 包含列避免回表
对于游标分页，这个索引还能实现“索引定位”，效率极高。
2. 避免深分页的业务设计
很多场景其实不需要真正的“跳到第10000页”。可以考虑：
- 只提供“上一页/下一页”按钮（天然适合游标分页）
- 限制最大翻页数（如最多100页）
- 使用搜索代替浏览
3. 缓存总记录数
-- 只在第一次查询时计算总数，后续分页复用 select count(*) from orders; -- 缓存起来
4. 使用 fast 查询提示
select * from orders order by orderdate desc, orderid offset 0 rows fetch next 20 rows only option (fast 20); -- 优先快速返回前20行
5. 并行查询优化
对于大表，可以启用并行计划：
select * from orders order by orderdate desc, orderid offset 50000 rows fetch next 20 rows only option (maxdop 4); -- 使用4个cpu并行
六、综合推荐方案
场景1：数据量 < 10万，需要随机跳页
使用 offset-fetch，语法简洁，性能足够。
场景2：数据量 10万~100万，需要随机跳页
使用 row_number() ，配合索引优化，性能可控。
场景3：数据量 > 100万，只需连续翻页
使用游标分页（keyset pagination） ，性能最优。
场景4：数据量 > 100万，必须随机跳页
使用 offset-fetch + 限制最大翻页数，或者考虑引入elasticsearch等搜索引擎。
七、总结
| 分页方式 | 性能等级 | 适用数据量 | 跳页支持 | sql版本要求 |
|---|---|---|---|---|
| offset-fetch | ★★★★☆ | 中小规模 | ✅ | 2012+ |
| row_number | ★★★★☆ | 中小规模 | ✅ | 2005+ |
| 游标分页 | ★★★★★ | 大规模 | ❌（需连续） | 所有版本 |
| 双重top | ★★☆☆☆ | 小规模 | ✅ | 所有版本 |
最终结论：
- 如果你用的是sql server 2012以上版本，默认选 offset-fetch
- 如果你的数据量很大且用户是连续翻页，毫不犹豫用游标分页
- 永远不要为了“省事”使用双重top写法
记住：没有最好的分页方式，只有最适合你业务场景的分页方式。根据数据量和用户行为选择合适的方案，再配合合理的索引设计，才能真正做到高效分页。
到此这篇关于sql server中分页查询多种写法全面对比的文章就介绍到这了,更多相关sql分页查询内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论