MySQL 中如何解决深度分页的问题？

在 MySQL 中深度分页Deep Pagination是一个经典的性能瓶颈问题。问题现象当你执行类似以下的 SQL 时SELECT*FROMtable_nameLIMIT1000000,10;随着OFFSET偏移量的增大查询速度会显著变慢甚至导致超时。原因分析MySQL 的LIMIT offset, size机制是扫描数据库引擎通常是 InnoDB会扫描并读取前offset size行数据。丢弃丢弃前offset行数据。返回只返回剩下的size行数据。核心痛点当OFFSET很大时如 100 万MySQL 需要扫描 100 万 10 行数据然后丢弃 100 万行。如果查询是SELECT *MySQL 还需要通过主键索引回表Table Lookup去聚簇索引中读取整行数据这会产生大量的随机 I/O。即使使用了覆盖索引Covering Index扫描 100 万行索引节点本身也是昂贵的操作。解决方案针对深度分页主要有以下几种优化方案按推荐程度排序方案一延迟关联Deferred Join / 覆盖索引优化⭐最推荐原理利用覆盖索引Covering Index先只查询主键 ID然后再通过主键 ID 回表获取其他字段。因为索引树比数据表小得多扫描索引的代价远小于扫描整行数据。优化前SELECT*FROMtable_nameLIMIT1000000,10;优化后SELECTt1.*FROMtable_name t1INNERJOIN(SELECTidFROMtable_nameLIMIT1000000,10)t2ONt1.idt2.id;执行逻辑子查询SELECT id ... LIMIT ...只扫描索引树id通常是主键本身就是聚簇索引的一部分或者二级索引速度极快。子查询只返回 10 个 ID。外层查询通过这 10 个 ID 进行回表只读取 10 行完整数据。适用场景表数据量大深度分页。查询字段较多无法完全利用覆盖索引。方案二游标法 / 记录上次位置Seek Method / Keyset Pagination⭐性能最好原理不依赖OFFSET而是利用上一页最后一条数据的排序字段值通常是主键或时间戳作为查询起点。这种方式将“扫描 丢弃”变成了“范围查询”复杂度从 O(N) 降为 O(1)相对于偏移量。前提必须有排序字段通常是主键id或时间create_time且该字段有索引。数据不能频繁插入导致排序错乱或者业务允许。优化前-- 第 100000 页SELECT*FROMtable_nameORDERBYidLIMIT1000000,10;优化后假设上一页最后一条数据的id是1000010SELECT*FROMtable_nameWHEREid1000010ORDERBYidLIMIT10;执行逻辑直接利用索引定位到id 1000010的位置。直接读取接下来的 10 行。完全避免了扫描和丢弃前 100 万行数据。适用场景无限滚动加载Infinite Scroll如微博、朋友圈、电商列表。对“跳页”如直接跳到第 1000 页需求不强的场景。这是处理深度分页性能问题的终极方案。方案三限制最大页码业务限制原理从业务层面限制用户只能查看前 N 页例如前 100 页。大多数用户不会翻阅到第 1000 页如果确实需要查找深层数据应使用搜索Search功能代替分页。实施前端限制分页控件超过 100 页后隐藏或提示“请使用搜索功能”。后端拦截OFFSET过大的请求返回错误或提示。适用场景后台管理系统、普通列表展示。数据量极大但用户实际浏览深度有限的场景。方案四使用 ES 或搜索引擎架构升级原理如果数据量达到亿级且需要复杂的全文检索、深度分页、多条件组合查询MySQL 可能不再是最佳选择。将数据同步到Elasticsearch或Solr。优势ES 专为搜索设计处理深度分页虽然 ES 也有from size的限制但配合search_after机制可以高效处理。支持复杂的全文检索和聚合分析。适用场景海量数据亿级以上。需要全文检索、模糊查询、高亮显示。对实时性要求不是极高允许秒级延迟同步。方案对比总结方案性能实现难度适用场景缺点延迟关联高低通用场景必须支持跳页仍需扫描索引极深分页仍有损耗游标法 (Seek)极高中无限滚动、列表流不支持随机跳页如直接去第 500 页限制页码高低后台管理、普通列表用户体验受限无法查看深层数据ES 搜索引擎极高高海量数据、复杂检索架构复杂需维护同步链路最佳实践建议首选游标法如果你的业务是类似“加载更多”、“无限滚动”务必使用WHERE id last_id的方式这是性能提升最明显的方案。次选延迟关联如果必须支持用户输入页码跳转如page1000使用JOIN子查询优化。业务限制在 UI 上限制最大页码引导用户使用搜索功能。避免SELECT *只查询需要的字段减少回表数据量。索引优化确保ORDER BY的字段和WHERE条件字段有合适的联合索引。示例代码游标法实现假设有一个商品表products按id倒序排列第一次请求第一页SELECTid,name,priceFROMproductsORDERBYidDESCLIMIT10;-- 假设返回的最后一条 id 是 9990第二次请求下一页前端传入 last_id9990SELECTid,name,priceFROMproductsWHEREid9990-- 注意如果是倒序用 ORDERBYidDESCLIMIT10;这种方式无论翻多少页查询速度都保持在毫秒级。