目录一、业务背景真实业务场景两种实现对比二、数据库表结构简化版三、分层架构说明四、完整代码实战1. 底层 DO 实体对应单表OrderDOOrderItemDOUserDO、GoodsDO、LogisticsDO 省略字段和表一一对应2. 聚合 VO前端最终返回对象复杂组装结果3. Mapper 层全部单表简单查询无任何 JOINOrderMapperOrderItemMapperUserMapperGoodsMapper、LogisticsMapper 同理只提供批量 ID 查询接口4. Service 核心代码层面组装数据核心实战逻辑五、Controller 调用层六、核心技术要点生产环境必看1. 为什么拆分 SQL 比多表 JOIN 更好2. 避坑关键点防止 N1 查询灾难3. 内存组装三大工具4. 性能优化补充方案七、适用 不适用场景适合拆分 SQL 代码组装不适合直接写简单 JOIN 即可八、对比巨型 JOIN SQL 反面示例不推荐九、扩展进阶MyBatis-Plus 简化批量查询一、业务背景真实业务场景需求电商订单详情页需要一次性返回订单主表t_order订单基础信息订单商品明细表t_order_item多件商品用户表t_user下单人昵称、手机号商品表t_goods商品名称、图片、单价物流表t_order_logistics快递单号、收货地址两种实现对比传统写法致命问题写一张超级大LEFT JOIN多表 SQL几十行关联分页慢、索引失效、维护困难、联表锁表、扩展字段就要改 SQL推荐方案单表简单 SQL 分开查询Java 内存组装 VO解耦、易优化、分库分表友好、可加缓存。二、数据库表结构简化版sql-- 订单主表 CREATE TABLE t_order ( order_id BIGINT PRIMARY KEY, user_id BIGINT, order_no VARCHAR(32), total_amount DECIMAL(10,2), create_time DATETIME ); -- 订单商品明细一对多1订单N商品 CREATE TABLE t_order_item ( id BIGINT PRIMARY KEY, order_id BIGINT, goods_id BIGINT, buy_num INT, item_amount DECIMAL(10,2) ); -- 用户表 CREATE TABLE t_user ( user_id BIGINT PRIMARY KEY, nick_name VARCHAR(50), phone VARCHAR(11) ); -- 商品表 CREATE TABLE t_goods ( goods_id BIGINT PRIMARY KEY, goods_name VARCHAR(100), cover_img VARCHAR(255), price DECIMAL(10,2) ); -- 物流表一对一1订单1物流 CREATE TABLE t_order_logistics ( logistics_id BIGINT PRIMARY KEY, order_id BIGINT, express_no VARCHAR(32), receive_addr VARCHAR(200) );三、分层架构说明Controller → Service → Mapper VO前端聚合实体 ← 代码组装 ← 多个单表DO核心思想只做单表简单查询所有关联、分组、匹配逻辑交给 Java 代码。四、完整代码实战1. 底层 DO 实体对应单表OrderDOData public class OrderDO { private Long orderId; private Long userId; private String orderNo; private BigDecimal totalAmount; private LocalDateTime createTime; }OrderItemDOData public class OrderItemDO { private Long id; private Long orderId; private Long goodsId; private Integer buyNum; private BigDecimal itemAmount; }UserDO、GoodsDO、LogisticsDO 省略字段和表一一对应2. 聚合 VO前端最终返回对象复杂组装结果Data public class OrderDetailVO { // 订单基础 private Long orderId; private String orderNo; private BigDecimal totalAmount; private LocalDateTime createTime; // 用户信息 一对一 private String userNick; private String userPhone; // 物流信息 一对一 private String expressNo; private String receiveAddr; // 商品明细列表 一对多 private ListOrderItemVO itemList; Data public static class OrderItemVO { private Long goodsId; private String goodsName; private String coverImg; private BigDecimal price; private Integer buyNum; private BigDecimal itemAmount; } }3. Mapper 层全部单表简单查询无任何 JOINOrderMapperMapper public interface OrderMapper { // 根据订单ID查订单 OrderDO selectById(Long orderId); // 批量查订单批量场景用 ListOrderDO selectBatchIds(Param(orderIds) ListLong orderIds); }OrderItemMapperMapper public interface OrderItemMapper { // 根据订单ID集合批量查明细 ListOrderItemDO selectByOrderIds(Param(orderIds) ListLong orderIds); }UserMapperMapper public interface UserMapper { ListUserDO selectBatchIds(Param(userIds) ListLong userIds); }GoodsMapper、LogisticsMapper 同理只提供批量 ID 查询接口关键点全部使用 IN 批量查询杜绝循环单条查数据库N1 问题4. Service 核心代码层面组装数据核心实战逻辑Service public class OrderServiceImpl implements OrderService { Autowired private OrderMapper orderMapper; Autowired private OrderItemMapper itemMapper; Autowired private UserMapper userMapper; Autowired private GoodsMapper goodsMapper; Autowired private LogisticsMapper logisticsMapper; /** * 查询单个订单详情拆分多SQL内存组装 */ Override public OrderDetailVO getOrderDetail(Long orderId) { // 步骤1单表查询各层基础数据 // 1. 查询订单主数据 OrderDO orderDO orderMapper.selectById(orderId); if (orderDO null) { return null; } Long userId orderDO.getUserId(); // 2. 查询当前订单所有商品明细 ListOrderItemDO itemDOList itemMapper.selectByOrderIds(List.of(orderId)); // 3. 收集明细里所有商品ID批量查商品信息 ListLong goodsIdList itemDOList.stream() .map(OrderItemDO::getGoodsId) .distinct() .collect(Collectors.toList()); ListGoodsDO goodsDOList goodsMapper.selectBatchIds(goodsIdList); // 4. 批量查询用户、物流 UserDO userDO userMapper.selectBatchIds(List.of(userId)).get(0); ListOrderLogisticsDO logisticsList logisticsMapper.selectByOrderIds(List.of(orderId)); OrderLogisticsDO logisticsDO logisticsList.isEmpty() ? null : logisticsList.get(0); // 步骤2把DB数据转Map方便内存快速匹配核心优化 MapLong, GoodsDO goodsMap goodsDOList.stream() .collect(Collectors.toMap(GoodsDO::getGoodsId, g - g)); // 步骤3逐层组装VO OrderDetailVO vo new OrderDetailVO(); // 填充订单基础 vo.setOrderId(orderDO.getOrderId()); vo.setOrderNo(orderDO.getOrderNo()); vo.setTotalAmount(orderDO.getTotalAmount()); vo.setCreateTime(orderDO.getCreateTime()); // 填充用户信息 vo.setUserNick(userDO.getNickName()); vo.setUserPhone(userDO.getPhone()); // 填充物流 if (logisticsDO ! null) { vo.setExpressNo(logisticsDO.getExpressNo()); vo.setReceiveAddr(logisticsDO.getReceiveAddr()); } // 组装一对多商品明细核心循环明细从goodsMap匹配商品数据 ListOrderDetailVO.OrderItemVO itemVOList new ArrayList(); for (OrderItemDO itemDO : itemDOList) { OrderDetailVO.OrderItemVO itemVO new OrderDetailVO.OrderItemVO(); itemVO.setGoodsId(itemDO.getGoodsId()); itemVO.setBuyNum(itemDO.getBuyNum()); itemVO.setItemAmount(itemDO.getItemAmount()); // 从内存Map匹配商品信息无需查库 GoodsDO goods goodsMap.get(itemDO.getGoodsId()); if (goods ! null) { itemVO.setGoodsName(goods.getGoodsName()); itemVO.setCoverImg(goods.getCoverImg()); itemVO.setPrice(goods.getPrice()); } itemVOList.add(itemVO); } vo.setItemList(itemVOList); return vo; } /** * 扩展批量查询多个订单更能体现拆分SQL优势避免多表联查分页卡死 */ Override public ListOrderDetailVO listOrderBatch(ListLong orderIdList) { // 1. 批量查所有订单 ListOrderDO orderDOList orderMapper.selectBatchIds(orderIdList); if (CollUtil.isEmpty(orderDOList)) { return Collections.emptyList(); } // 2. 批量查所有订单明细、物流 ListOrderItemDO allItemList itemMapper.selectByOrderIds(orderIdList); ListOrderLogisticsDO allLogisticsList logisticsMapper.selectByOrderIds(orderIdList); // 3. 收集所有用户ID、商品ID一次性批量查询 ListLong userIdList orderDOList.stream() .map(OrderDO::getUserId) .distinct() .collect(Collectors.toList()); ListUserDO userDOList userMapper.selectBatchIds(userIdList); ListLong goodsIdList allItemList.stream() .map(OrderItemDO::getGoodsId) .distinct() .collect(Collectors.toList()); ListGoodsDO goodsDOList goodsMapper.selectBatchIds(goodsIdList); // 内存分组、转MapO(1)匹配 // 用户Map key:userId MapLong, UserDO userMap userDOList.stream() .collect(Collectors.toMap(UserDO::getUserId, u - u)); // 商品Map key:goodsId MapLong, GoodsDO goodsMap goodsDOList.stream() .collect(Collectors.toMap(GoodsDO::getGoodsId, g - g)); // 明细按orderId分组一对多核心分组 MapLong, ListOrderItemDO itemGroupByOrderId allItemList.stream() .collect(Collectors.groupingBy(OrderItemDO::getOrderId)); // 物流按orderId分组一对一 MapLong, OrderLogisticsDO logisticsMap allLogisticsList.stream() .collect(Collectors.toMap(OrderLogisticsDO::getOrderId, l - l)); // 循环组装每一个订单VO ListOrderDetailVO result new ArrayList(); for (OrderDO order : orderDOList) { OrderDetailVO vo new OrderDetailVO(); vo.setOrderId(order.getOrderId()); vo.setOrderNo(order.getOrderNo()); vo.setTotalAmount(order.getTotalAmount()); vo.setCreateTime(order.getCreateTime()); // 用户 UserDO user userMap.get(order.getUserId()); if (user ! null) { vo.setUserNick(user.getNickName()); vo.setUserPhone(user.getPhone()); } // 物流 OrderLogisticsDO logistics logisticsMap.get(order.getOrderId()); if (logistics ! null) { vo.setExpressNo(logistics.getExpressNo()); vo.setReceiveAddr(logistics.getReceiveAddr()); } // 商品明细列表 ListOrderItemDO itemDOs itemGroupByOrderId.getOrDefault(order.getOrderId(), Collections.emptyList()); ListOrderDetailVO.OrderItemVO itemVOs new ArrayList(); for (OrderItemDO item : itemDOs) { OrderDetailVO.OrderItemVO itemVO new OrderDetailVO.OrderItemVO(); itemVO.setGoodsId(item.getGoodsId()); itemVO.setBuyNum(item.getBuyNum()); itemVO.setItemAmount(item.getItemAmount()); GoodsDO goods goodsMap.get(item.getGoodsId()); if (goods ! null) { itemVO.setGoodsName(goods.getGoodsName()); itemVO.setCoverImg(goods.getCoverImg()); itemVO.setPrice(goods.getPrice()); } itemVOs.add(itemVO); } vo.setItemList(itemVOs); result.add(vo); } return result; } }五、Controller 调用层RestController RequestMapping(/order) public class OrderController { Autowired private OrderService orderService; GetMapping(/detail/{orderId}) public ResultOrderDetailVO detail(PathVariable Long orderId) { OrderDetailVO vo orderService.getOrderDetail(orderId); return Result.success(vo); } GetMapping(/batch) public ResultListOrderDetailVO batch(RequestParam ListLong orderIds) { ListOrderDetailVO list orderService.listOrderBatch(orderIds); return Result.success(list); } }六、核心技术要点生产环境必看1. 为什么拆分 SQL 比多表 JOIN 更好性能可控多表 LEFT JOIN数据量大时笛卡尔积、索引失效、分页limit扫描全表分单表查询每张表 SQL 简单索引高效可单独给单表加缓存Redis 缓存商品、用户。扩展性强新增表关联优惠券、发票只需要新增一次批量查询不用重构巨型 SQL适配分库分表订单、商品可能分不同库跨库不能 JOIN只能代码组装。维护简单单表 SQL 短小MyBatis 容易维护复杂 JOIN 几十行改字段极易漏关联条件。灵活加工数据内存中可以自由排序、过滤、计算、拼接SQL 很难实现复杂业务计算。2. 避坑关键点防止 N1 查询灾难❌ 错误写法循环订单 ID每一条订单单独查明细、商品、用户循环查库百万数据直接打垮 DB ✅ 标准规范全部使用批量 IN 查询一次性取出全量数据内存 Map 匹配3. 内存组装三大工具Collectors.toMap一对一快速匹配用户、物流、商品Collectors.groupingBy一对多分组订单明细按订单 ID 分组Stream 流式处理数据清洗、去重、提取 ID 集合4. 性能优化补充方案热点数据缓存商品、用户信息存入 Redis不用每次查库分页分层先分页查订单主表再批量查当前页关联数据避免全表加载异步冗余报表类大数据可通过 ES / 数据中台预聚合不在线上业务做大量内存组装空值保护所有 Map.get () 判空防止空指针。七、适用 不适用场景适合拆分 SQL 代码组装多表一对多、一对一聚合查询订单、商品、用户、物流分库分表、跨库查询禁止跨库 JOIN前端复杂 VO 聚合页面、列表分页表字段频繁迭代、业务逻辑经常变更不适合直接写简单 JOIN 即可仅 2 张表简单关联数据量很小简单报表、不需要复杂内存计算超高并发极简查询为减少 IO少量 JOIN 开销可接受八、对比巨型 JOIN SQL 反面示例不推荐sql-- 禁止这种写法5表LEFT JOIN数据量大性能爆炸 SELECT o.*,u.nick_name,u.phone,l.express_no,l.receive_addr, i.goods_id,i.buy_num,i.item_amount,g.goods_name,g.cover_img,g.price FROM t_order o LEFT JOIN t_user u ON o.user_id u.user_id LEFT JOIN t_order_logistics l ON o.order_id l.order_id LEFT JOIN t_order_item i ON o.order_id i.order_id LEFT JOIN t_goods g ON i.goods_id g.goods_id WHERE o.order_id #{orderId}问题关联越多优化器越难走索引、无法单独缓存商品表、分页慢、新增一张表就要改整条 SQL。九、扩展进阶MyBatis-Plus 简化批量查询项目使用 MP 时Mapper 不用手写 XML直接调用内置批量方法// 代替手写XML selectBatchIds ListOrderDO list orderMapper.selectBatchIds(orderIdList);