N+1 없애보기

1. 쿼리 최적화

1) 마이페이지 조회

- 기존 코드 - 서비스 메서드들을 호출할 때 User를 한 번 더 호출해줘서 생기는 문제

// UserPageController
@GetMapping("/my-page")
public String myPage(Model model, @AuthenticationPrincipal UserDetailsImpl userDetails) {
    Long userId = userDetails.getId();
    String userName = userDetails.getUser().getUserName();
    String userEmail = userDetails.getUser().getUserEmail();
    String streamKey = userDetails.getUser().getStreamKey();
    String userPhone = userDetails.getUser().getUserPhone();
    String userAddress = userDetails.getUser().getUserAddress();
    String postcode = userDetails.getUser().getPostcode();
    List<Broadcast> broadcastList = userService.getBroadcasts(userId);
    List<Orders> orderList = userService.getOrders(userId);

    model.addAttribute("userName", userName);
    model.addAttribute("userEmail", userEmail);
    model.addAttribute("streamKey", streamKey);
    model.addAttribute("userPhone", userPhone);
    model.addAttribute("userAddress", userAddress);
    model.addAttribute("postcode", postcode);
    model.addAttribute("broadcastList", broadcastList);
    model.addAttribute("orderList", orderList);
    return "myPage";
}

// userService
public List<Broadcast> getBroadcasts(Long userId) {
    User user = findUser(userId);
    return new ArrayList<>(user.getBroadcastList());
}

public List<Orders> getOrders(Long userId) {
    User user = findUser(userId);
    return new ArrayList<>(user.getOrderList());
}

🤔 그럼 서비스 메서드가 두 개니까 유저 조회 쿼리가 총 세 개가 나왔어야 됐던 거 아닌가? (현재 2개)

→ 데이터베이스 세션에서 1차로 캐싱해줘서 한 번만 실행됨

- 변경 코드 - Repository에서 직접 찾아서 반환해주도록 변경

// UserPageController
@GetMapping("/my-page")
public String myPage(Model model, @AuthenticationPrincipal UserDetailsImpl userDetails) {
    Long userId = userDetails.getId();
    String userName = userDetails.getUser().getUserName();
    String userEmail = userDetails.getUser().getUserEmail();
    String streamKey = userDetails.getUser().getStreamKey();
    String userPhone = userDetails.getUser().getUserPhone();
    String userAddress = userDetails.getUser().getUserAddress();
    String postcode = userDetails.getUser().getPostcode();
    List<Broadcast> broadcastList = broadcastService.findBroadcastListByUserId(userId);
    List<Orders> orderList = orderService.findOrderListByUserId(userId);

    model.addAttribute("userName", userName);
    model.addAttribute("userEmail", userEmail);
    model.addAttribute("streamKey", streamKey);
    model.addAttribute("userPhone", userPhone);
    model.addAttribute("userAddress", userAddress);
    model.addAttribute("postcode", postcode);
    model.addAttribute("broadcastList", broadcastList);
    model.addAttribute("orderList", orderList);
    return "myPage";
}

// BroadcastService
public List<Broadcast> findBroadcastListByUserId(Long userId) {
    return broadcastRepository.findAllByUserUserId(userId);
}

// OrderService
public List<Orders> findOrderListByUserId(Long userId) {
    return orderRepository.findAllByUserUserId(userId);
}

- N + 1 문제 해결

방송/주문 안에 있는 상품 조회 쿼리까지 함께 발생하는 N+1 문제가 생김

→ 방송/주문과 상품을 Join Fetch 하여 한 쿼리로 묶어줌

// BroadcastRepository
@Query("SELECT b FROM Broadcast b LEFT JOIN FETCH b.product p WHERE b.user.userId = :userId")
List<Broadcast> findAllByUserUserId(Long userId);

// OrderRepository
@Query("SELECT o FROM orders o LEFT JOIN FETCH o.product p WHERE o.user.userId = :userId")
List<Orders> findAllByUserUserId(Long userId);

2) 방송 목록 조회

- N + 1 문제 해결

방송 안에서 조회할 수 있는 유저/상품 정보를 여러 번 조회

→ 방송 조회 한 번으로 조회할 수 있게 수정

// BroadcastRepository
@Query("SELECT b FROM Broadcast b LEFT JOIN FETCH b.product LEFT JOIN FETCH b.user WHERE b.onAir = true")
List<Broadcast> findAllByOnAirTrue();

3) 방송 화면 조회

- N + 1 문제 해결 

LAZY 로딩으로 인한 추가 쿼리 발생을 막기위해 Broadcast 가져올 때, 필요한 연관 엔티티 함께 가져오도록 변경

// BroadcastRepository
@Query("SELECT b FROM Broadcast b LEFT JOIN FETCH b.product LEFT JOIN FETCH b.user WHERE b.broadcastId = :broadcastId")
Optional<Broadcast> findByBroadcastId(Long broadcastId);

