分页查询优化的核心在于抛弃传统OFFSET-LIMIT的笨重方式，转向基于索引的键集分页、延迟关联或覆盖索引，从而在数据量增长时保持查询性能的稳定。
很多开发者都有过这种体验：数据量还不大时分页查询跑得飞快，一旦数据突破百万甚至千万级，点击第100页就变成了一次漫长的等待，这背后的问题根源，往往不是服务器硬件不够,而是分页查询的实现方式本身就存在设计缺陷。
传统分页查询为什么越往后越慢
OFFSET-LIMIT的工作原理
大多数分页查询都长这样：
SELECT FROM table ORDER BY id LIMIT 20 OFFSET 1000;这条语句看起来简单，但数据库在执行时，会先扫描出前1020行，然后丢弃前1000行，只返回最后20行，随着OFFSET值增大，扫描的行数线性增长，但实际返回的行数始终不变，这种浪费在数据量较小时不明显，一旦OFFSET超过几十万行，性能就会急剧下降,因为大量扫描和排序操作白白消耗了CPU和IO资源。
深度分页的典型困境
当OFFSET值达到百万级或者更高时，数据库可能需要扫描上百万行数据来完成一次普通的分页请求，这直接导致两个后果：响应时间变得不可控，常常超过用户可接受的阈值；数据库负载飙升，影响其他读写操作，在一些电商或后台管理系统中，深度分页问题往往成为性能瓶颈的常客,尤其是当用户需要查看历史数据或翻到很靠后的页面时。
分页查询优化主流方案对比
键集分页：从根本上避免偏移
键集分页（Keyset Pagination）也叫游标分页或Seek Method，它的核心思路是“记住上一页最后一条记录的位置”，然后直接从该位置之后开始取数据,而不是通过偏移量来定位。
SELECT FROM table WHERE id > 上一页最后ID ORDER BY id LIMIT 20;这个方案的优势在于无论翻到第几页，查询扫描的行数始终等于每页返回的行数，不会因为页数增加而变慢，它尤其适合实时数据列表、Feed流、API接口等场景，因为不需要依赖OFFSET,结果具有一致性。
不过键集分页也有局限：它要求排序列必须是唯一且递增的，否则可能出现数据重复或遗漏，如果排序包含多个字段，需要保证排序组合的唯一性，实现起来会略复杂一些，在大多数主键递增的场景下,这是最推荐的分页方式。
延迟关联：减少回表成本
当查询需要返回大量字段，而分页又必须使用OFFSET时，可以考虑延迟关联，它的思路是先通过索引快速定位到当前页需要的主键ID,然后再用主键ID去关联原表取出完整行数据。
SELECT FROM table
INNER JOIN (
SELECT id FROM table ORDER BY id LIMIT 20 OFFSET 1000
) AS tmp ON table.id = tmp.id;这种方法的好处是：子查询部分可以充分利用索引，只扫描索引树，速度很快；外层查询再通过主键精确查找，避免了大范围回表扫描，在数据量中等且无法改用键集分页的场景下,延迟关联能显著提升分页效率。
覆盖索引：让查询不再回表
如果查询所需的字段全部在索引中，数据库可以直接从索引返回结果，完全跳过数据行，这就是覆盖索引，对于分页查询，如果SELECT列表只包含索引列，或者包含索引列加上主键，那么查询就可以完全在索引中完成,不需要额外的IO操作。
实际应用中，可以针对常用的分页查询建立复合索引，比如ORDER BY和WHERE条件涉及的字段组合，但需要注意索引不能太宽，否则维护成本会增加，覆盖面索引适合查询字段固定的场景,比如只显示ID和标题的列表页。
方案对比一览
| 方案 | 适用场景 | 主要优势 | 限制条件 |
|---|---|---|---|
| 键集分页 | 实时列表、API、无限滚动 | 扫描行数固定，性能稳定 | 排序列必须唯一且递增 |
| 延迟关联 | 大偏移量查询，需返回多字段 | 减少回表，提升分页效率 | 子查询仍可能需要扫描大量索引 |
| 覆盖索引 | 查询字段有限，且都在索引中 | 极致性能，无需访问数据行 | 索引设计受限，字段多时效果减弱 |
分页查询优化实战场景
电商商品列表的分页优化
电商网站的商品列表页通常面临两个挑战：排序字段多（价格、销量、上架时间），且用户经常翻到很靠后的页面，如果使用传统键集分页，排序字段可能不唯一，导致数据错乱，这时可以采用混合策略：排序字段使用时间+ID组合，确保唯一性；对于价格排序，可以用价格+ID组合，在列表页实现无限滚动（Scroll Pagination）时，键集分页几乎是标准选择,因为它能保证每次滚动加载都快速稳定。
后台管理系统的大数据量分页
后台管理系统中，数据量动辄百万级，且通常支持跳转到指定页码，键集分页在这里不适用，因为无法直接跳转，这时可以考虑延迟关联或覆盖索引，配合前端缓存，还有一个常见做法是限制最大可翻页数，比如只允许查看前100页，超出部分提示用户使用搜索或筛选条件，这种思路在电商后台、财务系统、日志平台中经常出现,能有效降低深度分页对数据库的压力。
分页查询效率低怎么办？
排查索引是否被正确使用
当分页查询变慢时，第一步应该用EXPLAIN或类似工具检查执行计划，看是否使用了索引，以及扫描行数是否异常，如果发现索引没有用到，或者进行了全表扫描，那优化方向就是调整索引，常见问题包括：WHERE条件中的字段没有索引，ORDER BY字段与索引顺序不匹配,或者使用了函数导致索引失效。
调整SQL写法与参数
如果索引已经合理，但性能仍然不理想，可以尝试调整SQL写法，限制查询返回的字段，只取需要的列；将ORDER BY和LIMIT的字段做成复合索引；在查询条件中增加范围过滤，减少每次分页需要扫描的数据量，对于MySQL，还可以调整sort_buffer_size等参数，但效果有限,关键还是从查询结构和索引设计入手。
考虑使用缓存或预计算
对于数据变化不频繁的分页查询，引入缓存是性价比很高的方案，将分页结果缓存到Redis或内存中，设定合理的过期时间，能大幅减少数据库压力，如果数据量极大且对实时性要求不高，还可以考虑预计算生成静态分页文件，或者使用搜索引擎（如Elasticsearch）来处理复杂排序和分页。
分页查询优化常见问题解答
分页查询优化是否适用于所有数据库？
键集分页、延迟关联、覆盖索引这些优化思路在主流关系型数据库（MySQL、PostgreSQL、SQL Server、Oracle）中都是通用的，只是具体语法和实现细节略有差异，对于NoSQL数据库，例如MongoDB，也有类似机制，比如使用游标分页代替skip+limit，核心思想是一致的：避免大偏移量,充分利用索引。
键集分页怎么处理排序字段重复的情况？
在排序字段有重复值时，键集分页可能导致数据遗漏或重复，解决方案是添加一个唯一性字段（如ID）作为排序的次要条件，确保整体排序稳定，具体做法是：在ORDER BY中同时指定排序字段和ID，在WHERE条件中同时比较排序字段和ID，实现精确的游标定位，例如在价格排序中，使用(price, id)作为组合排序，查询时用WHERE price > 上一页最大价格 OR (price = 上一页最大价格 AND id > 上一页最大ID)。
分页查询优化能提升多少性能？
在深度分页场景下，优化效果非常明显，对于百万级数据量，传统OFFSET分页到第1000页时可能需要几秒甚至更久，而改用键集分页后，响应时间通常能稳定在毫秒级，延迟关联在同样场景下也能将时间缩短到原先的十分之一甚至更低，性能提升幅度取决于数据量、索引设计、查询复杂度等因素，但行业共识是：一旦数据量超过几十万行,深度分页优化就是必须考虑的措施。
首发原创文章，作者：王坚，如若转载，请注明出处：https://test.idctop.com/article/556949.html
