MyBatis多表查询与分页实战：从原理到深分页优化

做后端开发这几年MyBatis 的多表查询和分页查询几乎每个项目都会碰到但真正能把这两块玩明白、不踩坑的人其实不多。网上资料要么只讲个select join的写法要么上来就贴一堆 resultMap看得人云里雾里。这篇博客不打算讲那种“照着敲能跑”的入门 demo而是从实际项目中经常纠结的问题出发多表查询的映射结果为什么行数翻倍、PageHelper 用着用着怎么突然分页失效了、深分页慢成狗到底该怎么优化、MyBatis Plus 里想跳过逻辑删除怎么处理。如果你正在用 Spring Boot 整合 MyBatis或者在面试前想把这几个高频考点彻底吃透这篇文章值得花十分钟看完。我会把多表查询的映射原理、分页插件的工作机制、以及一堆踩坑经验一次讲完争取让你读完能直接拿去用。1. 多表查询的方案选型与核心思路1.1 先搞清楚对象关系再动手很多新手拿到多表查询的需求就急着写 SQL结果写出来的结果要么字段对不上要么查出重复数据。我一般建议先花两分钟把对象关系理清楚因为 MyBatis 的多表查询本质上是在做一件事把数据库表之间的关联关系映射到 Java 对象的嵌套结构上。对象关系无非三种一对一、一对多、多对多。一对一比如订单表关联用户表一个订单属于一个用户返回一个对象里嵌另一个对象用association标签。一对多比如订单关联订单明细一个订单有多个商品条目返回一个对象里嵌一个 List用collection标签。多对多比如学生选课本质上是两个一对多中间表拆开以后处理方式和一对多没有区别重点在于中间结果的去重。这个阶段还需要想明白一个关键问题到底是用一条大 JOIN 查出所有字段还是分多条 SQL 查询后在内存里组装。MyBatis 提供了两种对应的手段分别是嵌套结果映射和嵌套查询。嵌套结果映射就是靠一条 SQL 把关联表的列全部查出来再通过 resultMap 的association、collection子标签把列分配到子对象上。嵌套查询则是在 resultMap 里配置一个 select 属性MyBatis 在遍历主结果时会再次执行一条子查询去拿关联数据。两种方案没有绝对的好坏选型取决于数据量和表结构的复杂程度。关联数据量可控、层级不深、分页维度在主表上我用 JOIN 嵌套结果关联层级深、需要按需加载、或者关联表的查询条件很独立我会考虑嵌套查询。后面会专门讲嵌套查询最经典的 N1 问题这里先记住结论默认优先用 JOIN除非你有明确的理由要懒加载。1.2 association 与 collection 的配置细节直接给一个最常用的 resultMap 模板场景是订单列表需要返回用户昵称和订单下的商品明细列表。resultMap idorderDetailMap typecom.example.entity.Order id propertyid columnid / result propertyorderNo columnorder_no / result propertyuserId columnuser_id / result propertycreateTime columncreate_time / association propertyuser javaTypecom.example.entity.User id propertyid columnuser_id / result propertynickname columnnickname / /association collection propertyitems ofTypecom.example.entity.OrderItem id propertyid columnitem_id / result propertyproductName columnproduct_name / result propertyquantity columnquantity / /collection /resultMap对应的 SQL 大概是这样的SELECT o.id, o.order_no, o.user_id, o.create_time, u.nickname, oi.id AS item_id, oi.product_name, oi.quantity FROM t_order o LEFT JOIN t_user u ON o.user_id u.id LEFT JOIN t_order_item oi ON o.id oi.order_id WHERE o.create_time #{startTime} ORDER BY o.create_time DESC有两点必须多说一句。第一id标签不只是主键的意思它还是 MyBatis 结果去重的依据。一对多查询时一条订单带三条明细JOIN 出来是三行记录MyBatis 靠id标签判断哪几行属于同一个订单对象然后把明细塞进同一个 List 里。如果你漏掉id或者指定的列不是唯一标识去重逻辑会乱结果里会出现大量重复的父对象。第二关联表查询的列名非常容易撞车。比如 t_order 里有个statust_order_item 里也有个status直接用*查询时 resultMap 会映射错乱。我习惯给所有关联字段都起别名并且在前端返回的 DTO 里用驼峰命名比如item_status、order_status别偷懒写SELECT *这个问题在真实项目里能坑死一票人。说到懒加载如果要在association或collection上配置fetchTypelazy还需要在 MyBatis 全局配置里打开lazyLoadingEnabled并把aggressiveLazyLoading设为 false。实际项目里我基本不开启 MyBatis 的懒加载因为它带来的性能收益不可控反而增加了 Debug 时查 SQL 的复杂度。1.3 嵌套查询的隐患N1 问题嵌套查询配置起来很省事比如在collection里这样写collection propertyitems selectcom.example.mapper.OrderItemMapper.selectByOrderId column{orderIdid} /意思是查完一条订单再根据订单 id 去执行selectByOrderId查商品列表。如果主查询返回 100 条订单这个子查询就会被执行 100 次再加上主查询 1 次总共 101 条 SQL这就是经典的 N1 问题。N1 问题在数据量小的时候看不出来一旦订单数上到几百上千数据库压力会瞬间拉满接口响应时间呈线性上涨。我之前接手过一个项目主列表查询只返回 50 条数据但因为有嵌套查询实际产生了几十条额外 SQL页面加载慢得离谱。排查这个问题的办法很简单把 MyBatis 的 SQL 日志打开看一次请求里到底执行了多少条 SQL只要看到同样的 SELECT 反复出现基本就是 N1 没跑了。解决办法也很直白要么改用 JOIN 嵌套结果映射用一条 SQL 查出全部数据要么先查出主列表收集 id 列表再用WHERE order_id IN (...)批量查出来最后在 service 层做内存组装。第二种方案在分页场景下特别实用后面第三章会演示完整做法。2. 分页查询的实现原理与实战2.1 PageHelper 使用与踩坑分页查询是后端接口里最基础也最容易被写坏的功能。单独用 MyBatis 的话最常用的分页方案是 PageHelperSpring Boot 项目引入依赖再配一下就能用。dependency groupIdcom.github.pagehelper/groupId artifactIdpagehelper-spring-boot-starter/artifactId version1.4.7/version /dependency配置方面我常用的几个参数pagehelper: helper-dialect: mysql reasonable: true support-methods-arguments: true params: countcountSql使用方式非常简单PageHelper.startPage(pageNum, pageSize); ListOrder list orderMapper.selectPageList(keyword); PageInfoOrder pageInfo new PageInfo(list);这里有一个最常见的坑PageHelper.startPage()之后必须跟着一条 Mapper 查询中间如果穿插了一行其他查询代码比如先从 Redis 查个缓存再执行 Mapper分页参数就会被下一个查询消耗掉导致你的目标查询没有被分页。更隐蔽的情况是在同一个方法里startPage后先调用了另一个 Mapper 的查询分页作用在了无关的查询上目标查询反而查出了全量数据。reasonable这个配置也很重要。开启后当 pageNum 小于 1 时会自动修正为 1pageNum 超过最大页数时会自动查最后一页。这个配置在边界情况下能避免接口报错但也容易掩盖掉前端传参的 bug我通常会在联调阶段开启上线前根据业务决定是否保留。另外排序字段尽量不要直接拼接在 SQL 末尾。如果业务上有排序需求比如order by create_time desc建议在 XML 里用choose判断传入的排序字段名只允许白名单里的字段进 SQL防止 SQL 注入。这个点面试官比较爱问要在项目中形成习惯。2.2 分页插件的工作原理用 PageHelper 用久了很多人会好奇它到底是怎么把一条普通查询改写成分页 SQL 的。其实原理不复杂PageHelper 本质上是一个 MyBatis 插件通过拦截器机制拦截了 Executor 的 query 方法。简单说一下 MyBatis 的执行链路SqlSession 执行 CRUD 时会把请求交给 ExecutorExecutor 内部再通过 StatementHandler 去和 JDBC 打交道。PageHelper 的核心拦截器是PageInterceptor它在 Executor.query 方法执行前拦截做两件事第一拿到 ThreadLocal 里保存的分页参数第二根据当前执行的 MappedStatement 生成分页 SQL 和 count SQL。这里有个关键点为什么startPage要紧接着查询。因为PageHelper.startPage()内部是把分页参数放进了 ThreadLocal下一个执行的查询会判断 ThreadLocal 里是否有参数有就做分页处理处理完之后马上清掉。如果你在中间调用了别的查询分页参数就被那个查询“消费”掉了。分页 SQL 的生成基于不同的方言MySQL 就是拼limit offset, size或者limit size offset offset。count SQL 会去掉原 SQL 的 order by 和 select 列包一层select count(0)但遇到很复杂的 SQL 时自动生成的 count 可能有问题需要在 mapper 里手写 count 方法或者在配置里指定countSuffix来手动覆盖。从分页类型上看PageHelper 做的是物理分页也就是真的只查询当前页需要的数据。与之相对的是逻辑分页也就是一次性把全表数据加载到内存用 Java 层面做截取。逻辑分页在小数据量下看着方便但数据量一大就必然 OOM所以实际项目一律用物理分页。2.3 深分页问题与 Redis 优化方案分页做得多了你会发现前几页响应很快越往后翻越慢翻到几百页的时候 MySQL 直接卡死。这背后的原因不是数据量本身大而是limit的机制问题。比如这样一条 SQLSELECT * FROM t_order ORDER BY create_time DESC LIMIT 100000, 20MySQL 的limit 100000, 20并不是直接定位到第 100000 行开始读而是先扫描出前 100020 行然后把前 100000 行丢弃只返回最后 20 行。也就是说翻得越深MySQL 白扫描的数据越多耗时自然越久。针对深分页我实际项目里用过三种方案各有适用场景。方案一游标分页。适合移动端下拉加载不需要跳页的场景。核心思想是不用页码而是记录上一页最后一条数据的 id查询下一页时带上lastId。ListOrder list orderMapper.selectByCursor(lastId, pageSize);select idselectByCursor resultTypecom.example.entity.Order SELECT id, order_no, user_id, create_time FROM t_order WHERE id #{lastId} ORDER BY id ASC LIMIT #{pageSize} /select游标分页的好处是无论翻多深SQL 都能利用主键索引快速定位性能非常稳定。缺点是无法跳页用户想直接跳到第 100 页就做不到了。方案二覆盖索引加延迟关联。如果业务必须支持传统页码又不想让深分页拖垮数据库可以先只查主键再通过主键关联回原表查完整数据。SELECT o.id, o.order_no, o.user_id, o.create_time FROM ( SELECT id FROM t_order ORDER BY create_time DESC LIMIT 100000, 20 ) tmp JOIN t_order o ON tmp.id o.id这个方案用到了覆盖索引优化子查询只需要扫描索引而不需要回表读完整数据最后只对 20 条 id 做回表性能提升非常明显。方案三Redis 缓存分页结果。这个方案适合数据量大且分页访问比较集中的场景比如排行榜、热榜列表。我当时的做法是把订单 id 列表按页缓存到 Redis 里key 设计成order:page:{pageNum}value 存这一页的订单 id 列表。查询时先从 Redis 取 id再拿 id 去数据库批量查订单详情。Redis 方案的关键在于缓存一致性。订单新增、删除、状态变更都会影响列表页内容我的处理方式是给缓存设一个很短的过期时间比如 30 秒到 1 分钟牺牲一点实时性换取数据库压力下降。对于实时性要求不高的列表页这个折中完全够用。如果数据变化非常频繁就不建议用 Redis 缓存页结果了直接用游标分页更稳。3. 真实场景实战订单多表查询加分页3.1 业务需求与表结构设计讲完基础用一个实际场景把所有知识点串起来。需求是这样的订单列表页需要展示订单号、下单时间、下单用户昵称以及订单包含的商品明细。列表按创建时间倒序分页每页 10 条同时支持按订单号模糊搜索。表结构简化为三张表t_order订单主表字段有 id、order_no、user_id、create_time、statust_user用户表字段有 id、nicknamet_order_item订单明细表字段有 id、order_id、product_name、quantity、price这里最容易踩的坑是列表需要展示商品明细而一个订单通常有多个商品。如果直接把三张表 JOIN结果行数会按商品数量翻倍Order 对象在 resultMap 里虽然会被去重但分页的 count 会算错LIMIT作用在 JOIN 后的临时表上导致分页结果不稳定。所以正确思路是分页查询只查主表和用户表商品明细单独查再在 service 层组装。3.2 SQL 与 Mapper 完整实现主查询 SQL 这样写重点是不 JOIN 明细表保证一页对应订单数准确select idpageOrders resultMaporderPageMap SELECT o.id, o.order_no, o.user_id, o.create_time, o.status, u.nickname FROM t_order o LEFT JOIN t_user u ON o.user_id u.id where if testkeyword ! null and keyword ! bind namelikeKeyword value% keyword %/ AND (o.order_no LIKE #{likeKeyword} OR u.nickname LIKE #{likeKeyword}) /if /where ORDER BY o.create_time DESC /selectresultMap 只需要订单字段加一个association用户信息不需要collection。这样 PageHelper 自动生成的 count SQL 也会很简单不会出现一对多导致的 count 翻倍问题。接着是批量查询商品明细select idselectItemsByOrderIds resultTypecom.example.entity.OrderItem SELECT order_id, product_name, quantity, price FROM t_order_item WHERE order_id IN foreach collectionorderIds itemorderId open( separator, close) #{orderId} /foreach /select最后在 service 层做组装PageHelper.startPage(pageNum, pageSize); ListOrder orders orderMapper.pageOrders(keyword); ListLong orderIds orders.stream().map(Order::getId).toList(); if (CollectionUtils.isNotEmpty(orderIds)) { ListOrderItem items orderItemMapper.selectItemsByOrderIds(orderIds); MapLong, ListOrderItem itemMap items.stream() .collect(Collectors.groupingBy(OrderItem::getOrderId)); orders.forEach(order - order.setItems(itemMap.getOrDefault(order.getId(), new ArrayList()))); } return new PageInfo(orders);这样整个过程只执行了三条 SQL一条分页主查询、一条 count、一条批量明细查询。无论一页多少条数据SQL 数量都是固定的完美避免 N1。3.3 Count 查询与分页结果组装PageHelper 会自动执行 count 查询但它生成的 count 在某些场景下会出问题。比如主查询里带了group by或者distinct自动生成的 count SQL 返回的结果可能不是你想要的。这时候解决办法是在 mapper 里单独写一个countOrders方法通过 PageHelper 的countSuffix配置指定后缀。pagehelper: count-suffix: Count然后在 mapper 里写select idpageOrdersCount resultTypelong SELECT COUNT(*) FROM t_order o LEFT JOIN t_user u ON o.user_id u.id where if testkeyword ! null and keyword ! bind namelikeKeyword value% keyword %/ AND (o.order_no LIKE #{likeKeyword} OR u.nickname LIKE #{likeKeyword}) /if /where /selectPageHelper 在执行主查询时会自动根据方法名匹配pageOrdersCount作为 count 查询这样 count 的 SQL 由我们自己控制稳定性高很多。组装分页结果时注意 PageHelper 返回的PageInfo对象里有total、pageNum、pageSize、pages等字段直接封装成统一响应体返回给前端就行。千万不要在拿到List后又new ArrayList(list)这样会丢掉分页信息PageInfo转换后 total 就变成当前页条数了。之前有过同事在这里栽了跟头前端一直以为总共就 10 条数据。4. 热榜问题的解决与排查实践4.1 特定查询禁用逻辑删除的两种做法MyBatis Plus 的逻辑删除功能很好用实体类字段标上TableLogic后MP 会自动在查询条件里加上logic_delete 0。但有时业务上确实需要查出已删除的数据比如管理后台要恢复用户、统计历史报表。热词里有“禁用逻辑删除”这里说两个我实际用过的做法。做法一绕过 MyBatis Plus 内置方法。MP 的自动逻辑删除只对它自身注入的 SQL 生效你自己写的 mapper.xml 里的 SQL 不会自动拼接逻辑删除条件。所以在 XML 里手写 SQL 查询是最直接的跳过方式。做法二对特定 Mapper 方法单独处理。如果你在某个方法上只想忽略逻辑删除可以在自己的 XML 里写原生的SELECT * FROM t_order这样 MP 不会自动加WHERE logic_delete 0。但要注意如果实体类字段上有TableLogicMyBatis 仍然会正常映射不会报错。这里要特别提醒跳过逻辑删除属于危险操作一定要确保业务上真的有明确需求并且对查询结果做足够的安全校验否则线上很可能出现已删除数据被展示、被统计的严重事故。4.2 一级缓存、二级缓存与多表查询的坑MyBatis 的一级缓存是 SqlSession 级别的默认开启。在一次会话里执行相同的 SQL第二次会直接命中缓存不再查数据库。这个机制在同一个方法里重复查询时能提升效率但有个坑如果你在同一个 SqlSession 里先查询再更新缓存没有正确清理可能读到旧数据。Spring 管理下每个 Mapper 查询都会拿到新的 SqlSession一级缓存的作用范围其实被极大削弱了所以不必过分依赖它。二级缓存是 SqlSessionFactory 级别的跨会话共享但我在多表查询场景下几乎都是禁用的。原因在于二级缓存对关联表的变化不敏感。比如订单列表做了二级缓存缓存了关联用户昵称的查询结果此时如果用户表里昵称被修改了订单模块的二级缓存并不会感知到用户看到的是旧昵称这就是经典的多表查询脏缓存问题。所以我的建议是MyBatis 默认不开二级缓存尤其是有 JOIN 的多表查询宁可数据库多扛一点查询也不要引入看似聪明实则坑爹的缓存。如果你的项目确实有高频列表查询的缓存需求优先考虑 Redis自己控制缓存失效时机比如更新关联表时主动删除相关缓存 key。4.3 查询慢的排查从 SQL 打印到 Redis 缓存分页查询变慢第一步永远是看执行 SQL 和索引不要急着上缓存。MyBatis 可以在配置文件里打开日志打印mybatis: configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl这样控制台会打印完整 SQL 语句但输出的占位符是?参数是单独绑定的看起来不方便。想看到直接可执行的 SQL可以装 MyBatis Log Plugin 这个 IDEA 插件它会把 SQL 和参数拼成完整的可执行语句排查问题效率高很多。也可以引入 p6spy 在日志中直接输出最终 SQL这个方案更通用不依赖 IDE。拿到 SQL 之后放到数据库里执行一遍EXPLAIN重点看三个指标type是否为 ALL、rows扫描了多少行、possible_keys和key是否走对了索引。深分页优化我前面已经讲了这里补充一个排查思路如果rows特别大先确认 where 条件里的列是否有索引如果排序字段没有索引MySQL 会产生 filesort一旦排序数据量超过内存阈值会落盘导致严重性能下降。Redis 优化分页慢我的实践是区分场景。像订单列表这种需要实时数据的直接用游标分页不存 Redis。像用户排行榜、热门商品列表这种写少读多、实时性要求不高的场景才用 Redis 缓存每页的 id 列表key 设计成module:page:{pageNum}value 存 id 列表TTL 设置 30 到 60 秒查询时先取 id 列表再批量查询数据库。4.4 高频疑问速查表把平时群里和工位上被问得比较多的问题整理成一张表方便大家快速查问题结论与建议打印可执行 SQL 的插件IDEA 的 MyBatis Log Plugin或引入 p6spy 依赖if test里判断字符串包含用 OGNL 表达式testname ! null and name.indexOf(a) ! -1查询结果增加行号MySQL 用SELECT (rownum : rownum 1) AS rownum记得初始化(SELECT rownum : 0) rSpring 报 write operations are not allowed in read-only mode这个报错不是 MyBatis 抛的而是 Spring 事务设置了readOnly true底层连接拒绝写操作。排查Transactional(readOnly true)是否误加在写方法上MyBatis-Flex 里两个 or 怎么嵌套核心是控制括号优先级不同 ORM 的 QueryWrapper 都有and(...)、or(...)嵌套方法MyBatis-Flex 同理具体方法名以官方文档为准多表查询开的二级缓存脏数据怎么破最稳妥的方案是关掉二级缓存用 Redis 自行管理动态 SQL 有哪些标签if、where、foreach、choose、when、otherwise、trim、set掌握这八个基本够用MyBatis 工作原理核心链路SqlSessionFactoryBuilder 构建 SqlSessionFactorySqlSession 调用 ExecutorExecutor 通过 StatementHandler 执行 JDBCResultSetHandler 处理结果集这些内容单独拿出来每个都能写一篇长文但如果你能把我上面讲到的多表映射、分页原理、深分页优化和缓存陷阱都吃透应付日常开发和一个普通的 MyBatis 面试环节已经绰绰有余。最后再多说一句自己的体会。MyBatis 这个东西表面看就是写写 XML但真正决定项目上限的往往是这些平时不起眼的细节resultMap 去重、分页参数清理、嵌套查询的 SQL 条数、缓存一致性的控制。我在实际项目中踩过太多次类似的坑比如订单列表因为 JOIN 明细表导致分页错乱比如startPage被无关查询消费导致线上接口静默返回全量数据。写这篇博客也算是对自己经验的一次系统性整理如果你在落地过程中遇到了其他奇奇怪怪的问题欢迎带着具体的 SQL 和 resultMap 来交流这类问题光靠描述往往说不清楚看到实际代码才能定位到根因。