解决n+1查询数据库问题

1. 问题描述

在写项目中，可能会碰到一个问题：通过查询表A得到一个list结果，再对list中的n个元素各查询一次关联的表B。形成对数据库执行n+1次查询。这种代码会无形增加数据库的处理负担，影响整体性能。

举例：给定categoryId，查询dish和dish_flavor相关信息（dish表和dish_flavor表存在冗余字段dishId）。表的实体类结构如下：

• dish：

  @Data
  @AllArgsConstructor
  @NoArgsConstructor
  @TableName("dish")
  public class Dish {@TableId(type = IdType.AUTO)private Long id;private String name;private Integer categoryId;
  }
  

• dish_flavor：

  @Data
  @AllArgsConstructor
  @NoArgsConstructor
  @TableName("dish_flavor")
  public class DishFlavor {@TableId(type = IdType.AUTO)private Long id;private Long dishId;private String name;private String value;
  }
  

2. 解决方法

1. 批量查询
   先根据categoryId查询到所有的dish信息，然后提取所有dish的id，再根据dish_id批量查询得到dish_flavor信息，最后组合返回。

   • vo:

     @Data
     @AllArgsConstructor
     @NoArgsConstructor
     @Builder
     public class DishFlavorVO {private Long id;private String name;private Integer categoryId;private List<DishFlavor> flavors = new ArrayList<>();
     }
     

   • ServiceImpl:

     @Service
     @Slf4j
     public class DishServiceImpl implements DishService {@Autowiredprivate DishMapper dishMapper;/*** 方法 1：批量查询* @param categoryId* @return*/public List<DishFlavorVO> method1(Integer categoryId) {//1. 获取所有的dishesList<DishFlavorVO> dishFlavorVOS = dishMapper.getDishes(categoryId);//1.1 获取所有的dish_idList<Long> dishIds = dishFlavorVOS.stream().map(DishFlavorVO::getId).collect(Collectors.toList());//2.获得所有的dish_flavorList<DishFlavor> dishFlavors = dishMapper.selectFlavorsByDishIdList(dishIds);//3 聚合dish和dish_flavorMap<Long, List<DishFlavor>> flavorsMap = dishFlavors.stream().collect(Collectors.groupingBy(DishFlavor::getDishId));for (DishFlavorVO dishFlavorVO : dishFlavorVOS) {dishFlavorVO.setFlavors(flavorsMap.get(dishFlavorVO.getId()));}return dishFlavorVOS;}}
     

   • mapper:

     <?xml version="1.0" encoding="UTF-8" ?>
     <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
     <mapper namespace="com.example.solution.mapper.DishMapper"><select id="getDishes" resultType="com.example.solution.pojo.vo.DishFlavorVO">select * from dish where category_id = #{categoryId}</select><!--批量查询--><select id="selectFlavorsByDishIdList" resultType="com.example.solution.pojo.entity.DishFlavor">select * from dish_flavorwhere dish_id in<foreach collection="dishIds" separator="," open="(" close=")" item="dishId">#{dishId}</foreach></select></mapper>
     

2. join联表查询
   需要额外新建接收返回参数的类，再封装成返回给前端的vo。

   • item:

     @Data
     @AllArgsConstructor
     @NoArgsConstructor
     public class DishesAndFlavorsItem {private Long id;private String name;private Integer categoryId;private Long fDishId;private Long fId;private String fName;private String fValue;
     }
     

   • ServiceImpl:

     /*** 方法 2：联表查询* @param categoryId* @return*/
     public List<DishFlavorVO> method2(Integer categoryId){List<DishFlavorVO> dishFlavorVOS = new ArrayList<>();//1. 联表查询List<DishesAndFlavorsItem> dishes = dishMapper.getDishesAndFlavors(categoryId);//2. 组合dish和dish_flavorMap<Long, List<DishesAndFlavorsItem>> collect = dishes.stream().collect(Collectors.groupingBy(DishesAndFlavorsItem::getFDishId));for (Map.Entry<Long, List<DishesAndFlavorsItem>> entry : collect.entrySet()) {List<DishFlavor> dishFlavors = new ArrayList<>();Long dishId = entry.getKey();List<DishesAndFlavorsItem> dishesAndFlavorsItems = entry.getValue();for (DishesAndFlavorsItem dishesAndFlavorsItem : dishesAndFlavorsItems) {DishFlavor dishFlavor = new DishFlavor();dishFlavor.setId(dishesAndFlavorsItem.getFId());dishFlavor.setValue(dishesAndFlavorsItem.getFValue());dishFlavor.setName(dishesAndFlavorsItem.getFName());dishFlavor.setDishId(dishesAndFlavorsItem.getFDishId());dishFlavors.add(dishFlavor);}DishFlavorVO dishFlavorVO = new DishFlavorVO();dishFlavorVO.setFlavors(dishFlavors);dishFlavorVO.setId(dishId);}return dishFlavorVOS;
     }
     

   • mapper:

     <select id="getDishesAndFlavors" resultType="com.example.solution.pojo.item.DishesAndFlavorsItem">SELECTd.id as id,d.category_id as categoryId,df.dish_id as f_dishId,df.id as f_id,df.`name` as f_name,df.`value` as f_valueFROMdish as dLEFT JOINdish_flavor as df ON d.id = df.dish_idWHEREd.category_id = #{categoryId}
     </select>
     

3. 总结

可以通过批量查询或join联表查询来获取。使用批量查询需要分两次sql来查；使用join需要额外构建类来接收结果。如果效率需进一步提升，可使用redis缓存。

以上为个人学习分享，如有问题，欢迎指出：）