1. 深度分页问题的本质与表现当我们需要从MySQL数据库中获取大量数据时通常会使用LIMIT offset, size语法进行分页查询。但随着页码的深入特别是offset值超过10万后查询性能会出现断崖式下降。我曾在一个用户行为分析系统中遇到过这样的场景当查询第500页数据每页20条时响应时间从最初的200ms骤增到8秒以上。这种现象背后的原理是MySQL在执行LIMIT 100000, 20时会先读取100020条记录然后丢弃前10万条只返回最后的20条。这个读取后丢弃的过程造成了巨大的资源浪费。通过EXPLAIN分析可以看到即使使用了索引type列仍显示为index而非range说明引擎仍在进行全索引扫描。2. 主流解决方案对比与选型2.1 游标分页Cursor-based Pagination这是目前最推荐的解决方案尤其适合无限滚动场景。其核心思想是记录上一页最后一条记录的ID或时间戳下页查询时直接定位SELECT * FROM orders WHERE id 上一页最后ID ORDER BY id ASC LIMIT 20;我在电商订单系统中实测发现无论翻到第几页查询时间都稳定在50ms以内。但需要注意必须使用唯一且有序的字段作为游标不支持随机跳页如直接从第1页跳到第100页新增数据可能导致少量记录重复或遗漏2.2 延迟关联Delayed Join对于需要复杂WHERE条件的情况可以先用子查询获取主键再关联原表SELECT t.* FROM table t JOIN (SELECT id FROM table WHERE condition ORDER BY id LIMIT 100000,20) tmp ON t.id tmp.id;在某次日志分析项目中这种方案使查询时间从12秒降到0.3秒。原理是子查询只需扫描索引避免了回表操作。2.3 覆盖索引优化如果查询字段都包含在某个索引中可以直接使用该索引避免回表-- 假设有联合索引(status, create_time, id) SELECT id, status, create_time FROM orders WHERE status paid ORDER BY create_time DESC LIMIT 100000, 20;3. 特殊场景下的解决方案3.1 基于业务时间的分页对于按时间排序的场景如新闻、微博可以结合游标和分区SELECT * FROM articles WHERE publish_time 上一页最小时间 ORDER BY publish_time DESC LIMIT 20;配合按天/周的分区表设计可以进一步提升性能。我在内容管理系统中的实测显示百万数据下查询稳定在100ms内。3.2 预计算分页结果对于报表类应用可以在后台定时计算并缓存分页结果。某金融系统采用Redis有序集合存储预计算的页数据前端查询直接命中缓存响应时间控制在10ms内。4. 实战中的避坑指南COUNT(*)优化分页常伴随总数统计但COUNT(*)在InnoDB中很耗时。替代方案使用EXPLAIN的rows字段估算维护单独的计数表对于精度要求不高的场景直接显示1000条结果JOIN查询陷阱多表关联时确保ORDER BY字段来自驱动表。曾有个慢查询案例因为ORDER BY被关联表字段导致全表扫描改为驱动表字段后性能提升20倍。索引失效场景当使用LIMIT offset, size且offset过大时优化器可能放弃使用索引。这时需要用FORCE INDEX强制指定SELECT * FROM orders FORCE INDEX(create_time_idx) ORDER BY create_time DESC LIMIT 100000, 20;分布式ID问题如果使用雪花ID等分布式ID注意游标分页时的时间回拨问题。解决方案是在查询中添加ID和时间戳的双重校验。5. 性能对比实测数据在1000万条记录的测试表中各种方案的查询时间对比方案第1页第1万页第10万页内存消耗传统LIMIT2ms450ms4200ms高游标分页2ms3ms3ms低延迟关联5ms60ms550ms中覆盖索引1ms3ms5ms极低6. 架构层面的解决方案当单机MySQL性能达到瓶颈时可以考虑读写分离将分页查询路由到只读副本分库分表按照分页维度水平拆分如按用户ID哈希搜索引擎将数据同步到Elasticsearch等专业搜索工具在某社交平台项目中我们采用ES处理好友动态的分页查询性能比MySQL原生方案提升50倍。但需要注意数据一致性的维护成本。