OFFSET-FETCH 分页在数据量超10万行后性能急剧下降，因OFFSET需全扫描前N行；应改用键集分页（如WHERE created_at

直接说结论：用 OFFSET-FETCH 写分页在数据量超过 10 万行后就会明显变慢，尤其翻到第 1000 页之后，逻辑读暴增、tempdb 溢出、响应时间跳到秒级——这不是 SQL 写得不对，是它底层机制决定的。真要处理大批量数据，得换思路，不是调参数能救的。

为什么 OFFSET-FETCH 在大数据量下越往后越慢

因为 OFFSET 10000 ROWS 不是“跳到第 10001 行”，而是让 SQL Server 从头开始逐行扫描、计数、跳过前 10001 行，再取下一批。哪怕你只想要 20 条，它也得把前面所有行都过一遍。

• 排序字段没索引？直接全表扫描 + Sort 算子溢出到 tempdb
• ORDER BY 里用了 LEFT JOIN 后的字段（比如 u.name），该字段可能为 NULL，导致排序不稳定，OFFSET 可能漏行或重复
• WHERE 条件没覆盖索引，或者用了函数（如 YEAR(created_at)），索引失效，性能雪崩

用键集分页（Keyset Pagination）替代 OFFSET-FETCH

核心是放弃“第 N 页”这种基于偏移量的思维，改用“从上次最后一条记录的主键/时间戳继续往后查”。它不依赖行号，只靠索引定位，查询复杂度接近 O(log N)。

• 必须确保分页字段有高效索引，例如按 created_at DESC, id DESC 排序，就建 CREATE INDEX IX_orders_created_id ON orders(created_at DESC, id DESC)
• 避免用 NOT IN 或子查询做游标判断，比如 WHERE id NOT IN (SELECT TOP 1000 id...)，大数据下子查询会退化成全表扫描
• 推荐写法是：每次查 WHERE created_at ，配合复合索引可走 Seek
• 前端需传回上一页最后一条的 created_at 和 id，不能只传页码——这是键集分页的契约，绕不开

存储过程中怎么安全拼接动态排序和条件

排序字段、表名、WHERE 条件如果来自参数，不用 QUOTENAME() 包裹就是 SQL 注入温床，哪怕只给内部系统用也不保险。

• 表名、列名必须用 QUOTENAME(@TableName) 和 QUOTENAME(@SortField)，否则传入 orders; DROP TABLE orders-- 就完蛋
• WHERE 条件字符串建议统一拼在变量里，比如 SET @where = 'status = 1 AND amount > 100'，然后分页查询和 COUNT(*) 都复用它，避免两边条件不一致导致总数和数据对不上
• 别用 COUNT(*) OVER() 想“一次查出总数+数据”，执行计划仍要扫全表+加窗口聚合，比分开查还重
• 如果业务允许弱一致性（如后台报表），可在查询中加 WITH (NOLOCK)，但得清楚脏读风险——比如刚插入又被回滚的订单可能短暂出现在列表里

总条数要不要每次都算？

要，但得控制代价。对千万级表执行 SELECT COUNT(*) 是个阻塞操作，尤其并发高时容易拖垮整个库。

• 硬限制最大可查页码，比如 IF @PageIndex > 500 BEGIN RAISERROR('Page too deep', 16, 1) RETURN END，防恶意或误操作触发深分页
• 后台类场景可缓存总条数：用带时间戳的物化视图或定时任务更新统计表，查分页时只读缓存值，误差容忍几秒到几分钟
• 如果必须实时，且 WHERE 条件固定，可建带过滤的索引视图（Indexed View），让 COUNT(*) 走索引查找而非扫描

真正难的不是写出能跑的分页存储过程，而是判断什么时候该放弃“页码”这个概念——当用户翻到第 5000 页时，他大概率不是在找数据，是在测试你的系统底线。这时候返回“请用搜索或筛选”比返回空结果或超时更诚实。