I got a bad idea..如果在代码评审或技术讨论里听到这句话多数人第一反应是希望对方趁早收手别把不可控的复杂度带进项目。但这句话如果换一个场景恰恰可能是某个优化方案“即将改变现状”的开始。本文想从一个真实常见的工程问题讲起数据库深分页。它看起来模块简单、逻辑清晰就是一个LIMIT加OFFSET却会在数据量增长后把接口拖慢到让人怀疑人生。更麻烦的是针对它的几种优化方案从表面看都像“坏主意”延迟关联改写复杂、游标分页会改变翻页行为、加索引还可能触发更多坑。这让很多团队宁可继续忍耐慢查询也不愿意碰这个看起来很傻的问题。越是这样越值得认真拆解。这篇文章会从“坏主意”这个判断出发讲清楚为什么深分页会慢什么情况下适合用延迟关联什么情况下适合用游标分页并给出可复制的 SQL、MyBatis 示例和验证方法。读完以后你可以独立完成一次查询优化改造也能在下次代码评审里用更清晰的逻辑判断一个方案是不是真的“bad idea”。1. 为什么“坏主意”值得被认真对待1.1 “坏主意”往往是被低估的信号在工程语境里一个方案被称做“坏主意”通常有三种原因它打破了团队已经习惯的编码方式。它引入了额外复杂度短期看不到收益。它可能改变产品上一个看起来“正常”的行为。这三种原因都和“技术正确性”没有直接关系。换句话说当一个方案被叫做坏主意时往往只是因为它的成本被高估了或者它的收益没有被完整表达。很多最终被证明有效的架构调整最初都顶着“坏主意”的标签。比如在单体应用里先抽出独立的缓存服务、在前端引入服务端渲染、在定时任务里改为消息驱动这些方案在初期都被质疑过价值不明显、改动面大、维护成本高。但真正让它们被接受的不是“看着合理”而是可验证的效果。所以当你说“I got a bad idea”时正确的下一步不是立刻否定而是把它转译成一个可以验证的问题它解决了什么痛点它改变了哪个环节它会不会引入新的故障点1.2 判断的标准不是观感而是可验证的影响一个方案是否值得做可以建立一组朴素标准是否针对真实的性能或稳定性问题。是否在可控范围内改变现状。是否有可观测的指标来证明它变好了。出现问题之后能否回滚或降级。如果满足以上四点即便方案看起来不常规也值得投入小成本验证。如果只是“听说这样更优雅”或者“感觉以后用得上”那确实应该趁早放弃。数据库深分页的改造正好满足前三点它是真实问题改的是查询路径可以通过执行计划和响应时间验证。唯一需要谨慎的是第四点即回滚方案。所以这篇文章也会重点讲怎么验证、怎么控制风险。2. 深分页一个看起来很傻的问题2.1 传统分页的代价先看一段最常见的分页 SQLSELECT id, order_no, user_id, amount, created_at FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;它的作用是跳过前面 10 万行取出第 100001 到第 100020 行。数据库执行时需要先扫描到第 10 万行再丢弃前 10 万行最后返回 20 行。前面的 10 万行其实都没有返回给客户端但因为要排序和定位数据库必须把它们全部读出来。数据量小的时候这个代价完全无感。但订单表、日志表、行为记录表一旦增长到百万级、千万级越往后的页面延迟会明显增加。这就是深分页问题的本质扫描量大OFFSET越大扫描的行数越多。排序代价高ORDER BY会先对扫描结果排序即使最终只返回少部分行。索引利用率差如果索引无法完全覆盖排序和过滤条件数据库还要回表读取更多数据。2.2 延迟关联与游标分页初看像“坏主意”针对深分页常见方案有两类。第一类是延迟关联。它先通过覆盖索引快速定位目标行的主键再回表获取完整数据。SQL 会被改写成类似这样SELECT t.id, t.order_no, t.user_id, t.amount, t.created_at FROM orders t INNER JOIN ( SELECT id FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000 ) tmp ON t.id tmp.id ORDER BY t.created_at DESC;表面上看这条 SQL 比原来复杂了子查询也增加了阅读难度。但它让内层 SELECT 可以只扫描索引页大幅减少回表次数。对数据库来说扫描 10 万行索引页比扫描 10 万行“索引 数据页”的成本低很多。第二类是游标分页。它不再使用OFFSET而是记住上一页最后一条记录的排序值下一页通过WHERE条件继续取SELECT id, order_no, user_id, amount, created_at FROM orders WHERE created_at 2024-11-01 12:00:00 ORDER BY created_at DESC LIMIT 20;这个方案在翻页场景下高效稳定但会改变分页行为用户不能直接从第 1 页跳到第 100 页所有页面必须按顺序翻。这在很多产品里是不可接受的。所以它常常被引用为“坏主意”的典型。然而如果我们把场景限定在“个人中心订单列表”“移动端消息列表”“App 内下拉加载更多”等场景游标分页反而比传统分页更合理。因为用户很少需要精确跳页而数据库却节省了大量无谓扫描。下表可以直观对比两种方案方案核心思路优点缺点适合场景传统 LIMIT/OFFSET跳过前 N 行实现简单、支持任意跳页深分页时扫描量大、延迟高数据量小、后台管理列表延迟关联先查主键再回表大幅减少回表兼容原有跳页SQL 相对复杂、需要覆盖索引支撑大数据量但必须支持跳页游标分页基于排序键翻页扫描量稳定、性能可预期不支持精确跳页、依赖稳定排序键App 列表、消息流、滚动加载3. 环境准备与前置条件本文后续示例以 MySQL 和 Spring Boot 项目为基础。具体版本不用刻意追求最新只要满足下面的条件即可操作系统Linux、macOS 或 Windows 均可。数据库MySQL 5.7 或 8.x。JDKJava 8 及以上。框架Spring Boot 2.x / 3.x 均可示例以 MyBatis 为主。构建工具Maven 或 Gradle。如果团队目前没有现成的 Spring Boot 项目也可以通过 JDBC 或数据库客户端直接执行 SQL 来理解核心逻辑。实际改造时再迁移到项目代码里。以下环境参数建议提前确认# 数据库连接示例具体以本地环境为准 spring.datasource.urljdbc:mysql://localhost:3306/demo_db?useUnicodetruecharacterEncodingutf8serverTimezoneAsia/Shanghai spring.datasource.usernameroot spring.datasource.passwordyour_password需要说明的是本文不会写死某个 MySQL 小版本因为核心原理在 5.7 和 8.x 上都成立。真正影响实验效果的是你的测试数据量和表结构设计。4. 核心流程拆解4.1 复现深分页问题任何优化都从复现问题开始。先准备一张订单表写入一定量测试数据然后模拟深分页查询。建议分三步走创建简单的订单表。批量插入测试数据几百条不够至少要几十万条才能看到差异。用不同OFFSET执行查询观察耗时和执行计划。复现时最容易犯的错误是数据量太少。如果你的表只有几千行OFFSET拉到 10000数据库扫描成本依然很低优化前后几乎看不出区别。这会导致你误以为方案无效。所以在本地实验时建议用存储过程或脚本插入 50 万行以上的数据。4.2 方案一延迟关联优化延迟关联的核心思想是把“回表”动作尽量延后。原始查询是这样的SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;当OFFSET很大时数据库可能要读取大量数据页然后丢弃。延迟关联的改法是SELECT t.* FROM orders t INNER JOIN ( SELECT id FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000 ) tmp ON t.id tmp.id ORDER BY t.created_at DESC;这里的关键在于内层查询只需要返回主键id。如果表上存在(created_at, id)的复合索引数据库可以直接在索引上完成排序和分页不需要查询完整行记录。外层再通过主键回表只取 20 行的完整数据。从数据库执行流程看它把原来的“扫 10 万行数据页”变成了“扫 10 万行索引页 取 20 行数据页”。索引页的大小通常远小于数据页单位时间内能读取的索引记录数更多因此整体 I/O 有明显下降。4.3 方案二游标分页如果说延迟关联是对原有 SQL 的“补丁”游标分页则是对分页交互的重新设计。传统分页的搜索条件是“第几页”。游标分页的搜索条件是“从哪个位置继续”。比如第一页查询SELECT id, order_no, user_id, amount, created_at FROM orders ORDER BY created_at DESC LIMIT 20;拿到结果后记录最后一条数据的created_at作为下一页的启动位置SELECT id, order_no, user_id, amount, created_at FROM orders WHERE created_at 2024-11-01 12:00:00 ORDER BY created_at DESC LIMIT 20;这个方案的优势很明显无论你翻到第 100 页还是第 10000 页数据库都只扫描LIMIT指定数量的记录性能非常稳定。但坏处也很明显它无法支持任意跳页。产品如果有一个“用户可以直接跳到第 200 页”的需求游标分页就做不到了。所以游标分页适合“瀑布流加载”和“加载更多”这类场景而不适合传统的后台管理系统分页表格。5. 完整示例与代码实现5.1 建表与测试数据先创建订单表CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, KEY idx_created_id (created_at, id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里建议加上idx_created_id (created_at, id)复合索引它是延迟关联生效的关键。如果没有这个索引内层查询排序时可能要使用文件排序优化效果会大打折扣。批量写入测试数据可以使用存储过程也可以使用 Java 程序循环插入。为了在数据库客户端快速验证推荐用脚本方式DROP PROCEDURE IF EXISTS insert_test_orders; DELIMITER $$ CREATE PROCEDURE insert_test_orders(IN total INT) BEGIN DECLARE i INT DEFAULT 1; START TRANSACTION; WHILE i total DO INSERT INTO orders (order_no, user_id, amount, status, created_at) VALUES ( CONCAT(NO, LPAD(i, 8, 0)), FLOOR(RAND() * 10000) 1, ROUND(RAND() * 1000, 2), 0, DATE_SUB(NOW(), INTERVAL i SECOND) ); SET i i 1; IF i % 1000 0 THEN COMMIT; START TRANSACTION; END IF; END WHILE; COMMIT; END $$ DELIMITER ; CALL insert_test_orders(500000);注意存储过程插入 50 万行可能需要几十秒甚至更久不同机器差异较大。如果不想等太久可以先插入 10 万行做验证后面再按需加量。5.2 MyBatis Mapper 实现延迟关联先给一个原始的 Mapper 接口// 文件路径src/main/java/com/example/demo/mapper/OrderMapper.java public interface OrderMapper { ListOrder selectPage(PageParam param); }对应的 XML!-- 文件路径src/main/resources/mapper/OrderMapper.xml -- select idselectPage resultTypecom.example.demo.entity.Order SELECT id, order_no, user_id, amount, status, created_at FROM orders ORDER BY created_at DESC, id DESC LIMIT #{offset}, #{limit} /select这是最常规的分页写法。改造为延迟关联后!-- 文件路径src/main/resources/mapper/OrderMapper.xml -- select idselectPageByLazyJoin resultTypecom.example.demo.entity.Order SELECT t.id, t.order_no, t.user_id, t.amount, t.status, t.created_at FROM orders t INNER JOIN ( SELECT id FROM orders ORDER BY created_at DESC, id DESC LIMIT #{offset}, #{limit} ) tmp ON t.id tmp.id ORDER BY t.created_at DESC, t.id DESC /select这里的PageParam至少包含offset和limit两个字段// 文件路径src/main/java/com/example/demo/param/PageParam.java public class PageParam { private Integer offset; private Integer limit; public Integer getOffset() { return offset; } public void setOffset(Integer offset) { this.offset offset; } public Integer getLimit() { return limit; } public void setLimit(Integer limit) { this.limit limit; } }如果你用的是 PageHelper也可以在业务层先查出一页主键再用主键查询完整记录效果类似。但这里的 XML 写法更直观适合理解原理。5.3 游标分页的 Java 层实现游标分页的参数不再是offset而是上一页最后一条记录的位置。定义如下// 文件路径src/main/java/com/example/demo/param/PageCursorParam.java public class PageCursorParam { private String lastCreatedAt; private Long lastId; private Integer limit; // getter/setter 略 }Mapper 接口// 文件路径src/main/java/com/example/demo/mapper/OrderMapper.java ListOrder selectPageByCursor(PageCursorParam param);XML 实现!-- 文件路径src/main/resources/mapper/OrderMapper.xml -- select idselectPageByCursor resultTypecom.example.demo.entity.Order SELECT id, order_no, user_id, amount, status, created_at FROM orders where if testlastCreatedAt ! null and lastCreatedAt ! created_at lt; #{lastCreatedAt} OR (created_at #{lastCreatedAt} AND id lt; #{lastId}) /if /where ORDER BY created_at DESC, id DESC LIMIT #{limit} /select注意条件里同时带上created_at和id是为了处理排序字段重复的情况。如果只按created_at排序而同一秒内有多条记录翻页时可能丢数据或重复数据。再加上id后排序和过滤条件都是唯一且确定的。在 XML 中需要使用lt;这是 MyBatis 的常见坑很多新手第一次写游标分页都会在这里报错。业务层调用时需要把上一页最后一条记录的created_at和id取出来封装到参数里// 文件路径src/main/java/com/example/demo/service/OrderService.java public ListOrder pageByCursor(Order lastOrder, int limit) { PageCursorParam param new PageCursorParam(); if (lastOrder ! null) { param.setLastCreatedAt(lastOrder.getCreatedAt()); param.setLastId(lastOrder.getId()); } param.setLimit(limit); return orderMapper.selectPageByCursor(param); }这个示例展示的是最简单的同步分页。在真实项目里返回结果还会附带游标信息方便前端传回后端。5.4 如何运行与验证运行前提数据库表结构创建成功。测试数据已经插入。Spring Boot 项目可以正常启动。如果只想快速验证延迟关联效果可以不启动项目直接在 MySQL 客户端执行两条 SQL对比执行计划EXPLAIN SELECT id, order_no, user_id, amount, status, created_at FROM orders ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 100000; EXPLAIN SELECT t.id, t.order_no, t.user_id, t.amount, t.status, t.created_at FROM orders t INNER JOIN ( SELECT id FROM orders ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 100000 ) tmp ON t.id tmp.id ORDER BY t.created_at DESC, t.id DESC;游标分页则可以这样验证EXPLAIN SELECT id, order_no, user_id, amount, status, created_at FROM orders WHERE created_at 2024-11-01 12:00:00 ORDER BY created_at DESC, id DESC LIMIT 20;查看执行计划时重点看rows字段传统分页在深分页时扫描行数会随着OFFSET增大而明显增长延迟关联和游标分页的扫描行数则更接近目标行数。6. 运行结果与效果验证6.1 用 EXPLAIN 验证执行计划以 50 万行数据为例传统深分页的执行计划通常会在rows字段显示很大的估算值。如果表结构没有正确索引还会出现Using filesort。延迟关联的 inner 查询会走idx_created_id索引执行计划一般能看到typeindexkeyidx_created_idExtraUsing index这意味着查询完全在索引上完成不需要回表。外层通过主键id回表时只读取LIMIT对应的少量行所以耗时远小于原始方案。游标分页在WHERE created_at ?条件下如果索引生效执行计划会显示type为range扫描范围控制在两个连续值之间性能稳定。6.2 页面耗时与扫描行数对比优化的验证不能只看 SQL 是否多读了索引。建议做两组对比第一组固定 OFFSET对比原始 SQL 和延迟关联 SQL。在服务层分别调用两个查询。用日志打印耗时。记录OFFSET 1000、10000、50000、100000时的耗时变化。结果是在数据量较小时两者差异不明显随着OFFSET增大原始 SQL 耗时增长更快延迟关联的耗时相对平缓。这也说明一个原则没有深分页场景的表不需要为了优化而优化。第二组游标分页和传统分页对比。模拟连续翻页例如连续翻 100 页记录每页的平均耗时。传统分页在第 1 页和第 100 页的耗时可能相差很大游标分页则每一页耗时都接近。具体判断标准可以这样定如果OFFSET超过 1 万行时接口 P99 延迟明显超过 500ms说明问题真实存在。如果优化后同样OFFSET下耗时下降 50% 以上方案有效。如果扫描行数显著下降且没有新增慢查询说明可以进入灰度阶段。这里不要纠结于“到底降了多少毫秒”因为不同机器的磁盘、内存、并发压力差异很大。更重要的是看趋势优化方案是否在大偏移量下保持低延迟。7. 常见问题与排查思路问题现象可能原因排查方式解决方案延迟关联后反而更慢表数据量太小优化优势不明显或索引缺失查看 EXPLAIN确认是否使用覆盖索引确保(created_at, id)索引存在扩大到足够数据量测试游标分页出现重复数据只按created_at排序时间字段重复检查上页最后一条记录的时间值排序条件补上id并让过滤条件同时包含时间和 idMyBatis 执行报 SQL 语法错误XML 中的被当成标签开始符查看日志中完整 SQL将改为lt;ORDER BY 和 WHERE 条件不一致翻页时排序键顺序变化对比两条 SQL 的排序字段统一排序条件为created_at DESC, id DESC游标分页无法跳页产品交互不支持基于游标跳页和产品确认需求保留传统分页接口或改用延迟关联深分页时索引失效查询条件里使用了函数、隐式转换使用EXPLAIN查看type字段避免在索引列上使用函数保持数据类型一致插入大量测试数据很慢单条 INSERT 频繁提交使用存储过程或批量插入脚本分批事务提交每批 1000 条左右这些问题是实际改造时比较常见的坑尤其是 MyBatis 中lt;转义和排序字段重复很容易出现。8. 最佳实践与工程建议8.1 改造前先确认场景不是所有分页都需要从传统方案迁移到延迟关联或游标分页。建议先做一次盘点列表接口的分页深度分布如何用户主要翻前几页还是深翻数据量增长趋势是否会导致问题恶化后台管理系统是否需要精确跳页如果查询基本上只翻前几页OFFSET不超过几十优化就没有太大意义。相反如果存在“导出全量数据”“爬虫高频翻页”“用户大批量浏览”的情况深分页优化会带来明显收益。8.2 让“坏主意”进入评审闭环一个方案被提出时最容易出现的两种极端是直接否定或者无脑接受。更合理的流程是把它拉进一个轻量评审闭环写清楚现状指标当前接口耗时的 P95、P99扫描行数。明确方案改动点SQL、索引、接口参数、前端交互。设计小范围验证用测试环境或影子流量做对比。制定回滚方案先改接口再切换前端或者先灰度 10% 流量。回归性能指标对比优化前后趋势而不是单次数据。“I got a bad idea”这句话最理想的后续是“但我们可以先花半小时验证一下”。8.3 生产环境的安全意识延迟关联和游标分页改造涉及的是数据库查询路径操作前要注意以下几点索引变更属于 DDL线上执行会锁表或占用资源务必在低峰期进行并有备份和回滚方案。不要直接在核心库上跑大规模测试数据优先在测试环境验证。生产环境灰度发布时先让少量流量命中新 SQL观察数据库慢查询数量、CPU 和连接池占用情况。如果游标分页参数由前端传入必须校验参数长度和格式避免恶意构造超长条件。任何 SQL 改动都要结合查询日志和监控平台回看不要只看一次手工查询结果。这些意识比具体代码更重要。毕竟查询优化这件事改错了不会立刻报警但可能在业务高峰期集中爆发。9. 总结回到标题那句话“I got a bad idea..”。它可能是一句自嘲也可能是一个被低估的优化契机。真正的工程判断不取决于方案听起来是否常规而取决于它是否解决了真实问题、能否被验证、是否可回滚。这篇文章通过深分页这个具体问题讲解了延迟关联和游标分页两种方案。前者适合必须支持跳页的业务后者适合“加载更多”类场景。两者的核心目标都是减少数据库无谓的扫描和回表从而稳定接口延迟。你可能还没有遇到过千万级订单表也可能你的系统在未来半年都不需要这种优化。但下一次听到团队成员说“我有一个坏主意”时不妨先问一句你打算怎么证明它有效如果能用 30 分钟跑一个最小实验用执行计划和耗时代替主观判断很多“坏主意”反而会成为项目里最有价值的优化项。 数据库深分页优化：延迟关联与游标分页的工程实践
I got a bad idea..如果在代码评审或技术讨论里听到这句话多数人第一反应是希望对方趁早收手别把不可控的复杂度带进项目。但这句话如果换一个场景恰恰可能是某个优化方案“即将改变现状”的开始。本文想从一个真实常见的工程问题讲起数据库深分页。它看起来模块简单、逻辑清晰就是一个LIMIT加OFFSET却会在数据量增长后把接口拖慢到让人怀疑人生。更麻烦的是针对它的几种优化方案从表面看都像“坏主意”延迟关联改写复杂、游标分页会改变翻页行为、加索引还可能触发更多坑。这让很多团队宁可继续忍耐慢查询也不愿意碰这个看起来很傻的问题。越是这样越值得认真拆解。这篇文章会从“坏主意”这个判断出发讲清楚为什么深分页会慢什么情况下适合用延迟关联什么情况下适合用游标分页并给出可复制的 SQL、MyBatis 示例和验证方法。读完以后你可以独立完成一次查询优化改造也能在下次代码评审里用更清晰的逻辑判断一个方案是不是真的“bad idea”。1. 为什么“坏主意”值得被认真对待1.1 “坏主意”往往是被低估的信号在工程语境里一个方案被称做“坏主意”通常有三种原因它打破了团队已经习惯的编码方式。它引入了额外复杂度短期看不到收益。它可能改变产品上一个看起来“正常”的行为。这三种原因都和“技术正确性”没有直接关系。换句话说当一个方案被叫做坏主意时往往只是因为它的成本被高估了或者它的收益没有被完整表达。很多最终被证明有效的架构调整最初都顶着“坏主意”的标签。比如在单体应用里先抽出独立的缓存服务、在前端引入服务端渲染、在定时任务里改为消息驱动这些方案在初期都被质疑过价值不明显、改动面大、维护成本高。但真正让它们被接受的不是“看着合理”而是可验证的效果。所以当你说“I got a bad idea”时正确的下一步不是立刻否定而是把它转译成一个可以验证的问题它解决了什么痛点它改变了哪个环节它会不会引入新的故障点1.2 判断的标准不是观感而是可验证的影响一个方案是否值得做可以建立一组朴素标准是否针对真实的性能或稳定性问题。是否在可控范围内改变现状。是否有可观测的指标来证明它变好了。出现问题之后能否回滚或降级。如果满足以上四点即便方案看起来不常规也值得投入小成本验证。如果只是“听说这样更优雅”或者“感觉以后用得上”那确实应该趁早放弃。数据库深分页的改造正好满足前三点它是真实问题改的是查询路径可以通过执行计划和响应时间验证。唯一需要谨慎的是第四点即回滚方案。所以这篇文章也会重点讲怎么验证、怎么控制风险。2. 深分页一个看起来很傻的问题2.1 传统分页的代价先看一段最常见的分页 SQLSELECT id, order_no, user_id, amount, created_at FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;它的作用是跳过前面 10 万行取出第 100001 到第 100020 行。数据库执行时需要先扫描到第 10 万行再丢弃前 10 万行最后返回 20 行。前面的 10 万行其实都没有返回给客户端但因为要排序和定位数据库必须把它们全部读出来。数据量小的时候这个代价完全无感。但订单表、日志表、行为记录表一旦增长到百万级、千万级越往后的页面延迟会明显增加。这就是深分页问题的本质扫描量大OFFSET越大扫描的行数越多。排序代价高ORDER BY会先对扫描结果排序即使最终只返回少部分行。索引利用率差如果索引无法完全覆盖排序和过滤条件数据库还要回表读取更多数据。2.2 延迟关联与游标分页初看像“坏主意”针对深分页常见方案有两类。第一类是延迟关联。它先通过覆盖索引快速定位目标行的主键再回表获取完整数据。SQL 会被改写成类似这样SELECT t.id, t.order_no, t.user_id, t.amount, t.created_at FROM orders t INNER JOIN ( SELECT id FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000 ) tmp ON t.id tmp.id ORDER BY t.created_at DESC;表面上看这条 SQL 比原来复杂了子查询也增加了阅读难度。但它让内层 SELECT 可以只扫描索引页大幅减少回表次数。对数据库来说扫描 10 万行索引页比扫描 10 万行“索引 数据页”的成本低很多。第二类是游标分页。它不再使用OFFSET而是记住上一页最后一条记录的排序值下一页通过WHERE条件继续取SELECT id, order_no, user_id, amount, created_at FROM orders WHERE created_at 2024-11-01 12:00:00 ORDER BY created_at DESC LIMIT 20;这个方案在翻页场景下高效稳定但会改变分页行为用户不能直接从第 1 页跳到第 100 页所有页面必须按顺序翻。这在很多产品里是不可接受的。所以它常常被引用为“坏主意”的典型。然而如果我们把场景限定在“个人中心订单列表”“移动端消息列表”“App 内下拉加载更多”等场景游标分页反而比传统分页更合理。因为用户很少需要精确跳页而数据库却节省了大量无谓扫描。下表可以直观对比两种方案方案核心思路优点缺点适合场景传统 LIMIT/OFFSET跳过前 N 行实现简单、支持任意跳页深分页时扫描量大、延迟高数据量小、后台管理列表延迟关联先查主键再回表大幅减少回表兼容原有跳页SQL 相对复杂、需要覆盖索引支撑大数据量但必须支持跳页游标分页基于排序键翻页扫描量稳定、性能可预期不支持精确跳页、依赖稳定排序键App 列表、消息流、滚动加载3. 环境准备与前置条件本文后续示例以 MySQL 和 Spring Boot 项目为基础。具体版本不用刻意追求最新只要满足下面的条件即可操作系统Linux、macOS 或 Windows 均可。数据库MySQL 5.7 或 8.x。JDKJava 8 及以上。框架Spring Boot 2.x / 3.x 均可示例以 MyBatis 为主。构建工具Maven 或 Gradle。如果团队目前没有现成的 Spring Boot 项目也可以通过 JDBC 或数据库客户端直接执行 SQL 来理解核心逻辑。实际改造时再迁移到项目代码里。以下环境参数建议提前确认# 数据库连接示例具体以本地环境为准 spring.datasource.urljdbc:mysql://localhost:3306/demo_db?useUnicodetruecharacterEncodingutf8serverTimezoneAsia/Shanghai spring.datasource.usernameroot spring.datasource.passwordyour_password需要说明的是本文不会写死某个 MySQL 小版本因为核心原理在 5.7 和 8.x 上都成立。真正影响实验效果的是你的测试数据量和表结构设计。4. 核心流程拆解4.1 复现深分页问题任何优化都从复现问题开始。先准备一张订单表写入一定量测试数据然后模拟深分页查询。建议分三步走创建简单的订单表。批量插入测试数据几百条不够至少要几十万条才能看到差异。用不同OFFSET执行查询观察耗时和执行计划。复现时最容易犯的错误是数据量太少。如果你的表只有几千行OFFSET拉到 10000数据库扫描成本依然很低优化前后几乎看不出区别。这会导致你误以为方案无效。所以在本地实验时建议用存储过程或脚本插入 50 万行以上的数据。4.2 方案一延迟关联优化延迟关联的核心思想是把“回表”动作尽量延后。原始查询是这样的SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;当OFFSET很大时数据库可能要读取大量数据页然后丢弃。延迟关联的改法是SELECT t.* FROM orders t INNER JOIN ( SELECT id FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000 ) tmp ON t.id tmp.id ORDER BY t.created_at DESC;这里的关键在于内层查询只需要返回主键id。如果表上存在(created_at, id)的复合索引数据库可以直接在索引上完成排序和分页不需要查询完整行记录。外层再通过主键回表只取 20 行的完整数据。从数据库执行流程看它把原来的“扫 10 万行数据页”变成了“扫 10 万行索引页 取 20 行数据页”。索引页的大小通常远小于数据页单位时间内能读取的索引记录数更多因此整体 I/O 有明显下降。4.3 方案二游标分页如果说延迟关联是对原有 SQL 的“补丁”游标分页则是对分页交互的重新设计。传统分页的搜索条件是“第几页”。游标分页的搜索条件是“从哪个位置继续”。比如第一页查询SELECT id, order_no, user_id, amount, created_at FROM orders ORDER BY created_at DESC LIMIT 20;拿到结果后记录最后一条数据的created_at作为下一页的启动位置SELECT id, order_no, user_id, amount, created_at FROM orders WHERE created_at 2024-11-01 12:00:00 ORDER BY created_at DESC LIMIT 20;这个方案的优势很明显无论你翻到第 100 页还是第 10000 页数据库都只扫描LIMIT指定数量的记录性能非常稳定。但坏处也很明显它无法支持任意跳页。产品如果有一个“用户可以直接跳到第 200 页”的需求游标分页就做不到了。所以游标分页适合“瀑布流加载”和“加载更多”这类场景而不适合传统的后台管理系统分页表格。5. 完整示例与代码实现5.1 建表与测试数据先创建订单表CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, KEY idx_created_id (created_at, id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里建议加上idx_created_id (created_at, id)复合索引它是延迟关联生效的关键。如果没有这个索引内层查询排序时可能要使用文件排序优化效果会大打折扣。批量写入测试数据可以使用存储过程也可以使用 Java 程序循环插入。为了在数据库客户端快速验证推荐用脚本方式DROP PROCEDURE IF EXISTS insert_test_orders; DELIMITER $$ CREATE PROCEDURE insert_test_orders(IN total INT) BEGIN DECLARE i INT DEFAULT 1; START TRANSACTION; WHILE i total DO INSERT INTO orders (order_no, user_id, amount, status, created_at) VALUES ( CONCAT(NO, LPAD(i, 8, 0)), FLOOR(RAND() * 10000) 1, ROUND(RAND() * 1000, 2), 0, DATE_SUB(NOW(), INTERVAL i SECOND) ); SET i i 1; IF i % 1000 0 THEN COMMIT; START TRANSACTION; END IF; END WHILE; COMMIT; END $$ DELIMITER ; CALL insert_test_orders(500000);注意存储过程插入 50 万行可能需要几十秒甚至更久不同机器差异较大。如果不想等太久可以先插入 10 万行做验证后面再按需加量。5.2 MyBatis Mapper 实现延迟关联先给一个原始的 Mapper 接口// 文件路径src/main/java/com/example/demo/mapper/OrderMapper.java public interface OrderMapper { ListOrder selectPage(PageParam param); }对应的 XML!-- 文件路径src/main/resources/mapper/OrderMapper.xml -- select idselectPage resultTypecom.example.demo.entity.Order SELECT id, order_no, user_id, amount, status, created_at FROM orders ORDER BY created_at DESC, id DESC LIMIT #{offset}, #{limit} /select这是最常规的分页写法。改造为延迟关联后!-- 文件路径src/main/resources/mapper/OrderMapper.xml -- select idselectPageByLazyJoin resultTypecom.example.demo.entity.Order SELECT t.id, t.order_no, t.user_id, t.amount, t.status, t.created_at FROM orders t INNER JOIN ( SELECT id FROM orders ORDER BY created_at DESC, id DESC LIMIT #{offset}, #{limit} ) tmp ON t.id tmp.id ORDER BY t.created_at DESC, t.id DESC /select这里的PageParam至少包含offset和limit两个字段// 文件路径src/main/java/com/example/demo/param/PageParam.java public class PageParam { private Integer offset; private Integer limit; public Integer getOffset() { return offset; } public void setOffset(Integer offset) { this.offset offset; } public Integer getLimit() { return limit; } public void setLimit(Integer limit) { this.limit limit; } }如果你用的是 PageHelper也可以在业务层先查出一页主键再用主键查询完整记录效果类似。但这里的 XML 写法更直观适合理解原理。5.3 游标分页的 Java 层实现游标分页的参数不再是offset而是上一页最后一条记录的位置。定义如下// 文件路径src/main/java/com/example/demo/param/PageCursorParam.java public class PageCursorParam { private String lastCreatedAt; private Long lastId; private Integer limit; // getter/setter 略 }Mapper 接口// 文件路径src/main/java/com/example/demo/mapper/OrderMapper.java ListOrder selectPageByCursor(PageCursorParam param);XML 实现!-- 文件路径src/main/resources/mapper/OrderMapper.xml -- select idselectPageByCursor resultTypecom.example.demo.entity.Order SELECT id, order_no, user_id, amount, status, created_at FROM orders where if testlastCreatedAt ! null and lastCreatedAt ! created_at lt; #{lastCreatedAt} OR (created_at #{lastCreatedAt} AND id lt; #{lastId}) /if /where ORDER BY created_at DESC, id DESC LIMIT #{limit} /select注意条件里同时带上created_at和id是为了处理排序字段重复的情况。如果只按created_at排序而同一秒内有多条记录翻页时可能丢数据或重复数据。再加上id后排序和过滤条件都是唯一且确定的。在 XML 中需要使用lt;这是 MyBatis 的常见坑很多新手第一次写游标分页都会在这里报错。业务层调用时需要把上一页最后一条记录的created_at和id取出来封装到参数里// 文件路径src/main/java/com/example/demo/service/OrderService.java public ListOrder pageByCursor(Order lastOrder, int limit) { PageCursorParam param new PageCursorParam(); if (lastOrder ! null) { param.setLastCreatedAt(lastOrder.getCreatedAt()); param.setLastId(lastOrder.getId()); } param.setLimit(limit); return orderMapper.selectPageByCursor(param); }这个示例展示的是最简单的同步分页。在真实项目里返回结果还会附带游标信息方便前端传回后端。5.4 如何运行与验证运行前提数据库表结构创建成功。测试数据已经插入。Spring Boot 项目可以正常启动。如果只想快速验证延迟关联效果可以不启动项目直接在 MySQL 客户端执行两条 SQL对比执行计划EXPLAIN SELECT id, order_no, user_id, amount, status, created_at FROM orders ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 100000; EXPLAIN SELECT t.id, t.order_no, t.user_id, t.amount, t.status, t.created_at FROM orders t INNER JOIN ( SELECT id FROM orders ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 100000 ) tmp ON t.id tmp.id ORDER BY t.created_at DESC, t.id DESC;游标分页则可以这样验证EXPLAIN SELECT id, order_no, user_id, amount, status, created_at FROM orders WHERE created_at 2024-11-01 12:00:00 ORDER BY created_at DESC, id DESC LIMIT 20;查看执行计划时重点看rows字段传统分页在深分页时扫描行数会随着OFFSET增大而明显增长延迟关联和游标分页的扫描行数则更接近目标行数。6. 运行结果与效果验证6.1 用 EXPLAIN 验证执行计划以 50 万行数据为例传统深分页的执行计划通常会在rows字段显示很大的估算值。如果表结构没有正确索引还会出现Using filesort。延迟关联的 inner 查询会走idx_created_id索引执行计划一般能看到typeindexkeyidx_created_idExtraUsing index这意味着查询完全在索引上完成不需要回表。外层通过主键id回表时只读取LIMIT对应的少量行所以耗时远小于原始方案。游标分页在WHERE created_at ?条件下如果索引生效执行计划会显示type为range扫描范围控制在两个连续值之间性能稳定。6.2 页面耗时与扫描行数对比优化的验证不能只看 SQL 是否多读了索引。建议做两组对比第一组固定 OFFSET对比原始 SQL 和延迟关联 SQL。在服务层分别调用两个查询。用日志打印耗时。记录OFFSET 1000、10000、50000、100000时的耗时变化。结果是在数据量较小时两者差异不明显随着OFFSET增大原始 SQL 耗时增长更快延迟关联的耗时相对平缓。这也说明一个原则没有深分页场景的表不需要为了优化而优化。第二组游标分页和传统分页对比。模拟连续翻页例如连续翻 100 页记录每页的平均耗时。传统分页在第 1 页和第 100 页的耗时可能相差很大游标分页则每一页耗时都接近。具体判断标准可以这样定如果OFFSET超过 1 万行时接口 P99 延迟明显超过 500ms说明问题真实存在。如果优化后同样OFFSET下耗时下降 50% 以上方案有效。如果扫描行数显著下降且没有新增慢查询说明可以进入灰度阶段。这里不要纠结于“到底降了多少毫秒”因为不同机器的磁盘、内存、并发压力差异很大。更重要的是看趋势优化方案是否在大偏移量下保持低延迟。7. 常见问题与排查思路问题现象可能原因排查方式解决方案延迟关联后反而更慢表数据量太小优化优势不明显或索引缺失查看 EXPLAIN确认是否使用覆盖索引确保(created_at, id)索引存在扩大到足够数据量测试游标分页出现重复数据只按created_at排序时间字段重复检查上页最后一条记录的时间值排序条件补上id并让过滤条件同时包含时间和 idMyBatis 执行报 SQL 语法错误XML 中的被当成标签开始符查看日志中完整 SQL将改为lt;ORDER BY 和 WHERE 条件不一致翻页时排序键顺序变化对比两条 SQL 的排序字段统一排序条件为created_at DESC, id DESC游标分页无法跳页产品交互不支持基于游标跳页和产品确认需求保留传统分页接口或改用延迟关联深分页时索引失效查询条件里使用了函数、隐式转换使用EXPLAIN查看type字段避免在索引列上使用函数保持数据类型一致插入大量测试数据很慢单条 INSERT 频繁提交使用存储过程或批量插入脚本分批事务提交每批 1000 条左右这些问题是实际改造时比较常见的坑尤其是 MyBatis 中lt;转义和排序字段重复很容易出现。8. 最佳实践与工程建议8.1 改造前先确认场景不是所有分页都需要从传统方案迁移到延迟关联或游标分页。建议先做一次盘点列表接口的分页深度分布如何用户主要翻前几页还是深翻数据量增长趋势是否会导致问题恶化后台管理系统是否需要精确跳页如果查询基本上只翻前几页OFFSET不超过几十优化就没有太大意义。相反如果存在“导出全量数据”“爬虫高频翻页”“用户大批量浏览”的情况深分页优化会带来明显收益。8.2 让“坏主意”进入评审闭环一个方案被提出时最容易出现的两种极端是直接否定或者无脑接受。更合理的流程是把它拉进一个轻量评审闭环写清楚现状指标当前接口耗时的 P95、P99扫描行数。明确方案改动点SQL、索引、接口参数、前端交互。设计小范围验证用测试环境或影子流量做对比。制定回滚方案先改接口再切换前端或者先灰度 10% 流量。回归性能指标对比优化前后趋势而不是单次数据。“I got a bad idea”这句话最理想的后续是“但我们可以先花半小时验证一下”。8.3 生产环境的安全意识延迟关联和游标分页改造涉及的是数据库查询路径操作前要注意以下几点索引变更属于 DDL线上执行会锁表或占用资源务必在低峰期进行并有备份和回滚方案。不要直接在核心库上跑大规模测试数据优先在测试环境验证。生产环境灰度发布时先让少量流量命中新 SQL观察数据库慢查询数量、CPU 和连接池占用情况。如果游标分页参数由前端传入必须校验参数长度和格式避免恶意构造超长条件。任何 SQL 改动都要结合查询日志和监控平台回看不要只看一次手工查询结果。这些意识比具体代码更重要。毕竟查询优化这件事改错了不会立刻报警但可能在业务高峰期集中爆发。9. 总结回到标题那句话“I got a bad idea..”。它可能是一句自嘲也可能是一个被低估的优化契机。真正的工程判断不取决于方案听起来是否常规而取决于它是否解决了真实问题、能否被验证、是否可回滚。这篇文章通过深分页这个具体问题讲解了延迟关联和游标分页两种方案。前者适合必须支持跳页的业务后者适合“加载更多”类场景。两者的核心目标都是减少数据库无谓的扫描和回表从而稳定接口延迟。你可能还没有遇到过千万级订单表也可能你的系统在未来半年都不需要这种优化。但下一次听到团队成员说“我有一个坏主意”时不妨先问一句你打算怎么证明它有效如果能用 30 分钟跑一个最小实验用执行计划和耗时代替主观判断很多“坏主意”反而会成为项目里最有价值的优化项。