前阵子帮同事做 SQLServer 的慢查询审核发现系统里一条很常见的分页语句成了头号性能瓶颈。用的是很多团队都在用的 ROW_NUMBER() 写法从一张 3000 万行的订单表里取第 100 万行附近的 10 条数据单次查询跑了 9 秒多。业务那边反馈列表页越来越卡翻到后面基本就是在转圈。今天干脆把这次 SQLServer 分页优化的完整过程整理出来顺便把 ROW_NUMBER() 分页存在的几个典型问题一次说清楚。如果你平时写后端接口、维护老系统或者正在为列表深分页发愁这篇应该能帮你省不少事。1. 这场分页优化的起因与核心诉求1.1 业务背景一个看起来很普通的分页需求这个项目是一个订单管理系统核心列表是“订单查询”每天有大量运营人员使用。原始需求不复杂订单列表按创建时间倒序排列每页 10 条用户可以通过页码跳转也可以点“上一页/下一页”。数据量在早期只有几十万行那时用 ROW_NUMBER() 分页完全没问题查询基本是毫秒级返回。但随着系统运行时间变长订单表累积到 3000 万行以上列表接口的响应时间开始肉眼可见地变差。最典型的报障场景是运营人员翻到列表第 5000 页以后页面要等很久才能加载出来甚至直接超时。开发同学一开始以为是前端渲染问题后来发现接口本身就在数据库层卡住了。定位到具体 SQL 之后看到的是这样一个典型写法SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY CreateTime DESC) AS RN, OrderId, OrderNo, CustomerId, Status, CreateTime FROM dbo.Orders ) t WHERE RN BETWEEN 1000001 AND 1000010 ORDER BY CreateTime DESC;这个写法在几十万条数据时确实好用逻辑也清晰但一旦表变大、页码变深性能会呈线性甚至超线性下降。我当时看到这条 SQL 的第一反应是这又是一个被 ROW_NUMBER() 的“表面简单”坑到的典型案例。1.2 定位问题的第一现场耗时与执行计划我先在测试环境复现了问题开了三个标准的分析开关SET STATISTICS IO ON; SET STATISTICS TIME ON; SET STATISTICS PROFILE ON;实际执行后拿到的数据让我印象深刻逻辑读超过 8 万次CPU 耗时接近 9 秒。执行计划里最显眼的两个算子一个是针对排序列的Sort排序另一个是对整张表或大范围索引的Scan扫描。由于ROW_NUMBER()需要先按CreateTime DESC给所有满足条件的数据排好序、编好号再交给外层子查询过滤RN BETWEEN 1000001 AND 1000010数据库必须把排序列和所有参与查询的列都处理一遍才“舍得”丢弃前面那 100 万行。更严重的是这条查询在CreateTime上居然没有合适的索引支撑。因为在原表上CreateTime只存在于一个复合索引的非首列位置排序时优化器评估后认为走索引还不如直接扫描整张聚集索引来得快。于是每次深分页查询都会触发一次全表级别的排序动作。这种场景下逻辑读的数量几乎是随着页数线性上涨数据量越往后翻代价越高。当时我心里也清楚这个问题的核心不在“要不要分页”而在于“用什么方式分页”。下面就把 ROW_NUMBER() 分页的机制和坑拆开聊。2. Row_Number()分页的机制与隐蔽短板2.1 一条ROW_NUMBER()分页SQL的执行路径拆解先看语法层面。ROW_NUMBER()是一个窗口函数它的作用是按照OVER()子句里指定的排序规则给每一行分配一个从 1 开始的递增序号。分页场景中开发人员通常用一个子查询把它包起来然后通过BETWEEN过滤序号区间达到“取第 N 页”的效果。逻辑上很简单但数据库执行时是另一回事。以 SQL Server 为例ROW_NUMBER() OVER (ORDER BY CreateTime DESC)在绝大多数情况下会触发排序运算。优化器拿到这个查询后至少要做三件事先定位到符合 WHERE 条件的数据范围再按排序列做全量排序最后把排序完的数据逐行标号。也就是说即使你最终只需要返回第 100 万页的 10 条数据数据库也必须先算出前 100 万条数据的完整顺序。这个逻辑用生活里的例子类比就是你需要翻到一本 5000 页书的第 4900 页但系统不允许你直接翻到那一页必须从第 1 页开始逐页数过去。数据量小的时候“逐页翻”还能接受一旦表里有几千万行这个操作就变成了一场灾难。2.2 “大偏移量”为什么会拖垮性能不少开发同学对 ROW_NUMBER() 分页的性能问题有耳闻但说不清到底为什么慢。我把数据层面上的原因归纳成三点第一排序代价无法规避。只要ORDER BY的列上没有合适的索引SQL Server 就需要在内存或 tempdb 里维护一个排序结构。深分页时参与排序的数据行不是 10 条而是所有满足条件的数据行。数据量越大内存授予可能不够还会把中间结果溢写到磁盘这个时候查询会慢到你怀疑人生。第二行号计算必须全量完成。窗口函数与普通聚合不同ROW_NUMBER()必须知道每一行的相对位置。尤其在RN BETWEEN 1000001 AND 1000010这个条件下数据库没有办法先“跳过前 100 万行”再给后面的行编号。它只能老老实实生成完整的行号集合再交给外层过滤。第三SELECT 列过多会放大IO开销。很多生产 SQL 会直接SELECT *导致排序之外还要做大量的键查找或数据页读取。即使你的逻辑读只有 3 万次但如果每次都要读取大字段比如备注、JSON 配置IO 消耗也会成倍增加。2.3 除了性能还有什么隐藏风险性能只是最明显的问题。实际工作中我还发现 ROW_NUMBER() 分页还有几个容易忽略的隐患。第一个隐患是排序结果不稳定。ROW_NUMBER()要求排序字段尽量唯一。如果ORDER BY CreateTime DESC而CreateTime存在大量相同值那每一行之间的相对顺序是不确定的。SQL Server 不保证在这种情况下的分页结果是稳定的跨页时容易出现“重复数据”或“丢失数据”也就是用户翻页时偶尔会看到之前已经看过的记录。第二个隐患是深分页对缓存和内存不友好。每次深分页都要触发大范围的排序和扫描这不仅拖慢了当前查询还可能把 buffer pool 里原本的热点数据挤出去造成整个库的并发性能波动。换句话说单个分页查询慢只是表象它还会拖累其他正常查询。第三个隐患是与 NOLOCK 等隔离级别组合时的连锁反应。有些老系统为了减少阻塞给分页查询加了WITH(NOLOCK)。但 ROW_NUMBER() 分页本身读取范围大NOLOCK 下遇到页分裂或数据变更更容易出现重复读或跳读让分页结果看起来“随机漂移”。这个问题排查起来相当隐蔽经常被误报为“系统有 bug”。3. 分页方案选型对比主流做法的取舍3.1 常见分页方案速览既然 ROW_NUMBER() 分页有问题那用什么替代我把实际项目中接触过的几种主流方案整理出来放在一起对比一下。方案写法特点深分页性能适用场景注意事项ROW_NUMBER()子查询灵活兼容 SQL Server 2005随偏移量线性下降通用小表、简单中等数据量大偏移量慎用排序字段必须唯一OFFSET/FETCH语法简洁SQL Server 2012性能依然会随偏移量下降中小数据量、需求简单不要与 TOP 混用逻辑容易乱键集分页KeysetWHERE 条件加游标值配合 TOP性能稳定基本不受页码影响大表、高并发列表、无限滚动无法按页码随机跳转需要传参临时表/表变量先存结果再分页首查开销大重复查询快报表、复杂聚合、多步处理临时表要合理建索引注意 tempdb应用层缓存把数据页缓存到 Redis 等最快但增加一致性成本数据量小、更新不频繁缓存失效策略要设计好从表里能看出没有绝对完美的分页方案。Row_Number() 分页适合的是“数据量可控、查询条件简单、能够接受大偏移量代价”的场景。一旦表数据量到了千万级别而且用户确实会翻到很深的页码键集分页往往是最务实的方案。3.2 键集分页Keyset Pagination为什么值得优先考虑键集分页的核心思路是不告诉数据库“我要第几页”而是告诉数据库“我要从哪条记录之后开始取”。用户点击“下一页”时前端把当前列表最后一条记录的排序键值传给后端后端再把查询改写成类似这样SELECT TOP (10) OrderId, OrderNo, CustomerId, Status, CreateTime FROM dbo.Orders WHERE CreateTime lastCreateTime -- 向下翻页 ORDER BY CreateTime DESC;这种写法下SQL Server 可以借助索引直接定位到lastCreateTime这条记录的位置然后从那个位置往后继续扫描即可。前面已经看过的数据完全不需要处理性能也不会随着翻页次数增加而线性恶化。键集分页的代价是不能再像传统分页那样提供“跳转到第 N 页”的功能。大多数 to B 系统的业务场景其实都不需要精确跳到第 5000 页用户更常用的就是“下一页”和“加载更多”。把产品需求从“页码跳转”改成“流式加载”收益远大于损失。3.3 OFFSET/FETCH 并不是万能解SQL Server 2012 之后提供的OFFSET/FETCH语法对比 ROW_NUMBER() 减少了一层子查询写起来更清爽SELECT OrderId, OrderNo, CustomerId, Status, CreateTime FROM dbo.Orders ORDER BY CreateTime DESC OFFSET 1000000 ROWS FETCH NEXT 10 ROWS ONLY;但要注意一个事实OFFSET/FETCH底层执行时依然需要扫描并跳过前面的 100 万行。它省掉的是行号计算和外层过滤的开销省不掉“跳过偏移量”的物理读取。所以如果偏移量达到百万级OFFSET/FETCH 也只是比 ROW_NUMBER() 快一些不会改变性能随页码增加而下降的总趋势。我实际测试过在同一个 3000 万行表上取出中间位置的数据OFFSET/FETCH 大概比 ROW_NUMBER() 快 20% 到 30%。这个提升在小数据量上可能感知不明显但在深分页场景下依然是杯水车薪。想彻底解决深分页问题必须把目光从“页码偏移”转向“游标定位”。还有一个容易踩的坑OFFSET/FETCH要求有ORDER BY而ORDER BY的列同样需要索引支撑。如果你不加索引就会看到执行计划里出现排序运算性能照样很差。3.4 索引设计才是分页的根基无论你最终选择哪种分页写法都绕不开索引。分页查询最理想的状态是通过一个精确定义的索引直接以范围扫描的方式拿回数据全程没有排序、没有查找。要达到这个状态需要满足两个条件第一WHERE 条件里用于过滤的列最好就是排序键本身或其前缀第二查询要返回的所有列尽量都包含在这个索引里避免回表。比如按CreateTime DESC排序分页最直接的索引设计就是CREATE NONCLUSTERED INDEX IX_Orders_CreateTime_Include ON dbo.Orders (CreateTime DESC) INCLUDE (OrderId, OrderNo, CustomerId, Status);这里把常用的返回列放进INCLUDE查询时就能走覆盖索引逻辑读会低到不可思议。当然索引不是越多越好每增加一个索引都会牺牲写入性能和存储空间。实际操作时我会先看业务列表页到底需要展示哪些列再决定要不要把全部列塞进 INCLUDE而不是无脑套模板。4. 实操过程一次完整的优化落地4.1 现状梳理与目标设定前面铺垫了这么多下面进入这次优化的实际操作环节。我当时给自己定的目标是把深分页查询从 9 秒优化到 200 毫秒以内同时不能明显影响写入性能。先梳理现状出问题的表结构大致如下CREATE TABLE dbo.Orders ( OrderId BIGINT IDENTITY(1,1) PRIMARY KEY, OrderNo VARCHAR(32), CustomerId INT, Status TINYINT, CreateTime DATETIME, Remark NVARCHAR(500) );现有的索引只有一个基于OrderId的聚集索引以及一个IX_Orders_CustomerId的非聚集索引。也就是说按CreateTime排序时没有现成索引可用。当时的查询除了分页条件还经常附带CustomerId cid之类的筛选。业务上也存在“按客户查订单列表”“按状态查订单列表”等场景但这次优化的主线是全局列表的分页。4.2 第一步为排序字段打造匹配索引我决定先解决排序列没有索引的问题。综合考虑业务中“大多数列表都按 CreateTime 倒序”的特点创建了这样的覆盖索引CREATE NONCLUSTERED INDEX IX_Orders_CreateTime_Desc ON dbo.Orders (CreateTime DESC, OrderId DESC) INCLUDE (OrderNo, CustomerId, Status);这里把OrderId追加到索引键里是为了让排序结果更稳定。因为CreateTime可能重复OrderId作为唯一递增列可以确保排序顺序完全确定避免翻页内容漂移。同时OrderNo、CustomerId、Status放到 INCLUDE 里让大多数分页查询都能直接覆盖不需要回聚集索引。创建这个索引后我重新跑了一遍原来的 ROW_NUMBER() 分页 SQL逻辑读立刻从 8 万多次降到了 2000 多次耗时也从 9 秒降到了 500 毫秒左右。这说明索引对 ROW_NUMBER() 分页同样有巨大帮助。但我也知道一旦页码继续往后翻逻辑读还是会继续涨上去。索引帮它“延寿”了却没法根治。4.3 第二步改写分页查询为键集分页为了让性能不随页码线性衰减我把接口的分页逻辑改成了键集分页。前端不再传当前页码而是传“上一页最后一条记录的 CreateTime 和 OrderId”。后端收到的参数类似lastCreateTime 2024-06-01 12:00:00、lastOrderId 98999123。查询改写成SELECT TOP (10) OrderId, OrderNo, CustomerId, Status, CreateTime FROM dbo.Orders WHERE CreateTime lastCreateTime OR (CreateTime lastCreateTime AND OrderId lastOrderId) ORDER BY CreateTime DESC, OrderId DESC;注意这个 WHERE 条件的写法。因为CreateTime可能重复不能只写CreateTime lastCreateTime否则会漏掉那些创建时间相同、但 OrderId 更小的记录。我把CreateTime和OrderId组成复合游标条件CreateTime与上一页最后一条相同记录的内部排序也能稳定衔接。上一页的逻辑也类似但查询方向相反。把ORDER BY改成升序然后条件反向取查出结果后在应用层倒序返回即可SELECT TOP (10) OrderId, OrderNo, CustomerId, Status, CreateTime FROM dbo.Orders WHERE CreateTime firstCreateTime OR (CreateTime firstCreateTime AND OrderId firstOrderId) ORDER BY CreateTime ASC, OrderId ASC;在我这次的实践中键集分页改写之后不管用户翻到第几页单次查询的逻辑读始终保持在 100 次以内耗时稳定在 20~50 毫秒和页码深浅完全无关。这才是真正的根治。4.4 第三步验证效果与回归优化完成后我在同一台测试库上对几种方案做了对比数据如下方案深分页耗时时长逻辑读备注优化前 ROW_NUMBER()约 9 秒8 万无排序索引全表排序ROW_NUMBER() 索引约 0.5 秒约 2000有覆盖索引但偏移量影响仍在OFFSET/FETCH 索引约 0.4 秒约 1800写法更简洁本质仍是跳过偏移键集分页 索引约 0.03 秒约 80深翻页性能稳定无累积开销这个结果也在预期之内。索引能解决“排序”的痛点但无法解决“偏移量越大、扫描越多”的物理事实。键集分页真正避开了深偏移量问题所以性能才能稳定。回归测试时我特别关注了两个点一是各种带筛选条件的列表比如按客户查、按状态查是否仍然能走索引二是写入性能是否因为新增索引而明显劣化。实测下来新增一个非聚集索引对写入的影响在可接受范围内关键业务的 INSERT 耗时增加不到 5%。对千万级表来说这个代价换来核心列表页的稳定体验是很划算的。5. 常见问题与排查技巧实录5.1 按非唯一字段排序导致分页内容乱跳这个问题我遇到不止一次。有团队用ORDER BY CreateTime DESC做 ROW_NUMBER() 分页结果用户反馈“翻下一页后上一页的某条数据又出现了一次”或者“有些数据永远等不到”。原因就是排序键不唯一。CreateTime精确到秒同一秒内可能插入多条订单。当排序键相同时SQL Server 不保证行的顺序是稳定的。每次查询的执行计划可能不同数据分布和并行度也可能不同行号顺序就可能在多个值之间抖动。解决办法很简单在排序键后面追加一个唯一列比如ORDER BY CreateTime DESC, OrderId DESC。这样每一行的位置是完全确定的分页结果才稳定。这个经验同样适用于键集分页的游标条件设计。5.2 加了索引却没走为什么有同学在做优化时加完索引一看执行计划发现还是扫描。常见原因有三个第一统计信息过期。数据量大增后旧的统计信息可能让优化器误判索引选择率太低于是认为扫描更便宜。解决办法是用UPDATE STATISTICS更新对应表的统计信息或者让统计信息的自动更新阈值更敏感。第二隐式类型转换。比如表里OrderId是 BIGINT查询条件却写成WHERE OrderId 98999123字符串常量会和字段类型不一致SQL Server 会在比较时对字段做隐式转换这会导致索引失效。排查时看执行计划里有没有CONVERT_IMPLICIT运算符即可。第三查询返回了过多列。如果索引只覆盖了 3 列而 SELECT 需要 10 列SQL Server 发现每条记录都要回表评估后可能选择直接扫聚集索引。这时候要么把查询列收窄要么把高频返回列加到索引的 INCLUDE 中。我在实际优化中遇到最多的是第三种情况。很多老系统喜欢SELECT *这对分页查询的索引设计非常不友好。优化的第一步往往不是加索引而是先和业务方确认列表到底需要展示哪些字段。5.3 深分页的另一种解法缓存与游标键集分页是最通用的方案但有些场景不能改业务逻辑必须支持页码跳转。这种时候如果数据量到了千万级我会建议从应用层另想办法。一种办法是对排序键做分页映射缓存。单独建一张小表按顺序存储排序键值比如把每页最后一条记录的OrderId缓存起来用户请求第 N 页时直接读取缓存的位置信息然后通过键集方式查数据。这种方案既能保留页码跳转又不让数据库承受大偏移量扫描。另一种办法是在 Redis 里缓存当前用户的前若干页结果。这个办法适合列表数据更新不频繁的场景。用户在短时间内来回翻页时数据直接从缓存返回数据库压力会小很多。但要注意缓存失效机制避免用户看到陈旧数据。5.4 排查分页慢的检查清单实操中遇到分页变慢我一般按下面的顺序排查。建议你把它存下来碰到问题直接照着做看 SQL 文本是不是用了ROW_NUMBER()且偏移量巨大是不是有SELECT *。看执行计划有没有Sort、Scan、Key Lookup有没有隐式转换。看 SET STATISTICS IO逻辑读是否随页码增长如果逻辑读高优先加覆盖索引。看排序键唯一性行顺序不稳定会让分页结果错乱。看业务需求是否真的需要“跳到第 N 页”能否改成“下一页/加载更多”。看表数据增长趋势如果每月新增百万行即使今天性能还行半年后也会出问题。这套流程下来大多数分页慢问题都能定位到根因。我个人在实际操作中的体会是分页优化这件事最重要的不是背住某个函数用法而是理解数据库在“跳过偏移量”这件事上付出的物理代价。如果你想在千万级数据量上做稳定的分页键集分页配合覆盖索引几乎是当前最省心也最可靠的一条路。当然具体到每张表、每个业务场景还是要结合数据分布和查询频率去权衡没有银弹但有好的排查思路和落地手段至少能让你在踩坑时快速走出来。