4) 상품 상세 조회

- N + 1 문제 해결 

// StockRepository
@Query("SELECT s FROM Stock s JOIN FETCH s.product WHERE s.stockId = :stockId")
Stock findStockWithProduct(@Param("stockId") Long stockId);

5) 주문 상세 조회

- N + 1 문제 해결 

// OrderRepository
@Query("SELECT o FROM orders o JOIN FETCH o.user WHERE o.orderId = :orderId")
Optional<Orders> findOrderWithUserById(@Param("orderId") Long orderId);

6) 방송 시작하기

- 기존 코드

findUserByEmail에서 User를 호출하면서 auth자체에서 또 User를 호출해서 두번 호출됨

// BroadcastService
public BroadcastResponseDto createBroadcast(UserDetailsImpl auth, BroadcastRequestDto requestDto) {
    User user = userService.findUserByEmail(auth.getUsername());

    Product product = productService.createProduct(requestDto);

    Broadcast broadcast = new Broadcast(requestDto.getBroadcastTitle(), requestDto.getBroadcastDescription(), user, product);
    broadcastRepository.save(broadcast);
    return new BroadcastResponseDto(broadcast);
}

- 변경 코드

바로 불러오는 걸로 변경

public BroadcastResponseDto createBroadcast(UserDetailsImpl auth, BroadcastRequestDto requestDto) {
    User user = auth.getUser();

    Product product = productService.createProduct(requestDto);

    Broadcast broadcast = new Broadcast(requestDto.getBroadcastTitle(), requestDto.getBroadcastDescription(), user, product);
    broadcastRepository.save(broadcast);
    return new BroadcastResponseDto(broadcast);
}

7) 주문하기

- 기존 코드

//OrderController
@Controller
@RequiredArgsConstructor
@RequestMapping("/api")
public class OrderController {

    private final OrderService orderService;
    private final StockService stockService;

    @PostMapping("/products/{productId}/orders")
    public ResponseEntity<OrderResponseDto> createOrder(@PathVariable Long productId,
                                              @RequestBody OrderRequestDto orderRequestDto,
                                              @AuthenticationPrincipal UserDetailsImpl userDetails){
        User user = userDetails.getUser();
        Stock stock = stockService.findStockById(productId);
        OrderResponseDto orderResponseDto = orderService.createOrder(stock.getStockId(), productId, orderRequestDto, user);
        return ResponseEntity.ok(orderResponseDto);
    }
}

//OrderService
@DistributedLock(key = "#lockName")
public OrderResponseDto createOrder(Long lockName, Long productId, OrderRequestDto orderRequestDto, User user) {
    Product product = productService.findProduct(productId);
    Stock stock = stockService.findStockById(productId);

    if (stock.getProductStock() < orderRequestDto.getQuantity()) {
        throw new IllegalArgumentException(ErrorMessage.NOT_EXIST_STOCK_ERROR_MESSAGE.getErrorMessage());
    }

    Orders order = new Orders(orderRequestDto, product, user, false);
    stock.updateStock(orderRequestDto.getQuantity());
    orderRepository.save(order);

    return new OrderResponseDto(order);
}

- 변경 코드

//OrderController
@PostMapping("/products/{productId}/orders")
public ResponseEntity<OrderResponseDto> createOrder(@PathVariable Long productId,
                                          @RequestBody OrderRequestDto orderRequestDto,
                                          @AuthenticationPrincipal UserDetailsImpl userDetails){
    User user = userDetails.getUser();
    OrderResponseDto orderResponseDto = orderService.createOrder(productId, orderRequestDto, user);
    return ResponseEntity.ok(orderResponseDto);
}

//OrderService    
@DistributedLock(key = "#productId")
public OrderResponseDto createOrder(Long productId, OrderRequestDto orderRequestDto, User user) {
    Stock stock = stockService.findStockWithProduct(productId);
    Product product = stock.getProduct();

    if (stock.getProductStock() < orderRequestDto.getQuantity()) {
        throw new IllegalArgumentException(ErrorMessage.NOT_EXIST_STOCK_ERROR_MESSAGE.getErrorMessage());
    }

    Orders order = new Orders(orderRequestDto, product, user, false);
    stock.updateStock(orderRequestDto.getQuantity());
    orderRepository.save(order);

    return new OrderResponseDto(order);
}

//OrderRepository
@Query("SELECT o FROM orders o JOIN FETCH o.user WHERE o.orderId = :orderId")
Optional<Orders> findOrderWithUserById(@Param("orderId") Long orderId);

2. 쿼리 최적화

시나리오 - 100명이 회원가입하고 로그인하고 마이페이지 조회를 5초마다 10번 수행

쿼리 최적화 전