在业务系统开发中，分页查询是最常见的功能之一。当数据表记录较少时，使用简单的limit语句即可快速完成数据返回。然而，随着业务发展，表数据量可能迅速膨胀至百万甚至千万级别。此时，传统的分页方式会暴露出严重的性能问题，尤其是深分页场景下，查询响应时间会呈指数级上升，甚至拖垮整个数据库实例。要解决这个痛点，必须深入理解数据库的执行机制，并采取针对性的优化策略。
传统分页查询的底层执行原理与性能瓶颈
大多数开发者在初期编写分页查询时，通常会使用类似 LIMIT offset, count 的语法。例如，每页展示20条数据，当查询第1000页时，SQL语句中的偏移量将达到19980。数据库在接收到这条指令时，并不能直接定位到第19981条记录。存储引擎层需要从索引的最左侧开始，逐行扫描并访问数据页，直到越过前19980条记录，最后才返回所需的20条数据。如果查询中包含非索引列，数据库还必须执行回表操作，即先在二级索引中找到主键，再去聚簇索引中读取完整的行数据。
SELECT * FROM orders ORDER BY create_time DESC LIMIT 19980, 20;
这种机制导致了深分页的性能灾难。偏移量越大，需要扫描和丢弃的数据行就越多。对于数据库而言，扫描并丢弃数据同样需要消耗大量的CPU和I/O资源。当并发请求增加时，这种低效查询会迅速占用数据库连接池，导致系统整体响应延迟。因此，要提升分页效率，核心思路就是减少不必要的行扫描，尽量避免在深偏移量下进行全表回表。
利用覆盖索引与延迟关联优化查询
针对深分页回表成本过高的问题，索引覆盖是一种非常有效的优化手段。如果一个查询只需要读取索引中的列，数据库就无需回表到聚簇索引，直接在二级索引树上即可获取结果。我们可以利用子查询先获取目标页的主键集合，然后再用主键去关联原表获取完整数据。这种方式被称为延迟关联。
SELECT t1.* FROM orders t1
INNER JOIN (
SELECT id FROM orders ORDER BY create_time DESC LIMIT 19980, 20
) t2 ON t1.id = t2.id;延迟关联的核心逻辑在于，先通过一个快速查询在索引树上筛选出当前页的主键ID。由于子查询只查询主键，可以利用覆盖索引特性，全程在内存中完成扫描，速度极快。拿到这批主键ID后，再通过JOIN操作去原表中精准提取对应的行数据。这样一来，原本需要回表19980次的操作，被缩减到了仅仅20次，性能提升立竿见影。
需要注意的是，延迟关联要求子查询的排序字段必须建立合适的索引。如果排序字段没有索引，数据库依然需要进行全表扫描和文件排序，优化效果将大打折扣。此外，如果表结构非常宽大，单行数据占用的空间很大，延迟关联减少回表次数带来的收益会更加显著。
游标分页：应对海量数据的终极方案
当数据量极其庞大，或者业务场景对实时性要求极高时，基于偏移量的分页无论怎么优化，都难以彻底消除扫描成本。此时，游标分页成为了更优的选择。游标分页放弃了传统的页码概念，转而记录上一页最后一条数据的某个特定标识（通常是自增主键或时间戳）。
SELECT * FROM orders
WHERE id < #{last_id}
ORDER BY id DESC LIMIT 20;在游标分页中，客户端每次请求需要携带上一页最后一条记录的ID。服务端接收到请求后，执行类似 WHERE id < last_id ORDER BY id LIMIT 20 的查询。由于主键索引的存在，数据库可以直接定位到last_id所在的位置，然后顺序向后读取20条记录。这种查询的时间复杂度是常数级别的，无论翻到第几页，查询速度都几乎一样快。
游标分页的代价是牺牲了随机跳页的能力。用户无法直接从第1页跳转到第500页，只能通过上一页、下一页进行顺序浏览。这种模式在移动端的信息流、社交媒体时间线等场景中非常适用。如果业务确实需要跳页功能，可以考虑结合缓存策略，将热门页面的结果缓存起来，或者限制允许跳转的最大页数。
业务层面的妥协与架构设计思考
技术上的优化往往有其极限，有时候解决深分页问题需要从业务层面寻找突破口。很多产品在设计时盲目提供全量数据的分页浏览功能，但实际上，用户极少会翻阅到几千页之后的数据。通过限制最大可访问页数，例如只允许查看前100页，可以从根本上杜绝深分页慢查询的出现。
对于历史数据的查询，冷热数据分离是一个值得考虑的架构方案。将活跃数据保留在主表中，将历史数据定期归档到单独的扩展表或数据仓库中。这样不仅减小了主表的体积，降低了索引维护成本，也使得常规分页查询始终在一个较小的数据集上进行。
在复杂的搜索与分页场景下，关系型数据库可能并不是最佳工具。如果查询条件包含多字段组合、全文检索等复杂逻辑，引入Elasticsearch等专门的搜索引擎是更好的选择。搜索引擎使用倒排索引处理分页，虽然其深分页同样存在性能问题，但通过search_after等机制，可以很好地支持游标分页模式，从而保障系统的高效稳定运行。