1. 深度分页问题的本质剖析当我们在MySQL中执行类似SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10这样的查询时表面上看只是获取第100万条记录开始的10条数据但MySQL的实际执行过程却远比这复杂。问题的核心在于MySQL的LIMIT机制并非直接跳转到offset位置而是需要先扫描offsetN条记录然后丢弃前offset条只返回最后的N条。我曾在一个电商系统的订单查询模块中遇到过这个问题当用户翻到第500页时页面响应时间从正常的200ms骤增到8秒以上。通过EXPLAIN分析发现虽然id字段有主键索引但查询优化器却选择了全表扫描。这是因为当offset值超过某个阈值时通常为总行数的30%MySQL优化器会认为全表扫描比使用索引更高效。2. 四种核心优化方案对比2.1 游标分页基于ID范围查询这是最简单有效的优化方案适用于按主键或唯一索引排序的场景SELECT * FROM t_order WHERE id 上一页最后一条记录的ID ORDER BY id LIMIT 10;优势分析完全避免OFFSET带来的性能问题查询时间稳定不受页码影响只需在B树上做范围查找时间复杂度O(logN)实战注意事项需要前端配合传递last_id参数不支持随机跳页只能顺序翻页当排序字段不是ID时需要建立联合索引如(status, id)2.2 子查询优化方案SELECT * FROM t_order WHERE id ( SELECT id FROM t_order ORDER BY id LIMIT 1000000, 1 ) ORDER BY id LIMIT 10;执行原理子查询先利用索引快速定位到offset位置主查询基于定位到的ID做范围扫描性能对比测试1000万行数据方案执行时间扫描行数原始LIMIT12.8s1000010子查询0.18s102.3 延迟关联优化SELECT t1.* FROM t_order t1 INNER JOIN ( SELECT id FROM t_order ORDER BY id LIMIT 1000000, 10 ) t2 ON t1.id t2.id;优化原理内层查询只获取ID利用覆盖索引避免回表外层通过JOIN快速获取完整记录适用场景需要返回完整实体信息的查询多条件复合查询需建立合适联合索引2.4 覆盖索引终极方案SELECT id, order_no, status FROM t_order ORDER BY id LIMIT 1000000, 10;关键点只查询索引包含的字段避免回表操作需要提前规划业务需要的字段3. 复合查询场景的进阶优化3.1 多条件过滤查询优化对于WHERE status1 ORDER BY create_time LIMIT 1000000,10这类查询建立联合索引(status, create_time, id)使用延迟关联SELECT t1.* FROM t_order t1 INNER JOIN ( SELECT id FROM t_order WHERE status1 ORDER BY create_time, id LIMIT 1000000, 10 ) t2 ON t1.id t2.id;3.2 大数据量下的分页策略当数据量超过1亿时建议采用以下架构方案读写分离分页查询走从库数据分片按时间或ID范围水平分表二级索引使用Elasticsearch等建立搜索索引结果缓存缓存前N页查询结果4. 生产环境实战经验4.1 性能对比测试数据在AWS r5.2xlarge机型8vCPU64GB内存上的测试结果数据量方案页码耗时扫描行数1000万原始LIMIT第1页0.02s101000万原始LIMIT第50万页8.7s50000101000万延迟关联第50万页0.25s50000101000万游标分页连续翻页0.01s104.2 常见踩坑点索引失效陷阱排序字段与WHERE条件字段未建立合适联合索引使用OR条件导致索引失效对索引列使用函数操作隐式类型转换-- user_id是varchar类型但传入了数字 SELECT * FROM orders WHERE user_id 10086 LIMIT 100000,10错误使用FORCE INDEX强制使用索引可能导致优化器无法选择更优执行计划应优先通过优化索引解决问题5. 架构级解决方案5.1 预计算分页策略对于相对静态的数据可以采用预计算方案定时任务预先计算各页的起始ID将分页元数据存入Redis查询时直接获取ID范围5.2 搜索引擎整合将分页查询需求转移到Elasticsearch建立业务数据的全文索引利用ES的游标分页(scroll/search_after)实现方案SearchRequest request new SearchRequest(orders); SearchSourceBuilder sourceBuilder new SearchSourceBuilder(); sourceBuilder.query(QueryBuilders.matchAllQuery()); sourceBuilder.size(10); sourceBuilder.sort(SortBuilders.fieldSort(create_time)); request.source(sourceBuilder); SearchResponse response client.search(request, RequestOptions.DEFAULT);5.3 分布式环境下的分页挑战在分库分表场景下传统分页方式完全失效。解决方案全局索引表维护所有分片的ID映射分片查询内存排序-- 在各分片执行 SELECT * FROM t_order_1 ORDER BY id LIMIT 1000010,10; -- 在内存中合并排序后取10条使用ShardingSphere等中间件提供的分页优化6. 监控与调优建议慢查询监控配置# my.cnf配置 slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1关键指标预警分页查询耗时500ms分页offset值10000全表扫描的分页查询执行计划分析要点EXPLAIN SELECT * FROM t_order ORDER BY id LIMIT 1000000,10;关注type列应出现range或index检查Extra列避免出现Using filesort确认key列使用了正确的索引在实际项目中我建议将分页查询封装成统一的DAO层方法根据不同的参数动态选择最优分页策略。同时要建立完善的分页查询监控体系当发现深度分页查询时及时通知开发人员优化。记住没有放之四海皆准的最优方案需要根据具体业务场景选择合适的分页策略。 
长河君 · 2026/10/31 7:43:59 · 阅读 1.2k MySQL深度分页优化：原理与实战方案
1. 深度分页问题的本质剖析当我们在MySQL中执行类似SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10这样的查询时表面上看只是获取第100万条记录开始的10条数据但MySQL的实际执行过程却远比这复杂。问题的核心在于MySQL的LIMIT机制并非直接跳转到offset位置而是需要先扫描offsetN条记录然后丢弃前offset条只返回最后的N条。我曾在一个电商系统的订单查询模块中遇到过这个问题当用户翻到第500页时页面响应时间从正常的200ms骤增到8秒以上。通过EXPLAIN分析发现虽然id字段有主键索引但查询优化器却选择了全表扫描。这是因为当offset值超过某个阈值时通常为总行数的30%MySQL优化器会认为全表扫描比使用索引更高效。2. 四种核心优化方案对比2.1 游标分页基于ID范围查询这是最简单有效的优化方案适用于按主键或唯一索引排序的场景SELECT * FROM t_order WHERE id 上一页最后一条记录的ID ORDER BY id LIMIT 10;优势分析完全避免OFFSET带来的性能问题查询时间稳定不受页码影响只需在B树上做范围查找时间复杂度O(logN)实战注意事项需要前端配合传递last_id参数不支持随机跳页只能顺序翻页当排序字段不是ID时需要建立联合索引如(status, id)2.2 子查询优化方案SELECT * FROM t_order WHERE id ( SELECT id FROM t_order ORDER BY id LIMIT 1000000, 1 ) ORDER BY id LIMIT 10;执行原理子查询先利用索引快速定位到offset位置主查询基于定位到的ID做范围扫描性能对比测试1000万行数据方案执行时间扫描行数原始LIMIT12.8s1000010子查询0.18s102.3 延迟关联优化SELECT t1.* FROM t_order t1 INNER JOIN ( SELECT id FROM t_order ORDER BY id LIMIT 1000000, 10 ) t2 ON t1.id t2.id;优化原理内层查询只获取ID利用覆盖索引避免回表外层通过JOIN快速获取完整记录适用场景需要返回完整实体信息的查询多条件复合查询需建立合适联合索引2.4 覆盖索引终极方案SELECT id, order_no, status FROM t_order ORDER BY id LIMIT 1000000, 10;关键点只查询索引包含的字段避免回表操作需要提前规划业务需要的字段3. 复合查询场景的进阶优化3.1 多条件过滤查询优化对于WHERE status1 ORDER BY create_time LIMIT 1000000,10这类查询建立联合索引(status, create_time, id)使用延迟关联SELECT t1.* FROM t_order t1 INNER JOIN ( SELECT id FROM t_order WHERE status1 ORDER BY create_time, id LIMIT 1000000, 10 ) t2 ON t1.id t2.id;3.2 大数据量下的分页策略当数据量超过1亿时建议采用以下架构方案读写分离分页查询走从库数据分片按时间或ID范围水平分表二级索引使用Elasticsearch等建立搜索索引结果缓存缓存前N页查询结果4. 生产环境实战经验4.1 性能对比测试数据在AWS r5.2xlarge机型8vCPU64GB内存上的测试结果数据量方案页码耗时扫描行数1000万原始LIMIT第1页0.02s101000万原始LIMIT第50万页8.7s50000101000万延迟关联第50万页0.25s50000101000万游标分页连续翻页0.01s104.2 常见踩坑点索引失效陷阱排序字段与WHERE条件字段未建立合适联合索引使用OR条件导致索引失效对索引列使用函数操作隐式类型转换-- user_id是varchar类型但传入了数字 SELECT * FROM orders WHERE user_id 10086 LIMIT 100000,10错误使用FORCE INDEX强制使用索引可能导致优化器无法选择更优执行计划应优先通过优化索引解决问题5. 架构级解决方案5.1 预计算分页策略对于相对静态的数据可以采用预计算方案定时任务预先计算各页的起始ID将分页元数据存入Redis查询时直接获取ID范围5.2 搜索引擎整合将分页查询需求转移到Elasticsearch建立业务数据的全文索引利用ES的游标分页(scroll/search_after)实现方案SearchRequest request new SearchRequest(orders); SearchSourceBuilder sourceBuilder new SearchSourceBuilder(); sourceBuilder.query(QueryBuilders.matchAllQuery()); sourceBuilder.size(10); sourceBuilder.sort(SortBuilders.fieldSort(create_time)); request.source(sourceBuilder); SearchResponse response client.search(request, RequestOptions.DEFAULT);5.3 分布式环境下的分页挑战在分库分表场景下传统分页方式完全失效。解决方案全局索引表维护所有分片的ID映射分片查询内存排序-- 在各分片执行 SELECT * FROM t_order_1 ORDER BY id LIMIT 1000010,10; -- 在内存中合并排序后取10条使用ShardingSphere等中间件提供的分页优化6. 监控与调优建议慢查询监控配置# my.cnf配置 slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1关键指标预警分页查询耗时500ms分页offset值10000全表扫描的分页查询执行计划分析要点EXPLAIN SELECT * FROM t_order ORDER BY id LIMIT 1000000,10;关注type列应出现range或index检查Extra列避免出现Using filesort确认key列使用了正确的索引在实际项目中我建议将分页查询封装成统一的DAO层方法根据不同的参数动态选择最优分页策略。同时要建立完善的分页查询监控体系当发现深度分页查询时及时通知开发人员优化。记住没有放之四海皆准的最优方案需要根据具体业务场景选择合适的分页策略。