做后端开发日常CRUD开发基本离不开MyBatis、MyBatis-Plus。很多小伙伴开发速度很快接口功能跑通就直接提测上线但上线后经常遇到一个问题接口数据量不大响应却越来越慢数据库CPU偶尔莫名飙升。我之前排查过好几次线上慢接口最终定位原因基本都是同一个——MyBatis N1 查询问题。这是新手最容易踩、也最容易被忽略的性能陷阱。平时测试数据量小感知不到一旦线上数据量上来查询次数成倍暴增直接拖垮整个服务性能。今天我结合真实项目踩坑经历通俗讲清楚什么是N1查询、为什么会产生、如何彻底解决附带完整错误代码、优化实战代码帮大家彻底根治这个MyBatis性能问题。一、什么是 MyBatis N1 查询问题简单直白解释我们在做一对多、一对一关联查询时先执行1条SQL查询出主列表数据随后遍历每一条主数据再单独执行一次关联查询最终形成1条主查询 N条子查询也就是N1查询。举个业务场景查询用户列表同时关联查询每个用户的订单信息。如果查出100个用户就会额外多执行100次订单查询数据库总共执行101次SQL。数据量越大查询次数越多性能浪费极其严重。很多人以为是数据库慢、服务器卡实则是自己代码写法不规范凭空制造了大量无效SQL请求。二、线上错误实战代码N1 经典坑下面这段代码是我之前项目里的原始写法也是网上90%新手的通用错误写法开启懒加载、循环嵌套查询完美触发N1性能陷阱。实体类关联配置/** * 用户实体 - 错误示范开启懒加载极易触发N1 */ public class User { private Long id; private String username; private String phone; // 一对多关联订单默认懒加载 private ListOrder orderList; // get、set省略 }Mapper查询先查所有用户循环遍历查订单// 错误业务代码典型N1查询 Service public class UserServiceImpl implements UserService { Autowired private UserMapper userMapper; Override public ListUser getUserWithOrder() { // 第1次SQL查询全部用户 ListUser userList userMapper.selectList(null); // 遍历用户逐个查询订单触发N次SQL for (User user : userList) { ListOrder orderList user.getOrderList(); } return userList; } }只要执行这段代码控制台会打印大量重复SQL。用户数量越多SQL执行次数越多数据库压力直接拉满。三、为什么会出现 N1 问题核心原因就是MyBatis的懒加载机制和关联嵌套查询。MyBatis默认关联属性懒加载只有使用到关联数据时才会发起SQL查询。我们循环遍历集合读取关联数据就会循环触发查询最终产生海量SQL。很多同学为了方便直接用MP自带的list查询再循环取值关联数据看似代码简洁实则埋了巨大性能隐患。四、最优解决方案联表查询 一次性加载想要彻底消灭N1问题核心思路就一个拒绝循环查询一次性加载所有关联数据。生产环境最稳妥的两种方案MyBatis联表关联映射、分批IN查询。这里给大家分享我线上稳定落地的XML关联查询优化方案彻底杜绝多次查询。!-- 优化后单SQL联表查询一次性封装用户订单数据 -- select idgetUserAndOrderList resultMapUserOrderMap SELECT u.id, u.username, u.phone, o.id order_id, o.order_no, o.amount FROM user u LEFT JOIN order o ON u.id o.user_id /select resultMap idUserOrderMap typecom.entity.User id propertyid columnid/ result propertyusername columnusername/ result propertyphone columnphone/ collection propertyorderList ofTypecom.entity.Order id propertyid columnorder_id/ result propertyorderNo columnorder_no/ result propertyamount columnamount/gt; lt;/collectiongt; lt;/resultMapgt;优化后的业务层代码只执行一次SQL就能查询出所有用户和对应的订单数据不存在循环查询彻底消灭N1隐患。Override public ListUser getUserWithOrderOpt() { // 仅执行一次联表SQL数据全部封装完成 return userMapper.getUserAndOrderList(); }五、补充优化关闭全局懒加载新手必备如果团队新手较多很难保证每段代码都规范建议直接在yml全局关闭懒加载从源头杜绝N1问题mybatis: configuration: lazy-loading-enabled: false关闭之后关联数据会一次性加载不会出现后续循环查询的情况适合中小型项目统一规范。六、日常开发避坑总结踩过这次坑之后我给自己团队定了三个开发规范基本再也没出现过N1性能问题。第一禁止循环中执行Mapper查询、禁止遍历集合读取懒加载关联数据第二一对多、一对一关联场景优先使用联表查询、一次性数据封装第三代码自测开启SQL打印观察控制台SQL数量数量异常立即优化。总结MyBatis的N1查询属于典型的开发层面低级性能漏洞不报错、不崩溃但会持续拖垮服务性能数据量越大危害越明显。很多线上接口卡顿、数据库压力大根源都是这个小问题。建议大家写完代码多观察SQL执行次数养成优化习惯提前规避线上性能事故让项目更稳定、更专业。Wap.flGwc.cN/Article/95139.shtmlWap.flGwc.cN/Article/40854.shtmlWap.flGwc.cN/Article/02048.shtmlWap.flGwc.cN/Article/93959.shtmlWap.flGwc.cN/Article/55393.shtmlWap.flGwc.cN/Article/37419.shtmlWap.flGwc.cN/Article/67404.shtmlWap.flGwc.cN/Article/30617.shtmlWap.flGwc.cN/Article/88088.shtmlWap.flGwc.cN/Article/65778.shtmlWap.flGwc.cN/Article/87653.shtmlWap.flGwc.cN/Article/55309.shtmlWap.flGwc.cN/Article/32168.shtmlWap.flGwc.cN/Article/38581.shtmlWap.flGwc.cN/Article/80627.shtmlWap.flGwc.cN/Article/25344.shtmlWap.flGwc.cN/Article/00959.shtmlWap.flGwc.cN/Article/59857.shtmlWap.flGwc.cN/Article/99016.shtmlWap.flGwc.cN/Article/23135.shtmlWap.flGwc.cN/Article/52500.shtmlWap.flGwc.cN/Article/82564.shtmlWap.flGwc.cN/Article/18106.shtmlWap.flGwc.cN/Article/22111.shtmlWap.flGwc.cN/Article/96380.shtmlWap.flGwc.cN/Article/88351.shtmlWap.flGwc.cN/Article/73229.shtmlWap.flGwc.cN/Article/05224.shtmlWap.flGwc.cN/Article/27177.shtmlWap.flGwc.cN/Article/99506.shtmlWap.flGwc.cN/Article/63368.shtmlWap.flGwc.cN/Article/07290.shtmlWap.flGwc.cN/Article/96405.shtmlWap.flGwc.cN/Article/33089.shtmlWap.flGwc.cN/Article/94556.shtmlWap.flGwc.cN/Article/51391.shtmlWap.flGwc.cN/Article/81322.shtmlWap.flGwc.cN/Article/21769.shtmlWap.flGwc.cN/Article/13302.shtmlWap.flGwc.cN/Article/72241.shtmlWap.flGwc.cN/Article/64876.shtmlWap.flGwc.cN/Article/94379.shtmlWap.flGwc.cN/Article/36874.shtmlWap.flGwc.cN/Article/24064.shtmlWap.flGwc.cN/Article/97611.shtmlWap.flGwc.cN/Article/59661.shtmlWap.flGwc.cN/Article/72460.shtmlWap.flGwc.cN/Article/72105.shtmlWap.flGwc.cN/Article/12133.shtmlWap.flGwc.cN/Article/68146.shtmlWap.flGwc.cN/Article/94391.shtmlWap.flGwc.cN/Article/25515.shtmlWap.flGwc.cN/Article/37000.shtmlWap.flGwc.cN/Article/13065.shtmlWap.flGwc.cN/Article/26819.shtmlWap.flGwc.cN/Article/22907.shtmlWap.flGwc.cN/Article/65479.shtmlWap.flGwc.cN/Article/90243.shtmlWap.flGwc.cN/Article/06218.shtmlWap.flGwc.cN/Article/12996.shtmlWap.flGwc.cN/Article/50387.shtmlWap.flGwc.cN/Article/72192.shtmlWap.flGwc.cN/Article/12865.shtmlWap.flGwc.cN/Article/55181.shtmlWap.flGwc.cN/Article/15925.shtmlWap.flGwc.cN/Article/89485.shtmlWap.flGwc.cN/Article/87295.shtmlWap.flGwc.cN/Article/22428.shtmlWap.flGwc.cN/Article/51520.shtmlWap.flGwc.cN/Article/51024.shtmlWap.flGwc.cN/Article/22373.shtmlWap.flGwc.cN/Article/73228.shtmlWap.flGwc.cN/Article/30345.shtmlWap.flGwc.cN/Article/62674.shtmlWap.flGwc.cN/Article/27201.shtmlWap.flGwc.cN/Article/67871.shtmlWap.flGwc.cN/Article/22385.shtmlWap.flGwc.cN/Article/38866.shtmlWap.flGwc.cN/Article/82167.shtmlWap.flGwc.cN/Article/88735.shtmlWap.flGwc.cN/Article/44286.shtmlWap.flGwc.cN/Article/16871.shtmlWap.flGwc.cN/Article/28220.shtmlWap.flGwc.cN/Article/67460.shtmlWap.flGwc.cN/Article/15518.shtmlWap.flGwc.cN/Article/30430.shtmlWap.flGwc.cN/Article/10769.shtmlWap.flGwc.cN/Article/55915.shtmlWap.flGwc.cN/Article/37056.shtmlWap.flGwc.cN/Article/38955.shtmlWap.flGwc.cN/Article/30645.shtmlWap.flGwc.cN/Article/07978.shtmlWap.flGwc.cN/Article/55011.shtmlWap.flGwc.cN/Article/93688.shtmlWap.flGwc.cN/Article/51850.shtmlWap.flGwc.cN/Article/97463.shtmlWap.flGwc.cN/Article/32692.shtmlWap.flGwc.cN/Article/12044.shtmlWap.flGwc.cN/Article/55711.shtmlWap.flGwc.cN/Article/32823.shtmlWap.flGwc.cN/Article/67119.shtmlWap.flGwc.cN/Article/00188.shtmlWap.flGwc.cN/Article/64365.shtmlWap.flGwc.cN/Article/85741.shtmlWap.flGwc.cN/Article/05343.shtmlWap.flGwc.cN/Article/53592.shtmlWap.flGwc.cN/Article/08404.shtmlWap.flGwc.cN/Article/20981.shtmlWap.flGwc.cN/Article/25097.shtmlWap.flGwc.cN/Article/71144.shtmlWap.flGwc.cN/Article/81164.shtmlWap.flGwc.cN/Article/72087.shtmlWap.flGwc.cN/Article/48004.shtmlWap.flGwc.cN/Article/20112.shtmlWap.flGwc.cN/Article/56433.shtmlWap.flGwc.cN/Article/52534.shtmlWap.flGwc.cN/Article/15810.shtmlWap.flGwc.cN/Article/56080.shtmlWap.flGwc.cN/Article/29188.shtmlWap.flGwc.cN/Article/56518.shtmlWap.flGwc.cN/Article/03846.shtmlWap.flGwc.cN/Article/97030.shtmlWap.flGwc.cN/Article/76543.shtmlWap.flGwc.cN/Article/35298.shtmlWap.flGwc.cN/Article/59034.shtmlWap.flGwc.cN/Article/11245.shtmlWap.flGwc.cN/Article/64358.shtmlWap.flGwc.cN/Article/55277.shtmlWap.flGwc.cN/Article/17550.shtmlWap.flGwc.cN/Article/16736.shtmlWap.flGwc.cN/Article/75453.shtmlWap.flGwc.cN/Article/72800.shtmlWap.flGwc.cN/Article/26368.shtmlWap.flGwc.cN/Article/49664.shtmlWap.flGwc.cN/Article/80104.shtmlWap.flGwc.cN/Article/67189.shtmlWap.flGwc.cN/Article/76194.shtmlWap.flGwc.cN/Article/49740.shtmlWap.flGwc.cN/Article/24266.shtmlWap.flGwc.cN/Article/31897.shtmlWap.flGwc.cN/Article/81289.shtmlWap.flGwc.cN/Article/79824.shtmlWap.flGwc.cN/Article/69811.shtmlWap.flGwc.cN/Article/85129.shtmlWap.flGwc.cN/Article/85408.shtmlWap.flGwc.cN/Article/27347.shtmlWap.flGwc.cN/Article/59668.shtmlWap.flGwc.cN/Article/59512.shtmlWap.flGwc.cN/Article/68561.shtmlWap.flGwc.cN/Article/71319.shtmlWap.flGwc.cN/Article/34189.shtmlWap.flGwc.cN/Article/76192.shtmlWap.flGwc.cN/Article/12205.shtmlWap.flGwc.cN/Article/84073.shtmlWap.flGwc.cN/Article/61000.shtmlWap.flGwc.cN/Article/66829.shtmlWap.flGwc.cN/Article/87003.shtmlWap.flGwc.cN/Article/41513.shtmlWap.flGwc.cN/Article/33951.shtmlWap.flGwc.cN/Article/21911.shtmlWap.flGwc.cN/Article/70254.shtmlWap.flGwc.cN/Article/87498.shtmlWap.flGwc.cN/Article/68644.shtmlWap.flGwc.cN/Article/00846.shtmlWap.flGwc.cN/Article/84688.shtmlWap.flGwc.cN/Article/14993.shtmlWap.flGwc.cN/Article/94137.shtmlWap.flGwc.cN/Article/71051.shtmlWap.flGwc.cN/Article/54492.shtmlWap.flGwc.cN/Article/83112.shtmlWap.flGwc.cN/Article/85127.shtmlWap.flGwc.cN/Article/37677.shtmlWap.flGwc.cN/Article/18763.shtmlWap.flGwc.cN/Article/96620.shtmlWap.flGwc.cN/Article/63337.shtmlWap.flGwc.cN/Article/39415.shtmlWap.flGwc.cN/Article/22835.shtmlWap.flGwc.cN/Article/92817.shtmlWap.flGwc.cN/Article/53205.shtmlWap.flGwc.cN/Article/14961.shtmlWap.flGwc.cN/Article/88666.shtmlWap.flGwc.cN/Article/54834.shtmlWap.flGwc.cN/Article/45406.shtmlWap.flGwc.cN/Article/43067.shtmlWap.flGwc.cN/Article/97962.shtmlWap.flGwc.cN/Article/01736.shtmlWap.flGwc.cN/Article/78723.shtmlWap.flGwc.cN/Article/87525.shtmlWap.flGwc.cN/Article/59031.shtmlWap.flGwc.cN/Article/17345.shtmlWap.flGwc.cN/Article/48747.shtmlWap.flGwc.cN/Article/56193.shtmlWap.flGwc.cN/Article/94444.shtmlWap.flGwc.cN/Article/34421.shtmlWap.flGwc.cN/Article/40535.shtmlWap.flGwc.cN/Article/49785.shtmlWap.flGwc.cN/Article/13347.shtmlWap.flGwc.cN/Article/46475.shtmlWap.flGwc.cN/Article/41914.shtmlWap.flGwc.cN/Article/60816.shtmlWap.flGwc.cN/Article/11511.shtmlWap.flGwc.cN/Article/65230.shtmlWap.flGwc.cN/Article/82102.shtmlWap.flGwc.cN/Article/34808.shtmlWap.flGwc.cN/Article/52375.shtmlWap.flGwc.cN/Article/28724.shtmlWap.flGwc.cN/Article/74696.shtmlWap.flGwc.cN/Article/88757.shtmlWap.flGwc.cN/Article/86214.shtmlWap.flGwc.cN/Article/49959.shtmlWap.flGwc.cN/Article/59736.shtmlWap.flGwc.cN/Article/46074.shtmlWap.flGwc.cN/Article/41144.shtmlWap.flGwc.cN/Article/09483.shtmlWap.flGwc.cN/Article/92544.shtmlWap.flGwc.cN/Article/56095.shtmlWap.flGwc.cN/Article/23126.shtmlWap.flGwc.cN/Article/00925.shtmlWap.flGwc.cN/Article/57321.shtmlWap.flGwc.cN/Article/54886.shtmlWap.flGwc.cN/Article/85586.shtmlWap.flGwc.cN/Article/84868.shtmlWap.flGwc.cN/Article/18241.shtmlWap.flGwc.cN/Article/02278.shtmlWap.flGwc.cN/Article/62792.shtmlWap.flGwc.cN/Article/88901.shtmlWap.flGwc.cN/Article/06175.shtmlWap.flGwc.cN/Article/17478.shtmlWap.flGwc.cN/Article/89293.shtmlWap.flGwc.cN/Article/98207.shtmlWap.flGwc.cN/Article/20078.shtmlWap.flGwc.cN/Article/48899.shtmlWap.flGwc.cN/Article/89223.shtmlWap.flGwc.cN/Article/40202.shtmlWap.flGwc.cN/Article/90555.shtmlWap.flGwc.cN/Article/17047.shtmlWap.flGwc.cN/Article/83494.shtmlWap.flGwc.cN/Article/13561.shtmlWap.flGwc.cN/Article/86932.shtmlWap.flGwc.cN/Article/20033.shtmlWap.flGwc.cN/Article/28126.shtmlWap.flGwc.cN/Article/30454.shtmlWap.flGwc.cN/Article/21198.shtmlWap.flGwc.cN/Article/93038.shtmlWap.flGwc.cN/Article/56261.shtmlWap.flGwc.cN/Article/02101.shtmlWap.flGwc.cN/Article/44790.shtmlWap.flGwc.cN/Article/23495.shtmlWap.flGwc.cN/Article/36849.shtmlWap.flGwc.cN/Article/66320.shtmlWap.flGwc.cN/Article/34768.shtmlWap.flGwc.cN/Article/43049.shtmlWap.flGwc.cN/Article/90780.shtmlWap.flGwc.cN/Article/61528.shtmlWap.flGwc.cN/Article/72377.shtmlWap.flGwc.cN/Article/51704.shtmlWap.flGwc.cN/Article/26032.shtmlWap.flGwc.cN/Article/24277.shtmlWap.flGwc.cN/Article/57338.shtmlWap.flGwc.cN/Article/05232.shtmlWap.flGwc.cN/Article/03448.shtmlWap.flGwc.cN/Article/81864.shtmlWap.flGwc.cN/Article/37045.shtmlWap.flGwc.cN/Article/61798.shtmlWap.flGwc.cN/Article/93929.shtmlWap.flGwc.cN/Article/28697.shtmlWap.flGwc.cN/Article/14492.shtmlWap.flGwc.cN/Article/56329.shtmlWap.flGwc.cN/Article/44596.shtmlWap.flGwc.cN/Article/84344.shtmlWap.flGwc.cN/Article/45868.shtmlWap.flGwc.cN/Article/54120.shtmlWap.flGwc.cN/Article/18856.shtmlWap.flGwc.cN/Article/59548.shtmlWap.flGwc.cN/Article/00455.shtmlWap.flGwc.cN/Article/43401.shtmlWap.flGwc.cN/Article/41913.shtmlWap.flGwc.cN/Article/78853.shtmlWap.flGwc.cN/Article/39776.shtmlWap.flGwc.cN/Article/36896.shtmlWap.flGwc.cN/Article/15818.shtmlWap.flGwc.cN/Article/56999.shtmlWap.flGwc.cN/Article/79488.shtmlWap.flGwc.cN/Article/34076.shtmlWap.flGwc.cN/Article/04226.shtmlWap.flGwc.cN/Article/45659.shtmlWap.flGwc.cN/Article/73891.shtmlWap.flGwc.cN/Article/23305.shtmlWap.flGwc.cN/Article/99625.shtmlWap.flGwc.cN/Article/83296.shtmlWap.flGwc.cN/Article/97630.shtmlWap.flGwc.cN/Article/55177.shtmlWap.flGwc.cN/Article/32236.shtmlWap.flGwc.cN/Article/58987.shtmlWap.flGwc.cN/Article/69869.shtmlWap.flGwc.cN/Article/36487.shtmlWap.flGwc.cN/Article/68628.shtmlWap.flGwc.cN/Article/66842.shtmlWap.flGwc.cN/Article/94163.shtmlWap.flGwc.cN/Article/32126.shtmlWap.flGwc.cN/Article/96591.shtmlWap.flGwc.cN/Article/68448.shtmlWap.flGwc.cN/Article/56514.shtmlWap.flGwc.cN/Article/00512.shtmlWap.flGwc.cN/Article/51428.shtmlWap.flGwc.cN/Article/30586.shtmlWap.flGwc.cN/Article/62626.shtmlWap.flGwc.cN/Article/33410.shtmlWap.flGwc.cN/Article/02518.shtmlWap.flGwc.cN/Article/02652.shtmlWap.flGwc.cN/Article/68414.shtmlWap.flGwc.cN/Article/35641.shtmlWap.flGwc.cN/Article/86621.shtmlWap.flGwc.cN/Article/26474.shtmlWap.flGwc.cN/Article/87964.shtmlWap.flGwc.cN/Article/20741.shtmlWap.flGwc.cN/Article/04696.shtmlWap.flGwc.cN/Article/72606.shtmlWap.flGwc.cN/Article/52950.shtmlWap.flGwc.cN/Article/18322.shtmlWap.flGwc.cN/Article/76654.shtmlWap.flGwc.cN/Article/54212.shtmlWap.flGwc.cN/Article/81636.shtmlWap.flGwc.cN/Article/52904.shtmlWap.flGwc.cN/Article/00360.shtmlWap.flGwc.cN/Article/90210.shtmlWap.flGwc.cN/Article/98288.shtmlWap.flGwc.cN/Article/54229.shtmlWap.flGwc.cN/Article/17132.shtmlWap.flGwc.cN/Article/07154.shtmlWap.flGwc.cN/Article/39501.shtmlWap.flGwc.cN/Article/49004.shtmlWap.flGwc.cN/Article/84624.shtmlWap.flGwc.cN/Article/47422.shtmlWap.flGwc.cN/Article/65236.shtmlWap.flGwc.cN/Article/95369.shtmlWap.flGwc.cN/Article/13068.shtmlWap.flGwc.cN/Article/28137.shtmlWap.flGwc.cN/Article/03574.shtmlWap.flGwc.cN/Article/62020.shtmlWap.flGwc.cN/Article/54501.shtmlWap.flGwc.cN/Article/38412.shtmlWap.flGwc.cN/Article/16125.shtmlWap.flGwc.cN/Article/18438.shtmlWap.flGwc.cN/Article/51522.shtmlWap.flGwc.cN/Article/67274.shtmlWap.flGwc.cN/Article/27558.shtmlWap.flGwc.cN/Article/66904.shtmlWap.flGwc.cN/Article/45964.shtmlWap.flGwc.cN/Article/29929.shtmlWap.flGwc.cN/Article/80808.shtmlWap.flGwc.cN/Article/48091.shtmlWap.flGwc.cN/Article/27769.shtmlWap.flGwc.cN/Article/93960.shtmlWap.flGwc.cN/Article/58693.shtmlWap.flGwc.cN/Article/17201.shtmlWap.flGwc.cN/Article/47976.shtmlWap.flGwc.cN/Article/92590.shtmlWap.flGwc.cN/Article/76131.shtmlWap.flGwc.cN/Article/39706.shtmlWap.flGwc.cN/Article/16982.shtmlWap.flGwc.cN/Article/85443.shtmlWap.flGwc.cN/Article/17306.shtml