最近在整理餐厅管理后台的菜品模块一个看似人畜无害的菜品分页查询接口把我折腾得够呛。刚开始我天真地以为这就是一条select * from dish limit offset, size的事真正拆到需求、参数、SQL、性能、联调之后才发现分页查询要兼顾的东西远比想象中多。这篇学习笔记就是我在做菜品接口分页查询过程中最完整的记录从需求定位、接口定义、数据层实现、上下游联调到深分页优化和线上排错每一步我都尽量说透为什么这么做。如果你也正在做管理系统类的接口尤其是菜品、商品、订单这类典型 CRUD 中带筛选和分页的查询接口这篇笔记应该能帮你少走不少弯路。我不光写能跑的代码还会把我踩过的坑、压测的数据和排查的思路一起贴出来方便你直接参考和复现。1. 菜品分页查询接口的需求定位1.1 为什么菜品列表一定要做分页很多人觉得分页是常识但真要问你为什么要做可能一时答不完整。菜品数据在我们餐厅后台大概有三千多条这还只是一个中等规模的连锁品牌。如果接口一次性把所有菜品全返回前端渲染 DOM 会卡顿菜单页下拉会明显掉帧图片懒加载基本失效用户在手机端体验会非常差。更关键的是接口层面一次返回三千条菜品就算每条只算 300 字节的业务字段一次响应也有 1MB 左右在弱网环境下可能拖到几秒甚至超时。数据库层面也不轻松全表查询没有 limit每次都要把三千行数据从磁盘捞出来再通过网络传输这对数据库连接池和网络带宽都是浪费。分页的本质是用按需加载的思维把大结果集切成小份让前端、后端、数据库三方都保持低负载。1.2 需求边界这个查询接口要覆盖哪些场景做接口之前我习惯先问一句这个接口到底服务谁菜品分页查询在我们项目里主要服务两个端管理后台端运营人员按菜品名称、分类、上下架状态筛选菜品需要一页一页地维护数据。业务端小程序/App用户按分类浏览菜品但这个场景一般不是靠分页而是靠分类下的全量列表加上搜索来完成的因为点餐页需要快速看到全部可用菜品。所以我最后把接口定位成面向管理后台的菜品分页查询接口同时保留分类筛选和关键词搜索能力。例如我要查川菜分类下、名字里带鱼、并且已上架的菜品这个接口要能满足。这意味着它不能是简单的一页数据而是一个组合条件的动态查询。明确了边界后面设计参数和返回结构才不会跑偏。2. 接口契约设计参数、返回结构与状态码2.1 分页参数的定义与校验规则接口定义是前后端协作的起点我在项目里把参数封装成一个DishPageQuery对象而不是在 Controller 里散落一堆RequestParam。这样做的好处是参数多的时候不会乱校验逻辑也能集中处理。参数类型必填说明pageInteger是当前页码从 1 开始pageSizeInteger是每页条数默认 10上限 100keywordString否菜品名称模糊搜索categoryIdLong否分类 ID 精确筛选statusInteger否状态筛选1 上架0 下架sortFieldString否排序字段可传 sort、price、createTimesortOrderString否排序方向asc 或 desc校验规则很多人容易忽略尤其是pageSize。如果前端传了个pageSize100000SQL 里就会出现limit 0, 100000这跟不带分页没区别。我在全局参数校验里限制page 1、pageSize必须介于 1 到 100 之间一旦非法直接返回参数错误而不是把风险丢给数据库。还有一个细节page传 0 的情况。MyBatis-Plus 默认会把第 0 页当成第一页处理但如果你手写 SQLlimit 0 offset 0和limit 10 offset 0的结果完全不一样。所以我统一要求前端从 1 开始传页码尽量避免后端去猜语义。2.2 返回结构设计分页接口的返回结构我统一走项目里的ResultT包装状态码里200是成功400是参数错误500是服务器异常。body 中的data部分才是分页数据我单独定义了PageResultVpublic class PageResultV { private Long total; // 总条数 private Long pages; // 总页数 private Long current; // 当前页码 private Long size; // 每页条数 private ListV records; // 当前页数据 }为什么返回total和pages因为前端分页组件需要展示总页数、跳转页码。为什么current和size要回传因为前端需要跟实际请求参数做校准万一后端做了参数修正前端能通过返回值感知到。records 里面我放的是DishVO直接暴露Dish实体类看起来省事但实体里往往带着is_deleted、update_time等内部字段还可能有数据库相关的注解痕迹。把实体吐给前端既增加不必要的数据量也不利于接口数据的稳定性。VO 层做一次字段筛选是接口设计里很值得坚持的一步。2.3 排序稳定性与接口幂等性分页查询接口是 GET天然是幂等的但如果你忽略排序稳定性幂等性也会被破坏。最简单的反面例子按create_time排序同一秒内创建了 5 个菜品第一次请求排序顺序和第二次请求可能不一样于是第一页出现了某个菜品第二页又出现一次用户看到的就是重复数据或翻页错乱。我的解决方法是双重排序先按业务排序字段排再按主键id升序或降序作为兜底。因为主键在 MySQL 的 InnoDB 里是唯一的加上它之后任何一行的顺序都是确定的。代码里我习惯这样写wrapper.orderByAsc(Dish::getSort) .orderByAsc(Dish::getId);如果用户传了sortFieldprice就按price排序后再加上id兜底保证排序结果稳定。这一点在接口定义阶段就要定好规则不能等上线后发现翻页错乱再去补救。3. 数据层实现从SQL到分页插件3.1 手写SQL分页还是用分页插件数据层实现我经历了两个阶段。一开始用 MyBatis-Plus直接依赖它的分页插件确实省事Configuration public class MybatisPlusConfig { Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor new MybatisPlusInterceptor(); interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL)); return interceptor; } }配置好插件后Service 里new Page(page, pageSize)传入 mapper 方法MyBatis-Plus 会自动拦截要执行的 SQL生成 count 语句和 limit 语句。这对简单分页非常友好。但如果项目里没有这种现成框架手写 SQL 也不难SELECT id, name, category_id, price, status, sort, create_time FROM dish WHERE status 1 ORDER BY sort ASC, id ASC LIMIT #{offset}, #{pageSize}手写的好处是 SQL 完全可控你清楚知道数据库到底执行了什么坏处是要自己数 count稍不留神就会忘记在 count 语句里带上筛选条件导致总数不准。我个人的建议是简单查询可以用插件涉及多表联查、复杂聚合时手写 SQL 加 Page 查询组装会更稳妥。3.2 count查询的性能细节分页接口最容易被忽略的性能瓶颈其实是 count 查询。很多人在意 select 语句到底走了什么索引却忘了select count(*)同样要执行一条完整的带 where 条件的 SQL。MyBatis-Plus 分页插件默认会生成一句 count 语句它会把 select 字段替换成 count但 where 条件保留。实际操作中发现一个细节如果你在 SQL 里写了order byMySQL 执行 count 时其实不需要排序因为 count 只统计行数不需要关心顺序。MyBatis-Plus 在解析时会尝试去掉 order by但手写 SQL 时建议主动把 order by 去掉否则没有使用缓存的情况下 count 可能比 select 还慢。另外如果菜品表数据量超过十万级count(*)扫描的天然成本就在那里。数据库层面的优化空间有限有时我会考虑用一个总数缓存来缓解但前提是菜品数据不是高频变更。菜品这种业务数据变更很频繁缓存 time-to-live 设太短没有意义设太长会带来脏数据。所以我的结论是大部分场景下先确保 count 的 where 条件走索引别让 count 变成全表扫描这才是性价比最高的优化。3.3 动态多条件查询的拼接菜品分页查询的关键就是条件不固定。用户可能只按分类查可能只按关键字查也可能分类、状态、关键字一起来。在 MyBatis-Plus 里我用LambdaQueryWrapper可以避免手拼字符串带来的 SQL 注入风险LambdaQueryWrapperDish wrapper new LambdaQueryWrapper(); if (StrUtil.isNotBlank(query.getKeyword())) { wrapper.like(Dish::getName, query.getKeyword().trim()); } if (query.getCategoryId() ! null) { wrapper.eq(Dish::getCategoryId, query.getCategoryId()); } if (query.getStatus() ! null) { wrapper.eq(Dish::getStatus, query.getStatus()); }这里有一个坑我必须单独提一下用户输入的关键词如果包含 SQL 里的%或_like查询会把它们当成通配符处理。比如用户搜50%折扣或老_干妈结果可能匹配到一整片数据。我的处理方式是提供一个转义工具把%和_分别替换成\%和\_再结合like后面的escape处理。这个细节如果不做线上迟早会出一次搜索菜名出现一堆无关数据的故障。4. 业务层、控制层与前端联调4.1 Service层为什么不能只传page和pageSize很多同事写 Service 层时经常把 Controller 里的page、pageSize原封不动地传进去然后在 Service 里直接组装查询。这在小项目里能跑通但一旦业务复杂就会出现逻辑泄漏。比如我们的菜品查询有个隐含规则如果当前登录的是门店店长角色他只能看到自己门店的菜品如果是超级管理员才能看全部菜品。这条规则如果在 Controller 里写一次、在另一个接口里又写一次就会重复出现。正确的做法是把角色数据的过滤逻辑收敛到 Service 层Service 内部根据当前登录用户自动拼接门店条件对外暴露的就是一个干净的pageDish(DishPageQuery query)方法。另外Service 层还负责对查询结果做二次过滤。比如某些内部测试菜品不能在管理后台列表展示这类数据不方便加在 SQL where 条件里因为点餐端可能还要看到我会在 Service 层做排除。这些逻辑放 Controller 会让控制器越来越肿放 Mapper 又会污染 SQL 的通用性放在 Service 层是逻辑上最合适的位置。4.2 控制层参数校验与全局异常处理Controller 里我做得比较克制只做三件事接收参数、校验基础合法性、调用 Service。参数校验我用的是Validated注解加自定义校验同时保留了手动判断兜底。这里分享一个实际教训不要信任前端传的sortField。以前我对排序字段没做白名单用户传sortFieldname就拼到 SQL 里。有次测试人员传了个sortFieldname;select sleep(2)之类的内容虽然 MyBatis-Plus 的 wrapper 机制过滤掉了 SQL 注入风险但也给我提了个醒。现在我要求sortField必须匹配指定的几个字段之一不在白名单内就直接默认按 sort 排序。异常处理统一用RestControllerAdvice把参数校验异常、业务异常、未知异常分别转成对应的错误码和提示信息。这样做的好处是接口返回始终是结构化的 JSON前端对接起来不用针对各种异常堆栈做特殊处理。4.3 返回DTO不能直接吐Entity前面提过 records 里放的是DishVO为什么这么坚持我举个例子Dish 表里我加了一个is_deleted字段做软删除数据库查询时所有 SQL 都带着is_deleted 0条件。但如果你直接把 Dish 实体返回给前端前端就看到了这个字段。这次没出问题下次前端开发看到字段名顺手就用来做过滤一旦过滤条件写错就会出现数据不一致。更实际的问题是字段体积。Dish 实体里面有description大字段、image_url、create_time、update_time、create_by、update_by但列表页其实只需要名称、分类、价格、状态、缩略图。字段全量返回接口响应体白白胖了一圈。用DishVO做裁剪之后响应从平均 2KB 降到了 800B 左右在弱网环境体验完全不一样。转换工具我用过BeanUtils.copyProperties也用过 MapStruct后者性能更好习惯之后反而更省事。5. 深分页、缓存与性能调优实测5.1 深分页为什么慢项目上线初期一切正常直到菜品数据突破了五万运营同事反馈翻到最后一页要等很久。我当时的第一反应是索引失效了一查执行计划索引走了但还是慢。最后定位到这是典型的深分页问题。LIMIT 10000, 20的语义不是跳过一万行再取二十行数据库引擎在执行时还是要扫描前一万零二十行再把前一万行丢掉。走得越快丢得越多这个代价是线性增长的。而且ORDER BY sort ASC, id ASC如果对应的不是覆盖索引每一行数据都需要回表查询主键对应的完整记录深分页的回表次数会非常夸张。我做了个本地压测五万条菜品数据、单表无复杂联查的情况下limit 0, 20耗时才 15ms 左右而limit 40000, 20直接飙到了 420ms。如果查询条件里有like %鱼%这类非左前缀匹配的模糊条件额外扫出的中间结果会更多时间翻倍也不是不可能。5.2 优化的几种思路我压测后做了两个改动效果比较明显。第一个改动是限制最大页码。管理后台的分页逻辑里用户不太可能需要忽然跳到第 1000 页去改数据。我在接口层做了限制超过 100 页的页码统一返回空列表或提示请通过筛选缩小范围。很多人觉得这是逃避问题但它确实是性价比最高的防深分页手段之一因为用户根本不需要访问那么深的数据。第二个改动是采用延迟关联写法。之前的分页 SQL 是先 order by 再 limit再回表拿完整字段。优化成先获取主键 ID 集合再跟原表做关联SELECT d.id, d.name, d.category_id, d.price, d.status, d.sort FROM dish d INNER JOIN ( SELECT id FROM dish WHERE is_deleted 0 ORDER BY sort ASC, id ASC LIMIT #{offset}, #{pageSize} ) t ON d.id t.id ORDER BY d.sort ASC, d.id ASC;这样内层只需要扫描索引树拿到主键后再回表查对应的行。对于深分页场景这个优化通常能把耗时降一个数量级。我在五万数据量的表上实测limit 40000, 20从 420ms 降到了 80ms 左右。另外必须配上合适的联合索引思路是让WHERE等值条件、ORDER BY字段尽量能落在同一个索引树里。比如经常按category_id筛选、按sort排序那就建立(category_id, sort, id)联合索引这样 order by 不再额外触发 filesort性能会稳很多。5.3 菜品列表缓存的取舍有人问过我怎么给菜品分页查询加 Redis 缓存。我的回答是先谨慎判断别一上来就缓存。菜品是个变化频繁的数据价格调整、上架下架、库存变化都会让列表数据失效。分页查询的条件组合又非常多某分类第一页、带鱼关键字第一页、上架状态第一页不同组合的缓存 key 可能命中率很低。我做了一个折中方案只缓存没有关键词、只有固定分类和固定排序的热门查询结果缓存时间为 30 秒。这个场景在前端首页的猜你喜欢、热销榜用得多命中率相对可观。对于管理后台那种带各种搜索词的分页查询我干脆不缓存把精力放在索引和 SQL 优化上因为管理后台的访问量本身不大缓存带来的收益远小于数据一致性风险。6. 排错实录从日志到线上问题的排查链路6.1 一个典型的接口越查越慢排查我把这次线上问题的完整排查链路记录下来供大家参考。现象是运营反馈菜品分页接口翻到后面的页就卡住。我先查了慢查询日志定位到具体 SQL 语句发现是limit 2000, 20和limit 40000, 20这类深分页。再用EXPLAIN看执行计划发现查询走了category_id的单列索引但ORDER BY sort部分没有走索引需要 filesort临时表数据量一大CPU 和 IO 开销就上来了。根因基本明确后我先加了覆盖索引再改造成延迟关联写法随后在代码里限定了最大页码。上线之后观察监控接口 P95 响应时间从 900ms 降到 200ms。这整个过程给我的经验是排查性能问题不能只盯着是否走索引还要看排序、回表、临时文件、数据分布这几个维度。6.2 接口自动化和压测分页接口改动频繁回归测试不能每次靠手工点页面。我用 JUnit 加 MockMvc 给分页接口写了一组自动化测试用例重点覆盖第一页返回条数是否正确total 是否和实际筛选结果一致pageSize 超过上限时是否返回参数错误组合条件查询时结果是否符合预期排序字段传非法值时的兜底逻辑翻页过程中是否有重复或遗漏数据压测我用 JMeter 模拟了 50 个并发用户连续翻页的请求重点关注两个指标接口的平均响应时间和错误率。压测的时候我同时盯着数据库的连接数和慢查询日志防止接口层看起来没问题、数据库层已经吃不消的情况。JMeter 的聚合报告里我还会看吞吐量因为分页查询接口通常会被前端多个组件频繁调用吞吐量直接决定了后端能不能撑住高峰期流量。6.3 容易忽略的坑最后集中说几个我在菜品分页接口开发和维护中踩过的坑。第一个坑是多表联查时排序字段的歧义。菜品分页后期要连分类表查出category_nameSQL 里同时有sort字段和create_time字段如果不加表别名MySQL 会提示Column sort in order clause is ambiguous。所以写多表分页 SQL 时的第一习惯就是给表起别名排序字段一律用别名修饰。第二个坑是状态字段的前后端约定。我们status1表示上架、0表示下架但前端某个版本把筛选条件传成了1字符串MyBatis 里eq还能正常匹配到了手写 SQL 的时候由于类型转换问题就会查不出数据。现在我会在接口文档里明确标注字段类型接口定义阶段就要把这些细节钉死。第三个坑是 keyword 搜索的空白字符。用户从 Excel 复制菜名过来可能带着空格或换行符。我直接在 Service 里先trim()如果为空就不拼条件。否则一个包含\r\n的关键词会让 like 查询匹配不到任何数据而且这个问题在测试环境很难复现一上生产就会被吐槽搜索怎么是坏的。这些坑单个拎出来都不大但每个都可能导致线上故障或者前后端联调扯皮。分页查询接口看起来是最基础的接口类型能不能写稳往往就体现在这些细节里。我现在的习惯是每接一个分页查询需求先问一遍排序稳不稳定、count 能不能走索引、有没有深分页风险、筛选字段会不会被判型不一致。把这些问题想清楚再动手写代码后面返工的成本会小很多。