记一次SQLServer的分页优化兼谈谈使用Row_Number()分页存在的问题

记一次SQLServer的分页优化兼谈谈使用Row_Number()分页存在的问题前言分页的“坑”从哪里来在开发Web应用或API接口时分页几乎是绕不开的需求。用户需要浏览大量数据时我们不可能一次性把所有数据都丢给前端而是通过分页按需加载。在SQL Server中最常见的分页方式莫过于使用ROW_NUMBER()函数配合BETWEEN或OFFSET/FETCH来实现。但最近我在优化一个老项目时发现一个“看似正常”的分页查询居然在数据量达到百万级后性能急剧下降甚至让数据库服务器的CPU飙升到90%。这篇文章就记录这次优化过程同时深入分析ROW_NUMBER()分页的潜在问题以及如何用更高效的方式替代它。## 问题场景一个“慢”分页查询项目是一个电商后台的订单列表表Orders有约500万条记录。前端需要按创建时间倒序分页每页20条。原有的SQL是这样写的sql-- 原有分页查询使用ROW_NUMBER() BETWEENDECLARE PageNumber INT 10000; -- 第10000页DECLARE PageSize INT 20;WITH OrderedOrders AS ( SELECT OrderID, CustomerName, OrderDate, TotalAmount, ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS RowNum FROM Orders)SELECT OrderID, CustomerName, OrderDate, TotalAmountFROM OrderedOrdersWHERE RowNum BETWEEN (PageNumber - 1) * PageSize 1 AND PageNumber * PageSizeORDER BY RowNum;看起来逻辑清晰先生成行号再取指定范围。但问题在于——当页码很大时比如第10000页这个查询需要先对整个表或全索引进行排序然后扫描到第200000行再丢弃前面的199980行只返回最后20行。这相当于白白做了大量的无用功。在实际测试中这个查询执行了8.3秒而用户期望的响应时间在1秒以内。## 问题根源ROW_NUMBER()分页的三大缺陷### 1. 全量排序与扫描ROW_NUMBER() OVER (ORDER BY ...)必须对所有参与排序的数据进行排序和编号即使你只需要最后20条。SQL Server优化器无法“聪明地”跳过前面的行因为它必须先知道每一行的行号才能过滤。### 2. 索引利用不充分虽然ORDER BY OrderDate DESC可以借助索引但ROW_NUMBER()本身会强制生成一个计算列导致索引查找变成索引扫描或排序操作。特别是当排序字段不是聚集索引时性能会更差。### 3. 分页偏移越大性能越差这是最核心的问题随着页码增大需要排序和丢弃的数据量线性增长。第1页很快第100页还行第10000页就灾难了。这种分页方式的时间复杂度是O(n)n是偏移量而不是O(1)。## 优化方案一使用键集分页Keyset Pagination键集分页的核心思想是记住上一页最后一条记录的位置然后从那个位置开始往后找下一页。这样就不需要计算行号直接利用索引定位。假设我们按OrderDate DESC和OrderID作为唯一键排序上一页最后一条记录的OrderDate是2024-01-15 10:30:00OrderID是12345。那么下一页的查询可以这样写sql-- 优化后的键集分页查询利用索引进行快速定位DECLARE LastOrderDate DATETIME 2024-01-15 10:30:00;DECLARE LastOrderID INT 12345;DECLARE PageSize INT 20;SELECT TOP (PageSize) OrderID, CustomerName, OrderDate, TotalAmountFROM OrdersWHERE -- 先按时间比较时间相同再按订单ID比较确保唯一性 (OrderDate LastOrderDate) OR (OrderDate LastOrderDate AND OrderID LastOrderID)ORDER BY OrderDate DESC, OrderID DESC;优化原理这个查询直接利用(OrderDate DESC, OrderID DESC)的复合索引进行范围查找不需要排序和扫描无用行。SQL Server可以快速定位到上一页结束的位置然后向后取20条。性能对比在同样的500万数据量下这个查询执行时间从8.3秒降到了0.02秒性能提升超过400倍。注意事项- 必须有一个唯一列如主键来打破平局否则可能丢失数据。- 前端需要传递上一页的最后一条记录的值不能直接传页码。- 不支持跳页直接跳到第10000页但大多数Web应用是顺序翻页完全可以接受。## 优化方案二使用OFFSET/FETCHSQL Server 2012如果你必须支持跳页或者无法改造前端传递键值可以使用OFFSET/FETCH语法它比ROW_NUMBER()更高效因为它能更好地利用索引。sql-- 使用OFFSET/FETCH实现分页支持跳页DECLARE PageNumber INT 10000;DECLARE PageSize INT 20;SELECT OrderID, CustomerName, OrderDate, TotalAmountFROM OrdersORDER BY OrderDate DESC, OrderID DESC -- 添加唯一列确保稳定性OFFSET (PageNumber - 1) * PageSize ROWSFETCH NEXT PageSize ROWS ONLY;虽然OFFSET/FETCH本质上也是先排序再跳过但SQL Server优化器在内部实现上比ROW_NUMBER()更优。它会尝试使用索引来跳过行而不需要生成完整的行号集合。性能对比在相同数据量下OFFSET/FETCH执行时间约为2.5秒虽然比键集分页慢但比ROW_NUMBER()的8.3秒好很多。适用场景- 数据量在百万级以内且对跳页有硬性需求。- 数据库版本是SQL Server 2012及以上。## 优化方案三使用游标分批适合后台批处理对于非用户交互的后台任务如数据导出、报表生成可以使用游标逐批处理避免一次性加载大量数据。sql-- 使用游标分批处理数据适合后台批处理DECLARE BatchSize INT 1000;DECLARE LastOrderID INT 0;DECLARE CurrentOrderID INT;WHILE 1 1BEGIN SELECT TOP (BatchSize) OrderID, CustomerName, OrderDate, TotalAmount INTO #TempBatch FROM Orders WHERE OrderID LastOrderID ORDER BY OrderID; IF ROWCOUNT 0 BREAK; -- 处理这批数据比如更新、导出等 -- ...业务逻辑... -- 获取本批最后一条记录的ID SELECT CurrentOrderID MAX(OrderID) FROM #TempBatch; SET LastOrderID CurrentOrderID; DROP TABLE #TempBatch;END这种方式的优势在于每次只处理一小批数据内存占用极低而且可以断点续传。如果处理过程中断下次可以从LastOrderID继续。## 总结如何选择分页方案经过这次优化我总结了几条经验1.优先选择键集分页对于大多数Web应用顺序翻页用键集分页能获得最好的性能。它利用索引定位时间复杂度是O(1)数据量再大也不怕。2.OFFSET/FETCH作为备选如果必须支持跳页且数据量在百万级以内可以使用OFFSET/FETCH。它比ROW_NUMBER()更高效但仍有性能瓶颈。3.避免使用ROW_NUMBER()分页特别是当页码很大时它会成为性能杀手。如果老代码中使用了这种方式建议重构为键集分页或OFFSET/FETCH。4.索引是分页的灵魂无论哪种方案确保排序字段有合适的索引。对于键集分页复合索引必须包含所有排序字段。5.不要迷信“通用方案”ROW_NUMBER()分页虽然写法简单但它牺牲了性能。在数据库优化中没有银弹需要根据业务场景选择最合适的方案。最后记住一个原则不要用计算的方式去模拟索引的功能而是直接利用索引去定位数据。这就是键集分页的精髓所在。