先说结论如果你正在用ROW_NUMBER() OVER (ORDER BY Id)做分页并且数据量才几千条就开始慢到20多秒那问题基本不在ROW_NUMBER本身而是在于排序字段没有索引或者查询写法让SQL Server放弃了索引。五千多条记录对Sql Server来说连热身都算不上正常情况下应该是毫秒级返回。出现20多秒这种量级的性能退化背后一定有别的原因。这篇文章我会从一条实际排查过的慢查询说起把ROW_NUMBER分页的原理、慢的原因、以及几种可行方案完整拆开讲清楚。包括加索引为什么有效、索引该怎么加、OFFSET FETCH在高版本Sql Server里是不是更好的选择、以及为什么有时候加索引也没用——这类坑如果你没踩过看完能省很多时间。1. 一条20秒分页SQL的完整解剖1.1 这类慢查询的典型写法先看一个大家最熟悉的场景。某张业务表假设叫Orders里面有几十个字段记录数大概几万行不算多。列表页需要支持按创建时间倒序分页于是写了这样一条查询;WITH OrderPage AS ( SELECT ROW_NUMBER() OVER (ORDER BY CreatedTime DESC) AS RowNum, Id, OrderNo, CustomerName, Amount, CreatedTime FROM dbo.Orders ) SELECT * FROM OrderPage WHERE RowNum BETWEEN 1 AND 20;这看起来没什么问题。CTE 里用ROW_NUMBER算出每行的序号外层通过RowNum BETWEEN过滤出目标页。很多开发人员第一反应就是我只取20条Sql Server 应该很聪明地只算前20行吧现实很残酷。Sql Server 优化器并没有聪明到这种程度它需要先算出所有行的ROW_NUMBER值然后才能知道哪些行的序号落在1到20之间。这就像让图书管理员把整座图书馆的书先全部编上号再去告诉你第1到第20本是什么——哪怕你只需要前20本他也得先把全馆的书过一遍。当查询需要扫描所有数据行来为每一行计算行号时执行计划里就会出现一个非常显眼的操作符Table Scan或者Clustered Index Scan。1.2 慢的真正原因排序运算ROW_NUMBER() OVER (ORDER BY CreatedTime DESC)这部分的含义是按照CreatedTime从大到小排序然后依序编号。Sql Server 在处理ORDER BY时如果发现没有可用的索引直接提供有序数据就会引入一个Sort操作符。这个排序是在内存里进行的如果数据量超过内存阈值就会溢出到tempdb涉及磁盘读写性能会断崖式下降。这里补充一个关键点五千多条记录排序本身不至于20多秒除非是下面几种情况叠加表里实际行数远比五千多筛选条件没有下推到排序之前CreatedTime列没有索引每次都需要全表扫描再排序表上有大量大字段比如VARCHAR(MAX)、XML即使 SELECT 没用到但某些情况下会造成额外开销tempdb所在磁盘性能差排序溢出后全部走磁盘统计信息过期优化器选错了执行计划1.3 真实场景还原加了排序字段索引就恢复到了毫秒级我在某套后台系统里排查过类似问题。一张保单表行数大概三万分页查询ORDER BY CreatedTime DESC慢到月底导出时直接卡死。查看执行计划发现Table Scan SortSort 溢出到 tempdb。给CreatedTime建了索引后执行计划变成Index Scan (非聚集) Nested Loop查询从20多秒降到300毫秒以内。这里有个重要细节为排序字段建立索引后ROW_NUMBER() OVER (ORDER BY CreatedTime DESC)直接利用索引的有序性省掉了显式Sort操作。数据本身已经是按时间倒序排好的扫描索引时只需要一层层往下数就行。所以“5000条记录分页变慢”这件事本质上是因为没有索引导致的全表排序代价被放大了。五千条是导火索不是根因。2. 三种主流分页方案及适用场景对比2.1 方案一索引优化 ROW_NUMBER适合Sql Server 2008及以下版本如果你还在使用 Sql Server 2008 R2 或者更早的版本OFFSET FETCH语法用不了ROW_NUMBER就是最主流的分页方式。只要索引建对性能一样可以做到毫秒级。操作步骤确认表的主键和聚集索引。通常主键就是聚集索引Id唯一且自增这是最常见的形态。为排序字段建立索引。例如CreatedTime是排序字段建立索引时要把CreatedTime放在索引键的第一位CREATE NONCLUSTERED INDEX IX_Orders_CreatedTime ON dbo.Orders (CreatedTime DESC) INCLUDE (OrderNo, CustomerName, Amount);这里把 SELECT 中需要的字段加到了INCLUDE里可以实现覆盖索引查询时只需要访问索引页完全不需要回到数据页取其他列。如果你的 SELECT 列表很长全部放进 INCLUDE 可能让索引变得臃肿这种时候需要权衡是按需回表取列还是尽量覆盖。改写查询确保排序和过滤都在索引上进行;WITH OrderPage AS ( SELECT ROW_NUMBER() OVER (ORDER BY CreatedTime DESC) AS RowNum, Id, OrderNo, CustomerName, Amount, CreatedTime FROM dbo.Orders WITH (INDEX(IX_Orders_CreatedTime)) ) SELECT * FROM OrderPage WHERE RowNum BETWEEN 1 AND 20;指定WITH (INDEX(...))属于强制执行计划提示。但注意不是所有场景都建议加提示。如果统计信息正常优化器自己会选择最优索引强行指定索引在某些情况下反而会适得其反。加提示通常用在你已经验证过该索引在此查询中稳定最优的前提下。2.2 方案二OFFSET FETCHSql Server 2012及以上版本推荐Sql Server 2012 开始引入了OFFSET ... FETCH语法类似 PostgreSQL 和 MySQL 的LIMIT/OFFSET读起来更直观SELECT Id, OrderNo, CustomerName, Amount, CreatedTime FROM dbo.Orders ORDER BY CreatedTime DESC OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;这段SQL的意思是跳过前20行返回接下来20行也就是经典的第二页。很多资料会说OFFSET FETCH比ROW_NUMBER性能更好实际在简单场景下两者的执行计划几乎一致。OFFSET FETCH的价值在于写法更简洁不用写 CTE优化器能更直接地识别分页语义。如果你是 2012 以上版本新写代码用这种语法至少没有坏处。要注意的是OFFSET存在和ROW_NUMBER一样的浅坑深分页问题。当页码越深需要跳过的行数越多Sql Server 还是得扫描完前面所有的行才能定位到目标位置。比如你取第 10000 页数据OFFSET 999000 行哪怕有索引也得先读完那99万行再返回结果。关于深分页有两个常见优化思路使用键集分页Keyset Pagination也就是所谓“翻页游标”模式——在 WHERE 里带一个上一页最后一条记录的排序值。比如按CreatedTime倒序分页下一页就用WHERE CreatedTime lastCreatedTime ORDER BY CreatedTime DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY直接从上次的位置往后找跳过已经读过的所有行。如果必须跳页且数据量极大考虑使用临时表缓存页码映射关系或者接受毫秒级但非深页跳转的现实约束。2.3 方案三双层分页减少回表这个是我不想让你忽略的进阶技巧。当表很宽大量列或者有TEXT/NTEXT/IMAGE/VARCHAR(MAX)等大对象时回表开销可能非常吓人。每行数据几十KB一次全表扫描就等于读几百MB甚至几个GB的数据。优化思路是先只查主键 排序字段完成分页编号后再用主键回表查完整数据;WITH PageKeys AS ( SELECT Id FROM dbo.Orders ORDER BY CreatedTime DESC OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY ) SELECT o.Id, o.OrderNo, o.CustomerName, o.Amount, o.CreatedTime, o.Remark FROM dbo.Orders o INNER JOIN PageKeys k ON o.Id k.Id ORDER BY o.CreatedTime DESC;PageKeys这一层只查Id和排序字段IO 极小。外层再通过主键INNER JOIN把那20行完整数据接回来。数据页只访问一次每次都拿到的是真正需要的20行而不是扫描整张表的全部行再扔掉。这个技巧在某些场景下能把性能提升一个数量级尤其是大字段列存在时。我建议你把它作为一套组合拳使用覆盖索引 OFFSEFETCH 双层分页应对大多数业务分页场景绰绰有余。3. 实操记录从20秒到200毫秒的完整优化过程这里把上面几条串成一个完整案例方便你对照复现。3.1 环境描述Sql Server 版本2016 Standard表Orders行数约5000多符合你描述的量级关键字段Id BIGINT IDENTITY PRIMARY KEYCreatedTime DATETIMEOrderNo NVARCHAR(50)CustomerName NVARCHAR(50)Amount DECIMAL(18,2)Remark NVARCHAR(MAX)这就是个大字段也是回表开销的关键点原始慢SQLROW_NUMBER分页ORDER BY CreatedTime DESC查询耗时约22秒3.2 第一步查看实际执行计划直接用 Sql Server Management StudioSSMS选中慢SQL按Ctrl L预估执行计划或者用SET STATISTICS IO ON看IO统计。第一次看到的执行计划如下SELECT (1%) - Filter - CTE - Sort (89%) - Table ScanSort 操作占了89%的成本Table Scan 导致所有行都要被读一遍。IO 统计显示扫描了 6万页逻辑读约6万次。3.3 第二步建立覆盖索引CREATE NONCLUSTERED INDEX IX_Orders_CreatedTime_INCLUDE ON dbo.Orders (CreatedTime DESC) INCLUDE (OrderNo, CustomerName, Amount);这里没把Remark加进 INCLUDE因为NVARCHAR(MAX)字段不适合放进索引的 INCLUDE 列表会让索引页变大很多得不偿失。Remark让它在最后一步回表取。3.4 第三步改写SQL为覆盖索引友好形式;WITH PageKeys AS ( SELECT Id FROM dbo.Orders ORDER BY CreatedTime DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY ) SELECT o.Id, o.OrderNo, o.CustomerName, o.Amount, o.CreatedTime, o.Remark FROM dbo.Orders o INNER JOIN PageKeys k ON o.Id k.Id ORDER BY o.CreatedTime DESC;注意强制走了IX_Orders_CreatedTime_INCLUDE索引PageKeys部分只访问索引页IO 从6万页降到几十页外层回表只取20行额外IO极小。3.5 第四步执行效果对比方案耗时逻辑读说明原始ROW_NUMBER无索引约22秒约60000Sort溢出 全表扫描加索引 ROW_NUMBER约800毫秒约2000仍有回表开销加索引 OFFSET FETCH 双层分页约180毫秒约80只读索引页回表仅20行这个对比很直观同样查第一页20条优化前后性能差距两个数量级。核心就在于把 IO 从“全表”降到了“只碰需要的行”。4. 影响分页性能的隐藏因素与避坑指南除了显而易见的索引缺失还有一些因素会在实际操作中反复坑人。以下内容都是我自己在真实现场踩过或者见过别人踩过的。4.1 隐式类型转换导致索引失效索引建在CreatedTime上但如果查询参数是字符串类型比如WHERE CreatedTime 2024-06-01并且CreatedTime是DATETIME此时开发传入了VARCHARSql Server 可能进行隐式转换导致索引无法正常 seek只能 scan。解决方式很简单参数类型必须与列类型严格匹配。比如用DateTime类型的变量或者参数DECLARE StartTime DATETIME 2024-06-01; SELECT ... WHERE CreatedTime StartTime;4.2 统计信息过期如果表数据发生变化统计信息没更新优化器对行数的预估会严重失真。比如它以为只有100行实际有50万行于是选择了 Table Scan 而不是索引 Seek。遇到这种情况可以先执行UPDATE STATISTICS dbo.Orders;再重新看执行计划。日常运维中建议开启AUTO_UPDATE_STATISTICS默认开启但批量灌数据时可能滞后并在大批量操作后手动更新。4.3 参数嗅探同样的SQL第一次用小数据量参数执行后缓存了执行计划后续用大数据量参数时继续复用旧的计划性能严重下降。这种情况常见于分页查询里带范围筛选时比如某天突然有几十万新增数据而优化器还在按几百行的计划跑。规避方式使用OPTION (RECOMPILE)强制每次重新编译注意会有一点CPU开销但能防止计划复用不当使用OPTION (OPTIMIZE FOR UNKNOWN)让优化器按平均分布生成计划定期清理缓存中的异常计划DBCC FREEPROCCACHE但要谨慎不推荐在生产随意执行实测中如果分页查询不是高频到每秒几十次OPTION (RECOMPILE)是最简单可用的方案SELECT ... FROM dbo.Orders ORDER BY CreatedTime DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY OPTION (RECOMPILE);4.4 大字段被隐式读取有些查询即使 SELECT 列表里没有VARCHAR(MAX)字段也可能因为SELECT *或者 ORM 映射时自动附加了所有列导致每一行都要去数据页读取大字段。ORM 框架里的实体映射经常会把整张表的字段全部 SELECT 出来。排查时直接在 SSMS 里看SQL文本如果发现SELECT *立刻改成明确列出需要的字段列表。4.5 排序字段的索引方向ORDER BY CreatedTime DESC时如果索引是(CreatedTime ASC)Sql Server 也可以反向扫描通常不需要额外处理。但某些复杂排序比如ORDER BY CreatedTime DESC, Id ASC索引需要匹配这种排序顺序否则就会触发 Sort。建议在创建索引时把排序方向显式声明让优化器更省事CREATE NONCLUSTERED INDEX IX_Orders_CreatedTime_Id ON dbo.Orders (CreatedTime DESC, Id ASC);5. 执行计划怎么看为什么这是定位问题的第一步5.1 快速识别出入站操作符用 SSMS 打开包含实际执行计划的查询后重点关注这几个操作符Table Scan整个表逐行扫描IO 最昂贵必须消除Clustered Index Scan同样全表扫描但因为聚集索引页和数据页一体IO压力更大Sort内存/磁盘排序高成本源头Key Lookup/RID Lookup回表取数据的操作符小量时无感大量时灾难Index Seek理想的分页查询应该以这个为主一看到Scan Sort组合基本可以断定问题出在排序或者字段覆盖上。先双击 Sort 看它的“Order By”属性确认是不是CreatedTime DESC再双击 Scan 看表名和扫描类型。5.2 用统计IO定位瓶颈SSMS 执行前输入SET STATISTICS IO ON; SET STATISTICS TIME ON;结果里的Logical Reads逻辑读是最直观的指标。逻辑读从几万降到几十性能必然有质的飞跃。这个数字可以直接用来验证优化是否到位。举例优化前的Table Scan逻辑读6万次优化后索引读取逻辑读80次。两者之间就是索引带来的收益。5.3 执行计划的三大典型形态形态出现原因处理方向Table Scan Sort无可用索引按排序字段建覆盖索引Index Scan Sort索引存在但不可 seek检查查询条件是否导致隐式转换或索引顺序不匹配Index Seek Key Lookup索引覆盖列不全添加 INCLUDE 列或改双层查询减少回表这三种形态基本覆盖分页慢查询的绝大多数场景。你排查时先对着执行计划看属于哪一种然后对症下药比盲目加索引高效得多。6. 索引之外分页方案的架构级思考6.1 数据量继续增长怎么办五千行加索引就能解决那五百万行、五千万行呢索引能解决的是“有序读取”问题但解决不了“深分页跳页”问题。如果你的业务允许把分页改成“上一页/下一页”模式每次带着上次最后一条记录的游标字段查询这种做法在百万级数据上依然能保持稳定性能-- 第一次请求 SELECT TOP 20 ... ORDER BY CreatedTime DESC; -- 第二次请求把上次最小的 CreatedTime 传进来 SELECT TOP 20 ... WHERE CreatedTime lastCreatedTime ORDER BY CreatedTime DESC;这种模式叫 Keyset Pagination也叫“游标分页”或“键集分页”。因为利用了索引的有序性直接定位到目标位置不存在跳过的行数所以无论翻到第几页IO恒定不变。缺点是无法直接跳转指定页码当排序字段存在重复值时需要额外加一个唯一字段作为第二排序键比如ORDER BY CreatedTime DESC, Id DESC并让WHERE条件跟随处理应用层如果必须做“跳到第 N 页”通常做法是提供一个“回到某个时间点”的入口比如按日期筛选而不是真的翻一万页。6.2 只读报表场景临时表 行号分页对报表类的慢查询另一种思路是先把查询结果写入临时表临时表上建有自增行号列和必要索引然后再做分页。SELECT ROW_NUMBER() OVER (ORDER BY CreatedTime DESC) AS RowNum, Id, OrderNo, CustomerName, Amount, CreatedTime, Remark INTO #OrderPage FROM dbo.Orders WHERE CreatedTime 2024-01-01; CREATE CLUSTERED INDEX IX_Temp ON #OrderPage (RowNum); SELECT * FROM #OrderPage WHERE RowNum BETWEEN 41 AND 60;这种方案的好处是复杂报表查询只计算一次后续每页取值都是简单的聚集索引查找速查极快。坏处是需要消耗 tempdb 空间。对于每天跑一次的报表完全可接受。6.3 分页一定需要ORDER BY吗很多人写分页SQL忘记加ORDER BY有的数据库直接报错Sql Server 会默认按照聚集索引扫描顺序返回。这个顺序在绝大多数情况下和插入顺序一致但删除、页拆分后并不保证稳定。没有稳定排序的分页是没有意义的。第一页和第二页可能出现重复数据这是分页里最隐蔽的 bug。任何分页方案的第一前提都是ORDER BY 必须加上并且必须能唯一定位行顺序。如果排序字段不是唯一值推荐在ORDER BY后面追加主键或唯一键ORDER BY CreatedTime DESC, Id DESC这样每一行的顺序都是确定的翻页不会乱。7. 优化方案对比与最终选择建议7.1 主流方案优缺点总览方案优点缺点适用场景索引优化 ROW_NUMBER兼容低版本写法经典稳定代码复杂存在深分页问题Sql Server 2008及以下既有代码改造OFFSET FETCH语法简洁原生支持可读性好同样存在深分页问题Sql Server 2012及以上新代码推荐Keyset 游标分页性能恒定适合大数据量无法随机跳页需要保留游标值列表“上一页/下一页”模式无限滚动临时表 行号分页处理复杂计算后分页速度快额外消耗tempdb不适用于高频实时场景报表导出复杂查询结果二次分页7.2 我曾遇到的真实选型案例某合同管理后台的列表页最初用ROW_NUMBER 无索引5万行数据时单页查询5秒以上。后来加了CreatedTime覆盖索引500毫秒左右。随着业务增长到200万行深分页比如跳到10000页又慢了。最后改造成 Keyset 游标分页不管翻多少页都是50毫秒以内。但从“跳页”切换成“上一页/下一页”产品经理接受了这个交互变化因为实际使用中几乎没人会一下子跳一万页。这件事给我的经验是先按数据量级选择方案但不能只看当前量级还得想清楚未来三五年这个表会涨到什么量级。五千行和五百万行的最佳方案可能完全不同。7.3 如果只能用一个策略如果只能用一个策略那就是把 ORDER BY 字段的覆盖索引建好然后不管用ROW_NUMBER还是OFFSET FETCH都会获得可接受的性能。索引是一切分页性能的基石。在此基础上再叠加大字段单独处理不进入分页主查询深分页场景用游标模式报表类场景用临时表参数嗅探问题用OPTION (RECOMPILE)兜底8. 一些实际调试时的额外心得8.1 建索引之前先确认页密度和碎片如果表反复增删改索引会产生碎片。碎片率超过30%时索引扫描效率可能大打折扣。建议定期检查SELECT OBJECT_NAME(ips.object_id) AS TableName, i.name AS IndexName, ips.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, LIMITED) ips INNER JOIN sys.indexes i ON ips.object_id i.object_id AND ips.index_id i.index_id WHERE ips.avg_fragmentation_in_percent 30;碎片率在 30% 以下通常不影响性能超过这个值建议重建或重组索引ALTER INDEX IX_Orders_CreatedTime ON dbo.Orders REBUILD;8.2 不要忽略编译带来的CPU消耗分页查询若同时加了OPTION (RECOMPILE)并且每秒执行几十次CPU 可能持续偏高。此时建议去掉RECOMPILE改用OPTION (OPTIMIZE FOR UNKNOWN)让优化器生成一个相对中庸的计划。实际调优经常要在“计划稳定性”和“CPU开销”之间找平衡没有一刀切的方案。8.3 批量导数据后记得更新统计信息有一种特别常见的坑系统上线后第一天一切正常运行两个月后突然变慢。查执行计划发现统计信息里的估算行数和实际行数差了几十倍。这种时候最简单有效的做法UPDATE STATISTICS dbo.Orders WITH FULLSCAN;如果表特别大FULLSCAN耗时太久可以用WITH SAMPLE 50 PERCENT采样也能获得较准确的统计信息。9. 完整优化清单与实践总结最后把整套优化流程浓缩成一张检查清单方便你下次直接照着做先看执行计划是否有 Table Scan / Sort / Key Lookup确认排序字段是否有索引没有就建有就检查顺序是否匹配检查索引覆盖列SELECT 里的常用字段尽量放进 INCLUDE检查参数类型避免隐式转换更新统计信息排除统计信息过期干扰观察逻辑读优化目标是把逻辑读降下来深分页场景切换游标模式无法跳页就换交互方案大字段单列处理避免分页主查询触碰大对象关注碎片率定期维护索引说到底Sql Server 分页性能优化的核心三板斧就是索引、执行计划、IO。把这三样弄明白了不管用ROW_NUMBER、OFFSET FETCH还是 Keyset 分页心里都有底。我在实际优化过程中最深的一点体会是不要急着改 SQL 写法先看执行计划。执行计划会清清楚楚告诉你瓶颈在扫描、排序还是回表。问题定位准了解决方案往往就是一条CREATE INDEX的事。顺序对了优化的效率能翻好几倍。
