在后端开发的职业生涯中如果说有什么问题是每个程序员都会遇到且每次遇到都会让人血压飙升的那“深分页”绝对榜上有名。想象一下这个场景周五下午快下班时运营同学在后台点开了一个拥有上亿数据的订单列表然后为了找半年前的一笔异常订单疯狂地点击“下一页”或者直接在页码框里输入了100000并按下了回车。 紧接着数据库 CPU 瞬间打满慢查询告警像雪花一样飞到你的企业微信里线上其他业务开始出现超时……这就是典型的 MySQL 深分页灾难。面试中这道题也是检验候选人是“CRUD 熟练工”还是“资深架构师”的试金石。今天我们就来彻底把这个亿级翻页的问题扒得底朝天从 MySQL 底层原理到延迟关联再到结合 Elasticsearch 的终极降维打击给你一套能直接上生产环境的解决方案。一、 为什么 MySQL 深分页会把数据库拖垮在给出解法之前我们必须先搞懂“病理”。很多同学知道LIMIT 1000000, 10会慢但具体慢在哪里MySQL 的 InnoDB 引擎使用的是 B 树索引。索引分为聚簇索引主键索引叶子节点存的是完整的数据行和非聚簇索引二级索引叶子节点存的是主键 ID。当你执行下面这个传统的深分页 SQL 时-- 示例1传统的慢查询深分页 SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 1000000, 10;运行结果说明这条 SQL 在千万级表中执行通常会耗时数秒甚至数十秒直接导致慢查询。它的底层执行逻辑是这样的优化器选择create_time这个二级索引。引擎沿着create_time索引树扫描出前 1000010 条满足status 1的主键 ID。高能预警回表拿着这 1000010 个主键 ID去主键索引树上查找完整的行数据。在内存中把这 1000010 条完整数据排序/过滤最后丢弃前 1000000 条只返回最后 10 条给客户端。看懂了吗为了这区区 10 条数据MySQL 做了一百万次无用的回表操作磁盘 I/O 和 CPU 全浪费在这些最终会被丢弃的数据上了。二、 纯 MySQL 体系内的自救延迟关联与游标法在不引入第三方存储的情况下我们如何用架构师的思维来改造它核心思路只有一个尽可能减少回表次数。1. 游标法基于最大 ID 翻页如果你仔细观察过如今的移动端 APP比如朋友圈、抖音、淘宝商品流你会发现它们根本没有“页码”这个概念全都是“下拉加载更多”。这就是对深分页在产品形态上的一种妥协与优化。我们可以每次查询时带上上一页最后一条记录的 ID或排序字段。-- 示例2游标法 (滚动翻页) SELECT * FROM orders WHERE status 1 AND id 9527000 -- 前端传过来的上一页最小ID (降序排列时) ORDER BY id DESC LIMIT 10;运行结果说明执行时间通常在几毫秒到十几毫秒之间无论翻到多深性能都非常稳定。因为引擎直接定位到该 ID往后扫 10 条即可完全没有 offset 的开销。架构师点拨如果是按非主键字段排序如时间游标法需要前端同时传回上一页最后一条记录的时间和IDSQL 条件需改为(create_time last_time) OR (create_time last_time AND id last_id)以确保排序的唯一性与连续性。局限性只能“上一页/下一页”无法实现“跳转到第 10000 页”。2. 延迟关联覆盖索引的妙用如果产品经理拿刀架在脖子上说 PC 端后台“必须有跳页功能”怎么办 这时候就需要用到延迟关联Deferred Join了。既然回表慢那我们就先不回表。我们先在二级索引上把分页做完拿到那 10 个目标 ID然后再去关联主表拿明细。这就叫“覆盖索引”。-- 示例3延迟关联优化深分页 SELECT t1.* FROM orders t1 INNER JOIN ( -- 子查询只查主键ID利用覆盖索引避免回表 -- 注意必须加入主键作为第二排序维度防止排序不稳定导致的分页重复/遗漏 SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC, id DESC LIMIT 1000000, 10 ) t2 ON t1.id t2.id ORDER BY t1.create_time DESC, t1.id DESC;运行结果说明相比示例 1执行时间大幅缩短。虽然子查询依然扫描了 1000010 条记录但因为只查 ID这部分数据完全可以在内存Buffer Pool中飞速处理。最终只有 10 个 ID 参与了外层的回表操作。三、 亿级数据的终极解法Elasticsearch 组合架构当你的单表数据量突破 5000 万甚至上亿时即使是延迟关联子查询里扫描百万条记录的 CPU 开销也会让数据库吃不消。这时候关系型数据库的物理极限就到了我们需要引入搜索引擎 Elasticsearch (ES)。ES 天生适合做多维度检索但它同样有深分页问题默认index.max_result_window是 10000。// 示例4ES 传统 fromsize 分页 (超过 10000 会报错) GET /orders/_search { query: { term: { status: 1 } }, sort: [ { create_time: desc }, { id: desc } ], from: 1000000, size: 10 }运行结果说明ES 直接抛出异常提示请求的 from size 超过了 10000 的限制拒绝执行。终极杀器ES Search After MySQL 组合在 ES 中解决深分页的官方推荐方案是search_after。它类似于 MySQL 的游标法但支持多节点分布式的海量数据快速定位。我们的整体架构设计如下ES 负责检索和分页将 MySQL 中的核心搜索字段如状态、时间、用户ID等同步到 ES但不要把所有业务大字段存入 ESES 只存索引字段和主键 ID。MySQL 负责兜底明细通过 ES 拿到目标页的 10 个主键 ID 后再去 MySQL 中批量IN查询完整数据。// 示例5ES 使用 search_after 进行深分页 GET /orders/_search { size: 10, query: { term: { status: 1 } }, sort: [ { create_time: desc }, { id: desc } ], search_after: [1682300000000, 9527000] // 上一页最后一条记录的排序值 }运行结果说明ES 瞬间返回下一页的 10 个文档 ID耗时通常在毫秒级且不受深度影响。架构层面的深度考量数据一致性引入 ES 后必须接受最终一致性。由于 Canal/MQ 同步到 ES 存在秒级延迟刚创建的订单可能在 ES 中查不到。对策对于实时性要求极高的场景可以在写入 MySQL 后同步写入 Redis 缓存或者在前端做延迟刷新处理规避同步时间差导致的“数据消失”错觉。在 Java 工程中的落地伪代码如下// 示例6Java 业务层整合 ES 与 MySQL public PageResultOrderDTO getOrderPage(OrderQueryReq req) { // 1. 构建 ES 查询条件使用 search_after SearchRequest searchRequest buildEsSearchRequest(req); SearchResponse esResponse esClient.search(searchRequest, RequestOptions.DEFAULT); // 2. 提取 ES 返回的 主键 ID 集合 ListLong orderIds extractIdsFromEs(esResponse); if (orderIds.isEmpty()) { return PageResult.empty(); } // 3. 拿着 ID 去 MySQL 查明细 (注意保持 ES 返回的顺序) ListOrder orders orderMapper.selectByIds(orderIds); ListOrder sortedOrders sortByIdList(orders, orderIds); // 内存重排 // 4. 返回给前端并附带本次最后一条记录的 sortValues供下一次请求使用 Object[] nextSearchAfter extractLastSortValues(esResponse); return new PageResult(sortedOrders, nextSearchAfter); }运行结果说明业务侧成功获取到分页数据。ES 承担了复杂的过滤和排序MySQL 仅作为 KV 存储通过主键获取数据两者各司其职系统整体吞吐量大幅提升。四、 思维拓展架构师的取舍与“邪修”设计1. 常见误区与坑点盲目加缓存有些初级开发喜欢把分页数据丢进 Redis List 里。只要数据一更新整个 List 就要失效缓存击穿和一致性问题会让你痛不欲生。ES 当关系型数据库用把 MySQL 的所有字段一股脑同步到 ES。这会导致 ES 索引极其臃肿JVM 堆内存频繁 GC。记住ES 是倒排索引它是用来“找”数据的不是用来“存”大文本的。2. 架构层面的考虑技术永远服务于业务。在死磕深分页技术方案之前架构师首先要问的是“业务真的需要看第 10000 页的数据吗” 人类的肉眼和耐心是不可能看到第 10000 页的。如果是正常用户他应该通过增加搜索条件比如限制日期范围来缩小结果集如果是爬虫我们更应该限制他3. 输出“邪修”版本架构设计如果你在一个极度内卷又缺乏资源的团队且产品经理非常强势这里提供一套“邪修”方案前端欺骗法无论总数据有多少前端分页控件最多只显示 100 页。当用户点击第 100 页时提示“搜索结果过多请细化搜索条件”。很多大厂的搜索引擎就是这么干的。降级路由法在网关层做拦截如果发现page 1000直接把请求路由到一台快要淘汰的 MySQL 备用从库上。让它慢慢查查得出来算运气查超时了就直接给前端抛“系统繁忙”。主库的稳定性得到了绝对保障。注使用此方案前请确保该备用库是真正的“孤岛”不要与其他业务共享否则容易被拉去祭天。五、 总结深分页 problem本质上是关系型数据库底层存储结构B树与业务不合理需求之间的矛盾。Takeaway核心结论百万级数据MySQL 自身优化。C端业务改用“下拉加载”使用游标法基于最大 IDB端业务必须跳页的使用延迟关联覆盖索引 JOIN。务必注意排序字段的唯一性加入主键排序。千万/亿级数据引入Elasticsearch。使用search_after语法ES 查 IDMySQL 查明细各司其职。需额外关注异构存储的数据一致性延迟问题。架构师法则解决深分页最好的方法就是从产品层面干掉深分页。限制最大翻页深度引导用户使用精确搜索这才是性价比最高的架构设计。技术没有银弹只有在特定业务场景下的权衡与取舍。希望这篇文章能帮你彻底搞定深分页下次线上告警时你可以从容地端起咖啡微微一笑。
MySQL深分页终极解法：延迟关联与Elasticsearch组合优化的亿级翻页实战.md
在后端开发的职业生涯中如果说有什么问题是每个程序员都会遇到且每次遇到都会让人血压飙升的那“深分页”绝对榜上有名。想象一下这个场景周五下午快下班时运营同学在后台点开了一个拥有上亿数据的订单列表然后为了找半年前的一笔异常订单疯狂地点击“下一页”或者直接在页码框里输入了100000并按下了回车。 紧接着数据库 CPU 瞬间打满慢查询告警像雪花一样飞到你的企业微信里线上其他业务开始出现超时……这就是典型的 MySQL 深分页灾难。面试中这道题也是检验候选人是“CRUD 熟练工”还是“资深架构师”的试金石。今天我们就来彻底把这个亿级翻页的问题扒得底朝天从 MySQL 底层原理到延迟关联再到结合 Elasticsearch 的终极降维打击给你一套能直接上生产环境的解决方案。一、 为什么 MySQL 深分页会把数据库拖垮在给出解法之前我们必须先搞懂“病理”。很多同学知道LIMIT 1000000, 10会慢但具体慢在哪里MySQL 的 InnoDB 引擎使用的是 B 树索引。索引分为聚簇索引主键索引叶子节点存的是完整的数据行和非聚簇索引二级索引叶子节点存的是主键 ID。当你执行下面这个传统的深分页 SQL 时-- 示例1传统的慢查询深分页 SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 1000000, 10;运行结果说明这条 SQL 在千万级表中执行通常会耗时数秒甚至数十秒直接导致慢查询。它的底层执行逻辑是这样的优化器选择create_time这个二级索引。引擎沿着create_time索引树扫描出前 1000010 条满足status 1的主键 ID。高能预警回表拿着这 1000010 个主键 ID去主键索引树上查找完整的行数据。在内存中把这 1000010 条完整数据排序/过滤最后丢弃前 1000000 条只返回最后 10 条给客户端。看懂了吗为了这区区 10 条数据MySQL 做了一百万次无用的回表操作磁盘 I/O 和 CPU 全浪费在这些最终会被丢弃的数据上了。二、 纯 MySQL 体系内的自救延迟关联与游标法在不引入第三方存储的情况下我们如何用架构师的思维来改造它核心思路只有一个尽可能减少回表次数。1. 游标法基于最大 ID 翻页如果你仔细观察过如今的移动端 APP比如朋友圈、抖音、淘宝商品流你会发现它们根本没有“页码”这个概念全都是“下拉加载更多”。这就是对深分页在产品形态上的一种妥协与优化。我们可以每次查询时带上上一页最后一条记录的 ID或排序字段。-- 示例2游标法 (滚动翻页) SELECT * FROM orders WHERE status 1 AND id 9527000 -- 前端传过来的上一页最小ID (降序排列时) ORDER BY id DESC LIMIT 10;运行结果说明执行时间通常在几毫秒到十几毫秒之间无论翻到多深性能都非常稳定。因为引擎直接定位到该 ID往后扫 10 条即可完全没有 offset 的开销。架构师点拨如果是按非主键字段排序如时间游标法需要前端同时传回上一页最后一条记录的时间和IDSQL 条件需改为(create_time last_time) OR (create_time last_time AND id last_id)以确保排序的唯一性与连续性。局限性只能“上一页/下一页”无法实现“跳转到第 10000 页”。2. 延迟关联覆盖索引的妙用如果产品经理拿刀架在脖子上说 PC 端后台“必须有跳页功能”怎么办 这时候就需要用到延迟关联Deferred Join了。既然回表慢那我们就先不回表。我们先在二级索引上把分页做完拿到那 10 个目标 ID然后再去关联主表拿明细。这就叫“覆盖索引”。-- 示例3延迟关联优化深分页 SELECT t1.* FROM orders t1 INNER JOIN ( -- 子查询只查主键ID利用覆盖索引避免回表 -- 注意必须加入主键作为第二排序维度防止排序不稳定导致的分页重复/遗漏 SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC, id DESC LIMIT 1000000, 10 ) t2 ON t1.id t2.id ORDER BY t1.create_time DESC, t1.id DESC;运行结果说明相比示例 1执行时间大幅缩短。虽然子查询依然扫描了 1000010 条记录但因为只查 ID这部分数据完全可以在内存Buffer Pool中飞速处理。最终只有 10 个 ID 参与了外层的回表操作。三、 亿级数据的终极解法Elasticsearch 组合架构当你的单表数据量突破 5000 万甚至上亿时即使是延迟关联子查询里扫描百万条记录的 CPU 开销也会让数据库吃不消。这时候关系型数据库的物理极限就到了我们需要引入搜索引擎 Elasticsearch (ES)。ES 天生适合做多维度检索但它同样有深分页问题默认index.max_result_window是 10000。// 示例4ES 传统 fromsize 分页 (超过 10000 会报错) GET /orders/_search { query: { term: { status: 1 } }, sort: [ { create_time: desc }, { id: desc } ], from: 1000000, size: 10 }运行结果说明ES 直接抛出异常提示请求的 from size 超过了 10000 的限制拒绝执行。终极杀器ES Search After MySQL 组合在 ES 中解决深分页的官方推荐方案是search_after。它类似于 MySQL 的游标法但支持多节点分布式的海量数据快速定位。我们的整体架构设计如下ES 负责检索和分页将 MySQL 中的核心搜索字段如状态、时间、用户ID等同步到 ES但不要把所有业务大字段存入 ESES 只存索引字段和主键 ID。MySQL 负责兜底明细通过 ES 拿到目标页的 10 个主键 ID 后再去 MySQL 中批量IN查询完整数据。// 示例5ES 使用 search_after 进行深分页 GET /orders/_search { size: 10, query: { term: { status: 1 } }, sort: [ { create_time: desc }, { id: desc } ], search_after: [1682300000000, 9527000] // 上一页最后一条记录的排序值 }运行结果说明ES 瞬间返回下一页的 10 个文档 ID耗时通常在毫秒级且不受深度影响。架构层面的深度考量数据一致性引入 ES 后必须接受最终一致性。由于 Canal/MQ 同步到 ES 存在秒级延迟刚创建的订单可能在 ES 中查不到。对策对于实时性要求极高的场景可以在写入 MySQL 后同步写入 Redis 缓存或者在前端做延迟刷新处理规避同步时间差导致的“数据消失”错觉。在 Java 工程中的落地伪代码如下// 示例6Java 业务层整合 ES 与 MySQL public PageResultOrderDTO getOrderPage(OrderQueryReq req) { // 1. 构建 ES 查询条件使用 search_after SearchRequest searchRequest buildEsSearchRequest(req); SearchResponse esResponse esClient.search(searchRequest, RequestOptions.DEFAULT); // 2. 提取 ES 返回的 主键 ID 集合 ListLong orderIds extractIdsFromEs(esResponse); if (orderIds.isEmpty()) { return PageResult.empty(); } // 3. 拿着 ID 去 MySQL 查明细 (注意保持 ES 返回的顺序) ListOrder orders orderMapper.selectByIds(orderIds); ListOrder sortedOrders sortByIdList(orders, orderIds); // 内存重排 // 4. 返回给前端并附带本次最后一条记录的 sortValues供下一次请求使用 Object[] nextSearchAfter extractLastSortValues(esResponse); return new PageResult(sortedOrders, nextSearchAfter); }运行结果说明业务侧成功获取到分页数据。ES 承担了复杂的过滤和排序MySQL 仅作为 KV 存储通过主键获取数据两者各司其职系统整体吞吐量大幅提升。四、 思维拓展架构师的取舍与“邪修”设计1. 常见误区与坑点盲目加缓存有些初级开发喜欢把分页数据丢进 Redis List 里。只要数据一更新整个 List 就要失效缓存击穿和一致性问题会让你痛不欲生。ES 当关系型数据库用把 MySQL 的所有字段一股脑同步到 ES。这会导致 ES 索引极其臃肿JVM 堆内存频繁 GC。记住ES 是倒排索引它是用来“找”数据的不是用来“存”大文本的。2. 架构层面的考虑技术永远服务于业务。在死磕深分页技术方案之前架构师首先要问的是“业务真的需要看第 10000 页的数据吗” 人类的肉眼和耐心是不可能看到第 10000 页的。如果是正常用户他应该通过增加搜索条件比如限制日期范围来缩小结果集如果是爬虫我们更应该限制他3. 输出“邪修”版本架构设计如果你在一个极度内卷又缺乏资源的团队且产品经理非常强势这里提供一套“邪修”方案前端欺骗法无论总数据有多少前端分页控件最多只显示 100 页。当用户点击第 100 页时提示“搜索结果过多请细化搜索条件”。很多大厂的搜索引擎就是这么干的。降级路由法在网关层做拦截如果发现page 1000直接把请求路由到一台快要淘汰的 MySQL 备用从库上。让它慢慢查查得出来算运气查超时了就直接给前端抛“系统繁忙”。主库的稳定性得到了绝对保障。注使用此方案前请确保该备用库是真正的“孤岛”不要与其他业务共享否则容易被拉去祭天。五、 总结深分页 problem本质上是关系型数据库底层存储结构B树与业务不合理需求之间的矛盾。Takeaway核心结论百万级数据MySQL 自身优化。C端业务改用“下拉加载”使用游标法基于最大 IDB端业务必须跳页的使用延迟关联覆盖索引 JOIN。务必注意排序字段的唯一性加入主键排序。千万/亿级数据引入Elasticsearch。使用search_after语法ES 查 IDMySQL 查明细各司其职。需额外关注异构存储的数据一致性延迟问题。架构师法则解决深分页最好的方法就是从产品层面干掉深分页。限制最大翻页深度引导用户使用精确搜索这才是性价比最高的架构设计。技术没有银弹只有在特定业务场景下的权衡与取舍。希望这篇文章能帮你彻底搞定深分页下次线上告警时你可以从容地端起咖啡微微一笑。
