一次线上事故一次性能调优让我下定决心彻底告别LIMIT ? OFFSET ?这种深分页写法。一、深分页把数据库搞挂了某天周五下午运营同学后台点开订单第 10000 页页面转圈超过 10 秒前端开始大量 504数据库 CPU 直接飙到 98%。紧急排查罪魁祸首就是这条 SQLSELECT*FROMordersORDERBYidLIMIT20OFFSET199980;orders 表当时 280 万行分了 64 个库表。主键索引是有的但OFFSET 199980意味着 MySQL 得先扫描前 199980 行再全扔掉只留最后 20 行。这操作放到谁身上都扛不住。类似的问题在 C 端系统里也常见用户中心的消息列表、资讯列表、Feed 流一旦翻个几十页接口响应时间指数级上涨。很多人把锅甩给“数据库不行”其实是我们分页的姿势不对。二、EXPLAIN 一看毛病全在扫描为了把问题看透建一张实验表CREATETABLEorders(idbigintNOTNULLAUTO_INCREMENT,user_idbigintNOTNULL,order_novarchar(64)NOTNULL,amountdecimal(10,2)NOTNULL,statustinyintNOTNULL,created_atdatetimeNOTNULLDEFAULTCURRENT_TIMESTAMP,PRIMARYKEY(id),KEYidx_created_at_id(created_at,id))ENGINEInnoDBDEFAULTCHARSETutf8mb4;插 100 万行测试数据模拟一次深分页查询按创建时间倒序第 10000 页每页 20 条拿EXPLAIN看EXPLAINSELECT*FROMordersORDERBYcreated_atDESC,idDESCLIMIT20OFFSET199980;执行计划里 select_type 是 SIMPLEtable 是 orderstype 是 index表示它打算走二级索引idx_created_at_id预计扫描 200000 行。索引是(created_at, id)按这个排序正好满足ORDER BY created_at DESC, id DESC所以不需要 filesort直接扫就行。问题在于查询列是*索引里没有 user_id、amount 那些字段每扫一条索引记录就必须拿着主键 id 再回表拿完整行——一次随机 IO。扫 20 万行回表 20 万次最后只留下 20 条。这钱花得太冤枉。OFFSET 越大数据库被迫扫描丢弃的行越多响应时间自然线性飙涨。深分页慢的本质就是数据库花了大量精力去扫描和丢弃无用行而不是直接定位到目标区间。三、几个常见分页方案挨个聊1. 传统LIMIT ? OFFSET ?最直接的分页写法ORM 框架默认基本都是它。SELECT*FROMordersORDERBYcreated_atDESC,idDESCLIMIT20OFFSET199980;优点就是实现简单而且支持跳页用户想跳哪页就哪页。缺点是 OFFSET 一大性能就完蛋全是无效扫描和回表。几千条数据的后台管理列表用着没毛病数据量上来再这么玩就等着报警吧。2. 子查询 / 延迟关联思路是先拿覆盖索引查出目标行的主键再用主键去 JOIN 原表拿完整行尽量减少回表次数。SELECTo.*FROMorders oJOIN(SELECTidFROMordersORDERBYcreated_atDESC,idDESCLIMIT20OFFSET199980)tmpONo.idtmp.idORDERBYo.created_atDESC,o.idDESC;内层子查询只走索引不碰回表IO 压力小了很多。但别高兴太早子查询里照样要把 OFFSET 之前的索引项逐条扫过扔掉只是省了回表而已。而且 MySQL 优化器有时会把子查询物化掉搞出临时表性能不稳定。作为临时方案还行但别指望它彻底解决深分页。3. 覆盖索引覆盖索引的意思是查询需要的所有列都能在索引里直接拿到不用回表。比如只查id和created_at二级索引里都有直接返回。SELECTid,created_atFROMordersORDERBYcreated_atDESC,idDESCLIMIT20OFFSET199980;这种情况下速度确实能提升不少。但实际业务哪有这么简单列表通常要展示一堆字段把所有列都塞进索引里索引体积会变得很大写入性能也会被拖累。而且 OFFSET 本身需要扫描的索引项一点没减少。所以覆盖索引适合查询列少、分页不深的场景通用性一般。4. Keyset Pagination游标分页思路完全不一样不通过 OFFSET 去跳过数据而是记住上一页最后一个排序位置用 WHERE 条件直接定位下一页的起始点。SELECT*FROMordersWHERE(created_at,id)(2024-05-01 10:00:00,123456)ORDERBYcreated_atDESC,idDESCLIMIT20;(created_at, id)是有序索引MySQL 可以直接用它定位到游标位置然后往后取 20 条。不管翻多深扫描的行数始终只有那 20 条响应时间基本恒定。缺点是没法跳页必须一页一页顺着翻。但仔细想想C 端 App 的列表基本都是滚动加载谁会去点第 10000 页这缺点在大多数场景下根本不是问题。四、Keyset Pagination 手把手实现1. 基本原理上一页最后一条记录是(created_at 2024-05-01 10:00:00, id 123456)下一页的查询条件就是WHEREcreated_at2024-05-01 10:00:00OR(created_at2024-05-01 10:00:00ANDid123456)写成行构造器更简洁WHERE(created_at,id)(2024-05-01 10:00:00,123456)MySQL 会拿这个行构造器和索引的有序性做匹配直接定位到游标处。这里有个硬性要求排序字段必须唯一。如果只按created_at排序长时间相同的数据会有好几条翻页时靠肯定漏数据所以第二排序字段必须加主键id。也正因如此Keyset 分页天然适合基于游标的场景。2. 单列排序如果只按主键id倒序那最简单publicclassOrderQuery{privateLonglastId;// 上一页最后一条记录 id第一页传 nullprivateIntegerpageSize20;publicStringbuildWhere(){if(lastIdnull)return;returnWHERE id lastId;}publicStringbuildOrderLimit(){returnORDER BY id DESC LIMIT pageSize;}}这个方案可以彻底避开深翻页但同样不能跳页只能顺着翻。3. 多列排序通用写法实际业务里排序通常不会只有 id比如ORDER BY created_at DESC, id DESC。这时需要定义一个游标对象publicclassOrderCursor{privateLocalDateTimecreatedAt;privateLongid;// getter / setter / constructor}MyBatis Mapper 的写法publicclassOrderMapper{publicStringbuildCursorCondition(OrderCursorcursor){if(cursornull){return;}returnWHERE (created_at #{cursor.createdAt}) OR (created_at #{cursor.createdAt} AND id #{cursor.id});}}对应的 MyBatis XMLselectidselectByCursorresultTypeOrderSELECT * FROM ordersiftestcursor ! nullWHERE (created_atlt;#{cursor.createdAt}) OR (created_at #{cursor.createdAt} AND idlt;#{cursor.id})/ifORDER BY created_at DESC, id DESC LIMIT #{pageSize}/selectService 层调用时第一页传 null得到结果后取最后一个 Order构造新游标下一次查询带上。publicListOrderpage(OrderCursorcursor,intpageSize){returnorderMapper.selectByCursor(cursor,pageSize);}4. 混合排序方向尽量别碰如果排序是ORDER BY created_at DESC, id ASC两个方向不一致游标条件写起来很别扭而且索引默认升序倒序读时 id 也是倒序想让它升序就不好走索引了。工程上最稳妥的办法是统一排序方向或者干脆用覆盖索引加子查询绕开。真遇到这种需求建议先跟产品掰扯掰扯多半能改。5. 增量同步场景Keyset 分页做增量数据同步也特别顺手。比如定时把订单同步到数仓首次同步没有游标就全量拉之后每次同步完成把最后一条记录的 id 或 updated_at 存到元数据表下次直接从游标开始拉。数据量再大也不慌每批只取固定条数不重不漏。五、性能实测传统 LIMIT 被按在地上摩擦我在本地用 100 万行数据试了一下MySQL 8.0.33Apple M1 Pro分别跑了传统 LIMIT、覆盖索引、子查询、Keyset 四种方案模拟不同页偏移量的单条 SQL 响应时间。结果有点夸张第 1 页大家差不多都在 10ms 上下。第 1000 页传统 LIMIT 跑到 86msKeyset 还是 12ms。第 5000 页传统 LIMIT 已经 348ms子查询 104msKeyset 15ms。第 10000 页传统 LIMIT 830ms覆盖索引 490ms子查询 215msKeyset 18ms。第 50000 页传统 LIMIT 直接飙到 4.2s覆盖索引 2.1s子查询 1.1sKeyset 22ms。随着页偏移量增加传统 LIMIT 的响应时间几乎线性往上走而 Keyset 始终稳定在 20ms 左右。这差距不是靠调参数能抹平的完全是访问路径的不同。具体压测代码就不贴了无非是用 JMH 跑那几个 SQL有兴趣的可以自己写。结论很明确深分页场景下Keyset 是降维打击。六、在 MyBatis-Plus / JPA 里落地原理搞懂了代码也要能塞进项目里。MyBatis-Plus默认的分页插件PaginationInnerInterceptor只支持 LIMIT OFFSETKeyset 得自己写 Mapper。定义一个接口publicinterfaceOrderMapperextendsBaseMapperOrder{ListOrderselectPageByCursor(Param(cursor)OrderCursorcursor,Param(pageSize)intpageSize);}XML 里写selectidselectPageByCursorresultTypeOrderSELECT * FROM orderswhereiftestcursor ! nulliftestcursor.createdAt ! null(created_at, id)lt;(#{cursor.createdAt}, #{cursor.id})/if/if/whereORDER BY created_at DESC, id DESC LIMIT #{pageSize}/select注意这里用的是 MySQL 的行构造器写法如果切到 PostgreSQL 要改成(created_at, id) (?, ?)也行但有些数据库语法不一样需要适配。调用时publicPageResultOrderlistByCursor(OrderCursorcursor,intpageSize){ListOrderrecordsorderMapper.selectPageByCursor(cursor,pageSize);OrderCursornextCursornull;if(!records.isEmpty()){Orderlastrecords.get(records.size()-1);nextCursornewOrderCursor(last.getCreatedAt(),last.getId());}returnnewPageResult(records,nextCursor);}PageResult里除了记录把下一个游标也带出去前端下次请求时带上正好是游标分页的天然接口。Spring Data JPAJPA 可以用 Specification 动态拼条件。实体类不写了直接看核心publicclassOrderSpecs{publicstaticSpecificationOrderbyCursor(OrderCursorcursor){return(root,query,cb)-{if(cursornull){returncb.conjunction();}PathLocalDateTimecreatedAtroot.get(createdAt);PathLongidroot.get(id);returncb.or(cb.lessThan(createdAt,cursor.getCreatedAt()),cb.and(cb.equal(createdAt,cursor.getCreatedAt()),cb.lessThan(id,cursor.getId())));};}}Repository 继承JpaSpecificationExecutor调用时publicPageResultOrderlistByCursor(OrderCursorcursor,intpageSize){SpecificationOrderspecOrderSpecs.byCursor(cursor);ListOrderrecordsorderRepository.findAll(spec,PageRequest.of(0,pageSize,Sort.by(Sort.Order.desc(createdAt),Sort.Order.desc(id)))).getContent();// 构造 nextCursor...}通用组件封装如果项目里多个表都要用 Keyset可以抽个通用对象publicclassCursorPageT,C{privateListTrecords;privateCnextCursor;// null 表示没有更多数据}然后通过FunctionT, C把“从最后一条记录构建游标”的逻辑做成通用方法具体排序字段由调用方决定这样能少写不少重复代码。七、搜索场景下的 ES search_after关系型数据库的 Keyset 很香但遇到复杂搜索、全文检索、聚合排序还是得上 Elasticsearch。ES 同样有深分页问题from size深分页要遍历所有分片再合并排序代价大得惊人。官方方案叫search_after说白了就是分布式版的 Keyset。ES 查询排序{size:20,query:{match_all:{}},sort:[{created_at:desc},{id:desc}]}返回结果里有sort字段hits:[{_id:123,_source:{created_at:2024-05-01 10:00:00,id:123},sort:[2024-05-01 10:00:00,123]}]下一页就把最后一个sort值放进search_after{size:20,query:{match_all:{}},sort:[{created_at:desc},{id:desc}],search_after:[2024-05-01 10:00:00,123]}思想和数据库游标一模一样只是 ES 需要跨分片保证全局排序的一致性所以不能只靠一个id必须把排序组合值传过去。注意如果 UI 需要跳页search_after就无能为力了。ES 7.x 里的scroll更适合大数据量后台导出别拿来做实时查询。实时查询要是必须跳页就只能fromsize限制在浅分页范围内。八、总结与最佳实践LIMIT OFFSET在数据量上了百万级之后早晚会成为瓶颈。Keyset Pagination 用简单的WHERE 排序字段替代了昂贵的扫表让深翻页的响应时间保持恒定这个思路值得每个开发者掌握。先评估业务需不需要跳页。C 端 App 的列表基本都是无限滚动Keyset 完美适配管理后台如果数据量不大传统分页也没啥问题别硬上。排序字段必须唯一。常用组合就是“业务时间字段 主键”保证排序稳定不然会漏数据。游标分页替代不了搜索分页。有复杂查询条件还是把数据同步到 ES让 ES 扛搜索MySQL 只做存储。封装成统一组件。基于 MyBatis-Plus / JPA 抽个CursorPage团队人人都能直接用省得每次都重新写一遍游标逻辑。上线前压一下。用 JMH 或者 JMeter 模拟接近真实的数据量看看 TP99再决定分页策略要不要换。深分页优化这事儿说简单也简单一句话就是“别让数据库扫描多余的行”。但真做起来还是得理解索引结构和访问路径否则很容易被各种“优化方案”带偏。希望这篇文章能帮你把这些坑提前踩平让线上接口在百万级数据下也能稳得住。